面试题（四）、数据库部分

五、数据库部分 
套题一： 
数据表： 
dept:    
deptno(primary key), dname, loc 
emp:    
empno(primary key), ename, job, mgr(references emp(empno)), sal, 
deptno(references dept(deptno)) 
 
 
 
 
 
 
 
 
DEPT 
deptno(primary key) 
dname 
loc 

EMP 
empno(primary key) 
ename 
job 
mgr(references emp(empno)) 
sal 
deptno(references dept(deptno))  
1  列出 emp表中各部门的部门号，最高工资，最低工资 
 
select max(sal) as  最高工资,min(sal) as  最低工资,deptno from emp group by deptno; 
2  列出 emp 表中各部门 job  含’REP’的员工的部门号，最低工资，最高
工资 
 
select max(sal) as  最高工资,min(sal) as  最低工资,deptno as  部门号  from emp where job  like  '%REP%' group by 
deptno; 
3  对于emp中最低工资小于7000的部门中job为'SA_REP'的员工的部门
号，最低工资，最高工资 
 
select max(sal) as  最高工资,min(sal) as  最低工资,deptno as  部门号  from emp  b 
where job='SA_REP' and 7000>(select min(sal) from emp    a where a.deptno=b.deptno) group by 
b.deptno 
 
4写出对上题的另一解决方法 
（请补充） 
 
select deptno,min(sal),max(sal) 
from emp 
where job = 'SA_REP' and deptno in ( 
select deptno 
from emp 
--group by deptno 
having min(sal) < 7000 
) 
group by deptno  
5 根据部门号由高而低，工资由低而高列出每个员工的姓名，部门号，
工资 
 
select deptno as  部门号,ename as  姓名,sal as  工资  from emp order by deptno desc,sal asc 
6  列出'Abel'所在部门中每个员工的姓名与部门号 
 
select ename,deptno from emp where deptno = (select deptno from emp where ename = 'Abel') 
 
(法二) 
select ename,deptno 
from emp e1 
where exists ( 
             select 'x' 
             from emp e2 
             where e1.deptno = e2.deptno 
             and e2.ename = 'Abel'    
               ) 
7  列出每个员工的姓名，工作，部门号，部门名 
 
select ename,job,emp.deptno,dept.dname from emp,dept where emp.deptno=dept.deptno 
8  列出 emp中工作为'SH_CLERK'的员工的姓名，工作，部门号，部门名 
 
select ename,job,dept.deptno,dname from emp,dept where dept.deptno=emp.deptno and 
job='SH_CLERK'  
9  对于 emp中有管理者的员工，列出姓名，管理者姓名（管理者外键为
mgr） 
 
select a.ename as  姓名,b.ename as  管理者  from emp    a,emp    b where a.mgr is not null and 
a.mgr=b.empno 
10  对于 dept表中，列出所有部门名，部门号，同时列出各部门工作为
'SH_CLERK'的员工名与工作 
 
select dname as  部门名,dept.deptno as  部门号,ename as  员工名,job as  工作  from dept,emp   
where dept.deptno = emp.deptno(+) and job = 'SH_CLERK' 
11  对于工资高于本部门平均水平的员工，列出部门号，姓名，工资，
按部门号排序 
 
select a.deptno as  部门号,a.ename as  姓名,a.sal as  工资  from emp    a 
where a.sal>(select avg(sal) from emp    b where a.deptno=b.deptno) order by a.deptno 
 
(法二)select e.deptno,ename,sal 
from emp e,(select deptno,avg(sal) avg_sal from emp group by deptno) b 
where e.sal > b.avg_sal and e.deptno = b.deptno 
 
12  对于 emp，列出各个部门中工资高于本部门平均水平的员工数和部
门号，按部门号排序 
 
select count(a.sal) as  员工数,a.deptno    部门号  from emp    a 
where a.sal>(select avg(sal) from emp    b where a.deptno=b.deptno) group by a.deptno order by 
a.deptno  
13.  对于 emp中工资高于本部门平均水平，人数多于 1人的，列出部门
号，高于部门平均工资的人数，按部门号排序 
 
