数据库原理实验二——基本表的数据操作_用嵌套查询这样的工程:供给该工程的零

实验目的

1. 熟练掌握图形用户界面中关于基本表的各种数据操作。
2. 熟练掌握对基本表进行数据插入、修改和删除的SQL语句。
3. 熟练掌握数据查询的SQL语句。
4. 掌握SQL语句查询性能分析的基本知识。
5. 了解TPC-H基准数据库。

实验内容

1． 针对SPJ_MNG数据库和Student数据库，用SQL语句插入实验一中所列的示例数据。（5分）
对于Student数据库的S表，插入数据的代码为：

INSERT INTO `student`.`s` (`SNO`, `SNAME`, `SGENDER`, `SBIRTH`, `SDEPT`) VALUES ('2001', '李勇', '男', '2000/01/01', 'MA');
INSERT INTO `student`.`s` (`SNO`, `SNAME`, `SGENDER`, `SBIRTH`, `SDEPT`) VALUES ('2002', '刘晨', '女', '2001/01/01', 'IS');
INSERT INTO `student`.`s` (`SNO`, `SNAME`, `SGENDER`, `SBIRTH`, `SDEPT`) VALUES ('2003', '王敏', '女', '1999/10/01', 'CS');
INSERT INTO `student`.`s` (`SNO`, `SNAME`, `SGENDER`, `SBIRTH`, `SDEPT`) VALUES ('2004', '张立', '男', '2001/06/01', 'IS');
1
2
3
4

其余几个表的插入类似。
2． 针对Student数据库，在图形用户界面中对表中的数据进行更新。（5分）
(1) 修改S表的任意一条数据。
直接点击要修改的数据位置，修改后点击Apply。
(2) 删除S表的任意一条数据。

点击如图所示符号删除数据。

3． 针对SPJ_MNG数据库，用SQL语句完成下面的数据更新。（10分）
(1) 把全部红色零件的颜色改为蓝色。

UPDATE p   
SET COLOR = '蓝'   
WHERE COLOR = '红';  
1
2
3

(2) 由S5供给J4的零件P6改为由S3供应，请做必要的修改。

UPDATE spj
SET SNO = 'S3'
WHERE SNO = 'S5' AND PNO = 'P6' AND JNO = 'J4';
1
2
3

(3) 从供应商表中删除S2的记录，并从供应情况表中删除相应记录。

DELETE 
FROM s
WHERE SNO = 'S2';

DELETE   
FROM spj  
WHERE SNO = 'S2';  

1
2
3
4
5
6
7
8

(4) 请将(S2,J6,P4,200)插入供应情况表SPJ。

INSERT
INTO spj
VALUE(20,'S2','P4','J6',200)
1
2
3

4． 针对SPJ_MNG数据库，用SQL语句完成下面的数据查询。（50分）
(1) 找出所有供应商的姓名和所在城市。

SELECT SNAME,CITY
FROM s
1
2

(2) 求供应工程J1零件P1的供应商号码。

SELECT DISTINCT SNO
FROM spj
WHERE PNO = 'P1' AND JNO = 'J1'; 
1
2
3

(3) 找出使用供应商S1或者S2所供应零件的工程号码。

SELECT DISTINCT JNO
FROM spj
WHERE SNO = 'S1' OR SNO = 'S2'
1
2
3

(4) 求供应工程J1零件为红色的供应商号码。

SELECT SNO
FROM p,spj
WHERE spj.JNO = 'J1' AND p.COLOR = '红' AND spj.PNO = p.PNO;
1
2
3

(5) 查询每个供应商号码以及其供应零件的总个数。

SELECT SNO,COUNT(PNO)
FROM spj
GROUP BY SNO
1
2
3

(6) 求每个供应商号码，供应商名以及所供应零件的种类数量。

SELECT spj.SNO,s.SNAME,COUNT(DISTINCT PNO)
FROM spj,s
WHERE spj.SNO = s.SNO
GROUP BY SNO;
1
2
3
4

