多上网查查 SQL 面试题 1.学号(自动编号) 姓名 性别 年龄- 0001 xw 男 18- 0002 mc 女 16- 0003 ww 男 21- 0004 xw 男 18- 请写出实现如下功能的SQL语句:- 删除除了学号(自动编号)字段以外,其它字段都相同的冗余记录!- 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)
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);
--求部门中哪些人的薪水最高----此处开始使用的是scott账户下的自带表 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 //从里到外 ---先求出每个员工的薪水等级,然后再按照部门求出平均薪水等级 select avg_grade,deptno from (select avg(grade) avg_grade,deptno ( select grade,empno,deptno from emp e join salgrade s on(e.sal between s.losal adn s.hisal) ) group by deptno ) ----完整的---- select empname ,avg_grade dept d join (select deptno,avg(grade) as avg_grade from (select deptno,empno,grade from emp e join salgrade s on(e.sal between s.losal and s.hisal) ) group by deptno )t1 on d.depno=t1.deptno;
1.平均薪水:select deptno, avg(sal) from emp group by deptno; 2.平均薪水的等级:
select deptno, grade, avg_sal 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);
3.平均薪水最低的等级:
select min (grade) from ( select deptno, grade, avg_sal 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) );
4.平均薪水最低的等级的部门:显示部门 2.连接dept表 select dname, t1.deptno, grade, avg_sal from // deptno 未明确定义列 (select deptno, grade, avg_sal 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) ) t1 join dept on (t1.deptno = dept.deptno) where t1.grade = ( select min (grade) from ( select deptno, grade, avg_sal 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) ) ); //有完全重复的地方
:::::::创建视图,视图就是表,子查询:虚表 ,链接:::::::: create view v$_dept_avg_sal_info as select deptno, grade, avg_sal 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);
//视图已创建; /////////不能建表 ,权限不足 conn sys/10023 as sysdba; grant create table, create view to sctt; /////////默认是可以建表的;
select * from v$_dept_avg_sal_info; 5.化简 select dname, t1.deptno, grade, avg_sal from v$_dept_avg_sal_info t1 join dept on (t1.deptno = dept.deptno) where t1.grade = ( select min (grade) from v$_dept_avg_sal_info );
--求部门经理人中平均薪水最低的部门名称 (思考题) --求比普通员工的最高薪水还要高的经理人名称 1.select distinct mgr from emp; //king'mgr is null; 2.select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null); 3.select ename from emp where empno in (select distinct mgr from emp where mgr is not null) and sal > ( select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null) );
--求薪水最高的前5名雇员 --求薪水最高的第6到第10名雇员(重点掌握) --练习: 求最后入职的5名员工 --面试题: 比较效率
select * from emp where deptno = 10 and ename like '%A%'; select * from emp where ename like '%A%' and deptno = 10;
////////数字不对,后面就不用看了 ,先比较数字快;//也许Oracle有优化 //CSDN - 专家门诊 MS-SQL Server :::::::::::::::::::::::::::::::::::回家作业::::::::::::::::::::::::::::::::::::::::::: 一个简单的表TABLE 有100条以上的信息, 其中包括: 产品 颜色 数量 产品1 红色 123 产品1 蓝色 126 产品2 蓝色 103 产品2 红色 NULL 产品2 红色 89 产品1 红色 203 ………………………… 请用SQL语句完成一下问题: 没有主键 1.按产品分类,仅列出各类商品中红色多于蓝色的商品名称及差额数量: 2.按产品分类,将数据按下列方式进行统计显示 产品 红色 蓝色
|
|