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)