Top SQL Practice Interview Questions - Solved

Q: What is the difference between where and having clause and when would you use one?
Q: What is difference between "DROP" and "TRUNCATE" in SQL?
Q: SQL query to select maximum of salary for each department ?
Q: SQL query to select a particular pattern ?
Q: What is a primary key in SQL?
Q: Difference between a unique key and a primary key?
Q: Selecting maximum from each column.
Q: What is a foreign key?
Q: What is a self-join?
Q: What is a union and how is it used?
Q: What is a subquery and how is it different from a regular query?
Q: Difference between a clustered index and a non-clustered index?
Q: What is a view?
Q: What is a stored procedure and how is it used?
Q: Salaries of all employees who make more than the average?
Q: Return the names and department names of all employees who have been with the company for more than 5 years?
Q: Query that will return the names and review scores of all employees who have received a score of 4 or higher on their most recent review?
Q: Identify customers who have made a purchase in the last 3 months?
Q: How would you delete duplicates from a table ?


Don't Leave it to Chance


Create Your Professional Resume Now

SQL or Structured Query Language is widely used query language to query RDBMS tables. SQL is widely used in data analysis, data transformation, ETL etc. This page will provide basic as well as scenario based SQL interview questions. We encourage you to understand these concepts and logics and do a hands-on before going for an interview.

Q: Difference between "WHERE" and "HAVING" caluse in SQL ?

Both "WHERE" and "HAVING" clause is used to filter unwanted record in a query result but the difference lies within their usage.

Use Having Clause : When you have an aggregate function along with grouping then to filtr out records you need to use "HAVING" clause in your query.



Use Where Clause : When you are selecting records in a query without aggegate or group by , in that case "WHERE" clause to be used to filter out records from the query output.



"Where" clause is also used in selecting keys while joining tables.

Q: "DROP" Vs "TRUNCATE" in SQL ?

When you "DROP" a table, it deletes the data as well as table. This means that it will delete the table structure also i.e. its DDL will be deleted also.

One need to use "CREATE TABLE" comand again to create table.
After droping a table , if you use "Select" command then you will get "Table not found" error.

When "DELETE" command is used, it deletes only the data from table.
It will also give you flexibity of deleting only particular records by using "WHERE" clause with "DELETE" command.
When you delete full table data, structure stil remains intact.
When you run "Select" command after deleting full data then it will give you output as zero records or zero rows returned as tale is still there but data is deleted.

Q: SQL query to select maximum of salary per department ?

In this query there is a database table which contains information about employees along with their department and salary.
Our task is to select those employees records which are having highest salary within their department.



Select department_name, max(salary) as maximum_salary from employee_table group by department_name

Q: SQL query to select employees whose name starts with K or contains "and" in it .

This is a simple interview question where we have to search column for a pattern.



Select * from employee_table where employee_name like 'K%'
The above query will fetch the records with employees having name starts with "K"

Select * from employee_table where employee_name like '%and%'
The above query will fetch the records with employees having name containing string "and"

Q: What is a primary key in SQL?

A primary key is a column or set of columns in a database table that uniquely identifies each row in the table. Primary keys are important because they are used to enforce the uniqueness of data in a table and ensure that no two rows have the same primary key value.

Q: Can you explain the difference between a unique key and a primary key?

In a database, a unique key is a field that is guaranteed to be unique across all records in a table. This means that no two records in the table can have the same value for the unique key field. A primary key, on the other hand, is a special type of unique key that is used to identify each record in a database table. It is called a primary key because it is the main way to identify a record in the table, and it is typically used as a foreign key in other tables to establish a relationship between those tables and the table that contains the primary key.

Q: How could you get max of each column from a given table ?

This can be achieved with simple select and max keyword :
select max(a), max(b), max(c) from table

Q: What is a foreign key?

A foreign key is a column or group of columns in a database table that refers to the primary key in another table. The purpose of a foreign key is to establish and enforce a link between two tables to ensure the accuracy and integrity of the data. For example, if you have a customers table and an orders table, the orders table might have a foreign key that references the primary key of the customers table, to indicate which customer placed each order. This helps to ensure that each order is associated with the correct customer, and it also helps to prevent orphaned records in the orders table if a customer is deleted from the customers table.

Q: What is a self-join?

A self-join is a type of SQL join that allows a table to be joined with itself. This is typically done when a table has a self-referential relationship, such as when a table has a foreign key that references its own primary key. Self-joins can be used to compare values in a column with other values in the same column in the same table, or to retrieve hierarchical data from a table. For example, an employee table with a manager_id column that references the employee_id of the employee's manager could use a self-join to retrieve the hierarchy of employees and their managers

Q: What is a union and how is it used?

A union in SQL is a set operator that is used to combine the results of two or more SELECT statements. The union operator combines the results of the individual SELECT statements and eliminates any duplicate rows. For example, if you have two SELECT statements that each return a list of names, you could use the union operator to combine the results and create a single list of all names from both queries, with no duplicates.
Here is an example of how you might use the union operator in a SQL query:

SELECT name FROM table1
UNION
SELECT name FROM table2

This query would combine the results of the two SELECT statements and return a single list of unique names from both table1 and table2.

Q: What is a subquery and how is it different from a regular query?