(7) 找出使用上海供应商的零件的工程名称。
连接查询

SELECT JNAME
FROM j,spj,s
WHERE spj.SNO = s.SNO AND spj.JNO = j.JNO AND s.CITY = '上海';
1
2
3

嵌套查询

SELECT JNAME
FROM j
WHERE JNO in
	(
		SELECT JNO
        FROM spj
        WHERE SNO in
			(
				SELECT SNO
                FROM S
                WHERE CITY = '上海'
			)
	);
1
2
3
4
5
6
7
8
9
10
11
12
13

(8) 求没有使用天津供应商生产的红色零件的工程号码。

SELECT DISTINCT spj.JNO
FROM p,spj,j,s
WHERE s.SNO = spj.SNO AND  p.PNO = spj.PNO AND j.JNO = spj.JNO AND !(s.CITY = '天津' AND p.COLOR = '红')
ORDER BY JNO;
1
2
3
4

(9) 求至少使用了供应商S1所供应的全部零件的工程号。

SELECT JNO
FROM spj
WHERE PNO in
	(
		SELECT DISTINCT PNO 
        FROM spj
        WHERE SNO = 'S1'
    )
GROUP BY JNO
HAVING COUNT(DISTINCT PNO) = (SELECT COUNT(DISTINCT PNO) FROM spj WHERE SNO = 'S1');  
1
2
3
4
5
6
7
8
9
10

(10) 查询这样的工程：供给该工程的零件P1的平均供应量大于供给工程J1的任何一种零件的最大供应量。

SELECT JNO
FROM SPJ
WHERE PNO = 'P1'
GROUP BY JNO
HAVING AVG(QTY) > (SELECT MAX(QTY) FROM spj WHERE JNO = 'J1');
1
2
3
4
5

5． 针对Student数据库用SQL语句完成下面的数据查询。（10分）
(1) 查询每个学生已经获得的学分的总分（成绩及格表示获得该门课的学分），并按照所获学分由高到低的顺序输出学号，姓名，所获学分。

SELECT s.SNO,s.SNAME,SUM(CREDIT)
FROM s,c,sc
WHERE s.SNO = sc.SNO AND c.CNO = sc.CNO AND GRADE >= 60
GROUP BY s.SNO 
ORDER BY sum(credit) DESC;
1
2
3
4
5

(2) 查询这样的学生姓名：该学生选修了全部课程并且其中一门课在90分以上。

SELECT SNAME
FROM S,SC A
WHERE NOT EXISTS
(
	SELECT * 
	FROM C
    WHERE NOT EXISTS
    (
		SELECT *
        FROM SC B
        WHERE B.SNO = S.SNO AND B.CNO = C.CNO
	)
)
GROUP BY S.SNO
1
2
3
4
5
6
7
8
9
10
11
12
13
14

6． 针对Student数据库用至少三种不同的SQL语句进行查询：查询选修了课程名为“数据库”的学生学号和姓名，然后自己设计实验，用数据比较分析三种查询的效率，并分析原因。（20分）
方法一：

SELECT S.SNO,S.SNAME
FROM S,SC,C
WHERE S.SNO = SC.SNO AND SC.CNO = C.CNO AND C.CNAME = '数据库'
1
2
3

方法二：

SELECT SNO,SNAME
FROM S
WHERE SNO IN
(
	SELECT SNO 
    FROM SC
    WHERE CNO IN
    (
		SELECT CNO
        FROM C
        WHERE CNAME = '数据库'
    )
);
1
2
3
4
5
6
7
8
9
10
11
12
13

方法三：

SELECT SNO,SNAME
FROM S
WHERE SNO IN
(
	SELECT SNO 
    FROM SC,C
    WHERE SC.CNO = C.CNO AND C.CNAME = '数据库'
);
1
2
3
4
5
6
7
8