SQL 45习题+答案，硬核一下，欢迎拿走

最新推荐文章于 2024-02-29 18:34:55 发布

打工人何苦为难打工人

最新推荐文章于 2024-02-29 18:34:55 发布

阅读量546

点赞数 6

分类专栏：干饭人刚需系列文章标签： sql

本文链接：https://blog.csdn.net/m0_66194642/article/details/122470935

版权

干饭人刚需系列专栏收录该内容

24 篇文章 1 订阅

订阅专栏

CREATE TABLE STUDENTS

(

NO VARCHAR2(3) NOT NULL,

NAME VARCHAR2(4) NOT NULL,

SEX VARCHAR2(2) NOT NULL,

BIRTHDAY DATE,

CLASS VARCHAR2(5)

);

CREATE TABLE COURSES

(

NO VARCHAR2(5) NOT NULL,

NAME VARCHAR2(10) NOT NULL,

TEACHER_NO VARCHAR2(10) NOT NULL

);

CREATE TABLE SCORES

(

STUDENT_NO VARCHAR2(3) NOT NULL,

COURSE_NO VARCHAR2(5) NOT NULL,

DEGREE NUMBER(10, 1) NOT NULL

);

CREATE TABLE TEACHERS

(

NO VARCHAR2(3) NOT NULL,

NAME VARCHAR2(4) NOT NULL,

SEX VARCHAR2(2) NOT NULL,

BIRTHDAY DATE NOT NULL,

PROF VARCHAR2(6),

DEPART VARCHAR2(10) NOT NULL

);

CREATE TABLE GRADES

(

RANK CHAR(1) NOT NULL,

LOW NUMBER(3, 0) NOT NULL,

UPP NUMBER(3,0) NOT NULL

);

INSERT INTO STUDENTS VALUES ('108', '曾华', '男' ,to_date('1977-09-01', 'yyyy-mm-dd'), '95033');

INSERT INTO STUDENTS VALUES ('105', '匡明', '男' ,to_date('1975-10-02', 'yyyy-mm-dd'), '95031');

INSERT INTO STUDENTS VALUES ('107', '王丽', '女' ,to_date('1976-01-23', 'yyyy-mm-dd'), '95033');

INSERT INTO STUDENTS VALUES ('101', '李军', '男' ,to_date('1976-02-20', 'yyyy-mm-dd'), '95033');

INSERT INTO STUDENTS VALUES ('109', '王芳', '女' ,to_date('1975-02-10', 'yyyy-mm-dd'), '95031');

INSERT INTO STUDENTS VALUES ('103', '陆君', '男' ,to_date('1974-06-03', 'yyyy-mm-dd'), '95031');

INSERT INTO STUDENTS VALUES ('108', '曾华', '男' ,'1977-09-01', '95033');

INSERT INTO STUDENTS VALUES ('110', '匡明', '男' ,'1975-10-02', '95031');

INSERT INTO STUDENTS VALUES ('107', '王丽', '女' ,'1976-01-23', '95033');

INSERT INTO STUDENTS VALUES ('111', '李军', '男' ,'1976-02-20', '95033');

INSERT INTO STUDENTS VALUES ('109', '王芳', '女' ,'1975-02-10', '95031');

INSERT INTO STUDENTS VALUES ('103', '陆君', '男' ,'1974-06-03', '95031');

INSERT INTO COURSES VALUES ('3-105', '计算机导论', '825');

INSERT INTO COURSES VALUES ('3-245', '操作系统', '804');

INSERT INTO COURSES VALUES ('6-166', '数据电路', '856');

INSERT INTO COURSES VALUES ('9-888', '高等数学', '831');

INSERT INTO SCORES VALUES ('103', '3-245', 86);

INSERT INTO SCORES VALUES ('105', '3-245', 75);

INSERT INTO SCORES VALUES ('109', '3-245', 68);

INSERT INTO SCORES VALUES ('103', '3-105', 92);

INSERT INTO SCORES VALUES ('105', '3-105', 88);

INSERT INTO SCORES VALUES ('109', '3-105', 76);

INSERT INTO SCORES VALUES ('101', '3-105', 64);

INSERT INTO SCORES VALUES ('107', '3-105', 91);

INSERT INTO SCORES VALUES ('108', '3-105', 78);

INSERT INTO SCORES VALUES ('101', '6-166', 85);