In SQL, a subquery is a query within another query. A subquery is executed by the database engine and the result of the subquery is passed back to the outer query for further processing. Subqueries are often used in WHERE and SELECT clauses, but they can also be used in other clauses such as UPDATE and DELETE.

One key difference between a subquery and a regular query is that a subquery is used to return a result set that is used in the main query, while a regular query is used to return a result set that is returned directly to the user. Another difference is that a subquery can be used to select, update, or delete data, while a regular query can only be used to select data. Finally, a subquery can be used to test for the existence or non-existence of data, while a regular query cannot.

Q: Can you explain the difference between a clustered index and a non-clustered index?

A clustered index is a type of index in a database that determines the physical order of data in a table. This means that the data rows in the table are stored in the order dictated by the clustered index, which can improve the speed of certain types of queries.
On the other hand, a non-clustered index is a type of index that does not determine the physical order of data in a table. Instead, it creates a separate "index" table that stores the column values that the index is based on, along with a pointer to the data rows in the original table.
This can also improve query performance, but in a different way than a clustered index.

Q: What is a view and how is it used?

A view is a virtual table in a database that is based on the results of a SELECT query. It allows users to store complex query results in a database in the form of a table, which can be accessed and used like any other database table.
This can be useful in a variety of situations, such as when you want to store the results of a complex query that is used frequently, or when you want to provide users with access to specific data without giving them access to the underlying tables.
In general, views are used to simplify and streamline the way users work with data in a database

Q: What is a stored procedure and how is it used?

A stored procedure is a pre-defined set of SQL statements that can be called and executed on a database server. It is typically used to perform a specific task, such as calculating the total amount due for a customer's orders, or to provide a common interface for accessing data in the database.
Stored procedures can be used to improve the performance and security of a database, since they allow you to group together multiple SQL statements and execute them with a single call. This can reduce network traffic and improve the overall efficiency of the database.
Additionally, since stored procedures are pre-defined and stored on the server, they can be tightly controlled and secured, which can help protect against malicious or unauthorized access to the database.
Here is an example of a simple stored procedure that calculates the total amount due for a customer's orders in a database:

CREATE PROCEDURE get_total_due(IN customer_id INT)
BEGIN
SELECT SUM(amount_due)
FROM orders
WHERE customer_id = customer_id;
END;

To use this stored procedure, you would call it with a statement like this:

CALL get_total_due(1234);
This would execute the stored procedure and return the total amount due for all orders belonging to the customer with ID 1234.

Q: You are working on a database that stores information about employees and their salaries. Your manager asks you to write a query that will return the names and salaries of all employees who make more than the average salary. How would you approach this task?


SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Q: You are working on a database that stores information about employees and departments. Write a query that will return the names and department names of all employees who have been with the company for more than 5 years.


SELECT e.name, d.name
FROM employees e
JOIN departments d
ON e.department_id = d.id
WHERE DATEDIFF(YEAR, e.hire_date, CURDATE()) > 5;

Q: You are working on a database that stores information about employees and their performance reviews. Write a query that will return the names and review scores of all employees who have received a score of 4 or higher on their most recent review.


SELECT e.name, r.score
FROM employees e
JOIN reviews r
ON e.id = r.employee_id
WHERE r.review_date = (SELECT MAX(review_date)
FROM reviews
WHERE employee_id = e.id)
AND r.score >= 4;

Q: You are working on a project to analyze customer data and you need to write a query to identify customers who have made a purchase in the last 3 months but have not made a purchase in the last 6 months.

To identify customers who have made a purchase in the last 3 months but have not made a purchase in the last 6 months, you can use the following query:

SELECT customer_id
FROM purchases
WHERE purchase_date BETWEEN CURRENT_DATE - INTERVAL 3 MONTH AND CURRENT_DATE
AND customer_id NOT IN (SELECT customer_id FROM purchases WHERE purchase_date BETWEEN CURRENT_DATE - INTERVAL 6 MONTH AND CURRENT_DATE - INTERVAL 3 MONTH)


This query selects the customer_id from the purchases table where the purchase_date is within the last 3 months (between the current date and 3 months ago). It then uses a subquery to exclude customers who have made a purchase within the last 6 months but not within the last 3 months.
Note that this query assumes that you have a table named purchases with columns named customer_id and purchase_date. The exact syntax for the BETWEEN and INTERVAL clauses may vary depending on the database management system (DBMS) being used.

Q: How would you delete duplicates from a table ?


WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY col1, col2, ... ORDER BY col1, col2, ...) AS rn
FROM mytable
)
DELETE FROM cte WHERE rn > 1;


This query uses a common table expression (CTE) to select all rows from the mytable table, along with a row_number() value that is calculated for each row based on the values in the specified columns (col1, col2, etc.). The PARTITION BY clause is used to specify the columns to group by, and the ORDER BY clause is used to specify the order in which the rows should be ranked within each group.

The CTE is then used to delete all rows where the rn value is greater than 1, effectively deleting all duplicates.
Note that this query will only delete duplicates if they are exact copies of each other. If you want to delete rows that are similar but not identical, you will need to modify the PARTITION BY and ORDER BY clauses to specify the relevant columns and define what constitutes a duplicate.