经典SQL练习题

最新推荐文章于 2023-05-24 12:02:15 发布

xbgprogrammer

最新推荐文章于 2023-05-24 12:02:15 发布

阅读量344

点赞数

from http://www.2cto.com/database/201402/282086.html

今天在网上找了几道经典的SQL练习题做了一下，虽然都不难，但是对打基础是很有好处的，在明白的基础上可以进一步做分析，来研究一下各种解法的优劣，甚至进行简单的优化。。
现在将题目和答案分享一下。我使用的是MYSQL 5.0，但是绝大部分都是标准SQL。

CREATE TABLE STUDENT
(SNO VARCHAR(3) NOT NULL,
SNAME VARCHAR(4) NOT NULL,
SSEX VARCHAR(2) NOT NULL,
SBIRTHDAY DATETIME,
CLASS VARCHAR(5))ENGINE=MyISAM AUTO_INCREMENT=348 DEFAULT CHARSET=utf8 ;;

CREATE TABLE COURSE
(CNO VARCHAR(5) NOT NULL,
CNAME VARCHAR(10) NOT NULL,
TNO VARCHAR(10) NOT NULL)ENGINE=MyISAM AUTO_INCREMENT=348 DEFAULT CHARSET=utf8 ;;

CREATE TABLE SCORE
(SNO VARCHAR(3) NOT NULL,
CNO VARCHAR(5) NOT NULL,
DEGREE NUMERIC(10, 1) NOT NULL)ENGINE=MyISAM AUTO_INCREMENT=348 DEFAULT CHARSET=utf8 ;;

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)ENGINE=MyISAM AUTO_INCREMENT=348 DEFAULT CHARSET=utf8 ;;

INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾华'
,'男' ,1977-09-01,95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明'
,'男' ,1975-10-02,95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王丽'
,'女' ,1976-01-23,95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李军'
,'男' ,1976-02-20,95033);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳'
,'女' ,1975-02-10,95031);
INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陆君'
,'男' ,1974-06-03,95031);

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' ,'高等数学' ,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,'李诚','男','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','助教','电子工程系');

--1、查询Student表中的所有记录的Sname、Ssex和Class列。
--select S.SNAME, S.SSEX, S.CLASS from dbo.STUDENT S

--2、查询教师所有的单位即不重复的Depart列。
--select distinct DEPART from dbo.TEACHER

--3、查询Student表的所有记录
--select * from STUDENT

--4、查询Score表中成绩在60到80之间的所有记录。
--select * from dbo.SCORE where DEGREE between 60 and 80

--5、查询Score表中成绩为85，86或88的记录
--select * from dbo.SCORE where DEGREE in (85,86,88)

--6、查询Student表中“95031”班或性别为“女”的同学记录。
--select * from STUDENT where CLASS = '95031' or SSEX='女'

--7、以Class降序查询Student表的所有记录。
--select * from STUDENT order by CLASS desc

--8、以Cno升序、Degree降序查询Score表的所有记录
--select * from SCORE order by CNO asc, DEGREE desc

--9、查询“95031”班的学生人数
--select s.CLASS, COUNT(*) as population from STUDENT s where s.CLASS = '95031' group by s.CLASS

--10、查询Score表中的最高分的学生学号和课程号。
--select top 1 SNO, CNO from SCORE order by DEGREE desc

--11、查询‘计算机导论’课程的平均分。
--select C.cno, C.CNAME, AVG(S.DEGREE) from SCORE S inner join COURSE C on S.CNO = C.CNO where c.CNAME='计算机导论' group by C.CNO, C.CNAME

--12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
--select CNO, AVG(degree) from SCORE where CNO like '3%' group by CNO having COUNT(*)>5

--13、查询最低分大于70，最高分小于90的Sno列。
--select SNO, MIN(degree), MAX(degree) from SCORE S group by SNO having MAX(degree) <90 and MIN(degree)>70

--14、查询所有学生的Sname、Cno和Degree列
--select A.SNAME, B.CNO, B.DEGREE from STUDENT A inner join SCORE B on A.SNO=B.SNO

--16、查询所有学生的Sname、Cname和Degree列。
--select B.SNAME, A.CNO, C.CNAME, A.DEGREE from SCORE A inner join STUDENT B on A.SNO = B.SNO LEFT join COURSE C on A.CNO = C.CNO

--17、查询“95033”班所选课程的平均分。
--select B.CLASS, AVG(A.DEGREE) from SCORE A inner join STUDENT B on A.SNO = B.SNO where B.CLASS = '95033' group by B.CLASS

--19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
--select B.SNO, B.SNAME from SCORE A inner join STUDENT B on A.SNO = B.SNO where A.CNO='3-105' and A.DEGREE > (select DEGREE from SCORE where SNO='109' and CNO='3-105')

--20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。
--SELECT sno FROM score s WHERE DEGREE<(SELECT MAX(DEGREE) FROM SCORE) GROUP BY SNO HAVING COUNT(SNO)>1 --ORDER BY DEGREE

--23、查询“张旭“教师任课的学生成绩。
--select A.SNO, C.CNAME, T.TNAME from SCORE A inner join COURSE C on A.CNO = C.CNO inner join TEACHER T on T.TNO=C.TNO where T.TNAME='张旭'

--24、查询选修某课程的同学人数多于5人的教师姓名。
--select * from COURSE C inner join TEACHER T on C.TNO=t.TNO where C.CNO in( select cno from SCORE group by CNO having COUNT(*)>5)

--26、查询存在有85分以上成绩的课程Cno.
--select CNO, MAX(degree) from SCORE group by CNO having MAX(degree)>85

--27、查询出“计算机系“教师所教课程的成绩表。
--select * from TEACHER
--select C.CNAME, T.TNAME, T.DEPART from COURSE C inner join TEACHER T on C.TNO=T.TNO where T.DEPART='计算机系'

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

--31、查询所有教师和同学的name、sex和birthday.
--select SNAME as name, SSEX as sex, SBIRTHDAY FROM STUDENT union select TNAME as name, TSEX as sex, TBIRTHDAY as birthdary from TEACHER

--33、查询成绩比该课程平均成绩低的同学的成绩表。
--select * from SCORE A where A.DEGREE < (select AVG(degree) from SCORE B where B.CNO=A.CNO group by B.CNO)

--34、查询所有任课教师的Tname和Depart.