1/81
Week 3 Workshop
2/81
Housekeeping
1 Thank you again for providing us with your valuable feedback!
2 Refer to the post in Wattle News Forum for makeup information for the
CECS teaching pause.
3 Assessment on SQL (Assignment 1) will be available on Wattle at 11:59pm
on Aug 20 (Friday) and due at 11:59pm on Sep 3 (Friday).
This assessment should be done individually and no group work is
allowed.
You should not post any solutions/results/ideas/interpretations related
to assessment items (including assignments, quizzes, tests) on the
Wattle discussion forum.
Additional drop-in sessions will be available in Week 5 if you need any
further clarification for this assignment.
4 Here are our course representatives for COMP2400/6240 in S2 2021
Julian Crosby, Julian. .au
Yixin Liu, Yixin. .au
Navdeep Gill, .au
Xueqi Lin, Xueqi. .au
2/81
Housekeeping
1 Thank you again for providing us with your valuable feedback!
2 Refer to the post in Wattle News Forum for makeup information for the
CECS teaching pause.
3 Assessment on SQL (Assignment 1) will be available on Wattle at 11:59pm
on Aug 20 (Friday) and due at 11:59pm on Sep 3 (Friday).
This assessment should be done individually and no group work is
allowed.
You should not post any solutions/results/ideas/interpretations related
to assessment items (including assignments, quizzes, tests) on the
Wattle discussion forum.
Additional drop-in sessions will be available in Week 5 if you need any
further clarification for this assignment.
4 Here are our course representatives for COMP2400/6240 in S2 2021
Julian Crosby, Julian. .au
Yixin Liu, Yixin. .au
Navdeep Gill, .au
Xueqi Lin, Xueqi. .au
2/81
Housekeeping
1 Thank you again for providing us with your valuable feedback!
2 Refer to the post in Wattle News Forum for makeup information for the
CECS teaching pause.
3 Assessment on SQL (Assignment 1) will be available on Wattle at 11:59pm
on Aug 20 (Friday) and due at 11:59pm on Sep 3 (Friday).
This assessment should be done individually and no group work is
allowed.
You should not post any solutions/results/ideas/interpretations related
to assessment items (including assignments, quizzes, tests) on the
Wattle discussion forum.
Additional drop-in sessions will be available in Week 5 if you need any
further clarification for this assignment.
4 Here are our course representatives for COMP2400/6240 in S2 2021
Julian Crosby, Julian. .au
Yixin Liu, Yixin. .au
Navdeep Gill, .au
Xueqi Lin, Xueqi. .au
2/81
Housekeeping
1 Thank you again for providing us with your valuable feedback!
2 Refer to the post in Wattle News Forum for makeup information for the
CECS teaching pause.
3 Assessment on SQL (Assignment 1) will be available on Wattle at 11:59pm
on Aug 20 (Friday) and due at 11:59pm on Sep 3 (Friday).
This assessment should be done individually and no group work is
allowed.
You should not post any solutions/results/ideas/interpretations related
to assessment items (including assignments, quizzes, tests) on the
Wattle discussion forum.
Additional drop-in sessions will be available in Week 5 if you need any
further clarification for this assignment.
4 Here are our course representatives for COMP2400/6240 in S2 2021
Julian Crosby, Julian. .au
Yixin Liu, Yixin. .au
Navdeep Gill, .au
Xueqi Lin, Xueqi. .au
2/81
Housekeeping
1 Thank you again for providing us with your valuable feedback!
2 Refer to the post in Wattle News Forum for makeup information for the
CECS teaching pause.
3 Assessment on SQL (Assignment 1) will be available on Wattle at 11:59pm
on Aug 20 (Friday) and due at 11:59pm on Sep 3 (Friday).
This assessment should be done individually and no group work is
allowed.
You should not post any solutions/results/ideas/interpretations related
to assessment items (including assignments, quizzes, tests) on the
Wattle discussion forum.
Additional drop-in sessions will be available in Week 5 if you need any
further clarification for this assignment.
4 Here are our course representatives for COMP2400/6240 in S2 2021
Julian Crosby, Julian. .au
Yixin Liu, Yixin. .au
Navdeep Gill, .au
Xueqi Lin, Xueqi. .au
3/81
Outline
1 Insert, Update, Delete Statements
v.s. Relational Database State
2 Select Statements
3 A Bunch of Tables
4/81
Insert, Update, Delete Statements
Insert, Delete, Update Statements
v.s. Relational Database State
5/81
Relational Database State – Example
A relational database state of S is a set of relations such that
there is just one relation for each relation schema in S, and
all the relations satisfy the integrity constraints IC.
STUDENT
StudentID Name DoB Email
456 Tom 25/01/1988
458 Peter 23/05/1993
459 Fran 11/09/1987
COURSE
No Cname Unit
COMP1130 Introduction to Advanced Computing I 6
COMP2400 Relational Databases 6
ENROL
StudentID CourseNo Semester Status EnrolDate
456 COMP2400 2016 S2 active 25/05/2016
458 COMP1130 2016 S1 active 20/02/2016
459 COMP2400 2016 S2 active 11/06/2016
6/81
Insert Statement – Example
CREATE TABLE Student(StudentID INT PRIMARY KEY, Name VARCHAR(50),
DoB DATE, Email VARCHAR(100));
Will the following Insert statements work?
INSERT INTO Student
VALUES (456, ‘Tom’, ’25/01/1988′, ‘ ‘);
Yes.
INSERT INTO Student(StudentID)
VALUES (459);
Yes. The values for Name, DoB and Email will be NULL.
INSERT INTO Student(Name, DoB, Email)
VALUES (‘John’, ’15/11/1998′, ‘ ‘);
No. The primary key value cannot be NULL.
6/81
Insert Statement – Example
CREATE TABLE Student(StudentID INT PRIMARY KEY, Name VARCHAR(50),
DoB DATE, Email VARCHAR(100));
Will the following Insert statements work?
INSERT INTO Student
VALUES (456, ‘Tom’, ’25/01/1988′, ‘ ‘);
Yes.
INSERT INTO Student(StudentID)
VALUES (459);
Yes. The values for Name, DoB and Email will be NULL.
INSERT INTO Student(Name, DoB, Email)
VALUES (‘John’, ’15/11/1998′, ‘ ‘);
No. The primary key value cannot be NULL.
6/81
Insert Statement – Example
CREATE TABLE Student(StudentID INT PRIMARY KEY, Name VARCHAR(50),
DoB DATE, Email VARCHAR(100));
Will the following Insert statements work?
INSERT INTO Student
VALUES (456, ‘Tom’, ’25/01/1988′, ‘ ‘);
Yes.
INSERT INTO Student(StudentID)
VALUES (459);
Yes. The values for Name, DoB and Email will be NULL.
INSERT INTO Student(Name, DoB, Email)
VALUES (‘John’, ’15/11/1998′, ‘ ‘);
No. The primary key value cannot be NULL.
6/81
Insert Statement – Example
CREATE TABLE Student(StudentID INT PRIMARY KEY, Name VARCHAR(50),
DoB DATE, Email VARCHAR(100));
Will the following Insert statements work?
INSERT INTO Student
VALUES (456, ‘Tom’, ’25/01/1988′, ‘ ‘);
Yes.
INSERT INTO Student(StudentID)
VALUES (459);
Yes. The values for Name, DoB and Email will be NULL.
INSERT INTO Student(Name, DoB, Email)
VALUES (‘John’, ’15/11/1998′, ‘ ‘);
No. The primary key value cannot be NULL.
6/81
Insert Statement – Example
CREATE TABLE Student(StudentID INT PRIMARY KEY, Name VARCHAR(50),
DoB DATE, Email VARCHAR(100));
Will the following Insert statements work?
INSERT INTO Student
VALUES (456, ‘Tom’, ’25/01/1988′, ‘ ‘);
Yes.
INSERT INTO Student(StudentID)
VALUES (459);
Yes. The values for Name, DoB and Email will be NULL.
INSERT INTO Student(Name, DoB, Email)
VALUES (‘John’, ’15/11/1998′, ‘ ‘);
No. The primary key value cannot be NULL.
6/81
Insert Statement – Example
CREATE TABLE Student(StudentID INT PRIMARY KEY, Name VARCHAR(50),
DoB DATE, Email VARCHAR(100));
Will the following Insert statements work?
INSERT INTO Student
VALUES (456, ‘Tom’, ’25/01/1988′, ‘ ‘);
Yes.
INSERT INTO Student(StudentID)
VALUES (459);
Yes. The values for Name, DoB and Email will be NULL.
INSERT INTO Student(Name, DoB, Email)
VALUES (‘John’, ’15/11/1998′, ‘ ‘);
No. The primary key value cannot be NULL.
7/81
Update Statement – Example
STUDENT
StudentID Name DoB Email
456 Tom 25/01/1988
458 Peter 23/05/1993
459 Fran 11/09/1987
What is the resulting table after executing the following statement?
UPDATE Student SET Name=’Tom Lee’, Email=’tom. ‘
WHERE StudentID=456;
STUDENT
StudentID Name DoB Email
456 Tom Lee 25/01/1988 tom.
458 Peter 23/05/1993
459 Fran 11/09/1987
7/81
Update Statement – Example
STUDENT
StudentID Name DoB Email
456 Tom 25/01/1988
458 Peter 23/05/1993
459 Fran 11/09/1987
What is the resulting table after executing the following statement?
UPDATE Student SET Name=’Tom Lee’, Email=’tom. ‘
WHERE StudentID=456;
STUDENT
StudentID Name DoB Email
456 Tom Lee 25/01/1988 tom.
458 Peter 23/05/1993
459 Fran 11/09/1987
8/81
Delete Statement – Example
STUDENT
StudentID Name DoB Email
456 Tom 25/01/1988
458 Peter 23/05/1993
459 Fran 11/09/1987
What is the resulting table after executing the following statement?
DELETE FROM Student WHERE StudentID=456;
STUDENT
StudentID Name DoB Email
458 Peter 23/05/1993
459 Fran 11/09/1987
8/81
Delete Statement – Example
STUDENT
StudentID Name DoB Email
456 Tom 25/01/1988
458 Peter 23/05/1993
459 Fran 11/09/1987
What is the resulting table after executing the following statement?
DELETE FROM Student WHERE StudentID=456;
STUDENT
StudentID Name DoB Email
458 Peter 23/05/1993
459 Fran 11/09/1987
9/81
Delete Statement – Example
STUDENT
StudentID Name DoB Email
456 Tom 25/01/1988
458 Peter 23/05/1993
459 Fran 11/09/1987
What is the resulting table after executing the following statement?
DELETE FROM Student;
STUDENT
StudentID Name DoB Email
DROP TABLE Student;
The Table STUDENT is deleted.
Note the difference between the Delete and Drop Table statements.
9/81
Delete Statement – Example
STUDENT
StudentID Name DoB Email
456 Tom 25/01/1988
458 Peter 23/05/1993
459 Fran 11/09/1987
What is the resulting table after executing the following statement?
DELETE FROM Student;
STUDENT
StudentID Name DoB Email
DROP TABLE Student;
The Table STUDENT is deleted.
Note the difference between the Delete and Drop Table statements.
9/81
Delete Statement – Example
STUDENT
StudentID Name DoB Email
456 Tom 25/01/1988
458 Peter 23/05/1993
459 Fran 11/09/1987
What is the resulting table after executing the following statement?
DELETE FROM Student;
STUDENT
StudentID Name DoB Email
DROP TABLE Student;
The Table STUDENT is deleted.
Note the difference between the Delete and Drop Table statements.
9/81
Delete Statement – Example
STUDENT
StudentID Name DoB Email
456 Tom 25/01/1988
458 Peter 23/05/1993
459 Fran 11/09/1987
What is the resulting table after executing the following statement?
DELETE FROM Student;
STUDENT
StudentID Name DoB Email
DROP TABLE Student;
The Table STUDENT is deleted.
Note the difference between the Delete and Drop Table statements.
10/81
Delete Statement – Example
Consider the following foreign key defined on ENROL:
FOREIGN KEY(StudentID) REFERENCES STUDENT(StudentID)
ON DELETE NO ACTION
ENROL
StudentID CourseNo Semester Status EnrolDate
456 COMP1130 2016 S1 active 25/02/2016
458 COMP1130 2016 S1 active 25/02/2016
456 COMP2400 2016 S2 active 09/03/2016
STUDENT
StudentID Name DoB Email
456 Tom 25/01/1988
458 Peter 20/02/1991
What will happen if we execute the following statement?
DELETE FROM Student WHERE StudentID=456;
The deletion of a student who has enrolled at least one course will throw out
an error concerning the foreign key.
10/81
Delete Statement – Example
Consider the following foreign key defined on ENROL:
FOREIGN KEY(StudentID) REFERENCES STUDENT(StudentID)
ON DELETE NO ACTION
ENROL
StudentID CourseNo Semester Status EnrolDate
456 COMP1130 2016 S1 active 25/02/2016
458 COMP1130 2016 S1 active 25/02/2016
456 COMP2400 2016 S2 active 09/03/2016
STUDENT
StudentID Name DoB Email
456 Tom 25/01/1988
458 Peter 20/02/1991
What will happen if we execute the following statement?
DELETE FROM Student WHERE StudentID=456;
The deletion of a student who has enrolled at least one course will throw out
an error concerning the foreign key.
11/81
Delete Statement – Example
Consider the following foreign key defined on ENROL:
FOREIGN KEY(StudentID) REFERENCES STUDENT(StudentID)
ON DELETE CASCADE
ENROL
StudentID CourseNo Semester Status EnrolDate
456 COMP1130 2016 S1 active 25/02/2016
458 COMP1130 2016 S1 active 25/02/2016
456 COMP2400 2016 S2 active 09/03/2016
STUDENT
StudentID Name DoB Email
456 Tom 25/01/1988
458 Peter 20/02/1991
What will happen if we execute the following statement?
DELETE FROM Student WHERE StudentID=456;
We would have ENROL below after deleting the student 456.
StudentID CourseNo Semester Status EnrolDate
458 COMP1130 2016 S1 active 25/02/2016
11/81
Delete Statement – Example
Consider the following foreign key defined on ENROL:
FOREIGN KEY(StudentID) REFERENCES STUDENT(StudentID)
ON DELETE CASCADE
ENROL
StudentID CourseNo Semester Status EnrolDate
456 COMP1130 2016 S1 active 25/02/2016
458 COMP1130 2016 S1 active 25/02/2016
456 COMP2400 2016 S2 active 09/03/2016
STUDENT
StudentID Name DoB Email
456 Tom 25/01/1988
458 Peter 20/02/1991
What will happen if we execute the following statement?
DELETE FROM Student WHERE StudentID=456;
We would have ENROL below after deleting the student 456.
StudentID CourseNo Semester Status EnrolDate
458 COMP1130 2016 S1 active 25/02/2016
11/81
Delete Statement – Example
Consider the following foreign key defined on ENROL:
FOREIGN KEY(StudentID) REFERENCES STUDENT(StudentID)
ON DELETE CASCADE
ENROL
StudentID CourseNo Semester Status EnrolDate
456 COMP1130 2016 S1 active 25/02/2016
458 COMP1130 2016 S1 active 25/02/2016
456 COMP2400 2016 S2 active 09/03/2016
STUDENT
StudentID Name DoB Email
456 Tom 25/01/1988
458 Peter 20/02/1991
What will happen if we execute the following statement?
DELETE FROM Student WHERE StudentID=456;
We would have ENROL below after deleting the student 456.
StudentID CourseNo Semester Status EnrolDate
458 COMP1130 2016 S1 active 25/02/2016
12/81
Select Statement
Select Statement
13/81
Select Statement
The SELECT statement has the following basic form:
SELECT attribute_list
FROM table_list
[WHERE condition]
[GROUP BY attribute_list [HAVING group_condition]]
[ORDER BY attribute_list];
14/81
Select Statement
STUDENT
StudentID Name DoB Email
456 Tom 25/01/1988
458 Peter 23/05/1993
459 Fran 11/09/1987
What is the result for the following Select statement?
SELECT * FROM Student WHERE Email like ‘%@gmail.com’;
StudentID Name DoB Email
458 Peter 23/05/1993
459 Fran 11/09/1987
SELECT StudentID FROM Student WHERE Email like ‘%@gmail.com’;
StudentID
458
459
14/81
Select Statement
STUDENT
StudentID Name DoB Email
456 Tom 25/01/1988
458 Peter 23/05/1993
459 Fran 11/09/1987
What is the result for the following Select statement?
SELECT * FROM Student WHERE Email like ‘%@gmail.com’;
StudentID Name DoB Email
458 Peter 23/05/1993
459 Fran 11/09/1987
SELECT StudentID FROM Student WHERE Email like ‘%@gmail.com’;
StudentID
458
459
14/81
Select Statement
STUDENT
StudentID Name DoB Email
456 Tom 25/01/1988
458 Peter 23/05/1993
459 Fran 11/09/1987
What is the result for the following Select statement?
SELECT * FROM Student WHERE Email like ‘%@gmail.com’;
StudentID Name DoB Email
458 Peter 23/05/1993
459 Fran 11/09/1987
SELECT StudentID FROM Student WHERE Email like ‘%@gmail.com’;
StudentID
458
459
14/81
Select Statement
STUDENT
StudentID Name DoB Email
456 Tom 25/01/1988
458 Peter 23/05/1993
459 Fran 11/09/1987
What is the result for the following Select statement?
SELECT * FROM Student WHERE Email like ‘%@gmail.com’;
StudentID Name DoB Email
458 Peter 23/05/1993
459 Fran 11/09/1987
SELECT StudentID FROM Student WHERE Email like ‘%@gmail.com’;
StudentID
458
459
15/81
Select Statement
STUDENT
StudentID Name DoB Email
456 Tom 25/01/1988
458 peter 23/05/1993
459 Fran 11/09/1987
460 Peter 03/09/1992
What is the result for the following Select statement?
SELECT * FROM Student WHERE Name = ‘Peter’;
STUDENT
StudentID Name DoB Email
460 Peter 03/09/1992
SELECT * FROM Student WHERE lower(Name) = ‘peter’;
STUDENT
StudentID Name DoB Email
458 peter 23/05/1993
460 Peter 03/09/1992
15/81
Select Statement
STUDENT
StudentID Name DoB Email
456 Tom 25/01/1988
458 peter 23/05/1993
459 Fran 11/09/1987
460 Peter 03/09/1992
What is the result for the following Select statement?
SELECT * FROM Student WHERE Name = ‘Peter’;
STUDENT
StudentID Name DoB Email
460 Peter 03/09/1992
SELECT * FROM Student WHERE lower(Name) = ‘peter’;
STUDENT
StudentID Name DoB Email
458 peter 23/05/1993
460 Peter 03/09/1992
15/81
Select Statement
STUDENT
StudentID Name DoB Email
456 Tom 25/01/1988
458 peter 23/05/1993
459 Fran 11/09/1987
460 Peter 03/09/1992
What is the result for the following Select statement?
SELECT * FROM Student WHERE Name = ‘Peter’;
STUDENT
StudentID Name DoB Email
460 Peter 03/09/1992
SELECT * FROM Student WHERE lower(Name) = ‘peter’;
STUDENT
StudentID Name DoB Email
458 peter 23/05/1993
460 Peter 03/09/1992
15/81
Select Statement
STUDENT
StudentID Name DoB Email
456 Tom 25/01/1988
458 peter 23/05/1993
459 Fran 11/09/1987
460 Peter 03/09/1992
What is the result for the following Select statement?
SELECT * FROM Student WHERE Name = ‘Peter’;
STUDENT
StudentID Name DoB Email
460 Peter 03/09/1992
SELECT * FROM Student WHERE lower(Name) = ‘peter’;
STUDENT
StudentID Name DoB Email
458 peter 23/05/1993
460 Peter 03/09/1992
16/81
Select + Group By
GROUP BY attribute list groups tuples for each value combination in the
attribute list.
Aggregate functions can be applied to aggregate a group of attribute values
into a single value, e.g.,
COUNT returns the total number of argument values
AVG returns the average of argument values
MIN returns the minimum value of the arguments
MAX returns the maximum value of the arguments
SUM returns the sum of the argument values
We can use HAVING condition to add the condition on the groups.
16/81
Select + Group By
GROUP BY attribute list groups tuples for each value combination in the
attribute list.
Aggregate functions can be applied to aggregate a group of attribute values
into a single value, e.g.,
COUNT returns the total number of argument values
AVG returns the average of argument values
MIN returns the minimum value of the arguments
MAX returns the maximum value of the arguments
SUM returns the sum of the argument values
We can use HAVING condition to add the condition on the groups.
16/81
Select + Group By
GROUP BY attribute list groups tuples for each value combination in the
attribute list.
Aggregate functions can be applied to aggregate a group of attribute values
into a single value, e.g.,
COUNT returns the total number of argument values
AVG returns the average of argument values
MIN returns the minimum value of the arguments
MAX returns the maximum value of the arguments
SUM returns the sum of the argument values
We can use HAVING condition to add the condition on the groups.
17/81
Aggregate Functions – Example
List the total number of courses, the sum of the units of courses, the
minimum unit in COURSE
COURSE
No Cname Unit
COMP1130 Introduction to Advanced Computing I 6
COMP2400 Relational Databases 6
COMP3600 Algorithms 4
SELECT COUNT(unit), MAX(unit) FROM Course;
The query result will be:
COUNT MAX
3 6
17/81
Aggregate Functions – Example
List the total number of courses, the sum of the units of courses, the
minimum unit in COURSE
COURSE
No Cname Unit
COMP1130 Introduction to Advanced Computing I 6
COMP2400 Relational Databases 6
COMP3600 Algorithms 4
SELECT COUNT(unit), MAX(unit) FROM Course;
The query result will be:
COUNT MAX
3 6
17/81
Aggregate Functions – Example
List the total number of courses, the sum of the units of courses, the
minimum unit in COURSE
COURSE
No Cname Unit
COMP1130 Introduction to Advanced Computing I 6
COMP2400 Relational Databases 6
COMP3600 Algorithms 4
SELECT COUNT(unit), MAX(unit) FROM Course;
The query result will be:
COUNT MAX
3 6
18/81
Select + Group By – Example
STUDY
StudentID CourseNo Hours
111 COMP2400 120
222 COMP2400 115
333 STAT2001 120
111 BUSN2011 110
111 ECON2102 120
333 BUSN2011 130
What would happen for the following SELECT + Group By StudentID?
SELECT …
FROM Study
Group By StudentID;
19/81
Select + Group By – Example
Group STUDY
StudentID StudentID CourseNo Hours
111
111 COMP2400 120
111 BUSN2011 110
111 ECON2102 120
222 222 COMP2400 115
333
333 STAT2001 120
333 BUSN2011 130
What would happen for the following SELECT + Group By StudentID?
SELECT …
FROM Study
Group By StudentID;
20/81
Select + Group By – Example
Group STUDY
StudentID StudentID CourseNo Hours
111
111 COMP2400 120
111 BUSN2011 110
111 ECON2102 120
222 222 COMP2400 115
333
333 STAT2001 120
333 BUSN2011 130
What is the result for the following SELECT + Group By StudentID?
SELECT StudentID
FROM Study
Group By StudentID;
StudentID
111
222
333
20/81
Select + Group By – Example
Group STUDY
StudentID StudentID CourseNo Hours
111
111 COMP2400 120
111 BUSN2011 110
111 ECON2102 120
222 222 COMP2400 115
333
333 STAT2001 120
333 BUSN2011 130
What is the result for the following SELECT + Group By StudentID?
SELECT StudentID
FROM Study
Group By StudentID;
StudentID
111
222
333
21/81
Select + Group By – Example
Group STUDY
StudentID StudentID CourseNo Hours
111
111 COMP2400 120
111 BUSN2011 110
111 ECON2102 120
222 222 COMP2400 115
333
333 STAT2001 120
333 BUSN2011 130
What is the result for the following SELECT + Group By StudentID?
SELECT StudentID, COUNT(*)
FROM Study
Group By StudentID;
StudentID COUNT
111 3
222 1
333 2
21/81
Select + Group By – Example
Group STUDY
StudentID StudentID CourseNo Hours
111
111 COMP2400 120
111 BUSN2011 110
111 ECON2102 120
222 222 COMP2400 115
333
333 STAT2001 120
333 BUSN2011 130
What is the result for the following SELECT + Group By StudentID?
SELECT StudentID, COUNT(*)
FROM Study
Group By StudentID;
StudentID COUNT
111 3
222 1
333 2
22/81
Select + Group By – Example
Group STUDY
StudentID StudentID CourseNo Hours
111
111 COMP2400 120
111 BUSN2011 110
111 ECON2102 120
222 222 COMP2400 115
333
333 STAT2001 120
333 BUSN2011 130
What is the result for the following SELECT + Group By StudentID?
SELECT StudentID, MAX(hours)
FROM Study
Group By StudentID;
StudentID MAX
111 120
222 115
333 130
22/81
Select + Group By – Example
Group STUDY
StudentID StudentID CourseNo Hours
111
111 COMP2400 120
111 BUSN2011 110
111 ECON2102 120
222 222 COMP2400 115
333
333 STAT2001 120
333 BUSN2011 130
What is the result for the following SELECT + Group By StudentID?
SELECT StudentID, MAX(hours)
FROM Study
Group By StudentID;
StudentID MAX
111 120
222 115
333 130
23/81
Select + Group By – Example
Group STUDY
StudentID StudentID CourseNo Hours
111
111 COMP2400 120
111 BUSN2011 110
111 ECON2102 120
222 222 COMP2400 115
333
333 STAT2001 120
333 BUSN2011 130
What is the result for the following SELECT + Group By StudentID?
SELECT StudentID, COUNT(StudentID)
FROM Study
Group By StudentID;
StudentID COUNT
111 3
222 1
333 2
23/81
Select + Group By – Example
Group STUDY
StudentID StudentID CourseNo Hours
111
111 COMP2400 120
111 BUSN2011 110
111 ECON2102 120
222 222 COMP2400 115
333
333 STAT2001 120
333 BUSN2011 130
What is the result for the following SELECT + Group By StudentID?
SELECT StudentID, COUNT(StudentID)
FROM Study
Group By StudentID;
StudentID COUNT
111 3
222 1
333 2
24/81
Select + Group By – Example
Group STUDY
StudentID StudentID CourseNo Hours
111
111 COMP2400 120
111 BUSN2011 110
111 ECON2102 120
222 222 COMP2400 115
333
333 STAT2001 120
333 BUSN2011 130
What is the result for the following SELECT + Group By StudentID?
SELECT StudentID, CourseNo
FROM Study
Group By StudentID;
Error Message.
24/81
Select + Group By – Example
Group STUDY
StudentID StudentID CourseNo Hours
111
111 COMP2400 120
111 BUSN2011 110
111 ECON2102 120
222 222 COMP2400 115
333
333 STAT2001 120
333 BUSN2011 130
What is the result for the following SELECT + Group By StudentID?
SELECT StudentID, CourseNo
FROM Study
Group By StudentID;
Error Message.
25/81
Select + Group By – Example
Group STUDY
StudentID StudentID CourseNo Hours
111
111 COMP2400 120
111 BUSN2011 110
111 ECON2102 120
222 222 COMP2400 115
333
333 STAT2001 120
333 BUSN2011 130
What is the result for the following SELECT + Group By StudentID?
SELECT *
FROM Study
Group By StudentID;
Error Message.
25/81
Select + Group By – Example
Group STUDY
StudentID StudentID CourseNo Hours
111
111 COMP2400 120
111 BUSN2011 110
111 ECON2102 120
222 222 COMP2400 115
333
333 STAT2001 120
333 BUSN2011 130
What is the result for the following SELECT + Group By StudentID?
SELECT *
FROM Study
Group By StudentID;
Error Message.
26/81
Select + Group By – Example
Group STUDY
StudentID StudentID CourseNo Hours
111
111 COMP2400 120
111 BUSN2011 110
111 ECON2102 120
222 222 COMP2400 115
333
333 STAT2001 120
333 BUSN2011 130
What is the result for the following SELECT + Group By StudentID?
SELECT COUNT(*)
FROM Study
Group By StudentID;
COUNT
3
1
2
26/81
Select + Group By – Example
Group STUDY
StudentID StudentID CourseNo Hours
111
111 COMP2400 120
111 BUSN2011 110
111 ECON2102 120
222 222 COMP2400 115
333
333 STAT2001 120
333 BUSN2011 130
What is the result for the following SELECT + Group By StudentID?
SELECT COUNT(*)
FROM Study
Group By StudentID;
COUNT
3
1
2
27/81
Select + Group By – Example
STUDY
StudentID CourseNo Hours
111 COMP2400 120
222 COMP2400 115
333 STAT2001 120
111 BUSN2011 110
111 ECON2102 120
333 BUSN2011 130
What would happen for the following SELECT + Group By CourseNo?
SELECT …
FROM Study
Group By CourseNo;
28/81
Select + Group By – Example
Group STUDY
CourseNo StudentID CourseNo Hours
BUSN2011
111 BUSN2011 110
333 BUSN2011 130
COMP2400
111 COMP2400 120
222 COMP2400 115
ECON2102 111 ECON2102 120
STAT2001 333 STAT2001 120
What would happen for the following SELECT + Group By CourseNo?
SELECT …
FROM Study
Group By CourseNo;
29/81
Select + Group By – Example
Group STUDY
CourseNo StudentID CourseNo Hours
BUSN2011
111 BUSN2011 110
333 BUSN2011 130
COMP2400
111 COMP2400 120
222 COMP2400 115
ECON2102 111 ECON2102 120
STAT2001 333 STAT2001 120
What is the result for the following SELECT + Group By CourseNo?
SELECT CourseNo, COUNT(*)
FROM Study
Group By CourseNo;
CourseNo COUNT
BUSN2011 2
COMP2400 2
ECON2102 1
STAT2001 1
29/81
Select + Group By – Example
Group STUDY
CourseNo StudentID CourseNo Hours
BUSN2011
111 BUSN2011 110
333 BUSN2011 130
COMP2400
111 COMP2400 120
222 COMP2400 115
ECON2102 111 ECON2102 120
STAT2001 333 STAT2001 120
What is the result for the following SELECT + Group By CourseNo?
SELECT CourseNo, COUNT(*)
FROM Study
Group By CourseNo;
CourseNo COUNT
BUSN2011 2
COMP2400 2
ECON2102 1
STAT2001 1
30/81
Select + Group By – Example
Group STUDY
CourseNo StudentID CourseNo Hours
BUSN2011
111 BUSN2011 110
333 BUSN2011 130
COMP2400
111 COMP2400 120
222 COMP2400 115
ECON2102 111 ECON2102 120
STAT2001 333 STAT2001 120
What is the result for the following SELECT + Group By CourseNo?
SELECT CourseNo, Hours
FROM Study
Group By CourseNo;
Error Message.
30/81
Select + Group By – Example
Group STUDY
CourseNo StudentID CourseNo Hours
BUSN2011
111 BUSN2011 110
333 BUSN2011 130
COMP2400
111 COMP2400 120
222 COMP2400 115
ECON2102 111 ECON2102 120
STAT2001 333 STAT2001 120
What is the result for the following SELECT + Group By CourseNo?
SELECT CourseNo, Hours
FROM Study
Group By CourseNo;
Error Message.
31/81
Select + Group By + Having – Example
Group STUDY
CourseNo StudentID CourseNo Hours
BUSN2011
111 BUSN2011 110
333 BUSN2011 130
COMP2400
111 COMP2400 120
222 COMP2400 115
ECON2102 111 ECON2102 120
STAT2001 333 STAT2001 120
What is the result for the following SELECT + Group By + Having?
SELECT CourseNo
FROM Study
Group By CourseNo
Having MAX(Hours) > 120;
CourseNo
BUSN2011
31/81
Select + Group By + Having – Example
Group STUDY
CourseNo StudentID CourseNo Hours
BUSN2011
111 BUSN2011 110
333 BUSN2011 130
COMP2400
111 COMP2400 120
222 COMP2400 115
ECON2102 111 ECON2102 120
STAT2001 333 STAT2001 120
What is the result for the following SELECT + Group By + Having?
SELECT CourseNo
FROM Study
Group By CourseNo
Having MAX(Hours) > 120;
CourseNo
BUSN2011
32/81
Select + Group By + Having – Example
Group STUDY
CourseNo StudentID CourseNo Hours
BUSN2011
111 BUSN2011 110
333 BUSN2011 130
COMP2400
111 COMP2400 120
222 COMP2400 115
ECON2102 111 ECON2102 120
STAT2001 333 STAT2001 120
What is the result for the following SELECT + Group By + Having?
SELECT CourseNo
FROM Study
Group By CourseNo
Having COUNT(*) > 1;
CourseNo
BUSN2011
COMP2400
32/81
Select + Group By + Having – Example
Group STUDY
CourseNo StudentID CourseNo Hours
BUSN2011
111 BUSN2011 110
333 BUSN2011 130
COMP2400
111 COMP2400 120
222 COMP2400 115
ECON2102 111 ECON2102 120
STAT2001 333 STAT2001 120
What is the result for the following SELECT + Group By + Having?
SELECT CourseNo
FROM Study
Group By CourseNo
Having COUNT(*) > 1;
CourseNo
BUSN2011
COMP2400
33/81
A Bunch of Tables
A Bunch of Tables
34/81
Set Operations
SQL incorporates several set operations: UNION (set union) and INTERSECT
(set intersection), and sometimes EXCEPT (set difference / minus).
Set operations result in return of a relation of tuples (no duplicates).
Set operations apply to relations that have the same attribute types
appearing in the same order.
35/81
Set Operations
STUDY
StudentID CourseNo Hours
111 COMP2400 120
222 COMP2400 115
333 STAT2001 120
111 BUSN2011 110
111 ECON2102 120
333 BUSN2011 130
What is the result for the following SQL query?
SELECT StudentID FROM Study
WHERE CourseNo=’COMP2400′
UNION
SELECT StudentID FROM Study
WHERE CourseNo=’ECON2102′;
StudentID
111
222
UNION
StudentID
111
35/81
Set Operations
STUDY
StudentID CourseNo Hours
111 COMP2400 120
222 COMP2400 115
333 STAT2001 120
111 BUSN2011 110
111 ECON2102 120
333 BUSN2011 130
What is the result for the following SQL query?
SELECT StudentID FROM Study
WHERE CourseNo=’COMP2400′
UNION
SELECT StudentID FROM Study
WHERE CourseNo=’ECON2102′;
StudentID
111
222
UNION
StudentID
111
36/81
Set Operations
STUDY
StudentID CourseNo Hours
111 COMP2400 120
222 COMP2400 115
333 STAT2001 120
111 BUSN2011 110
111 ECON2102 120
333 BUSN2011 130
What is the result for the following SQL query?
SELECT StudentID FROM Study
WHERE CourseNo=’COMP2400′
UNION
SELECT StudentID FROM Study
WHERE CourseNo=’ECON2102′;
StudentID
111
222
37/81
Set Operations
STUDY
StudentID CourseNo Hours
111 COMP2400 120
222 COMP2400 115
333 STAT2001 120
111 BUSN2011 110
111 ECON2102 120
333 BUSN2011 130
What is the result for the following SQL query?
SELECT CourseNo FROM Study
WHERE StudentID=111
EXCEPT
SELECT CourseNo FROM Study
WHERE StudentID=222;
CourseNo
COMP2400
BUSN2011
ECON2102
EXCEPT
CourseNo
COMP2400
37/81
Set Operations
STUDY
StudentID CourseNo Hours
111 COMP2400 120
222 COMP2400 115
333 STAT2001 120
111 BUSN2011 110
111 ECON2102 120
333 BUSN2011 130
What is the result for the following SQL query?
SELECT CourseNo FROM Study
WHERE StudentID=111
EXCEPT
SELECT CourseNo FROM Study
WHERE StudentID=222;
CourseNo
COMP2400
BUSN2011
ECON2102
EXCEPT
CourseNo
COMP2400
38/81
Set Operations
STUDY
StudentID CourseNo Hours
111 COMP2400 120
222 COMP2400 115
333 STAT2001 120
111 BUSN2011 110
111 ECON2102 120
333 BUSN2011 130
What is the result for the following SQL query?
SELECT CourseNo FROM Study
WHERE StudentID=111
EXCEPT
SELECT CourseNo FROM Study
WHERE StudentID=222;
CourseNo
BUSN2011
ECON2102
39/81
Set Operations
STUDY
StudentID CourseNo Hours
111 COMP2400 120
222 COMP2400 115
333 STAT2001 120
111 BUSN2011 110
111 ECON2102 120
333 BUSN2011 130
What is the result for the following SQL query?
SELECT CourseNo FROM Study
WHERE StudentID=111
EXCEPT
SELECT StudentID FROM Study
WHERE CourseNo=’ECON2102′;
CourseNo
COMP2400
BUSN2011
ECON2102
EXCEPT
StudentID
111
39/81
Set Operations
STUDY
StudentID CourseNo Hours
111 COMP2400 120
222 COMP2400 115
333 STAT2001 120
111 BUSN2011 110
111 ECON2102 120
333 BUSN2011 130
What is the result for the following SQL query?
SELECT CourseNo FROM Study
WHERE StudentID=111
EXCEPT
SELECT StudentID FROM Study
WHERE CourseNo=’ECON2102′;
CourseNo
COMP2400
BUSN2011
ECON2102
EXCEPT
StudentID
111
40/81
Set Operations
STUDY
StudentID CourseNo Hours
111 COMP2400 120
222 COMP2400 115
333 STAT2001 120
111 BUSN2011 110
111 ECON2102 120
333 BUSN2011 130
What is the result for the following SQL query?
SELECT CourseNo FROM Study
WHERE StudentID=111
EXCEPT
SELECT StudentID FROM Study
WHERE CourseNo=’ECON2102′;
ERROR MESSAGE
41/81
Join Operations
When we want to retrieve data from more than one relations, we often need
to use join operations.
Inner Join: tuples are included in the result only if there is at least one
matching in both relations.
Left/Right Join: all tuples of the left/right table are included in the result,
even if there are no matches in the relations.
42/81
Inner Join – Example
COURSE
No Cname Unit
COMP2400 Relational Databases 6
BUSN2011 Management Accounting 6
ECON2102 Macroeconomics 6
ENROL
StudentID CourseNo Semester Status
111 BUSN2011 2016 S1 active
222 COMP2400 2016 S1 active
111 COMP2400 2016 S2 active
What would happen for the following INNER JOIN statement?
SELECT …
FROM Course INNER JOIN Enrol ON Course.No=Enrol.CourseNo;
COURSE ENROL
No Cname Unit StudentID CourseNo Semester Status
COMP2400 Relational Databases 6 222 COMP2400 2016 S1 active
COMP2400 Relational Databases 6 111 COMP2400 2016 S2 active
BUSN2011 Management Accounting 6 111 BUSN2011 2016 S1 active
42/81
Inner Join – Example
COURSE
No Cname Unit
COMP2400 Relational Databases 6
BUSN2011 Management Accounting 6
ECON2102 Macroeconomics 6
ENROL
StudentID CourseNo Semester Status
111 BUSN2011 2016 S1 active
222 COMP2400 2016 S1 active
111 COMP2400 2016 S2 active
What would happen for the following INNER JOIN statement?
SELECT …
FROM Course INNER JOIN Enrol ON Course.No=Enrol.CourseNo;
COURSE ENROL
No Cname Unit StudentID CourseNo Semester Status
COMP2400 Relational Databases 6 222 COMP2400 2016 S1 active
COMP2400 Relational Databases 6 111 COMP2400 2016 S2 active
BUSN2011 Management Accounting 6 111 BUSN2011 2016 S1 active
43/81
Inner Join – Example
COURSE
No Cname Unit
COMP2400 Relational Databases 6
BUSN2011 Management Accounting 6
ECON2102 Macroeconomics 6
ENROL
StudentID CourseNo Semester Status
111 BUSN2011 2016 S1 active
222 COMP2400 2016 S1 active
111 COMP2400 2016 S2 active
What is the result for the following INNER JOIN statement?
SELECT Course.No
FROM Course INNER JOIN Enrol ON Course.No=Enrol.CourseNo;
COURSE ENROL
No Cname Unit StudentID CourseNo Semester Status
COMP2400 Relational Databases 6 222 COMP2400 2016 S1 active
COMP2400 Relational Databases 6 111 COMP2400 2016 S2 active
BUSN2011 Management Accounting 6 111 BUSN2011 2016 S1 active
44/81
Inner Join – Example
COURSE
No Cname Unit
COMP2400 Relational Databases 6
BUSN2011 Management Accounting 6
ECON2102 Macroeconomics 6
ENROL
StudentID CourseNo Semester Status
111 BUSN2011 2016 S1 active
222 COMP2400 2016 S1 active
111 COMP2400 2016 S2 active
What is the result for the following INNER JOIN statement?
SELECT Course.No
FROM Course INNER JOIN Enrol ON Course.No=Enrol.CourseNo;
No
COMP2400
COMP2400
BUSN2011
45/81
Left Join – Example
COURSE
No Cname Unit
COMP2400 Relational Databases 6
BUSN2011 Management Accounting 6
ECON2102 Macroeconomics 6
ENROL
StudentID CourseNo Semester Status
111 BUSN2011 2016 S1 active
222 COMP2400 2016 S1 active
111 COMP2400 2016 S2 active
What would happen for the following LEFT JOIN statement?
SELECT …
FROM Course LEFT JOIN Enrol ON Course.No=Enrol.CourseNo;
COURSE ENROL
No Cname Unit StudentID CourseNo Semester Status
COMP2400 Relational Databases 6 222 COMP2400 2016 S1 active
COMP2400 Relational Databases 6 111 COMP2400 2016 S2 active
BUSN2011 Management Accounting 6 111 BUSN2011 2016 S1 active
ECON2102 Macroeconomics 6 NULL NULL NULL NULL
45/81
Left Join – Example
COURSE
No Cname Unit
COMP2400 Relational Databases 6
BUSN2011 Management Accounting 6
ECON2102 Macroeconomics 6
ENROL
StudentID CourseNo Semester Status
111 BUSN2011 2016 S1 active
222 COMP2400 2016 S1 active
111 COMP2400 2016 S2 active
What would happen for the following LEFT JOIN statement?
SELECT …
FROM Course LEFT JOIN Enrol ON Course.No=Enrol.CourseNo;
COURSE ENROL
No Cname Unit StudentID CourseNo Semester Status
COMP2400 Relational Databases 6 222 COMP2400 2016 S1 active
COMP2400 Relational Databases 6 111 COMP2400 2016 S2 active
BUSN2011 Management Accounting 6 111 BUSN2011 2016 S1 active
ECON2102 Macroeconomics 6 NULL NULL NULL NULL
46/81
Left Join – Example
COURSE
No Cname Unit
COMP2400 Relational Databases 6
BUSN2011 Management Accounting 6
ECON2102 Macroeconomics 6
ENROL
StudentID CourseNo Semester Status
111 BUSN2011 2016 S1 active
222 COMP2400 2016 S1 active
111 COMP2400 2016 S2 active
What is the result for the following LEFT JOIN statement?
SELECT Course.No
FROM Course LEFT JOIN Enrol ON Course.No=Enrol.CourseNo;
COURSE ENROL
No Cname Unit StudentID CourseNo Semester Status
COMP2400 Relational Databases 6 222 COMP2400 2016 S1 active
COMP2400 Relational Databases 6 111 COMP2400 2016 S2 active
BUSN2011 Management Accounting 6 111 BUSN2011 2016 S1 active
ECON2102 Macroeconomics 6 NULL NULL NULL NULL
47/81
Left Join – Example
COURSE
No Cname Unit
COMP2400 Relational Databases 6
BUSN2011 Management Accounting 6
ECON2102 Macroeconomics 6
ENROL
StudentID CourseNo Semester Status
111 BUSN2011 2016 S1 active
222 COMP2400 2016 S1 active
111 COMP2400 2016 S2 active
What is the result for the following LEFT JOIN statement?
SELECT Course.No
FROM Course LEFT JOIN Enrol ON Course.No=Enrol.CourseNo;
No
COMP2400
COMP2400
BUSN2011
ECON2102
48/81
Natural Join
A natural join is considered as one kind of inner join.
In a natural join, two relations are joined implicitly by comparing all attributes
of the same names in both relations.
A natural join retains all the data of the two tables for only the matched rows,
without duplication.
49/81
Natural Join – Example
COURSE
CourseNo Cname Unit
COMP2400 Relational Databases 6
BUSN2011 Management Accounting 6
ECON2102 Macroeconomics 6
ENROL
StudentID CourseNo Semester Status
111 BUSN2011 2016 S1 active
222 COMP2400 2016 S1 active
111 COMP2400 2016 S2 active
What would happen for the following NATURAL JOIN statement?
SELECT …
FROM Course NATURAL JOIN Enrol;
COURSE ENROL
CourseNo Cname Unit StudentID Semester Status
COMP2400 Relational Databases 6 222 2016 S1 active
COMP2400 Relational Databases 6 111 2016 S2 active
BUSN2011 Management Accounting 6 111 2016 S1 active
49/81
Natural Join – Example
COURSE
CourseNo Cname Unit
COMP2400 Relational Databases 6
BUSN2011 Management Accounting 6
ECON2102 Macroeconomics 6
ENROL
StudentID CourseNo Semester Status
111 BUSN2011 2016 S1 active
222 COMP2400 2016 S1 active
111 COMP2400 2016 S2 active
What would happen for the following NATURAL JOIN statement?
SELECT …
FROM Course NATURAL JOIN Enrol;
COURSE ENROL
CourseNo Cname Unit StudentID Semester Status
COMP2400 Relational Databases 6 222 2016 S1 active
COMP2400 Relational Databases 6 111 2016 S2 active
BUSN2011 Management Accounting 6 111 2016 S1 active
50/81
Natural Join – Example
COURSE
CourseNo Cname Unit
COMP2400 Relational Databases 6
BUSN2011 Management Accounting 6
ECON2102 Macroeconomics 6
ENROL
StudentID CourseNo Semester Status
111 BUSN2011 2016 S1 active
222 COMP2400 2016 S1 active
111 COMP2400 2016 S2 active
What is the result for the following NATURAL JOIN statement?
SELECT CourseNo
FROM Course NATURAL JOIN Enrol;
COURSE ENROL
CourseNo Cname Unit StudentID Semester Status
COMP2400 Relational Databases 6 222 2016 S1 active
COMP2400 Relational Databases 6 111 2016 S2 active
BUSN2011 Management Accounting 6 111 2016 S1 active
51/81
Natural Join – Example
COURSE
CourseNo Cname Unit
COMP2400 Relational Databases 6
BUSN2011 Management Accounting 6
ECON2102 Macroeconomics 6
ENROL
StudentID CourseNo Semester Status
111 BUSN2011 2016 S1 active
222 COMP2400 2016 S1 active
111 COMP2400 2016 S2 active
What is the result for the following NATURAL JOIN statement?
SELECT CourseNo
FROM Course NATURAL JOIN Enrol;
CourseNo
COMP2400
COMP2400
BUSN2011
52/81
Natural Join – Example
COURSE
No Cname Unit
COMP2400 Relational Databases 6
BUSN2011 Management Accounting 6
ECON2102 Macroeconomics 6
ENROL
StudentID CourseNo Semester Status
111 BUSN2011 2016 S1 active
222 COMP2400 2016 S1 active
111 COMP2400 2016 S2 active
What is the result for the following NATURAL JOIN statement?
SELECT *
FROM Course NATURAL JOIN Enrol;
If there are no matching attributes in two tables for NATURAL JOIN,
SELECT *
FROM Course, Enrol;
52/81
Natural Join – Example
COURSE
No Cname Unit
COMP2400 Relational Databases 6
BUSN2011 Management Accounting 6
ECON2102 Macroeconomics 6
ENROL
StudentID CourseNo Semester Status
111 BUSN2011 2016 S1 active
222 COMP2400 2016 S1 active
111 COMP2400 2016 S2 active
What is the result for the following NATURAL JOIN statement?
SELECT *
FROM Course NATURAL JOIN Enrol;
If there are no matching attributes in two tables for NATURAL JOIN,
SELECT *
FROM Course, Enrol;
53/81
Natural Join – Example
COURSE
CourseNo Cname Unit
COMP2400 Relational Databases 6
BUSN2011 Management Accounting 6
ECON2102 Macroeconomics 6
ENROL
StudentID CourseNo Semester Status
111 BUSN2011 2016 S1 active
222 COMP2400 2016 S1 active
111 COMP2400 2016 S2 active
What is the result for the following NATURAL JOIN statement?
SELECT *
FROM Course NATURAL JOIN Enrol ON Course.CourseNo=Enrol.CourseNo;
ERROR MESSAGE because a NATURAL JOIN implicitly compares all
attributes of the same names in two table.
53/81
Natural Join – Example
COURSE
CourseNo Cname Unit
COMP2400 Relational Databases 6
BUSN2011 Management Accounting 6
ECON2102 Macroeconomics 6
ENROL
StudentID CourseNo Semester Status
111 BUSN2011 2016 S1 active
222 COMP2400 2016 S1 active
111 COMP2400 2016 S2 active
What is the result for the following NATURAL JOIN statement?
SELECT *
FROM Course NATURAL JOIN Enrol ON Course.CourseNo=Enrol.CourseNo;
ERROR MESSAGE because a NATURAL JOIN implicitly compares all
attributes of the same names in two table.
54/81
Join – More Examples
STUDENT
StudentID Name DoB Email
COURSE
No Cname Unit
ENROL
StudentID CourseNo Status
List all information of students who have enrolled in a course with
CourseNo=’X’ and the CourseNo of these courses.
1 Use SELECT + FROM (Cartesian Product) + WHERE
2 Use SELECT + FROM (INNER JOIN) + ON
3 Use SELECT + FROM (INNER JOIN) + ON + WHERE
4 Use SELECT + FROM (NATURAL JOIN) + WHERE
55/81
Join – More Examples
STUDENT
StudentID Name DoB Email
ENROL
StudentID CourseNo Status
List all information of students who have enrolled in a course with
CourseNo=’X’ and the CourseNo of these courses.
(1) Use SELECT + FROM (Cartesian Product) + WHERE
SELECT Student.*, Enrol.CourseNo
FROM Student, Enrol
WHERE (Student.StudentID=Enrol.StudentID)
AND (Enrol.CourseNo = ‘X’);
55/81
Join – More Examples
STUDENT
StudentID Name DoB Email
ENROL
StudentID CourseNo Status
List all information of students who have enrolled in a course with
CourseNo=’X’ and the CourseNo of these courses.
(1) Use SELECT + FROM (Cartesian Product) + WHERE
SELECT Student.*, Enrol.CourseNo
FROM Student, Enrol
WHERE (Student.StudentID=Enrol.StudentID)
AND (Enrol.CourseNo = ‘X’);
56/81
Join – More Examples
STUDENT
StudentID Name DoB Email
ENROL
StudentID CourseNo Status
List all information of students who have enrolled in a course with
CourseNo=’X’ and the CourseNo of these courses.
(2) Use SELECT + FROM (INNER JOIN) + ON
SELECT Student.*, Enrol.CourseNo
FROM Student INNER JOIN Enrol
ON (Student.StudentID=Enrol.StudentID)
AND (Enrol.CourseNo = ‘X’);
56/81
Join – More Examples
STUDENT
StudentID Name DoB Email
ENROL
StudentID CourseNo Status
List all information of students who have enrolled in a course with
CourseNo=’X’ and the CourseNo of these courses.
(2) Use SELECT + FROM (INNER JOIN) + ON
SELECT Student.*, Enrol.CourseNo
FROM Student INNER JOIN Enrol
ON (Student.StudentID=Enrol.StudentID)
AND (Enrol.CourseNo = ‘X’);
57/81
Join – More Examples
STUDENT
StudentID Name DoB Email
ENROL
StudentID CourseNo Status
List all information of students who have enrolled in a course with
CourseNo=’X’ and the CourseNo of these courses.
(3) Use SELECT + FROM (INNER JOIN) + ON + WHERE
SELECT Student.*, Enrol.CourseNo
FROM Student INNER JOIN Enrol
ON Student.StudentID=Enrol.StudentID
WHERE Enrol.CourseNo = ‘X’;
57/81
Join – More Examples
STUDENT
StudentID Name DoB Email
ENROL
StudentID CourseNo Status
List all information of students who have enrolled in a course with
CourseNo=’X’ and the CourseNo of these courses.
(3) Use SELECT + FROM (INNER JOIN) + ON + WHERE
SELECT Student.*, Enrol.CourseNo
FROM Student INNER JOIN Enrol
ON Student.StudentID=Enrol.StudentID
WHERE Enrol.CourseNo = ‘X’;
58/81
Join – More Examples
STUDENT
StudentID Name DoB Email
ENROL
StudentID CourseNo Status
List all information of students who have enrolled in a course with
CourseNo=’X’ and the CourseNo of these courses.
(4) Use SELECT + FROM (NATURAL JOIN) + WHERE
SELECT Student.*, Enrol.CourseNo
FROM Student NATURAL JOIN Enrol
WHERE Enrol.CourseNo = ‘X’;
58/81
Join – More Examples
STUDENT
StudentID Name DoB Email
ENROL
StudentID CourseNo Status
List all information of students who have enrolled in a course with
CourseNo=’X’ and the CourseNo of these courses.
(4) Use SELECT + FROM (NATURAL JOIN) + WHERE
SELECT Student.*, Enrol.CourseNo
FROM Student NATURAL JOIN Enrol
WHERE Enrol.CourseNo = ‘X’;
59/81
Subqueries
Subqueries can be viewed as temporary tables (usually in conjunction with
aliases and renaming, exist only for the query).
Subqueries can be specified within the FROM-clause.
Subqueries can also be specified within the WHERE-clause, e.g.,
IN subquery tests if tuple occurs in the temporary table of the
subquery.
EXISTS subquery tests whether the temporary table of the subquery is
empty or not.
using ALL, SOME or ANY before a subquery makes subqueries usable in
comparison formulae (SOME and ANY are interchangeable).
in all these cases the condition involving the subquery can be negated
using a preceding NOT.
60/81
Subqueries IN – Example
STUDENT
StudentID Name DoB Email
ENROL
StudentID CourseNo Status
List all information of students who have enrolled in a course with
CourseNo=’X’ and the CourseNo of these courses, we have:
SELECT Student.*, Enrol.CourseNo
FROM Student NATURAL JOIN Enrol
WHERE Enrol.CourseNo = ‘X’;
Now if we want to list all information of students who have enrolled in a
course that has less than 10 students enrolled and the CourseNo of these
courses.
61/81
Subqueries IN – Example
List all information of students who have enrolled in a course that has less
than 10 students enrolled and the CourseNo of these courses.
List the CourseNo of the courses that have less than 10 students
enrolled
SELECT CourseNo
FROM Enrol
GROUP BY CourseNo
HAVING COUNT(*)<10; List all information of students who have enrolled in a course with CourseNo='X' and the CourseNo of these courses SELECT Student.*, Enrol.CourseNo FROM Student NATURAL JOIN Enrol WHERE Enrol.CourseNo = 'X'; 61/81 Subqueries IN – Example List all information of students who have enrolled in a course that has less than 10 students enrolled and the CourseNo of these courses. List the CourseNo of the courses that have less than 10 students enrolled SELECT CourseNo FROM Enrol GROUP BY CourseNo HAVING COUNT(*)<10; List all information of students who have enrolled in a course with CourseNo='X' and the CourseNo of these courses SELECT Student.*, Enrol.CourseNo FROM Student NATURAL JOIN Enrol WHERE Enrol.CourseNo = 'X'; 61/81 Subqueries IN – Example List all information of students who have enrolled in a course that has less than 10 students enrolled and the CourseNo of these courses. List the CourseNo of the courses that have less than 10 students enrolled SELECT CourseNo FROM Enrol GROUP BY CourseNo HAVING COUNT(*)<10; List all information of students who have enrolled in a course with CourseNo='X' and the CourseNo of these courses SELECT Student.*, Enrol.CourseNo FROM Student NATURAL JOIN Enrol WHERE Enrol.CourseNo = 'X'; 61/81 Subqueries IN – Example List all information of students who have enrolled in a course that has less than 10 students enrolled and the CourseNo of these courses. List the CourseNo of the courses that have less than 10 students enrolled SELECT CourseNo FROM Enrol GROUP BY CourseNo HAVING COUNT(*)<10; List all information of students who have enrolled in a course with CourseNo='X' and the CourseNo of these courses SELECT Student.*, Enrol.CourseNo FROM Student NATURAL JOIN Enrol WHERE Enrol.CourseNo = 'X'; 61/81 Subqueries IN – Example List all information of students who have enrolled in a course that has less than 10 students enrolled and the CourseNo of these courses. List the CourseNo of the courses that have less than 10 students enrolled SELECT CourseNo FROM Enrol GROUP BY CourseNo HAVING COUNT(*)<10; List all information of students who have enrolled in a course with CourseNo='X' and the CourseNo of these courses SELECT Student.*, Enrol.CourseNo FROM Student NATURAL JOIN Enrol WHERE Enrol.CourseNo = 'X'; 62/81 Subqueries IN – Example List all information of students who have enrolled in a course that has less than 10 students enrolled and the CourseNo of these courses. SELECT Student.*,e1.CourseNo FROM Student NATURAL JOIN Enrol e1 WHERE e1.CourseNo IN (SELECT e2.CourseNo FROM Enrol e2 GROUP BY e2.CourseNo HAVING COUNT(*)<10); Why do we use aliases e1 and e2 for ENROL? Distinguish two ENROL tables. 62/81 Subqueries IN – Example List all information of students who have enrolled in a course that has less than 10 students enrolled and the CourseNo of these courses. SELECT Student.*,e1.CourseNo FROM Student NATURAL JOIN Enrol e1 WHERE e1.CourseNo IN (SELECT e2.CourseNo FROM Enrol e2 GROUP BY e2.CourseNo HAVING COUNT(*)<10); Why do we use aliases e1 and e2 for ENROL? Distinguish two ENROL tables. 62/81 Subqueries IN – Example List all information of students who have enrolled in a course that has less than 10 students enrolled and the CourseNo of these courses. SELECT Student.*,e1.CourseNo FROM Student NATURAL JOIN Enrol e1 WHERE e1.CourseNo IN (SELECT e2.CourseNo FROM Enrol e2 GROUP BY e2.CourseNo HAVING COUNT(*)<10); Why do we use aliases e1 and e2 for ENROL? Distinguish two ENROL tables. 63/81 Subqueries IN – Example List all information of students who have enrolled in a course that has less than 10 students enrolled and the CourseNo of these courses. SELECT Student.*,e1.CourseNo FROM Student NATURAL JOIN Enrol e1 WHERE e1.CourseNo IN (SELECT e2.CourseNo, COUNT(*) FROM Enrol e2 GROUP BY e2.CourseNo HAVING COUNT(*)<10); Is the above query correct? No. IN subquery tests if tuple occurs in the temporary table of the subquery. 63/81 Subqueries IN – Example List all information of students who have enrolled in a course that has less than 10 students enrolled and the CourseNo of these courses. SELECT Student.*,e1.CourseNo FROM Student NATURAL JOIN Enrol e1 WHERE e1.CourseNo IN (SELECT e2.CourseNo, COUNT(*) FROM Enrol e2 GROUP BY e2.CourseNo HAVING COUNT(*)<10); Is the above query correct? No. IN subquery tests if tuple occurs in the temporary table of the subquery. 63/81 Subqueries IN – Example List all information of students who have enrolled in a course that has less than 10 students enrolled and the CourseNo of these courses. SELECT Student.*,e1.CourseNo FROM Student NATURAL JOIN Enrol e1 WHERE e1.CourseNo IN (SELECT e2.CourseNo, COUNT(*) FROM Enrol e2 GROUP BY e2.CourseNo HAVING COUNT(*)<10); Is the above query correct? No. IN subquery tests if tuple occurs in the temporary table of the subquery. 64/81 Subqueries EXISTS – Example STUDENT StudentID Name 111 Tom 222 Emily 333 John ENROL StudentID CourseNo Semester 111 BUSN2011 2016 S1 222 COMP2400 2016 S1 111 COMP2400 2016 S2 Count the number of students who have enrolled in at least one course? SELECT COUNT(*) FROM Student s WHERE EXISTS (SELECT * FROM Enrol e WHERE s.StudentID=e.StudentID); 1st tuple of Student, EXISTS 2st tuple of Student, EXISTS StudentID CourseNo Semester 111 BUSN2011 2016 S1 111 COMP2400 2016 S2 StudentID CourseNo Semester 222 COMP2400 2016 S1 The above query (returning 2) is correct! 64/81 Subqueries EXISTS – Example STUDENT StudentID Name 111 Tom 222 Emily 333 John ENROL StudentID CourseNo Semester 111 BUSN2011 2016 S1 222 COMP2400 2016 S1 111 COMP2400 2016 S2 Count the number of students who have enrolled in at least one course? SELECT COUNT(*) FROM Student s WHERE EXISTS (SELECT * FROM Enrol e WHERE s.StudentID=e.StudentID); 1st tuple of Student, EXISTS 2st tuple of Student, EXISTS StudentID CourseNo Semester 111 BUSN2011 2016 S1 111 COMP2400 2016 S2 StudentID CourseNo Semester 222 COMP2400 2016 S1 The above query (returning 2) is correct! 64/81 Subqueries EXISTS – Example STUDENT StudentID Name 111 Tom 222 Emily 333 John ENROL StudentID CourseNo Semester 111 BUSN2011 2016 S1 222 COMP2400 2016 S1 111 COMP2400 2016 S2 Count the number of students who have enrolled in at least one course? SELECT COUNT(*) FROM Student s WHERE EXISTS (SELECT * FROM Enrol e WHERE s.StudentID=e.StudentID); 1st tuple of Student, EXISTS 2st tuple of Student, EXISTS StudentID CourseNo Semester 111 BUSN2011 2016 S1 111 COMP2400 2016 S2 StudentID CourseNo Semester 222 COMP2400 2016 S1 The above query (returning 2) is correct! 65/81 Subqueries EXISTS – Example STUDENT StudentID Name 111 Tom 222 Emily 333 John ENROL StudentID CourseNo Semester 111 BUSN2011 2016 S1 222 COMP2400 2016 S1 111 COMP2400 2016 S2 Count the number of students who have enrolled in at least one course? SELECT COUNT(*) FROM Enrol e WHERE EXISTS (SELECT * FROM Student s WHERE e.StudentID=s.StudentID); 1st tuple in Enrol, EXISTS 2nd tuple in Enrol, EXISTS 3rd tuple in Enrol, EXISTS StudentID Name 111 Tom StudentID Name 222 Emily StudentID Name 111 Tom The above query (returning 3 instead of 2) is incorrect! 65/81 Subqueries EXISTS – Example STUDENT StudentID Name 111 Tom 222 Emily 333 John ENROL StudentID CourseNo Semester 111 BUSN2011 2016 S1 222 COMP2400 2016 S1 111 COMP2400 2016 S2 Count the number of students who have enrolled in at least one course? SELECT COUNT(*) FROM Enrol e WHERE EXISTS (SELECT * FROM Student s WHERE e.StudentID=s.StudentID); 1st tuple in Enrol, EXISTS 2nd tuple in Enrol, EXISTS 3rd tuple in Enrol, EXISTS StudentID Name 111 Tom StudentID Name 222 Emily StudentID Name 111 Tom The above query (returning 3 instead of 2) is incorrect! 66/81 Subqueries EXISTS – Example STUDENT StudentID Name 111 Tom 222 Emily 333 John ENROL StudentID CourseNo Semester 111 BUSN2011 2016 S1 222 COMP2400 2016 S1 111 COMP2400 2016 S2 Count the number of students who have enrolled in at least one course? SELECT COUNT(*) FROM Student s WHERE EXISTS (SELECT * FROM Enrol e WHERE s.StudentID=e.StudentID); SELECT COUNT(*) FROM Student s WHERE EXISTS (SELECT StudentID FROM Enrol e WHERE s.StudentID=e.StudentID); Both queries are correct! EXISTS subquery tests whether the temporary table of the subquery is empty or not. 66/81 Subqueries EXISTS – Example STUDENT StudentID Name 111 Tom 222 Emily 333 John ENROL StudentID CourseNo Semester 111 BUSN2011 2016 S1 222 COMP2400 2016 S1 111 COMP2400 2016 S2 Count the number of students who have enrolled in at least one course? SELECT COUNT(*) FROM Student s WHERE EXISTS (SELECT * FROM Enrol e WHERE s.StudentID=e.StudentID); SELECT COUNT(*) FROM Student s WHERE EXISTS (SELECT StudentID FROM Enrol e WHERE s.StudentID=e.StudentID); Both queries are correct! EXISTS subquery tests whether the temporary table of the subquery is empty or not. 67/81 Using Cartesian Product – Same Example STUDENT StudentID Name 111 Tom 222 Emily 333 John ENROL StudentID CourseNo Semester 111 BUSN2011 2016 S1 222 COMP2400 2016 S1 111 COMP2400 2016 S2 Count the number of students who have enrolled in at least one course? SELECT COUNT(*) FROM Student, Enrol WHERE Student.StudentID=Enrol.StudentID; STUDENT ENROL StudentID Name StudentID CourseNo Semester 111 Tom 111 BUSN2011 2016 S1 111 Tom 111 COMP2400 2016 S2 222 Emily 222 COMP2400 2016 S1 The above query is incorrect! We should use COUNT(DISTINCT StudentID) instead of COUNT(*). 67/81 Using Cartesian Product – Same Example STUDENT StudentID Name 111 Tom 222 Emily 333 John ENROL StudentID CourseNo Semester 111 BUSN2011 2016 S1 222 COMP2400 2016 S1 111 COMP2400 2016 S2 Count the number of students who have enrolled in at least one course? SELECT COUNT(*) FROM Student, Enrol WHERE Student.StudentID=Enrol.StudentID; STUDENT ENROL StudentID Name StudentID CourseNo Semester 111 Tom 111 BUSN2011 2016 S1 111 Tom 111 COMP2400 2016 S2 222 Emily 222 COMP2400 2016 S1 The above query is incorrect! We should use COUNT(DISTINCT StudentID) instead of COUNT(*). 67/81 Using Cartesian Product – Same Example STUDENT StudentID Name 111 Tom 222 Emily 333 John ENROL StudentID CourseNo Semester 111 BUSN2011 2016 S1 222 COMP2400 2016 S1 111 COMP2400 2016 S2 Count the number of students who have enrolled in at least one course? SELECT COUNT(*) FROM Student, Enrol WHERE Student.StudentID=Enrol.StudentID; STUDENT ENROL StudentID Name StudentID CourseNo Semester 111 Tom 111 BUSN2011 2016 S1 111 Tom 111 COMP2400 2016 S2 222 Emily 222 COMP2400 2016 S1 The above query is incorrect! We should use COUNT(DISTINCT StudentID) instead of COUNT(*). 67/81 Using Cartesian Product – Same Example STUDENT StudentID Name 111 Tom 222 Emily 333 John ENROL StudentID CourseNo Semester 111 BUSN2011 2016 S1 222 COMP2400 2016 S1 111 COMP2400 2016 S2 Count the number of students who have enrolled in at least one course? SELECT COUNT(*) FROM Student, Enrol WHERE Student.StudentID=Enrol.StudentID; STUDENT ENROL StudentID Name StudentID CourseNo Semester 111 Tom 111 BUSN2011 2016 S1 111 Tom 111 COMP2400 2016 S2 222 Emily 222 COMP2400 2016 S1 The above query is incorrect! We should use COUNT(DISTINCT StudentID) instead of COUNT(*). 68/81 Using INNER JOIN – Same Example STUDENT StudentID Name 111 Tom 222 Emily 333 John ENROL StudentID CourseNo Semester 111 BUSN2011 2016 S1 222 COMP2400 2016 S1 111 COMP2400 2016 S2 Count the number of students who have enrolled in at least one course? SELECT COUNT(*) FROM Student s INNER JOIN Enrol e ON s.StudentID=e.StudentID; s e StudentID Name StudentID CourseNo Semester 111 Tom 111 BUSN2011 2016 S1 111 Tom 111 COMP2400 2016 S2 222 Emily 222 COMP2400 2016 S1 The above query is incorrect! We should use COUNT(DISTINCT StudentID) instead of COUNT(*). 68/81 Using INNER JOIN – Same Example STUDENT StudentID Name 111 Tom 222 Emily 333 John ENROL StudentID CourseNo Semester 111 BUSN2011 2016 S1 222 COMP2400 2016 S1 111 COMP2400 2016 S2 Count the number of students who have enrolled in at least one course? SELECT COUNT(*) FROM Student s INNER JOIN Enrol e ON s.StudentID=e.StudentID; s e StudentID Name StudentID CourseNo Semester 111 Tom 111 BUSN2011 2016 S1 111 Tom 111 COMP2400 2016 S2 222 Emily 222 COMP2400 2016 S1 The above query is incorrect! We should use COUNT(DISTINCT StudentID) instead of COUNT(*). 68/81 Using INNER JOIN – Same Example STUDENT StudentID Name 111 Tom 222 Emily 333 John ENROL StudentID CourseNo Semester 111 BUSN2011 2016 S1 222 COMP2400 2016 S1 111 COMP2400 2016 S2 Count the number of students who have enrolled in at least one course? SELECT COUNT(*) FROM Student s INNER JOIN Enrol e ON s.StudentID=e.StudentID; s e StudentID Name StudentID CourseNo Semester 111 Tom 111 BUSN2011 2016 S1 111 Tom 111 COMP2400 2016 S2 222 Emily 222 COMP2400 2016 S1 The above query is incorrect! We should use COUNT(DISTINCT StudentID) instead of COUNT(*). 68/81 Using INNER JOIN – Same Example STUDENT StudentID Name 111 Tom 222 Emily 333 John ENROL StudentID CourseNo Semester 111 BUSN2011 2016 S1 222 COMP2400 2016 S1 111 COMP2400 2016 S2 Count the number of students who have enrolled in at least one course? SELECT COUNT(*) FROM Student s INNER JOIN Enrol e ON s.StudentID=e.StudentID; s e StudentID Name StudentID CourseNo Semester 111 Tom 111 BUSN2011 2016 S1 111 Tom 111 COMP2400 2016 S2 222 Emily 222 COMP2400 2016 S1 The above query is incorrect! We should use COUNT(DISTINCT StudentID) instead of COUNT(*). 69/81 Using NATURAL JOIN – Same Example STUDENT StudentID Name 111 Tom 222 Emily 333 John ENROL StudentID CourseNo Semester 111 BUSN2011 2016 S1 222 COMP2400 2016 S1 111 COMP2400 2016 S2 Count the number of students who have enrolled in at least one course? SELECT COUNT(*) FROM Student NATURAL JOIN Enrol; STUDENT ENROL StudentID Name CourseNo Semester 111 Tom BUSN2011 2016 S1 111 Tom COMP2400 2016 S2 222 Emily COMP2400 2016 S1 The above query is incorrect! We should use COUNT(DISTINCT StudentID) instead of COUNT(*). 69/81 Using NATURAL JOIN – Same Example STUDENT StudentID Name 111 Tom 222 Emily 333 John ENROL StudentID CourseNo Semester 111 BUSN2011 2016 S1 222 COMP2400 2016 S1 111 COMP2400 2016 S2 Count the number of students who have enrolled in at least one course? SELECT COUNT(*) FROM Student NATURAL JOIN Enrol; STUDENT ENROL StudentID Name CourseNo Semester 111 Tom BUSN2011 2016 S1 111 Tom COMP2400 2016 S2 222 Emily COMP2400 2016 S1 The above query is incorrect! We should use COUNT(DISTINCT StudentID) instead of COUNT(*). 69/81 Using NATURAL JOIN – Same Example STUDENT StudentID Name 111 Tom 222 Emily 333 John ENROL StudentID CourseNo Semester 111 BUSN2011 2016 S1 222 COMP2400 2016 S1 111 COMP2400 2016 S2 Count the number of students who have enrolled in at least one course? SELECT COUNT(*) FROM Student NATURAL JOIN Enrol; STUDENT ENROL StudentID Name CourseNo Semester 111 Tom BUSN2011 2016 S1 111 Tom COMP2400 2016 S2 222 Emily COMP2400 2016 S1 The above query is incorrect! We should use COUNT(DISTINCT StudentID) instead of COUNT(*). 69/81 Using NATURAL JOIN – Same Example STUDENT StudentID Name 111 Tom 222 Emily 333 John ENROL StudentID CourseNo Semester 111 BUSN2011 2016 S1 222 COMP2400 2016 S1 111 COMP2400 2016 S2 Count the number of students who have enrolled in at least one course? SELECT COUNT(*) FROM Student NATURAL JOIN Enrol; STUDENT ENROL StudentID Name CourseNo Semester 111 Tom BUSN2011 2016 S1 111 Tom COMP2400 2016 S2 222 Emily COMP2400 2016 S1 The above query is incorrect! We should use COUNT(DISTINCT StudentID) instead of COUNT(*). 70/81 A Simple Solution – Same Example STUDENT StudentID Name 111 Tom 222 Emily 333 John ENROL StudentID CourseNo Semester 111 BUSN2011 2016 S1 222 COMP2400 2016 S1 111 COMP2400 2016 S2 Count the number of students who have enrolled in at least one course? SELECT COUNT(DISTINCT StudentID) FROM Enrol; The above query is correct! Is this the shortest query to answer the above question? Refer to the last slide on “[Credit Cookie] The Shortest Code/Program?”. 70/81 A Simple Solution – Same Example STUDENT StudentID Name 111 Tom 222 Emily 333 John ENROL StudentID CourseNo Semester 111 BUSN2011 2016 S1 222 COMP2400 2016 S1 111 COMP2400 2016 S2 Count the number of students who have enrolled in at least one course? SELECT COUNT(DISTINCT StudentID) FROM Enrol; The above query is correct! Is this the shortest query to answer the above question? Refer to the last slide on “[Credit Cookie] The Shortest Code/Program?”. 70/81 A Simple Solution – Same Example STUDENT StudentID Name 111 Tom 222 Emily 333 John ENROL StudentID CourseNo Semester 111 BUSN2011 2016 S1 222 COMP2400 2016 S1 111 COMP2400 2016 S2 Count the number of students who have enrolled in at least one course? SELECT COUNT(DISTINCT StudentID) FROM Enrol; The above query is correct! Is this the shortest query to answer the above question? Refer to the last slide on “[Credit Cookie] The Shortest Code/Program?”. 71/81 Subqueries – More Examples List the courses that have the largest number of students enrolled in Semester 2 2016 List the CourseNo and the corresponding number of students enrolled for all courses in Semester 2 2016 SELECT CourseNo, COUNT(*) AS NoOfStudents FROM Enrol WHERE Semester = '2016 S2' GROUP BY CourseNo; List the largest number of students enrolled in a course in Semester 2 2016 SELECT MAX(NoOfStudents) FROM (SELECT CourseNo, COUNT(*) AS NoOfStudents FROM Enrol WHERE Semester = '2016 S2' GROUP BY CourseNo); 71/81 Subqueries – More Examples List the courses that have the largest number of students enrolled in Semester 2 2016 List the CourseNo and the corresponding number of students enrolled for all courses in Semester 2 2016 SELECT CourseNo, COUNT(*) AS NoOfStudents FROM Enrol WHERE Semester = '2016 S2' GROUP BY CourseNo; List the largest number of students enrolled in a course in Semester 2 2016 SELECT MAX(NoOfStudents) FROM (SELECT CourseNo, COUNT(*) AS NoOfStudents FROM Enrol WHERE Semester = '2016 S2' GROUP BY CourseNo); 71/81 Subqueries – More Examples List the courses that have the largest number of students enrolled in Semester 2 2016 List the CourseNo and the corresponding number of students enrolled for all courses in Semester 2 2016 SELECT CourseNo, COUNT(*) AS NoOfStudents FROM Enrol WHERE Semester = '2016 S2' GROUP BY CourseNo; List the largest number of students enrolled in a course in Semester 2 2016 SELECT MAX(NoOfStudents) FROM (SELECT CourseNo, COUNT(*) AS NoOfStudents FROM Enrol WHERE Semester = '2016 S2' GROUP BY CourseNo); 71/81 Subqueries – More Examples List the courses that have the largest number of students enrolled in Semester 2 2016 List the CourseNo and the corresponding number of students enrolled for all courses in Semester 2 2016 SELECT CourseNo, COUNT(*) AS NoOfStudents FROM Enrol WHERE Semester = '2016 S2' GROUP BY CourseNo; List the largest number of students enrolled in a course in Semester 2 2016 SELECT MAX(NoOfStudents) FROM (SELECT CourseNo, COUNT(*) AS NoOfStudents FROM Enrol WHERE Semester = '2016 S2' GROUP BY CourseNo); 71/81 Subqueries – More Examples List the courses that have the largest number of students enrolled in Semester 2 2016 List the CourseNo and the corresponding number of students enrolled for all courses in Semester 2 2016 SELECT CourseNo, COUNT(*) AS NoOfStudents FROM Enrol WHERE Semester = '2016 S2' GROUP BY CourseNo; List the largest number of students enrolled in a course in Semester 2 2016 SELECT MAX(NoOfStudents) FROM (SELECT CourseNo, COUNT(*) AS NoOfStudents FROM Enrol WHERE Semester = '2016 S2' GROUP BY CourseNo); 72/81 Subqueries – More Complicated List the courses that have the largest number of students enrolled in Semester 2 2016 SELECT e.CourseNo FROM (SELECT e1.CourseNo, COUNT(*) AS NoOfStudents FROM Enrol e1 WHERE e1.Semester = '2016 S2' GROUP BY e1.CourseNo) e WHERE e.NoOfStudents = (SELECT MAX(e2.NoOfStudents) FROM (SELECT e1.CourseNo, COUNT(*) AS NoOfStudents FROM Enrol e1 WHERE e1.Semester = '2016 S2' GROUP BY e1.CourseNo) e2); 73/81 Subqueries – More Complicated List the courses that have the largest number of students enrolled in Semester 2 2016 Use “WITH” to break down complicated queries into simpler parts.1 WITH Sem2Students AS (SELECT e1.CourseNo, COUNT(*) AS NoOfStudents FROM Enrol e1 WHERE e1.Semester = '2016 S2' GROUP BY e1.CourseNo) SELECT e.CourseNo FROM Sem2Students e WHERE e.NoOfStudents = (SELECT MAX(e2.NoOfStudents) FROM Sem2Students e2); 1 https://www.postgresql.org/docs/current/static/queries-with.html https://www.postgresql.org/docs/current/static/queries-with.html 73/81 Subqueries – More Complicated List the courses that have the largest number of students enrolled in Semester 2 2016 Use “WITH” to break down complicated queries into simpler parts.1 WITH Sem2Students AS (SELECT e1.CourseNo, COUNT(*) AS NoOfStudents FROM Enrol e1 WHERE e1.Semester = '2016 S2' GROUP BY e1.CourseNo) SELECT e.CourseNo FROM Sem2Students e WHERE e.NoOfStudents = (SELECT MAX(e2.NoOfStudents) FROM Sem2Students e2); 1 https://www.postgresql.org/docs/current/static/queries-with.html https://www.postgresql.org/docs/current/static/queries-with.html 74/81 Subqueries – More Complicated List the courses that have the largest number of students enrolled in Semester 2 2016 Input: ENROL StudentID CourseNo Semester 111 BUSN2011 2016 S2 111 COMP1100 2016 S2 111 COMP2400 2016 S2 111 ECON2102 2016 S2 222 BUSN2011 2016 S2 222 COMP2400 2016 S2 333 BUSN2011 2016 S2 333 COMP2400 2016 S2 333 ECON2102 2016 S2 Output: CourseNo COMP2400 BUSN2011 74/81 Subqueries – More Complicated List the courses that have the largest number of students enrolled in Semester 2 2016 Input: ENROL StudentID CourseNo Semester 111 BUSN2011 2016 S2 111 COMP1100 2016 S2 111 COMP2400 2016 S2 111 ECON2102 2016 S2 222 BUSN2011 2016 S2 222 COMP2400 2016 S2 333 BUSN2011 2016 S2 333 COMP2400 2016 S2 333 ECON2102 2016 S2 Output: CourseNo COMP2400 BUSN2011 75/81 Subqueries – More Examples List all the courses that have more students enrolled than at least one other course in Semester 2 2016 SELECT e.CourseNo FROM (SELECT e1.CourseNo, COUNT(*) AS NoOfStudents FROM Enrol e1 WHERE e1.Semester = '2016 S2' GROUP BY e1.CourseNo) e WHERE e.NoOfStudents > ANY (SELECT e2.NoOfStudents
FROM (SELECT e1.CourseNo, COUNT(*) AS NoOfStudents
FROM Enrol e1
WHERE e1.Semester = ‘2016 S2’
GROUP BY e1.CourseNo) e2);
75/81
Subqueries – More Examples
List all the courses that have more students enrolled than at least one other
course in Semester 2 2016
SELECT e.CourseNo
FROM (SELECT e1.CourseNo, COUNT(*) AS NoOfStudents
FROM Enrol e1
WHERE e1.Semester = ‘2016 S2’
GROUP BY e1.CourseNo) e
WHERE e.NoOfStudents
> ANY (SELECT e2.NoOfStudents
FROM (SELECT e1.CourseNo, COUNT(*) AS NoOfStudents
FROM Enrol e1
WHERE e1.Semester = ‘2016 S2’
GROUP BY e1.CourseNo) e2);
76/81
Subqueries – More Examples
List all the courses that have more students enrolled than at least one other
course in Semester 2 2016
WITH Sem2Students AS
(SELECT e1.CourseNo, COUNT(*) AS NoOfStudents
FROM Enrol e1
WHERE e1.Semester = ‘2016 S2’
GROUP BY e1.CourseNo)
SELECT e.CourseNo
FROM Sem2Students e
WHERE e.NoOfStudents
> ANY (SELECT e2.NoOfStudents
FROM Sem2Students e2);
76/81
Subqueries – More Examples
List all the courses that have more students enrolled than at least one other
course in Semester 2 2016
WITH Sem2Students AS
(SELECT e1.CourseNo, COUNT(*) AS NoOfStudents
FROM Enrol e1
WHERE e1.Semester = ‘2016 S2’
GROUP BY e1.CourseNo)
SELECT e.CourseNo
FROM Sem2Students e
WHERE e.NoOfStudents
> ANY (SELECT e2.NoOfStudents
FROM Sem2Students e2);
77/81
Subqueries – More Complicated
List all the courses that have more students enrolled than at least one other
course in Semester 2 2016
Input:
ENROL
StudentID CourseNo Semester
111 BUSN2011 2016 S2
111 COMP1100 2016 S2
111 COMP2400 2016 S2
111 ECON2102 2016 S2
222 BUSN2011 2016 S2
222 COMP2400 2016 S2
333 BUSN2011 2016 S2
333 COMP2400 2016 S2
333 ECON2102 2016 S2
Output:
CourseNo
COMP2400
BUSN2011
ECON2102
77/81
Subqueries – More Complicated
List all the courses that have more students enrolled than at least one other
course in Semester 2 2016
Input:
ENROL
StudentID CourseNo Semester
111 BUSN2011 2016 S2
111 COMP1100 2016 S2
111 COMP2400 2016 S2
111 ECON2102 2016 S2
222 BUSN2011 2016 S2
222 COMP2400 2016 S2
333 BUSN2011 2016 S2
333 COMP2400 2016 S2
333 ECON2102 2016 S2
Output:
CourseNo
COMP2400
BUSN2011
ECON2102
78/81
Subqueries – More Examples
List all students’ IDs and names who are under-enrolled (< 4 courses) in Semester 2 2016, and the number of courses they are enrolled in. List the students’ IDs and the corresponding number of enrolled courses in Semester 2 2016 SELECT e.StudentID, COUNT(*) AS NoOfEnrols FROM Enrol e WHERE e.Semester = '2016 S2' GROUP BY e.StudentID; 78/81 Subqueries – More Examples List all students’ IDs and names who are under-enrolled (< 4 courses) in Semester 2 2016, and the number of courses they are enrolled in. List the students’ IDs and the corresponding number of enrolled courses in Semester 2 2016 SELECT e.StudentID, COUNT(*) AS NoOfEnrols FROM Enrol e WHERE e.Semester = '2016 S2' GROUP BY e.StudentID; 78/81 Subqueries – More Examples List all students’ IDs and names who are under-enrolled (< 4 courses) in Semester 2 2016, and the number of courses they are enrolled in. List the students’ IDs and the corresponding number of enrolled courses in Semester 2 2016 SELECT e.StudentID, COUNT(*) AS NoOfEnrols FROM Enrol e WHERE e.Semester = '2016 S2' GROUP BY e.StudentID; 79/81 Subqueries – More Examples List all students’ IDs and names who are under-enrolled (< 4 courses) in Semester 2 2016, and the number of courses they are enrolled in. SELECT s.StudentID, s.Name, ne.NoOfEnrols FROM (SELECT e.StudentID, COUNT(*) AS NoOfEnrols FROM Enrol e WHERE e.Semester = '2016 S2' GROUP BY e.StudentID) ne INNER JOIN Student s ON (s.StudentID = ne.StudentID) AND (ne.NoOfEnrols < 4); WITH StudEnrols AS ( SELECT e.StudentID, COUNT(*) AS NoOfEnrols FROM Enrol e WHERE e.Semester = '2016 S2' GROUP BY e.StudentID) SELECT s.StudentID, s.Name, ne.NoOfEnrols FROM Student s INNER JOIN StudEnrols ne ON (s.StudentID = ne.StudentID) AND (ne.NoOfEnrols < 4); 79/81 Subqueries – More Examples List all students’ IDs and names who are under-enrolled (< 4 courses) in Semester 2 2016, and the number of courses they are enrolled in. SELECT s.StudentID, s.Name, ne.NoOfEnrols FROM (SELECT e.StudentID, COUNT(*) AS NoOfEnrols FROM Enrol e WHERE e.Semester = '2016 S2' GROUP BY e.StudentID) ne INNER JOIN Student s ON (s.StudentID = ne.StudentID) AND (ne.NoOfEnrols < 4); WITH StudEnrols AS ( SELECT e.StudentID, COUNT(*) AS NoOfEnrols FROM Enrol e WHERE e.Semester = '2016 S2' GROUP BY e.StudentID) SELECT s.StudentID, s.Name, ne.NoOfEnrols FROM Student s INNER JOIN StudEnrols ne ON (s.StudentID = ne.StudentID) AND (ne.NoOfEnrols < 4); 79/81 Subqueries – More Examples List all students’ IDs and names who are under-enrolled (< 4 courses) in Semester 2 2016, and the number of courses they are enrolled in. SELECT s.StudentID, s.Name, ne.NoOfEnrols FROM (SELECT e.StudentID, COUNT(*) AS NoOfEnrols FROM Enrol e WHERE e.Semester = '2016 S2' GROUP BY e.StudentID) ne INNER JOIN Student s ON (s.StudentID = ne.StudentID) AND (ne.NoOfEnrols < 4); WITH StudEnrols AS ( SELECT e.StudentID, COUNT(*) AS NoOfEnrols FROM Enrol e WHERE e.Semester = '2016 S2' GROUP BY e.StudentID) SELECT s.StudentID, s.Name, ne.NoOfEnrols FROM Student s INNER JOIN StudEnrols ne ON (s.StudentID = ne.StudentID) AND (ne.NoOfEnrols < 4); 80/81 Subqueries – More Examples List all students’ IDs and names who are under-enrolled (< 4 courses) in Semester 2 2016, and the number of courses they are enrolled in. ENROL StudentID CourseNo Semester 111 BUSN2011 2016 S2 111 COMP1100 2016 S2 111 COMP2400 2016 S2 111 ECON2102 2016 S2 222 BUSN2011 2016 S2 222 COMP2400 2016 S2 333 BUSN2011 2016 S2 333 COMP2400 2016 S2 333 ECON2102 2016 S2 STUDENT StudentID Name 111 Tom 222 Emily 333 John Result: StudentID Name NoOfEnrols 222 Emily 2 333 John 3 80/81 Subqueries – More Examples List all students’ IDs and names who are under-enrolled (< 4 courses) in Semester 2 2016, and the number of courses they are enrolled in. ENROL StudentID CourseNo Semester 111 BUSN2011 2016 S2 111 COMP1100 2016 S2 111 COMP2400 2016 S2 111 ECON2102 2016 S2 222 BUSN2011 2016 S2 222 COMP2400 2016 S2 333 BUSN2011 2016 S2 333 COMP2400 2016 S2 333 ECON2102 2016 S2 STUDENT StudentID Name 111 Tom 222 Emily 333 John Result: StudentID Name NoOfEnrols 222 Emily 2 333 John 3 81/81 [Credit Cookie] The Shortest Code/Program? Occam’s razor is the problem-solving principle that “entities should not be multiplied beyond necessity”. The minimum description length of a data set (i.e., Kolmogorov complexity) cannot be computed. https://en.wikipedia.org/wiki/Andrey_Kolmogorov https://en.wikipedia.org/wiki/Andrey_Kolmogorov 81/81 [Credit Cookie] The Shortest Code/Program? Occam’s razor is the problem-solving principle that “entities should not be multiplied beyond necessity”. The minimum description length of a data set (i.e., Kolmogorov complexity) cannot be computed. https://en.wikipedia.org/wiki/Andrey_Kolmogorov https://en.wikipedia.org/wiki/Andrey_Kolmogorov