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