《数据库原理》实验报告DB2——数据库查询_db2实验报告

一. 实验内容、步骤以及结果

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

SPJ_MING:

建表s：
CREATE TABLE s (
	Sno CHAR (10),
  	Sname CHAR (20),
sta_tus INT ,	
   	City CHAR (10),
  	PRIMARY KEY (Sno )
);
插入数据：
INSERT INTO s Values('S1','精益',20,'天津');
INSERT INTO s Values('S2','盛锡',10,'北京');
INSERT INTO s Values('S3','东方红',30,'北京');
INSERT INTO s Values('S4','丰泰盛',20,'天津');
INSERT INTO s Values('S5','为民',30,'上海');
建表p：
CREATE TABLE p (
	Pno CHAR (10),
   	Pname CHAR (20),
  	Color CHAR (10), 
	Weight INT ,
  	PRIMARY KEY (Pno )
);
插入数据：
INSERT INTO p Values('P1','螺母','红',12);
INSERT INTO p Values('P2','螺栓','绿',17);
INSERT INTO p Values('P3','螺丝刀','蓝',14);
INSERT INTO p Values('P4','螺丝刀','红',14);
INSERT INTO p Values('P5','凸轮','蓝',40);
INSERT INTO p Values('P6','齿轮','红',30);
建表J：
CREATE TABLE J (
	Jno CHAR (10),
   	Jname CHAR (20),
  	City CHAR (10), 
  	PRIMARY KEY (Jno )
);
插入数据：
INSERT INTO J Values('J1','三建','北京');
INSERT INTO J Values('J2','一汽','长春');
INSERT INTO J Values('J3','弹簧厂','天津');
INSERT INTO J Values('J4','造船厂','天津');
INSERT INTO J Values('J5','机车厂','西安');
INSERT INTO J Values('J6','无线电厂','常州');
INSERT INTO J Values('J7','半导体厂','南京'); 
建表spj：
CREATE TABLE spj (
	Sno CHAR (10),
  	Pno CHAR (10),
  	Jno CHAR (10),
   	QTY INT ,
  	FOREIGN KEY (Sno) REFERENCES s(Sno),
    FOREIGN KEY (Pno) REFERENCES p(Pno),
  	FOREIGN KEY (Jno) REFERENCES j(Jno)
);
插入数据：
INSERT INTO spj Values
('S1','P1','J1',200),
('S1','P1','J3',100),
('S1','P1','J4',700),
('S1','P2','J2',100),
('S2','P3','J1',400),
('S2','P3','J2',200),
('S2','P3','J4',500), 
('S2','P3','J5',400),
('S2','P5','J1',400),
('S2','P5','J2',100),
('S3','P3','J1',200),
('S3','P3','J1',200),
('S4','P5','J1',200),
('S4','P6','J3',100),
('S4','P6','J4',300),
('S5','P2','J4',100),
('S5','P3','J1',200),
('S5','P6','J2',200),
('S5','P6','J4',500);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75

Student:

建表s：
CREATE TABLE s (
	Sno INT ,
  	Sname CHAR (20),
	Sgender CHAR (5) ,	
   	Birth CHAR (20),
    Sdept CHAR (5),
  	PRIMARY KEY (Sno )
);
插入数据：
INSERT INTO s Values(2001,'李勇','男','2000/01/01','MA');
INSERT INTO s Values(2002,'刘晨','女','2001/02/01','IS');
INSERT INTO s Values(2003,'王敏','女','1999/10/01','CS');
INSERT INTO s Values(2004,'张立','男','2001/06/01','IS');
建表c：
CREATE TABLE c (
	Cno INT ,
  	Cname CHAR (20),
	Cpno INT,
	Credit INT,
  	PRIMARY KEY (Cno )
);
插入数据：
INSERT INTO c Values
(1,'数据库',2,3),
(2,'高等数学',NULL,5),
(3,'信息系统',1,2),
(4,'操作系统',5,3),
(5,'数据结构',6,3),
(6,'C语言',NULL,2);
建表sc：
CREATE TABLE sc (
	Sno INT ,
  	Cno INT ,
  	Grade INT,
    FOREIGN KEY (Sno) REFERENCES s(Sno),
    FOREIGN KEY (Cno) REFERENCES c(Cno)
);
插入数据：
INSERT INTO sc Values
(2001,1,92),
(2001,2,85),
(2001,3,90),
(2002,2,78),
(2002,3,84),
(2003,6,91);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

