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

Tables:

• Doctors (Doctor_ID, Doctor_Name, Specialization, Hospital_ID)

• Hospitals (Hospital_ID, Hospital_Name, City)

• Description: Create tables for Doctors and Hospitals, with Doctor_ID and Hospital_ID as primary keys. Write a query to find doctors who work at hospitals located in a specific city (e.g., 'New York'), using a nested query to filter hospitals by city.

2. Problem Statement: GROUP BY, HAVING, Aggregate Functions

Tables:

• Flights (Flight_ID, Flight_Number, Departure_Time, Airline_ID)

• Airlines (Airline_ID, Airline_Name, Country)

• Description: Write a query to group flights by Airline_ID and find the total number of flights for each airline. Use HAVING to show only airlines with more than 100 flights.

3. Problem Statement: Foreign Key, DML, Aggregate Function

Tables:

• Products (Product_ID, Product_Name, Category, Supplier_ID, Price)

• Suppliers (Supplier_ID, Supplier_Name, City)

• Description: Insert new products into the Products table, and use a foreign key to connect Supplier_ID. Write a query to find the total number of products supplied by each supplier, using the COUNT() aggregate function.

4. Problem Statement: Nested Query, GROUP BY, DML

Tables:

• Rides (Ride_ID, Ride_Type, Distance, Driver_ID)

• Drivers (Driver_ID, Driver_Name, City)

• Description: Insert new ride records into the Rides table. Write a nested query to find the drivers who have driven more than the average ride distance, grouped by Driver_ID.

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

Tables:

• Authors (Author_ID, Author_Name, Country)

• Books (Book_ID, Book_Title, Published_Year, Author_ID, Copies_Sold)

• Description: Create the Authors and Books tables, where Author_ID in Books is a foreign key referencing Authors. Write a query to find the total number of copies sold by each author, using the SUM() aggregate function.

6. Problem Statement: Primary Key, GROUP BY, HAVING

Tables:

• Cars (Car_ID, Car_Model, Year_Manufactured, Owner_ID)

• Owners (Owner_ID, Owner_Name, City)

• Description: Write a query to group cars by Owner_ID and find the total number of cars owned by each person. Use HAVING to show only owners with more than two cars.

7. Problem Statement: Nested Query, DML, Aggregate Function

Tables:

• Purchases (Purchase_ID, Customer_ID, Product_ID, Purchase_Amount, Purchase_Date)

• Customers (Customer_ID, Customer_Name, Email)

• Description: Insert new purchase records into the Purchases table. Write a nested query to find customers who have made a purchase larger than the average purchase amount.

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

Tables:

• Rooms (Room_ID, Room_Type, Hotel_ID, Rate_Per_Night)

• Hotels (Hotel_ID, Hotel_Name, City)

• Description: Create the Rooms and Hotels tables, with Hotel_ID as a foreign key. Write a nested query to find rooms that are in hotels located in 'Los Angeles' and have a rate above the average rate for that city.

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

Tables:

• Sales (Sale_ID, Product_ID, Sale_Amount, Sale_Date)

• Products (Product_ID, Product_Name, Category, Price)

• Description: Write a query to group sales by Product_ID and calculate the total sales for each product. Use HAVING to display only products whose total sales exceed the average sale amount, calculated via a nested query.

10. Problem Statement: Foreign Key, DML, Aggregate Function

Tables:

• Orders (Order_ID, Customer_ID, Total_Amount, Order_Date)

• Customers (Customer_ID, Customer_Name, City)

• Description: Insert new orders into the Orders table. Write a query to calculate the total sales per customer and use SUM() to aggregate the total sales, with Customer_ID as a foreign key.

11. Problem Statement: Nested Query, GROUP BY, Aggregate Function

Tables:

• Employees (Employee_ID, Employee_Name, Department_ID, Salary)

• Departments (Department_ID, Department_Name)

• Description: Write a query to group employees by Department_ID and find the average salary per department. Then, use a nested query to find all employees whose salary is greater than the department's average salary.

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

Tables:

• Stores (Store_ID, Store_Name, City)

• Sales (Sale_ID, Store_ID, Sale_Amount, Sale_Date)

• Description: Create a Stores table with Store_ID as a primary key and Sales table with Store_ID as a foreign key. Write a query to find the total sales amount per store using SUM().

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

Tables:

• Enrollments (Enrollment_ID, Student_ID, Course_ID, Grade)

• Courses (Course_ID, Course_Name, Credits)

• Description: Write a query to group enrollments by Course_ID and find the average grade for each course. Use HAVING to filter courses where the average grade is higher than the overall average grade, calculated via a nested query.

14. Problem Statement: DML, GROUP BY, Aggregate Function

Tables:

• Orders (Order_ID, Order_Date, Customer_ID, Total_Amount)

• Customers (Customer_ID, Customer_Name, City)

• Description: Insert new orders into the Orders table. Write a query to group orders by Customer_ID and calculate the total order amount per customer using SUM().

15. Problem Statement: DDL, Foreign Key, HAVING

Tables:

• Flights (Flight_ID, Flight_Number, Airline_ID, Departure_Time, Arrival_Time)

• Airlines (Airline_ID, Airline_Name, Country)

• Description: Create a table for Flights and Airlines, with Airline_ID as a foreign key. Write a query to group flights by Airline_ID and find the total number of flights per airline, using HAVING to filter airlines with more than 10 flights.

16. Problem Statement: Nested Query, DML, GROUP BY

Tables:

• Shifts (Shift_ID, Employee_ID, Shift_Date, Hours_Worked)

• Employees (Employee_ID, Employee_Name, Position)

• Description: Insert new shifts into the Shifts table. Write a nested query to find employees who have worked more hours than the average number of hours worked in all shifts, grouped by Employee_ID.

17. Problem Statement: DDL, Primary Key, Aggregate Function

Tables:

• Teams (Team_ID, Team_Name, City)

• Matches (Match_ID, Team_ID, Opponent_Team_ID, Score)

• Description: Create the Teams and Matches tables. Write a query to calculate the average score per team using AVG().

18. Problem Statement: GROUP BY, HAVING, Aggregate Function

Tables:

• Purchases (Purchase_ID, Customer_ID, Product_ID, Purchase_Amount)

• Customers (Customer_ID, Customer_Name, City)

• Description: Write a query to group purchases by Customer_ID and calculate the total purchase amount for each customer using SUM(). Use HAVING to filter customers whose total purchases exceed $1,000.

19. Problem Statement: Nested Query, DML, HAVING

Tables:

• Projects (Project_ID, Project_Name, Employee_ID, Hours_Worked)

• Employees (Employee_ID, Employee_Name, Department)

• Description: Insert new project records into the Projects table. Write a query to group projects by Employee_ID and find employees who have worked more than the average project hours, using a nested query and HAVING.

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

Tables:

• Vehicles (Vehicle_ID, Model, Year, Owner_ID)

• Owners (Owner_ID, Owner_Name, City)

• Description: Create the Vehicles and Owners tables. Write a nested query to find vehicles owned by people who live in a particular city (e.g., 'Los Angeles') and were manufactured after the year 2015.