select * 
from( 
select deptno,count(*) count_num 
from emp e 
where sal > ( 
      select avg(sal) 
      from emp e1 
      where e.deptno = e1.deptno   
) 
group by deptno 
) e1 
where e1.count_num > 1 
order by e1.deptno 
 
14  对于 emp中工资高于本部门平均水平，且其人数多于 3人的，列出
部门号，部门人数，按部门号排序 
 
select count(a.empno) as  员工数,a.deptno as  部门号,avg(sal) as  平均工资  from emp a 
where (select count(c.empno) from emp   c where c.deptno=a.deptno and c.sal>(select avg(sal) from 
emp b where c.deptno=b.deptno))>3 
group by a.deptno order by a.deptno 
 
(法二) 
select m.deptno,count(ee1.empno) 
from( 
select e1.deptno,count(empno) count_num 
from emp e1 
where e1.sal >  
(select avg(sal) from emp e2 where e1.deptno = e2.deptno) 
group by e1.deptno 
) m,emp ee1  
where m.count_num > 3 and m.deptno = ee1.deptno 
group by m.deptno 
15  对于 emp中低于自己工资至少5 人的员工，列出其部门号，姓名，
工资，以及工资少于自己的人数 
 
select a.deptno,a.ename,a.sal,(select count(b.ename) from emp as b where b.sal<a.sal) as  人数 
from emp as a 
where (select count(b.ename) from emp as b where b.sal<a.sal)>5 
 
套题二： 
Student(SID, Sname, Sage, Ssex,Sbirth)  学生表   
  Course(CID, Cname, TID)  课程表   
  SC(SID, CID, score)  成绩表   
  Teacher(TID, Tname)  教师表 
 
 
 
 
 
 
 
 
 
 
 
 
 
问题：   
1、查询 201课程比 202课程成绩高的所有学生的学号；   
  select a.SID 
  from (select Sid,score from SC where CID=201) a,(select Sid,score   
  from SC where CID=202) b   
<Course 课程表> 
CID 
Cname 
TID 
<SC 成绩表> 
SID 
CID 
score 
<Student学生表> 
SID 
Sname 
Sage 
Ssex 
Sbirth 
<Teacher教师表> 
TID 
Tname  
  where a.score>b.score 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(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='叶平'); 
   
 
select student.sid,student.sname 
from student 
where sid not in ( 
                  select sid 
                  from sc 
                  where cid in ( 
                  select cid 
                  from course 
                  where tid = (select tid  
                  from teacher 
                  where tname = '叶平') 
                  )  
                 ) 
6、查询学过“201”并且也学过编号“202”课程的同学的学号、姓名；   
  select Student.SID,Student.Sname   
  from Student,SC   
  where Student.SID=SC.SID and SC.CID='001'and 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 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、查询课程编号“202”的成绩比课程编号“201”课程低的所有同学的学
