sql的练习题整理

S(SNO，SNAME，AGE，SEX，DNAME)
C(CNO，CNAME，CEDIT，PRE_CNO，TEACHER)
SC(SNO，CNO，SCORE)

1.查询全体学生的学号与姓名。
SELECT SNO,SNAME
FROM S;

2.查询全体学生的姓名、学号、所在系。
SELECT SNAME,SNO,DNAME
FROM S;

3.查询全体学生的详细记录。
SELECT *
FROM S;

4.查全体学生的姓名及其出生年份。
SELECT SNAME,2021-AGE
FROM S;

5.查询所有年龄在20岁以下的学生姓名及其年龄。
SELECT SNAME,AGE
FROM S
WHERE AGR<20;

6.查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。
SELECT SNAME,DNAME,AGE
FROM S
WHERE AGR>=20 AND AGE<=23;
//WHERE AGR BETWEEN 20 AND 23;

7.查询年龄不在20~23岁之间的学生姓名、系别和年龄。
SELECT SNAME,DNAME,AGE
FROM S
WHERE AGR<20 AND AGE>23;
//WHERE AGR NOT BETWEEN 20 AND 23;

8.查询自动化系、电子系和计 算机系学生的姓名和性别。
SELECT SNAME,SEX
FROM S
WHERE DAME=’自动化’ OR DAME=’电子’ OR DAME=’计算机’;
//WHERE DAME IN(’自动化’ ,’电子’ ,’计算机’);

9.查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。
SELECT SNAME,SEX
FROM S
WHERE DAME NOT ! = ’信息’ AND DAME ! = ’数学’ AND DAME ! = ’计算机科学’;
//WHERE DAME NOT IN (’信息’ ,’数学’ ,’计算机科学’);

10.查询所有姓刘学生的姓名、学号和性别。
SELECT SNAME,SNO,SEX
FROM S
WHERE SNAME LIKE ‘刘%’；

11.查询姓"欧阳"且全名为三个汉字的学生的姓名。
SELECT SNAME
FROM S
WHERE SNAME LIKE ‘欧阳_’；

12.查询名字中第2个字为"阳"字的学生的姓名和学号。
SELECT SNAME,SNO
FROM S
WHERE SNAME LIKE ‘_阳%’；

13.查询所有不姓刘的学生姓名。
SELECT SNAME
FROM S
WHERE SNAME NOT LIKE ‘刘%’；

14.查询DB_Design课程的课程号和学分。
SELECT CNO,CREDIT
FROM C
WHERE CNAME=”DB_Design”
ESCAPE ‘\’;

15.查询以"DB_"开头，且倒数第3个字符为 i的课程的详细情况。
SELECT *
FROM C
WHERE CNAME=’DB_%i_ _’
ESCAPE ‘\’;

16.某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。
SELECT SNO,CNO
FROM SC
WHERE SCORE IS NULL;

17.查所有有成绩的学生学号和课程号
SELECT SNO,CNO
FROM SC
WHERE SCORE IS NOT NULL;

18.查询计算机系年龄在20岁以下的学生姓名。
SELECT SNAME
FROM S
WHERE DNAME=’计算机’ AND AGE<20;

19.查询选修了C3号课程的学生的学号及其成绩，查询结果按分数降序排列。
SELECT SNO,SCORE
FROM SC
WHERE CNO=’C3’
ORDER BY SCORE DESC;

20.查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。
SELECT *
FROM S
ORDER BY DNAME,AGE DESC;

21.查询学生总人数。
SELECT COUNT(*)
FROM S;

22.查询选修了数学课程的学生人数。
SELECT COUNT(*)
FROM SC
WHERE CNO=(SELECT CNO FROM C WHERE CNAME=’数学’)；

23.计算1号课程的学生平均成绩。
SELECT AVG(SCORE)
FROM SC
WHERE CNO=’1’;

24.查询选修1号课程的学生最高分数。
SELECT MAX(SCORE)
FROM SC
WHERE CNO=’1’;

25.求各个课程号及相应的选课人数。
SELECT CNO,COUNT(*)
FROM SC
GROUP BY CNO;

26.查询选修了3门以上课程的学生学号。
SELECT SNO
FROM SC
GROUP BY SNO
HAVING COUNT(*)>=3;

27.查询有3门以上课程是90分以上的学生学号及（90分以上的）课程数。
SELECT SNO,COUNT(CNO)
FROM SC
WHERE SCORE>=90
GROUP BY SNO
HAVING COUNT(CNO)>=3;

28.查询选修‘C2’号课程的所有学生的学号和姓名。
SELECT S.SNO,S.SNAME
FROM S,SC
WHERE S.SNO=SC.SNO AND CNO=’C2’;

29.查询“计算机”系学生的学号、姓名、其选修的课程号和成绩，查询结果学号和课程号升序排列
SELECT S.SNO,S.NAME,SC.CNO,SC.SCORE
FROM S,SC
WHERE S.SNO=SC.SNO AND DNAME=’计算机’;
ORDER BY S.SNO,SC.CNO;

30.查询选修了 “C2” 号课程的学生姓名。
SELECT S.SNAME
FROM S,SC
WHERE S.SNO=SC.SNO AND CNO=’C2’;

31.查询和‘S1’号同学在同一个系的学生学号和姓名。
SELECT SNO,SNAME
FROM S
WHERE DNAME IN
(SELECT DNAME
FROM S
WHERE SNO=’S1’); —嵌套查询

SELECT SNO,SNAME
FROM S AS S1,S AS S2
WHERE S1.DNAME=S2.DNAME AND S2.SNO=’S1’; —连接查询

