IDEA激活码实验目的：掌握基本的数据库查询命令和语法。
版权声明：本文实验参考自《数据库系统-实验指导书-湖南大学2019版》。
实验内容：按照下文中的步骤，在MySQL中执行SQL语句，逐一学习体会各SQL语句的用法。
---6 The Database Language SQL
create table LAPTOP
(
MODEL VARCHAR(30),
SPEED float(4,2),
RAM INT(38),
HD INT(38),
SCREEN INT(3),
PRICE INT(7)
);
create table MOVIEEXEC
(
NAME VARCHAR(30),
ADDRESS VARCHAR(80),
CERT INT(38) not null,
NETWORTH INT(38)
);
alter table MOVIEEXEC
add primary key (CERT);
create table MOVIES
(
TITLE VARCHAR(100) not null,
YEAR NUMERIC(38) not null,
LENGTH NUMERIC(38),
GENRE VARCHAR(30),
STUDIONAME VARCHAR(30),
PRODUCERC float(38)
);
alter table MOVIES
add primary key (TITLE, YEAR);
create table MOVIESTAR
(
NAME VARCHAR(30) not null,
ADDRESS VARCHAR(80),
GENDER CHAR(1),
BIRTHDATE DATE
);
alter table MOVIESTAR
add primary key (NAME);
create table PC
(
MODEL VARCHAR(30),
SPEED float(4,2),
RAM float(38),
HD float(38),
PRICE float(7,2)
);
create table PRINTER
(
MODEL VARCHAR(30),
COLOR VARCHAR(5),
TYPE VARCHAR(10),
PRICE float(7,2)
);
create table PRODUCT
(
MAKER VARCHAR(30),
MODEL VARCHAR(10) not null,
TYPE VARCHAR(15)
);
alter table PRODUCT
add primary key (MODEL);
create table STARSIN
(
MOVIETITLE VARCHAR(100) not null,
MOVIEYEAR float(38) not null,
STARNAME VARCHAR(30) not null
);
alter table STARSIN
add primary key (MOVIETITLE, MOVIEYEAR, STARNAME);
create table STUDIO
(
NAME VARCHAR(30) not null,
ADDRESS VARCHAR(80),
PRESC float(38)
);
alter table STUDIO
add primary key (NAME);
-- Loading LAPTOP...
insert into LAPTOP (MODEL, SPEED, RAM, HD, SCREEN, PRICE)
values ('2001', 2, 2048, 240, 20.1, 3673);
insert into LAPTOP (MODEL, SPEED, RAM, HD, SCREEN, PRICE)
values ('2002', 1.73, 1024, 80, 17, 949);
insert into LAPTOP (MODEL, SPEED, RAM, HD, SCREEN, PRICE)
values ('2003', 1.8, 512, 60, 15.4, 549);
insert into LAPTOP (MODEL, SPEED, RAM, HD, SCREEN, PRICE)
values ('2004', 2, 512, 60, 13.3, 1150);
insert into LAPTOP (MODEL, SPEED, RAM, HD, SCREEN, PRICE)
values ('2005', 2.16, 1024, 120, 17, 2500);
insert into LAPTOP (MODEL, SPEED, RAM, HD, SCREEN, PRICE)
values ('2006', 2, 2048, 80, 15.4, 1700);
insert into LAPTOP (MODEL, SPEED, RAM, HD, SCREEN, PRICE)
values ('2007', 1.83, 1024, 120, 13.3, 1429);
insert into LAPTOP (MODEL, SPEED, RAM, HD, SCREEN, PRICE)
values ('2008', 1.6, 1024, 100, 15.4, 900);
insert into LAPTOP (MODEL, SPEED, RAM, HD, SCREEN, PRICE)
values ('2009', 1.6, 512, 80, 14.1, 680);
insert into LAPTOP (MODEL, SPEED, RAM, HD, SCREEN, PRICE)
values ('2010', 2, 2048, 60, 15.4, 2300);
commit;
-- Loading MOVIEEXEC...
insert into MOVIEEXEC (NAME, ADDRESS, CERT, NETWORTH)
values ('Ted Kotcheff', 'Golden Ears Provincial Park', 98765, 9000);
insert into MOVIEEXEC (NAME, ADDRESS, CERT, NETWORTH)
values ('MaryTurner', '789 Palm Dr., Beverly Hills', 99999, 1000);
insert into MOVIEEXEC (NAME, ADDRESS, CERT, NETWORTH)
values ('Harrison Ford', '789 Palm Dr., Beverly Hills', 67890, 8000);
commit;
-- Loading MOVIES...
insert into MOVIES (TITLE, YEAR, LENGTH, GENRE, STUDIONAME, PRODUCERC)
values ('Pretty Woman', 1999, 119, 'romance', 'Disney', 99999);
insert into MOVIES (TITLE, YEAR, LENGTH, GENRE, STUDIONAME, PRODUCERC)
values ('The Da Vinci Code', 2006, 119, 'drama', 'Fox', 11456);
insert into MOVIES (TITLE, YEAR, LENGTH, GENRE, STUDIONAME, PRODUCERC)
values ('Love Story', 1989, 119, 'comedy','Fox',12568);
insert into MOVIES (TITLE, YEAR, LENGTH, GENRE, STUDIONAME, PRODUCERC)
values ('First Blood', 2008, 225, 'action', 'Anabasis', 98765);
insert into MOVIES (TITLE, YEAR, LENGTH, GENRE, STUDIONAME, PRODUCERC)
values ('First Blood', 1982, 220, 'action', 'Anabasis', 98765);
insert into MOVIES (TITLE, YEAR, LENGTH, GENRE, STUDIONAME, PRODUCERC)
values ('First Blood', 1988, 265, 'action', 'Anabasis', 98765);
insert into MOVIES (TITLE, YEAR, LENGTH, GENRE, STUDIONAME, PRODUCERC)
values ('First Blood', 1985, 220, 'action', 'Anabasis', 98765);
insert into MOVIES (TITLE, YEAR, LENGTH, GENRE, STUDIONAME, PRODUCERC)
values ('Gone With the Wind', 1939, 231, 'drama', 'DreamWorks', 12345);
insert into MOVIES (TITLE, YEAR, LENGTH, GENRE, STUDIONAME, PRODUCERC)
values ('Star Wars', 1977, 124, 'scifi', 'Fox', 67890);
insert into MOVIES (TITLE, YEAR, LENGTH, GENRE, STUDIONAME, PRODUCERC)
values ('Wayne"s World', 1992,95, 'comedy', 'Paramount', 99999);
insert into MOVIES (TITLE, YEAR, LENGTH, GENRE, STUDIONAME, PRODUCERC)
values ('Galaxy Quest', 1999, 104, 'comedy', 'Fox', 12120);
commit;
-- Loading MOVIESTAR...
insert into MOVIESTAR (NAME, ADDRESS, BIRTHDATE)
values ('Carrie Fisher', '123 Maple St., Hollywood', 19990909);
insert into MOVIESTAR (NAME, ADDRESS, BIRTHDATE)
values ('Mark Hamill', '456 Oak Rd.,Brentwood', 19880808);
insert into MOVIESTAR (NAME, ADDRESS, BIRTHDATE)
values ('Harrison Ford', '789 Palm Dr., Beverly Hills', 19970707);
insert into MOVIESTAR (NAME, ADDRESS, BIRTHDATE)
values ('David', '123 Maple St., Hollywood', 19991210);
insert into MOVIESTAR (NAME, ADDRESS, BIRTHDATE)
values ('Misheir', 'USA', 19920606);
insert into MOVIESTAR (NAME, ADDRESS, BIRTHDATE)
values ('Stallone', 'USA', 19480502);
commit;
-- Loading PC...
insert into PC (MODEL, SPEED, RAM, HD, PRICE)
values ('1001', 2.66, 1024, 250, 2114);
insert into PC (MODEL, SPEED, RAM, HD, PRICE)
values ('1002', 2.1, 512, 250, 995);
insert into PC (MODEL, SPEED, RAM, HD, PRICE)
values ('1003', 1.42, 512, 80, 478);
insert into PC (MODEL, SPEED, RAM, HD, PRICE)
values ('1004', 2.8, 1024, 250, 649);
insert into PC (MODEL, SPEED, RAM, HD, PRICE)
values ('1005', 3.2, 512, 250, 630);
insert into PC (MODEL, SPEED, RAM, HD, PRICE)
values ('1006', 3.2, 1024, 320, 1049);
insert into PC (MODEL, SPEED, RAM, HD, PRICE)
values ('1007', 2.2, 1024, 200, 510);
insert into PC (MODEL, SPEED, RAM, HD, PRICE)
values ('1008', 2.2, 2, 250, 770);
insert into PC (MODEL, SPEED, RAM, HD, PRICE)
values ('1009', 2.2, 1024, 250, 650);
insert into PC (MODEL, SPEED, RAM, HD, PRICE)
values ('1010', 2.8, 2048, 300, 770);
insert into PC (MODEL, SPEED, RAM, HD, PRICE)
values ('1011', 1.86, 2048, 160, 959);
insert into PC (MODEL, SPEED, RAM, HD, PRICE)
values ('1012', 2.8, 1024, 160, 649);
insert into PC (MODEL, SPEED, RAM, HD, PRICE)
values ('1013', 3.06, 5124, 80, 529);
commit;
-- Loading PRINTER...
insert into PRINTER (MODEL, COLOR, TYPE, PRICE)
values ('3001', 'true', 'ink-jet', 99);
insert into PRINTER (MODEL, COLOR, TYPE, PRICE)
values ('3002', 'false', 'laser', 239);
insert into PRINTER (MODEL, COLOR, TYPE, PRICE)
values ('3003', 'true', 'laser', 899);
insert into PRINTER (MODEL, COLOR, TYPE, PRICE)
values ('3004', 'true', 'ink-jet', 120);
insert into PRINTER (MODEL, COLOR, TYPE, PRICE)
values ('3005', 'false', 'laser', 120);
insert into PRINTER (MODEL, COLOR, TYPE, PRICE)
values ('3006', 'true', 'ink-jet', 100);
insert into PRINTER (MODEL, COLOR, TYPE, PRICE)
values ('3007', 'true', 'laser', 200);
commit;
-- Loading PRODUCT...
insert into PRODUCT (MAKER, MODEL, TYPE)
values ('A', '1001', 'pc');
insert into PRODUCT (MAKER, MODEL, TYPE)
values ('A', '1002', 'pc');
insert into PRODUCT (MAKER, MODEL, TYPE)
values ('A', '1003', 'pc');
insert into PRODUCT (MAKER, MODEL, TYPE)
values ('A', '2004', 'laptop');
insert into PRODUCT (MAKER, MODEL, TYPE)
values ('A', '2005', 'laptop');
insert into PRODUCT (MAKER, MODEL, TYPE)
values ('A', '2006', 'laptop');
insert into PRODUCT (MAKER, MODEL, TYPE)
values ('B', '1004', 'pc');
insert into PRODUCT (MAKER, MODEL, TYPE)
values ('B', '1005', 'pc');
insert into PRODUCT (MAKER, MODEL, TYPE)
values ('B', '1006', 'pc');
insert into PRODUCT (MAKER, MODEL, TYPE)
values ('B', '2007', 'laptop');
insert into PRODUCT (MAKER, MODEL, TYPE)
values ('C','1007', 'pc');
insert into PRODUCT (MAKER, MODEL, TYPE)
values ('D', '3005', 'printer');
insert into PRODUCT (MAKER, MODEL, TYPE)
values ('D','1008', 'pc');
insert into PRODUCT (MAKER, MODEL, TYPE)
values ('D', '1009', 'pc');
insert into PRODUCT (MAKER, MODEL, TYPE)
values ('D', '1010', 'pc');
insert into PRODUCT (MAKER, MODEL, TYPE)
values ('D', '3004', 'printer');
insert into PRODUCT (MAKER, MODEL, TYPE)
values ('E', '1011', 'pc');
insert into PRODUCT (MAKER, MODEL, TYPE)
values ('E','1012', 'pc');
insert into PRODUCT (MAKER, MODEL, TYPE)
values ('E','1013', 'pc');
insert into PRODUCT (MAKER, MODEL, TYPE)
values ('E', '2001', 'laptop');
insert into PRODUCT (MAKER, MODEL, TYPE)
values ('E', '2002', 'laptop');
insert into PRODUCT (MAKER, MODEL, TYPE)
values ('E', '2003', 'laptop');
insert into PRODUCT (MAKER, MODEL, TYPE)
values ('E', '3001', 'printer');
insert into PRODUCT (MAKER, MODEL, TYPE)
values ('E', '3002', 'printer');
insert into PRODUCT (MAKER, MODEL, TYPE)
values ('E', '3003', 'printer');
insert into PRODUCT (MAKER, MODEL, TYPE)
values ('F', '2008', 'laptop');
insert into PRODUCT (MAKER, MODEL, TYPE)
values ('F', '2009', 'laptop');
insert into PRODUCT (MAKER, MODEL, TYPE)
values ('G', '2010', 'laptop');
insert into PRODUCT (MAKER, MODEL, TYPE)
values ('H', '3006', 'printer');
insert into PRODUCT (MAKER, MODEL, TYPE)
values ('H', '3007', 'printer');
commit;
-- Loading STARSIN...
insert into STARSIN (MOVIETITLE, MOVIEYEAR, STARNAME)
values ('First Blood', 1982, 'Stallone');
insert into STARSIN (MOVIETITLE, MOVIEYEAR, STARNAME)
values ('First Blood', 1985, 'Stallone');
insert into STARSIN (MOVIETITLE, MOVIEYEAR, STARNAME)
values ('First Blood', 1988, 'Stallone');
insert into STARSIN (MOVIETITLE, MOVIEYEAR, STARNAME)
values ('First Blood', 2008, 'Stallone');
insert into STARSIN (MOVIETITLE, MOVIEYEAR, STARNAME)
values ('Galaxy Quest', 1999, 'Harrison Ford');
insert into STARSIN (MOVIETITLE, MOVIEYEAR, STARNAME)
values ('Gone With the Wind', 1939, 'Carrie Fisher');
insert into STARSIN (MOVIETITLE, MOVIEYEAR, STARNAME)
values ('Love Story', 1989, 'Mark Hamill');
insert into STARSIN (MOVIETITLE, MOVIEYEAR, STARNAME)
values ('Pretty Woman', 1999, 'Harrison Ford');
insert into STARSIN (MOVIETITLE, MOVIEYEAR, STARNAME)
values ('Star Wars', 1977, 'David');
insert into STARSIN (MOVIETITLE, MOVIEYEAR, STARNAME)
values ('Wayne"s World', 1992, 'Harrison Ford');
commit;
---Loading STUDIO...
insert into STUDIO (NAME, ADDRESS, PRESC)
values ('Fox', 'Hollywood', null);
insert into STUDIO (NAME, ADDRESS, PRESC)
values ('Paramount', 'Buena Vista', null);
insert into STUDIO (NAME, ADDRESS, PRESC)
values ('Disney', 'Buena Vista', null);
commit;---6.1 Simple Queries in SQL
-- 查询迪斯尼1999年发行的电影的具体信息：
select * from movies
where studioName='Disney' and year=1999; 
---6.1.1 Projection in SQL
-- 查询迪斯尼1999年发行的电影的名字与片长：
select title, length from movies
where studioName='Disney' and year=1999;
-- 查询迪斯尼1999年发行的电影的名字与片长，
-- 名字字段命名为“name”，片长字段命名为“duration”：
select title as name, length as duration from movies
where studioName='Disney' and year=1999;
-- 查询迪斯尼1999年发行的电影的名字与片长，名字字段命名为“name”，
-- 片长字段命名为“lengthInHours”，以小时为单位：
select title as name, length*0.016667 as lengthlnHours from movies
where studioName='Disney' and year=1999;
-- 查询迪斯尼1999年发行的电影的名字与片长，名字字段命名为“name”，
-- 片长字段命名为“length”，以小时为单位，并用名为“InHours”的一列记录电影片长的单位(hrs.)：
select title as name, length*0.016667 as length, 'hrs.' as InHours from movies
where studioName='Disney' and year=1999;
- 查询迪斯尼1999年发行的电影的名字与片长：
- 查询迪斯尼1999年发行的电影的名字与片长，名字字段命名为“name”，片长字段命名为“duration”：
- 查询迪斯尼1999年发行的电影的名字与片长，名字字段命名为“name”，片长字段命名为“lengthInHours”，以小时为单位：
- 查询迪斯尼1999年发行的电影的名字与片长，名字字段命名为“name”，片长字段命名为“length”，以小时为单位，并用名为“InHours”的一列记录电影片长的单位(hrs.)：
---6.1.2 Selection in SQL
-- 查询Fox在1970年之后发行的片长在90分钟以内的电影的名字：
select title from movies
where (year>1970 or length<90) and studioName='Fox'; 
---6.1.4 Pattern Matching in SQL
-- 在电影表中查询以“Star”开头的电影的名字：
select title
from movies
where title like 'Star%'; 
---6.1.5 Dates and Times
-- 在不存在的“dual”表格中查询常量date：
select date'1948-05-14', time '12:08:08.08'
from dual;
select date'1948-05-14', time '12:08:08.08'
from dual;
select date'1948-05-14', timestamp'1948-05-14 12:08:08.08'
from dual; 
由结果可以看到，常量查询的结果与from之后指明的表格无关。
---6.1.6 Null Values and Comparisons Involving NULL
-- 在不存在的“dual”表格中查询1+2、1+null、null+1的结果：
select 1+2,1+null,null+1 from dual;
-- 查询电影表中满足1=1的电影的详细信息：
select * from movies where 1=1;
-- 查询电影表中满足null=null的电影的详细信息：
select * from movies where null=null;
-- 查询电影表中title字段非空的电影的详细信息：
select * from movies where title is not null;- 在不存在的“dual”表格中查询1+2、1+null、null+1的结果：
由结果可以看到，null与任何数的运算结果都为null。
- 查询电影表中满足1=1的电影的详细信息：
由于1=1为永真式，因此这里相当于查询电影表中的所有记录。
- 查询电影表中满足null=null的电影的详细信息：
由于null=null结果为null，对应为false，因此这里查询结果为空。
- 查询电影表中title字段非空的电影的详细信息：
---6.1.7 The Truth-Value UNKNOWN
-- 查询电影表中的全部记录，并使用排他锁：
select * from movies for update;
-- 查询电影表中的片长不超过120分钟或超过120分钟的电影：
select * from movies
where length<=120 or length>120;
-- 查询电影表中title字段为空的电影的详细信息：
select * from movies where title is null;- 查询电影表中的全部记录，并使用排他锁：
for update：当一个事务未完成时，其他事务可以读取，但不能写入或更新。
- 查询电影表中的片长不超过120分钟或超过120分钟的电影：
由结果可以看到，这里相当于查询电影表中的全部记录。
- 查询电影表中title字段为空的电影的详细信息：
由于电影表中不存在title字段为空的记录，因此查询结果为空。
---6.1.8 Ordering the Output
-- 查询电影表中Fox发行的全部电影的详细信息，按片长与片名升序排序：
select * from movies where Studioname = 'Fox' order by length, title;- 查询电影表中Fox发行的全部电影的详细信息，按片长与片名升序排序。
---6.1.9 Exercises for Section 6.1
exercise 6.1.3
---a)find the model number,speed, and hard-disk size for all PC's whose price is under $1000.
答：select model, speed, hd from pc where price<1000;
---b)do the same as (a), but rename the speed column gigahertz and the hd column gigabytes.
答：select model, speed as gigahertz , hd as gigabytes from pc where price<1000;
---c)find the manufactures of printers.
答：select model, maker from product where type='printer';
---d)find the model number,memeory size, and screen size for laptops costing more than $1500.
答：select model, ram, screen from laptop where price>1500;
---e)find all the tuples in the printe relation for color printers.
答：select * from printer where color='true';
---f)find the model number and hard-disk size for those PC's that have a speed of 3.2 and price less than $2000.
答：select model, hd from pc where speed=3.2 and price<2000;
---6.2 Queries Involving More Than One Relation
---6.2.1 Products and Joins in SQL
-- 查询片名为“Star Wars”，
-- movieexec表中cert字段等于movie表中producerc字段的电影在movieexec表中的名字：
select name from movies, movieexec
where title='Star Wars' and producerc=cert; 
---6.2.2 Disambiguating Attributes
-- 查询moviestar表中的住址与movieexec表中的住址相同的记录，
-- 在两个表格中的名字：
select moviestar.name, movieexec.name
from moviestar, movieexec
where moviestar.address = movieexec. address;
---6.2.3 Tuple Variables
-- 查询住址相同的不同电影明星的名字，结果成对出现：
select star1.name, star2.name
from moviestar star1, moviestar star2
where star1.address = star2. address
and star1.name<star2.name;
---6.2.4 Interpreting Multirelation Queries
-- 查询电影明星表中可能出现关联的两人名字，结果成对出现：
select star1.name, star2.name
from moviestar star1, moviestar star2;
...
即笛卡尔积。
6.2.6 Exercises for Section 6.2
exercise 6.2.2
---a)give the manufacturer and speed of laptops with a hard disk of at least thirty gigabytes.
答：select l.speed as speed, p.maker as manufacturer from laptop as l, product as p
where l.model=p.model and l.hd>=30;
---b)find the model number and price of all products of any type made by manufacturer B.
答：select p.model as model, t.price as price from product as p, ((select model, price from printer) union (select model, price from pc) union (select model, price from laptop)) as t
where p.model=t.model and p.maker='B';
---c)find those manufacturers that sell laptops, but not pc's.
答：select distinct maker from product
where type='laptop' and maker not in(select distinct maker from product where type='pc');
---d)find those hard-disk size that occur in two or more pc's.
答：select distinct hd from pc group by hd having count(*)>1;
---e)find those pairs of PC models that have both the same speed and RAM.
答：select * from pc pc1 where exists(select * from pc pc2 where pc1.model != pc2.model and pc1.speed=pc2.speed and pc1.ram=pc2.ram) order by speed, ram;
---f)find those manufacturers of at least two diffrent computers(PC"s or laptops) with speeds of at least 3.0.
答：select p.maker as manufacturer from product as p, ((select model, speed from pc where speed > 3.0) union (select model, speed from laptop where speed > 3.0)) as computer where p.model = computer.model group by manufacturer having count(*) > 1;
