SQL interview questions have been a critical component of technical hiring for decades. If you’re a data scientist or software engineer on the job market, the ability to demonstrate your database skills in an SQL interview is critical to landing your next role.
Despite being over four decades old, SQL is still evolving at a rapid pace. To succeed in an SQL challenge, you’ll need to stay up to date on the latest advancements and prepare for the styles of problems you might encounter. Instead of reviewing basic database definitions and concepts, this article will challenge you with seven advanced SQL interview question examples you need to be familiar with to land your next dream job.
Overview of SQL Interview Questions
SQL (structured query language) is the industry-standard language for working with relational databases. Used for creating, defining, and maintaining databases, SQL is a vital skill for data scientists and software engineers.
During an SQL interview problem, candidates receive sets of data tables, input formats, and output formats and are challenged to perform a series of queries or functions with that data.
SQL interview questions can cover a wide range of database concepts, including normalization, transactions, subqueries, joining, and ordering.
While some interview questions cover basic-level questions – what is normalization? – experienced engineers and data scientists will encounter problems that test their SQL skills through hands-on coding.
Depending on the employer’s technical interviewing tool, candidates can choose from a range of relational database tools, including DB2, MySQL, Oracle, and MS SQL Server.
7 Advanced SQL Interview Questions
Below are seven examples of the kinds of problems a data scientist or software engineer might face during a technical interview. These questions all test SQL and relational database skills, and are meant to be solved in a collaborative integrated development environment (IDE).
To view the the data tables that accompany each question, click the solve problem link.
Print Prime Numbers
Write a query to print all prime numbers less than or equal to 1000. Print your result on a single line, and use the ampersand (&) character as your separator (instead of a space).
For example, the output for all prime numbers <= 10 would be:
2&3&5&7
New Companies
Amber’s conglomerate corporation just acquired some new companies. Each of the companies follows this hierarchy: Founder → Lead Manager → Senior Manager → Manager → Employee
Given the table schemas below, write a query to print the company_code, founder name, total number of lead managers, total number of senior managers, total number of managers, and total number of employees. Order your output by ascending company_code.
The tables may contain duplicate records. The company_code is string, so the sorting should not be numeric. For example, if the company_codes are C_1, C_2, and C_10, then the ascending company_codes will be C_1, C_10, and C_2.
Weather Observation Station
Consider P1(a,b) and P2(c,d) to be two points on a 2D plane.
- a happens to equal the minimum value in Northern Latitude (LAT_N in STATION).
- b happens to equal the minimum value in Western Longitude (LONG_W in STATION).
- c happens to equal the maximum value in Northern Latitude (LAT_N in STATION).
- d happens to equal the maximum value in Western Longitude (LONG_W in STATION).
Query the Manhattan Distance between points P1 and P2 and round it to a scale of 4 decimal places.
The STATION table is described as follows:
where LAT_N is the northern latitude and LONG_W is the western longitude.
Binary Tree Nodes
You are given a table, BST, containing two columns: N and P, where N represents the value of a node in Binary Tree, and P is the parent of N.
Write a query to find the node type of Binary Tree ordered by the value of the node. Output one of the following for each node:
- Root: If node is root node.
- Leaf: If node is leaf node.
- Inner: If node is neither root nor leaf node.
Question: Tenured Employees
Concepts Covered: SQL (Basic), JOIN, ORDER BY
There are two data tables with employee information: EMPLOYEE and EMPLOYEE_UIN. Query the tables to generate a list of all employees who have been employed fewer than three years in order of NAME, then of ID, both ascending. The result should include the UIN followed by the NAME. While the secondary sort is by ID, the result includes UIN but not ID.
Interview Guidelines
Join the tables to get UIN. Filter results to TIME < 3 and sort ascending by name, id.
Schema
| EMPLOYEE | ||
| Name | Type | Description |
| ID | Integer | The ID of the employee. This is a primary key. |
| NAME | String | The name of the employee having [1, 20] characters. |
| TIME | Integer | The tenure of the employee. |
| ADDRESS | String | The address of the employee having [1, 25] characters. |
| SALARY | Integer | The salary of the employee. |
| EMPLOYEE_UIN | ||
| Name | Type | Description |
| ID | Integer | The ID of the employee. This is a primary key. |
| UIN | String | The unique identification number of the employee. |
Sample Input
| EMPLOYEE | ||||
| ID | NAME | TIME | ADDRESS | SALARY |
| 1 | Sherrie | 1 yrs | Paris | 74635 |
| 2 | Paul | 7 yrs | Sydney | 72167 |
| 3 | Mary | 2 yrs | Paris | 75299 |
| 4 | Sam | 3 yrs | Sydney | 46681 |
| 5 | Dave | .33 yrs | Texas | 11843 |
| EMPLOYEE_UIN | |
| 1 | 57520-0440 |
| 2 | 49638-001 |
| 3 | 63550-194 |
| 4 | 68599-6112 |
| 5 | 63868-453 |
| Sample Output |
| 63868-453 Dave |
| 63550-194 Mary |
| 57520-0440 Sherrie |
Challenge Question: 15 Days of Learning SQL
Difficult Level: Hard
Julia conducted a 15 days of learning SQL contest. The start date of the contest was March 01, 2016 and the end date was March 15, 2016.
Write a query to print the total number of unique hackers who made at least 1 submission each day (starting on the first day of the contest), and find the hacker_id and name of the hacker who made maximum number of submissions each day (without considering if they made submissions the days before or after). If more than one such hacker has the maximum number of submissions, print the lowest hacker_id. The query should print this information for each day of the contest, sorted by the date.
Input Format
The following tables hold contest data:
- Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker.
- Submissions: The submission_date is the date of the submission, submission_id is the id of the submission, hacker_id is the id of the hacker who made the submission, and score is the score of the submission.
Challenge Question: Interviews
Difficulty Level: Hard
Samantha interviews many candidates from different colleges using coding challenges and contests. Write a query to print the contest_id, hacker_id, name, and the sums of total_submissions, total_accepted_submissions, total_views, and total_unique_views for each contest sorted by contest_id. Exclude the contest from the result if all four sums are 0.
Note: A specific contest can be used to screen candidates at more than one college, but each college only holds 1 screening contest.
Input Format
The tables hold interview data:
- Contests: The contest_id is the id of the contest, hacker_id is the id of the hacker who created the contest, and name is the name of the hacker.
- Colleges: The college_id is the id of the college, and contest_id is the id of the contest that Samantha used to screen the candidates.
- Challenges: The challenge_id is the id of the challenge that belongs to one of the contests whose contest_id Samantha forgot, and college_id is the id of the college where the challenge was given to candidates.
- View_Stats: The challenge_id is the id of the challenge, total_views is the number of times the challenge was viewed by candidates, and total_unique_views is the number of times the challenge was viewed by unique candidates.
- Submission_Stats: The challenge_id is the id of the challenge, total_submissions is the number of submissions for the challenge, and total_accepted_submission is the number of submissions that achieved full scores.