Scribd
Upload
7 views18 pages

Week 04 Advanced SQL Enhanced

This document outlines the content covered in Week 04 of an Advanced SQL course, focusing on aggregate functions, SQL joins, and subqueries. It includes examples of various SQL queries such as COUNT, SUM, AVG, and different types of joins like INNER, LEFT, RIGHT, and FULL JOIN. The week concludes with a revision and practice session to reinforce the concepts learned.

Uploaded by

temoor.arshad3432
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
7 views18 pages

Week 04 Advanced SQL Enhanced

This document outlines the content covered in Week 04 of an Advanced SQL course, focusing on aggregate functions, SQL joins, and subqueries. It includes examples of various SQL queries such as COUNT, SUM, AVG, and different types of joins like INNER, LEFT, RIGHT, and FULL JOIN. The week concludes with a revision and practice session to reinforce the concepts learned.

Uploaded by

temoor.arshad3432
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

WEEK 04 – ADVANCED SQL

QUERIES & JOINS


• Aggregate Functions, GROUP BY, HAVING,
Subqueries, SQL Joins
• With Example Queries & Sample Outputs
DAY 1 – Advanced SQL Overview
• Advanced Queries
• Aliases, Logical Operators
• Execution Order
SQL Execution Order
• FROM → WHERE → GROUP BY → HAVING →
SELECT → ORDER BY
DAY 2 – COUNT Function
• Query:
• SELECT COUNT(*) FROM Employees;

• Sample Output:
•5
DAY 2 – SUM Function
• Query:
• SELECT SUM(Salary) FROM Employees;

• Sample Output:
• 295000
DAY 2 – AVG Function
• Query:
• SELECT AVG(Salary) FROM Employees;

• Sample Output:
• 59000
DAY 2 – MAX & MIN
• Query:
• SELECT MAX(Salary), MIN(Salary) FROM
Employees;

• Sample Output:
• 80000 , 40000
DAY 3 – GROUP BY Example
• Query:
• SELECT Department, SUM(Salary)
• FROM Employees
• GROUP BY Department;

• Sample Output:
• IT – 130000
• HR – 85000
• Finance – 80000
DAY 3 – HAVING Example
• Query:
• SELECT Department, SUM(Salary)
• FROM Employees
• GROUP BY Department
• HAVING SUM(Salary) > 90000;

• Output:
• IT – 130000
DAY 3 – Non-Correlated Subquery
• Query:
• SELECT Name FROM Employees
• WHERE Salary > (SELECT AVG(Salary) FROM
Employees);
DAY 3 – Correlated Subquery
• Query:
• SELECT Name FROM Employees E1
• WHERE Salary >
• (SELECT AVG(Salary) FROM Employees E2
WHERE [Link] = [Link]);
DAY 4 – INNER JOIN
• Query:
• SELECT Name, Amount FROM Employees
• INNER JOIN Orders ON [Link] =
[Link];

• Output:
• Ali – 5000
• Ayesha – 3000
• Bilal – 7000
DAY 4 – LEFT JOIN
• Query:
• SELECT Name, Amount FROM Employees
• LEFT JOIN Orders ON [Link] =
[Link];
DAY 4 – RIGHT JOIN
• Query:
• SELECT Name, Amount FROM Employees
• RIGHT JOIN Orders ON [Link] =
[Link];
DAY 4 – FULL JOIN
• Query:
• SELECT Name, Amount FROM Employees
• FULL JOIN Orders ON [Link] =
[Link];
DAY 4 – SELF JOIN
• Query:
• SELECT [Link] AS Employee, [Link] AS
Manager
• FROM Employees E1
• LEFT JOIN Employees E2 ON [Link] =
[Link];
Final Revision
• Aggregates, GROUP BY, HAVING
• Subqueries
• All SQL Joins
End of Week 04
• Practice, Assessment & Lab Work

You might also like