30 SQL Queries Interview Questions and Answers

You use WHERE for filtering rows before applying any grouping or aggregation. The code snippet below illustrates the use of WHERE. It filters the Users table for rows where the Age is greater than 18.

SELECT * FROM UsersWHERE Age > 18;

The result of the query is similar to the table below.

On the other hand, you use HAVING to filter groups after performing grouping and aggregation. You apply it to the result of aggregate functions, and it is mostly used with the GROUP BY clause.

SELECT FirstName, Age FROM UsersGROUP BY FirstName, AgeHAVING Age > 30;

The code above selects the FirstName and Age columns, then groups by the FirstName and Age, and finally gets entries with age greater than 30. The result of the query looks like this:

To find duplicate records, you must first define the criteria for detecting duplicates. Is it a combination of two or more columns where you want to detect the duplicates, or are you searching for duplicates within a single column?

The following steps will help you find duplicate data in a table.

• Use the GROUP BY clause to group all the rows by the column(s) on which you want to check the duplicate values.

• Use the COUNT function in the HAVING command to check if any groups have more than one entry.

Let's see how to handle single-column duplicates. In a table Users, there are three users who are 30 years of age. Let's use the GROUP BY clause and COUNT function to find the duplicate values.

SELECT Age, COUNT(Age)FROM UsersGROUP BY AgeHAVING COUNT(Age) > 1

The result of the query looks like this:

Handling multi-column (composite) duplicates is similar to handling single-column duplicates.

SELECT FirstName, LastName, COUNT(*) AS dup_countFROM UsersGROUP BY FirstName, LastNameHAVING COUNT(*) > 1;

After finding duplicates, you might be asked how to delete the duplicates. The query to delete duplicates is shown below using Common Table Expression (CTE) and ROW_NUMBER().

WITH ranked AS (  SELECT *,         ROW_NUMBER() OVER (PARTITION BY Age ORDER BY id) AS rn  FROM Users)DELETE FROM UsersWHERE id IN (  SELECT id  FROM ranked  WHERE rn > 1);

The query deletes all the duplicates while retaining the first row of data.

A JOIN combines data from two or more tables based on a related column between them. It is useful when you need to retrieve data spread across multiple tables in relational database management systems.

An INNER JOIN returns only rows with a match in both tables based on the specified join condition. If there are no matching rows, there will be no results. The SQL syntax for an INNER JOIN is shown in the code snippet below.

SELECT table1.column_name1, table1.column_name2, table2.column_name1, table2.column_name2 FROM table1INNER JOIN table2ON table1.column_name = table2.column_name

For example, there are two tables Users and Cities with the following data:

Users table

Cities table

Let's say you want to retrieve a list of users and their respective city names. You can achieve this using the INNER JOIN query.

SELECT users.firstName, users.lastName, users.age, cities.name as cityName FROM usersINNER JOIN citiesON users.cityId = cities.id

LEFT JOIN returns all the rows from the left table (table 1) and the matched rows from the right table (table 2). If no matching rows exist in the right table (table 2), then NULL values are returned. The SQL syntax for a Left join is shown in the code snippet below.

SELECT table1.column_name1, table1.column_name2, table2.column_name1, table2.column_name2 FROM table1LEFT JOIN table2ON table1.column_name = table2.column_name 

Let's have a look at a practical example with Users and Cities tables from before.

When you execute the LEFT JOIN query, you get the table below.

Given a table Salaries,

The query to find the second-highest salary is shown in the code snippet below

SELECT DISTINCT SalaryFROM SalariesORDER BY Salary DESCLIMIT 1 OFFSET 1

The result of the query is shown below

UNION is used for removing duplicates while UNION ALL keeps all duplicates. UNION is slower compared to UNION ALL because of de-duplication. You use UNION when you want to obtain unique records and UNION ALL when you want every row even if they are repeated.

Indexes in databases are like the indexes in books. They increase the speed of data retrieval from a database. When you want to read data from a table, instead of going through all the rows of the table, indexes help to go straight to the row you are looking for.

They improve SELECT queries, improve performance, and make sorting and filtering faster. They also ensure data integrity. There are different types of indexes, which include:

• B-Tree index

• Composite index

• Unique index

• Full text index

• Bitmap index

• Clustered index

• Non-clustered index

A primary key is the unique identifier of a row of data in a table. You use it to identify each row uniquely, and no two rows can have the same primary key. A primary key column cannot be null. In the example below, user_id is the primary key.

