UIT2201: Tutorial Set 7 (Fall 2016)
(Solution Sketch to Selected Problems)
** NOT TO BE GIVEN TO FUTURE UIT2201 STUDENTS **
Solution sketches are provided as guidelines only.
Do not think of them as *model answers*.
As you have noticed from our tutorial discussions,
there can be many answers and approaches. So keep an open mind.
(More importantly, DO NOT CIRCULATE these solution sketches.)
|
Comments on designing SQL Queries:
0. Start with the SELECT... FROM... WHERE... template.
1. First analyze what fields (columns) are needed (directly/indirectly) in the SELECT part.
2. Analyze which tables these fields are from. Put them in the FROM part.
3. Put in the join-conditions (as necessary) -- in the WHERE part.
4. Put in other conditions needed by the problem-statement.
|
T7-D1: (Simple SQL Query) --- SOLUTION SKETCH
Problem 6 on page 617 (Ch. 13) of [SG3].
SELECT ID, PlanType
FROM Employees, InsurancePolicies
WHERE (Birthdate > #1/01/60#
AND (ID = EmployeeID);
|
|
|
Output of the Query:
| ID |
PlanType |
| 149 |
B2 |
| 149 |
A1 |
| 149 |
C2 |
|
Consider a database with 3 tables,
STUDENT-INFO,
COURSE-INFO, and
ENROLMENT.
Assume
•
the STUDENT-INFO table has 30,000 (3x104) rows,
•
the COURSE-INFO table has 1,000 (103) rows,
•
the ENROLMENT table has 100,000 (105) rows.
STUDENT-INFO
| Student-ID |
Name |
NRIC-ID |
Address |
Tel-No |
Faculty |
Major |
|
... |
... |
... |
... |
... |
... |
... |
COURSE-INFO
| Course-ID |
Name |
Day |
Hour |
Venue |
Instructor |
|
... |
... |
... |
... |
... |
... |
|
|
|
|
|
ENROLMENT
| Student-ID |
Course-ID |
|
... |
... |
|
T7-D2: (Efficient Query Processing) --- SOLUTION SKETCH
(a)
Answer: List Name, Faculty, Course-ID of all courses taken by student with Student-ID U2908888P.
SELECT Name, Faculty, Course-ID
FROM SI, EN
WHERE (Student-ID='U2908888P') and (SI.Student-ID = EN.Student-ID)
|
(b,c)(i)
List the Student-ID, Name, Tel-No of the all "CS" majors;
SELECT SI.Student-ID, SI.Name, SI.Tel-No
FROM SI
WHERE (SI.Major="CS")
|
Analysis: 30,000 row operations.
(b,c)(ii)
List the Student-ID, Name, Tel-No of the students enrolled in course
"UIT2201";
SELECT SI.Student-ID, SI.Name, SI.Tel-No
FROM SI, EN
WHERE (EN.Course-ID="UIT2201")
AND (SI.S-ID = EN.S-ID)
|
Analysis: Need a join of SI and EN. Needs 30,000 x 100,000 = 3x109 row operations.
(b,c)(iii)
List the Student-ID, Name, Tel-No of students
taught by instructor "LeongHW".
SELECT SI.Student-ID, SI.Name, SI.Tel-No
FROM SI, CI, EN
WHERE (CI.Instructor="LeongHW")
AND (SI.S-ID = EN.S-ID)
AND (CI.C-ID = EN.C-ID)
|
Analysis: Needs two joins:
Method-1: [S1: join SI and EN to get XX; then S2:join CI and XX]
S1: join needs 30000*100,000 = 3x109 row ops. Table XX has 100,000 rows)
S2: Join needs 1000*100,000 = 108 row ops
Total: 3.1x109 row ops;
UIT2201: CS & IT Revolution; (LeongHW, 2016)
[Prints well with A4-page-offsets l=r=b=0.5", t=0.7"]