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

实验名称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 https://www.360docs.net/doc/bf13015550.html,o=https://www.360docs.net/doc/bf13015550.html,o

(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 SPJ.pno=P.pno and not ci 天津' and not color='红'

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表

P表

S表

SPJ表

四、思考题