Introduction
In the world of data management and analysis, SQL (Structured Query Language) stands as an indispensable tool. Whether you’re a seasoned database administrator, a software engineer, or someone aiming to enter the realm of data science, mastering SQL is often a prerequisite for success. SQL interviews, especially for technical positions, frequently include scenario-based questions. These questions not only test your ability to write complex queries but also assess your problem-solving skills within the context of database management.
This article, “Scenario-Based SQL Interview Questions and Answers,” is your comprehensive guide to acing SQL interviews by delving into various real-world scenarios. We will provide you with a collection of diverse, hands-on SQL scenarios, each followed by a detailed answer. These scenarios cover a wide array of practical applications, from e-commerce inventory management to employee performance evaluation, offering valuable insights into the skills and knowledge needed for success in SQL-related roles.
So, whether you’re a job seeker preparing for a SQL interview or someone simply looking to enhance your SQL skills, this article is your gateway to mastering the art of solving complex, scenario-based SQL problems.
Scenario Based Interview Questions and Answers
Scenario 1: Retrieving Data
Question: You have a database with two tables: `employees` and `departments`. The `employees` table has columns for `employee_id`, `employee_name`, and `department_id`. The `departments` table has columns for `department_id` and `department_name`. You want to retrieve the names of employees and their corresponding department names.
Answer:
SELECT e.employee_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;Scenario 2: Filtering Data
Question: In the previous scenario, you need to retrieve only the employees from the “Sales” department.
Answer:
SELECT e.employee_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = 'Sales';Scenario 3: Aggregation
Question: You need to find the average salary of all employees in the “Marketing” department.
Answer:
SELECT AVG(salary) AS average_salary
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = 'Marketing';Scenario 4: Updating Data
Question: You’ve been asked to update the salary of an employee with the ID 101 to $60,000.
Answer:
UPDATE employees
SET salary = 60000
WHERE employee_id = 101;Scenario 5: Inserting Data
Question: You need to add a new department called “Research & Development” with a department ID of 4.
Answer:
INSERT INTO departments (department_id, department_name)
VALUES (4, 'Research & Development');Scenario 6: Deleting Data
Question: An employee with the ID 203 has left the company and should be removed from the database.
Answer:
DELETE FROM employees
WHERE employee_id = 203;Scenario 7: Handling NULL Values
Question: You need to find the employees who haven’t been assigned to any department. Some employee records have NULL values in the `department_id` column.
Answer:
SELECT employee_name
FROM employees
WHERE department_id IS NULL;Scenario 8: Subqueries
Question: Find the names of employees who earn more than the average salary in their respective departments.
Answer:
SELECT e.employee_name
FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);Scenario 9: Joining Three Tables
Question: You have a third table, `salaries`, with columns `employee_id` and `salary`. Retrieve the names of employees and their salaries, including those with no salary records.
Answer:
SELECT e.employee_name, s.salary
FROM employees e
LEFT JOIN salaries s ON e.employee_id = s.employee_id;Scenario 10: Data Migration
Question: You need to migrate all employees from the “Finance” department to the “Accounting” department, preserving their employee IDs.
Answer:
UPDATE employees
SET department_id = (SELECT department_id FROM departments WHERE department_name = 'Accounting')
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Finance');Scenario 11: Data Validation
Question: You are tasked with ensuring that all email addresses in the `customers` table are in a valid format (e.g., “[email protected]”). How would you verify and correct invalid email addresses?
Answer:
To validate and correct invalid email addresses, you can use an UPDATE statement with the SQL function `REGEXP_REPLACE`. Here’s an example:
UPDATE customers
SET email = REGEXP_REPLACE(email, '[^a-zA-Z0-9@._-]', '') -- Remove invalid characters
WHERE NOT REGEXP_LIKE(email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$');This query will remove any invalid characters and update the email addresses to a valid format.
Scenario 12: Handling Data Duplicates
Question: In your database, you’ve noticed there are duplicate entries in the `orders` table. How would you identify and remove these duplicates while preserving one record?
Answer:
To remove duplicates from the `orders` table, you can use a common table expression (CTE) with the ROW_NUMBER() function. Here’s an example:
WITH CTE AS (
SELECT ,
ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY order_date) AS row_num
FROM orders
)
DELETE FROM CTE WHERE row_num > 1;This query assigns a row number to each order within the same `order_id` group, and then it deletes all but the first occurrence.
Scenario 13: Complex Joins
Question: You need to retrieve a list of customers and their most recent order details, including the order date and total amount. How would you structure the SQL query?
Answer:
You can achieve this using a subquery within a JOIN. Here’s an example:
SELECT c.customer_name, o.order_date, o.total_amount
FROM customers c
JOIN (
SELECT customer_id, MAX(order_date) AS max_order_date
FROM orders
GROUP BY customer_id
) recent_orders
ON c.customer_id = recent_orders.customer_id
JOIN orders o
ON recent_orders.customer_id = o.customer_id
AND recent_orders.max_order_date = o.order_date;This query first finds the most recent order date for each customer in a subquery, and then joins the customers and orders tables to retrieve the desired information.
Scenario 14: Calculating Running Totals
Question: You have a table called `sales` with columns `sales_date` and `revenue`. How would you calculate a running total of revenue over time?
Answer:
To calculate a running total, you can use a window function such as SUM() OVER(ORDER BY). Here’s an example:
SELECT sales_date, revenue, SUM(revenue) OVER (ORDER BY sales_date) AS running_total
FROM sales;This query will provide a result set with the sales date, revenue for each date, and a running total that accumulates the revenue over time.
Scenario 15: Data Export
Question: You’ve been tasked with exporting the contents of a table to a CSV file for external reporting. How would you export the data from SQL?
Answer:
Most database management systems provide a way to export data to a CSV file using SQL. Here’s an example in PostgreSQL:
COPY table_name TO '/path/to/exported/file.csv' WITH CSV HEADER;This query exports the contents of `table_name` to a CSV file with a header row.
Scenario 16: Handling NULL Values
Question: You have a table called `products` with a column `price` that contains NULL values. You want to update all NULL prices to a default value of 0.00. How would you do this?
Answer:
You can use an UPDATE statement with the COALESCE function to replace NULL values with a default value. Here’s an example:
UPDATE products
SET price = COALESCE(price, 0.00);This query updates the `price` column to 0.00 where it was previously NULL.
Scenario 17: Finding the Nth Highest Salary
Question: You need to find the third highest salary from a table called `employees`. How would you write an SQL query to achieve this?
Answer:
You can use the LIMIT and OFFSET clauses in combination with ORDER BY to find the third highest salary. Here’s an example:
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2;This query orders salaries in descending order and skips the first two rows, giving you the third highest salary.
Scenario 18: Data Audit Trail
Question: You’ve been tasked with creating an audit trail for changes to a table called `orders`. You need to track changes to the `status` column. How would you design a database schema for this audit trail?
Answer:
You can create a new table, e.g., `order_status_changes`, to store the audit trail. It could have columns like `order_id`, `old_status`, `new_status`, `change_date`, and an audit ID. Whenever the `status` column in the `orders` table is updated, you insert a record into the `order_status_changes` table with the relevant details.
Scenario 19: Data Backup
Question: You need to create a backup of a database table called `customer_data` to ensure data integrity during updates. How would you back up the data?
Answer:
One way to create a backup is to use a SELECT INTO statement to copy the data into a new table. Here’s an example:
SELECT
INTO customer_data_backup
FROM customer_data;This query copies the data from `customer_data` into a new table called `customer_data_backup`.
Scenario 20: Managing Hierarchical Data
Question: You have a table called `categories` that stores hierarchical data with columns `category_id` and `parent_category_id`. How would you retrieve all the subcategories of a specific category?
Answer:
You can use a recursive common table expression (CTE) to retrieve all subcategories of a specific category. Here’s an example:
WITH RECURSIVE Subcategories AS (
SELECT category_id, category_name, parent_category_id
FROM categories
WHERE category_id = :your_category_id -- Replace with the specific category ID
UNION ALL
SELECT c.category_id, c.category_name, c.parent_category_id
FROM categories c
JOIN Subcategories s ON c.parent_category_id = s.category_id
)
SELECT category_id, category_name
FROM Subcategories;This query recursively retrieves all subcategories of the specified category.
Scenario 21: E-commerce Orders
Question: You have a database for an e-commerce website with tables for `customers`, `orders`, and `order_items`. How would you retrieve the total revenue generated by a specific customer?
Answer:
You can retrieve the total revenue generated by a specific customer by joining the `customers`, `orders`, and `order_items` tables and applying a filter for the customer of interest. Here’s an example:
SELECT c.customer_name, SUM(oi.quantity oi.unit_price) AS total_revenue
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE c.customer_id = :customer_id; -- Replace with the specific customer IDThis query retrieves the total revenue for a specific customer by summing the product of item quantity and unit price for their orders.
Scenario 22: Inventory Management
Question: You are managing an inventory database with a `products` table that contains product information, including the quantity in stock. How would you update the quantity in stock when a new shipment arrives?
Answer:
To update the quantity in stock when a new shipment arrives, you can use an SQL UPDATE statement. Here’s an example:
UPDATE products
SET quantity_in_stock = quantity_in_stock + :new_shipment_quantity -- Replace with the quantity received
WHERE product_id = :product_id; -- Replace with the specific product IDThis query increments the quantity in stock for a specific product after a new shipment arrives.
Scenario 23: Employee Hierarchy
Question: You have an `employees` table with columns for `employee_id` and `manager_id`. How would you retrieve a list of employees and their respective managers?
Answer:
You can retrieve a list of employees and their managers by joining the `employees` table with itself to establish a hierarchy. Here’s an example:
SELECT e.employee_name, m.employee_name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;This query retrieves a list of employees and their respective managers, using a self-join on the `employees` table.
Scenario 24: Product Recommendations
Question: You want to create a product recommendation system for an e-commerce website. How would you write an SQL query to recommend products to a customer based on their previous purchases?
Answer:
To recommend products to a customer based on their previous purchases, you can use a subquery or a common table expression (CTE) to find products purchased by the customer and then find products frequently purchased with those items by other customers. Here’s an example:
WITH CustomerPurchases AS (
SELECT product_id
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.customer_id = :customer_id -- Replace with the specific customer ID
)
SELECT oi.product_id, COUNT(oi.product_id) AS frequency
FROM order_items oi
WHERE oi.product_id NOT IN (SELECT product_id FROM CustomerPurchases)
AND oi.order_id IN (SELECT order_id FROM CustomerPurchases)
GROUP BY oi.product_id
ORDER BY frequency DESC
LIMIT 5; -- Recommend the top 5 productsThis query finds products that other customers frequently purchase with items bought by the specific customer, providing product recommendations.
Scenario 25: Data Archiving
Question: You have a large table with historical data, and you want to archive records older than five years to improve database performance. How would you archive the data?
Answer:
To archive data, you can create a new table for archived records and move old records to it while keeping the recent data in the original table. Here’s an example:
-- Create an archived_records table with the same structure as the original table
CREATE TABLE archived_records AS
SELECT
FROM original_table
WHERE date < CURRENT_DATE - INTERVAL '5 years';
-- Delete the archived records from the original table
DELETE FROM original_table
WHERE date < CURRENT_DATE - INTERVAL '5 years';This process creates an archive of old records while removing them from the original table to improve database performance.
Scenario 26: Online Bookstore Inventory
Question: You manage an online bookstore database with tables for books (`books`), orders (`orders`), and order items (`order_items`). How would you retrieve a list of books that have never been ordered?
Answer:
You can retrieve a list of books that have never been ordered by using a LEFT JOIN and checking for NULL values in the `order_items` table. Here’s an example:
SELECT b.book_id, b.title
FROM books b
LEFT JOIN order_items oi ON b.book_id = oi.book_id
WHERE oi.order_item_id IS NULL;This query retrieves books that have no corresponding entries in the `order_items` table.
Scenario 27: Social Media Posts and Comments
Question: You have a database for a social media platform with tables for posts (`posts`) and comments (`comments`). How would you retrieve a list of posts and the total number of comments on each post?
Answer:
You can retrieve a list of posts and the total number of comments on each post by using a COUNT() and a GROUP BY clause. Here’s an example:
SELECT p.post_id, p.post_content, COUNT(c.comment_id) AS total_comments
FROM posts p
LEFT JOIN comments c ON p.post_id = c.post_id
GROUP BY p.post_id, p.post_content;This query counts the number of comments for each post using a LEFT JOIN and GROUP BY.
Scenario 28: Managing Employee Shifts
Question: You have a table (`employee_shifts`) that stores employee shift information with columns for `employee_id`, `shift_date`, and `shift_type`. How would you find employees who have worked on all weekdays (Monday to Friday)?
Answer:
You can find employees who have worked on all weekdays using a COUNT() and HAVING clause. Here’s an example:
SELECT employee_id
FROM employee_shifts
WHERE DAYOFWEEK(shift_date) BETWEEN 2 AND 6 -- 2 represents Monday, 6 represents Friday
GROUP BY employee_id
HAVING COUNT(DISTINCT DAYOFWEEK(shift_date)) = 5; -- 5 for all weekdaysThis query checks for employees who have worked on all weekdays by counting the distinct weekdays and filtering with HAVING.
Scenario 29: Analyzing Sales Data
Question: You have a database for a retail store with tables for sales (`sales`) and products (`products`). How would you retrieve a list of the top 10 best-selling products along with their revenue?
Answer:
You can retrieve the top 10 best-selling products and their revenue by joining the `sales` and `products` tables, grouping, and ordering by revenue. Here’s an example:
SELECT p.product_id, p.product_name, SUM(s.quantity p.price) AS revenue
FROM products p
JOIN sales s ON p.product_id = s.product_id
GROUP BY p.product_id, p.product_name
ORDER BY revenue DESC
LIMIT 10;This query calculates the revenue for each product, orders the results by revenue in descending order, and limits the output to the top 10 products.
Scenario 30: Geographic Data
Question: You have a table (`locations`) that stores geographic data with columns for latitude (`lat`) and longitude (`lon`). How would you retrieve locations that are within a specified radius from a given point?
Answer:
To retrieve locations within a specified radius, you can use the Haversine formula, which calculates distances on the Earth’s surface. Here’s an example:
SELECT
FROM locations
WHERE (
6371 ACOS(
COS(RADIANS(:given_lat)) COS(RADIANS(lat))
COS(RADIANS(lon) - RADIANS(:given_lon)) +
SIN(RADIANS(:given_lat)) SIN(RADIANS(lat))
)
) <= :radius_km; -- Replace with the specified radius in kilometersThis query retrieves locations within the specified radius from a given point (latitude and longitude).
Scenario 31: Blog Post and Comments
Question: You’re managing a blog database with tables for blog posts (`blog_posts`) and comments (`comments`). How would you retrieve a list of the five most recent blog posts along with the number of comments on each post?
Answer:
You can retrieve the five most recent blog posts and count the comments on each post by joining the `blog_posts` and `comments` tables, grouping by blog post, and ordering by the post date. Here’s an example:
SELECT bp.post_id, bp.title, COUNT(c.comment_id) AS comment_count
FROM blog_posts bp
LEFT JOIN comments c ON bp.post_id = c.post_id
GROUP BY bp.post_id, bp.title
ORDER BY bp.post_date DESC
LIMIT 5;This query retrieves the most recent blog posts, counts the number of comments for each, and limits the results to the top five.
Scenario 32: Inventory Management
Question: You’re managing an inventory database with a table for products (`products`). How would you retrieve a list of products that are out of stock (quantity in stock is 0)?
Answer:
You can retrieve a list of products that are out of stock by using a simple SELECT statement with a WHERE clause. Here’s an example:
SELECT product_id, product_name
FROM products
WHERE quantity_in_stock = 0;This query retrieves products with a quantity in stock of 0.
Scenario 33: Employee Hierarchies
Question: You have an `employees` table with columns for `employee_id`, `employee_name`, and `manager_id`. How would you retrieve a hierarchical list of employees and their managers?
Answer:
You can retrieve a hierarchical list of employees and their managers by using a recursive common table expression (CTE). Here’s an example:
WITH EmployeeHierarchy AS (
SELECT employee_id, employee_name, manager_id
FROM employees
WHERE manager_id IS NULL -- Start with top-level managers
UNION ALL
SELECT e.employee_id, e.employee_name, e.manager_id
FROM employees e
JOIN EmployeeHierarchy h ON e.manager_id = h.employee_id
)
SELECT employee_id, employee_name, manager_id
FROM EmployeeHierarchy;This query retrieves all employees and their managers, including the top-level managers.
Scenario 34: E-commerce Sales and Discounts
Question: You manage a database for an e-commerce site with tables for sales (`sales`) and products (`products`). How would you retrieve a list of products that have been sold with a discount greater than 10%?
Answer:
You can retrieve a list of products sold with a discount greater than 10% by joining the `sales` and `products` tables and applying a filter for the discount percentage. Here’s an example:
SELECT p.product_id, p.product_name, s.discount
FROM products p
JOIN sales s ON p.product_id = s.product_id
WHERE s.discount > 0.10; -- 10% discount or greaterThis query retrieves products with a discount percentage greater than 10%.
Scenario 35: Order Tracking
Question: You have tables for orders (`orders`) and order statuses (`order_statuses`). How would you retrieve a list of orders and their current status (latest status based on timestamp)?
Answer:
You can retrieve a list of orders and their current status by joining the `orders` and `order_statuses` tables and using a subquery to find the latest status based on timestamps. Here’s an example:
SELECT o.order_id, o.order_date, latest_status.status
FROM orders o
JOIN (
SELECT os1.order_id, os1.status
FROM order_statuses os1
WHERE os1.timestamp = (
SELECT MAX(timestamp)
FROM order_statuses os2
WHERE os2.order_id = os1.order_id
)
) latest_status ON o.order_id = latest_status.order_id;This query retrieves the orders and their current status based on the latest timestamp.
Scenario 36: Product Reviews
Question: You have a database with tables for products (`products`) and product reviews (`product_reviews`). How would you retrieve a list of products that have an average review rating greater than 4.0?
Answer:
You can retrieve a list of products with an average review rating greater than 4.0 by joining the `products` and `product_reviews` tables, grouping by product, and using the HAVING clause. Here’s an example:
SELECT p.product_id, p.product_name, AVG(pr.rating) AS average_rating
FROM products p
JOIN product_reviews pr ON p.product_id = pr.product_id
GROUP BY p.product_id, p.product_name
HAVING AVG(pr.rating) > 4.0;This query calculates the average rating for each product and filters for those with an average rating greater than 4.0.
Scenario 37: User Activity Tracking
Question: You manage a database that logs user activity with tables for users (`users`) and user activity (`user_activity`). How would you retrieve a list of users who have not been active in the last 30 days?
Answer:
You can retrieve a list of users who have not been active in the last 30 days by joining the `users` and `user_activity` tables and using a WHERE clause to filter for inactive users. Here’s an example:
SELECT u.user_id, u.username
FROM users u
LEFT JOIN user_activity ua ON u.user_id = ua.user_id
WHERE ua.activity_date < DATE_SUB(CURDATE(), INTERVAL 30 DAY)
OR ua.activity_date IS NULL;This query selects users with no recent activity (activity date older than 30 days or no activity records at all).
Scenario 38: Sales Territories
Question: You have a table for sales territories (`sales_territories`) with hierarchical data and columns for `territory_id`, `territory_name`, and `parent_territory_id`. How would you retrieve a list of territories and their hierarchical structure?
Answer:
You can retrieve a list of territories and their hierarchical structure by using a recursive common table expression (CTE). Here’s an example:
WITH RecursiveTerritories AS (
SELECT territory_id, territory_name, parent_territory_id
FROM sales_territories
WHERE parent_territory_id IS NULL
UNION ALL
SELECT st.territory_id, st.territory_name, st.parent_territory_id
FROM sales_territories st
JOIN RecursiveTerritories rt ON st.parent_territory_id = rt.territory_id
)
SELECT territory_id, territory_name, parent_territory_id
FROM RecursiveTerritories;This query retrieves the territories and their hierarchical structure.
Scenario 39: Online Courses and Enrollments
Question: You’re managing a database for an online education platform with tables for courses (`courses`) and course enrollments (`enrollments`). How would you retrieve a list of courses that have no enrollments?
Answer:
You can retrieve a list of courses with no enrollments by using a LEFT JOIN and checking for NULL values in the `enrollments` table. Here’s an example:
SELECT c.course_id, c.course_name
FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id
WHERE e.enrollment_id IS NULL;This query retrieves courses with no corresponding enrollment records.
Scenario 40: Geographic Data Analysis
Question: You have a table (`cities`) that stores geographic data with columns for city names (`city_name`) and population. How would you retrieve the top 10 most populous cities?
Answer:
You can retrieve the top 10 most populous cities by ordering the data by population in descending order and using the LIMIT clause. Here’s an example:
SELECT city_name, population
FROM cities
ORDER BY population DESC
LIMIT 10;This query retrieves the top 10 most populous cities.
Scenario 41: Employee Time Tracking
Question: You’re managing a database for tracking employee work hours with tables for employees (`employees`) and work hours (`work_hours`). How would you retrieve a list of employees who have worked overtime (more than 40 hours in a week)?
Answer:
You can retrieve a list of employees who have worked overtime by joining the `employees` and `work_hours` tables, grouping by employee and week, and using the HAVING clause to filter for overtime work. Here’s an example:
SELECT e.employee_id, e.employee_name
FROM employees e
JOIN work_hours wh ON e.employee_id = wh.employee_id
GROUP BY e.employee_id, e.employee_name, YEAR(wh.work_date), WEEK(wh.work_date)
HAVING SUM(wh.hours_worked) > 40;This query calculates the total hours worked by each employee per week and filters for those who worked more than 40 hours in a week.
Scenario 42: Product Categories and Subcategories
Question: You have a table for product categories (`categories`) with a hierarchical structure. How would you retrieve a list of all categories and their respective subcategories?
Answer:
You can retrieve a list of categories and their subcategories by using a recursive common table expression (CTE). Here’s an example:
WITH RecursiveCategories AS (
SELECT category_id, category_name, parent_category_id
FROM categories
WHERE parent_category_id IS NULL
UNION ALL
SELECT c.category_id, c.category_name, c.parent_category_id
FROM categories c
JOIN RecursiveCategories rc ON c.parent_category_id = rc.category_id
)
SELECT category_id, category_name, parent_category_id
FROM RecursiveCategories;This query retrieves the categories and their hierarchical structure.
Scenario 43: Sales and Discounts
Question: You manage a database for a retail store with tables for sales (`sales`) and products (`products`). How would you retrieve a list of products that have been sold at a discount greater than $10?
Answer:
You can retrieve a list of products sold with a discount greater than $10 by joining the `sales` and `products` tables and applying a filter for the discount amount. Here’s an example:
SELECT p.product_id, p.product_name, s.discount
FROM products p
JOIN sales s ON p.product_id = s.product_id
WHERE s.discount > 10.00; -- $10 discount or greaterThis query retrieves products with a discount greater than $10.
Scenario 44: Event Registration
Question: You’re managing a database for event registrations with tables for events (`events`) and registrations (`registrations`). How would you retrieve a list of events that have reached their maximum registration limit?
Answer:
You can retrieve a list of events that have reached their maximum registration limit by joining the `events` and `registrations` tables, grouping by event, and using the HAVING clause to filter for events with the maximum number of registrations. Here’s an example:
SELECT e.event_id, e.event_name
FROM events e
LEFT JOIN registrations r ON e.event_id = r.event_id
GROUP BY e.event_id, e.event_name
HAVING COUNT(r.registration_id) >= e.max_registrations;This query checks for events with the maximum number of registrations or more.
Scenario 45: Product Sales Trends
Question: You have tables for products (`products`) and daily sales data (`daily_sales`). How would you retrieve a list of products with declining sales trends over the last 30 days?
Answer:
You can retrieve a list of products with declining sales trends by joining the `products` and `daily_sales` tables, calculating the change in sales over the last 30 days, and filtering for products with a negative trend. Here’s an example:
SELECT p.product_id, p.product_name
FROM products p
JOIN (
SELECT product_id,
(SUM(sales_amount) - LAG(SUM(sales_amount), 30) OVER (PARTITION BY product_id ORDER BY sale_date)) AS sales_change
FROM daily_sales
GROUP BY product_id, sale_date
) s ON p.product_id = s.product_id
WHERE sales_change < 0;This query calculates the change in sales over the last 30 days and retrieves products with declining sales trends.
Scenario 46: Order Fulfillment
Question: You’re managing a database for an e-commerce business with tables for customer orders (`orders`) and order items (`order_items`). How would you retrieve a list of orders with all items in stock?
Answer:
You can retrieve a list of orders with all items in stock by joining the `orders` and `order_items` tables, grouping by order, and using the HAVING clause to filter for orders where the count of items in stock matches the count of order items. Here’s an example:
SELECT o.order_id, o.order_date
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE oi.product_id IN (SELECT product_id FROM products WHERE quantity_in_stock > 0)
GROUP BY o.order_id, o.order_date
HAVING COUNT(oi.product_id) = COUNT(CASE WHEN oi.product_id IN (SELECT product_id FROM products WHERE quantity_in_stock > 0) THEN 1 ELSE NULL END);This query retrieves orders where all items are in stock.
Scenario 47: Employee Skills Database
Question: You have a database with tables for employees (`employees`) and employee skills (`employee_skills`). How would you retrieve a list of employees with a specific skill, such as “Java programming”?
Answer:
You can retrieve a list of employees with a specific skill by joining the `employees` and `employee_skills` tables and filtering for the desired skill. Here’s an example:
SELECT e.employee_id, e.employee_name
FROM employees e
JOIN employee_skills es ON e.employee_id = es.employee_id
WHERE es.skill = 'Java programming';This query retrieves employees with the specified skill.
Scenario 48: Social Media Friendships
Question: You’re managing a database for a social media platform with tables for users (`users`) and friendships (`friendships`). How would you retrieve a list of users who have no friends on the platform?
Answer:
You can retrieve a list of users who have no friends by using a LEFT JOIN to join the `users` and `friendships` tables and filtering for users without friends. Here’s an example:
SELECT u.user_id, u.username
FROM users u
LEFT JOIN friendships f ON u.user_id = f.user_id
WHERE f.friend_id IS NULL;This query retrieves users without any friendship connections.
Scenario 49: Product Inventory Restocking
Question: You’re managing an inventory database with a table for products (`products`). How would you retrieve a list of products that need restocking, where the quantity in stock is below a certain threshold, e.g., 10 units?
Answer:
You can retrieve a list of products that need restocking by using a WHERE clause to filter for products with a quantity in stock below the threshold. Here’s an example:
SELECT product_id, product_name
FROM products
WHERE quantity_in_stock < 10;This query retrieves products with a quantity in stock below 10 units.
Scenario 50: Student Exams and Scores
Question: You have tables for students (`students`) and their exam scores (`exam_scores`). How would you retrieve a list of students who scored above 90 on all their exams?
Answer:
You can retrieve a list of students who scored above 90 on all their exams by joining the `students` and `exam_scores` tables, grouping by student, and using the HAVING clause to filter for students with all exam scores above 90. Here’s an example:
SELECT s.student_id, s.student_name
FROM students s
JOIN exam_scores es ON s.student_id = es.student_id
GROUP BY s.student_id, s.student_name
HAVING MIN(es.score) > 90;This query retrieves students who scored above 90 on all their exams.
Scenario 51: Library Catalog
Question: You manage a database for a library catalog with tables for books (`books`) and book checkouts (`checkouts`). How would you retrieve a list of books that are currently checked out?
Answer:
You can retrieve a list of books that are currently checked out by joining the `books` and `checkouts` tables, filtering for books that have a checkout record with a return date in the future. Here’s an example:
SELECT b.book_id, b.title
FROM books b
JOIN checkouts c ON b.book_id = c.book_id
WHERE c.return_date > NOW();This query retrieves books that are currently checked out.
Scenario 52: Employee Benefits
Question: You have tables for employees (`employees`) and their benefits (`employee_benefits`). How would you retrieve a list of employees who have opted for health insurance and dental coverage?
Answer:
You can retrieve a list of employees who have opted for health insurance and dental coverage by joining the `employees` and `employee_benefits` tables and filtering for employees with the corresponding benefit options. Here’s an example:
SELECT e.employee_id, e.employee_name
FROM employees e
JOIN employee_benefits eb ON e.employee_id = eb.employee_id
WHERE eb.health_insurance = 'Yes' AND eb.dental_coverage = 'Yes';This query retrieves employees who have opted for both health insurance and dental coverage.
Scenario 53: Online Survey Data
Question: You’re managing a database for online survey responses with tables for respondents (`respondents`) and survey responses (`responses`). How would you retrieve a list of respondents who have completed all available surveys?
Answer:
You can retrieve a list of respondents who have completed all available surveys by joining the `respondents` and `responses` tables, grouping by respondent, and using the HAVING clause to filter for respondents with the count of completed surveys matching the total number of surveys. Here’s an example:
SELECT r.respondent_id, r.respondent_name
FROM respondents r
JOIN responses res ON r.respondent_id = res.respondent_id
GROUP BY r.respondent_id, r.respondent_name
HAVING COUNT(DISTINCT res.survey_id) = (SELECT COUNT() FROM surveys);This query retrieves respondents who have completed all available surveys.
Scenario 54: Order Shipping Tracking
Question: You have tables for customer orders (`orders`) and order shipping information (`shipping_info`). How would you retrieve a list of orders with pending or delayed shipments?
Answer:
You can retrieve a list of orders with pending or delayed shipments by joining the `orders` and `shipping_info` tables, filtering for orders with a status indicating pending or delayed shipping. Here’s an example:
SELECT o.order_id, o.order_date
FROM orders o
JOIN shipping_info si ON o.order_id = si.order_id
WHERE si.shipping_status IN ('Pending', 'Delayed');This query retrieves orders with pending or delayed shipping.
Scenario 55: Product Warranty Tracking
Question: You’re managing a database for product warranties with tables for products (`products`) and warranty claims (`warranty_claims`). How would you retrieve a list of products with the most warranty claims?
Answer:
You can retrieve a list of products with the most warranty claims by joining the `products` and `warranty_claims` tables, grouping by product, and using the ORDER BY and LIMIT clauses to select the top products with the most warranty claims. Here’s an example:
SELECT p.product_id, p.product_name, COUNT(wc.claim_id) AS warranty_claims
FROM products p
LEFT JOIN warranty_claims wc ON p.product_id = wc.product_id
GROUP BY p.product_id, p.product_name
ORDER BY warranty_claims DESC
LIMIT 5; -- Retrieve the top 5 products with the most warranty claimsThis query retrieves the products with the most warranty claims.
Scenario 56: Customer Support Tickets
Question: You’re managing a database for customer support tickets with tables for tickets (`tickets`) and ticket assignments (`assignments`). How would you retrieve a list of open tickets that haven’t been assigned to support agents?
Answer:
You can retrieve a list of open tickets that haven’t been assigned by using a LEFT JOIN between the `tickets` and `assignments` tables, and then filtering for tickets with no corresponding assignment records. Here’s an example:
SELECT t.ticket_id, t.issue_description
FROM tickets t
LEFT JOIN assignments a ON t.ticket_id = a.ticket_id
WHERE t.status = 'Open' AND a.support_agent_id IS NULL;This query retrieves open tickets that haven’t been assigned to support agents.
Scenario 57: Sales Performance
Question: You have tables for sales reps (`sales_reps`) and their sales data (`sales_data`). How would you retrieve a list of the top-performing sales reps based on their total sales amount?
Answer:
You can retrieve a list of the top-performing sales reps by joining the `sales_reps` and `sales_data` tables, grouping by sales rep, and using the ORDER BY and LIMIT clauses to select the top sales reps based on total sales amount. Here’s an example:
SELECT sr.sales_rep_id, sr.sales_rep_name, SUM(sd.sales_amount) AS total_sales
FROM sales_reps sr
JOIN sales_data sd ON sr.sales_rep_id = sd.sales_rep_id
GROUP BY sr.sales_rep_id, sr.sales_rep_name
ORDER BY total_sales DESC
LIMIT 5; -- Retrieve the top 5 sales reps by total sales amountThis query retrieves the top-performing sales reps based on total sales amount.
Scenario 58: Inventory Movement
Question: You manage an inventory database with tables for products (`products`) and inventory movements (`inventory_movements`). How would you retrieve a list of products that have had the most inventory movements (both additions and removals)?
Answer:
You can retrieve a list of products with the most inventory movements by joining the `products` and `inventory_movements` tables, grouping by product, and using the ORDER BY and LIMIT clauses to select the top products based on the count of inventory movements. Here’s an example:
SELECT p.product_id, p.product_name, COUNT(im.movement_id) AS total_movements
FROM products p
LEFT JOIN inventory_movements im ON p.product_id = im.product_id
GROUP BY p.product_id, p.product_name
ORDER BY total_movements DESC
LIMIT 5; -- Retrieve the top 5 products with the most inventory movementsThis query retrieves products with the most inventory movements.
Scenario 59: E-commerce Shipping Status
Question: You have tables for customer orders (`orders`) and order shipping status (`shipping_status`). How would you retrieve a list of orders with a “Shipped” status that haven’t been updated in the last 7 days?
Answer:
You can retrieve a list of orders with a “Shipped” status that haven’t been updated in the last 7 days by joining the `orders` and `shipping_status` tables, filtering for orders with a “Shipped” status and an update date older than 7 days. Here’s an example:
SELECT o.order_id, o.order_date
FROM orders o
JOIN shipping_status ss ON o.order_id = ss.order_id
WHERE ss.status = 'Shipped' AND ss.update_date < DATE_SUB(CURDATE(), INTERVAL 7 DAY);This query retrieves orders with a “Shipped” status that haven’t been updated in the last 7 days.
Scenario 60: Product Price Analysis
Question: You have tables for products (`products`) and historical price data (`price_history`). How would you retrieve a list of products that have had the most price changes in the last 30 days?
Answer:
You can retrieve a list of products with the most price changes in the last 30 days by joining the `products` and `price_history` tables, filtering for price changes within the specified time frame, and using the ORDER BY and LIMIT clauses to select the top products based on the count of price changes. Here’s an example:
SELECT p.product_id, p.product_name, COUNT(ph.price_change_id) AS price_changes
FROM products p
JOIN price_history ph ON p.product_id = ph.product_id
WHERE ph.change_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY p.product_id, p.product_name
ORDER BY price_changes DESC
LIMIT 5; -- Retrieve the top 5 products with the most price changes in the last 30 daysThis query retrieves products with the most price changes in the last 30 days.
Conclusion
SQL interview scenarios are more than just questions and answers; they represent real challenges faced by professionals working with data on a daily basis. Mastering these scenarios not only demonstrates your proficiency in SQL but also your ability to apply your knowledge to solve practical problems. The ability to navigate databases effectively and extract valuable insights is a skill highly sought after in today’s data-driven world.
As you’ve explored the diverse scenarios and their corresponding solutions in this article, you’ve gained a deeper understanding of how SQL can be used to address complex, real-world challenges. Whether it’s managing inventory, tracking orders, analyzing sales trends, or any other practical scenario, SQL proves to be a versatile and powerful tool in the hands of a skilled practitioner.
With this knowledge in your toolkit, you’ll be better prepared for SQL interviews and equipped to excel in roles that demand data management and analysis. Remember, practice makes perfect, so keep refining your SQL skills by tackling these scenarios, and you’ll be well on your way to becoming a SQL pro in no time. Good luck with your SQL journey, and may your future interviews be a breeze!