SELECT SNO,SNAME
FROM S AS S1
WHERE EXISTS
(SELECT *
FROM S AS S2
WHERE S1.DNAME=S2.DNAME AND S2.SNO=’S1’);—EXISTS查询

32.查询“计算机”系的学生选修了哪些课程，要求显示课程号，并按课程号升序排列（要求用嵌套查询做）
SELECT CNO
FROM SC
WHERE SNO IN
(SELECT SNO
FROM S
WHERE DNAME=’计算机’)
ORDER BY CNO;

33.查询选修了‘C2’课程并且成绩高于该门课程平均分的学生学号。
SELECT SNO
FROM SC
WHERE CNO=’C2’ AND SCORE>
(SELECT AVG(SCORE)
FROM SC
WHERE CNO=’C2’);

34.查询其他系中比“计算机”系某一个学生年龄小的学生姓名和年龄（本题可以用<ANY或<MAX做）
SELECT SNAME,AGE
FROM S
WHERE DNAME != ’计算机’ AND AGE<ANY
(SELECT AGE
FROM S
WHERE DNAME=’计算机’);

35.查询所有选修了C1号课程的学生姓名。
SELECT S.SNAME
FROM S,SC
WHERE S.NO=SC.NO AND SC.CNO=’C1’; —连接查询

SELECT SNAME
FROM S
WHERE EXISTS
(SELECT *
FROM SC
WHERE S.SNO=SC.SNO AND CNO=’C1’);—EXISTS查询

36.查询没有选修C1号课程的学生姓名。
SELECT SNAME
FROM S
WHERE NOT EXISTS
(SELECT *
FROM SC
WHERE S.SNO=SC.SNO AND CNO=’C1’);—EXISTS查询

37.查询选修了全部课程的学生姓名。( 也就是说不存在一门课程这个学生没有选修)
SELECT SNAME
FROM S
WHERE NOT EXISTS
(SELECT *
FROM C
WHERE NOT EXISTS
(SELECT*
FROM SC
WHERE S.SNO=SC.SNO AND C.CNO=SC.CNO));

38.查询至少选修了学生“S3”号学生所选修的全部课程的学生学号。（也就是不存在一个课程同学S3选了而同学X没选）
SELECT DISTINCT SNO
FROM SC AS SC1
WHERE NOT EXISTS
(SELECT *
FROM SC AS SC2
WHERE SC2.SNO=’S3’ AND NOT EXISTS
(SELECT *
FROM SC AS SC3
WHERE SC3.CNO=SC2.CNO AND SC3.SNO=SC1.SNO AND SC3.SNO!=‘S3’));

39.将一个新学生记录插入到S表中， 学号：“S20”；姓名：“陈冬”；性别：“男”；所在系：“信息系”；年龄：18岁。
INSERT INTO S(SNO,SNAME,SEX,AGE,DNAME) VALUES(‘S20’,’陈冬’,‘男’,18,’信息系’)；

40.插入一条选课记录( ‘S20’，‘C1 ')。
INSERT INTO SC(SNO,CNO) VALUES(‘S20’,’C1’);

41.对每一个系，求学生的平均年龄，并把结果存入数据库中的新表dept_age(DNAME,AVG_AGE)
INSERT INTO dept_age(DNAME,AVG_AGE)
SELECT DNAME,AVG(AGE)
FROM S
GROUP BY DNAME;

42.从学生表S中删除学号为‘S9’的学生记录。
DELETE
FROM S
WHERE SNO=’S9’;

43.删除2号课程的所有选课记录。
DELETE
FROM SC
WHERE CNO=’2’;

44.删除所有的学生选课记录。
DELETE
FROM SC;

45.删除“计算机”系所有学生的选课记录。
DELETE
FROM SC
WHERE SNO IN
(SELECT SNO
FROM S
WHERE DNAME=‘计算机’);

46.将学生“S1”的年龄改为22岁。
UPDATE S
SET AGE=’22’
WHERE SNO=’S1’;

47.将所有学生的年龄增加1岁。
UPDATE S
SET AGE=AGE+1;

48.将信息系所有学生的年龄增加1岁。
UPDATE S
SET AGE=AGE+1
WHERE DNAME=’信息’;

49.将“计算机”系全体学生的成绩置空。
UPDATE SC
SET SCORE=NULL
WHERE SNO IN
（SELECT SNO
FROM S
WHERE DNAME=‘计算机’);

50.建立一个反映“计算机”系学生学号、姓名和年龄的视图V1。
CREATE VIEW V1 (SNO,SNAME,AGE)
AS SELECT *
FROM S
WHERE DNAME=’计算机’;

51.建立“计算机”系选修了“C1”号课程的学生学号、姓名和成绩视图V2。
CREATE VIEW V2(S.SNO,S.SNAME,SC.SCORE)
AS SELECT S.SNO,S.SNAME,SC.SCORE
FROM S,SC
WHERE S.SNO=SC.SNO AND S.DNAME=’计算机’ AND SC.CNO=’C1’;

52.将学生的学号及其平均成绩定义为一个视图V3。
CREATE VIEW V3(SNO,GAVG)
AS SELECT SNO,AVG(SCORE)
FROM SC
GROUP BY SNO;

53.在“计算机”系的学生视图V1中查找年龄小于20岁的学生。
SELECT *
FROM V1
WHERE AGE<20;

54.在v3视图中查询平均成绩在90分以上的学生学号和平均成绩
SELECT *
FROM V3
WHERE GAVG>=90;

55.将计算机系学生视图V1中学号S1 的学生姓名改为“刘辰”。
UPDATE V1
SET SNAME=’刘辰’
WHERE SNO=’S1’;