SQL Interview Questions

In this document, you can find the list of some commonly asked SQL interview questions.

Before starting with questions it's important that you revise some important topics related to SQL before the interview. For an SQL interview, you can prepare for the following topics.

If not all you should be confident in 80% of these topics and familiar with the concept of others.

Here are some important topics to study before an SQL interview:

SQL basics: Make sure you have a solid understanding of SQL syntax and the basic commands (SELECT, FROM, WHERE, ORDER BY, GROUP BY, etc.) and can write simple queries.
Joins: Be familiar with different types of joins (INNER, LEFT, RIGHT, FULL OUTER) and how to use them to combine data from multiple tables.
Aggregate functions: Know how to use aggregate functions (SUM, COUNT, AVG, MAX, MIN) to calculate values based on groups of rows.
Subqueries: Be able to use subqueries to retrieve data from other tables based on a condition.
Indexes: Understand how indexes work and how to create and use them to improve query performance.
Constraints: Be familiar with different types of constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK) and how to use them to enforce data integrity.
Views: Understand how views work and how to create and use them to simplify complex queries.
Stored procedures and functions: Be able to create and use stored procedures and functions to encapsulate business logic.
Transactions: Understand how transactions work and how to use them to ensure data consistency and integrity.
Window functions: Be able to use window functions to perform complex calculations based on groups of rows.
Data types: Understand different data types (numeric, string, date/time, etc.) and how to use them in queries.
Normalization: Be familiar with the concept of database normalization and the different levels of normalization.
Performance tuning: Understand how to optimize query performance by using indexes, avoiding subqueries, and other techniques.
Database design: Be familiar with principles of database design, including entity-relationship modeling, data modeling, and schema design.
Recent updates and features: Be aware of any recent updates and features in the SQL language, and be able to discuss how they might impact your work.

It's also important to practice writing SQL queries on your own and to review and analyze sample queries and data sets. By mastering these topics and practicing your skills, you can feel confident and well-prepared for your SQL interview.

Below are some commonly asked interview questions from basics to intermediate.

What is SQL? SQL (Structured Query Language) is a programming language used to manage and manipulate data in relational databases.
What are the types of SQL statements? There are several types of SQL statements, including SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, and ALTER.
What is the difference between a primary key and a foreign key? A primary key is a column or a set of columns that uniquely identifies each row in a table, while a foreign key is a column or a set of columns that refers to the primary key of another table.
What is a join in SQL? A join is a SQL operation used to combine data from two or more tables based on a related column between them. There are different types of joins, such as inner join, left join, right join, and full outer join.
What is a subquery in SQL? A subquery is a query nested inside another query. It can be used to retrieve data from one or more tables and use that data in the main query.
What is the difference between a view and a table in SQL? A view is a virtual table based on the result of a SQL statement, while a table is a physical structure that stores data. Views do not store data and are generally used to simplify complex queries.
What is normalization in SQL? Normalization is a process of organizing data in a database to reduce redundancy and improve data integrity. There are different levels of normalization, such as first normal form (1NF), second normal form (2NF), and third normal form (3NF).
What is an index in SQL? An index is a data structure used to improve the performance of queries by providing quick access to specific rows in a table. It is created on one or more columns in a table.
What is a trigger in SQL? A trigger is a SQL code that is automatically executed in response to a specific event, such as inserting, updating, or deleting data in a table.
What is the difference between a stored procedure and a function in SQL? A stored procedure is a precompiled block of SQL code that can be executed multiple times, while a function is a set of SQL statements that returns a single value. Functions can be used in SQL queries, while stored procedures cannot.
Write a query to find the total number of customers in a database
```
SELECT COUNT(*) FROM customers;
```

Write a query to find the names of all customers who have placed an order in the past month.

SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.order_date > DATEADD(month, -1, GETDATE());

Write a query to find the top 5 best-selling products.

SELECT p.name, SUM(oi.quantity) AS total_sold
FROM products p
JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.name
ORDER BY total_sold DESC
LIMIT 5;

Write a query to find the average order value for each customer.

SELECT c.name, AVG(o.total) AS average_order_value
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;

Write a query to find the number of orders placed by each customer in the past year.

SELECT c.name, COUNT(*) AS total_orders
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.order_date > DATEADD(year, -1, GETDATE())
GROUP BY c.name;

