The first laboratory exercise is to connect to a database, populate it with data, and run very simple SQL queries.
mysql> CREATE DATABASE school;
mysql> use school;
mysql> source \path\to\DDL-MySQL.sql
mysql> source \path\to\smallRelationsInsertFile.sql
This lab covers simple SQL queries. A sample lab (for a 2 to 3 hour session) can be found here.
mysql> select name from instructor where dept_name = 'Biology';
+-------+
| name |
+-------+
| Crick |
+-------+
1 row in set (0.02 sec)
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)
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)
mysql> select sum(credits)
-> from takes natural join course
-> where ID = 12345;
+--------------+
| sum(credits) |
+--------------+
| 14 |
+--------------+
1 row in set (0.02 sec)
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)
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)
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)
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)
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)
);
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');
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)
没错就是我了。
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)
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)
This lab covers more complex SQL queries. A sample lab (for a 2 to 3 hour session) can be found here.
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)
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)
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');
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)
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)
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)
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)
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)
这里报错是因为学生 ID 和教师 ID 冲突。
mysql> insert into student
-> select ID, name, dept_name, '0'
-> from instructor;
ERROR 1062 (23000): Duplicate entry '76543' for key 'PRIMARY'
因为上一问报错了,因此这一问实际上什么都没删除。
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)
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
这一步出现报错,和数据库的约束条件冲突。
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.
这里统计的是上过任何一门由计算机系开设课程的学生数量。
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)