IS5413 Database Management Systems
Lab Tutorial 3 – Advanced SQL
Objectives:
Select Statement with Aggregation Function
Inner Join & Left Outer Join
Lab 3 (This week): Select Statement with= Subqueries
Lab 4 (Next week): View (Create, Alter)
Prerequisites:
SQL Knowledge:
o Create Tables and Relationships
o Insert, Update & Delete Records
o Basic Select Statements
o Joint search across tables
Environment:
o SQL Server Management Studio
o Login to Assigned Database (Server name: 144.214.55.157)
o DB with Course, Student and Enrollment tables from previous tutorial (Lab 2)
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
Lab3Prerequisite.sql
-- Prerequisite for Lab 3 Environment
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 ('IS5413', '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);
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);
3
insert into Enrollment values ('1','50123456', 'IS5413', '2007-10-20', 'P');
insert into Enrollment values ('2','50123457', 'IS5413', '2007-10-20', 'P');
insert into Enrollment values ('3','50123458', 'IS5413', '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', 'IS5413', '2010-10-20', 'P');
insert into Enrollment values ('12','50123452', 'IS5413', '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');
4
Select Statement with Subqueries
In SQL, a subquery is a query within another query.
A subquery can reside in the WHERE clause, the FROM clause, or the SELECT clause of another query.
Subquery in WHERE clause:
SELECT column(s)
FROM table(s)
WHERE column OPERATOR (SELECT column
FROM table(s)
WHERE condition(s));
Subquery in FROM clause:
SELECT column(s)
FROM table(s), (SELECT column(s)
FROM table(s)
WHERE condition(s)) AS alias
WHERE condition(s);
Subquery in SELECT clause:
SELECT column(s), (SELECT column
FROM table(s)
WHERE condition(s)) AS alias
FROM table(s)
WHERE condition(s);
Exercise 1: Subquery
Non-correlated Subquery
Write a query to find out the name, student ID of the student enrolled the course ‘IS5413’ or
‘CB2500’.
5
The result should look like the following:
Hint: You need to use data from tables: Student and Enrollment
SELECT Name, SID
FROM Student
WHERE SID IN (
SELECT SID
FROM Enrollment
WHERE CID = 'IS5413' OR CID = 'CB2500')
Alternatively:
SELECT DISTINCT Student.Name, Student.SID
FROM Student JOIN Enrollment
ON Student.SID = Enrollment.SID
WHERE Enrollment.CID = 'IS5413' OR Enrollment.CID = 'CB2500'
6
Write a query to find out the name, student ID of the student enrolled the course ‘IS5413’ or
‘CB2500’ with Course ID.
The result should look like the following:
Hint: You need to use data from tables: Student and Enrollment
SELECT S.Name, S.SID, E.CID
FROM Student AS S,
(SELECT SID, CID
FROM Enrollment
WHERE CID = 'IS5413' OR CID = 'CB2500') AS E
WHERE S.SID = E.SID
Alternatively:
SELECT S.Name, S.SID, E.CID
FROM Student AS S JOIN Enrollment AS E
ON S.SID = E.SID
WHERE E.CID = 'IS5413' OR E.CID = 'CB2500'
Write a query to find out the total number of courses enrolled for each student.
7
The result should look like the following:
Hint: You need to use data from table: Student and Enrollment
SELECT S.Name,
(SELECT COUNT(*)
FROM Enrollment AS E
WHERE S.SID = E.SID) AS CNUM
FROM Student AS S
Alternatively:
SELECT S.Name, COUNT(EID) AS CNUM
FROM Student AS S LEFT JOIN Enrollment AS E
ON S.SID = E.SID
GROUP BY S.Name
8
Exercise
Non-correlated Subquery (Do not depend on data from the outer query, execute once only)
A: Write a query to find out the student ID and name of all the students who have enrolled in a CB or GE course.
SELECT S.SID, S.Name
FROM Student AS S
WHERE S.SID IN (
SELECT E.SID
FROM Enrollment AS E
WHERE E.CID LIKE 'CB%' OR E.CID LIKE 'GE%'
);
B: Write a query to find out the enrollment records of course named “Data Management”. Display the Student ID,
Student Name, Course ID, and the enrollment date of the records.
SELECT E.SID, S.Name, E.CID, E.Date
FROM Enrollment AS E
JOIN Student AS S ON E.SID = S.SID
WHERE E.CID = (SELECT CID FROM Course WHERE Name = 'Data Management');
Correlated Subquery (Make use of data from the outer query, execute row by row)
C: Write a query to find out the total number of courses enrolled by each student:
SELECT S.Name,
(SELECT COUNT(*)
FROM Enrollment AS E
WHERE S.SID = E.SID) AS CNUM
FROM Student AS S;
Reference:
Modern Database Management, 10th Edition, Chapter 6, 7
http://msdn2.microsoft.com/en-us/library/ms189826.aspx
9
https://www.w3schools.com/sql/default.asp
End of Tutorial
10
