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

Airlines

AirlineID	AirlineName
1	Airline A
2	Airline B

Passengers

PassengerID	Name
1	John Doe
2	Jane Smith

Bookings

BookingID	FlightID	PassengerID
1	1	1
2	2	2
3	3	1

Question:

List the names of passengers and the destinations they are flying to.

SQL Query:

SELECT p.Name, f.Destination
FROM Bookings b
JOIN Passengers p ON b.PassengerID = p.PassengerID
JOIN Flights f ON b.FlightID = f.FlightID;

5. Stores, Products, and Sales Tables

Stores

StoreID	StoreName	Location
1	Store A	New York
2	Store B	Los Angeles
3	Store C	Chicago

Products

ProductID	ProductName	Price
1	Laptop	1500
2	Tablet	600
3	Smartphone	1000

Sales

SaleID	StoreID	ProductID	Quantity	SaleDate
1	1	1	2	2023-06-01
2	2	2	1	2023-06-02
3	3	3	3	2023-06-03

Question:

Calculate the total sales amount for each store.

SQL Query:

SELECT s.StoreName, SUM(sa.Quantity * p.Price) AS TotalSales
FROM Sales sa
JOIN Stores s ON sa.StoreID = s.StoreID
JOIN Products p ON sa.ProductID = p.ProductID
GROUP BY s.StoreName;

6. Movies, Directors, and MovieDirectors Tables

Movies

MovieID	Title	ReleaseYear
1	The Godfather	1972
2	Pulp Fiction	1994
3	Inception	2010

Directors

DirectorID	Name
1	Francis Ford Coppola
2	Quentin Tarantino
3	Christopher Nolan

MovieDirectors

MovieID	DirectorID
1	1
2	2
3	3

Question:

List the titles of movies and their directors.

SQL Query:

SELECT m.Title, d.Name AS Director
FROM MovieDirectors md
JOIN Movies m ON md.MovieID = m.MovieID
JOIN Directors d ON md.DirectorID = d.DirectorID;

7. Courses, Students, and Enrollments Tables

Courses

CourseID	CourseName
1	Math
2	Science
3	History

Students

StudentID	Name
1	Emily
2	Jack
3	Olivia

Enrollments

EnrollmentID	CourseID	StudentID
1	1	1
2	2	2
3	3	3
4	1	2

Question:

Find the number of students enrolled in each course.

SQL Query:

SELECT c.CourseName, COUNT(e.StudentID) AS TotalStudents
FROM Enrollments e
JOIN Courses c ON e.CourseID = c.CourseID
GROUP BY c.CourseName;

8. Authors, Books, and BookAuthors Tables

Authors

AuthorID	Name
1	J.K. Rowling
2	J.R.R. Tolkien
3	George R.R. Martin

Books

BookID	Title	PublicationYear
1	Harry Potter	1997
2	The Hobbit	1937
3	A Game of Thrones	1996

BookAuthors

BookID	AuthorID
1	1
2	2
3	3

Question:

Retrieve the titles of books along with their authors.

SQL Query:

SELECT b.Title, a.Name AS Author
FROM BookAuthors ba
JOIN Books b ON ba.BookID = b.BookID
JOIN Authors a ON ba.AuthorID = a.AuthorID;

9. Departments, Employees, and Salaries Tables

Departments

DepartmentID	DepartmentName
1	IT
2	HR
3	Finance

Employees

EmployeeID	Name	DepartmentID
1	John	1
2	Jane	2
3	Michael	3

Salaries

SalaryID	EmployeeID	Amount
1	1	70000
2	2	60000
3	3	75000

Question:

Calculate the total salary for each department.

SQL Query:

SELECT d.DepartmentName, SUM(s.Amount) AS TotalSalary
FROM Salaries s
JOIN Employees e ON s.EmployeeID = e.EmployeeID
JOIN Departments d ON e.DepartmentID = d.DepartmentID
GROUP BY d.DepartmentName;

10. Courses, Students, and Grades Tables

Courses

CourseID	CourseName
1	Math
2	Science
3	History

Students

StudentID	Name
1	Emily
2	Jack
3	Olivia

Grades

GradeID	CourseID	StudentID	Grade
1	1	1	A
2	2	2	B
3	3	3	A
4	1	2	C

Question:

List the students who received an 'A' grade in any course.

SQL Query:

SELECT s.Name
FROM Grades g
JOIN Students s ON g.StudentID = s.StudentID
WHERE g.Grade = 'A';