Oracle 之 SQL 面试题 录

Oracle 之 SQL　面试题 录

多上网查查   SQL　面试题

1.学号(自动编号) 姓名 性别 年龄­

0001 xw 男 18­

0002 mc 女 16­

0003 ww 男 21­

0004 xw 男 18­

请写出实现如下功能的SQL语句:­

删除除了学号（自动编号）字段以外，其它字段都相同的冗余记录！­

DELETE FROM table1­

WHERE (学号 NOT IN­

(SELECT MAX(学号) AS xh­

FROM TABLE1­

GROUP BY 姓名, 性别, 年龄))

2.数据库有3个表 teacher表 student表 tea_stu关系表 teacher表 teaID name age student表 stuID name age teacher_student表 teaID stuID 要求用一条sql查询出这样的结果: 1.显示的字段要有老师id age 每个老师所带的学生人数 2.只列出老师age为40以下 学生age为12以上的记录。

select a.teaID,a.age count(*)
from teacher a,student b,teacher_student c
where a.teaID=c.teaID
and b.stuID=c.stuID
and a.age>40
and b.age>12
group by a.teaID,a.age;

 

3.sql面试题一条语句查询每个部门共有多少人­

前提:a 部门表 b 员工表 ­

a表字段( ­

id --部门编号 ­

departmentName-部门名称 ­

) ­

b表字段( ­

id--部门编号 ­

employee- 员工名称 ­

) ­

问题:如何一条sql语句查询出每个部门共有多少人­

select a.department,count from
tA a,tB b
where a.id=b.id
group by b.id,a,deparment

 

 

4.有3张表，Student表、SC表和Course表 ­

Student表:学号（Sno）、姓名（Sname）、性别（Ssex）、年龄（Sage）和系名（Sdept） ­

Course表:课程号（Cno）、课程名（Cname）和学分（Ccredit）； ­

SC表：学号（Sno）、课程号（Cno）和成绩（Grade） ­

请使用SQL语句查询学生姓名及其课程总学分 ­

（注：如果课程不及格，那么此课程学分为0）­

方法1：

select Sname,sum(Ccredit) as totalCredit from Student,Course,SC where Grade>=60 and Student.Sno=SC.Sno and Course.Cno=SC.Cno group by Sname 

 

方法2：对xyphoenix的修改 ­

select sname,sum(case when sc.grade<60 then 0 else course.Ccredit end) as totalCredit from Student,sc,course where sc.sno=student.sno and sc.cno=course.cno group by sname 

 

方法3：对napolun180410的修改 ­

select Sname,SUM(case when Grade<60 then 0 else Ccredit end) as totalGrade FROM SC JOIN Student ON(Student.sno = SC.sno) JOIN Course ON(SC.Cno = Course.Cno) GROUP BY Student.Sname; 

 

-------------------------------------------------------------------------

有3个表S，C，SC

S（SNO，SNAME）代表（学号，姓名）

C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）

SC（SNO，CNO，SCGRADE）代表（学号，课号成绩）

问题：

1，找出没选过“黎明”老师的所有学生姓名。

2，列出2门以上（含2门）不及格学生姓名及平均成绩。

3，即学过1号课程又学过2号课所有学生的姓名。

请用标准SQL语言写出答案，方言也行（请说明是使用什么方言）。

-----------------------------------------------------------------------------

答案:

S（SNO，SNAME）代表（学号，姓名）

C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）

SC（SNO，CNO，SCGRADE）代表（学号，课号成绩）

select sno,sname from s;

select cno,cname,cteacher from c;

select sno,cno,scgrade from sc;

 

问题1.找出没选过“黎明”老师的所有学生姓名。

第一步:求黎明老师教的所有课的课号

select distinct cno from c where cteacher='黎明'

 

第二步:选了黎明老师的所有学生的编号

select sno from sc where cno in (

    第一步的结果

)

第三步:没有选黎明老师的所有学生的姓名

select sname from s where sno not in (

    第二步的结果

)

即:

select sname from s where sno not in (

    select sno from sc where cno in (

        select distinct cno from c where cteacher='黎明'

    )

)

 

----------------------------------------------------------------------------

问题2:列出2门以上（含2门）不及格学生姓名及平均成绩。

第一步:2门以上不及格的学生的学号

select sno from sc where scgrade < 60 group by sno having count(*) >= 2

 

第二步:每个学生平均分

select sno, avg(scgrade) as avg_grade from sc group by sno

 

第三步:第一步中得到的学号对应的学生姓名以及平均分

select s.sname ,avg_grade from s

    join

         第一步的结果

         on s.sno = t.sno

    join

        第二步的结果

        on s.sno = t1.sno

即:

select s.sname ,avg_grade from s

    join

         (select sno, count(*) from sc where scgrade < 60 group by sno having count(*) >= 2)t

         on s.sno = t.sno

    join

        (select sno, avg(scgrade) as avg_grade from sc group by sno )t1

        on s.sno = t1.sno

 

错误的写法:

错误在于：求的是所有不及格的课程的平均分，而不是所有课程（包括及格的）的平均分

执行顺序:

    首先会执行Where语句，将不符合选择条件的记录过滤掉，

    然后再将过滤后的数据按照group by子句中的字段进行分组，

    接着使用having子句过滤掉不符合条件的分组，

    然后再将剩下的数据排序显示。

select sname, avg_scgrade from s join

(select sno, avg(scgrade) avg_scgrade from sc where scgrade < 60 group by sno having count(*) >= 2) t

on (s.sno = t.sno);

----------------------------------------------------------------------------

select sno,sname from s;

select cno,cname,cteacher from c;

select sno,cno,scgrade from sc;

 

问题3:即学过1号课程又学过2号课所有学生的姓名。

第一步:学过1号课程的学号

select sno from sc where cno = 1

第二步:学过2号课程的学号

select sno from sc where cno = 2

第三步:即学过1号课程又学过2号课的学号

select sno from sc where cno =1 and sno in (select sno from sc where cno = 2)

第四步:得到姓名

select sname from s where sno in (

       select sno from sc where cno = 1 and sno in (select sno from sc where cno = 2)

)

 

或者:

select sname from s where

       sno in (select sno from sc where cno = 1)

       and

       sno in (select sno from sc where cno = 2)

company    公司名(companyname)    编号(id)