INSERT INTO SCORES VALUES ('107', '6-106', 79);

INSERT INTO SCORES VALUES ('108', '6-166', 81);

INSERT INTO TEACHERS VALUES ('804', '李诚', '男', to_date('1958-12-02', 'yyyy-mm-dd'), '副教授', '计算机系');

INSERT INTO TEACHERS VALUES ('856', '张旭', '男', to_date('1969-03-12', 'yyyy-mm-dd'), '讲师', '电子工程系');

INSERT INTO TEACHERS VALUES ('825', '王萍', '女', to_date('1972-05-05', 'yyyy-mm-dd'), '助教', '计算机系');

INSERT INTO TEACHERS VALUES ('831', '刘冰', '女', to_date('1977-08-14', 'yyyy-mm-dd'), '助教', '电子工程系');

INSERT INTO TEACHERS VALUES ('804', '李诚', '男','1958-12-02', '副教授', '计算机系');

INSERT INTO TEACHERS VALUES ('856', '张旭', '男','1969-03-12', '讲师', '电子工程系');

INSERT INTO TEACHERS VALUES ('825', '王萍', '女','1972-05-05', '助教', '计算机系');

INSERT INTO TEACHERS VALUES ('831', '刘冰', '女','1977-08-14', '助教', '电子工程系');

INSERT INTO GRADES VALUES('A', 90, 100);

INSERT INTO GRADES VALUES('B', 80, 89);

INSERT INTO GRADES VALUES('C', 70, 79);

INSERT INTO GRADES VALUES('D', 60, 69);

INSERT INTO GRADES VALUES('E', 0, 59);

练习题目:

首先画出表的关系图

1、查询STUDENTS表中的所有记录的NAME、SEX和CLASS列。

select name,sex,class from students;

2、查询教师所有的单位即不重复的DEPART列。

select distinct depart from teachers;

3、查询STUDENTS表的所有记录。

select no as '学号', name as '姓名',sex as '性别',brithday as'出生日期',class as'班号'from students;

4、查询SCORES表中成绩在60到80之间的所有记录。

select * from scores where degree>'60' and degree<'80';

select * from scores where degree between 60 and 80;

5、查询SCORES表中成绩为85，86或88的记录。

select * from scores where degree in('85','86','88');

6、查询STUDENTS表中“95031”班或性别为“女”的同学记录。

select * from students where class='95031' or sex='女';

7、以CLASS降序查询STUDENTS表的所有记录。

select * from students order by class desc;

8、以NO升序、DEGREE降序查询SCORES表的所有记录

select * from scores order by course_no asc,degree desc;

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

select count(*) as '95031班人数' from students where class='95031';

10、查询SCORES表中的最高分的学生学号和课程号。

select student_no as '学号',course_no as '课程号',degree as '最高分' from scores where degree=(select max(degree) from scores);

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

select course_no as '课程号',avg(degree) as '课程平均分' from scores where course_no='3-105';

