Accessing the Database

The first laboratory exercise is to connect to a database, populate it with data, and run very simple SQL queries.

• In case a shared database is provided for course students, user accounts need to be created on the database. Otherwise, the lab should also cover setting up a database system. Information on setting up a database may be found here
• The next step is to connect to the database. Although most databases have their own text-based interface, we recommend using a graphical interface such as the database browser of the Netbeans IDE, or a database specific interface. More information on accessing these interfaces may be found here.
• The next step is to create tables and load sample data. Scripts for these tasks can be found here.
• Try out some queries, and see what they do. Some example queries:
  • select * from instructor
  • select name from instructor where dept_name = ‘Comp. Sci.’ and salary > 70000
  • select * from instructor, department where instructor.dept_name = department.dept_name

mysql> CREATE DATABASE school;
mysql> use school;
mysql> source \path\to\DDL-MySQL.sql
mysql> source \path\to\smallRelationsInsertFile.sql

Basic SQL

This lab covers simple SQL queries. A sample lab (for a 2 to 3 hour session) can be found here.

Find the names of all the instructors from Biology department

mysql> select name from instructor where dept_name = 'Biology';
+-------+
| name  |
+-------+
| Crick |
+-------+
1 row in set (0.02 sec)

Find the names of courses in Computer science department which have 3 credits

mysql> select title from course where credits = 3;
+---------------------------+
| title                     |
+---------------------------+
| Computational Biology     |
| Robotics                  |
| Image Processing          |
| Database System Concepts  |
| Intro. to Digital Systems |
| Investment Banking        |
| World History             |
| Music Video Production    |
+---------------------------+
8 rows in set (0.00 sec)

For the student with ID 12345 (or any other value), show all course id and title of all courses registered for by the student

mysql> select course_id, title
    -> from takes natural join course
    -> where ID = 12345;
+-----------+----------------------------+
| course_id | title                      |
+-----------+----------------------------+
| CS-101    | Intro. to Computer Science |
| CS-190    | Game Design                |
| CS-315    | Robotics                   |
| CS-347    | Database System Concepts   |
+-----------+----------------------------+
4 rows in set (0.02 sec)

As above, but show the total number of credits for such courses (taken by that student). Don’t display the tot creds value from the student table, you should use SQL aggregation on courses taken by the student

mysql> select sum(credits)
    -> from takes natural join course
    -> where ID = 12345;
+--------------+
| sum(credits) |
+--------------+
|           14 |
+--------------+
1 row in set (0.02 sec)

As above, but display the total credits for each of the students, along with the ID of the student; don’t bother about the name of the student. (Don’t bother about students who have not registered for any course, they can be omitted)

mysql> select ID, sum(credits)
    -> from takes natural join course
    -> group by ID;
+-------+--------------+
| ID    | sum(credits) |
+-------+--------------+
| 98988 |            8 |
| 00128 |            7 |
| 12345 |           14 |
| 45678 |           11 |
| 54321 |            8 |
| 76543 |            7 |
| 98765 |            7 |
| 76653 |            3 |
| 23121 |            3 |
| 19991 |            3 |
| 55739 |            3 |
| 44553 |            4 |
+-------+--------------+
12 rows in set (0.02 sec)

Find the names of all students who have taken any Comp. Sci. course ever (there should be no duplicate names)

mysql> select distinct S.name from student as S,
    -> (select * from takes natural join course) as C
    -> where S.ID = C.ID and C.dept_name = 'Comp. Sci.';
+----------+
| name     |
+----------+
| Zhang    |
| Shankar  |
| Levy     |
| Williams |
| Brown    |
| Bourikas |
+----------+
6 rows in set (0.00 sec)

Display the IDs of all instructors who have never taught a couse. Note: (1) Oracle uses the keyword minus in place of except; (2) interpret ”taught” as ”taught or is scheduled to teach”

mysql> select ID from instructor
    -> where ID not in (
    ->   select ID
    ->   from instructor natural join teaches);
+-------+
| ID    |
+-------+
| 76543 |
| 58583 |
| 33456 |
+-------+
3 rows in set (0.02 sec)

As above, but display the names of the instructors also, not just the IDs

mysql> select name, ID from instructor
    -> where ID not in (select ID from instructor natural join teaches);
+-----------+-------+
| name      | ID    |
+-----------+-------+
| Gold      | 33456 |
| Califieri | 58583 |
| Singh     | 76543 |
+-----------+-------+
3 rows in set (0.00 sec)

You need to create a movie database. Create three tables, one for actors(AID, name), one for movies(MID, title) and one for actor role(MID, AID, rolename). Use appropriate data types for each of the attributes, and add appropriate primary/foreign key constraints

