SQL Server 实验查询.doc

3、 实验步骤 select TNO 工号,TN 姓名,SEX 性别,AGE 年龄,PROF 职称,SAL 薪水,COMM 奖金,DEPT 系别 from T;select SNo 学号,SN 姓名,Sex 性别,Age 年龄,DEPT 系别 FROM S;select CNo 课程号,CN 课程名,CT 课时 from C;select TNO 工号,CNO 课程号 FROM TC;select SNo 学号,CNO 课程号,Score 成绩 from SC;1.1、 查询T1老师所授课程的课程号和课程名；select C.CNO 课程号,CN 课程名from C,TCwhere C.CNO=TC.CNO AND TNO=T1;2.2、 查询年龄大于18岁的男同学的学号、姓名、系别；select SNo 学号,SN 姓名,DEPT 系别from Swhere Age18;3.3、 查询“李力”老师所授课程的课程号和课程名、课时；select C.CNO 课程号,CN 课程名,CT 课时from C,TCwhere C.CNO=TC.CNO AND TNO=(select TNO from T where TN=李力);4.4、 查询学号为S1的同学所选修的课程号和课程名和成绩；select SC.CNO 课程号,CN 课程名,Score 成绩from SC,Cwhere SC.CNO=C.CNO and SNo=S1;5.5、 查询“钱尔”同学所选修课程的课程号和课程名和成绩；select SC.CNO 课程号,CN 课程名,Score 成绩from SC,C,Swhere SC.CNO=C.CNO and SC.SNo=S.SNo and SN=钱尔;6、查询至少选修了“刘(李)伟”老师所授全部课程的学生姓名；6.select distinct SN 姓名from S,SC SCXwhere S.SNo=SCX.SNo and not exists (select * from T,TC TCX where TN=李伟 and T.TNO=TCX.TNO and not exists 7.(select * from SC SCZ where SCZ.SNo=SCX.SNo and SCZ.CNO=TCX.CNO );7、 查询“李思”同学未选修的课程号和课程名；select C.CNO 课程号,CN 课程名from Cwhere not exists(select* from SC,S where SC.SNo=S.SNo and SC.CNO=C.CNO and S.SN=李思);8.8、 查询全部学生都选修了的课程号和课程名；select C.CNO 课程号,CN 课程名from Cwhere not exists(select* from S where not exists(select* from SC where S.SNo=SC.SNo and C.CNO=SC.CNO); 9.9、 查询选修了课程号为C1和C2的学生的学号和姓名；select S.SNO,SN from S,SC where S.SNo=SC.SNo and CNO=C1 intersect -交操作select S.SNO,SN from S,SC where S.SNo=SC.SNo and CNO=C2 ;10.10、查询选修了全部课程的学生的学号和姓名；select SNO 学号,SN 姓名from Swhere not exists (select *