Problem Statements

1. Employee Salary Analysis

Tables:

• Employee (EmpID, Name, DepartmentID, Salary)

• Department (DepartmentID, DepartmentName)

Problem: Write a query to find the average salary of employees in each department, including only departments that have more than 5 employees.

2. Top Performing Products

Tables:

• Product (ProductID, ProductName, CategoryID, Price)

• Sales (SaleID, ProductID, Quantity)

Problem: Find the names of products that have generated more than $10,000 in total sales revenue.

3. Employee Hierarchy

Tables:

• Employee (EmpID, Name, ManagerID, Salary)

Problem: Write a query to retrieve the names of employees who earn more than the average salary of their department's employees, excluding their own salary.

4. Student Course Enrollment

Tables:

• Student (StudentID, Name)

• Course (CourseID, CourseName)

• Enrollment (StudentID, CourseID)

Problem: Write a query to find the number of courses each student is enrolled in, showing only those students who are enrolled in more than 3 courses.

5. Customer Orders

Tables:

• Customer (CustomerID, CustomerName)

• Order (OrderID, CustomerID, OrderDate, TotalAmount)

Problem: Find the customer(s) who have placed the highest total amount in orders, including their total order value.

6. Books and Authors

Tables:

• Author (AuthorID, AuthorName)

• Book (BookID, Title, AuthorID, Price)

Problem: Write a query to find the total revenue generated by each author, including only authors who have published more than 2 books.

7. Student Grades

Tables:

• Student (StudentID, Name)

• Course (CourseID, CourseName)

• Grades (StudentID, CourseID, Grade)

Problem: Write a query to find students with an average grade above 85 across all their courses.

8. Inventory Management

Tables:

• Item (ItemID, ItemName, QuantityInStock, Price)

• Supplier (SupplierID, SupplierName)

• SupplyOrder (OrderID, SupplierID, ItemID, QuantityOrdered)

Problem: Retrieve the items that have not been supplied by any supplier.

9. Project Teams

Tables:

• Project (ProjectID, ProjectName)

• Employee (EmpID, Name, ProjectID)

Problem: Write a query to find projects that have more than 4 employees assigned to them.

10. Movie Ratings

Tables:

• Movie (MovieID, Title, Genre)

• Rating (MovieID, UserID, Score)

Problem: Find the highest-rated movie in each genre, including its average score.

11. Course Prerequisites

Tables:

• Course (CourseID, CourseName)

• Prerequisite (CourseID, PrerequisiteID)

Problem: Retrieve all courses that have at least one prerequisite.

12. Orders and Products

Tables:

• Order (OrderID, OrderDate)

• Product (ProductID, ProductName, Price)

• OrderDetails (OrderID, ProductID, Quantity)

Problem: Write a query to find the total quantity sold for each product, including those that were never sold.

13. Hospital Patients

Tables:

• Patient (PatientID, PatientName, AdmissionDate)

• Treatment (TreatmentID, PatientID, TreatmentDate, Cost)

Problem: Find patients who have incurred treatment costs exceeding the average treatment cost of all patients.

14. Library Management

Tables:

• Member (MemberID, MemberName)

• Book (BookID, Title, AvailableCopies)

• Borrow (MemberID, BookID, BorrowDate)

Problem: Write a query to find members who have borrowed books that are currently not available.

15. Online Store Customer Feedback

Tables:

• Customer (CustomerID, CustomerName)

• Product (ProductID, ProductName)

• Feedback (FeedbackID, CustomerID, ProductID, Rating)

Problem: Find products that have an average rating below 3, along with the number of feedback entries.

16. College Course Scheduling

Tables:

• Course (CourseID, CourseName, Credits)

• Schedule (CourseID, RoomNumber, StartTime, EndTime)

Problem: Retrieve courses that are scheduled in the same room for overlapping times.

17. Tournament Player Stats

Tables:

• Player (PlayerID, PlayerName, TeamID)

• Match (MatchID, PlayerID, Score)

Problem: Find the top scorer for each team, displaying player names and their scores.

18. Vendor Contract Management

Tables:

• Vendor (VendorID, VendorName)

• Contract (ContractID, VendorID, StartDate, EndDate, Amount)

Problem: Write a query to find vendors who have active contracts that exceed $50,000.

19. City Population Growth

Tables:

• City (CityID, CityName, Population, State)

• PopulationGrowth (CityID, Year, GrowthRate)

Problem: Retrieve cities with a population growth rate higher than the average growth rate for their state.

20. Music Album Sales

Tables:

• Album (AlbumID, AlbumName, ArtistID, ReleaseYear)

• Sales (SaleID, AlbumID, QuantitySold)

Problem: Write a query to find the total sales for albums released after 2020, grouped by artist.