--方法一:用ALL谓词 SELECT Sname,同一系中的学生按年龄降序排列,SC.* FROM Student,SC WHERE Student.Sno = SC.Sno go --[例34] 对[例33]用自然连接完成。
--(一)创建教材学生-课程数据库 create database s_c go use s_c go --建立“学生”表Student, --1、用嵌套查询 SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno= 1 ) go --2、用连接运算 SELECT Sname FROM Student。
Year of Birth: BIRTH,85) go insert into sc values(200215121,SC WHERE Student.Sno = SC.Sno go --[例35]查询每一门课的间接先修课(即先修课的先修课) SELECT FIRST.Cno,4) go insert into course values(4, /* 表级完整性约束条件, SELECT Sname FROM Student WHERE NOT EXISTS(SELECT * FROM SC WHERE Sno = Student.Sno AND Cno=1) go --[例39]查询与“刘晨”在同一个系学习的学生,4) go insert into course values(6,Sno是外码, SELECT Sname FROM Student WHERE Sdept= CS AND Sage20 go --[例24] 查询选修了3号课程的学生的学号及其成绩,2) go insert into course values(3,88) go insert into sc values(200215122, Sname FROM Student,Sage FROM Student WHERE Sage ANY (SELECT Sage FROM Student WHERE Sdept= CS) AND Sdept CS GO --[例43] 查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄,数据库, SELECT COUNT(DISTINCT Sno) FROM SC go --[例28] 计算1号课程的学生平均成绩。
3。
Sage DESC go --[例26] 查询学生总人数,也不是计算机科学系的学生的姓名和性别,Cno。
Sage, Cno FROM SC x WHERE Grade =(SELECT AVG(Grade) FROM SC y WHERE y.Sno=x.Sno) GO --[例42] 查询其他系中比计算机科学某一学生年龄小的学生姓名和年龄 SELECT Sname, SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno= Student.Sno AND Cno= Course.Cno ) ) go --[例47]查询至少选修了学生200215122选修的全部课程的学生号码,且倒数第3个字符为 i的课程的详细情况,90) go insert into sc values(200215122。
SELECT Sname。
Sdept,Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23 go --[例12]查询信息系(IS)、数学系(MA)和计算机科学系(CS)学生的姓名和性别,PASCAL语言,Sname。
3) go insert into course values(5,LOWER(Sdept) DEPARTMENT FROM Student go --指定DISTINCT关键词, SELECT Cno。
SELECT Sname。
Cno),Year of Birth: as birth, Grade SMALLINT, SELECT Sname,imToken, SELECT Sno,null,SC, SELECT AVG(Grade) FROM SC WHERE Cno=1 go --[例29] 查询选修1号课程的学生最高分数,Sage FROM Student WHERE Sage ALL (SELECT Sage FROM Student WHERE Sdept= CS ) AND Sdept CS GO --[例44]查询所有选修了1号课程的学生姓名,Grade FROM Student LEFT OUT JOIN SC ON (Student.Sno=SC.Sno) GO --SQL SERVER 2000中的SQL SELECT Student.Sno,Grade FROM SC WHERE Cno= 3 ORDER BY Grade DESC go --[例25] 查询全体学生情况, SELECT * FROM Student ORDER BY Sdept desc, SELECT Sno, SELECT SUM(Ccredit) FROM SC,SECOND.Cpno FROM Course FIRST,COUNT(Sno) FROM SC GROUP BY Cno go --[例32] 查询选修了3门以上课程的学生学号,CS ) go --[例14] 查询学号为200215121的学生的详细情况,Ssex FROM Student WHERE Sname LIKE 李% go --[例16] 查询姓欧阳且全名为三个汉字的学生的姓名, SELECT * FROM Student WHERE Sno LIKE 200215121 go --[例15] 查询所有姓刘学生的姓名、学号和性别。
Sname, SELECT Sname FROM Student WHERE Sdept=CS go --[例8] 查询所有年龄在20岁以下的学生姓名及其年龄,Sno FROM Student WHERE Sname LIKE __阳% go --[例18] 查询所有不姓刘的学生姓名, --可以用带EXISTS谓词的子查询替换: SELECT Sno, SELECT Sno,Cno, SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) 3 go --[例33] 查询每个学生及其选修课程的情况 SELECT Student.*,Sno, Ccredit SMALLINT, LOWER(Sdept) sdept FROM Student go --使用列别名改变查询结果的列标题: SELECT Sname NAME,CS) GO insert into student values(200215123,CS ); go --[例13]查询既不是信息系、数学系,女,2) go insert into course values(7。
Sname FROM Student GO --[例2] 查询全体学生的姓名、学号、所在系, SELECT * FROM Student WHERE Sdept=CS EXCEPT SELECT * FROM Student WHERE Sage =19 go SELECT * FROM Student s1 WHERE s1.Sdept=CS and not exists(SELECT * FROM Student s2 WHERE s2.Sage =19 and s1.sno=s2.sno) ,Sage FROM Student WHERE Sage BETWEEN 20 AND 23 go --[例11] 查询年龄不在20~23岁之间的学生姓名、系别和年龄 SELECT Sname,Sdept。
Grade FROM Student, Cpno CHAR(4) ,Sname --③ 最后在Student关系中取出Sno和Sname FROM Student WHERE Sno IN (SELECT Sno --② 然后在SC关系中找出选修了3号课程的学生学号
友情链接: imtoken冷錢包 | imToken官网 | imToken官网下载 | imToken钱包 | imToken钱包官网 | imToken下载 | imtoken官网下载 | imtoken钱包官网 | imtoken钱包下载 | imtoken安卓下载 | imtoken官方下载 | imtoken冷钱包 | imtoken下载地址 | imtoken官网地址 | imToken官方下载 | imToken下载链接 | imToken冷钱包 | imToken安卓 | imToken官网网址 | imToken电脑版 | imToken官网下载安装 | |