Bases de Données Fall, 2022
TD 1
Here are the tables we used in class:
course id title dept name credits id name dept name salary
BIO-101 Intro. to Biology Biology 4
10101 Srinivasan Comp. Sci. 65000.00
BIO-301 Genetics Biology 4
12121 Wu Finance 90000.00
BIO-399 Computational Biology Biology 3
15151 Mozart Music 40000.00
CS-101 Intro. to Computer Science Comp. Sci. 4
22222 Einstein Physics 95000.00
CS-190 Game Design Comp. Sci. 4
32343 El Said History 60000.00
CS-315 Robotics Comp. Sci. 3
33456 Gold Physics 87000.00
CS-319 Image Processing Comp. Sci. 3
45565 Katz Comp. Sci. 75000.00
CS-347 Database System Concepts Comp. Sci. 3
58583 Califieri History 62000.00
EE-181 Intro. to Digital Systems Elec. Eng. 3
76543 Singh Finance 80000.00
FIN-201 Investment Banking Finance 3
76766 Crick Biology 72000.00
HIS-351 World History History 3
83821 Brandt Comp. Sci. 92000.00
MU-199 Music Video Production Music 3
98345 Kim Elec. Eng. 80000.00
PHY-101 Physical Principles Physics 4
(a) course (b) teacher
course id sec id semester year building rn time id
id name dept name tot cred BIO-101 1 Summer 2009 Painter 514 B
00128 Zhang Comp. Sci. 102 BIO-301 1 Summer 2010 Painter 514 A
12345 Shankar Comp. Sci. 32 CS-101 1 Fall 2009 Packard 101 H
19991 Brandt History 80 CS-101 1 Spring 2010 Packard 101 F
23121 Chavez Finance 110 CS-190 1 Spring 2009 Taylor 3128 E
44553 Peltier Physics 56 CS-190 2 Spring 2009 Taylor 3128 A
45678 Levy Physics 46 CS-315 1 Spring 2010 Watson 120 D
54321 Williams Comp. Sci. 54 CS-319 1 Spring 2010 Watson 100 B
55739 Sanchez Music 38 CS-319 2 Spring 2010 Taylor 3128 C
70557 Snow Physics 0 CS-347 1 Fall 2009 Taylor 3128 A
76543 Brown Comp. Sci. 58 EE-181 1 Spring 2009 Taylor 3128 C
76653 Aoi Elec. Eng. 60 FIN-201 1 Spring 2010 Packard 101 B
98765 Bourikas Elec. Eng. 98 HIS-351 1 Spring 2010 Painter 514 C
98988 Tanaka Biology 120 MU-199 1 Spring 2010 Packard 101 D
PHY-101 1 Fall 2009 Watson 100 A
(c) student
(d) section
id course id sec id semester year grade
00128 CS-101 1 Fall 2009 A
00128 CS-347 1 Fall 2009 A-
12345 CS-101 1 Fall 2009 C
id course id sec id semester year 12345 CS-190 2 Spring 2009 A
10101 CS-101 1 Fall 2009
12345 CS-315 1 Spring 2010 A
10101 CS-315 1 Spring 2010
12345 CS-347 1 Fall 2009 A
10101 CS-347 1 Fall 2009
19991 HIS-351 1 Spring 2010 B
12121 FIN-201 1 Spring 2010
23121 FIN-201 1 Spring 2010 C+
15151 MU-199 1 Spring 2010
44553 PHY-101 1 Fall 2009 B-
22222 PHY-101 1 Fall 2009
45678 CS-101 1 Fall 2009 F
32343 HIS-351 1 Spring 2010
45678 CS-101 1 Spring 2010 B+
45565 CS-101 1 Spring 2010
45678 CS-319 1 Spring 2010 B
45565 CS-319 1 Spring 2010
54321 CS-101 1 Fall 2009 A-
76766 BIO-101 1 Summer 2009
54321 CS-190 2 Spring 2009 B+
76766 BIO-301 1 Summer 2010
55739 MU-199 1 Spring 2010 A-
83821 CS-190 1 Spring 2009
76543 CS-101 1 Fall 2009 A
83821 CS-190 2 Spring 2009
76543 CS-319 2 Spring 2010 A
83821 CS-319 2 Spring 2010
76653 EE-181 1 Spring 2009 C
98345 EE-181 1 Spring 2009
98765 CS-101 1 Fall 2009 C-
98765 CS-315 1 Spring 2010 B
(e) teaches 98988 BIO-101 1 Summer 2009 A
98988 BIO-301 1 Summer 2010
dept name building budget (f) takes
Biology Watson 90000.00
Comp. Sci. Taylor 100000.00
Elec. Eng. Taylor 85000.00
Finance Painter 120000.00
History Painter 50000.00
Music Packard 80000.00
Physics Watson 70000.00
(g) department
1. Find the titles of courses in Comp. Sci. department that have 3 credits.
SELECT course . title
FROM course
WHERE credits =3 and dept_name = ' Comp . Sci . ';
title
Robotics
Image Processing
Database System Concepts
2. Find the names of all instructors, along with their department names and department building name.
SELECT teacher . name , teacher . dept_name , department . building
FROM teacher , department
WHERE teacher . dept_name = department . dept_name ;
name dept name building
Srinivasan Comp. Sci. Taylor
Wu Finance Painter
Mozart Music Packard
Einstein Physics Watson
El Said History Painter
Gold Physics Watson
Katz Comp. Sci. Taylor
Califieri History Painter
Singh Finance Painter
Crick Biology Watson
Brandt Comp. Sci. Taylor
Kim Elec. Eng. Taylor
3. Find the names (without duplicates) of all students who have taken at least one Comp. Sci. course.
SELECT distinct student . name
FROM student , takes , course
WHERE student . id = takes . id and takes . course_id = course .
course_id and course . dept_name = ' Comp . Sci . ';
name
Bourikas
Brown
Levy
Shankar
Williams
Zhang
4. Find the names of all students who were taught by an instructor named Einstein; make sure there are no
duplicates in the result.
SELECT distinct student . name
FROM student , teacher , takes , teaches
WHERE student . id = takes . id and takes . course_id = teaches .
course_id and takes . sec_id = teaches . sec_id and takes .
semester = teaches . semester and takes . year = teaches . year and
teaches . id = teacher . id and teacher . name = ' Einstein ';
Another way to write the same query :
SELECT distinct student . name
FROM student , teacher , takes , teaches
WHERE student . id = takes . id and ( takes . course_id , takes . sec_id ,
takes . semester , takes . year ) = ( teaches . course_id , teaches .
sec_id , teaches . semester , teaches . year ) and teaches . id =
teacher . id and teacher . name = ' Einstein ';
name
Peltier
5. Find the list of all taught courses ids and their teachers’ name.
SELECT name , course_id
FROM teacher , teaches
WHERE teacher . id = teaches . id ;
name course id
Srinivasan CS-101
Srinivasan CS-315
Srinivasan CS-347
Wu FIN-201
Mozart MU-199
Einstein PHY-101
El Said HIS-351
Katz CS-101
Katz CS-319
Crick BIO-101
Crick BIO-301
Brandt CS-190
Brandt CS-190
Brandt CS-319
Kim EE-181
6. For each time a course was taught, output its title together with its teacher’s name.
SELECT course . title , teacher . name
FROM section , teacher , teaches , course
WHERE ( section . course_id , section . sec_id , section . semester ,
section . year ) = ( teaches . course_id , teaches . sec_id , teaches .
semester , teaches . year ) and teaches . id = teacher . id and
section . course_id = course . course_id ;
title name
Database System Concepts Srinivasan
Game Design Brandt
Game Design Brandt
Genetics Crick
Image Processing Brandt
Image Processing Katz
Intro. to Biology Crick
Intro. to Computer Science Srinivasan
Intro. to Computer Science Katz
Intro. to Digital Systems Kim
Investment Banking Wu
Music Video Production Mozart
Physical Principles Einstein
Robotics Srinivasan
World History El Said