CSC371-Database Systems I Lecture-9
(Lab)
(Spring2020)
Abdul Qayyum aqayyum@[Link]
Samia Arshad samiaarshad@[Link]
Faisal Mumtaz faisalmumtaz@[Link]
1
Previous Lecture Review
SQL Update
SQL Delete
SQL Select Top
SQL ORDER BY
SQL Aliases
SQL MIN() and MAX() Functions
SQL COUNT(), AVG() and SUM() Functions
2
Branch
Staff
3
Agenda
SQL Group By Clause
SQL Having Clause
SQL Views
4
GROUP BY Syntax
Groups rows that have the same values into summary rows
Find the number of staff members in each branch
It is often used with aggregate functions to group the result-set by one
or more columns.
Used COUNT, MAX, MIN, SUM, AVG functions.
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
5
Show branch wise data from staff table?
Select * from staff group by branchno
Select * from staff order by branchno
6
Use of Group by with Count function
Count how many employees in each branch?
SELECT branchno, COUNT(staffno) AS [Staff in each Branch]
FROM staff
GROUP BY branchno;
7
Group By with ORDER BY Clause
SELECT branchno, COUNT(staffno) AS [Staff in each Branch]
FROM staff
GROUP BY branchno
ORDER BY COUNT(staffno) ASC;
SELECT branchno, COUNT(staffno) AS [Staff in each Branch]
FROM staff
GROUP BY branchno
ORDER BY branchno DESC;
8
Can you find out the average salary of Assistants in each branch?
SELECT branchno, Avg(salary) AS [Average salary of Assistants in each Branch]
FROM staff
Where position = 'assistant'
GROUP BY branchno
ORDER BY branchno DESC;
9
Can you generate a report for branches where
more than two employees are working?
select branchNo, count(staffno) As [Total Employee]
From staff
where count(staffno) > 2
Group by branchno
10
The SQL HAVING Clause
The HAVING clause was added to SQL because the WHERE
keyword could not be used with aggregate functions.
Applied condition on group data
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
11
Can we find out the branch number with more than two
employees
select branchNo, count(staffno) As [Total Employee]
From staff
Group by branchno;
Having count(staffno) > 2
12
select branchNo, count(staffno) As [Total Employee], Avg(salary) AS [Average
Salary]
From staff
Where salary >= 9000
Group by branchno
Having count(staffno) > 1
Order By count(staffno) DESC;
13
select branchNo, count(staffno) As [Total Employee], Avg(salary) AS [Average
Salary]
From staff
Where salary > 10000
Group by branchno
Having count(staffno) > 1;
Is the Result correct?
If yes How?
If Not Why?
14
SQL CREATE VIEW Statement
Virtual table based on the result-set of an SQL statement.
The fields in a view are fields from one or more base/real tables in the
database.
Can add SQL functions, WHERE, JOIN statements and sub-query.
Fetch data from many tables to a view and present the data as if the data
were coming from one single table.
Security Purpose
Reporting Services
15
SQL CREATE VIEW
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
CREATE VIEW [Branch_B003] AS
SELECT staffno, fname, salary
FROM staff
WHERE branchno = ‘B003’’;
select * from branch_B003
16
SQL CREATE OR REPLACE VIEW
CREATE OR ALTER VIEW [Branch_B003] AS
SELECT staffno, fname, lname,salary
FROM staff
WHERE branchno = ‘B003’’;
select * from branch_B003
17
Drop View
DROP VIEW [branch_b003];
18
Summary
SQL Group By Clause
SQL Having Clause
SQL Views
19
