SQL Question Bank
.jpg)
Do you need help finding SQL questions on the internet? Searching ends here.
I've compiled all the SQL questions I found on social media, such as LinkedIn, YouTube, Instagram, and Telegram. Let's dive into the database world.
Top Interview Questions SQL
Q1: Explain the difference between RANK(), DENSE_RANK(), and ROW_NUMBER() functions using example.
Q2 – Q4 use Table: employee (EmpID, ManagerID, JoinDate, Dept, Salary)
Q2: Find the nth highest salary from the Employee table.
Q3: You have an employee table with employee ID and manager ID. Find all employees under a specific manager, including their subordinates at any level.
Q4: Write a query to find the cumulative salary of employees department-wise, who have joined the company in the last 30 days.
Q5: Find the top 2 customers with the highest order amount for each product category, handling ties appropriately. Table: Customer (CustomerID, ProductCategory, OrderAmount)
[1] Top N Analysis: Find top N products by sales, top N employees by salaries, or top N items within each category.
[2] Year-Over-Year Growth: Calculate YOY growth, products with higher sales than the previous month, or category-wise growth.
[3] Running Totals: Track running sales over months, rolling N months' sales, especially within categories.
[4] Pivot Operations: Transform rows to columns, such as displaying year-wise sales for each category in separate columns.
[5] Join Analysis: Count the records resulting from different types of joins.
1. How do window functions work in SQL, and when would you use them?
2. What is the purpose of indexing in SQL? How do you decide when to create an index?
3. Can you explain the differences between UNION and UNION ALL?
4. How do you write a SQL query to find the second highest salary in a table?
5. What are CTEs (Common Table Expressions) and their benefits?
1- Find the second-highest salary in a department. You may need to use the ROW_NUMBER() or DENSE_RANK() function to solve this.
2- Write a SQL query to return the total number of transactions per user for each day. This could involve using GROUP BY and COUNT() functions. This is common in data aggregation tasks.
3- Create a query that selects projects with the highest budget-per-employee ratio from two related tables (projects and employees). This tests your ability to handle complex joins and aggregations.
2- Write a SQL query to return the total number of transactions per user for each day. This could involve using GROUP BY and COUNT() functions. This is common in data aggregation tasks.
3- Create a query that selects projects with the highest budget-per-employee ratio from two related tables (projects and employees). This tests your ability to handle complex joins and aggregations.
1. Find the second-highest salary in a table without using LIMIT or TOP.
2. Write a SQL query to find all employees who earn more than their managers.
3. Find the duplicate rows in a table without using GROUP BY.
4. Write a SQL query to find the top 10% of earners in a table.
5. Find the cumulative sum of a column in a table.
6. Write a SQL query to find all employees who have never taken a leave.
7. Find the difference between the current row and the next row in a table.
8. Write a SQL query to find all departments with more than one employee.
9. Find the maximum value of a column for each group without using GROUP BY.
10. Write a SQL query to find all employees who have taken more than 3 leaves in a month.
These questions are designed to test your SQL skills, including your ability to write efficient queries, think creatively, and solve complex problems.
Here are the answers to these questions:
1. SELECT MAX(salary) FROM table WHERE salary NOT IN (SELECT MAX(salary) FROM table)
2. SELECT e1.* FROM employees e1 JOIN employees e2 ON e1.manager_id = (link unavailable) WHERE e1.salary > e2.salary
3. SELECT * FROM table WHERE rowid IN (SELECT rowid FROM table GROUP BY column HAVING COUNT(*) > 1)
4. SELECT * FROM table WHERE salary > (SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY salary) FROM table)
5. SELECT column, SUM(column) OVER (ORDER BY rowid) FROM table
6. SELECT * FROM employees WHERE id NOT IN (SELECT employee_id FROM leaves)
7. SELECT *, column - LEAD(column) OVER (ORDER BY rowid) FROM table
8. SELECT department FROM employees GROUP BY department HAVING COUNT(*) > 1
9. SELECT MAX(column) FROM table WHERE column NOT IN (SELECT MAX(column) FROM table GROUP BY group_column)
1️⃣ Find employee manager I'd.
✅https://lnkd.in/dFKea2Js
2️⃣ Find records present in table A and not in table B.
✅https://lnkd.in/d7VCfQCq
3️⃣ Find Nth highest salary.
✅https://lnkd.in/dJ32rWrC
4️⃣What will the output be for different types of joins.
✅https://lnkd.in/d35wnDDw
5️⃣Remove duplicate records from a table.
✅https://lnkd.in/dwjzuRVE
6️⃣Find Min and Max Salary departmentwise from the employee table.
✅https://lnkd.in/de9YB6N5
7️⃣Calculate the rolling sum total.
✅https://lnkd.in/daVju6Ut
8️⃣Convert rows to columns and columns to rows without pivot function.
✅https://lnkd.in/dUiRGikk
9️⃣ Find parent-child hierarchy.
✅https://lnkd.in/d4PYHezX
🔟Write a query to derive points and a winner from the cricket points table.
✅https://lnkd.in/da8nW9NX
These questions will cover topics like
👉 Windows Functions
👉 Joins/Self Joins
👉 Group By/Having Clause
👉 Sum/Case When
👉 CTE clause
✅https://lnkd.in/dFKea2Js
2️⃣ Find records present in table A and not in table B.
✅https://lnkd.in/d7VCfQCq
3️⃣ Find Nth highest salary.
✅https://lnkd.in/dJ32rWrC
4️⃣What will the output be for different types of joins.
✅https://lnkd.in/d35wnDDw
5️⃣Remove duplicate records from a table.
✅https://lnkd.in/dwjzuRVE
6️⃣Find Min and Max Salary departmentwise from the employee table.
✅https://lnkd.in/de9YB6N5
7️⃣Calculate the rolling sum total.
✅https://lnkd.in/daVju6Ut
8️⃣Convert rows to columns and columns to rows without pivot function.
✅https://lnkd.in/dUiRGikk
9️⃣ Find parent-child hierarchy.
✅https://lnkd.in/d4PYHezX
🔟Write a query to derive points and a winner from the cricket points table.
✅https://lnkd.in/da8nW9NX
These questions will cover topics like
👉 Windows Functions
👉 Joins/Self Joins
👉 Group By/Having Clause
👉 Sum/Case When
👉 CTE clause
1. You have a dataset of product sales with the following columns:
product_id, sale_date, quantity, price
Write a Query to calculate the total revenue and total quantity sold for each product in the last 3 months
Include only products with total revenue greater than $10, 000
2. You have two tables -
(a) order having columns
order_id, customer_id, order_date, total_amount
(b) customers having columns
customer_id, customer_name,
Write a Query to find the top 5 customers by total order amount in the last year
Hint: Join two tables on customer_id
product_id, sale_date, quantity, price
Write a Query to calculate the total revenue and total quantity sold for each product in the last 3 months
Include only products with total revenue greater than $10, 000
2. You have two tables -
(a) order having columns
order_id, customer_id, order_date, total_amount
(b) customers having columns
customer_id, customer_name,
Write a Query to find the top 5 customers by total order amount in the last year
Hint: Join two tables on customer_id
1. Write a query to fetch the third-highest salary without using LIMIT or ROW_NUMBER().
2. Find employees whose salaries are above their department's average salary but below the entire company's average salary.
3. Identify duplicate records in a table and write a query to delete all but one instance of each duplicate.
4. Retrieve the top three highest-paid employees in each department.
5. Write a query to calculate the percentage contribution of each product to the total sales.
6. Display employees who joined within the last 6 months.
7. Identify the employee(s) with the longest tenure in the company.
8. Create a query to find gaps in a sequence of IDs in a table.
9. Retrieve all records in a table that have a matching record in another table based on two or more columns.
10. Write a query to identify customers who placed more orders this year than last.
Answers
Solution 1
SELECT DISTINCT salary FROM employees e1
WHERE 2 = (SELECT COUNT(DISTINCT salary) FROM employees e2 WHERE e2.salary > e1.salary);
Solution 2
SELECT e.* FROM employees e
JOIN (SELECT department_id, AVG(salary) AS dept_avg_salary FROM employees GROUP BY department_id) d
ON e.department_id = d.department_id
WHERE e.salary > d.dept_avg_salary AND e.salary < (SELECT AVG(salary) FROM employees);
Solution 3
-- Find duplicates
SELECT column1, column2, COUNT(*) FROM table_name
GROUP BY column1, column2
HAVING COUNT(*) > 1;
-- Delete duplicates
DELETE FROM table_name
WHERE id NOT IN (
SELECT MIN(id) FROM table_name GROUP BY column1, column2
);
Solution 4
SELECT * FROM (
SELECT employee_id, department_id, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees
) ranked
WHERE rank <= 3;
Solution 5
SELECT product_id,
SUM(sales) AS product_sales,
(SUM(sales) * 100.0 / (SELECT SUM(sales) FROM sales_table)) AS percentage_contribution
FROM sales_table
GROUP BY product_id;
Solution 6
SELECT * FROM employees
WHERE join_date >= DATE_ADD(CURRENT_DATE, INTERVAL -6 MONTH);
Solution 7
SELECT employee_id, DATEDIFF(CURRENT_DATE, join_date) AS tenure
FROM employees
ORDER BY tenure DESC
LIMIT 1;
Solution 8
SELECT id + 1 AS missing_id
FROM table_name t1
WHERE NOT EXISTS (SELECT id FROM table_name t2 WHERE t2.id = t1.id + 1);
Solution 9
SELECT t1.* FROM table1 t1
JOIN table2 t2 ON t1.column1 = t2.column1 AND t1.column2 = t2.column2;
Solution 10
SELECT customer_id FROM (
SELECT customer_id,
SUM(CASE WHEN YEAR(order_date) = YEAR(CURRENT_DATE) THEN 1 ELSE 0 END) AS this_year_orders,
SUM(CASE WHEN YEAR(order_date) = YEAR(CURRENT_DATE) - 1 THEN 1 ELSE 0 END) AS last_year_orders
FROM orders
GROUP BY customer_id
) order_summary
WHERE this_year_orders > last_year_orders;
2. Find employees whose salaries are above their department's average salary but below the entire company's average salary.
3. Identify duplicate records in a table and write a query to delete all but one instance of each duplicate.
4. Retrieve the top three highest-paid employees in each department.
5. Write a query to calculate the percentage contribution of each product to the total sales.
6. Display employees who joined within the last 6 months.
7. Identify the employee(s) with the longest tenure in the company.
8. Create a query to find gaps in a sequence of IDs in a table.
9. Retrieve all records in a table that have a matching record in another table based on two or more columns.
10. Write a query to identify customers who placed more orders this year than last.
Answers
Solution 1
SELECT DISTINCT salary FROM employees e1
WHERE 2 = (SELECT COUNT(DISTINCT salary) FROM employees e2 WHERE e2.salary > e1.salary);
Solution 2
SELECT e.* FROM employees e
JOIN (SELECT department_id, AVG(salary) AS dept_avg_salary FROM employees GROUP BY department_id) d
ON e.department_id = d.department_id
WHERE e.salary > d.dept_avg_salary AND e.salary < (SELECT AVG(salary) FROM employees);
Solution 3
-- Find duplicates
SELECT column1, column2, COUNT(*) FROM table_name
GROUP BY column1, column2
HAVING COUNT(*) > 1;
-- Delete duplicates
DELETE FROM table_name
WHERE id NOT IN (
SELECT MIN(id) FROM table_name GROUP BY column1, column2
);
Solution 4
SELECT * FROM (
SELECT employee_id, department_id, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees
) ranked
WHERE rank <= 3;
Solution 5
SELECT product_id,
SUM(sales) AS product_sales,
(SUM(sales) * 100.0 / (SELECT SUM(sales) FROM sales_table)) AS percentage_contribution
FROM sales_table
GROUP BY product_id;
Solution 6
SELECT * FROM employees
WHERE join_date >= DATE_ADD(CURRENT_DATE, INTERVAL -6 MONTH);
Solution 7
SELECT employee_id, DATEDIFF(CURRENT_DATE, join_date) AS tenure
FROM employees
ORDER BY tenure DESC
LIMIT 1;
Solution 8
SELECT id + 1 AS missing_id
FROM table_name t1
WHERE NOT EXISTS (SELECT id FROM table_name t2 WHERE t2.id = t1.id + 1);
Solution 9
SELECT t1.* FROM table1 t1
JOIN table2 t2 ON t1.column1 = t2.column1 AND t1.column2 = t2.column2;
Solution 10
SELECT customer_id FROM (
SELECT customer_id,
SUM(CASE WHEN YEAR(order_date) = YEAR(CURRENT_DATE) THEN 1 ELSE 0 END) AS this_year_orders,
SUM(CASE WHEN YEAR(order_date) = YEAR(CURRENT_DATE) - 1 THEN 1 ELSE 0 END) AS last_year_orders
FROM orders
GROUP BY customer_id
) order_summary
WHERE this_year_orders > last_year_orders;
1️⃣ Find Top-N Records by Group
WITH RankedData AS (
SELECT
customer_id,
product_id,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY purchase_date DESC) AS rank
FROM purchases
)
SELECT *
FROM RankedData
WHERE rank <= 3;
2️⃣ Identify Overlapping Date Ranges
SELECT
a.id, b.id
FROM bookings a
JOIN bookings b
ON a.start_date <= b.end_date
AND a.end_date >= b.start_date
AND a.id != b.id;
3️⃣ Find Duplicate Rows in a Table
SELECT
column1, column2, COUNT(*)
FROM table_name
GROUP BY column1, column2
HAVING COUNT(*) > 1;
4️⃣ Compute Running Totals
SELECT
user_id,
transaction_date,
transaction_amount,
SUM(transaction_amount) OVER (PARTITION BY user_id ORDER BY transaction_date) AS running_total
FROM transactions;
5️⃣ Find Employees in Hierarchical Order
WITH RECURSIVE EmployeeTree AS (
SELECT
employee_id,
manager_id,
1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT
e.employee_id,
e.manager_id,
et.level + 1
FROM employees e
JOIN EmployeeTree et ON e.manager_id = et.employee_id
)
SELECT *
FROM EmployeeTree;
6️⃣ Find Median
SELECT
AVG(value) AS median
FROM (
SELECT
value,
NTILE(2) OVER (ORDER BY value) AS half
FROM numbers
) AS halves
WHERE half IN (1, 2);
7️⃣ Find Percentage Contribution
SELECT
category,
SUM(sales) AS total_sales,
SUM(sales) * 100.0 / SUM(SUM(sales)) OVER () AS percentage_contribution
FROM sales
GROUP BY category;
8️⃣ Find Missing Dates
SELECT
date::date
FROM generate_series('2024-01-01', '2024-01-31', interval '1 day') AS date
WHERE date NOT IN (SELECT DISTINCT attendance_date FROM attendance);
9️⃣ Rank Dense in SQL
SELECT
product_id,
category_id,
DENSE_RANK() OVER (PARTITION BY category_id ORDER BY sales DESC) AS rank
FROM product_sales;
🔟 Dynamic Pivot Using CASE
SELECT
user_id,
SUM(CASE WHEN month = 'January' THEN sales END) AS Jan_Sales,
SUM(CASE WHEN month = 'February' THEN sales END) AS Feb_Sales
FROM sales
GROUP BY user_id;
WITH RankedData AS (
SELECT
customer_id,
product_id,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY purchase_date DESC) AS rank
FROM purchases
)
SELECT *
FROM RankedData
WHERE rank <= 3;
2️⃣ Identify Overlapping Date Ranges
SELECT
a.id, b.id
FROM bookings a
JOIN bookings b
ON a.start_date <= b.end_date
AND a.end_date >= b.start_date
AND a.id != b.id;
3️⃣ Find Duplicate Rows in a Table
SELECT
column1, column2, COUNT(*)
FROM table_name
GROUP BY column1, column2
HAVING COUNT(*) > 1;
4️⃣ Compute Running Totals
SELECT
user_id,
transaction_date,
transaction_amount,
SUM(transaction_amount) OVER (PARTITION BY user_id ORDER BY transaction_date) AS running_total
FROM transactions;
5️⃣ Find Employees in Hierarchical Order
WITH RECURSIVE EmployeeTree AS (
SELECT
employee_id,
manager_id,
1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT
e.employee_id,
e.manager_id,
et.level + 1
FROM employees e
JOIN EmployeeTree et ON e.manager_id = et.employee_id
)
SELECT *
FROM EmployeeTree;
6️⃣ Find Median
SELECT
AVG(value) AS median
FROM (
SELECT
value,
NTILE(2) OVER (ORDER BY value) AS half
FROM numbers
) AS halves
WHERE half IN (1, 2);
7️⃣ Find Percentage Contribution
SELECT
category,
SUM(sales) AS total_sales,
SUM(sales) * 100.0 / SUM(SUM(sales)) OVER () AS percentage_contribution
FROM sales
GROUP BY category;
8️⃣ Find Missing Dates
SELECT
date::date
FROM generate_series('2024-01-01', '2024-01-31', interval '1 day') AS date
WHERE date NOT IN (SELECT DISTINCT attendance_date FROM attendance);
9️⃣ Rank Dense in SQL
SELECT
product_id,
category_id,
DENSE_RANK() OVER (PARTITION BY category_id ORDER BY sales DESC) AS rank
FROM product_sales;
🔟 Dynamic Pivot Using CASE
SELECT
user_id,
SUM(CASE WHEN month = 'January' THEN sales END) AS Jan_Sales,
SUM(CASE WHEN month = 'February' THEN sales END) AS Feb_Sales
FROM sales
GROUP BY user_id;
1. Write a query to find the second-highest salary in a department. You might use ROW_NUMBER() or DENSE_RANK() to achieve this.
2. Create a query to calculate the total number of transactions per user for each day. This typically involves GROUP BY and COUNT() for aggregation.
3. Write a query to select projects with the highest budget-per-employee ratio from two related tables (projects and employees). This tests your ability to handle complex joins and aggregations.
𝗧𝗼𝗽𝗶𝗰 𝟭: 𝗡𝗲𝘁𝗳𝗹𝗶𝘅 𝗠𝗼𝘃𝗶𝗲𝘀 & 𝗦𝗵𝗼𝘄𝘀
Dataset: https://lnkd.in/gpe8RMFb
You have the Netflix Movies & Shows dataset, which contains comprehensive information about movies and TV shows available on Netflix.
The columns in the dataset are:
• show_id
• type
• title
• director
• cast
• country
• date_added
• release_year
• rating
• duration
• listed_in
• description
B͟e͟g͟i͟n͟n͟e͟r͟ ͟Q͟u͟e͟s͟t͟i͟o͟n͟s͟
1/ How many movies and TV shows are there in the dataset? Display the count for each type.
2/ What percentage of content doesn’t have a country associated with it?
I͟n͟t͟e͟r͟m͟e͟d͟i͟a͟t͟e͟ ͟Q͟u͟e͟s͟t͟i͟o͟n͟s͟
3/ Find the top 3 directors with the most content on Netflix. Display the director's name, the count of their titles, and the year of their most recent content.
4/ For each year from 2015 to 2021, calculate the percentage of movies vs TV shows added to Netflix.
A͟d͟v͟a͟n͟c͟e͟d͟ ͟Q͟u͟e͟s͟t͟i͟o͟n͟
5/ Calculate the average month-over-month growth rate of content added to Netflix for each genre. What are the top 5 fastest-growing genres?
𝗧𝗼𝗽𝗶𝗰 𝟮: 𝗘𝗺𝗽𝗹𝗼𝘆𝗲𝗲 𝗜𝗻𝗳𝗼𝗿𝗺𝗮𝘁𝗶𝗼𝗻
Dataset: https://lnkd.in/geFBaxKx
The Employee Information dataset contains detailed data about employees in a company.
The columns are as follows:
• Education
• JoiningYear
• City
• PaymentTier
• Age
• Gender
• EverBenched
• ExperienceInCurrentDomain
• LeaveOrNot
B͟e͟g͟i͟n͟n͟e͟r͟ ͟Q͟u͟e͟s͟t͟i͟o͟n͟s͟
1/ How many employees are in each Payment Tier?
2/ Calculate the average age of employees for each gender.
I͟n͟t͟e͟r͟m͟e͟d͟i͟a͟t͟e͟ ͟Q͟u͟e͟s͟t͟i͟o͟n͟s͟
3/ Find the top 3 cities with the highest percentage of employees who have been benched at least once.
4/ For each Education level, calculate the average experience in the current domain for employees who joined before 2015 vs. those who joined in 2015 or later.
A͟d͟v͟a͟n͟c͟e͟d͟ ͟Q͟u͟e͟s͟t͟i͟o͟n͟
5/ Calculate an employee's likelihood to leave based on their Payment Tier, Experience in Current Domain, and the average leave rate of their city.
Dataset: https://lnkd.in/gpe8RMFb
You have the Netflix Movies & Shows dataset, which contains comprehensive information about movies and TV shows available on Netflix.
The columns in the dataset are:
• show_id
• type
• title
• director
• cast
• country
• date_added
• release_year
• rating
• duration
• listed_in
• description
B͟e͟g͟i͟n͟n͟e͟r͟ ͟Q͟u͟e͟s͟t͟i͟o͟n͟s͟
1/ How many movies and TV shows are there in the dataset? Display the count for each type.
2/ What percentage of content doesn’t have a country associated with it?
I͟n͟t͟e͟r͟m͟e͟d͟i͟a͟t͟e͟ ͟Q͟u͟e͟s͟t͟i͟o͟n͟s͟
3/ Find the top 3 directors with the most content on Netflix. Display the director's name, the count of their titles, and the year of their most recent content.
4/ For each year from 2015 to 2021, calculate the percentage of movies vs TV shows added to Netflix.
A͟d͟v͟a͟n͟c͟e͟d͟ ͟Q͟u͟e͟s͟t͟i͟o͟n͟
5/ Calculate the average month-over-month growth rate of content added to Netflix for each genre. What are the top 5 fastest-growing genres?
𝗧𝗼𝗽𝗶𝗰 𝟮: 𝗘𝗺𝗽𝗹𝗼𝘆𝗲𝗲 𝗜𝗻𝗳𝗼𝗿𝗺𝗮𝘁𝗶𝗼𝗻
Dataset: https://lnkd.in/geFBaxKx
The Employee Information dataset contains detailed data about employees in a company.
The columns are as follows:
• Education
• JoiningYear
• City
• PaymentTier
• Age
• Gender
• EverBenched
• ExperienceInCurrentDomain
• LeaveOrNot
B͟e͟g͟i͟n͟n͟e͟r͟ ͟Q͟u͟e͟s͟t͟i͟o͟n͟s͟
1/ How many employees are in each Payment Tier?
2/ Calculate the average age of employees for each gender.
I͟n͟t͟e͟r͟m͟e͟d͟i͟a͟t͟e͟ ͟Q͟u͟e͟s͟t͟i͟o͟n͟s͟
3/ Find the top 3 cities with the highest percentage of employees who have been benched at least once.
4/ For each Education level, calculate the average experience in the current domain for employees who joined before 2015 vs. those who joined in 2015 or later.
A͟d͟v͟a͟n͟c͟e͟d͟ ͟Q͟u͟e͟s͟t͟i͟o͟n͟
5/ Calculate an employee's likelihood to leave based on their Payment Tier, Experience in Current Domain, and the average leave rate of their city.
Window Functions
1. Compute the rolling average of sales for the last three months.
2. Rank employees uniquely based on their salaries in descending order.
3. Identify the earliest and latest purchase dates for each customer.
4. Find the second highest salary in each department using window functions.
5. Calculate the percentage contribution of each employee to the company’s total revenue.
Common Table Expressions (CTEs)
1. Use a CTE to separate full names into first and last names.
2. Write a CTE to determine the longest streak of consecutive sales by an employee.
3. Generate a sequence of Fibonacci numbers up to a specific value using a recursive CTE.
4. Use a CTE to detect and list duplicate entries in a table.
5. Calculate total sales per category and filter out categories with sales below a specific threshold using a CTE.
Joins (Inner, Outer, Cross, Self)
1. List all customers, highlighting who placed orders and who didn’t (Full Outer Join).
2. Identify employees assigned to more than one project using a self-join.
3. Match orders with customers and display unmatched orders as well (Left Join).
4. Create unique product combinations using a Cross Join while excluding identical product pairs.
5. Retrieve employees along with their direct managers using a self-join.
Subqueries
1. Find customers whose total purchase value exceeds the average order value.
2. Retrieve employees with the lowest salary in their respective departments.
3. Identify products ordered more than 10 times using a subquery.
4. List regions where the highest sales value is below a specified threshold.
Aggregate Functions
1. Compute the median salary for each department.
2. Summarize monthly sales and rank them in descending order.
3. Count the number of unique customers for each product.
4. Identify the top five regions based on total sales.
5. Calculate the average order value for every customer.
Indexing and Performance
1. Write a query to locate duplicate entries in a column with an index.
2. Evaluate the effect of a composite index on query performance.
3. Identify high-cardinality columns that could benefit from indexing.
4. Compare query execution times before and after implementing a clustered index.
5. Write a query that bypasses indexing to observe performance variations.
1. Compute the rolling average of sales for the last three months.
2. Rank employees uniquely based on their salaries in descending order.
3. Identify the earliest and latest purchase dates for each customer.
4. Find the second highest salary in each department using window functions.
5. Calculate the percentage contribution of each employee to the company’s total revenue.
Common Table Expressions (CTEs)
1. Use a CTE to separate full names into first and last names.
2. Write a CTE to determine the longest streak of consecutive sales by an employee.
3. Generate a sequence of Fibonacci numbers up to a specific value using a recursive CTE.
4. Use a CTE to detect and list duplicate entries in a table.
5. Calculate total sales per category and filter out categories with sales below a specific threshold using a CTE.
Joins (Inner, Outer, Cross, Self)
1. List all customers, highlighting who placed orders and who didn’t (Full Outer Join).
2. Identify employees assigned to more than one project using a self-join.
3. Match orders with customers and display unmatched orders as well (Left Join).
4. Create unique product combinations using a Cross Join while excluding identical product pairs.
5. Retrieve employees along with their direct managers using a self-join.
Subqueries
1. Find customers whose total purchase value exceeds the average order value.
2. Retrieve employees with the lowest salary in their respective departments.
3. Identify products ordered more than 10 times using a subquery.
4. List regions where the highest sales value is below a specified threshold.
Aggregate Functions
1. Compute the median salary for each department.
2. Summarize monthly sales and rank them in descending order.
3. Count the number of unique customers for each product.
4. Identify the top five regions based on total sales.
5. Calculate the average order value for every customer.
Indexing and Performance
1. Write a query to locate duplicate entries in a column with an index.
2. Evaluate the effect of a composite index on query performance.
3. Identify high-cardinality columns that could benefit from indexing.
4. Compare query execution times before and after implementing a clustered index.
5. Write a query that bypasses indexing to observe performance variations.
1️⃣ Find the second-highest salary in a department
• Efficiently solve using ROW_NUMBER() or DENSE_RANK().
2️⃣ Calculate daily transactions per user
• Use GROUP BY and COUNT() for aggregation.
3️⃣ Projects with the highest budget-per-employee ratio
• Showcase your expertise in JOINs and advanced aggregations!
• Efficiently solve using ROW_NUMBER() or DENSE_RANK().
2️⃣ Calculate daily transactions per user
• Use GROUP BY and COUNT() for aggregation.
3️⃣ Projects with the highest budget-per-employee ratio
• Showcase your expertise in JOINs and advanced aggregations!
1. You have given a sales table with columns: id, product_id, sales_date, quantity, and price. Write a query to calculate the total revenue generated per product in the last 6 months.
Ans:
SELECT
product_id,
SUM(quantity * price) AS total_revenue
FROM
sales
WHERE
sales_date >= ADD_MONTHS(SYSDATE, -6)
GROUP BY
product_id;
2. Find the total number of employees in each department where the count exceeds 5.
Ans:
SELECT
department_id,
COUNT(*) AS total_employees
FROM
employees
GROUP BY
department_id
HAVING
COUNT(*) > 5;
3. Using a CTE, find the average salary for each department and filter out departments where the average salary is below 50,000.
Ans:
WITH DepartmentAverage AS (
SELECT
department_id,
AVG(salary) AS avg_salary
FROM
employees
GROUP BY
department_id
)
SELECT
department_id,
avg_salary
FROM
DepartmentAverage
WHERE
avg_salary >= 50000;
4. For each employee, show their salary, department, and rank within the department based on salary.
Ans:
SELECT
employee_id,
salary,
department_id,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM
employees;
5. Given an employees table with columns employee_id, manager_id, and name, write a query to find the hierarchy under a specific manager.
Ans:
SELECT
employee_id,
name,
manager_id
FROM
employees
START WITH
manager_id = :specific_manager_id
CONNECT BY
PRIOR employee_id = manager_id;
6. Update all employees in the employees table to give a 10% bonus to those whose salary is below 30,000.
Ans:
UPDATE
employees
SET
salary = salary * 1.1
WHERE
salary < 30000;
Ans:
SELECT
product_id,
SUM(quantity * price) AS total_revenue
FROM
sales
WHERE
sales_date >= ADD_MONTHS(SYSDATE, -6)
GROUP BY
product_id;
2. Find the total number of employees in each department where the count exceeds 5.
Ans:
SELECT
department_id,
COUNT(*) AS total_employees
FROM
employees
GROUP BY
department_id
HAVING
COUNT(*) > 5;
3. Using a CTE, find the average salary for each department and filter out departments where the average salary is below 50,000.
Ans:
WITH DepartmentAverage AS (
SELECT
department_id,
AVG(salary) AS avg_salary
FROM
employees
GROUP BY
department_id
)
SELECT
department_id,
avg_salary
FROM
DepartmentAverage
WHERE
avg_salary >= 50000;
4. For each employee, show their salary, department, and rank within the department based on salary.
Ans:
SELECT
employee_id,
salary,
department_id,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM
employees;
5. Given an employees table with columns employee_id, manager_id, and name, write a query to find the hierarchy under a specific manager.
Ans:
SELECT
employee_id,
name,
manager_id
FROM
employees
START WITH
manager_id = :specific_manager_id
CONNECT BY
PRIOR employee_id = manager_id;
6. Update all employees in the employees table to give a 10% bonus to those whose salary is below 30,000.
Ans:
UPDATE
employees
SET
salary = salary * 1.1
WHERE
salary < 30000;
1. Write a SQL query to find the top 5 customers with the highest total purchase amount. Assume you have two tables: Customers (CustomerID, Name) and Orders (OrderID, CustomerID, Amount).
SELECT c.CustomerID, c.Name, SUM(o.Amount) AS TotalPurchase
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.Name
ORDER BY TotalPurchase DESC
LIMIT 5;
2. Write a query to find the nth highest salary from a table Employees with columns EmployeeID, Name, and Salary.
SELECT DISTINCT Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 1 OFFSET n-1;
Replace `n` with the desired rank (e.g., 2 for the second highest).
3. Given a table Sales with columns SaleID, ProductID, SaleDate, and Quantity, write a query to find the total quantity sold for each product per month.
SELECT ProductID, DATE_TRUNC('month', SaleDate) AS Month, SUM(Quantity) AS TotalQuantity
FROM Sales
GROUP BY ProductID, Month
ORDER BY ProductID, Month;
4. Write a SQL query to find all employees who have more than one manager. Assume you have a table Employees (EmployeeID, Name, ManagerID).
SELECT EmployeeID, Name
FROM Employees
GROUP BY EmployeeID, Name
HAVING COUNT(DISTINCT ManagerID) > 1;
5. Given a table Orders with columns OrderID, CustomerID, OrderDate, and a table OrderDetails with columns OrderID, ProductID, Quantity, write a query to find the top 3 products with the highest sales quantity.
SELECT ProductID, SUM(Quantity) AS TotalQuantity
FROM OrderDetails
GROUP BY ProductID
ORDER BY TotalQuantity DESC
LIMIT 3;
6. Write a SQL query to find the second most recent order date for each customer from a table Orders (OrderID, CustomerID, OrderDate).
SELECT CustomerID, MAX(OrderDate) AS SecondRecentOrderDate
FROM Orders
WHERE OrderDate < (SELECT MAX(OrderDate) FROM Orders o2 WHERE o2.CustomerID = Orders.CustomerID)
GROUP BY CustomerID;
7. Given a table Employees with columns EmployeeID, Name, DepartmentID, Salary, write a query to find the highest paid employee in each department.
SELECT DepartmentID, EmployeeID, Name, Salary
FROM Employees e1
WHERE Salary = (SELECT MAX(Salary) FROM Employees e2 WHERE e2.DepartmentID = e1.DepartmentID);
8. Write a SQL query to calculate the cumulative sales for each day in a table Sales with columns SaleID, SaleDate, and Amount.
SELECT SaleDate, SUM(Amount) OVER (ORDER BY SaleDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CumulativeSales
FROM Sales
ORDER BY SaleDate;
9. Given a table Products with columns ProductID, Name, Price, and a table Sales with columns SaleID, ProductID, Quantity, write a query to find the product with the highest revenue.
SELECT p.ProductID, p.Name, SUM(p.Price * s.Quantity) AS Revenue
FROM Products p
JOIN Sales s ON p.ProductID = s.ProductID
GROUP BY p.ProductID, p.Name
ORDER BY Revenue DESC
LIMIT 1;
SELECT c.CustomerID, c.Name, SUM(o.Amount) AS TotalPurchase
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.Name
ORDER BY TotalPurchase DESC
LIMIT 5;
2. Write a query to find the nth highest salary from a table Employees with columns EmployeeID, Name, and Salary.
SELECT DISTINCT Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 1 OFFSET n-1;
Replace `n` with the desired rank (e.g., 2 for the second highest).
3. Given a table Sales with columns SaleID, ProductID, SaleDate, and Quantity, write a query to find the total quantity sold for each product per month.
SELECT ProductID, DATE_TRUNC('month', SaleDate) AS Month, SUM(Quantity) AS TotalQuantity
FROM Sales
GROUP BY ProductID, Month
ORDER BY ProductID, Month;
4. Write a SQL query to find all employees who have more than one manager. Assume you have a table Employees (EmployeeID, Name, ManagerID).
SELECT EmployeeID, Name
FROM Employees
GROUP BY EmployeeID, Name
HAVING COUNT(DISTINCT ManagerID) > 1;
5. Given a table Orders with columns OrderID, CustomerID, OrderDate, and a table OrderDetails with columns OrderID, ProductID, Quantity, write a query to find the top 3 products with the highest sales quantity.
SELECT ProductID, SUM(Quantity) AS TotalQuantity
FROM OrderDetails
GROUP BY ProductID
ORDER BY TotalQuantity DESC
LIMIT 3;
6. Write a SQL query to find the second most recent order date for each customer from a table Orders (OrderID, CustomerID, OrderDate).
SELECT CustomerID, MAX(OrderDate) AS SecondRecentOrderDate
FROM Orders
WHERE OrderDate < (SELECT MAX(OrderDate) FROM Orders o2 WHERE o2.CustomerID = Orders.CustomerID)
GROUP BY CustomerID;
7. Given a table Employees with columns EmployeeID, Name, DepartmentID, Salary, write a query to find the highest paid employee in each department.
SELECT DepartmentID, EmployeeID, Name, Salary
FROM Employees e1
WHERE Salary = (SELECT MAX(Salary) FROM Employees e2 WHERE e2.DepartmentID = e1.DepartmentID);
8. Write a SQL query to calculate the cumulative sales for each day in a table Sales with columns SaleID, SaleDate, and Amount.
SELECT SaleDate, SUM(Amount) OVER (ORDER BY SaleDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CumulativeSales
FROM Sales
ORDER BY SaleDate;
9. Given a table Products with columns ProductID, Name, Price, and a table Sales with columns SaleID, ProductID, Quantity, write a query to find the product with the highest revenue.
SELECT p.ProductID, p.Name, SUM(p.Price * s.Quantity) AS Revenue
FROM Products p
JOIN Sales s ON p.ProductID = s.ProductID
GROUP BY p.ProductID, p.Name
ORDER BY Revenue DESC
LIMIT 1;
1. Write a query to fetch the third-highest salary without using LIMIT or ROW_NUMBER().
2. Find employees whose salaries are above the average salary of their department but below the average salary of the entire company.
3. Identify duplicate records in a table and write a query to delete all but one instance of each duplicate.
4. Retrieve the top three highest-paid employees in each department.
5. Write a query to calculate the percentage contribution of each product to the total sales.
6. Display employees who joined within the last 6 months.
7. Identify the employee(s) with the longest tenure in the company.
8. Create a query to find gaps in a sequence of IDs in a table.
9. Retrieve all records in a table that have a matching record in another table based on two or more columns.
10. Write a query to identify customers who placed more orders this year compared to last year.
Answers
Solution 1
SELECT DISTINCT salary FROM employees e1
WHERE 2 = (SELECT COUNT(DISTINCT salary) FROM employees e2 WHERE e2.salary > e1.salary);
Solution 2
SELECT e.* FROM employees e
JOIN (SELECT department_id, AVG(salary) AS dept_avg_salary FROM employees GROUP BY department_id) d
ON e.department_id = d.department_id
WHERE e.salary > d.dept_avg_salary AND e.salary < (SELECT AVG(salary) FROM employees);
Solution 3
-- Find duplicates
SELECT column1, column2, COUNT(*) FROM table_name
GROUP BY column1, column2
HAVING COUNT(*) > 1;
-- Delete duplicates
DELETE FROM table_name
WHERE id NOT IN (
SELECT MIN(id) FROM table_name GROUP BY column1, column2
);
Solution 4
SELECT * FROM (
SELECT employee_id, department_id, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees
) ranked
WHERE rank <= 3;
Solution 5
SELECT product_id,
SUM(sales) AS product_sales,
(SUM(sales) * 100.0 / (SELECT SUM(sales) FROM sales_table)) AS percentage_contribution
FROM sales_table
GROUP BY product_id;
Solution 6
SELECT * FROM employees
WHERE join_date >= DATE_ADD(CURRENT_DATE, INTERVAL -6 MONTH);
Solution 7
SELECT employee_id, DATEDIFF(CURRENT_DATE, join_date) AS tenure
FROM employees
ORDER BY tenure DESC
LIMIT 1;
Solution 8
SELECT id + 1 AS missing_id
FROM table_name t1
WHERE NOT EXISTS (SELECT id FROM table_name t2 WHERE t2.id = t1.id + 1);
Solution 9
SELECT t1.* FROM table1 t1
JOIN table2 t2 ON t1.column1 = t2.column1 AND t1.column2 = t2.column2;
Solution 10
SELECT customer_id FROM (
SELECT customer_id,
SUM(CASE WHEN YEAR(order_date) = YEAR(CURRENT_DATE) THEN 1 ELSE 0 END) AS this_year_orders,
SUM(CASE WHEN YEAR(order_date) = YEAR(CURRENT_DATE) - 1 THEN 1 ELSE 0 END) AS last_year_orders
FROM orders
GROUP BY customer_id
) order_summary
WHERE this_year_orders > last_year_orders;
2. Find employees whose salaries are above the average salary of their department but below the average salary of the entire company.
3. Identify duplicate records in a table and write a query to delete all but one instance of each duplicate.
4. Retrieve the top three highest-paid employees in each department.
5. Write a query to calculate the percentage contribution of each product to the total sales.
6. Display employees who joined within the last 6 months.
7. Identify the employee(s) with the longest tenure in the company.
8. Create a query to find gaps in a sequence of IDs in a table.
9. Retrieve all records in a table that have a matching record in another table based on two or more columns.
10. Write a query to identify customers who placed more orders this year compared to last year.
Answers
Solution 1
SELECT DISTINCT salary FROM employees e1
WHERE 2 = (SELECT COUNT(DISTINCT salary) FROM employees e2 WHERE e2.salary > e1.salary);
Solution 2
SELECT e.* FROM employees e
JOIN (SELECT department_id, AVG(salary) AS dept_avg_salary FROM employees GROUP BY department_id) d
ON e.department_id = d.department_id
WHERE e.salary > d.dept_avg_salary AND e.salary < (SELECT AVG(salary) FROM employees);
Solution 3
-- Find duplicates
SELECT column1, column2, COUNT(*) FROM table_name
GROUP BY column1, column2
HAVING COUNT(*) > 1;
-- Delete duplicates
DELETE FROM table_name
WHERE id NOT IN (
SELECT MIN(id) FROM table_name GROUP BY column1, column2
);
Solution 4
SELECT * FROM (
SELECT employee_id, department_id, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees
) ranked
WHERE rank <= 3;
Solution 5
SELECT product_id,
SUM(sales) AS product_sales,
(SUM(sales) * 100.0 / (SELECT SUM(sales) FROM sales_table)) AS percentage_contribution
FROM sales_table
GROUP BY product_id;
Solution 6
SELECT * FROM employees
WHERE join_date >= DATE_ADD(CURRENT_DATE, INTERVAL -6 MONTH);
Solution 7
SELECT employee_id, DATEDIFF(CURRENT_DATE, join_date) AS tenure
FROM employees
ORDER BY tenure DESC
LIMIT 1;
Solution 8
SELECT id + 1 AS missing_id
FROM table_name t1
WHERE NOT EXISTS (SELECT id FROM table_name t2 WHERE t2.id = t1.id + 1);
Solution 9
SELECT t1.* FROM table1 t1
JOIN table2 t2 ON t1.column1 = t2.column1 AND t1.column2 = t2.column2;
Solution 10
SELECT customer_id FROM (
SELECT customer_id,
SUM(CASE WHEN YEAR(order_date) = YEAR(CURRENT_DATE) THEN 1 ELSE 0 END) AS this_year_orders,
SUM(CASE WHEN YEAR(order_date) = YEAR(CURRENT_DATE) - 1 THEN 1 ELSE 0 END) AS last_year_orders
FROM orders
GROUP BY customer_id
) order_summary
WHERE this_year_orders > last_year_orders;
1. You have a table Transactions with columns TransactionID, CustomerID, Date, and Amount. Write a query to calculate the cumulative revenue per customer for each month in the last year.
2. A table Production contains columns PlantID, Date, and Output. Write a query to identify the plants that consistently exceeded their daily average output for at least 20 days in a given month.
3. In a table EmployeeAttendance with columns EmployeeID, Date, and Status (values: ‘Present’, ‘Absent’), write a query to find employees with the highest consecutive absences in the last quarter.
1. Write a query to calculate the total sales for each region over the past year.
SELECT region, SUM(sales) AS total_sales
FROM sales
WHERE year = 2024
GROUP BY region;
2. How would you identify the top 3 customers with the highest revenue in a specific quarter?
1st solution:
SELECT customer_id, quarter, SUM(revenue) AS total_revenue
FROM customers
WHERE quarter = 'Q1'
GROUP BY customer_id, quarter
ORDER BY total_revenue DESC
LIMIT 3;
Or Using RANK function:
SELECT customer_id, quarter, revenue
FROM (
SELECT customer_id, quarter, revenue,
RANK() OVER (PARTITION BY quarter ORDER BY revenue DESC) AS rank
FROM customers
) AS ranked_customers
WHERE quarter = 'Q1' AND rank <= 3;
3. Given a transactions table, find all products that were sold but have not been restocked for 6 months.
SELECT product_id
FROM products
WHERE sales_date IS NOT NULL
AND restock_date <= DATEADD(MONTH, -6, GETDATE());
4. Write a query to determine the monthly growth rate of active users.
SELECT month, SUM(growth_rate) AS total_growth
FROM users
WHERE user_state = 'active'
GROUP BY month;
5. Find the 3rd lowest salary in an employee table using a dynamic parameter.
1st solution:
SELECT employee_id, salary
FROM employee
ORDER BY salary ASC
LIMIT 1 OFFSET 2;
Or Using RANK function:
SELECT employee_id, salary
FROM (
SELECT employee_id, salary,
RANK() OVER (ORDER BY salary ASC) AS rank
FROM employee
) AS ranked_salaries
WHERE rank = 3;
SELECT region, SUM(sales) AS total_sales
FROM sales
WHERE year = 2024
GROUP BY region;
2. How would you identify the top 3 customers with the highest revenue in a specific quarter?
1st solution:
SELECT customer_id, quarter, SUM(revenue) AS total_revenue
FROM customers
WHERE quarter = 'Q1'
GROUP BY customer_id, quarter
ORDER BY total_revenue DESC
LIMIT 3;
Or Using RANK function:
SELECT customer_id, quarter, revenue
FROM (
SELECT customer_id, quarter, revenue,
RANK() OVER (PARTITION BY quarter ORDER BY revenue DESC) AS rank
FROM customers
) AS ranked_customers
WHERE quarter = 'Q1' AND rank <= 3;
3. Given a transactions table, find all products that were sold but have not been restocked for 6 months.
SELECT product_id
FROM products
WHERE sales_date IS NOT NULL
AND restock_date <= DATEADD(MONTH, -6, GETDATE());
4. Write a query to determine the monthly growth rate of active users.
SELECT month, SUM(growth_rate) AS total_growth
FROM users
WHERE user_state = 'active'
GROUP BY month;
5. Find the 3rd lowest salary in an employee table using a dynamic parameter.
1st solution:
SELECT employee_id, salary
FROM employee
ORDER BY salary ASC
LIMIT 1 OFFSET 2;
Or Using RANK function:
SELECT employee_id, salary
FROM (
SELECT employee_id, salary,
RANK() OVER (ORDER BY salary ASC) AS rank
FROM employee
) AS ranked_salaries
WHERE rank = 3;
➤ 𝗪𝗶𝗻𝗱𝗼𝘄 𝗙𝘂𝗻𝗰𝘁𝗶𝗼𝗻𝘀
1. Calculate the moving average of sales for the past 3 months.
2. Assign a dense rank to employees based on their salary.
3. Retrieve the first and last order date for each customer.
4. Find the Nth highest salary for each department using window functions.
5. Determine the percentage of total sales contributed by each employee.
➤ 𝗖𝗼𝗺𝗺𝗼𝗻 𝗧𝗮𝗯𝗹𝗲 𝗘𝘅𝗽𝗿𝗲𝘀𝘀𝗶𝗼𝗻𝘀 (𝗖𝗧𝗘)
1. Use a CTE to split a full name into first and last names.
2. Write a CTE to find the longest consecutive streak of sales for an employee.
3. Generate Fibonacci numbers up to a given limit using a recursive CTE.
4. Use a CTE to identify duplicate records in a table.
5. Find the total sales for each category and filter categories with sales greater than a threshold using a CTE.
➤ 𝗝𝗼𝗶𝗻𝘀 (𝗜𝗻𝗻𝗲𝗿, 𝗢𝘂𝘁𝗲𝗿, 𝗖𝗿𝗼𝘀𝘀, 𝗦𝗲𝗹𝗳)
1. Retrieve a list of customers who have placed orders and those who have not placed orders (Full Outer Join).
2. Find employees working on multiple projects using a self join.
3. Match orders with customers and also display unmatched orders (Left Join).
4. Generate a product pair list but exclude pairs with identical products (Cross Join with condition).
5. Retrieve employees and their managers using a self join.
➤ 𝗦𝘂𝗯𝗾𝘂𝗲𝗿𝗶𝗲𝘀
1. Find customers whose total order amount is greater than the average order amount.
2. Retrieve employees who earn the lowest salary in their department.
3. Identify products that have been ordered more than 10 times using a subquery.
4. Find regions where the maximum sales are below a given threshold.
➤ 𝗔𝗴𝗴𝗿𝗲𝗴𝗮𝘁𝗲 𝗙𝘂𝗻𝗰𝘁𝗶𝗼𝗻𝘀
1. Calculate the median salary for each department.
2. Find the total sales for each month and rank them in descending order.
3. Count the number of distinct customers for each product.
4. Retrieve the top 5 regions by total sales.
5. Calculate the average order value for each customer.
➤ 𝗜𝗻𝗱𝗲𝘅𝗶𝗻𝗴 𝗮𝗻𝗱 𝗣𝗲𝗿𝗳𝗼𝗿𝗺𝗮𝗻𝗰𝗲
1. Write a query to find duplicate values in an indexed column.
2. Analyze the impact of adding a composite index on query performance.
3. Identify columns with high cardinality that could benefit from indexing
4. Compare query execution times before and after adding a clustered index.
5. Write a query that avoids the use of an index to test performance differences.
1. Calculate the moving average of sales for the past 3 months.
2. Assign a dense rank to employees based on their salary.
3. Retrieve the first and last order date for each customer.
4. Find the Nth highest salary for each department using window functions.
5. Determine the percentage of total sales contributed by each employee.
➤ 𝗖𝗼𝗺𝗺𝗼𝗻 𝗧𝗮𝗯𝗹𝗲 𝗘𝘅𝗽𝗿𝗲𝘀𝘀𝗶𝗼𝗻𝘀 (𝗖𝗧𝗘)
1. Use a CTE to split a full name into first and last names.
2. Write a CTE to find the longest consecutive streak of sales for an employee.
3. Generate Fibonacci numbers up to a given limit using a recursive CTE.
4. Use a CTE to identify duplicate records in a table.
5. Find the total sales for each category and filter categories with sales greater than a threshold using a CTE.
➤ 𝗝𝗼𝗶𝗻𝘀 (𝗜𝗻𝗻𝗲𝗿, 𝗢𝘂𝘁𝗲𝗿, 𝗖𝗿𝗼𝘀𝘀, 𝗦𝗲𝗹𝗳)
1. Retrieve a list of customers who have placed orders and those who have not placed orders (Full Outer Join).
2. Find employees working on multiple projects using a self join.
3. Match orders with customers and also display unmatched orders (Left Join).
4. Generate a product pair list but exclude pairs with identical products (Cross Join with condition).
5. Retrieve employees and their managers using a self join.
➤ 𝗦𝘂𝗯𝗾𝘂𝗲𝗿𝗶𝗲𝘀
1. Find customers whose total order amount is greater than the average order amount.
2. Retrieve employees who earn the lowest salary in their department.
3. Identify products that have been ordered more than 10 times using a subquery.
4. Find regions where the maximum sales are below a given threshold.
➤ 𝗔𝗴𝗴𝗿𝗲𝗴𝗮𝘁𝗲 𝗙𝘂𝗻𝗰𝘁𝗶𝗼𝗻𝘀
1. Calculate the median salary for each department.
2. Find the total sales for each month and rank them in descending order.
3. Count the number of distinct customers for each product.
4. Retrieve the top 5 regions by total sales.
5. Calculate the average order value for each customer.
➤ 𝗜𝗻𝗱𝗲𝘅𝗶𝗻𝗴 𝗮𝗻𝗱 𝗣𝗲𝗿𝗳𝗼𝗿𝗺𝗮𝗻𝗰𝗲
1. Write a query to find duplicate values in an indexed column.
2. Analyze the impact of adding a composite index on query performance.
3. Identify columns with high cardinality that could benefit from indexing
4. Compare query execution times before and after adding a clustered index.
5. Write a query that avoids the use of an index to test performance differences.
1. How can you find the second-lowest salary in a table without using MIN?
2. Write a query to find employees who report to the same manager and have the exact same salary.
3. Identify rows where a specific column has missing or null values and replace them with the column’s average.
4. Write a query to find the employees whose salaries rank in the top 5% without using percentile functions.
5. Calculate a running total of sales in a table.
6. Find employees who have worked in more than one department.
7. Write a query to compare each employee’s salary with the average salary of their department.
8. Identify departments that currently have no employees.
9. Write a query to display the total sales for each month in a pivot-like format.
10. Determine employees whose salaries increased by more than 20% compared to their last salary.
These questions are designed to enhance your understanding of SQL and help you tackle real-world scenarios effectively.
Answers:
Here are the solutions to these questions:
1. Find the second-lowest salary without MIN
SELECT salary FROM employees
WHERE salary > (SELECT DISTINCT salary FROM employees ORDER BY salary ASC LIMIT 1)
ORDER BY salary ASC LIMIT 1;
2. Employees with the same salary under the same manager
SELECT e1.* FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.manager_id
WHERE e1.salary = e2.salary AND e1.employee_id <> e2.employee_id;
3. Replace NULLs with the column’s average
UPDATE table_name
SET column_name = (SELECT AVG(column_name) FROM table_name WHERE column_name IS NOT NULL)
WHERE column_name IS NULL;
4. Top 5% salaries without percentile functions
SELECT * FROM employees
WHERE salary >= (SELECT salary FROM employees ORDER BY salary DESC LIMIT (SELECT COUNT(*) FROM employees) * 5 / 100);
5. Calculate running total of sales
SELECT id, sales, SUM(sales) OVER (ORDER BY id) AS running_total
FROM sales_table;
6. Employees in multiple departments
SELECT employee_id FROM employee_department
GROUP BY employee_id
HAVING COUNT(DISTINCT department_id) > 1;
7. Compare salary with department average
SELECT employee_id, salary,
AVG(salary) OVER (PARTITION BY department_id) AS avg_dept_salary
FROM employees;
8. Departments with no employees
SELECT department_id FROM departments
WHERE department_id NOT IN (SELECT DISTINCT department_id FROM employees);
9. Pivot-like format for total sales
SELECT
SUM(CASE WHEN month = 'January' THEN sales END) AS January,
SUM(CASE WHEN month = 'February' THEN sales END) AS February,
SUM(CASE WHEN month = 'March' THEN sales END) AS March
FROM sales_table;
10. Employees with >20% salary increase
SELECT e.employee_id FROM employees e
JOIN salary_history s ON e.employee_id = s.employee_id
WHERE e.salary > 1.2 * s.previous_salary;
2. Write a query to find employees who report to the same manager and have the exact same salary.
3. Identify rows where a specific column has missing or null values and replace them with the column’s average.
4. Write a query to find the employees whose salaries rank in the top 5% without using percentile functions.
5. Calculate a running total of sales in a table.
6. Find employees who have worked in more than one department.
7. Write a query to compare each employee’s salary with the average salary of their department.
8. Identify departments that currently have no employees.
9. Write a query to display the total sales for each month in a pivot-like format.
10. Determine employees whose salaries increased by more than 20% compared to their last salary.
These questions are designed to enhance your understanding of SQL and help you tackle real-world scenarios effectively.
Answers:
Here are the solutions to these questions:
1. Find the second-lowest salary without MIN
SELECT salary FROM employees
WHERE salary > (SELECT DISTINCT salary FROM employees ORDER BY salary ASC LIMIT 1)
ORDER BY salary ASC LIMIT 1;
2. Employees with the same salary under the same manager
SELECT e1.* FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.manager_id
WHERE e1.salary = e2.salary AND e1.employee_id <> e2.employee_id;
3. Replace NULLs with the column’s average
UPDATE table_name
SET column_name = (SELECT AVG(column_name) FROM table_name WHERE column_name IS NOT NULL)
WHERE column_name IS NULL;
4. Top 5% salaries without percentile functions
SELECT * FROM employees
WHERE salary >= (SELECT salary FROM employees ORDER BY salary DESC LIMIT (SELECT COUNT(*) FROM employees) * 5 / 100);
5. Calculate running total of sales
SELECT id, sales, SUM(sales) OVER (ORDER BY id) AS running_total
FROM sales_table;
6. Employees in multiple departments
SELECT employee_id FROM employee_department
GROUP BY employee_id
HAVING COUNT(DISTINCT department_id) > 1;
7. Compare salary with department average
SELECT employee_id, salary,
AVG(salary) OVER (PARTITION BY department_id) AS avg_dept_salary
FROM employees;
8. Departments with no employees
SELECT department_id FROM departments
WHERE department_id NOT IN (SELECT DISTINCT department_id FROM employees);
9. Pivot-like format for total sales
SELECT
SUM(CASE WHEN month = 'January' THEN sales END) AS January,
SUM(CASE WHEN month = 'February' THEN sales END) AS February,
SUM(CASE WHEN month = 'March' THEN sales END) AS March
FROM sales_table;
10. Employees with >20% salary increase
SELECT e.employee_id FROM employees e
JOIN salary_history s ON e.employee_id = s.employee_id
WHERE e.salary > 1.2 * s.previous_salary;
1️⃣ Find the second-highest salary in a department
• Efficiently solve using ROW_NUMBER() or DENSE_RANK().
2️⃣ Calculate daily transactions per user
• Use GROUP BY and COUNT() for aggregation.
3️⃣ Projects with the highest budget-per-employee ratio
• Showcase your expertise in JOINs and advanced aggregations!
• Efficiently solve using ROW_NUMBER() or DENSE_RANK().
2️⃣ Calculate daily transactions per user
• Use GROUP BY and COUNT() for aggregation.
3️⃣ Projects with the highest budget-per-employee ratio
• Showcase your expertise in JOINs and advanced aggregations!
1️⃣ Employee Working Streak
You are given a table EmployeeLogs with columns EmployeeID, LoginTime, LogoutTime, and Date. Write a query to calculate the longest continuous working streak (consecutive days without missing a login) for each employee.
2️⃣ Top Products by Region
A table SalesData contains columns TransactionID, Region, ProductID, SaleDate, and Revenue. Write a query to identify the top 3 products with the highest cumulative revenue in each region over the last fiscal year.
You are given a table EmployeeLogs with columns EmployeeID, LoginTime, LogoutTime, and Date. Write a query to calculate the longest continuous working streak (consecutive days without missing a login) for each employee.
2️⃣ Top Products by Region
A table SalesData contains columns TransactionID, Region, ProductID, SaleDate, and Revenue. Write a query to identify the top 3 products with the highest cumulative revenue in each region over the last fiscal year.
1. Find the second-highest salary in a table without using LIMIT or TOP.
2. Write a SQL query to find all employees who earn more than their managers.
3. Find the duplicate rows in a table without using GROUP BY.
4. Write a SQL query to find the top 10% of earners in a table.
5. Find the cumulative sum of a column in a table.
6. Write a SQL query to find all employees who have never taken a leave.
7. Find the difference between the current row and the next row in a table.
8. Write a SQL query to find all departments with more than one employee.
9. Find the maximum value of a column for each group without using GROUP BY.
10. Write a SQL query to find all employees who have taken more than 3 leaves in a month.
These questions are designed to test your SQL skills, including your ability to write efficient queries, think creatively, and solve complex problems.
Here are the answers to these questions:
1. SELECT MAX(salary) FROM table WHERE salary NOT IN (SELECT MAX(salary) FROM table)
2. SELECT e1.* FROM employees e1 JOIN employees e2 ON e1.manager_id = (link unavailable) WHERE e1.salary > e2.salary
3. SELECT * FROM table WHERE rowid IN (SELECT rowid FROM table GROUP BY column HAVING COUNT(*) > 1)
4. SELECT * FROM table WHERE salary > (SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY salary) FROM table)
5. SELECT column, SUM(column) OVER (ORDER BY rowid) FROM table
6. SELECT * FROM employees WHERE id NOT IN (SELECT employee_id FROM leaves)
7. SELECT *, column - LEAD(column) OVER (ORDER BY rowid) FROM table
8. SELECT department FROM employees GROUP BY department HAVING COUNT(*) > 1
9. SELECT MAX(column) FROM table WHERE column NOT IN (SELECT MAX(column) FROM table GROUP BY group_column)
2. Write a SQL query to find all employees who earn more than their managers.
3. Find the duplicate rows in a table without using GROUP BY.
4. Write a SQL query to find the top 10% of earners in a table.
5. Find the cumulative sum of a column in a table.
6. Write a SQL query to find all employees who have never taken a leave.
7. Find the difference between the current row and the next row in a table.
8. Write a SQL query to find all departments with more than one employee.
9. Find the maximum value of a column for each group without using GROUP BY.
10. Write a SQL query to find all employees who have taken more than 3 leaves in a month.
These questions are designed to test your SQL skills, including your ability to write efficient queries, think creatively, and solve complex problems.
Here are the answers to these questions:
1. SELECT MAX(salary) FROM table WHERE salary NOT IN (SELECT MAX(salary) FROM table)
2. SELECT e1.* FROM employees e1 JOIN employees e2 ON e1.manager_id = (link unavailable) WHERE e1.salary > e2.salary
3. SELECT * FROM table WHERE rowid IN (SELECT rowid FROM table GROUP BY column HAVING COUNT(*) > 1)
4. SELECT * FROM table WHERE salary > (SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY salary) FROM table)
5. SELECT column, SUM(column) OVER (ORDER BY rowid) FROM table
6. SELECT * FROM employees WHERE id NOT IN (SELECT employee_id FROM leaves)
7. SELECT *, column - LEAD(column) OVER (ORDER BY rowid) FROM table
8. SELECT department FROM employees GROUP BY department HAVING COUNT(*) > 1
9. SELECT MAX(column) FROM table WHERE column NOT IN (SELECT MAX(column) FROM table GROUP BY group_column)
1, Write a query to find the second-highest salary of an employee?
2, Write a query to list customers who spent more than ₹1000 on their orders in the last month?
3, Write a query to find customers who spent the most on their orders in the last month?
4, Write a query to join two dataframes with different schemas, where the left table has more rows?
3, Write a query to find customers who spent the most on their orders in the last month?
4, Write a query to join two dataframes with different schemas, where the left table has more rows?
1- Top N products by sales, Top N products within each category, Ton N employees by salaries etc.
2- Year-over-year growth, YOY growth for each category, Products with higher sales than the previous month etc.
3- Running sales over months, rolling N months sales, within each category etc.
4- Pivot rows to columns, eg: year-wise sales for each category in separate columns etc.
5- Number of records after different kinds of joins.
2- Year-over-year growth, YOY growth for each category, Products with higher sales than the previous month etc.
3- Running sales over months, rolling N months sales, within each category etc.
4- Pivot rows to columns, eg: year-wise sales for each category in separate columns etc.
5- Number of records after different kinds of joins.
6- Write a query to calculate a running total with partitions based on specific conditions.
Source: Check out my LinkedIn profile... /RakeshMen
Comments
Post a Comment