CREATE TABLE users (   user_id INT PRIMARY KEY,   name VARCHAR(100),   phoneNumber VARCHAR(100));

A foreign key is like a bridge between two tables. A foreign key in one table is the primary key in another. It is the connector between the two tables.

GROUP BY is a standard SQL command that groups rows with the same value in the specified column. You should use with aggregate functions such as COUNT, MIN, MAX, etc.

The query below illustrates the GROUP BY clause:

SELECT columnName FROM TableGROUP BY columnName

If you SELECT a column not in the GROUP BY clause, it will throw an error stating that the column must be in the GROUP BY clause or in an aggregate function. Let's use the table below as an illustration.

If you run the query below against the database:

SELECT firstName, phoneNumber FROM phoneNumbersGROUP BY phoneNumber

The result will be an error because firstName is not in the GROUP BY clause and not using an aggregate function.

Given a table Users that looks like this:

The query to COUNT the number of users by country is:

SELECT country, COUNT(country) FROM usersGROUP BY country

The query uses the GROUP BY clause to group the users by country and then shows the count in the next column. The result of the query looks like this:

If you use the GROUP BY clause without an aggregate function, it is equivalent to using the DISTINCT command. For example, the command below:

SELECT phoneNumber FROM phoneNumbersGROUP BY phoneNumber

is equivalent to:

SELECT DISTINCT phoneNumber FROM phoneNumbers

The difference is that COUNT(*) counts all the rows of data, including NULL values, while COUNT(column_name) counts only non-NULL values in the specified column. Let's illustrate this using a table named Users.

If you use COUNT(*), the result will be 4 but if you use COUNT(firstName), it will return 3, omitting the null value.

A subquery is a query that is inside another query. You use it for queries that require complex logic. You should use subqueries when you want to use the result of that subquery for another query. In the example below, the subquery is in brackets.

SELECT firstName,  (SELECT COUNT(*)  FROM cities  WHERE cities.id = users.city_id) AS cityCountFROM users;

On the other hand, a JOIN combines two or more tables based on related columns between them. The related column is usually a foreign key. You should use JOINS when you want to pull related data from different tables together. The code below illustrates how to use a JOIN.

SELECT firstName, COUNT(*) FROM usersJOIN cities ON users.city_id = cities.id

A JOIN is faster than a subquery in the following scenarios:

• When you are querying data from multiple tables.

• When you are filtering or joining on index columns.

Given an Employees table with columns id, name, and salary that looks like this:

The query to find employees earning more than the average salary is:

SELECT * FROM employeesWHERE salary > (SELECT AVG(salary) FROM employees);

A correlated subquery is a subquery that depends on a value from the outer query. This means that the query is evaluated for each row that might be selected in the outer query. Below is an example of a correlated subquery.

SELECT name, country_id, salaryFROM employees emWHERE salary > (  SELECT AVG(salary) FROM employees  country_id = em.country_id);

The code above:

• Runs the outer query through each row of the table.

• Takes the country_id from the employees table.

• Iterates through the other rows and does the same calculation.

This leads to a degrading performance as the data in the table grows.

You should use a correlated subquery if you want to perform row-specific operations or cannot achieve an operation using JOIN or other aggregate functions.

EXISTS and IN are used in subqueries to filter results, but they perform different functions depending on their usage.

You should use EXISTS in the following situations:

• When you want to check if a row exists and not the actual values.

• When the subquery is a correlated query.

• When the subquery returns many rows but you want to get the first match.

You should use IN in the following scenarios:

• When you are comparing a column to a list of values.

• When the subquery returns a small or static list.

Yes, you can nest subqueries multiple levels deep when you want to perform complex logic. A nested subquery is a subquery inside another subquery, forming layers of subqueries. Many SQL engines allow multiple layers of subqueries, but this causes poor readability and degrades performance.

A window function is a function that allows you to perform operations on a specific set of rows related to the current row. Unlike aggregate functions that perform calculations on an entire data set, window functions can perform operations on a subset of data. These calculations are valid for aggregates, ranking, and cumulative totals without altering the original dataset.

Let's use a table Sales as a reference for this query. It has three columns: id, day which represents the day of the week, and amount which is the amount sold in US Dollars. The table looks like this:

The query to calculate the running total is:

SELECT  id,  sale_date,  amount,  SUM(amount) OVER (ORDER BY sale_date) AS running_totalFROM  sales;

The query uses a Window function OVER to sum the amount for each row of data and saving the running total. It gets the total for each day and adds it to the previous totals. The result of the query looks like this:

