DB --> LEC 8 -->Practical

Below is a set of practical questions in English designed to test your understanding of SQL JOINs, including all the topics you mentioned: SQL JOIN, AS Alias, WHERE Clause, Multiple Tables, Types of JOINs (INNER, LEFT, RIGHT, FULL OUTER, SELF, CROSS), and JOIN vs. Nested Queries. These questions include a sample database schema for context and require writing SQL queries to solve real-world scenarios. Each question is practical and focuses on applying the concepts.

Sample Database Schema

Use the following tables for all questions:

Customers:

customer_id (INT, Primary Key)
first_name (VARCHAR)
country (VARCHAR)

Orders:

order_id (INT, Primary Key)
customer_id (INT, Foreign Key)
order_date (DATE)
amount (DECIMAL)

OrderDetails:

order_id (INT, Foreign Key)
product_id (INT)
quantity (INT)

Products:

product_id (INT, Primary Key)
product_name (VARCHAR)
price (DECIMAL)

Employees:

employee_id (INT, Primary Key)
first_name (VARCHAR)
manager_id (INT, Foreign Key to employee_id)

Sample Data:


-- Create Customers Table
CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    country VARCHAR(50)
);

-- Create Orders Table
CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

-- Create OrderDetails Table
CREATE TABLE OrderDetails (
    order_id INT,
    product_id INT,
    quantity INT,
    FOREIGN KEY (order_id) REFERENCES Orders(order_id)
);

-- Create Products Table
CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50),
    price DECIMAL(10, 2)
);

-- Create Employees Table
CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES Employees(employee_id)
);

-- Insert Sample Data into Customers
INSERT INTO Customers (customer_id, first_name, country) VALUES
(1, 'Alice', 'USA'),
(2, 'Bob', 'UK'),
(3, 'Charlie', 'Canada'),
(4, 'David', 'USA');

-- Insert Sample Data into Orders
INSERT INTO Orders (order_id, customer_id, order_date, amount) VALUES
(101, 1, '2023-01-15', 150.00),
(102, 1, '2023-02-20', 300.00),
(103, 2, '2023-03-10', 200.00),
(104, 3, '2023-04-05', 100.00);

-- Insert Sample Data into OrderDetails
INSERT INTO OrderDetails (order_id, product_id, quantity) VALUES
(101, 1001, 5),
(101, 1002, 2),
(102, 1003, 3),
(103, 1001, 4);

-- Insert Sample Data into Products
INSERT INTO Products (product_id, product_name, price) VALUES
(1001, 'Laptop', 1000.00),
(1002, 'Mouse', 20.00),
(1003, 'Keyboard', 50.00);

-- Insert Sample Data into Employees
INSERT INTO Employees (employee_id, first_name, manager_id) VALUES
(1, 'Emma', NULL),
(2, 'Liam', 1),
(3, 'Olivia', 1),
(4, 'Noah', 2);

Below are the solutions to all 20 practical questions based on the provided database schema and sample data. Each solution includes the SQL query, a brief explanation, and the expected output based on the sample data provided. The queries are written to be clear, efficient, and compatible with standard SQL databases (e.g., MySQL, PostgreSQL, SQL Server). I’ve also included comparisons for questions involving JOIN vs. subqueries and addressed any special cases (e.g., emulating FULL OUTER JOIN).

Database Schema and Sample Data Recap

The tables (Customers, Orders, OrderDetails, Products, Employees) and sample data were provided in the previous response. For reference, here’s a quick summary of key data:

Customers: 4 customers (Alice, Bob, Charlie, David).
Orders: 4 orders (3 for Alice and Bob, 1 for Charlie, none for David).
OrderDetails: Links orders to products (e.g., order 101 includes Laptop and Mouse).
Products: 3 products (Laptop, Mouse, Keyboard).
Employees: 4 employees (Emma is the top manager, others report to Emma or Liam).

Solutions to Practical Questions

1. Basic SQL JOIN

Question: Retrieve the first name of customers and their order IDs for all orders placed using an INNER JOIN.

Query:

