SQL_Clauses_Joins for mtech students.pptx

ppt for mtech students

1. Use of SQL Clauses and JOIN Objective: Understand GROUP BY, HAVING, and execute SQL commands using JOIN

2. GROUP BY Clause • Groups rows based on column values. • Often used with aggregate functions (COUNT, SUM, AVG). Example: SELECT department, COUNT(*) FROM employees GROUP BY department;

3. HAVING Clause • Filters results after GROUP BY. • Similar to WHERE but for aggregated data. Example: SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5;

4. JOIN Overview • Combines rows from multiple tables. Types of JOIN: – INNER JOIN – LEFT JOIN – RIGHT JOIN – FULL OUTER JOIN

5. INNER JOIN Example Example: SELECT e.name, d.department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id; 👉 Returns only matching rows.

6. LEFT JOIN Example Example: SELECT e.name, d.department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id; 👉 Returns all employees even if no department.

7. Combining GROUP BY, HAVING, and JOIN Example: SELECT d.department_name, COUNT(e.emp_id) FROM employees e JOIN departments d ON e.department_id = d.department_id GROUP BY d.department_name HAVING COUNT(e.emp_id) > 3; 👉 Shows departments with more than 3 employees.

8. Conclusion • GROUP BY organizes rows into groups. • HAVING filters aggregated results. • JOIN combines data from multiple tables. • Together they help in powerful data analysis.