- 相关推荐
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以上的记录。
3.sql面试题一条语句查询每个部门共有多少人
前提:a 部门表 b 员工表
a表字段(
id --部门编号
departmentName-部门名称
)
b表字段(
id--部门编号
employee- 员工名称
)
问题:如何一条sql语句查询出每个部门共有多少人
select count(b.id)as employeecount,a.departmentName from a left join b on a.id=b.id group by b.id,a.departmentName
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，SCS(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 sjoin第一步的结果on s.sno = t.snojoin第二步的结果on s.sno = t1.sno即:select s.sname ,avg_grade from sjoin(select sno, count(*) from sc where scgrade < 60 group by sno having count(*) >= 2)ton s.sno = t.snojoin(select sno, avg(scgrade) as avg_grade from sc group by sno )t1on 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) ton (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 wheresno in (select sno from sc where cno = 1)andsno in (select sno from sc where cno = 2)
company 公司名(companyname) 编号(id)
LS 6
DG 9
GR 19
employeehired
公司(id) 人数(number) 财季(fiscalquarter)
6 2 1
9 2 4
19 4 1
1.找出表中的主键: company(id) employeehired (id)+(fiscalquarter)
2.找出表之间关系: 外键关系， employeehired (id) 参考 company (id)
3.求第四财季招聘过员工的公司名称:
select companyname from company c join employeehired e
on (c.id = e.id)
where fiscalquarter = 4;
4.求从1到3财季从没有招聘过员工的公司名称 //同理1到4财季
select companyname from company
where id not in
(select distinct id from employeehired
where fiscalquarter not in(1,2,3)
);
5.求从1到4财季之间招聘过员工的公司名称和他们各自招聘的员工总数
select companyname , sum_numhired from company c join
(
select sum(numhired) sum_numhired from employeehired group by id
) t
on (c.sum_numhired = t.sum_numhired);
--求部门中哪些人的薪水最高
select ename, sal from emp
join (select max(sal) max_sal, deptno from emp group by deptno) t
on (emp.sal = t.max_sal and emp.deptno = t.deptno);
--求每个部门的平均薪水的等级 //多表连接， 子查询
select deptno, avg_sal, grade from //从下面表中取，下表必须有字段
(select deptno, avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal);
--求每个部门的平均的薪水等级
select deptno, avg(grade) from
(select deptno, ename, grade from emp join salgrade s
on (emp.sal between s.losal and s.hisal)) t
group by deptno;
--求雇员中有哪些人是经理人
select ename from emp
where empno in (select distinct mgr from emp );
--不准用组函数，求薪水的最高值 (面试题) //很变态，不公平就不公平
自连接：左边表的数据小于右边表的 最大的连接不上 //说起来很简单
select distinct sal from emp
where sal not in (select distinct e1.sal from emp e1 join emp e2
on (e1.sal < e2.sal));
--求平均薪水最高的部门的部门编号
select deptno, avg_sal from
(select deptno, avg(sal) avg_sal from emp group by deptno)
where avg_sal =
(select max(avg_sal) from
(select avg(sal) avg_sal, deptno from emp group by deptno)
);
///////////另解../////////////////////////////
select deptno, avg_sal from
(select deptno, avg(sal) avg_sal from emp group by deptno)
where avg_sal =
(select max(avg(sal)) from emp group by deptno);
////////组函数嵌套，不过只能套2层，因为多行输入，单行输出//////////
--求平均薪水最高的部门的部门名称
select dname from dept where deptno =
(
select deptno from
(select deptno, avg(sal) avg_sal from emp group by deptno)
where avg_sal =
(select max(avg_sal) from
(select avg(sal) avg_sal, deptno from emp group by deptno)
)
);
--求平均薪水的等级最低的部门的部门名称 //太复杂了 PL SQL
//从里到外
1.平均薪水：select deptno, avg(sal) from emp group by deptno;
2.平均薪水的等级：
select deptno, grade, avg_sal from
(select deptno, avg(sal) avg_sal fr
【Oracle 之 SQL面试题】相关文章：
SQL面试题07-12
SQL Server数据库实训总结11-18
c面试题08-04
华为面试题07-11
「MySQL」经典面试题07-11
面试题与技巧07-12
采购面试题07-11
面试题集锦07-11
Java面试题07-12
数据分析人员必须掌握的一些sql语句07-10