欢迎大家访问我的博客!

SQL入门基础45题

数据挖掘 peike 3513℃

SQL基础入门45题基本覆盖了大部分知识点,作为入门练习还是不错。

运行环境:MySQL 5.7。

大部分都是SQL标准语法。有一些题目由于题目意思理解不同可能会有一定的不同,欢迎大家讨论。

创建表结构

CREATE TABLE STUDENT
(SNO VARCHAR(3) NOT NULL,
SNAME VARCHAR(4) NOT NULL,
SSEX VARCHAR(2) NOT NULL,
SBIRTHDAY DATETIME,
CLASS VARCHAR(5));

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, 1) NOT NULL);

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);

INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'Z.H'
,'m' ,'1977-09-01',95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'K.M'
,'m' ,'1975-10-02',95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'W.L'
,'f' ,'1976-01-23',95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'L.J'
,'m' ,'1976-02-20',95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'W.F'
,'f' ,'1975-02-10',95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'L.Y'
,'m' ,'1974-06-03',95031);

INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-105' ,'computer',825);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-245' ,'system' ,804);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('6-166' ,'circuit' ,856);
INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('9-888' ,'math' ,100);

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-106',79);
INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'6-166',81);

INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (804,'L.C','m','1958-12-02','AP','CS');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (856,'Z.X','m','1969-03-12','TE','EE');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (825,'W.P','f','1972-05-05','TA','CS');
INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
VALUES (831,'L.B','f','1977-08-14','TA','EE');

