《经典SQL面试题》由会员分享，可在线阅读，更多相关《经典SQL面试题（15页珍藏版）》请在金锄头文库上搜索。
1、经典SQL面试题资料仅供参考表Student(Sid,Sname,Sage,Ssex) 学生表 CREATE TABLE student ( sid varchar(10) NOT NULL, sName varchar(20) DEFAULT NULL, sAge datetime DEFAULT 1980-10-12 23:12:36, sSex varchar(10) DEFAULT NULL, PRIMARY KEY (sid) ENGINE=InnoDB DEFAULT CHARSET=utf8;Course(Cid,Cname,Tid) 课程表 CREATE TABLE cours
2、e ( cid varchar(10) NOT NULL, cName 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) DEFAULT NULL, cid varchar(10) DEFAULT NULL, score int(10) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=u
3、tf8;Teacher(Tid,Tname) 教师表 CREATE TABLE taacher ( tid int(10) DEFAULT NULL, tName varchar(10) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;数据：（MySQL）insert into taacher(tid,tName) values (1,李老师),(2,何以琛),(3,叶平);insert into student(sid,sName,sAge,sSex) values (1001,张三丰,1980-10-12 23:12:36,男),(1002
4、,张无极,1995-10-12 23:12:36,男),(1003,李奎,1992-10-12 23:12:36,女),(1004,李元宝,1980-10-12 23:12:36,女),(1005,李世明,1981-10-12 23:12:36,男),(1006,赵六,1986-10-12 23:12:36,男),(1007,田七,1981-10-12 23:12:36,女);insert into sc(sid,cid,score) values (1,001,80),(1,002,60),(1,003,75),(2,001,85),(2,002,70),(3,004,100),(3,001
5、,90),(3,002,55),(4,002,65),(4,003,60);insert into course(cid,cName,tid) values (001,企业管理,3),(002,马克思,3),(003,UML,2),(004,数据库,1),(005,英语,1);问题： 1、查询“001”课程比“002”课程成绩高的所有学生的学号; select a.Sid from (select sid,score from SC where Cid=001) a,(select sid,score from SC where Cid=002) b where a.scoreb.score
6、and a.sid=b.sid; 2、查询平均成绩大于60分的同学的学号和平均成绩; select Sid,avg(score) from sc group by Sid having avg(score) 60; 3、查询所有同学的学号、姓名、选课数、总成绩; select Student.Sid,Student.Sname,count(SC.Cid),sum(score) from Student left Outer join SC on Student.Sid=SC.Sid group by Student.Sid,Sname 4、查询姓“李”的老师的个数; select count(
7、distinct(Tname) from Teacher where Tname like 李%; 5、查询没学过“叶平”老师课的同学的学号、姓名; select Student.Sid,Student.Sname from Student where 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、查询学过“001”而且也学过编号“002”课程的同学的学号、姓名; sel
8、ect Student.Sid,Student.Sname from Student,SC where Student.Sid=SC.Sid and SC.Cid=001and exists( Select * from SC as SC_2 where SC_2.Sid=SC.Sid and SC_2.Cid=002); 7、查询学过“叶平”老师所教的所有课的同学的学号、姓名; select Sid,Sname from Student where Sid in (select Sid from SC ,Course ,Teacher where SC.Cid=Course.Cid and
9、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、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名; Select Sid,Sname from (select Student.Sid,Student.Sname,score ,(select score from SC SC_2 where S
10、C_2.Sid=Student.Sid and SC_2.Cid=002) score2 from Student,SC where Student.Sid=SC.Sid and Cid=001) S_2 where score2 60); 10、查询没有学全所有课的同学的学号、姓名; select Student.Sid,Student.Sname from Student,SC where Student.Sid=SC.Sid group by Student.Sid,Student.Sname having count(Cid) (select count(Cid) from Cours
11、e); 11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名; select Sid,Sname from Student,SC where Student.Sid=SC.Sid and Cid in select Cid from SC where Sid=1001; 12、查询至少学过学号为“001”同学所有一门课的其它同学学号和姓名; select distinct SC.Sid,Sname from Student,SC where Student.Sid=SC.Sid and Cid in (select Cid from SC where Sid=001);
12、13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩; update SC set score=(select avg(SC_2.score) from SC SC_2 where SC_2.Cid=SC.Cid ) from Course,Teacher where Course.Cid=SC.Cid and Course.Tid=Teacher.Tid and Teacher.Tname=叶平); 14、查询和“1002”号的同学学习的课程完全相同的其它同学学号和姓名; select Sid from SC where Cid in (select Cid from SC
13、where Sid=1002) group by Sid having count(*)=(select count(*) from SC where Sid=1002); 15、删除学习“叶平”老师课的SC表记录; Delect SC from course ,Teacher where Course.Cid=SC.Cid and Course.Tid= Teacher.Tid and Tname=叶平; 16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、2号课的平均成绩; Insert SC select Sid,002,(Select avg(
14、score) from SC where Cid=002) from Student where Sid not in (Select Sid from SC where Cid=002); 17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示： 学生ID,数据库,企业管理,英语,有效课程数,有效平均分 SELECT Sid as 学生ID ,(SELECT score FROM SC WHERE SC.Sid=t.Sid AND Cid=004) AS 数据库 ,(SELECT score FROM SC WHERE SC.Sid=t.Sid AND Cid=001) AS 企业管理 ,(SELECT score FROM SC WHERE SC.Sid=t.Sid AND Cid=006) AS 英语 ,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩 FROM SC AS t GROUP BY Sid ORDER BY avg(t.score) 18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分 SELECT L.Cid As 课程ID,L.score AS 最高分,R.sc
