**Employees Table:**
| employee_id | name | job_id | manager_id | department_id | location_id |
|-------------|--------|--------|------------|---------------|-------------|
| 1 | Arjun | 101 | NULL | 1 | 1 |
| 2 | Priya | 102 | 1 | 1 | 1 |
| 3 | Dev | 103 | 1 | 2 | 2 |
| 4 | Kavya | 104 | 2 | 1 | 1 |
| 5 | Aarav | 105 | 3 | 2 | 2 |
**Departments Table:**
| department_id | department_name | location_id |
|---------------|-----------------|-------------|
| 1 | Resources | 1 |
| 2 | Finance | 2 |
**Jobs Table:**
| job_id | job_title |
|--------|------------|
| 101 | Manager |
| 102 | Analyst |
| 103 | Clerk |
| 104 | Assistant |
| 105 | Director |
**Locations Table:**
| location_id | city |
|-------------|----------|
| 1 | Mumbai |
| 2 | Delhi |
1. **Joining Tables with Aggregation:**
Retrieve the total sales amount for each product along with the product name.
```sql
SELECT p.product_name, SUM(s.quantity * s.unit_price) AS total_sales_amount
FROM products p
JOIN sales s ON p.product_id = s.product_id
GROUP BY p.product_name;
```
2. **Combining Multiple Joins:**
Retrieve the names of customers along with their orders and the products they ordered.
```sql
SELECT c.customer_name, o.order_id, p.product_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_details od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id;
```
3. **Joining Tables with Filtering and Sorting:**
Retrieve the names of employees along with the names of their managers, sorted by the manager's name.
```sql
SELECT e.name AS employee_name, m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id
ORDER BY m.name;
```
4. **Joining Tables with Conditions:**
Retrieve the names of all employees who work in departments located in Mumbai.
```sql
SELECT e.name AS employee_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id
WHERE l.city = 'Mumbai';
```
5. **Self-Join for Hierarchical Data:**
Retrieve the names of employees and their respective managers recursively.
```sql
WITH RECURSIVE EmployeeHierarchy AS (
SELECT employee_id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.name, e.manager_id, eh.level + 1
FROM employees e
INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT name AS employee_name, (SELECT name FROM employees WHERE employee_id = EmployeeHierarchy.manager_id) AS manager_name, level
FROM EmployeeHierarchy;
```
Sure, here are five simple join questions for you to solve:
1. **Basic Inner Join:**
Write a query to retrieve the names of all customers along with their corresponding orders.
2. **Left Join with Null Values:**
List all employees and their respective department names. If an employee does not belong to any department, display 'No Department'.
3. **Self Join for Hierarchical Data:**
Retrieve the names of all employees along with the name of their managers.
4. **Joining Multiple Tables:**
Fetch the names of all products, their categories, and the suppliers.
5. **Using Aliases for Clarity:**
Retrieve the names of all students along with their course names and the instructors who teach those courses.
!
Contact us for software training, education or development
.png)
0 Comments