南邮数据库基础教程实验报告

实验报告
〔２０1 5 / ２０1 6 学年第二学期〕
课程名称数据库系统与设计
实验名称数据库系统程序设计与分析
实验时间２０1 6年6月3/17/22/24 日指导单位
指导教师
学生班级学号
学院(系) 电脑学院专业
实验报告
实验报告
insert into PC values(1010,,2048,300,770);
insert into PC values(1011,,2048,160,959);
insert into PC values(1012,,1024,160,649);
insert into PC values(1013,,512,80,529);
insert into Laptop values(2001,,2048,240,,3673);
insert into Laptop values(2002,,1024,80,,949);
insert into Laptop values(2003,,512,60,,549);
insert into Laptop values(2004,,512,60,,1150);
insert into Laptop values(2005,,1024,120,,2500);
insert into Laptop values(2006,,2048,80,,1700);
insert into Laptop values(2007,,1024,120,,1429);
insert into Laptop values(2008,,1024,100,,900);
insert into Laptop values(2009,,512,80,,680);
insert into Laptop values(2010,,2048,160,,2300);
insert into Printer values(3001,'true','ink_jet',99); insert into Printer values(3002,'false','laster',239); insert into Printer values(3003,'true','laster',899); insert into Printer values(3004,'true','ink_jet',120); insert into Printer values(3005,'false','laster',120);
insert into Printer values(3006,'true','ink_jet',100); insert into Printer values(3007,'true','laster',200);
insert into Product values('A',1001,'pc');
insert into Product values('A',1002,'pc');
insert into Product values('A',1003,'pc');
insert into Product values('A',2004,'laptop');
insert into Product values('A',2005,'laptop');
insert into Product values('A',2006,'laptop');
insert into Product values('B',1004,'pc');
insert into Product values('B',1005,'pc');
insert into Product values('B',1006,'pc');
insert into Product values('B',2007,'laptop');
insert into Product values('C',1007,'pc');
insert into Product values('D',1008,'pc');
insert into Product values('D',1009,'pc');
insert into Product values('D',1010,'pc')
insert into Product values('D',3004,'printer');
insert into Product values('D',3005,'printer');
insert into Product values('E',1011,'pc');
insert into Product values('E',1012,'pc');
insert into Product values('E',1013,'pc');
insert into Product values('E',2001,'laptop');
insert into Product values('E',2002,'laptop');
insert into Product values('E',2003,'laptop');
insert into Product values('E',3001,'printer');
insert into Product values('E',3002,'printer');
insert into Product values('E',3003,'printer');
insert into Product values('F',2008,'laptop');
insert into Product values('F',2009,'laptop');
insert into Product values('G',2010,'laptop')
insert into Product values('H',3006,'printer');
insert into Product values('H',3007,'printer');
实验二
A〕
SELECT maker,speed
From Product,Laptop
where Laptop.hd>=30 AND Laptop.model=Product.model
B〕
SELECT Product.model,price
From Product,PC
Where Product.maker='B'AND Product.model=PC.model UNION
SELECT Product.model,price
From Product,Laptop
Where Product.maker='B'AND Product.model=Laptop.model UNION
SELECT Product.model,price
FROM Product,Printer
WHERE Product.maker='B'AND Product.model=Printer.model
C〕
SELECT maker
From Product P
Where P.type='laptop'
EXCEPT
SELECT maker
From Product P
Where P.type='pc'
D〕
SELECT DISTINCT p.hd
FROM PC p,PC q
WHERE q.hd=p.hd AND p.model>q.model
E〕
SELECT p.model AS MODEL1,q.model AS MODEL2
FROM PC p,PC q
WHERE p.speed=q.speed AND p.ram=q.ram AND p.model>q.model
F〕
SELECT p.maker
FROM (
SELECT E.maker,F.model
FROM Product E,PC F
WHERE F.speed> 3.0 AND E.model=F.model
UNION
SELECT G.maker,H.model
FROM Product G,Laptop H
WHERE H.speed> 3.0 AND G.model=H.model
)p
GROUP BY p.maker
HAVING COUNT(p.model)>= 2
实验三
A〕
SELECT maker
FROM Product,(
SELECT model
FROM PC
WHERE PC.speed>
)p
WHERE Product.model=p.model
SELECT maker
FROM Product JOIN PC
ON Product.model=PC.model
WHERE speed>
B〕
SELECT p.price
FROM Printer p
WHERE p.price>=ALL(
SELECT price
FROM Printer
)
SELECT MAX(price)AS price FROM Printer
C〕
SELECT p.model
FROM Laptop p
WHERE p.speed<=(
SELECT MIN(speed)
FROM PC
)
SELECT p.model
FROM Laptop p
WHERE p.speed<=ALL(
SELECT speed
FROM PC
)
D〕
SELECT p.model
FROM (
SELECT model,price
FROM PC
UNION
SELECT model,price
FROM Laptop
UNION
SELECT model,price
FROM Printer
)p
WHERE p.price>=ALL(
SELECT price
FROM PC
UNION
SELECT price
FROM Laptop
UNION
SELECT price
FROM Printer
)
E〕
SELECT maker
FROM Product
WHERE model=(
SELECT model
FROM Printer
WHERE price=(
SELECT MIN(price)AS price
FROM Printer
)
)
SELECT p.maker
FROM Product p,Printer q
WHERE p.model=q.model AND q.price<=ALL(
SELECT price
FROM Printer
)
F〕
SELECT maker
FROM Product p,PC q
WHERE p.model=q.model
AND q.ram<=ALL(
SELECT ram
FROM PC
)
AND q.speed>=ALL(
SELECT speed
FROM PC
)
实验四
A〕
SELECT AVG(speed)AS AVGSPEED
FROM PC
B〕
SELECT AVG(speed)AS AVGSPEED
FROM PC
WHERE price> 1000
C〕
SELECT AVG(price)AS AVGPRICE
FROM PC
WHERE model IN(
SELECT model
FROM Product
WHERE maker='A'
)
D〕
SELECT AVG(p.price)AS AVGPRICE
FROM (
SELECT model,price
FROM PC
UNION
SELECT model,price
FROM Laptop
)p
WHERE p.model IN(
SELECT model
FROM Product
WHERE maker='D'
)
E〕
SELECT speed,AVG(price)AS AVGPRICE
FROM PC
GROUP BY speed
F〕
SELECT maker,AVG(screen)AS AVGSCREEN
FROM Product p JOIN Laptop q ON p.model=q.model GROUP BY maker
G〕
SELECT maker
FROM Product
WHERE type='PC'
GROUP BY maker
HAVING COUNT(model)>= 3
H〕
SELECT maker,MAX(price)AS MAXPRICE
FROM Product p JOIN PC q ON p.model=q.model GROUP BY maker
I〕
SELECT speed,AVG(price)AS AVGPRICE
FROM PC
Where speed>
GROUP BY speed
J〕
SELECT AVG(hd)AS AVGHD
FROM Product p JOIN PC q ON p.model=q.model WHERE maker IN(
SELECT maker
FROM Product
Where type='Printer'
)
实验报告。