CS计算机代考程序代写 SQL database algorithm 1/81

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