经典SQL面试题.docx

Student(Sid,Sname,Sage,Sse*) 学生表CREATE TABLE student (sid varchar(10) NOT NULL,sName varchar(20) DEFAULT NULL,sAge datetime DEFAULT 1980-10-12 23:12:36,sSe* varchar(10) DEFAULTNULL,PRIMARY KEY (sid)ENGINE=InnoDBDEFAULTCHARSET=utf8;Course(Cid,ame,Tid) 课程表CREATE TABLE course (cid varchar(10) NOT NULL,ame varchar(10) DEFAULT NULL,tid int(20) DEFAULT NULL,PRIMARY KEY (cid)ENGINE=InnoDB DEFAULT CHARSET=utf8; SC(Sid,Cid,score)成绩表CREATE TABLE sc (sid varchar(10) DEFAULTNULL,cid varchar(10) DEFAULTNULL,score int(10) DEFAULT NULL)ENGINE=InnoDB DEFAULT CHARSET=utf8; Teacher(Tid,Tname)教师表CREATE TABLE taacher (tid int(10) DEFAULT NULL,tNamevarchar(10) DEFAULTNULL)ENGINE=InnoDB DEFAULT CHARSET=utf8;数据：MySQL insert into taacher(tid,tName) values (1,教师)，(斓以琛)，(叶平)；insert into student(sid,sName,sAge,sSe*) values (11,三丰， 1980-10-12 23:12:36,男)，(12无极，1995-10-12 23:12:36,男)，(13奎， 1992-10-12 23:12:36,女)，(14元宝， 1980-10-12 23:12:36,女),(15,世明, 1981-10-12 23:12:36,男)，(16六， 1986-10-12 23:12:36,男)，(17田七，1981-10-12 23:12:36,女);insert into sc(sid,cid,score)values ( 1, 1,80),( 1, 2,60),( 1, 3,75),( 2, 1,85),( 2, 2,70),( 3,4,1),( 3, 1,90),( 3, 2,55),( 4, 2,65),( 4, 3,60);insert into course(cid,ame,tid) values (1,企业管理，3),( 2马克思，3),( 3,UML,2),(0(数据库，1),( 5,英语，1);问题:1、查询1 课程比2 课程成绩高的所有学生的*;select a.Sid from (selectsid,score from SC where Cid=1) a,(selectsid,scorefrom SC where Cid=2) bwhere a.scoreb.score and a.sid=b.sid;2、查询平均成绩大于60分的同学的*和平均成绩；select Sid,avg(score)from scgroup by Sid having avg(score) 60;3、查询所有同学的*、选课数、总成绩;select Student.Sid,Student.Sname,count(SC.Cid),sum(score)from Student leftOuter join SC on Student.Sid=SC.Sidgroup by Student.Sid,Sname4、查询姓的教师的个数;select count(distinct(Tname)from Teacherwhere Tname like%;5、查询没学过叶平 教师课的同学的*、；select Student.Sid,Student.Snamefrom Studentwhere Sid not in (select distinct(SC.Sid) from SC,Course,Teacher where SC.Cid=Course.Cid and Teacher.Tid=Course.Tid and Teacher.Tname= 叶平);6、查询学过1 并且也学过编号2 课程的同学的*、；select Student.Sid,Student.Sname from Student,SC where Student.Sid=SC.Sid and SC.Cid=1and e*ists(Select * from SC as SC_2 where SC_2.Sid=SC.Sid and SC_2.Cid= 2);7、查询学过叶平 教师所教的所有课的同学的*、；select Sid,Snamefrom Studentwhere Sid in (selectSid from SC ,Course ,Teacher where SC.Cid=Course.Cid and Teacher.Tid=Course.Tid and Teacher.Tname= 叶平group by Sid having count(SC.Cid) = (select count(Cid) from Course,Teacher where Teacher.Tid=Course.Tid and Tname= 叶平);8、查询课程编号2 的成绩比课程编号1 课程低的所有同学的*、；Select Sid,Sname from (select Student.Sid,Student.Sname,score , (selects core from SC SC_2 where SC_2.Sid=Student.Sid and SC_2.Cid= 2) score2from Student,SC where Student.Sid=SC.Sid and Cid=1) S_2 where score2 60);10、查询没有学全所有课的同学的*、；select Student.Sid,Student.Snamefrom Student,SCwhere Student.Sid=SC.Sid group by Student.Sid,Student.Sname having count(Cid) =60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数FROMSC T,Coursewheret.Cid=course.CidGROUPBY t.CidORDERBY 1 * SUM(CASE WHEN isnull(score,0)=60 THEN 1 ELSE 0 END)/COUNT(*) DESC20、查询如下课程平均成绩和及格率的百分数用1行显示）：企业管理1，马克思2，OO&UML 3，数据库4Cid WHEN 1 THENSELECT SUM(CASE WHEN Cid =1 THEN score ELSE 0 END)/SUM(CASE1 ELSE 0 END) AS企业管理平均分,1 * SUM(CASE WHENCid = 1 AND score = 60 THEN 1 ELSE0 END)/SUM(CASEWHENCid1 THEN 1 ELSE 0END) AS企业管理及格百分数,SUM(CASE WHEN Cid2 THEN score ELSE 0END)/SUM(CASECid WHEN 2THEN 1ELSE0 END) AS马克思平均分,1 * SUM(CASEWHENCid = 2 AND score =60 THEN 1 ELSE0 END)/SUM(CASEWHENCid 2 THEN 1ELSE 0END) AS马克思及格百分数,SUM(CASE WHENCid3 THEN score ELSE 0END)/SUM(CASECid WHEN 3THEN 1ELSE0 END) AS UML平均分,1 * SUM(CASEWHENCid = 3 AND score =60 THEN 1 ELSE0 END)/SUM(CASEWHENCid 3 THEN 1ELSE 0END) AS UML及格百分数,SUM(CASE WHENCid4 THEN score ELSE 0END)/SUM(CASECid WHEN 4THEN 1ELSE0 END) AS数据库平均分,1 * SUM(CASE WHENCid = 4 AND score =60 THEN 1 ELSE0 END)/SUM(CASEWHENCid 4 THEN 1 ELSE 0END) AS数据库及格百分数FROM SC21、查询不同教师所教不同课程平均分从高到低显示SELECT ma*(Z.Tid) AS教师 ID,MA*(Z.Tname) AS 教师，C.Cid AS 课程 I D,MA*(C.ame) AS课程名称,AVG(Score) AS平均成绩FROMSC AS T,CourseAS C ,Teacher AS ZwhereT.Cid=C.Cid andC.Tid=Z.TidGROUPBY C.CidORDERBY AVG(Score)DESC22、查询如下课程成绩第3名到第6名的学生成绩单：企业管理1，马克思2，UML 3，数据库4学生ID,学生企业管理,马克思,UML,数据库，平均成绩SELECT DISTINCT top 3SC.Sid As 学生*Student.Sname AS 学生，T1.scoreAS企业管理,T2.scoreAS马克思,T3.scoreASUML,T4.scoreAS数据库,ISNULL(T1.score,0) + ISNULL(T2.score,0)ISNULL(T3.score,0)ISNULL(T4.score,0) as总分FROM Student,SC LEFTJOIN SC AS T1ON SC.SidTl.Sid ANDT1.Cid = 1LEFT JOINSC AS T2ON SC.SidT2.Sid ANDT2.Cid = 2LEFT JOINSC AS T3ON SC.SidT3.Sid ANDT3.Cid = 3LEFT JOINSC AS T4ON SC.SidT4.Sid ANDT4.Cid = 4WHERE student.Sid=SC.Sid andISNULL(T1.score,0) + ISNULL(T2.score,0)ISNULL(T3.score,0)ISNULL(T4.score,0)NOT IN(SELECTDISTINCTTOP 15 WITH TIESISNULL(T1.score,0) + ISNULL(T2.score,0)ISNULL(T3.score,0)ISNULL(T4.score,0)FROMscLEFT JOIN sc AS T1ON sc.Sid = Tl.Sid ANDT1.Cid = k1LEFT JOIN sc AS T2ON sc.Sid = T2.Sid ANDT2.Cid = k2LEFT JOIN sc AS T3ON sc.Sid = T3.Sid ANDT3.Cid = k3LEFT JOIN sc AS T4ON sc.Sid = T4.Sid ANDT4.Cid = k4ORDER BY ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) DESC);23、统计列印各科成绩，各分数段人数:课程ID，课程名称，1-85,85-70,70-60, 60SELECT SC.Cid as课程 ID, ame as课程名称,SUM(CASEWHENscoreBETWEEN85AND1 THEN1 ELSE0 END)AS1 - 85,SUM(CASEWHENscoreBETWEEN70AND85THENELSE0 END)AS85 - 70,SUM(CASEWHENscoreBETWEEN60AND70THENELSE0 END)AS70 - 60,SUM(CASEWHENscore1;31、1981年出生的学生注：Student表中Sage列的类型是datetime)select Sname, CONVERT(char (11),DATEPART(year,Sage) as agefrom studentwhere CONVERT(char(11),DATEPART(year,Sage)=1981;32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩一样时，按课程号降序排列Select Cid,Avg(score) from SC group by Cid order by Avg(score),Cid DESC ;33、查询平均成绩大于85的所有学生的*、和平均成绩select Sname,SC.Sid , avg(score)from Student,SCwhere Student.Sid=SC.Sid group by SC.Sid,Sname having avg(score)85;34、查询课程名称为数据库，且分数低于60的学生和分数Select Sname,isnull(score,0)from Student,SC,CoursewhereSC.Sid=Student.Sid and SC.Cid=Course.Cid and Course.ame=数据库and score =70 AND SC.Sid=student.Sid;37、查询不及格的课程，并按课程号从大到小排列select cid from sc where scor e 80 and Cid=339、求选了课程的学生人数select count(*) from sc;40、查询选修叶平 教师所授课程的学生中，成绩最高的学生及其成绩select Student.Sname,scorefrom Student,SC,Course C,Teacherwhere Student.Sid=SC.Sid and SC.Cid=C.Cid and C.Tid=Teacher.Tid and Teacher.Tname= 叶平and SC.score=(select ma*(score)from SC where Cid=C.Cid );41、查询各个课程及相应的选修人数select count(*) from sc group by Cid;42、查询不同课程成绩一样的学生的*、课程号、学生成绩A.Score=B.Score and A.Cid B.Cid ;分数select distinct A.Sid,B.score from SC A , SC B where43、查询每门功成绩最好的前两名SELECT t1.Sid as 学生 ID,t1.Cid as 课程 ID,Score asFROM SC t1WHERE score IN (SELECT TOP 2 scoreFROM SCWHERE t1.Cid= CidORDERBY score DESC)ORDERBY t1.Cid;44、统计每门课程的学生选修人数超过10人的课程才统计。要求输出课程号和选修人数，查询结果按人数降序排列，查询结果按人数降序排列，假设人数一样，按课程号升序排列select Cid as 课程号，count(*) as 人数from scgroup by Cidorder by count(*) desc,cid45、检索至少选修两门课程的学生*select Sidfrom scgroup by sidhaving count(*) = 246、查询全部学生都选修的课程的课程号和课程名select Cid,amefrom Coursewhere Cid in (selectcid from sc group by cid)47、查询没学过叶平教师讲授的任一门课程的学生select Sname from Student where Sid not in (selectSid from Course,Teacher,SC whereeacher.Tid and SC.Cid=course.Cid and Tname= 叶平);48、查询两门以上不及格课程的同学的*及其平均成绩Course.Tid=Tgroup by Siselect Sid,avg(isnull(score,0) from SC where Sid in (selectSid from SC where score 2)group by Sid;49、检索4课程分数小于60，按分数降序排列的同学*select Sid from SCwhere Cid=4and score 60 order by score desc;50、删除2同学的1 课程的成绩delete from Sc where Sid=1and Cid=1;