SQL Server 实验查询.doc

实验步骤 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; 查询T1老师所授课程的课程号和课程名； select C.CNO 课程号,CN 课程名 from C,TC where C.CNO=TC.CNO AND TNO=T1; 查询年龄大于18岁的男同学的学号、姓名、系别； select SNo 学号,SN 姓名,DEPT 系别 from S where Age18; 查询“李力”老师所授课程的课程号和课程名、课时； select C.CNO 课程号,CN 课程名,CT 课时 from C,TC where C.CNO=TC.CNO AND TNO=(select TNO from T where TN=李力); 查询学号为S1的同学所选修的课程号和课程名和成绩； select SC.CNO 课程号,CN 课程名,Score 成绩 from SC,C where SC.CNO=C.CNO and SNo=S1; 查询“钱尔”同学所选修课程的课程号和课程名和成绩； select SC.CNO 课程号,CN 课程名,Score 成绩 from SC,C,S where SC.CNO=C.CNO and SC.SNo=S.SNo and SN=钱尔; 6、查询至少选修了“刘(李)伟”老师所授全部课程的学生姓名； select distinct SN 姓名 from S,SC SCX where S.SNo=SCX.SNo and not exists (select * from T,TC TCX where TN=李伟 and T.TNO=TCX.TNO and not exists (select * from SC SCZ where SCZ.SNo=SCX.SNo and SCZ.CNO=TCX.CNO )); 查询“李思”同学未选修的课程号和课程名； select C.CNO 课程号,CN 课程名 from C where not exists (select* from SC,S where SC.SNo=S.SNo and SC.CNO=C.CNO and S.SN=李思); 查询全部学生都选修了的课程号和课程名； select C.CNO 课程号,CN 课程名 from C where not exists (select* from S where not exists (select* from SC where S.SNo=SC.SNo and C.CNO=SC.CNO)); 查询选修了课程号为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、查询选修了全部课程的学生的学号和姓名； select SNO 学号,SN 姓名 from S where not exists (select * from C where not exists (select * from SC where S.SNO=SC.SNO and C.CNO=SC.CNO)); 1. 2. 3. 4. 5. 6. 7. 8. 9. 10.