错误 ;Mixinf of GROUP columns(min(),max(),count(0,...)with no GROUP columns is illegal if there is no GROUP BY clause

答案：select avg(degree)as 课程平均分 from scores where course_no='3-105';

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

select course_no as 课程号,avg(degree) as 平均分 from scores where course_no like '3%' group by course_no having count(*)>5;

13、查询最低分大于70，最高分小于90的NO列。

select no student_no where degree between 70 and 90; 我的错误答案。

select course_no from scores group by course_no having min(degree)>70 and max(degree)<90;

14、查询所有学生的NAME、NO和DEGREE列。

select scores.student_no,students.name,scores.degree from students,scores where students.no=scores.student_no;

15、查询所有学生的NO、NAME和DEGREE列。

select s.no,s.name,c.degree from students s,scores c where s.no=c.student_no;

16、查询所有学生的NAME、NAME和DEGREE列。

select s.name,o.degree,c.name from students s,scores o,courses c where s.no=o.student_no and c.no=o.course_no;

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

select avg(c.degree) from students s,scores c where s.no=c.student_no and s.class='95033';

18、查询所有同学的NO、NO和RANK列。

select student_no,course_no,rank from scores,grades where degree between low and upp;

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

select s.no,s.name,s.sex,s.borthday,s.class from students s,scores c where s.no=c.student_no and c.course_no in(3-105） and c.degree>(select degree from scores where student_no='109');

错误信息：Subquery returns more than 1 row

select s1.student_no,s1.course_no,s1.degree from scores s1,scores s2 where s1.course_no='3-105' and s1.degree>s2.degree and s2.student_no='109' and s2.course_no!='3-105';

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

select s1.student_no,s1.course_no,s1.degree from scores s1 where s1.degree<(select max(degree) from scores s2 where s1.course_no=s2.course_no);

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

select s1.student_no,s1.course_no,s1.degree from scores s1 where s1.degree<(select degree from scores s2 where s2.course_no='3-105' and s2.student_no='109');

22、查询和学号为108的同学同年出生的所有学生的NO、NAME和BIRTHDAY列。

select s1.no,s1.name,s1.borthday from students s1 where s1.borthday=(select borthday from students s2 where s2.no='108');

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

select s.degree,c.name,s.student_no from scores s,teachers t,courses c where c.teacher_no=t.no and c.no=s.course_no and t.name='张旭';

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

select t.name,c.no from courses c,teachers t where c.teacher_no=t.no and c.no in(select s.course_no from scores s group by s.course_no having count(s.course_no)>5);

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

select * from students where class in('95033','95031');

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

select course_no from scores group by course_no having max(degree)>85;

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

select s.degree,s.student_no,s.course_no,c.teacher_no from scores s,courses c where s.course_no=c.no and c.teacher_no in(select t.no from teachers t where t.depart='计算机系');

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

select distinct x.name,x.prof from teachers x,teachers y where x.prof!=y.prof and x.depart='电子工程系' and y.depart='计算机系';

29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的STUDENT_NO、COURSE_NO和DEGREE,并按DEGREE从高到低次序排序。

select * from scores x where x.course_no='3-105' and x.degree>any (select y.degree from scores y where y.course_no='3-245')order by degree desc;

30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的STUDENT_NO、STUDENT_NO和DEGREE.

select * from scores x where x.course_no='3-105' and x.degree>all (select y.degree from scores y where y.course_no='3-245');

31、查询所有教师和同学的NAME、SEX和BIRTHDAY.

select s.name,s.borthday,s.sex,t.name,t.birthday,t.sex from teachers t,students s;

select name,sex,birthday from teachers union select name,sex,borthday from students;

32、查询所有“女”教师和“女”同学的NAME、SEX和BIRTHDAY.

select name,sex,birthday from teachers where sex='女'union select name,sex,borthday from students where sex='女';

33、查询成绩比该课程平均成绩低的同学的成绩表。

select * from scores a where a.degree<(select avg(b.degree) from scores b group by b.course_no having a.course_no=b.course_no);

34、查询所有任课教师的NAME和DEPART.

select t.name,t.depart from teachers t where t.no in(select c.teacher_no from courses c);

35 查询所有未讲课的教师的NAME和DEPART.

select t.name,t.depart from teachers t where t.no not in(select c.teacher_no from courses c);

36、查询至少有2名男生的班号。

select x.class,count(*) from (select * from students where sex='男') x group by x.class having count(*)>1;

37、查询STUDENTS表中不姓“王”的同学记录。

select * from students where name not like '王%';

38、查询STUDENTS表中每个学生的姓名和年龄。

select name,date_format(from_days(to_days(now())-to_days(borthday)),'%y') as age from students;

39、查询STUDENTS表中最大和最小的BIRTHDAY日期值。

select max(x.borthday) from students x union select min(y.borthday) from students y;

40、以班号和年龄从大到小的顺序查询STUDENTS表中的全部记录。

select * from students order by class desc,borthday;

41、查询“男”教师及其所上的课程。

select x.name,y.name from teachers x,courses y where x.no=y.teacher_no and x.sex='男';

42、查询最高分同学的STUDENT_NO、COURSE_NO和DEGREE列。

select * from scores where degree=(select max(degree)from scores);

43、查询和“李军”同性别的所有同学的NAME.

select name from students where sex=(select sex from students where name='李军');

44、查询和“李军”同性别并同班的同学NAME.

select name from students where sex=(select sex from students where name='李军') and class=(select class from students where name='李军');

45、查询所有选修“计算机导论”课程的“男”同学的成绩表

select * from scores s where s.student_no in(select a.no from students a where a.sex='男') and s.course_no=(select b.no from courses b where b.name='计算机导论');