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

实验名称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'