Write a query to find the top-selling product for each year.

SELECT YEAR(order_date) AS year, 
       product_name, 
       total_sales
FROM (
  SELECT YEAR(order_date), 
         product_name, 
         SUM(quantity * price) AS total_sales,
         ROW_NUMBER() OVER (PARTITION BY YEAR(order_date) ORDER BY SUM(quantity * price) DESC) AS rn
  FROM orders o
  JOIN order_details od ON o.order_id = od.order_id
  JOIN products p ON od.product_id = p.product_id
  GROUP BY YEAR(order_date), product_name
) q
WHERE rn = 1;

Write a query to find the running total of sales for each month.

SELECT order_date, 
       SUM(total_sales) OVER (ORDER BY order_date) AS running_total
FROM (
  SELECT DATE_TRUNC('month', order_date) AS order_date, 
         SUM(quantity * price) AS total_sales
  FROM orders o
  JOIN order_details od ON o.order_id = od.order_id
  JOIN products p ON od.product_id = p.product_id
  GROUP BY DATE_TRUNC('month', order_date)
) q;

Write a query to find the difference in sales between each month and the previous month.

SELECT current_month, 
       previous_month, 
       current_month_sales - previous_month_sales AS sales_difference
FROM (
  SELECT DATE_TRUNC('month', order_date) AS current_month, 
         SUM(quantity * price) AS current_month_sales,
         LAG(DATE_TRUNC('month', order_date)) OVER (ORDER BY DATE_TRUNC('month', order_date)) AS previous_month,
         LAG(SUM(quantity * price)) OVER (ORDER BY DATE_TRUNC('month', order_date)) AS previous_month_sales
  FROM orders o
  JOIN order_details od ON o.order_id = od.order_id
  JOIN products p ON od.product_id = p.product_id
  GROUP BY DATE_TRUNC('month', order_date)
) q
WHERE previous_month IS NOT NULL;

Write a query to find the top-selling product for each customer.

SELECT customer_id, 
       product_name, 
       total_sales
FROM (
  SELECT customer_id, 
         product_name, 
         SUM(quantity * price) AS total_sales,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY SUM(quantity * price) DESC) AS rn
  FROM orders o
  JOIN order_details od ON o.order_id = od.order_id
  JOIN products p ON od.product_id = p.product_id
  GROUP BY customer_id, product_name
) q
WHERE rn = 1;

Write a query to find and remove duplicates from a table.

-- To find the duplicates
SELECT column1, column2, COUNT(*) AS count
FROM table_name
GROUP BY column1, column2
HAVING COUNT(*) > 1;

-- To remove the duplicates
DELETE FROM table_name
WHERE id NOT IN (
  SELECT MAX(id)
  FROM table_name
  GROUP BY column1, column2
);

Write a query to find and merge duplicates in a table.

-- To find the duplicates
SELECT column1, column2, COUNT(*) AS count
FROM table_name
GROUP BY column1, column2
HAVING COUNT(*) > 1;

-- To merge the duplicates
UPDATE table_name
SET column1 = new_value
WHERE id NOT IN (
  SELECT MAX(id)
  FROM table_name
  GROUP BY column1, column2
);

DELETE FROM table_name
WHERE id NOT IN (
  SELECT MAX(id)
  FROM table_name
  GROUP BY column1, column2
);

Write a query to find rows that have similar but not exact values in a column.

SELECT column_name, 
       COUNT(DISTINCT column_name) AS count
FROM table_name
GROUP BY SOUNDEX(column_name)
HAVING COUNT(DISTINCT column_name) > 1;

Write a query to find and replace duplicates in a table.

-- To find the duplicates
SELECT column1, column2, COUNT(*) AS count
FROM table_name
GROUP BY column1, column2
HAVING COUNT(*) > 1;

-- To replace the duplicates
UPDATE table_name
SET column1 = new_value
WHERE id NOT IN (
  SELECT MAX(id)
  FROM table_name
  GROUP BY column1, column2
);

DELETE FROM table_name
WHERE id NOT IN (
  SELECT MAX(id)
  FROM table_name
  GROUP BY column1, column2
);

PreviousDeveloping smart contracts NextPower BI Interview Questions

Last updated 1 year ago