SELECT c.first_name, o.order_id
FROM Customers c
INNER JOIN Orders o
ON c.customer_id = o.customer_id;

Explanation:

INNER JOIN returns only customers with orders.
Matches Customers.customer_id with Orders.customer_id.
Selects customer’s first name and order ID.

Expected Output:

first_name	order_id
Alice	101
Alice	102
Bob	103
Charlie	104

2. SQL JOIN With AS Alias

Question: List customer first name and order date using aliases c and o for Customers and Orders.

Query:

SELECT c.first_name, o.order_date
FROM Customers AS c
INNER JOIN Orders AS o
ON c.customer_id = o.customer_id;

Explanation:

Uses AS to alias tables (c for Customers, o for Orders).
INNER JOIN ensures only customers with orders are included.
Retrieves first_name and order_date.

Expected Output:

first_name	order_date
Alice	2023-01-15
Alice	2023-02-20
Bob	2023-03-10
Charlie	2023-04-05

3. JOIN With WHERE Clause

Question: Find customers with orders where amount > 150, showing first name, order ID, and amount.

Query:

SELECT c.first_name, o.order_id, o.amount
FROM Customers c
INNER JOIN Orders o
ON c.customer_id = o.customer_id
WHERE o.amount > 150;

Explanation:

INNER JOIN matches customers to orders.
WHERE clause filters orders with amount > 150.
Returns first name, order ID, and amount for qualifying orders.

Expected Output:

first_name	order_id	amount
Alice	102	300.00
Bob	103	200.00

4. JOIN Multiple Tables

Question: Retrieve customer first name, order date, product name, and quantity by joining Customers, Orders, OrderDetails, and Products.

Query:

SELECT c.first_name, o.order_date, p.product_name, od.quantity
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
JOIN OrderDetails od ON o.order_id = od.order_id
JOIN Products p ON od.product_id = p.product_id;

Explanation:

Chains JOINs to connect all four tables.
Matches: Customers → Orders (via customer_id), Orders → OrderDetails (via order_id), OrderDetails → Products (via product_id).
Retrieves first name, order date, product name, and quantity.

Expected Output:

first_name	order_date	product_name	quantity
Alice	2023-01-15	Laptop	5
Alice	2023-01-15	Mouse	2
Alice	2023-02-20	Keyboard	3
Bob	2023-03-10	Laptop	4

5. SQL INNER JOIN

Question: List customers with at least one order, showing first name and total number of orders using INNER JOIN and GROUP BY.

Query:

SELECT c.first_name, COUNT(o.order_id) AS order_count
FROM Customers c
INNER JOIN Orders o
ON c.customer_id = o.customer_id
GROUP BY c.first_name;

Explanation:

INNER JOIN excludes customers without orders.
GROUP BY groups results by first_name.
COUNT calculates the number of orders per customer.

Expected Output:

first_name	order_count
Alice	2
Bob	1
Charlie	1

6. SQL LEFT JOIN

Question: List all customers and their order IDs, including those without orders, using LEFT JOIN.

Query:

SELECT c.first_name, o.order_id
FROM Customers c
LEFT JOIN Orders o
ON c.customer_id = o.customer_id;

Explanation:

LEFT JOIN includes all customers, even those without orders.
Non-matching orders return NULL for order_id.
Returns first name and order ID.

Expected Output:

first_name	order_id
Alice	101
Alice	102
Bob	103
Charlie	104
David	NULL

7. SQL RIGHT JOIN

Question: List all orders and associated customer names, including orders without matching customers, using RIGHT JOIN.

Query:

SELECT c.first_name, o.order_id
FROM Customers c
RIGHT JOIN Orders o
ON c.customer_id = o.customer_id;

Explanation:

RIGHT JOIN includes all orders, with NULL for non-matching customers.
In this dataset, all orders have matching customers, so no NULLs appear.
Returns first name and order ID.

Expected Output:

first_name	order_id
Alice	101
Alice	102
Bob	103
Charlie	104

8. SQL FULL OUTER JOIN

Question: List all customers and orders, including those without matches, using FULL OUTER JOIN. If not supported, describe emulation.

Query:

