(23)实现集合减运算，查询级选修课程database而又没有选修UML的学生的编号。
三、实验结果
(1)
SELECT* FROMCOURSES;
(2)
SELECTsid FROMCHOICES;
(3)
SELECTcid FROMCOURSES WHEREhour<88;
(4)
SELECTsid FROMCHOICES
GROUPBYsidHAVINGSUM(score)>400;
(5)
SELECTCOUNT(cid) FROMCOURSES;
(6)
SELECTcid,COUNT(sid) FROMCHOICES GROUPBYcid;
(7)
SELECTsid FROMCHOICES WHEREscore>60 GROUPBYsid
HAVINGCOUNT(cid)>2;
(8)
SELECTsid,COUNT(cid),AVG(score) FROMCHOICES GROUPBYsid;
(9)
SELECTsid,sname FROMSTUDENTS WHEREsidIN (SELECTsid FROMCHOICES
WHEREcid=(SELECTcid
FROMCOURSES
WHEREcname='Java'));
(10) 等值连接：
SELECTcid,score FROMSTUDENTS,CHOICES
WHERESTUDENTS.sid=CHOICES.sidANDsname='sssht';
谓词IN：
SELECTcid,score FROMCHOICES WHEREsidIN (SELECTsid FROMSTUDENTS
WHEREsname='sssht');
(11)
SELECTcname FROMCOURSES WHEREhour> (SELECThour FROMCOURSES
WHEREcname='C++');
(12)
SELECTsid,sname FROMSTUDENTS WHEREsidIN (SELECTC1.sid
FROMCHOICESC1,CHOICESC2 WHEREC1.score>C2.scoreANDC1.cid=C2.cid
ANDC1.cid=(SELECTcidFROMCOURSESWHEREcname='c++')
ANDC2.sid=(SELECTsidFROMSTUDE
NTSWHEREsname='znkoo'));
(13)
SELECTsname FROMSTUDENTS WHEREgradeIN (SELECTgrade FROMSTUDENTS
WHEREsid='883794999'ORsid='85
5
0955252');
(14)
SELECTsname FROMSTUDENTS WHEREsidNOTIN (SELECTsid FROMCHOICES WHEREcid= (SELECTcid FROMCOURSES
WHEREcname='Java'));
(15)
SELECT* FROMCOURSES
WHEREhour=(SELECTMIN(hour)FROMCOURSES);
(16)
SELECTtid,cid FROMCHOICES WHEREtidIN (SELECTtid FROMTEACHERS WHEREsalary= (SELECTMAX(salary)
FROMTEACHERS));
(17)
SELECTsid FROMCHOICES WHEREscore= (SELECTMAX(score) FROMCHOICES WHEREcid= (SELECTcid FROMCOURSES
WHEREcname='ERP'))AND cid=(SELECTcid FROMCOURSES
WHEREcname='ERP');
(18)
SELECTcname FROMCOURSES WHEREcidNOTIN (SELECTcid
FROMCHOICES);
(19)
SELECTcname FROMCOURSES WHEREcidIN (SELECTcid FROMCHOICES WHEREtidIN (SELECTtid FROMCHOICES WHEREcid= (SELECTcid FROMCOURSES
WHEREcname='UML')));
(20)
SELECTsid FROMCHOICES WHEREcidIN (SELECTcid FROMCHOICES
WHEREtid='200102901')
GROUPBYsid HAVINGCOUNT(*)= (SELECTCOUNT(DISTINCTcid) FROMCHOICES
WHEREtid='200102901');
(21)
SELECTsid FROMCHOICES WHEREcid= (SELECTcid FROMCOURSES
WHEREcname='Database')
UNION SELECTsid FROMCHOICES WHEREcid= (SELECTcid FROMCOURSES
WHEREcname='UML');
(22)
SELECTC1.sid
FROMCHOICESC1,CHOICESC2
WHEREC1.cid=(SELECTcidFROMCOURSESWHEREcname='Database')
6
ANDC2.cid=(SELECTcidFROMCOURSANDC1.sid=C2.sid;
WHEREC1.cid=(SELECTcidFROMCOURSESWHEREcname='Database')
ANDC1.sid=C2.sid
ANDC2.cid<>(SELECTcidFROMCOUR
ESWHEREcname='UML')
(23)
SELECTC1.sid
FROMCHOICESC1,CHOICESC2
SESWHEREcname='UML');
7
实验1.3数据更新
一、实验目的
熟悉数据库的数据更新操作，能够使用SQL语句对数据库进行数据的插入、更新、删除操作 二、实验内容
(1) 向STUDENTS表插入编号是800022222且姓名是WangLan的元组。 (2) 向 TEACHERS表插入元组（200001000，LXL，s4zrck@pew.net，3024） (3) 将 TEACHERS表中编号为200010493的老师工资改为4000。 (4) 将 TEACHERS表中所有工资小于2500的老师工资改为2500。
(5) 将有编号200016731老师讲授的课程全部改成姓名rnupx的老师讲授。 (6) 更新编号800071780的学生年纪为2001。 (7) 删除没有学生选修的课程。 (8) 删除年级高于1998的学生信息。 (9) 删除没有选修课程的学生信息。 (10) 删除成绩不及格的选课记录。 三、实验结果
(1)
INSERT
INTOSTUDENTS(sid,sname)
VALUES ('800022222','WangLan');
SETtid=(SELECTtidFROMTEACHERSWHEREtname='rnupx') WHEREtid='200016721';
(6)
UPDATESTUDENTS SETgrade='2001' WHEREsid='800071780';
(2)
INSERT INTOTEACHERS VALUES
('200001000','LXL','s4zrck@pew.net',3024);
(7)
DELETE FROMCOURSES WHEREcidNOTIN
(SELECTDISTINCTcid FROMCHOICES);
(3)
UPDATETEACHERS SETsalary=4000 WHEREtid='200010493';
(8)
DELETE FROMSTUDENTS WHEREgrade<1998;
(4)
UPDATETEACHERS SETsalary=2500 WHEREsalary<2500;
(9)
DELETE FROMSTUDENTS
8
(5)
UPDATECHOICES