create table actors (
  AID varchar(20),
  name varchar(50),
  primary key (AID)
);
create table movies (
  MID varchar(20),
  title varchar(50),
  primary key (MID)
);
create table actor_role (
  MID varchar(20),
  AID varchar(20),
  rolename varchar(30),
  primary key (MID,AID,rolename),
  foreign key (MID) references movies(MID),
  foreign key (AID) references actors(AID)
);

Insert data to the above tables (approx 3 to 6 rows in each table), including data for actor ”Charlie Chaplin”, and for yourself (using your roll number as ID)

delete from actor_role;
delete from actors;
delete from movies;
insert into actors values ('01','Charlie Chaplin');
insert into movies values ('M1','City Lights');
insert into actor_role values ('M1','01','Tramp');
insert into actors values ('02','Stephen Chow');
insert into movies values ('M2','Kung Fu Hustle');
insert into actor_role values ('M2','02','Kung Fu Hustle');
insert into actors values ('03','Jay Chou');
insert into movies values ('M3','Initial D');
insert into actor_role values ('M3','03','Fujiwara Takumi');
insert into actors values ('04','Kan Wu');

Write a query to list all movies in which actor ”Charlie Chaplin” has acted, along with the number of roles he had in that movie

mysql> select name, title, count(rolename)
    -> from actor_role natural join movies natural join actors
    -> where name = 'Charlie Chaplin' group by MID;
+-----------------+-------------+-----------------+
| name            | title       | count(rolename) |
+-----------------+-------------+-----------------+
| Charlie Chaplin | City Lights |               1 |
+-----------------+-------------+-----------------+
1 row in set (0.00 sec)

Write a query to list all actors who have not acted in any movie

没错就是我了。

mysql> select name from actors
    -> where name not in (
    ->   select distinct name
    ->   from actor_role natural join actors);
+--------+
| name   |
+--------+
| Kan Wu |
+--------+
1 row in set (0.00 sec)

List names of actors, along with titles of movies they have acted in. If they have not acted in any movie, show the movie title as null. (Do not use SQL outerjoin syntax here, write it from scratch.)

ERROR 1248 (42000): Every derived table must have its own alias

这里遇到一个问题，每个子查询都要有一个别名。

mysql> select * from (
    -> (select name, title
    -> from actors, movies, actor_role
    -> where actors.AID = actor_role.AID and movies.MID = actor_role.MID)
    -> union
    -> (select name as name, null as title from
    -> (select name from actors
    -> where name not in
    -> (select distinct name
    -> from actor_role natural join actors)
    -> ) as alias1)
    -> ) as alias2;
+-----------------+----------------+
| name            | title          |
+-----------------+----------------+
| Charlie Chaplin | City Lights    |
| Stephen Chow    | Kung Fu Hustle |
| Jay Chou        | Initial D      |
| Kan Wu          | NULL           |
+-----------------+----------------+
4 rows in set (0.00 sec)

Intermediate SQL

This lab covers more complex SQL queries. A sample lab (for a 2 to 3 hour session) can be found here.

Find the maximum and minimum enrollment across all sections, considering only sections that had some enrollment, don’t worry about those that had no students taking that section

mysql> select max(enrollment), min(enrollment)
    -> from (select sec_id, semester, year, count(distinct id) as enrollment
    -> from takes
    -> group by sec_id, semester, year) as alias1;
+-----------------+-----------------+
| max(enrollment) | min(enrollment) |
+-----------------+-----------------+
|               7 |               1 |
+-----------------+-----------------+
1 row in set (0.02 sec)

Find all sections that had the maximum enrollment (along with the enrollment), using a subquery

mysql> with T(sec_id, semester, year, enrollment) as (select sec_id, semester, year, count(distinct id)from takes group by sec_id, semester, year)
    -> select T.sec_id, T.semester, T.year, T.enrollment from T, (select max(enrollment) as ma, min(enrollment) from T) as tmp
    -> where T.enrollment = tmp.ma;
+--------+----------+------+------------+
| sec_id | semester | year | enrollment |
+--------+----------+------+------------+
| 1      | Fall     | 2009 |          7 |
+--------+----------+------+------------+
1 row in set (0.00 sec)

As in in Q1, but now also include sections with no students taking them; the enrollment for such sections should be treated as 0. Do this in two different ways (and create require data for testing)

delete from course where course_id = 'CS-001';
delete from section where sec_id = '1' and semester = 'Fall' and year = '2010';
insert into course(course_id) values ('CS-001');
insert into section(course_id, sec_id, semester, year) values ('CS-001','1','Fall','2010');

Using a scalar subquery

mysql> select distinct sec_id, semester, year, (
    ->   select count(distinct id) from takes
    ->   where (
    ->     takes.sec_id, takes.semester, takes.year
    ->   ) = (
    ->     section.sec_id, section.semester, section.year))
    -> as cnt from section;