You can observe from the image that the last column is running_total, which takes the amount for the current day and adds it to its previous value to get its current value.

The RANK() function assigns each row a rank according to an ascending or descending order. If there are matching values, it assigns them the same position and then skips the next number for the next rank. For example, if two rows have equivalent values and are both assigned rank 1, the next rank would be 3 instead of 2.

Let's use the Sales table from the previous question to illustrate the RANK() function. The query to rank in order of the amount looks like this:

SELECT  id,  day,  amount,  RANK() OVER (ORDER BY amount DESC) AS amount_rankFROM  sales;

The result is shown in the image below. You will observe that the amount 900 takes the first rank and 200 the lowest rank. Also, there is a gap between rank 2 and 4 because two values have the same rank. You can also infer that the most sales were on Friday and the least on Monday.

DENSE_RANK() function is similar to RANK() in that it assigns ranks to rows, but the difference is that DENSE_RANK does not leave a gap when there are two or more equivalent values. Let's illustrate it with the Sales table from above. The query is shown below.

SELECT  id,  day,  amount,  DENSE_RANK() OVER (ORDER BY amount DESC) AS amount_rankFROM  sales;

The result is shown below. As you will notice, there is no gap between the ranks like in the RANK function.

ROW_NUMBER assigns a unique number to each row depending on the order you specify. It does not skip numbers; even though there are equivalent values, it assigns them different numbers, unlike RANK and DENSE_RANK functions that give them the same rank.

Let's use the same Sales table to illustrate. The query below shows how to use the ROW_NUMBER function.

SELECT  id,  day,  amount,  ROW_NUMBER() OVER (ORDER BY amount DESC) AS rowNumberFROM  sales;

The result is shown in the image below. You will notice that the rownumber column increases, and even though there are matching values, it just assigns a unique row number to each.

LAG() and LEAD() are window functions used to retrieve data from rows before and after a specified row. You can also refer to them as positional SQL functions.

LAG() allows you to access a value stored in rows before the current row. The row may be directly before or some rows before. Let's take a look at the syntax:

LAG(column_name, offset, default_value)

It takes three arguments.

• column_name: This specifies the column to fetch from the previous row.

• offset: This is an optional argument and specifies the number of rows behind to look at. The default is 1.

• default_value: This is the value to assign when no previous row exists. It is optional, and the default is NULL.

Using the Sales table, let's illustrate the LAG() function. The query is used to find the previous day sales. LAG() is useful when you want to create reports of past events.

SELECT  id,  day,  amount,  LAG(amount) OVER (ORDER BY id) AS previous_day_salesFROM  sales;

The result of the query looks like this:

You use the LEAD() function to get data from rows after the current row. Its syntax is similar to that of the LAG() function. You can use it for forecasting future trends by looking ahead.

The query using the LEAD() function is shown below.

SELECT  id,  day,  amount,  LEAD(amount) OVER (ORDER BY id) AS previous_day_salesFROM  sales;

You will use the LAG() function to detect gaps in a sequence of dates per user. You will compare each date with the previous one and check if the difference is greater than 1.

Let's use a table ClockIns to demonstrate how you detect gaps. The table has two columns, userId and clockInDate, representing the user identification number and the date the user clocked in with an access card into a facility. The table looks like this:

To query to find gaps per user looks like this:

WITH clockInGaps AS (  SELECT    userid,    clockInDate,    LAG(clockInDate) OVER (PARTITION BY userId ORDER BY clockInDate) AS previousClockInDate  FROM    clockIns)SELECT   userId,  previousClockInDate AS gapStart,  clockInDate AS gapEend,  clockInDate - previousClockInDate - 1 AS gapDaysFROM clockInGapsWHERE clockInDate - previousClockInDate > 1ORDER BY userId, gapStart;

The code above starts with creating an expression clockInGaps that queries for each user and their clockInDate and uses the LAG function to get the previous date for each user. Then, the main query filters each row and finds the gaps between the current date and the previous date. The result of the query looks like this:

NTILE() is a window function that divides rows into a pre-defined number of roughly equal groups. It's like breaking your data into different sets based on your defined criteria. For example, let's say you have some student scores from 1 to 100; you can use the NTILE() function to categorize the scores into different groups or buckets.

The syntax of the NTILE() function is:

NTILE(n) OVER (ORDER BY some_column)

• n: represents the number of groups you want to divide your rows into.

• ORDER BY: defines the order of the rows in each group where the function is applied.

