SQL Scenario-Based Questions with Detailed Explanations

Introduction:

In this document, I present ten scenario-based questions focusing on SQL queries and database schema diagrams. Each scenario is accompanied by a detailed explanation and solution, covering various aspects of SQL querying and database design principles.

Scenario 1: Employee Database

Description:
You're managing an employee database where each employee is associated with a department. Employees have attributes like ID, name, and salary, while departments have attributes like ID and name. Design a query to retrieve the names of all employees along with their corresponding department names.

Solution:

SELECT employees.name, departments.name AS department_name
FROM employees
JOIN departments ON employees.department_id = departments.department_id;

Explanation:
This query utilizes a JOIN operation to combine data from the "employees" and "departments" tables based on the common attribute "department_id". By selecting the employee names and department names, we can effectively retrieve the desired information.

Scenario 2: Sales Analysis

Description:
In a sales database, you have two tables: "orders" and "customers." Each order has a customer ID, order ID, and order date, while each customer has an ID and name. Write a SQL query to find the total number of orders placed by each customer.

Solution:

SELECT customers.name, COUNT(orders.order_id) AS total_orders
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY customers.customer_id;

Explanation:
This query joins the "customers" and "orders" tables using the customer ID as the common attribute. Then, it uses the COUNT() function to calculate the total number of orders for each customer, grouping the results by the customer ID.

Scenario 3: Product Inventory

Description:
You're working with a product inventory database that includes tables for "products" and "inventory." The products table contains information about each product, including product ID and name, while the inventory table tracks the quantity of each product in stock. Create a query to display the names of products with a stock quantity of less than 10.

Solution:

SELECT products.name
FROM products
JOIN inventory ON products.product_id = inventory.product_id
WHERE inventory.quantity < 10;

Explanation:
This query performs an inner join between the "products" and "inventory" tables based on the product ID. It selects the product names from the "products" table where the stock quantity in the "inventory" table is less than 10.

Scenario 4: Employee Hierarchy

Description:
In an organization, employees are organized into a hierarchical structure with each employee having a manager. The employee table contains columns for employee ID, name, and manager ID. Write a query to retrieve the names of all employees along with the names of their respective managers.

Solution:

SELECT e.name AS employee_name, m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

Explanation:
This query utilizes a LEFT JOIN operation to join the "employees" table with itself based on the manager ID. By selecting the employee names and their corresponding manager names, it retrieves the desired hierarchical information.

Scenario 5: Student Grades

Description:
You're managing a database for student grades, which includes tables for "students" and "grades." Each student has a student ID and name, while the grades table contains columns for student ID, course ID, and grade. Design a query to calculate the average grade for each student.

Solution:

SELECT students.name, AVG(grades.grade) AS average_grade
FROM students
JOIN grades ON students.student_id = grades.student_id
GROUP BY students.student_id;

Explanation:
This query performs an inner join between the "students" and "grades" tables based on the student ID. It calculates the average grade for each student using the AVG() function and groups the results by the student ID.

Scenario 6: Product Categories

Description:
A product database includes tables for "products" and "categories." Each product is associated with a category, and the categories table contains category ID and name. Write a query to list all product categories along with the count of products in each category.

Solution:

SELECT categories.name, COUNT(products.product_id) AS product_count
FROM categories
LEFT JOIN products ON categories.category_id = products.category_id
GROUP BY categories.category_id;

Explanation:
This query performs a LEFT JOIN operation between the "categories" and "products" tables based on the category ID. It counts the number of products in each category using the COUNT() function and groups the results by the category ID.

Scenario 7: Social Media Network

Description:
You're building a social media network with tables for "users" and "posts." The users table includes user ID and name, while the posts table contains post ID, user ID, and post content. Create a query to retrieve the names of users who have posted more than 5 times.

Solution:

SELECT users.name
FROM users
JOIN posts ON users.user_id = posts.user_id
GROUP BY users.user_id
HAVING COUNT(posts.post_id) > 5;

Explanation:
This query joins the "users" and "posts" tables based on the user ID. It then uses the COUNT() function to count the number of posts for each user and filters the results using the HAVING clause to only include users with more than 5 posts.

Scenario 8: Order Totals

Description:
In an e-commerce database, you have tables for "orders" and "order_items." Each order has an order ID and date, while the order_items table contains order ID, product ID, and quantity. Design a query to calculate the total amount spent on each order.

Solution:

SELECT orders.order_id, SUM(order_items.quantity * order_items.price) AS total_amount
FROM orders
JOIN order_items ON orders.order_id = order_items.order_id
GROUP BY orders.order_id;

Explanation:
This query performs an inner join between the "orders" and "order_items" tables based on the order ID. It calculates the total amount spent on each order by multiplying the quantity of each item by its price, using the SUM() function, and groups the results by the order ID.

Scenario 9: Library Management

Description:
You're managing a library database with tables for "books" and "checkouts." The books table includes book ID and title, while the checkouts table contains book ID, checkout date, and return date. Create a query to find the books that are currently checked out.

Solution:

SELECT books.title
FROM books
LEFT JOIN checkouts ON books.book_id = checkouts.book_id
WHERE checkouts.return_date IS NULL;

Explanation:
This query performs a LEFT JOIN operation between the "books" and "checkouts" tables based on the book ID. It selects the titles of books where the return date in the "checkouts" table is NULL, indicating that the book is currently checked out.

Scenario 10: Employee Salaries

Description:
In an employee database, you have a table named "salaries" that includes columns for employee ID, salary, and date. Write a query to find the highest salary for each year.

Solution:

SELECT YEAR(date) AS year, MAX(salary) AS max_salary
FROM salaries
GROUP BY YEAR(date);

Explanation:
This query extracts the year from the date column using the YEAR() function and calculates the maximum salary for each year using the MAX() function. It groups the results by the year extracted from the date column.

Conclusion:

These scenario-based questions cover various SQL querying techniques and database schema design concepts. By understanding and practicing these scenarios, you can enhance your SQL skills and gain a deeper understanding of database management principles.