号、姓名；   
Select SID,Sname from (select Student.SID,Student.Sname,score  ,(select score from SC SC_2 where 
SC_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 <score;   
   
9、查询所有课程成绩小于 60分的同学的学号、姓名；   
（取反操作处理） 
  select SID,Sname   
  from Student   
  where SID not in (select Student.SID from Student,SC where S.SID=SC.SID and score>60);   
    
10、查询没有学全所有课的同学的学号、姓名；   
（count(CID)得到课程的数目） 
     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 
Course);   
   
11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和
姓名；   
     select SID,Sname from Student,SC where Student.SID=SC.SID and CID in (select CID from SC where SID='1001');   
   
12、查询至少学过学号为“1001”同学所有一门课的其他同学学号和姓
名；   
     select distinct SC.SID,Sname   
     from Student,SC   
where Student.SID=SC.SID and CID in (select CID from SC where SID='001') 
and Student.SID <> 1001;   
   
13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；   
update SC 
set score=(select avg(SC_2.score) 
        from SC SC_2 
        where SC_2.CID=SC.CID ) 
where cid = ( 
            select 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 where SID='1002')   
     group by SID having count(*)=(select count(*) from SC where SID='1002');   
   
 
15、删除学习“叶平”老师课的 SC表记录；   
     Delete from sc 
where cid = ( 
              select cid 
              from course ,Teacher 
           where Course.CID=SC.CID and Course.TID= Teacher.TID and Tname='
叶平' 
            )   
   
16、向 SC 表中插入一些记录，这些记录要求符合以下条件：没有上过
编号“003”课程的同学学号、002号课的平均成绩；   
Insert    into SC   
as select SID,'002',(Select avg(score)   
             from SC where CID='002')   
from Student   
where SID not in (Select SID from SC where CID='002');   
   
17、按学生平均成绩从高到低显示所有学生的“数据库”、 “企业管理”、 “英
语”三门的课程成绩，按如下形式显示：学生 ID,数据库,企业管理,英语,
有效课程数,有效平均分   
（默认数据库是 004，企业管理是 001，英语是 006） 
     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 courseID,L.score  最高分,L.sid  学号,R.score  最低分,R.sid  学号   
FROM SC L ,SC R   
WHERE L.CID = R.CID and   
       L.score = (SELECT MAX(IL.score)   
                  FROM SC IL,Student IM   
                  WHERE L.CID = IL.CID and IM.SID=IL.SID   
                  GROUP BY IL.CID)   
AND   
       R.Score = (SELECT MIN(IR.score)   
                  FROM SC IR   
                  WHERE R.CID = IR.CID   
                  GROUP BY IR.CID)           
19、查询课程号，课程名称，平均成绩和及格率，并按各科平均成绩从
低到高和及格率的百分数从高到低顺序   
     SELECT t.CID AS  课程号,max(course.Cname)AS  课程名,isnull(AVG(score),0) AS  平均成绩   
         ,100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS  及格百分数   
     FROM SC T,Course   
     where t.CID=course.CID   
     GROUP BY t.CID   
     ORDER BY 100 * SUM(CASE WHEN isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC   
   
20、查询如下课程平均成绩和及格率的百分数(用"1行"显示):  企业管理
（001），马克思（002），OO&UML  （003），数据库（004）   
     SELECT SUM(CASE WHEN CID ='001' THEN score ELSE 0 END)/SUM(CASE CID WHEN '001' THEN 1 ELSE 0 END) 
AS  企业管理平均分    
         ,100 * SUM(CASE WHEN CID = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN CID = '001' 
THEN 1 ELSE 0 END) AS  企业管理及格百分数   
         ,SUM(CASE WHEN CID = '002' THEN score ELSE 0 END)/SUM(CASE CID WHEN '002' THEN 1 ELSE 0 END) AS 
马克思平均分   
         ,100 * SUM(CASE WHEN CID = '002' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN CID = '002' 
THEN 1 ELSE 0 END) AS  马克思及格百分数   
         ,SUM(CASE WHEN CID = '003' THEN score ELSE 0 END)/SUM(CASE CID WHEN '003' THEN 1 ELSE 0 END) AS 
UML 平均分   
          ,100 * SUM(CASE WHEN CID = '003' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN CID = '003' 
THEN 1 ELSE 0 END) AS UML 及格百分数   
         ,SUM(CASE WHEN CID = '004' THEN score ELSE 0 END)/SUM(CASE CID WHEN '004' THEN 1 ELSE 0 END) AS 
数据库平均分   
         ,100 * SUM(CASE WHEN CID = '004' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN CID = '004' 
THEN 1 ELSE 0 END) AS  数据库及格百分数   
  FROM SC 
   
21、查询不同老师所教不同课程平均分从高到低显示   
  SELECT max(Z.TID) AS  教师 ID,MAX(Z.Tname) AS  教师姓名,C.CID AS  课程ＩＤ,MAX(C.Cname) AS  课程名
称,AVG(Score) AS  平均成绩   
    FROM SC AS T,Course AS C ,Teacher AS Z   
    where T.CID=C.CID and C.TID=Z.TID   
  GROUP BY C.CID   
  ORDER BY AVG(Score) DESC   
   
22、查询如下课程成绩第  3  名到第  6  名的学生成绩单：企业管理
（001），马克思（002），UML  （003），数据库（004）   
     [学生 ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩   
     SELECT DISTINCT top 3   
       SC.SID As  学生学号,   
         Student.Sname AS  学生姓名  ,   
       T1.score AS  企业管理,   
       T2.score AS  马克思,   
       T3.score AS UML,   
       T4.score AS  数据库,   
       ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) as  总分   
       FROM Student,SC LEFT JOIN SC AS T1   
                       ON SC.SID = T1.SID AND T1.CID = '001'   
             LEFT JOIN SC AS T2    
                       ON SC.SID = T2.SID AND T2.CID = '002'   
             LEFT JOIN SC AS T3   
                       ON SC.SID = T3.SID AND T3.CID = '003'   
             LEFT JOIN SC AS T4   
                       ON SC.SID = T4.SID AND T4.CID = '004'   
       WHERE student.SID=SC.SID and   
       ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)   
        NOT IN   
       (SELECT   
             DISTINCT   
             TOP 15 WITH TIES   
             ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)   
       FROM sc   
             LEFT JOIN sc AS T1   
                       ON sc.SID = T1.SID AND T1.CID = 'k1'   
             LEFT JOIN sc AS T2   
                       ON sc.SID = T2.SID AND T2.CID = 'k2'   
             LEFT JOIN sc AS T3   
                       ON sc.SID = T3.SID AND T3.CID = 'k3'   
             LEFT JOIN sc AS T4   
                        ON sc.SID = T4.SID AND T4.CID = 'k4'   
       ORDER BY ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) DESC); 
      
