SQL Queries for Various Tables

1. Employees Table

Table: Employees

EmployeeID	Name	Department	Salary	JoiningDate
1	John Smith	IT	60000	2018-01-15
2	Jane Doe	HR	75000	2019-02-20
3	Alice Brown	IT	80000	2020-03-10
4	Bob White	Finance	50000	2017-04-25

Question:

Find the highest salary in each department.

SQL Query:

SELECT Department, MAX(Salary) AS HighestSalary
FROM Employees
GROUP BY Department;

2. Orders Table

Table: Orders

OrderID	CustomerID	OrderDate	Amount
1	101	2023-01-10	250.50
2	102	2023-01-12	150.75
3	103	2023-01-15	300.20
4	101	2023-01-20	220.10

Question:

Retrieve the total amount spent by each customer.

SQL Query:

SELECT CustomerID, SUM(Amount) AS TotalSpent
FROM Orders
GROUP BY CustomerID;

3. Sales Table

Table: Sales

SaleID	ProductID	Quantity	SaleDate	Price
1	2001	3	2022-11-05	150
2	2002	2	2022-11-10	200
3	2001	5	2022-11-15	150
4	2003	1	2022-11-20	300

Question:

Calculate the total revenue generated by each product.

SQL Query:

SELECT ProductID, SUM(Quantity * Price) AS TotalRevenue
FROM Sales
GROUP BY ProductID;

4. Students Table

Table: Students

StudentID	Name	Age	Grade
1	Emma	20	A
2	Liam	21	B
3	Olivia	22	A
4	Noah	23	C

Question:

Find the average age of students with grade 'A'.

SQL Query:

SELECT AVG(Age) AS AverageAge
FROM Students
WHERE Grade = 'A';

5. Products Table

Table: Products

ProductID	ProductName	Category	Price
1	Laptop	Electronics	800
2	Headphones	Electronics	50
3	Coffee Maker	Home Appliances	120
4	Mixer	Home Appliances	150

Question:

List the products with a price higher than the average price of all products.

SQL Query:

SELECT ProductName, Price
FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products);

6. Customers Table

Table: Customers

CustomerID	Name	Country	Age
1	Michael Scott	USA	45
2	Jim Halpert	Canada	35
3	Dwight Schrute	USA	40
4	Pam Beesly	UK	38

Question:

Find the youngest customer in each country.

SQL Query:

SELECT Country, MIN(Age) AS YoungestAge
FROM Customers
GROUP BY Country;

7. Attendance Table

Table: Attendance

StudentID	ClassDate	Status
1	2023-01-10	pre style="font-size: 18px;" style="font-size: 18px;"sent
2	2023-01-10	Absent
1	2023-01-11	Absent
2	2023-01-11	pre style="font-size: 18px;" style="font-size: 18px;"sent

Question:

Count the number of days each student was pre style="font-size: 18px;" style="font-size: 18px;"sent.

SQL Query:

SELECT StudentID, COUNT(*) AS Dayspre style="font-size: 18px;" style="font-size: 18px;"sent
FROM Attendance
WHERE Status = 'pre style="font-size: 18px;" style="font-size: 18px;"sent'
GROUP BY StudentID;

8. Inventory Table

Table: Inventory

ItemID	ItemName	Quantity	Price
1	Notebook	50	5.00
2	Pen	100	1.00
3	Pencil	75	0.50
4	Eraser	30	0.25

Question:

Find the total value of inventory.

SQL Query:

SELECT SUM(Quantity * Price) AS TotalInventoryValue
FROM Inventory;

9. Books Table

Table: Books

BookID	Title	Author	Price
1	The Alchemist	Paulo Coelho	10.00
2	1984	George Orwell	8.00
3	To Kill a Mockingbird	Harper Lee	12.00
4	The Great Gatsby	F. Scott Fitzgerald	15.00

Question:

List books priced higher than the average price of all books.

SQL Query:

SELECT Title, Price
FROM Books
WHERE Price > (SELECT AVG(Price) FROM Books);

10. Transactions Table

Table: Transactions

TransactionID	AccountID	Amount	TransactionDate
1	1001	500.00	2023-02-10
2	1002	150.00	2023-02-12
3	1001	250.00	2023-02-15
4	1003	300.00	2023-02-20

Question:

Calculate the total amount transacted by each account.

SQL Query:

SELECT AccountID, SUM(Amount) AS TotalAmount
FROM Transactions
GROUP BY AccountID;