Let's see a practical example using a table Scores. The table stores students' scores on a test. We will see how to use the NTILE() function.

The query using the NTILE() function looks like this:

SELECT  id,  score,  NTILE(3) OVER (ORDER BY score DESC) AS categoryFROM scores;

The NTILE() function is useful in data analysis because it can detect outliers in a data set and create histograms of data. It can also create percentiles and quartiles for data distribution.

To optimize slow-running queries, you need to analyze the query first to know what to optimize. You can perform different optimizations depending on the query. Some of the optimizations include:

• Using indexes effectively: Indexes speed up queries by enabling the database to find entries that fit specific criteria quickly. Indexing is the process of mapping the values of one or more columns to a unique value that makes it easy to search for rows that match a search criteria. You can create indexes on columns used frequently in the WHERE, JOIN, and ORDER BY clauses. However, note that creating too many indexes can slow down inserts, updates, and deletions.

• **Avoid SELECT *** : Using the SELECT ***** statement can slow down your query performance because it returns all the columns in a table including the ones not needed for the query. You should select only the columns that you need for a query for optimal performance. So when you see a query that selects all columns, you should check if all the columns are really needed and used further down the query chain.

• Avoid using subqueries: Subqueries slow down query performance, especially when you use them in the WHERE or HAVING clauses. You should avoid using subqueries where possible and use JOINs or other techniques instead.

• Utilize stored procedures: Stored procedures are precompiled SQL statements stored in a database, and can be called from an application or directly from a query. Using stored procedures can improve your query performance by reducing the amount of data that is sent between the database and your application, and also saves time required to compile the SQL statements.

You should avoid using **SELECT *** as much as possible in your production code for the following reasons:

• Increased IO: Using **SELECT ***, you can return unnecessary data that leads to increased Input/Output cycles at the database level since you will be reading all the data in a table. This effect will be more impactful on a table with a lot of data and even slow down your query.

• Increased network traffic: **SELECT *** returns more data than required to the client, which uses more network bandwidth than needed. The increase in network bandwidth causes data to take longer to reach the client application and impacts the application's performance.

• More application memory: The return of a lot of data would make your application require more memory to hold the unnecessary data which might you might not use and this impacts application performance.

• Makes maintenance more difficult: Using **SELECT *** makes code maintenance more challenging. If the table structure changes by adding, removing, or renaming columns, the queries using **SELECT *** could break unexpectedly. You should explicitly specify the columns from which you want to fetch data to ensure resilience against potential changes in the database schema.

Missing indexes can affect the performance of queries, especially when the data grows. The major impacts of missing indexes are listed below:

• Slow queries: Without indexes, every read query will go through the whole table to find matching rows. This will get worse as the data in the table grows.

• Locking and concurrency issues: Scanning a table without indexes takes longer, and locking the table can prevent other queries from running, affecting application performance.

• Inefficient joins: Joins on tables without indexes on the join keys are extremely slow and result in bad query performance.

• Poor user experience: Missing indexes can lead to poor user experience in your applications. It can result to slower page loads, application hanging when data is being fetched from the database.

SARGable stands for Search Argumentable query, which uses indexes and leads to efficient queries. If a query is SARGable, the database engine quickly locates rows using indexes, avoids scanning the whole table, and improves query performance.

The common mistakes people encounter when using the GROUP BY clause include:

• Selecting non-aggregated columns not in the GROUP BY clause: This is a common mistake made my beginners and experts. An example query of this looks like this:

SELECT day, amount FROM SalesGROUP BY day

In the query above, the amount column is not part of the GROUP BY clause and will throw an error that it must appear in the GROUP BY clause. To fix this, you should add an aggregate function to the amount column.

SELECT day, MAX(amount) FROM SalesGROUP BY day

• Not using aggregate functions: It is also a common mistake to use GROUP BY without aggregate functions. GROUP BY usually goes with aggregate functions like MAX, MIN, COUNT, etc.

• Grouping by multiple columns: Grouping by multiple columns can make the query meaningless. It is not common to group by many columns, and when this happens, you should check if you really need to group by those columns.

Since NULL is unknown, a NOT IN query containing a NULL or NULL in the list of possible values will always return 0 records because of the unknown result introduced by the NULL value. SQL cannot determine for sure whether the value is not in that list.

Let's illustrate this using a table Sales that looks like this:

If you run the query below, it will return an empty result because SQL cannot determine if the value is not in the list because nothing equals or doesn't equal NULL.

SELECT * from salesWHERE amount NOT IN (200, 300, 600, NULL);