SELECT c.first_name, o.order_id
FROM Customers c
FULL OUTER JOIN Orders o
ON c.customer_id = o.customer_id;

Explanation:

FULL OUTER JOIN includes all customers and orders, with NULLs for non-matches.
In this dataset, David has no orders, so his order_id is NULL.
If FULL OUTER JOIN is not supported (e.g., MySQL), emulate with:

Emulation Query (for databases like MySQL):

SELECT c.first_name, o.order_id
FROM Customers c
LEFT JOIN Orders o
ON c.customer_id = o.customer_id
UNION
SELECT c.first_name, o.order_id
FROM Customers c
RIGHT JOIN Orders o
ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

Expected Output:

first_name	order_id
Alice	101
Alice	102
Bob	103
Charlie	104
David	NULL

9. SQL SELF JOIN

Question: Display each employee’s first name and their manager’s first name using a SELF JOIN.

Query:

SELECT e1.first_name AS employee, e2.first_name AS manager
FROM Employees e1
LEFT JOIN Employees e2
ON e1.manager_id = e2.employee_id;

Explanation:

SELF JOIN joins Employees with itself.
LEFT JOIN ensures employees without managers (e.g., Emma) are included.
Aliases e1 and e2 distinguish the employee and manager instances.

Expected Output:

employee	manager
Emma	NULL
Liam	Emma
Olivia	Emma
Noah	Liam

10. SQL CROSS JOIN

Question: Generate all combinations of customers and products with price > 500, showing first name and product name.

Query:

SELECT c.first_name, p.product_name
FROM Customers c
CROSS JOIN Products p
WHERE p.price > 500;

Explanation:

CROSS JOIN pairs every customer with every product.
WHERE clause filters products with price > 500 (only Laptop).
Returns customer first name and product name.

Expected Output:

first_name	product_name
Alice	Laptop
Bob	Laptop
Charlie	Laptop
David	Laptop

11. Practical Scenario (Multiple JOIN Types)

Question: List USA customers, their order IDs, and amounts (> 100) using LEFT JOIN.

Query:

SELECT c.first_name, o.order_id, o.amount
FROM Customers c
LEFT JOIN Orders o
ON c.customer_id = o.customer_id
WHERE c.country = 'USA' AND (o.amount > 100 OR o.amount IS NULL);

Explanation:

LEFT JOIN includes all USA customers, even without orders.
WHERE filters for country = 'USA' and orders with amount > 100 or NULL (for customers without orders).
Returns first name, order ID, and amount.

Expected Output:

first_name	order_id	amount
Alice	101	150.00
Alice	102	300.00
David	NULL	NULL

12. JOIN vs. Nested Queries

Question: Find customers who ordered 'Laptop' using JOIN and subquery. Compare approaches.

JOIN Query:

SELECT DISTINCT c.first_name
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
JOIN OrderDetails od ON o.order_id = od.order_id
JOIN Products p ON od.product_id = p.product_id
WHERE p.product_name = 'Laptop';

Subquery Query:

SELECT first_name
FROM Customers
WHERE customer_id IN (
    SELECT o.customer_id
    FROM Orders o
    JOIN OrderDetails od ON o.order_id = od.order_id
    JOIN Products p ON od.product_id = p.product_id
    WHERE p.product_name = 'Laptop'
);

Expected Output (Both Queries): ``` first_name

Alice Bob


**Comparison**:
- **Readability**: JOIN is clearer for multi-table queries, as relationships are explicit. Subquery breaks logic into steps, which may be intuitive for simpler tasks but less readable for complex joins.
- **Performance**: JOIN is typically faster, as the database optimizes the join operation. The subquery may execute the inner query separately, especially if not optimized. For large datasets, JOIN is preferred.
- **Recommendation**: Use JOIN for this scenario due to clarity and performance.

---

#### 13. Advanced JOIN with Aggregation
**Question**: Calculate total order amount per customer, including those without orders, using LEFT JOIN.

**Query**:
```sql
SELECT c.first_name, COALESCE(SUM(o.amount), 0) AS total_amount
FROM Customers c
LEFT JOIN Orders o
ON c.customer_id = o.customer_id
GROUP BY c.first_name;