2． 在图形用户界面中对表中的数据进行更新（5分）。

(1) 修改S表的任意一条数据。

update s set city = '西安'	where sno = s1
1

(2) 删除S表的任意一条数据。

delete from s where sno = s1
1

3． 针对SPJ_MNG数据库，用SQL语句完成下面的数据更新（10分）。

(1) 把全部红色零件的颜色改为蓝色。

UPDATE p set Color = '蓝'
WHERE Color = '红';
1
2

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

UPDATE spj set Sno = 'S3'
WHERE Sno = 'S5' AND Jno = 'J4' AND Pno = 'P6';
1
2

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

delete from spj where sno = 's2';
delete from s where sno = 's2';
1
2

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

INSERT INTO spj Values(S2,J6,P4,200);
1

4． 针对SPJ_MNG数据库，用SQL语句完成下面的数据查询（50分）。

(1) 找出所有供应商的姓名和所在城市。

select sname,city from s  
1

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

select sno from spj
where pno = 'P1' and jno = 'J1'
1
2

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

select distinct jno from spj
where sno = 'S1' or sno = 'S2'
order by jno  
1
2
3

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

select distinct sno from spj,p
where spj.pno = p.pno and jno = 'J1' and color = '红'
1
2

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

select sno,count( pno) from spj
group by sno
1
2

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

select s.sno,sname,count(distinct pno) from s,spj
where s.sno = spj.sno
group by s.sno  
1
2
3

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

select jname from j
where jno in
	(
		select distinct jno from spj
		where sno in
			(
				select sno from s 
				where city = '上海'
			)
		order by jno
	)
1
2
3
4
5
6
7
8
9
10
11

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

select distinct jno from 
	(
		select spj.sno,spj.pno,spj.jno,qty,city,color
		from s,p,spj
		where spj.sno = s.sno and spj.pno = p.pno
	) as a
where (a.city = '天津' and color != '红') or (a.city != '天津')
order by jno
1
2
3
4
5
6
7
8

(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

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

select b.jno from
	(
		select jno,avg(qty) from spj
		where pno = 'p1'
		group by jno
	) as b(jno,qqq)
where b.qqq >
	(
		select max(a.qq) from 
			(
				select sum(qty) from spj
				where jno = 'j1'
				group by pno
			) as a(qq)
	)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

5． 针对Student数据库用SQL语句完成下面的数据查询（10分）。

(1) 查询每个学生已经获得的学分的总分（成绩及格表示获得该门课的学分），并按照所获学分由高到低的顺序输出学号，姓名，所获学分。

select s.sno,s.sname.sum(credit)
from c,sc,s
where grade >= 60 and s.sno = sc.sno and sc.cno = c.cno
group by 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.cno = c.cno and b.sno = a.sno
	)
)
group by a.sno
having max(a.grade) >= 90
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

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

(1) select sno,sname from s

where sno in
(
	select distinct sno from sc
	where cno in
	(
	select cno from c
	where cname = '数据库'
	)
)
1
2
3
4
5
6
7
8
9

(2) select s.sno,sname from s,c,sc

where cname = '数据库' and s.sno = sc.sno and sc.cno = c.cno
1

(3) select sno,sname from s

where sno in
(
	select sc.sno from sc,c
	where sc.cno = c.cno and cname = '数据库'
)
1
2
3
4
5

其中嵌套次数越多查询效率越高，因为连接两个表后再查询，相较于在表中直接查询效率更低。

二. 实验中出现的问题以及解决方案(对于未解决问题请将问题列出来)