IS5413 Database Management Systems
Lab Tutorial 2 – Advanced SQL
Objectives:
1. Select Statement with Aggregation Function
2. Inner Join & Left Outer Join
Prerequisites:
SQL Knowledge:
o Create Tables and Relationships
o Insert, Update & Delete Records
o Basic Select Statements
Environment:
o SQL Server Management Studio
o Login to Assigned Database (Server name: 144.214.55.157)
1
Start SQL Server Management Studio
Server type: Database Engine
Server name: 144.214.55.157
Authentication: SQL Server Authentication
Login: Provided through email
Password: Provided through email
2
1. Select Statement with Aggregation Function
Here are some common SQL aggregation functions:
Function Description
MIN( ) Returns the smallest value in a given column
MAX( ) Returns the largest value in a given column
SUM( ) Returns the sum of the numeric values in a given column
AVG( ) Returns the average value of a given column
COUNT( ) Returns the number of selected records
Reference: W3Schools (https://www.w3schools.com/sql/sql_join.asp)
Exercise 1
We will use the following table and records for demonstration:
CREATE TABLE SalesRecord (
SalesID VARCHAR(6) not null PRIMARY KEY,
ProductID VARCHAR(6) not null,
ProductName VARCHAR(100) not null,
QuantitySold INT not null,
UnitPrice DECIMAL(3,1) not null,
CustomerID VARCHAR(6) not null
);
INSERT INTO SalesRecord VALUES ('S001', 'P001', 'Coca Cola', 100, 5, 'C001');
INSERT INTO SalesRecord VALUES ('S002', 'P002', 'Seven-up', 120, 5.5, 'C002');
INSERT INTO SalesRecord VALUES ('S003', 'P003', 'Pepsi', 80, 6.0, 'C001');
INSERT INTO SalesRecord VALUES ('S004', 'P004', 'Fanta', 70, 5, 'C003');
INSERT INTO SalesRecord VALUES ('S005', 'P001', 'Coca Cola', 110, 5, 'C001');
INSERT INTO SalesRecord VALUES ('S006', 'P003', 'Pepsi', 95, 6.0, 'C002');
INSERT INTO SalesRecord VALUES ('S007', 'P001', 'Coca Cola', 12, 5, 'C004');
INSERT INTO SalesRecord VALUES ('S008', 'P002', 'Seven-up', 20, 5.5, 'C005');
INSERT INTO SalesRecord VALUES ('S009', 'P003', 'Pepsi', 60, 6.0, 'C002');
INSERT INTO SalesRecord VALUES ('S010', 'P004', 'Fanta', 24, 5, 'C004');
INSERT INTO SalesRecord VALUES ('S011', 'P001', 'Coca Cola', 24, 5, 'C001');
INSERT INTO SalesRecord VALUES ('S012', 'P003', 'Pepsi', 60, 6.0, 'C004');
3
A. What is the total number of sales records?
select count(*) as NumberOfRecords
from SalesRecord;
B. What is the total and average sales amount of those sales records?
select sum(QuantitySold * UnitPrice) as TotalSalesAmount, avg(QuantitySold * UnitPrice)
as AvgSalesAmount
from SalesRecord;
C. What are the total number of products sold for each product? Order by the total number of products sold in
descending order.
select ProductID, ProductName, sum(QuantitySold) as TotalNumSold
from SalesRecord
group by ProductID, ProductName
order by 3 desc;
D. How many sales records does each customer have? Order by the CustomerID in ascending order.
select CustomerID, count(*) as NumOfSales
from SalesRecord
group by CustomerID
order by 1;
E. Use queries to answer the following questions.
1). How many sales records belong to customer C001?
select count(*) as NumberOfRecords
from SalesRecord
where CustomerID ='C001';
2). What is the highest sales amount in the sales record?
select max(QuantitySold * UnitPrice) as HighestAmount
from SalesRecord;
4
2. Inner Join & Left Outer Join
Here are the different types of the JOINs in SQL:
(INNER) JOIN: Returns records that have matching values in both tables
LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table
Inner Join
Inner Join returns records that have matching values in both tables.
Note: When no instruction was given to SQL,
usually, it will automatically use Inner Join.
Inner Join Syntax:
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
Inner Join Example:
select e.EmployeeID, c.ContactID, c.EmailAddress
from Employee as e
inner join Contact as c
on e.ContactID = c.ContactID;
5
Exercise 2
drop table Enrollment;
drop table Course;
drop table Student;
create table Course (
CID varchar(6) not null primary key,
Name varchar(100) not null,
Credit int not null
);
create table Student (
SID varchar(8) not null primary key,
Name varchar(50) not null,
Phone int
);
create table Enrollment (
EID varchar(6) not null primary key,
SID varchar(8) not null foreign key references Student(SID),
CID varchar(6) not null foreign key references Course(CID),
Date datetime not null,
Status varchar(1) not null
check (Status IN ('S', 'N', 'P'))
);
insert into Course values ('IS3331', 'Data Management', 3);
insert into Course values ('IS3232', 'Knowledge Management', 3);
insert into Course values ('CB2022', 'Business Programming', 3);
insert into Course values ('CB2500', 'Information Systems', 2);
insert into Course values ('GE0010', 'Japanese', 2);
insert into Course values ('IS6400', 'Business Data Analytics', 3);
insert into Course values ('GE0001', 'Internet', 2);
insert into Student values ('50123456', 'Weiwei', 27888514);
insert into Student values ('50123457', 'Julie', 21942329);
6
insert into Student values ('50123458', 'Tina', 27889972);
insert into Student values ('50123450', 'Wendy', 97888514);
insert into Student values ('50123451', 'John', 61942329);
insert into Student values ('50123452', 'Tim', 37889972);
insert into Student values ('50123453', 'Tony', 67888514);
insert into Student values ('50123454', 'Wang', 91942329);
insert into Student values ('50123455', 'Joy', 20889972);
insert into Enrollment values ('1','50123456', 'IS3331', '2007-10-20',
'P');
insert into Enrollment values ('2','50123457', 'IS3331', '2007-10-20',
'P');
insert into Enrollment values ('3','50123458', 'IS3331', '2007-10-20',
'P');
insert into Enrollment values ('4','50123456', 'IS3232', '2006-10-20',
'P');
insert into Enrollment values ('5','50123458', 'IS3232', '2006-10-20',
'P');
insert into Enrollment values ('9','50123456', 'CB2500', '2009-10-20',
'P');
insert into Enrollment values ('10','50123458', 'CB2500', '2010-10-20',
'P');
insert into Enrollment values ('11','50123451', 'IS3331', '2010-10-20',
'P');
insert into Enrollment values ('12','50123452', 'IS3331', '2009-10-20',
'S');
insert into Enrollment values ('13','50123453', 'GE0001', '2010-10-20',
'P');
insert into Enrollment values ('14','50123457', 'IS3232', '2009-10-20',
'P');
insert into Enrollment values ('15','50123458', 'GE0001', '2009-10-20',
'S');
A: Write a query to find all the students enrolled in CB Courses, and display the student ID, name and enrollment
date.
select s.SID, s.Name, e.Date
from Enrollment as e
inner join Student as s
on s.SID = e.SID
7
where e.CID like 'CB%';
Alternatively:
select s.SID, s.Name, e.Date
from Enrollment as e, Student as s
where s.SID = e.SID
and (e.CID like 'CB%');
B: Write a query to find all the enrollment records of the student with student ID 50123456, and display course ID,
course Name, and the enrollment date.
select c.CID, c.Name, e.Date
from Student as s
join Enrollment as e
on s.SID = e.SID
join Course as c
on e.CID = c.CID
where s.SID = '50123456';
Alternatively:
select c.CID, c.Name, e.Date
from Enrollment as e, Course as c, Student as s
where s.SID = '50123456'
and s.SID = e.SID
and e.CID = c.CID
8
Left Outer Join
Left Outer Join returns all records from the left table, and the matched records
from the right table.
Left Outer Join Syntax:
SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name = table2.column_name;
Exercise 3 Take a screen capture of the query result and paste it to
your MS file.
Write a query to find out students who don’t have any enrollment records.
select s.Name, e.EID
from Student as s
left outer join Enrollment as e
on s.SID = e.SID
where e.EID is null
9
Reference:
Modern Database Management, 10th Edition, Chapter 6, 7
http://msdn2.microsoft.com/en-us/library/ms189826.aspx
https://www.w3schools.com/sql/default.asp
End of Tutorial
10