23 、统计各科成绩 , 各分数段人数 : 课程 ID, 课程名
称,[100-85],[85-70],[70-60],[ <60]   
     SELECT SC.CID as  课程 ID, Cname as  课程名称   
          ,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 - 85]   
         ,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 - 70]   
         ,SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 - 60]   
         ,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS [60 -]   
     FROM SC,Course   
     where SC.CID=Course.CID   
     GROUP BY SC.CID,Cname; 
   
24、查询学生平均成绩及其名次   
       SELECT 1+(SELECT COUNT( distinct  平均成绩)   
               FROM (SELECT SID,AVG(score) AS  平均成绩   
                        FROM SC    
                   GROUP BY SID   
                   ) AS T1   
             WHERE  平均成绩> T2.平均成绩) as  名次,   
       SID as  学生学号,平均成绩   
     FROM (SELECT SID,AVG(score)  平均成绩   
             FROM SC   
         GROUP BY SID   
         ) AS T2   
     ORDER BY  平均成绩desc;   
 
25、查询各科成绩前三名的记录:(不考虑成绩并列情况)   
       SELECT t1.SID as  学生ID,t1.CID as  课程ID,Score as  分数   
       FROM SC t1   
       WHERE score IN (SELECT TOP 3 score   
               FROM SC   
               WHERE t1.CID= CID   
             ORDER BY score DESC   
               )   
       ORDER BY t1.CID;   
      
26、查询每门课程被选修的学生数   
  select Cid,count(SID) from sc group by CID;   
   
27、查询出只选修了一门课程的全部学生的学号和姓名   
  select SC.SID,Student.Sname,count(CID) AS  选课数   
  from SC ,Student   
  where SC.SID=Student.SID group by SC.SID ,Student.Sname having count(CID)=1;   
   
28、查询男生、女生人数   
     Select count(Ssex) as  男生人数  from Student group by Ssex having Ssex='男';   
     Select count(Ssex) as  女生人数  from Student group by Ssex having Ssex='女'；   
    