CREATE TABLE GRADE(LOW NUMERIC(3,0),UPP NUMERIC(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');

题目

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列。

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

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

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

18、现查询所有同学的Sno、Cno和rank列。

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

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

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

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

23、查询“Z.X”教师任课的学生成绩。

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

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

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

27、查询出“CS“教师所教课程的成绩表。

28、查询“CS”与“EE“不同职称的教师的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、查询所有选修“computer”课程的“男”同学的成绩表。

解答

#1
SELECT SNAME,SSEX,CLASS FROM STUDENT;
#2
SELECT DISTINCT DEPART FROM TEACHER;
#3
SELECT * FROM STUDENT;
#4
SELECT * FROM SCORE WHERE DEGREE BETWEEN 60 AND 80;
#5
SELECT * FROM SCORE WHERE DEGREE IN (85,86,88);
#6
SELECT * FROM STUDENT WHERE CLASS=95031 OR SSEX='f';
#7
SELECT * FROM STUDENT ORDER BY CLASS DESC ;
#8
SELECT * FROM SCORE ORDER BY CNO ASC, DEGREE DESC ;
#9
SELECT count(*) FROM STUDENT WHERE CLASS=95031;
#10
SELECT SNO, CNO FROM SCORE WHERE DEGREE=(SELECT max(DEGREE) FROM SCORE);
#11
SELECT avg(DEGREE) FROM SCORE WHERE CNO='3-105';
#12 
SELECT avg(DEGREE),CNO FROM SCORE WHERE CNO LIKE '3%' GROUP BY CNO HAVING count(SNO)>=5;
#13
SELECT SNO FROM SCORE GROUP BY SNO HAVING max(DEGREE)<90 and min(DEGREE)>70;
#14
SELECT STUDENT.SNAME, SCORE.CNO, SCORE.DEGREE from STUDENT JOIN SCORE ON STUDENT.SNO=SCORE.SNO;
#15
SELECT SCORE.SNO, COURSE.CNAME,SCORE.DEGREE from SCORE JOIN COURSE ON SCORE.CNO= COURSE.CNO;
#16
SELECT STUDENT.SNAME,COURSE.CNAME,SCORE.DEGREE FROM SCORE JOIN (COURSE,STUDENT)
ON STUDENT.SNO=SCORE.SNO AND COURSE.CNO =SCORE.CNO;
#17
SELECT avg(SCORE.DEGREE) FROM STUDENT JOIN SCORE ON SCORE.SNO=STUDENT.SNO WHERE CLASS=95033;
#18
SELECT SCORE.SNO,SCORE.CNO,GRADE.RANK FROM SCORE,GRADE WHERE SCORE.DEGREE BETWEEN GRADE.LOW AND GRADE.UPP;
#19
SELECT * FROM SCORE WHERE CNO='3-105'AND DEGREE>(SELECT DEGREE FROM SCORE WHERE CNO='3-105' AND SNO=109);
#20 
SELECT * FROM SCORE WHERE DEGREE<(SELECT MAX(DEGREE) FROM SCORE)
GROUP BY SNO HAVING count(SNO)>1 ORDER BY DEGREE;
#21
SELECT * FROM SCORE WHERE DEGREE>(SELECT DEGREE FROM SCORE WHERE SNO=109 AND CNO='3-105');
#22
SELECT SNO,SNAME,SBIRTHDAY FROM STUDENT 
WHERE YEAR(SBIRTHDAY)=(SELECT YEAR(SBIRTHDAY) FROM STUDENT WHERE SNO=108);
#23
SELECT SCORE.SNO,SCORE.DEGREE FROM SCORE JOIN (TEACHER,COURSE)
ON SCORE.CNO=COURSE.CNO AND COURSE.TNO=TEACHER.TNO WHERE TEACHER.TNAME="Z.X";
#24 
SELECT TEACHER.TNAME FROM TEACHER JOIN (COURSE,SCORE) 
ON TEACHER.TNO=COURSE.TNO AND COURSE.CNO=SCORE.CNO 
GROUP BY COURSE.CNO HAVING count(COURSE.CNO)>5;
#25
SELECT * FROM STUDENT JOIN SCORE ON STUDENT.SNO=SCORE.SNO WHERE CLASS='95033' OR '95031';
#26
SELECT CNO FROM SCORE GROUP BY CNO HAVING max(DEGREE)>85;
#27
SELECT SCORE.* FROM SCORE JOIN (COURSE,TEACHER) 
ON COURSE.CNO=SCORE.CNO AND COURSE.TNO=TEACHER.TNO WHERE TEACHER.DEPART='CS';
#28
SELECT TNAME,PROF FROM TEACHER 
WHERE DEPART='CS' AND PROF NOT IN (SELECT PROF FROM TEACHER WHERE DEPART='EE');
#29
SELECT * FROM SCORE 
WHERE CNO='3-105' AND DEGREE>ANY(SELECT DEGREE FROM SCORE WHERE CNO='3-245') ORDER BY DEGREE DESC;
#30
SELECT * FROM SCORE WHERE CNO='3-105' AND DEGREE>ALL(SELECT DEGREE FROM SCORE WHERE CNO='3-245');
#31
SELECT SNAME AS NAME,SSEX AS SEX, SBIRTHDAY AS BIRTHDAY FROM STUDENT
UNION ALL
SELECT TNAME AS NAME,TSEX AS SEX,TBIRTHDAY AS BIRTHDAY FROM TEACHER;
#32
SELECT SNAME AS NAME,SSEX AS SEX, SBIRTHDAY AS BIRTHDAY FROM STUDENT WHERE SSEX='f'
UNION ALL
SELECT TNAME AS NAME,TSEX AS SEX,TBIRTHDAY AS BIRTHDAY FROM TEACHER WHERE TSEX='f';
#33 
SELECT * FROM SCORE A WHERE DEGREE<(SELECT avg(DEGREE) FROM SCORE B WHERE A.CNO=B.CNO);
#34
SELECT TNAME, DEPART FROM TEACHER WHERE TNO IN (SELECT TNO FROM COURSE);
SELECT TEACHER.TNAME,TEACHER.DEPART FROM TEACHER JOIN COURSE ON TEACHER.TNO=COURSE.TNO;
#35
SELECT TNAME, DEPART FROM TEACHER WHERE TNO NOT IN (SELECT TNO FROM COURSE);
SELECT TNAME,DEPART FROM TEACHER LEFT JOIN COURSE USING(TNO) WHERE isnull(COURSE.TNO);
#36
SELECT CLASS FROM STUDENT WHERE SSEX='m' GROUP BY CLASS HAVING count(SSEX)>1;
#37
SELECT * FROM STUDENT WHERE SNAME NOT LIKE 'W.%';
#38
SELECT SNAME, (YEAR(now())-YEAR(SBIRTHDAY)) AS AGE FROM STUDENT;
#39
SELECT SNAME,SBIRTHDAY AS MAX FROM STUDENT WHERE SBIRTHDAY=(SELECT min(SBIRTHDAY) FROM STUDENT)
UNION ALL
SELECT SNAME,SBIRTHDAY AS MIN FROM STUDENT WHERE SBIRTHDAY=(SELECT max(SBIRTHDAY) FROM STUDENT);
#40
SELECT * FROM STUDENT ORDER BY CLASS DESC,SBIRTHDAY ASC;
#41
SELECT TEACHER.TNAME, COURSE.CNAME FROM TEACHER JOIN COURSE USING(TNO) WHERE TEACHER.TSEX='m';
#42
SELECT * FROM SCORE WHERE DEGREE=(SELECT MAX(DEGREE) FROM SCORE);
#43
SELECT SNAME FROM STUDENT WHERE SSEX=(SELECT SSEX FROM STUDENT WHERE SNAME='L.J');
#44
SELECT SNAME FROM STUDENT WHERE SSEX=(SELECT SSEX FROM STUDENT WHERE SNAME='L.J')
AND CLASS=(SELECT CLASS FROM STUDENT WHERE SNAME='L.J');
#45
SELECT A.* FROM SCORE A JOIN (STUDENT B,COURSE C) 
USING(SNO,CNO) WHERE B.SSEX='m' AND C.CNAME='computer';

文章发布时间: 2017-03-13 22:27

最后修改于: 2017-03-14 13:39

原始链接: http://www.peikeli.com/data-mining/sql-practice/

转载请注明: 万物比特 » SQL入门基础45题

版权协议: 本作品采用©知识共享署名-非商业性使用-禁止演绎 4.0 国际许可协议进行许可

喜欢 (10)or分享 (0)