SQL Tables, Questions, and Answers

1. Sales, Customers, and Products Tables

Sales

SaleID	ProductID	CustomerID	Quantity	SaleDate
1	1	101	2	2023-01-01
2	2	102	3	2023-01-02
3	3	103	1	2023-01-03
4	1	101	1	2023-01-04

Customers

CustomerID	Name	Country
101	Alice Johnson	USA
102	Bob Smith	Canada
103	Carol White	UK

Products

ProductID	ProductName	Price
1	Laptop	1000
2	Tablet	500
3	Smartphone	800

Question:

Find the total revenue generated by each customer.

SQL Query:

SELECT c.Name, SUM(s.Quantity * p.Price) AS TotalRevenue
FROM Sales s
JOIN Customers c ON s.CustomerID = c.CustomerID
JOIN Products p ON s.ProductID = p.ProductID
GROUP BY c.Name;

2. Employees, Projects, and Assignments Tables

Employees

EmployeeID	Name	Department
1	John Smith	IT
2	Jane Doe	HR
3	Alice Brown	Finance

Projects

ProjectID	ProjectName	Budget
1	Project A	50000
2	Project B	75000
3	Project C	100000

Assignments

AssignmentID	EmployeeID	ProjectID	HoursWorked
1	1	1	100
2	2	2	150
3	3	3	200
4	1	2	120

Question:

Calculate the total hours worked by each employee across all projects.

SQL Query:

SELECT e.Name, SUM(a.HoursWorked) AS TotalHours
FROM Assignments a
JOIN Employees e ON a.EmployeeID = e.EmployeeID
GROUP BY e.Name;

3. Orders, Customers, and Products Tables

Orders

OrderID	CustomerID	OrderDate
1	101	2023-02-01
2	102	2023-02-02
3	103	2023-02-03
4	101	2023-02-04

Customers

CustomerID	Name	Country
101	Michael Scott	USA
102	Jim Halpert	Canada
103	Dwight Schrute	USA

Products

ProductID	ProductName	Price
1	Laptop	1200
2	Monitor	200
3	Keyboard	50

OrderDetails

OrderID	ProductID	Quantity
1	1	2
1	2	1
2	3	3
3	1	1
4	2	2

Question:

Retrieve the total amount spent by each customer on their orders.

SQL Query:

SELECT c.Name, SUM(od.Quantity * p.Price) AS TotalSpent
FROM Orders o
JOIN OrderDetails od ON o.OrderID = od.OrderID
JOIN Products p ON od.ProductID = p.ProductID
JOIN Customers c ON o.CustomerID = c.CustomerID
GROUP BY c.Name;

4. Flights, Airlines, and Passengers Tables

Flights

FlightID	AirlineID	Destination	DepartureDate
1	1	New York	2023-05-01
2	2	London	2023-05-02
3	1	Paris	2023-05-03
4	3	Tokyo	2023-05-04

Airlines

AirlineID	AirlineName	Country
1	Delta	USA
2	British Airways	UK
3	Japan Airlines	Japan

Passengers

PassengerID	Name	FlightID	SeatNumber
1	Emma Johnson	1	12A
2	Liam Brown	2	10B
3	Olivia Smith	3	8C
4	Noah Davis	4	14D

Question:

Find the number of passengers each airline has flown to their destinations.

SQL Query:

SELECT a.AirlineName, COUNT(p.PassengerID) AS NumberOfPassengers
FROM Passengers p
JOIN Flights f ON p.FlightID = f.FlightID
JOIN Airlines a ON f.AirlineID = a.AirlineID
GROUP BY a.AirlineName;

5. Students, Courses, and Enrollments Tables

Students

StudentID	Name	Major
1	Alice	Computer Science
2	Bob	Mathematics
3	Charlie	Biology
4	David	Physics

Courses

CourseID	CourseName	Credits
1	Algorithms	3
2	Linear Algebra	4
3	Genetics	3
4	Quantum Mechanics	4

Enrollments

EnrollmentID	StudentID	CourseID	Grade
1	1	1	A
2	2	2	B
3	3	3	A
4	4	4	C
5	1	2	B

Question:

Calculate the average grade of each student across all their courses.

SQL Query:

SELECT s.Name, AVG(
    CASE e.Grade
        WHEN 'A' THEN 4
        WHEN 'B' THEN 3
        WHEN 'C' THEN 2
        WHEN 'D' THEN 1
        ELSE 0
    END
) AS AverageGrade
FROM Enrollments e
JOIN Students s ON e.StudentID = s.StudentID
GROUP BY s.Name;