1.SQL查询：45道经典SQL题

CREATE TABLE STUDENT
(SNO VARCHAR(3) NOT NULL, 
SNAME VARCHAR(4) NOT NULL,
SSEX VARCHAR(2) NOT NULL, 
SBIRTHDAY DATE,
SCLASS VARCHAR(5));
 
CREATE TABLE TEACHER 
(TNO VARCHAR(3) NOT NULL, 
TNAME VARCHAR(4) NOT NULL, TSEX VARCHAR(2) NOT NULL, 
TBIRTHDAY DATETIME NOT NULL, PROF VARCHAR(6), 
DEPART VARCHAR(10) NOT NULL);
 
CREATE TABLE COURSE
(CNO VARCHAR(5) NOT NULL, 
CNAME VARCHAR(10) NOT NULL, 
TNO VARCHAR(10) NOT NULL);
 
CREATE TABLE SCORE 
(SNO VARCHAR(3) NOT NULL, 
CNO VARCHAR(5) NOT NULL, 
DEGREE NUMERIC(10, 0) NOT NULL); 
 
 
 
ALTER TABLE student
ADD CONSTRAINT primary key (sno);
ALTER TABLE course
ADD constraint primary key (cno);
ALTER TABLE score
ADD constraint primary key (sno, cno);
ALTER TABLE teacher
ADD constraint primary key (tno);
ALTER TABLE course
ADD constraint foreign key (tno) references teacher (tno);
ALTER TABLE score
ADD constraint foreign key (cno) references course (cno);
 
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,SCLASS) VALUES (108 ,'曾华' 
,'男' ,'1977-09-01',95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,SCLASS) VALUES (105 ,'匡明' 
,'男' ,'1975-10-02',95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,SCLASS) VALUES (107 ,'王丽' 
,'女' ,'1976-01-23',95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,SCLASS) VALUES (101 ,'李军' 
,'男' ,'1976-02-20',95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,SCLASS) VALUES (109 ,'王芳' 
,'女' ,'1975-02-10',95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,SCLASS) VALUES (103 ,'陆君' 
,'男' ,'1974-06-03',95031);
 
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) 
VALUES (804,'李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) 
VALUES (856,'张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (825,'王萍','女','1972-05-05','助教','计算机系');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) 
VALUES (831,'刘冰','女','1977-08-14','助教','电子工程系');
 
INSERT INTO COURSE(CNO,CNAME,TNO) VALUES ('3-105','计算机导论',825);
INSERT INTO COURSE(CNO,CNAME,TNO) VALUES ('3-245','操作系统',804);
INSERT INTO COURSE(CNO,CNAME,TNO) VALUES ('6-166','数据电路',856);
INSERT INTO COURSE(CNO,CNAME,TNO) VALUES ('9-888','高等数学',831);
 
INSERT INTO SCORE(SNO,CNO,DEGREE) VALUES (103,'3-245',86);
INSERT INTO SCORE(SNO,CNO,DEGREE) VALUES (105,'3-245',75);
INSERT INTO SCORE(SNO,CNO,DEGREE) VALUES (109,'3-245',68);
INSERT INTO SCORE(SNO,CNO,DEGREE) VALUES (103,'3-105',92);
INSERT INTO SCORE(SNO,CNO,DEGREE) VALUES (105,'3-105',88);
INSERT INTO SCORE(SNO,CNO,DEGREE) VALUES (109,'3-105',76);
INSERT INTO SCORE(SNO,CNO,DEGREE) VALUES (101,'3-105',64);
INSERT INTO SCORE(SNO,CNO,DEGREE) VALUES (107,'3-105',91);
INSERT INTO SCORE(SNO,CNO,DEGREE) VALUES (108,'3-105',78);
INSERT INTO SCORE(SNO,CNO,DEGREE) VALUES (101,'6-166',85);
INSERT INTO SCORE(SNO,CNO,DEGREE) VALUES (107,'6-166',79);
INSERT INTO SCORE(SNO,CNO,DEGREE) VALUES (108,'6-166',81); 

 

 题目：
1、 查询Student表中的所有记录的Sname、Ssex和Class列。
3、 查询Student表的所有记录。
4、 查询Score表中成绩在60到80之间的所有记录。
5、 查询Score表中成绩为85，86或88的记录。
6、 查询Student表中“95031”班或性别为“女”的同学记录。
7、 以Class降序查询Student表的所有记录。
8、 以Cno升序、Degree降序查询Score表的所有记录。
9、 查询“95031”班的学生人数。
10、查询Score表中的最高分的学生学号和课程号。
11、查询‘3-105’号课程的平均分。
12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
13、查询最低分大于70，最高分小于90的Sno列。
14、查询所有学生的Sname、Cno和Degree列。
15、查询所有学生的Sno、Cname和Degree列。
16、查询所有学生的Sname、Cname和Degree列。
17、查询“95033”班所选课程的平均分。
18、假设使用如下命令建立了一个grade表：
create table grade(low   number(3,0),upp   number(3),rank   char(1));
insert into grade values(90,100,’A’);
insert into grade values(80,89,’B’);
insert into grade values(70,79,’C’);
insert into grade values(60,69,’D’);
insert into grade values(0,59,’E’);
commit;
现查询所有同学的Sno、Cno和rank列。
19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
23、查询“张旭“教师任课的学生成绩。
24、查询选修某课程的同学人数多于5人的教师姓名。
25、查询95033班和95031班全体学生的记录。
26、查询存在有85分以上成绩的课程Cno.
27、查询出“计算机系“教师所教课程的成绩表。
28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
31、查询所有教师和同学的name、sex和birthday.
32、查询所有“女”教师和“女”同学的name、sex和birthday.
33、查询成绩比该课程平均成绩低的同学的成绩表。
34、查询所有任课教师的Tname和Depart.
35  查询所有未讲课的教师的Tname和Depart. 
36、查询至少有2名男生的班号。
37、查询Student表中不姓“王”的同学记录。
38、查询Student表中每个学生的姓名和年龄。
39、查询Student表中最大和最小的Sbirthday日期值。
40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
41、查询“男”教师及其所上的课程。
42、查询最高分同学的Sno、Cno和Degree列。
43、查询和“李军”同性别的所有同学的Sname.
44、查询和“李军”同性别并同班的同学Sname.
45、查询所有选修“计算机导论”课程的“男”同学的成绩表

1.查询Student表中的所有记录的Sname、Ssex和Class列

2.查询教师所有的单位即不重复的Depart列

3.查询Student表的所有记录

4. 查询Score表中成绩在60到80之间的所有记录

5.查询Score表中成绩为85，86或88的记录

6.查询Student表中“95031”班或性别为“女”的同学记录

 

7. 以Class降序查询Student表的所有记录

8.以Cno升序、Degree降序查询Score表的所有记录 

9.查询“95031”班的学生人数

10.查询Score表中的最高分的学生学号和课程号

11. 查询‘3-105’号课程的平均分

12.查询Score表中至少有5名学生选修的并以3开头的课程的平均分数

13. 查询最低分大于70，最高分小于90的Sno列

 14.查询所有学生的Sname、Cno和Degree列

SELECT SNAME,CNO,DEGREE FROM score,student WHERE student.SNO = score.SNO

SELECT A.SNAME,B.CNO,B.DEGREE FROM STUDENT AS A JOIN SCORE AS B ON A.SNO=B.SNO

15.查询所有学生的Sno、Cname和Degree列

select sno,cname,degree from course,score where score.cno=course.cno
SELECT A.SNO,B.CNAME,A.DEGREE FROM score AS A  JOIN course AS B ON A.CNO=B.CNO

16.查询所有学生的Sname、Cname和Degree列

select SNAME,CNAME,DEGREE from student,course,score where student.SNO = score.SNO and score.cno=course.cno

SELECT A.SNAME,B.CNAME,C.DEGREE FROM STUDENT A JOIN (COURSE B,SCORE C)
ON A.SNO=C.SNO AND B.CNO =C.CNO

17.查询“95033”班所选课程的平均分

18.假设使用如下命令建立了一个grade表：
create table grade(low   number(3,0),upp   number(3),rank   char(1));
insert into grade values(90,100,’A’);
insert into grade values(80,89,’B’);
insert into grade values(70,79,’C’);
insert into grade values(60,69,’D’);
insert into grade values(0,59,’E’);
commit;
现查询所有同学的Sno、Cno和rank列。

19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录

20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录

21.查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录

22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列

23、查询“张旭“教师任课的学生成绩

 

 或者更好的方法：SELECT SNO,DEGREE FROM SCORE A JOIN (TEACHER B,COURSE C)
ON A.CNO=C.CNO AND B.TNO=C.TNO
WHERE B.TNAME='张旭'

24、查询选修某课程的同学人数多于5人的教师姓名 

25、查询95033班和95031班全体学生的记录

select * from students where class='95033' or class='95031'

26、查询存在有85分以上成绩的课程Cno

select cno from scores group by cno having MAX(degree)>85

select distinct cno from score where degree in (select degree from score where  degree>85)

27、查询出“计算机系“教师所教课程的成绩表

28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof