《数据库原理及应用》实验报告

《数据库原理及应用》实验报告

实验名称SQL

多表链接、嵌套查询

专业班级：信息与计算科学 姓名：姬马宁 实 验 日 期 ： 2017 年10月27 日 一、实验目的

1. 掌握SQL多表链接 2. 掌握SQL嵌套查询 二、实验内容 1. 例题3.35-3.50

(1) select * from course where cname like 'DB\\_%i__'ESCAPE'\\'

(2) select sno,cno from SC where grade is not NULL

学号： (3) select sname from student where sdept='CS' and sage<20

(4) select sno,grade from SC where cno='3'order by grade desc

(5) select SUM(ccreate) from SC,course where sno='201215121' and sc.cno=course.cno

(6) SELECT cno,COUNT(sno) FROM SC GROUP BY cno

(7) SELECT sno FROM SC GROUP BY sno HAVING COUNT(*)>2

2． 第二章课后习题第6题

①select distinct sno from SPJ where jno='j1'

②select sno from SPJ where pno='p1' and jno='j1'

③select distinct sno from SPJ,P where SPJ.pno=P.pno and color='红'and jno='j1'

④

select distinct jno from SPJ,S,P where SPJ.sno=S.sno and 天津' and not color='红'

SPJ.pno=P.pno and not ci

3. 第三章课后习题 (1)SELECT sname,city FROM s

(2)SELECT pname,color,weight FROM p

(3)SELECT jno FROM spj WHERE sno='S1'

(4)SELECT pname,COUNT(pname) FROM p,spj WHERE jno='J2' AND spj.pno=p.pno GROUP BY pname

(5)SELECT p.pno FROM s,p,spj WHERE city='上海' AND s.sno=spj.sno AND spj.pno=p.pno

(6)SELECT DISTINCT j.jno FROM s,j,spj WHERE s.city='上海' AND s.sno=spj.sno AND spj.jno =j.jno

(7)SELECT jno FROM spj WHERE jno NOT IN(SELECT jno FROM spj,s WHERE s.sno=spj.sno AND s.city天津')

(8)UPDATE p SET color='蓝' WHERE color='红'

(9)UPDATE spj SET sno='S3' WHERE sno='S5' AND jno='J4' AND pno='P6'

(10)DELETE FROM spj WHERE sno='S2'

(11)INSERT INTO spj (sno,pno,jno,qty) VALUES ('S2','J6','P4',200)

三、实验数据 SPJ数据库

J表

jno j1 j2 j3 j4 j5 j6 j7 jname 三建 一汽 弹簧厂 造船厂 机车厂 无线电厂 半导体厂 city 北京 长春 天津 天津 唐山 常州 兰州 P表

pno p1 p2 p3 p4 p5 p6 pname 螺母 螺栓 螺丝刀 螺丝刀 凸轮 齿轮 color 红 绿 蓝 红 蓝 红 weight 12 17 14 14 40 30 S表

sno s1 s2 s3 s4 s5 sname JY SX DFH FTS WM status city 20 天津 10 北京 30 北京 20 天津 30 上海 SPJ表

sno s1 s1 s1 s1 s2 s2 s2 pno p1 p1 p1 p2 p3 p3 p3 jno j1 j3 j4 j2 j1 j2 j4 qty 200 100 700 100 400 200 500 s2 s2 s3 s3 s4 s4 s4 s5 s5 s5 s5 p3 p5 p1 p3 p5 p6 p6 p2 p3 p6 p6 j5 j2 j1 j1 j1 j3 j4 j4 j1 j2 j4 400 100 200 200 100 300 200 100 200 200 500

四、思考题