Oracle数据库练习题

12、取得每个薪水等级有多少员工

第一步：取得每个员工的薪水等级

select empno, ename, grade from emp e join salgrade g on e.sal between g.losal and g.hisal

第二步：根据等级进行分组，然后取得数量

select grade, count(*) from (select empno, ename, grade from emp e join salgrade g on e.sal bet ween g.losal and g.hisal) group by grade

13、面试题

有3个表S，C，SC

S(SNO，SNAME)代表(学号，姓名)

C(CNO，CNAME，CTEACHER)代表(课号，课名，教师)

SC(SNO，CNO，SCGRADE)代表(学号，课号，成绩)

问题：

第一题：找出没选过“黎明”老师的所有学生姓名。

第二题：列出2门以上(含2门)不及格学生姓名及平均成绩。

第三题：即学过1号课程又学过2号课所有学生的姓名。

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

CREATE TABLE SC
(
  SNO      VARCHAR2(200 BYTE),
  CNO      VARCHAR2(200 BYTE),
  SCGRADE  VARCHAR2(200 BYTE)
);

CREATE TABLE S
(
  SNO    VARCHAR2(200 BYTE),
  SNAME  VARCHAR2(200 BYTE)
);

CREATE TABLE C
(
  CNO       VARCHAR2(200 BYTE),
  CNAME     VARCHAR2(200 BYTE),
  CTEACHER  VARCHAR2(200 BYTE)
);

INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '1', '语文', '张'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '2', '政治', '王'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '3', '英语', '李'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '4', '数学', '赵'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '5', '物理', '黎明'); 
commit;
 
INSERT INTO S ( SNO, SNAME ) VALUES ( '1', '学生1'); 
INSERT INTO S ( SNO, SNAME ) VALUES ( '2', '学生2'); 
INSERT INTO S ( SNO, SNAME ) VALUES ( '3', '学生3'); 
INSERT INTO S ( SNO, SNAME ) VALUES ( '4', '学生4'); 
commit;
 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '1', '40'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '2', '30'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '3', '20'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '4', '80'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '1', '5', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '1', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '2', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '3', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '4', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '2', '5', '40'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '1', '60'); 
INSERT INTO SC ( SNO, CNO, SCGRADE ) VALUES ( '3', '3', '80'); 
commit;

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

第一步：求出黎明老师教授的课程编号

Select cno from c where CTEACHER =  '黎明'

第二步：查询选择黎明老师的课程的学生编号

select sno from sc where cno in (Select cno from c where CTEACHER =  '黎明')

第三步，得到结果

select sname from s where sno not in ( select sno from sc where cno in (Select cno from c where CTEACHER =  '黎明'))

问题二：列出2门以上(含2门)不及格学生姓名及平均成绩。

第一步：获取2门以上(含2门)不及格的学生编号

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

第二步：获取每个同学的平均成绩

Select sno, avg(scgrade) avgGrade from sc group by sno

第三步： 获取结果

Select sname, avgGrade from s join (select sno from sc where sc.SCGRADE < 60 group by sno having count(*) >= 2) n on s.sno = n.sno join (Select sno, avg(scgrade) avgGrade from sc group by sno) g on n.sno = g.sno

问题三：即学过1号课程又学过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' ))

14、列出所有员工及直接上级的姓名

(99语法)Select e.ename, nvl(m.ename, '没有上级') as mname from emp e left join emp m on e.mgr = m.empno 
(92语法) Select e.ename, nvl(m.ename, '没有上级') as mname from emp e, emp m where e.mgr = m.empno(+)

15、列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称

select e.empno, e.ename, d.dname from emp e join emp m on e.mgr = m.empno and e.hiredate < m.hiredate join dept d on e.deptno = d.deptno

16、列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门

Select d.dname, e.* from emp e right join dept d on e.deptno = d.deptno

17、列出至少有一个员工的所有部门

Select dname, count(*) from emp e join dept d on e.deptno = d.deptno group by dname

Select dname, count(e.empno) from emp e right join dept d on e.deptno = d.deptno group by dname having count(e.empno) > 0

 18、列出薪金比"SMITH"多的所有员工信息

select * from emp where sal > (select sal from emp where ename = 'SMITH')

19、列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数

第一步：获取工作岗位是CLERK的员工信息

Select deptno, ename from emp where job = 'CLERK '

第二步：获取部门名称

select ename ,dname from dept d join (Select deptno, ename from emp where job ='CLERK') t on t.deptno = d.deptno

第三步：取得每个部门的人数

Select dname, count(*) from emp e join dept d on e.deptno = d.deptno group by dname

第四步：获取结果

Select ename, d.dname, cc from (select ename ,dname from dept d join (Select deptno, ename from emp where job ='CLERK') t on t.deptno = d.deptno) d join (Select dname, count(*) cc from emp e join dept d on e.deptno = d.deptno group by dname) c on d.dname = c.dname

20、列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数

第一步：获取最低薪水大于1500的工作

Select job from emp group by job having min(sal) > 1500

第二步：取得每种工作岗位的员工数量

Select job ,count(*) from emp group by job

第三步：获取结果

Select j.job, cc from (Select job from emp group by job having min(sal) > 1500) j join (Select job ,count(*)  cc from emp group by job) c on j.job = c.job

21、列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部门编号

22、列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级

第一步 获取公司的平均薪水

Select avg(sal) from emp

第二步 获取大于平均薪水的员工

Select * from emp where sal > (Select avg(sal) from emp)

第三步 和部门表进行关联

Select ename, dname from (Select * from emp where sal > (Select avg(sal) from emp)) t join dept d on t.deptno = d.deptno 

第四步 和经理表关联

Select t.ename, d.dname, m.ename as mname  from (Select * from emp where sal > (Select avg(sal) from emp)) t join dept d on t.deptno = d.deptno left join emp m on t.mgr = m.empno

第五步 和等级关联

Select t.ename 姓名, d.dname 部门名称, nvl(m.ename, '无') 上级经理, grade 工资等级  from (Select * from emp where sal > (Select avg(sal) from emp)) t join dept d on t.deptno = d.deptno left join emp m on t.mgr = m.empno join salgrade g on t.sal between g.losal and g.hisal