Scribd
Upload
3 views9 pages

Advanced SQL Queries with Examples

The document provides advanced SQL query examples using an employee table, demonstrating various SQL operations such as GROUP BY, WHERE, HAVING, nested SELECT, INNER JOIN, LEFT JOIN, and UNION. Each section includes SQL queries along with their corresponding outputs, showcasing how to manipulate and retrieve data effectively. Key insights include employee counts and average salaries by department, filtering based on conditions, and joining tables to display related information.

Uploaded by

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

Advanced SQL Queries with Examples

The document provides advanced SQL query examples using an employee table, demonstrating various SQL operations such as GROUP BY, WHERE, HAVING, nested SELECT, INNER JOIN, LEFT JOIN, and UNION. Each section includes SQL queries along with their corresponding outputs, showcasing how to manipulate and retrieve data effectively. Key insights include employee counts and average salaries by department, filtering based on conditions, and joining tables to display related information.

Uploaded by

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

Advanced SQL Queries with

Examples & Outputs


Group By | Where | Having | Nested
Select | Joins | Union
Sample Employee Table (20 Rows)

• SQL Query:
• Employee table used for all queries:
•
Output:
• EmpID Name Department Salary JoiningDate
1 Alice HR 50000 2022-01-15
2 Bob IT 60000 2021-03-22
3 Charlie Finance 55000 2020-06-10
4 David IT 65000 2021-07-19
5 Eva Finance 70000 2019-08-25
6 Frank HR 48000 2023-02-05
7 Grace IT 72000 2022-11-30
8 Hannah Finance 67000 2021-12-17
9 Ian IT 58000 2020-09-08
10 Jack HR 52000 2022-05-14
11 Karen Finance 75000 2018-11-21
12 Leo IT 80000 2019-01-19
13 Mona Finance 71000 2020-03-10
14 Nina IT 64000 2023-04-15
15 Oscar HR 53000 2022-09-28
16 Paul Finance 69000 2021-10-05
17 Quinn IT 61000 2020-02-18
18 Rita HR 56000 2023-06-22
19 Sam Finance 78000 2019-07-11
20 Tina IT 75000 2021-08-03
GROUP BY with Aggregate

• SQL Query:
• SELECT Department, COUNT(*) AS EmpCount, AVG(Salary) AS AvgSalary
FROM Employee
GROUP BY Department;
•
Output:
• Department EmpCount AvgSalary
Finance 7 69285.714286
HR 5 51800.000000
IT 8 66875.000000
WHERE Clause

• SQL Query:
• SELECT * FROM Employee
WHERE Department = 'IT' AND Salary > 60000;
•
Output:
• EmpID Name Department Salary JoiningDate
4 David IT 65000 2021-07-19
7 Grace IT 72000 2022-11-30
12 Leo IT 80000 2019-01-19
14 Nina IT 64000 2023-04-15
17 Quinn IT 61000 2020-02-18
20 Tina IT 75000 2021-08-03
HAVING Clause

• SQL Query:
• SELECT Department, COUNT(*) AS EmpCount
FROM Employee
GROUP BY Department
HAVING COUNT(*) > 3;
•
Output:
• Department EmpCount
Finance 7
HR 5
IT 8
Nested SELECT

• SQL Query:
• SELECT Name, Salary FROM Employee
WHERE Salary > (SELECT AVG(Salary) FROM Employee);
•
Output:
• Name Salary
David 65000
Eva 70000
Grace 72000
Hannah 67000
Karen 75000
Leo 80000
Mona 71000
Nina 64000
Paul 69000
Sam 78000
Tina 75000
INNER JOIN Example

• SQL Query:
• SELECT [Link], [Link] FROM Employee E
INNER JOIN Department D ON [Link] = [Link];
•
Output:
• Name Location
Alice New York
Frank New York
Jack New York
Oscar New York
Rita New York
Bob San Francisco
David San Francisco
Grace San Francisco
Ian San Francisco
Leo San Francisco
Nina San Francisco
Quinn San Francisco
Tina San Francisco
Charlie Chicago
Eva Chicago
Hannah Chicago
Karen Chicago
Mona Chicago
Paul Chicago
Sam Chicago
LEFT OUTER JOIN Example

• SQL Query:
• SELECT [Link], [Link] FROM Employee E
LEFT JOIN Department D ON [Link] = [Link];
•
Output:
• Name Location
Alice New York
Bob San Francisco
Charlie Chicago
David San Francisco
Eva Chicago
Frank New York
Grace San Francisco
Hannah Chicago
Ian San Francisco
Jack New York
Karen Chicago
Leo San Francisco
Mona Chicago
Nina San Francisco
Oscar New York
Paul Chicago
Quinn San Francisco
Rita New York
Sam Chicago
Tina San Francisco
UNION Example

• SQL Query:
• SELECT Name FROM Employee WHERE Department = 'IT'
UNION
SELECT Name FROM Employee WHERE Department = 'HR';
•
Output:
• Name
Bob
David
Grace
Ian
Leo
Nina
Quinn
Tina
Alice
Frank
Jack
Oscar
Rita

You might also like