29、查询姓“张”的学生名单   
     SELECT Sname FROM Student WHERE Sname like '张%';   
   
30、查询同名学生名单，并统计同名人数   
  select Sname,count(*) from Student group by Sname having count(*)>1;;   
   
31、 1981年出生的学生名单(注： Student表中 Sage列的类型是 datetime)   
     select Sname, CONVERT(char (11),DATEPART(year,Sage)) as age   
     from student   
     where 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,SC   
     where Student.SID=SC.SID group by SC.SID,Sname having    avg(score)>85;   
   
34、查询课程名称为“数据库”，且分数低于 60的学生姓名和分数   
     Select Sname,isnull(score,0)   
     from Student,SC,Course   
     where SC.SID=Student.SID and SC.CID=Course.CID and Course.Cname='数据库'and score <60;   
    
35、查询所有学生的选课情况； （学号，姓名，课程编号，课程名字）  
     SELECT SC.SID,SC.CID,Sname,Cname   
     FROM SC,Student,Course   
     where SC.SID=Student.SID and SC.CID=Course.CID ;   
   
36、查询任何一门课程成绩在 70 分以上的学号、姓名、课程编号和分
数；   
     SELECT distinct student.SID,student.Sname,SC.CID,SC.score   
     FROM student,Sc   
     WHERE SC.score>=70 AND SC.SID=student.SID;   
   
37、查询学生学号，以及其不及格的课程，并按课程号从大到小排列   
     select sid,Cid from sc where score <60 order by CID ;   
   
38、查询课程编号为 003且课程成绩在 80分以上的学生的学号和姓名；    
     select SC.SID,Student.Sname from SC,Student where SC.SID=Student.SID and Score>80 and CID='003'; 
   
39、求选了课程的学生人数   
     select count(*) from sc;   
   
40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其
成绩   
     select Student.Sname,score   
     from Student,SC,CourseC,Teacher   
     where Student.SID=SC.SID and SC.CID=C.CID and C.TID=Teacher .TID and Teacher.Tname=' 叶平 ' and 
SC.score=(select max(score)from SC where CID=C.CID ); 
    
41、查询各个课程及相应的选修人数   
     select count(*) from sc group by CID;   
   
42、查询不同课程成绩相同的学生的学号、课程号、学生成绩   
  select distinct A.SID,B.score from SC A ,SC B where A.Score=B.Score and A.CID <>B.CID ; 
   
43、查询每门功成绩最好的前两名   
     SELECT t1.SID as  学生 ID,t1.CID as  课程 ID,Score as  分数   
       FROM SC t1   
       WHERE score IN (SELECT TOP 2 score   
               FROM SC   
               WHERE t1.CID= CID   
             ORDER BY score DESC   
               )   
        ORDER BY t1.CID;   
      
44、统计每门课程的学生选修人数（超过 10人的课程才统计）。要求输
出课程号和选修人数，查询结果按人数降序排列，查询结果按人数降序
排列，若人数相同，按课程号升序排列   
     select CID as  课程号,count(*) as  人数   
     from sc   
     group by CID   
     order by count(*) desc,Cid   
   
45、检索至少选修两门课程的学生学号   
     select SID   
     from sc   
     group by Sid   
     having count(*) > = 2   
    
46、查询全部学生都选修的课程的课程号和课程名   
     select CID,Cname   
     from Course   
     where CID in (select Cid from sc group by Cid) 
   
47、查询没学过“叶平”老师讲授的任一门课程的学生姓名   
     select Sname from Student where SID not in (select SID from Course,Teacher,SC where Course.TID=Teacher.TID 
and SC.CID=course.CID and Tname='叶平');   
 
48、查询两门以上不及格课程的同学的学号及其平均成绩   
     select SID,avg(isnull(score,0)) from SC where SID in (select SID from SC where score <60 group by SID having 
count(*)>2)group by SID;