Explanation:

LEFT JOIN includes all customers.
SUM aggregates order amounts; COALESCE converts NULL to 0 for customers without orders.
GROUP BY groups by first_name.

Expected Output:

first_name	total_amount
Alice	450.00
Bob	200.00
Charlie	100.00
David	0.00

14. Complex Multiple Table JOIN

Question: Find total quantity of each product ordered by UK customers.

Query:

SELECT p.product_name, SUM(od.quantity) AS total_quantity
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
JOIN OrderDetails od ON o.order_id = od.order_id
JOIN Products p ON od.product_id = p.product_id
WHERE c.country = 'UK'
GROUP BY p.product_name;

Explanation:

Joins connect Customers (filtered for UK), Orders, OrderDetails, and Products.
SUM calculates total quantity per product.
GROUP BY groups by product_name.

Expected Output:

product_name	total_quantity
Laptop	4

15. SELF JOIN with WHERE Clause

Question: Find pairs of employees with the same manager, showing their names and manager’s name.

Query:

SELECT e1.first_name AS employee1, e2.first_name AS employee2, m.first_name AS manager
FROM Employees e1
JOIN Employees e2 ON e1.manager_id = e2.manager_id AND e1.employee_id < e2.employee_id
JOIN Employees m ON e1.manager_id = m.employee lea_id;

Explanation:

SELF JOIN on Employees matches employees with the same manager_id.
e1.employee_id < e2.employee_id prevents duplicate pairs (e.g., Liam-Olivia vs. Olivia-Liam).
JOIN with Employees (as m) gets manager’s name.
Excludes Emma (no manager) since the JOIN requires a valid manager_id.

Expected Output:

employee1	employee2	manager
Liam	Olivia	Emma

16. CROSS JOIN for Combinations

Question: Pair every customer with products priced under 100, showing first name and product name.

Query:

SELECT c.first_name, p.product_name
FROM Customers c
CROSS JOIN Products p
WHERE p.price < 100;

Explanation:

CROSS JOIN creates all customer-product pairs.
WHERE filters for products with price < 100 (Mouse, Keyboard).
Returns first name and product name.

Expected Output:

first_name	product_name
Alice	Mouse
Alice	Keyboard
Bob	Mouse
Bob	Keyboard
Charlie	Mouse
Charlie	Keyboard
David	Mouse
David	Keyboard

17. Emulating FULL OUTER JOIN

Question: Emulate FULL OUTER JOIN to list all customers and orders, including unmatched rows.

Query:

SELECT c.first_name, o.order_id
FROM Customers c
LEFT JOIN Orders o
ON c.customer_id = o.customer_id
UNION
SELECT c.first_name, o.order_id
FROM Customers c
RIGHT JOIN Orders o
ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

Explanation:

LEFT JOIN gets all customers, including those without orders.
RIGHT JOIN gets all orders, filtering for unmatched orders (though none exist here).
UNION combines results, removing duplicates.
Mimics FULL OUTER JOIN behavior.

Expected Output:

first_name	order_id
Alice	101
Alice	102
Bob	103
Charlie	104
David	NULL

18. Practical Business Question

Question: Report all orders with customer first name, order date, and total order value (quantity * price).

Query:

SELECT c.first_name, o.order_date, SUM(od.quantity * p.price) AS total_value
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
JOIN OrderDetails od ON o.order_id = od.order_id
JOIN Products p ON od.product_id = p.product_id
GROUP BY c.first_name, o.order_date, o.order_id;

Explanation:

Joins connect all four tables.
quantity * price calculates the value per order detail.
SUM aggregates total value per order.
GROUP BY includes order_id to ensure unique orders.

Expected Output:

first_name	order_date	total_value
Alice	2023-01-15	5040.00
Alice	2023-02-20	150.00
Bob	2023-03-10	4000.00

19. JOIN with Sorting and Limiting

Question: List top 3 customers by total order amount, showing first name and total amount.

Query:

SELECT c.first_name, SUM(o.amount) AS total_amount
FROM Customers c
INNER JOIN Orders o
ON c.customer_id = o.customer_id
GROUP BY c.first_name
ORDER BY total_amount DESC
LIMIT 3;

Explanation:

INNER JOIN includes only customers with orders.
SUM calculates total order amount per customer.
ORDER BY sorts by total amount (descending).
LIMIT restricts to top 3.

Expected Output:

first_name	total_amount
Alice	450.00
Bob	200.00
Charlie	100.00

20. Subquery Alternative to LEFT JOIN

Question: Rewrite Question 6 (list all customers and order IDs) using a subquery. Compare approaches.

Subquery Query:

SELECT c.first_name, (
    SELECT o.order_id
    FROM Orders o
    WHERE o.customer_id = c.customer_id
    LIMIT 1
) AS order_id
FROM Customers c;

Explanation:

Subquery retrieves an order_id for each customer.
LIMIT 1 ensures one order per customer (note: this doesn’t list all orders like the LEFT JOIN).
To match the LEFT JOIN fully, we’d need a correlated subquery with multiple rows, which is complex.

Expected Output (Partial, as it shows one order per customer):

first_name	order_id
Alice	101
Bob	103
Charlie	104
David	NULL

Comparison:

Clarity: LEFT JOIN (Question 6) is clearer, as it naturally handles multiple orders per customer. The subquery is less intuitive and requires additional logic for multiple rows.
Performance: LEFT JOIN is more efficient, as it’s optimized by the database. The correlated subquery executes for each customer, potentially slowing down with large datasets.
Recommendation: Use LEFT JOIN for this scenario due to simplicity and performance.

Notes

Correctness: All queries were designed to produce the expected output based on the sample data. You can test them by running the table creation and insert scripts provided earlier.
Performance: Queries use JOINs where possible for efficiency. Indexes on customer_id, order_id, and product_id would improve performance in a real database.
Edge Cases: Handled cases like customers without orders (LEFT JOIN), employees without managers (SELF JOIN), and databases without FULL OUTER JOIN support.
Comparisons: For JOIN vs. subquery questions (12, 20), JOINs are generally preferred for multi-table queries due to readability and optimization.

Database SQL

FOX Academy

DB --> LEC 8 -->Practical_Questions

Sample Database Schema

Database Schema and Sample Data Recap

Solutions to Practical Questions

1. Basic SQL JOIN

2. SQL JOIN With AS Alias

3. JOIN With WHERE Clause

4. JOIN Multiple Tables

5. SQL INNER JOIN

6. SQL LEFT JOIN

7. SQL RIGHT JOIN

8. SQL FULL OUTER JOIN

9. SQL SELF JOIN

10. SQL CROSS JOIN

11. Practical Scenario (Multiple JOIN Types)

12. JOIN vs. Nested Queries

Expected Output (Both Queries): ``` first_name

14. Complex Multiple Table JOIN

15. SELF JOIN with WHERE Clause

16. CROSS JOIN for Combinations

17. Emulating FULL OUTER JOIN

18. Practical Business Question

19. JOIN with Sorting and Limiting

20. Subquery Alternative to LEFT JOIN

Notes

You may like these posts

Also Like

DB --> LEC 8 -->Practical_Questions

Sample Database Schema

Database Schema and Sample Data Recap

Solutions to Practical Questions

1. Basic SQL JOIN

2. SQL JOIN With AS Alias

3. JOIN With WHERE Clause

4. JOIN Multiple Tables

5. SQL INNER JOIN

6. SQL LEFT JOIN

7. SQL RIGHT JOIN

8. SQL FULL OUTER JOIN

9. SQL SELF JOIN

10. SQL CROSS JOIN

11. Practical Scenario (Multiple JOIN Types)

12. JOIN vs. Nested Queries

Expected Output (Both Queries): ``` first_name

14. Complex Multiple Table JOIN

15. SELF JOIN with WHERE Clause

16. CROSS JOIN for Combinations

17. Emulating FULL OUTER JOIN

18. Practical Business Question

19. JOIN with Sorting and Limiting

20. Subquery Alternative to LEFT JOIN

Notes

You may like these posts