<h2>Prepping for SQL Interview Questions?</h2> A lot of data science is based on data gathering and preprocessing, which more often than not takes place in a relational database. Aspiring data scientists must learn database terminology, design concepts, and the programming language - SQL. After all, these often make up a significant portion of data science interviews. If you want to get hired as a data professional you need to answer some basic data science SQL interview questions. <blockquote>Need to buff your Python skills? Get started with our free <a href="https://github.com/Appsilon/datascience-python" target="_blank" rel="noopener">Introduction to Data Science in Python course</a>.</blockquote> Today, we present you with ten SQL interview questions for data professionals. We’ve split the article into two sections: <ul><li>Database theory and terminology</li><li>Practical, hands-on coding problems</li></ul> We’ll keep the pace light with the assumption that the data science position you’re applying for doesn’t use SQL as the primary language. In this example, you might expect analysts/engineers who handle the most of database-related work. <strong>Are you new to SQL?</strong> Check out these resources to get started as a data scientist: <ul><li><a href="https://appsilon.com/introduction-to-sql/" rel="noopener noreferrer">Introduction to SQL: 5 Key Concepts Every Data Professional Must Know</a></li><li><a href="https://appsilon.com/intermediate-sql/" rel="noopener noreferrer">Intermediate SQL for Data Science - Analytical Functions Deep Dive</a></li></ul> Table of contents: <ul><li><a href="#question-1">Theory and Terminology - Foreign Key</a></li><li><a href="#question-2">Theory and Terminology - Difference Between DDL, DML, DCL, and TCL</a></li><li><a href="#question-3">Theory and Terminology - SQL Views</a></li><li><a href="#question-4">Theory and Terminology - SQL Joins - Difference Between Inner and Left</a></li><li><a href="#question-5">Theory and Terminology - Indexes</a></li><li><a href="#question-6">Coding Question - Copy Data/Structure from One Table to Another</a></li><li><a href="#question-7">Coding Question - Second Highest Salary</a></li><li><a href="#question-8">Coding Question - Duplicate Emails</a></li><li><a href="#question-9">Coding Question - Highest Salary by Department</a></li><li><a href="#question-10">Coding Question - Calculate Sales per Month</a></li><li><a href="#interview-tips">General Coding Interview Tips</a></li><li><a href="#conclusion">Conclusion</a></li></ul> <hr /> <h2>SQL Interview Questions - Theory and Terminology</h2> <h3 id="question-1">What is a Foreign Key? Demonstrate How to Implement it</h3> Put simply, a foreign key is a type of database key used to link two tables. It identifies the relationship between database tables by referencing a column/set of columns. A child table contains the foreign key constraint and it links to the primary key column/set of columns in the parent table. Foreign key constraint forces database referential integrity. Before inserting data into the child table, the existence of the foreign key value is checked in the parent’s table primary key. Unlike with a primary key, you can have as many foreign keys in a single table as you want. Remember that there’s a maximum of one primary key per table. When defining a foreign key constraint, you can also specify what action will be taken when the referenced value in the parent table is updated or deleted, using the <code>ON UPDATE</code> and <code>ON DELETE</code> clauses, respectively. There are four scenarios: <ul><li><strong>NO ACTION</strong> - An update/delete in the parent table will fail with an error.</li><li><strong>CASCADE</strong> - The same action performed on the referenced values of the parent table will be reflected in the child table.</li><li><strong>SET NULL</strong> - The related values in the child table are set to NULL if referenced values in the parent table are updated/deleted.</li><li><strong>SET DEFAULT</strong> - The related values in the child table will be set to their default value if referenced values in the parent table are updated/deleted.</li></ul> Let’s see how foreign key works in practice. Imagine we had the following two tables - <code>customer</code> and <code>sale</code> - with their respective structures: <img class="size-full wp-image-11756" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b01eb48d837221d29a23d6_customer-and-sale-foreign-key-works.webp" alt="Image 1 - Table structures for demonstrating foreign key constraint" width="1233" height="483" /> Image 1 - Table structures for demonstrating foreign key constraint As you can see, the <code>customer_id</code> column is the primary key column in the <code>customer</code> table. We want to track which customer made the sale in the <code>sale</code> table, so we <em>reference</em> the <code>customer_id</code> as a foreign key. Is it still confusing? Let’s take a look at the actual data: <img class="size-full wp-image-11758" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b01eb46c7e8f92760a3481_customer-and-sales-data.webp" alt="Image 2 - Customer-Sale relationship" width="1020" height="732" /> Image 2 - Customer-Sale relationship The <code>customer_id</code> column in the <code>sale</code> table can’t have any value that isn’t present in the parent table. The values in the <code>sale</code> table can repeat, as one customer can make multiple purchases. There can’t be a sale belonging to a customer with the ID of 5, because that customer doesn’t exist. Trying to insert such a row would result in an error. <h3 id="question-2">What is the Difference Between DDL, DML, DCL, and TCL?</h3> The only common letter in all acronyms is "L" - which stands for "Language". These represent four categories into which the SQL commands have been separated: <ul><li><strong>Data Definition Language (DDL)</strong> - Involves SQL commands used to <em>define</em> data structures - <code>CREATE</code>, <code>ALTER</code>, <code>TRUNCATE</code>, and <code>DROP</code>.</li><li><strong>Data Manipulation Language (DML)</strong> - Involves SQL commands used to <em>manipulate</em> data - <code>SELECT</code>, <code>INSERT</code>, <code>UPDATE</code>, <code>DELETE</code>. This is the part of the language data scientists will use the most.</li><li><strong>Data Control Language (DCL)</strong> - Involves SQL commands used commonly by database administrators (DBAs) to manage permissions - <code>GRANT</code>, <code>REVOKE</code>.</li><li><strong>Transaction Control Language (TCL)</strong> - Also commonly used by database administrators, but to ensure the transactions that occur in the database happen in such a way that minimizes the danger of suffering from data loss.</li></ul> <h3 id="question-3">What is an SQL View?</h3> A View is a virtual table that has contents obtained as a result of an SQL query. You can save the results of a complex SQL query to a view. Views are typically created with the <code>CREATE OR REPLACE VIEW view_name</code> statement, but the syntax may vary from one DBMS to the other. For example, let’s imagine you want to create a view that contains only sales where the total amount was greater than or equal to USD 50 (<em>see Image 2</em>). The resulting view - <code>v_sales_gte_50</code> - and the base table would look like this: <img class="size-full wp-image-11780" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b01eb5307dbcdd40afe850_SQL-view-data.webp" alt="Image 3 - SQL View demonstration" width="1022" height="712" /> Image 3 - SQL View demonstration And the underlying SQL query would look something like this: <script src="https://gist.github.com/darioappsilon/a1adbd6ee95a2f7b2d228b4a441cec72.js"></script> SQL Views are used when you want to save time by reducing the amount of SQL you need to write, and when you don’t want to allow each user to see every part of the database. <h3 id="question-4">What’s the Difference Between Inner and Left join?</h3> An SQL join allows you to display a result set that contains fields from two or more tables. Take a look at our Customer-Sale relationship - without joins it would be impossible to display the customer name and the total sale amount in one result set. We need joins for that, and the two most widely used ones are <code>INNER</code> and <code>LEFT</code> join. An <strong>inner join</strong> fetches only an intersection of two tables - in our example that would be only the customers that have made the sale - <code>customer_id</code> exists both in <code>customer</code> and <code>sale</code> tables. A <strong>left join</strong> fetches all rows from the left table and tries to match them with the rows on the right table. If the value exists only in the left table, the columns from the right table will have the value of NULL. In our example that would mean that we fetch all the customers and match them with their sales, and put nulls to those customers who haven’t made the sale yet. Let’s examine this visually. We’ve added another customer - Jane - that hasn’t placed any orders yet. In the case of an inner join, she isn’t displayed in the result set: <img class="size-full wp-image-11774" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b01eb627d78ec828839808_inner-join.webp" alt="Image 4 - Inner join demonstration" width="1022" height="1204" /> Image 4 - Inner join demonstration In the case of a left join, Jane is displayed in the result set, but her values for date of sale and the amount are NULL, as she didn’t make any sales yet: <img class="size-full wp-image-11776" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b01eb783f54e0b0d33cda3_left-join.webp" alt="Image 5 - Left join demonstration" width="1020" height="1256" /> Image 5 - Left join demonstration SQL-wise, you would write these joins as follows: <script src="https://gist.github.com/darioappsilon/6d30d1c48a1fae8459979e069917f1a4.js"></script> <h3 id="question-5">What’s an Index in the Database?</h3> Indexes are special lookup tables that the database search engine uses to speed up SQL queries. An index in the database is very similar to an index in the back of a book. For example, let’s imagine you’ve picked up a 1000-page book on object-oriented programming. You’re interested in the term <em>encapsulation</em>, so you refer to the index at the back of the book to find the page(s) where the term is mentioned. That way, you’re much faster than if you were to go over the book page by page in search of the term. An index can help you speed up <code>SELECT</code> queries and <code>WHERE</code> clauses, but it comes with a cost - it slows down data input (<code>INSERT</code> and <code>UPDATE</code>), for obvious reasons. After each insertion/update an entire index has to be updated. That’s similar to adding a new chapter to a book - the old index section becomes useless. You can create an index with the <code>CREATE INDEX &lt;index_name&gt; ON &lt;table_name&gt;</code> syntax, and you can create it on one or multiple columns. In the case of composite (multiple column) indexes, chose columns that you frequently use in the <code>WHERE</code> clause. <h2>Coding SQL Interview Questions</h2> <h3 id="question-6">How to Copy Data from One Table to Another? How to Copy Only the Structure?</h3> Oftentimes you want to copy a specific data subset from one table to the other, or copy table structure only. As it turns out, both are extremely easy to implement in SQL. Let’s start with copying data from one table to the other. You can use the <code>CREATE TABLE &lt;table_name&gt; AS SELECT</code> syntax that creates a new table from a query result. For example, the code snippet below copies the entire <code>customer</code> table: <script src="https://gist.github.com/darioappsilon/98635ce08ab8c6ea6e5134cf455fccde.js"></script> Here’s the resulting <code>customer_2</code> table: <img class="size-full wp-image-11760" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b01eb8cf125ca98b886d3d_customer_2-table.webp" alt="Image 6 - Copied table" width="810" height="288" /> Image 6 - Copied table You’re free to complicate the inner <code>SELECT</code> by adding <code>WHERE</code> clauses, joining tables, or anything else. Let’s see how to copy the structure only, without data. To do so, specify a condition in the <code>WHERE</code> clause that’s always false, such as <code>1 = 2</code>: <script src="https://gist.github.com/darioappsilon/783117fae2aa448fdb104916910c972b.js"></script> The resulting <code>customer_3</code> table is empty, as you would expect: <img class="size-full wp-image-11762" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b01eba790b9e1ad88e43e1_customer_3-table.webp" alt="Image 7 - Copied table structure" width="810" height="326" /> Image 7 - Copied table structure That does it for the first - and the simplest data science SQL interview question. <h3 id="question-7">Second Highest Salary</h3> It’s easy to get the highest or the lowest value of a column. But what about the second highest? That’s a riddle most beginners will find tricky at first. Think of it logically - the second highest salary is the highest salary that isn’t the first one - and that’s exactly how we solved the problem in the first method. The second method assumes you’re more familiar with SQL and you know what <code>OFFSET</code> does. Basically, we can keep only unique salaries and order them from largest to the lowest and keep only the first - offset by some number. The offset of 1 means we want to skip the first highest value. Here’s the code for creating the table, inserting the data, and implementing both solutions: <script src="https://gist.github.com/darioappsilon/2af7bffcdd65d2f58763d576fcce6c10.js"></script> The second highest salary is 7500: <img class="size-full wp-image-11778" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b01ebc790b9e1ad88e457c_second-highest-salary-output.webp" alt="Image 8 - Second highest salary" width="344" height="238" /> Image 8 - Second highest salary The second method is more scalable because you can use it to calculate any N-th highest salary. The first method works, but it would be tricky and repetitive to calculate, let’s say, the third-highest salary. <h3 id="question-8">Duplicate Emails</h3> Just like with the previous data science SQL interview question, this one can also be solved in multiple ways. The goal is to scan a column in a table and return only emails that occur more than once. The first method leverages the <code>COUNT()</code> method in a subquery. A subquery is created to show the count of the frequency of each email. The result set is then filtered to keep only emails with a count larger than 1. The second method is much faster to write, as it leverages the <code>HAVING</code> clause. It’s used as a replacement for the <code>WHERE</code> statement in conjunction with aggregations. Below you’ll find the code snippet for creating the table, inserting data, and implementing both methods: <script src="https://gist.github.com/darioappsilon/34d8d0a1aee6fb940a64016b4f2bb636.js"></script> Only "mark@email.com" occurs more than once: <img class="size-full wp-image-11766" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b01ebca597077171be4530_duplicate-email-output.webp" alt="Image 9 - Duplicate emails" width="340" height="236" /> Image 9 - Duplicate emails The first method shows you know how to think, while the second shows you know SQL. Both are fine, and you’re free to demonstrate both in an interview. <h3 id="question-9">Highest Salary by Department</h3> Let’s discuss salaries some more. This time, we’re interested in obtaining the highest salaries by department. To make things interesting, we’ve decided to split the data into two tables - <code>department</code> and <code>employee_detail</code>. Here’s the code to create and populate both tables: <script src="https://gist.github.com/darioappsilon/ef41851eb479197bb4342d48be2f3717.js"></script> The table <code>department</code> is sort of a lookup table, and has only two rows: <img class="size-full wp-image-11772" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b01ebd0a2ba062e1ba05ff_highest-salaries-by-department-table.webp" alt="Image 10 - Table department" width="406" height="202" /> Image 10 - Table department The <code>employee_detail</code> table holds a reference to the <code>department</code> table: <img class="size-full wp-image-11768" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b01ebec99b38c07a081344_employee-detail-table.webp" alt="Image 11 - Table employee detail" width="612" height="416" /> Image 11 - Table employee detail To get the highest salary by department, we’ll have to join both tables and use the <code>IN</code> clause to keep only the highest salary by department: <script src="https://gist.github.com/darioappsilon/4bad371ebf85f210000cdbd63c0fffd8.js"></script> Here’s the result set: <img class="size-full wp-image-11770" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b01ebf1acc439bc259ae44_highest-salaries-by-department-output.webp" alt="Image 12 - Highest salary by department" width="352" height="208" /> Image 12 - Highest salary by department Get comfortable with writing joins, as all companies structure their databases by respecting the <a href="https://www.guru99.com/database-normalization.html" target="_blank" rel="noopener noreferrer">normal forms</a>, which means each entity type is stored in a dedicated table to reduce redundancy. <h3 id="question-10">Calculate Total Sales per Month</h3> This question tests your ability to extract a month from the datetime column. Doing so is DMBS-specific, but in Postgres, you can use the <code>EXTRACT()</code> function to get the month from the datetime. The following query extracts the month from the datetime and sums the <code>total_amount</code> column. The result is then grouped by the month to get the total sales by month: <script src="https://gist.github.com/darioappsilon/abb386ac36ae10de0f53adc04bbe02b8.js"></script> We don’t have much data - only two months: <img class="size-full wp-image-11782" src="https://webflow-prod-assets.s3.amazonaws.com/6525256482c9e9a06c7a9d3c%2F65b01ec0fd01060a37df17f5_sum-of-sales-per-month.webp" alt="Image 13 - Sum of sales per month" width="340" height="188" /> Image 13 - Sum of sales per month And that does it for the hands-on questions. Let’s cover some general coding interview tips next. <h2 id="interview-tips">Tips for Data Science SQL Interview Questions</h2> As a data scientist, you should have a working knowledge of database terminology, design, and SQL. The last one is likely the most important, as you likely won’t be designing databases full time. Still, the first two are important to pass the technical interview. If you’re looking to ace your first data science interview, consider the following bonus tips: <ul><li><strong>State the problem clearly</strong> - Make sure you and the interviewer are on the same page. Don’t start writing code until you know exactly what has to be done, and you’re certain you can solve it. There’s nothing worse than figuring out your solution won’t work after writing 90% of the code.</li><li><strong>Explain your solution before coding it</strong> - A great way to ensure you and the interviewer are on the same page is by explaining your solution and thought process in plain English. Talk with the interviewer and come up with the simplest solution - it’s usually the correct one.</li><li><strong>Write pseudocode first</strong> - Sure, SQL looks like pseudocode, but you get the point. Pseudocode is an excellent way to fact-check your logic and eliminate any errors in your thought process.</li><li><strong>Think of the edge cases</strong> - What if the value is null? What if the value is too long to fit in the column? Make sure your interviewer knows you’re thinking of the edge cases.</li></ul> <hr /> <h2 id="conclusion">Conclusion for SQL Interview Questions Prep</h2> You should have only one goal in mind before your first technical interview - survival. It’s a high-stress environment, but it gets easier with time and experience. Having the fundamental knowledge and knowing the most common data science SQL interview questions will help with the anxiety issues, so take your time going over them. Make sure to understand the questions and answers, so you’re not lost if the interviewer formulates a question differently. <em>What did your first technical interview look like? Did you get the job? What are the tips and tricks you can give to beginners?</em> Share in the comment section below to let others know what to expect. <blockquote>Interested in Python interview questions for data science? <a href="https://appsilon.com/data-science-coding-interview-questions/" rel="noopener noreferrer">Check our curated list</a>.</blockquote>