SQL Query writing interviews questions and answers

Beginner

1. What is the difference between SELECT, WHERE, and ORDER BY clauses?

Answer:

• SELECT: Specifies the columns you want to retrieve from a table.
• WHERE: Filters the data based on a specific condition.
• ORDER BY: Sorts the results based on a particular column(s) in ascending or descending order.

How can you retrieve only distinct values for a specific column?

Answer:

• You can use the DISTINCT keyword after the SELECT clause.
• Example: SELECT DISTINCT City FROM Customers;

Intermediate

• Write a query to find the total number of employees in each department from an “Employees” table with a “Department” column.

Answer:

SELECT Department, COUNT(*) AS TotalEmployees
FROM Employees
GROUP BY Department;

Explain the difference between JOINs (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN).

Answer:

• Joins combine data from multiple tables based on a related column.
• INNER JOIN: Returns only matching rows from both tables.
• LEFT JOIN: Returns all rows from the left table and matching rows from the right table.
• RIGHT JOIN: Returns all rows from the right table and matching rows from the left table.
• FULL JOIN: Returns all rows from both tables, including unmatched rows.
• Write a query to find the top 5 highest-selling products from a “Products” table with a “Price” and “QuantitySold” column.
• Answer:

SELECT ProductName, Price * QuantitySold AS TotalSales
FROM Products
ORDER BY TotalSales DESC
LIMIT 5;

Advanced

• Explain the concept of subqueries and how they are used.

Answer:

Subqueries are nested queries used within another query to retrieve data based on a specific condition.

They can be used for filtering, aggregation, or comparisons.

• Write a query to find customers who haven’t placed any orders in the last 6 months from a “Customers” and “Orders” table (linked by a “CustomerID”).

Answer:

SELECT c.CustomerName
FROM Customers c
WHERE c.CustomerID NOT IN (
  SELECT CustomerID
  FROM Orders
  WHERE OrderDate >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
);

Explain how to handle NULL values in SQL queries.

Answer:

• Several functions like ISNULL, COALESCE, and CASE statements can be used to handle NULL values based on your requirement (checking for null, replacing null with a default value, etc.).

Question -1

write a sql query to find the first and last row of emp table

-- First row
SELECT * FROM emp ORDER BY emp_id ASC LIMIT 1;

-- Last row
SELECT * FROM emp ORDER BY emp_id DESC LIMIT 1;

Question -2

You have a table of employees with id, name, email address, salary etc. Write an SQL query to print names of all the employees whose salary greater than or equal to the employee with first name “Rita”. Employee table — Eid, Ename, Email, Salary

SELECT Ename
FROM Employee
WHERE Salary >= (
    SELECT Salary
    FROM Employee
    WHERE Ename = 'Rita'
);

Question 3

The company’s customer database contains duplicate records due to a recent data migration. Write a SQL query to identify and remove duplicate records from the customer table (Table_Customers), considering all columns for duplication check.

DELETE FROM Table_Customers c1
USING Table_Customers c2
WHERE c1.CustomerID <> c2.CustomerID AND c1.Name = c2.Name 
  AND c1.Email = c2.Email 
  AND c1.Phone = c2.Phone 
  -- Add more columns for comparison as needed (e.g., Address, City, etc.)
ORDER BY c1.CustomerID;

WITH CTE AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY col1, col2, ..., colN ORDER BY col1, col2, ..., colN) AS row_num
    FROM Table_Customers
)
DELETE FROM CTE WHERE row_num > 1;

Questions 4

You’re tasked with integrating data from two different sources into a single dataset for analysis. Table A contains customer information (customer_id, name, email) while Table B contains order information (order_id, customer_id, order_date, total_amount). Write a SQL query to retrieve the names of customers who have placed orders in the past month.

SELECT DISTINCT A.name
FROM Table_A A
JOIN Table_B B ON A.customer_id = B.customer_id
WHERE B.order_date >= DATEADD(month, -1, GETDATE()) AND B.order_date < GETDATE();

SELECT A.name
FROM Table_A AS A
INNER JOIN Table_B AS B ON A.customer_id = B.customer_id
WHERE B.order_date >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH);

Question-5

Write an SQL Query to fetch the Departments that have less than five people in it.

SELECT Department
FROM Employees  -- Replace 'Employees' with your actual table name for employees
GROUP BY Department
HAVING COUNT(*) < 5;

Questions -6

Write an SQL Query to Show One Row Twice in Results from A Table

SELECT *
FROM your_table
WHERE id = specific_id  -- Replace 'specific_id' with the actual ID
UNION ALL
SELECT *
FROM your_table
WHERE id = specific_id  -- Replace 'specific_id' with the actual ID;

SELECT t.*
FROM your_table t
CROSS JOIN (VALUES (1), (2)) AS d(dummy);  -- Adjust number of duplicates (2 in this case)
WHERE t.id = specific_id;  -- Replace 'specific_id' with the actual ID

Questions -7

You’re tasked with analyzing the sales performance of a particular product category over the past year. Write a SQL query to calculate the total sales amount for each month within the last year for the specified product category.

SELECT YEAR(order_date) AS year,
       MONTH(order_date) AS month,
       SUM(sales_amount) AS total_sales
FROM your_orders_table o
INNER JOIN your_products_table p ON o.product_id = p.product_id
WHERE p.product_category = 'specified_category'  -- Replace 'specified_category' with the actual category
  AND order_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY year, month;

How do you find duplicate records in a table?

SELECT column1, column2, COUNT(*)
FROM table_name
GROUP BY column1, column2
HAVING COUNT(*) > 1;