1. Problem Statement: Nested Query, GROUP BY, HAVING

Tables:

• Orders (Order_ID, Customer_ID, Order_Date, Order_Amount)

• Description: Write a query to find all customers from the Orders table who have placed more than 5 orders and whose total order value exceeds the average order value. Use GROUP BY to group by Customer_ID, HAVING to filter, and a nested query to calculate the average order value.

2. Problem Statement: DDL, Foreign Key, Aggregate Function

Tables:

• Authors (Author_ID, Author_Name, Nationality)

• Books (Book_ID, Book_Title, Price, Author_ID)

• Description: Create two tables, Authors with Author_ID as the primary key and Books with Book_ID as the primary key and a foreign key Author_ID. Write a query to find the average price of books per author.

3. Problem Statement: Nested Query, DML, Primary Key

Tables:

• Employees (Employee_ID, Employee_Name, Department_ID, Salary)

• Description: Insert a new employee into the Employees table. After insertion, use a nested query to find the employee whose salary is above the company-wide average salary. Ensure the Employee_ID is the primary key.

4. Problem Statement: Group By, HAVING, Aggregate Functions

Tables:

• Employees (Employee_ID, Employee_Name, Department_ID, Job_Title, Salary)

• Description: Write a query to list all departments from the Employees table, counting the total number of employees in each department and the average salary per department. Use HAVING to filter out departments with less than 10 employees.

5. Problem Statement: Nested Query, GROUP BY, DML Command

Tables:

• Sales (Sale_ID, Salesperson_ID, Sale_Amount, Sale_Date)

• Description: Insert new sales records into the Sales table. Then, use a nested query to find the maximum sales made by each Salesperson by grouping the results using GROUP BY.

6. Problem Statement: DDL Command, Primary Key, GROUP BY

Tables:

• Courses (Course_ID, Course_Name, Instructor_Name)

• Enrollments (Enrollment_ID, Student_ID, Course_ID, Enrollment_Date)

• Description: Create a table Courses with a primary key Course_ID and another table Enrollments with a foreign key referencing Course_ID. Write a query to group the Enrollments by Course_ID and calculate the total number of students in each course.

7. Problem Statement: Foreign Key, Nested Query, Aggregate Function

Tables:

• Customers (Customer_ID, Customer_Name, Address)

• Orders (Order_ID, Customer_ID, Order_Amount, Order_Date)

• Description: Create two tables, Customers and Orders. Use a foreign key relationship between Customer_ID. Write a nested query to find customers who have placed an order larger than the average order value, using AVG() to calculate the average.

8. Problem Statement: HAVING, DML, GROUP BY

Tables:

• Products (Product_ID, Product_Name, Price, Category)

• Description: Update the price of specific products in the Products table. Write a query to group the Products by Category and find the total product count per category. Use HAVING to filter categories where the product count is greater than 10.

9. Problem Statement: Primary Key, Foreign Key, Nested Query

Tables:

• Students (Student_ID, Student_Name)

• Classes (Class_ID, Student_ID, Class_Name)

• Description: Create a Students table with Student_ID as the primary key and a Classes table with Class_ID as the primary key and Student_ID as a foreign key. Write a query to find students enrolled in multiple classes using a nested query.

10. Problem Statement: DDL, Group By, Aggregate Functions

Tables:

• Departments (Department_ID, Department_Name, Manager_ID)

• Description: Create a table Departments and insert records into it. Write a query to find the department with the highest total salary by grouping by Department_ID and using the SUM() aggregate function.

11. Problem Statement: Nested Query, HAVING, Aggregate Functions

Tables:

• Employees (Employee_ID, Employee_Name, Department_ID, Salary)

• Description: Write a query to list the names of employees who earn more than the average salary in their department. Use a nested query to calculate the department's average salary and a HAVING clause to filter the results.

12. Problem Statement: DML, GROUP BY, Aggregate Functions

Tables:

• Employees (Employee_ID, Employee_Name, Job_Title, Salary)

• Description: Insert new records into the Employees table. Write a query to group employees by Job_Title, and calculate the average salary and total salary for each job title using AVG() and SUM() functions.

13. Problem Statement: DDL, Primary Key, Nested Query

Tables:

• Inventory (Item_ID, Item_Name, Price, Stock_Quantity)

• Description: Create a table Inventory with Item_ID as the primary key. Insert data, then write a nested query to find all items whose price is higher than the average price of all items in the inventory.

14. Problem Statement: Foreign Key, GROUP BY, HAVING

Tables:

• Customers (Customer_ID, Customer_Name, City)

• Sales (Sale_ID, Customer_ID, Sale_Amount, Sale_Date)

• Description: Write a query for the Sales and Customers tables (with a foreign key relationship) to find the total sales for each customer. Use GROUP BY on Customer_ID and HAVING to show only customers with sales greater than $10,000.

15. Problem Statement: Nested Query, DML, Primary Key

Tables:

• Customers (Customer_ID, Customer_Name, Email, Phone)

• Description: Insert new customer records into the Customers table and then write a nested query to find all customers who placed an order above the average order value. Ensure Customer_ID is the primary key.

16. Problem Statement: GROUP BY, Aggregate Functions, DML

Tables:

• Inventory (Item_ID, Item_Name, Category, Stock_Quantity, Price)

• Description: After updating the stock levels in the Inventory table, write a query to group items by Category and calculate the total stock value using SUM() and average stock value using AVG(). Display the result grouped by Category.

17. Problem Statement: DDL, Foreign Key, Nested Query

Tables:

• Projects (Project_ID, Project_Name, Start_Date)

• Tasks (Task_ID, Project_ID, Task_Description, Duration_Days)

• Description: Create two tables, Projects and Tasks, with a foreign key from Tasks referencing Project_ID in Projects. Write a nested query to find projects with tasks whose duration is longer than the average task duration.

18. Problem Statement: HAVING, GROUP BY, DML Commands

Tables:

• Orders (Order_ID, Customer_ID, Order_Amount, Order_Date)

• Description: After inserting new orders into the Orders table, write a query to group the orders by Customer_ID and calculate the total order value for each customer. Use HAVING to show only customers who have placed orders totaling more than $1,000.

19. Problem Statement: DDL, Primary Key, Aggregate Functions

Tables:

• Salaries (Employee_ID, Salary_Amount, Pay_Date)

• Description: Create a Salaries table with Employee_ID as the primary key. Insert records and write a query to find the maximum, minimum, and average salary using MAX(), MIN(), and AVG() aggregate functions.

20. Problem Statement: DML, Nested Query, HAVING

Tables:

• Employees (Employee_ID, Employee_Name, Job_Title, Salary, Hire_Date)

• Description: Insert new employee records into the Employees table. Write a nested query to find employees hired before a specific date who earn more than the average salary in their job title, using HAVING for filtering results.