+--------+----------+------+------+
| sec_id | semester | year | cnt  |
+--------+----------+------+------+
| 1      | Fall     | 2010 |    0 |
| 1      | Fall     | 2009 |    7 |
| 1      | Spring   | 2010 |    6 |
| 1      | Summer   | 2009 |    1 |
| 1      | Summer   | 2010 |    1 |
| 1      | Spring   | 2009 |    1 |
| 2      | Spring   | 2009 |    2 |
| 2      | Spring   | 2010 |    1 |
+--------+----------+------+------+
8 rows in set (0.00 sec)

Using aggregation on a left outer join (use the SQL natural left outer join syntax)

mysql> select distinct sec_id, semester, year, ifnull(cnt,0)
    -> from section left outer join (
    ->   select sec_id, semester, year, count(distinct id) as cnt
    ->   from takes group by sec_id, semester, year) as T
    -> using (sec_id, semester, year);
+--------+----------+------+---------------+
| sec_id | semester | year | ifnull(cnt,0) |
+--------+----------+------+---------------+
| 1      | Fall     | 2010 |             0 |
| 1      | Fall     | 2009 |             7 |
| 1      | Spring   | 2010 |             6 |
| 1      | Summer   | 2009 |             1 |
| 1      | Summer   | 2010 |             1 |
| 1      | Spring   | 2009 |             1 |
| 2      | Spring   | 2009 |             2 |
| 2      | Spring   | 2010 |             1 |
+--------+----------+------+---------------+
8 rows in set (0.00 sec)

Find all courses whose identifier starts with the string ”CS-1”

mysql> select course_id, title
    -> from section natural join course
    -> where course_id like 'CS-1%';
+-----------+----------------------------+
| course_id | title                      |
+-----------+----------------------------+
| CS-101    | Intro. to Computer Science |
| CS-101    | Intro. to Computer Science |
| CS-190    | Game Design                |
| CS-190    | Game Design                |
+-----------+----------------------------+
4 rows in set (0.00 sec)

Find instructors who have taught all the above courses

Using the ”not exists … except …” structure

mysql> select distinct ID, name from (
    ->   select * from teaches natural join instructor)
    -> as T where not exists (
    ->   select cs_course.course_id from (
    ->     select course_id from course
    ->     where course_id like 'CS-1%')
    ->   as cs_course where cs_course.course_id not in (
    ->     select course_id from (
    ->       select * from teaches natural join instructor)
    ->     as S where S.name = T.name));
Empty set (0.00 sec)

Using matching of counts which we covered in class (don’t forget the distinct clause!)

mysql> with S(course_id) as (
    ->   select distinct course_id
    ->   from teaches natural join instructor
    ->   where course_id like 'CS-1%')
    -> select distinct ID, name from (
    ->   select * from teaches natural join instructor) as T
    -> where ((select count(course_id) from S)=(
    ->   select count(distinct course_id)
    ->   from teaches natural join instructor
    ->   where name = T.name and course_id like 'CS-1%'
    -> ));
Empty set (0.00 sec)

Insert each instructor as a student, with tot creds = 0, in the same department

这里报错是因为学生 ID 和教师 ID 冲突。

mysql> insert into student
    -> select ID, name, dept_name, '0'
    -> from instructor;
ERROR 1062 (23000): Duplicate entry '76543' for key 'PRIMARY'

Now delete all the newly added ”students” above (note: already existing students who happened to have tot creds = 0 should not get deleted)

因为上一问报错了，因此这一问实际上什么都没删除。

mysql> delete from student
    -> where (ID, name, dept_name) in (
    ->   select ID, name, dept_name
    ->   from instructor);
Query OK, 0 rows affected (0.00 sec)

Some of you may have noticed that the tot creds value for students did not match the credits from courses they have taken. Write and execute query to update tot creds based on the credits passed, to bring the database back to consistency. (This query is provided in the book/slides.)

mysql> update student as S set tot_cred = (
    ->   select sum(credits)
    ->   from takes natural join course
    ->   where S.ID = takes.ID and takes.grade is not null);
Query OK, 13 rows affected (0.03 sec)
Rows matched: 13  Changed: 13  Warnings: 0

Update the salary of each instructor to 10000 times the number of course sections they have taught

这一步出现报错，和数据库的约束条件冲突。

mysql> update instructor as I set salary = 10000 * (
    ->   select count(distinct sec_id, semester, year)
    ->   from teaches as T where I.ID = T.ID);
ERROR 3819 (HY000): Check constraint 'instructor_chk_1' is violated.

Create your own query: define what you want to do in English, then write the query in SQL. Make it as difficult as you wish, the harder the better

这里统计的是上过任何一门由计算机系开设课程的学生数量。

mysql> select count(S.name) from student as S,(
    ->   select * from takes natural join course) as C
    -> where S.ID = C.ID and C.dept_name = 'Comp. Sci.';
+---------------+
| count(S.name) |
+---------------+
|            15 |
+---------------+
1 row in set (0.00 sec)