Oracle數(shù)據(jù)庫(kù)練習(xí)題

12、取得每個(gè)薪水等級(jí)有多少員工

第一步：取得每個(gè)員工的薪水等級(jí)

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

第二步：根據(jù)等級(jí)進(jìn)行分組，然后取得數(shù)量

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個(gè)表S，C，SC

S(SNO，SNAME)代表(學(xué)號(hào)，姓名)

C(CNO，CNAME，CTEACHER)代表(課號(hào)，課名，教師)

SC(SNO，CNO，SCGRADE)代表(學(xué)號(hào)，課號(hào)，成績(jī))

問(wèn)題：

第一題：找出沒(méi)選過(guò)“黎明”老師的所有學(xué)生姓名。

第二題：列出2門(mén)以上(含2門(mén))不及格學(xué)生姓名及平均成績(jī)。

第三題：即學(xué)過(guò)1號(hào)課程又學(xué)過(guò)2號(hào)課所有學(xué)生的姓名。

請(qǐng)用標(biāo)準(zhǔn)SQL語(yǔ)言寫(xiě)出答案，方言也行(請(qǐng)說(shuō)明是使用什么方言)。

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', '語(yǔ)文', '張'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '2', '政治', '王'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '3', '英語(yǔ)', '李'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '4', '數(shù)學(xué)', '趙'); 
INSERT INTO C ( CNO, CNAME, CTEACHER ) VALUES ( '5', '物理', '黎明'); 
commit;
 
INSERT INTO S ( SNO, SNAME ) VALUES ( '1', '學(xué)生1'); 
INSERT INTO S ( SNO, SNAME ) VALUES ( '2', '學(xué)生2'); 
INSERT INTO S ( SNO, SNAME ) VALUES ( '3', '學(xué)生3'); 
INSERT INTO S ( SNO, SNAME ) VALUES ( '4', '學(xué)生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;

問(wèn)題一：找出沒(méi)選過(guò)“黎明”老師的所有學(xué)生姓名。

第一步：求出黎明老師教授的課程編號(hào)

Select cno from c where CTEACHER =  '黎明'

第二步：查詢(xún)選擇黎明老師的課程的學(xué)生編號(hào)

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

第三步，得到結(jié)果

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

問(wèn)題二：列出2門(mén)以上(含2門(mén))不及格學(xué)生姓名及平均成績(jī)。

第一步：獲取2門(mén)以上(含2門(mén))不及格的學(xué)生編號(hào)

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

第二步：獲取每個(gè)同學(xué)的平均成績(jī)

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

第三步： 獲取結(jié)果

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

問(wèn)題三：即學(xué)過(guò)1號(hào)課程又學(xué)過(guò)2號(hào)課所有學(xué)生的姓名。

第一步：查詢(xún)選擇過(guò)1號(hào)課程和2號(hào)課程的學(xué)生編號(hào)

Select sno from sc where cno='1' and sno in ( select sno from sc where cno='2' )

第二步：獲取結(jié)果

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、列出所有員工及直接上級(jí)的姓名

(99語(yǔ)法)Select e.ename, nvl(m.ename, '沒(méi)有上級(jí)') as mname from emp e left join emp m on e.mgr = m.empno 
(92語(yǔ)法) Select e.ename, nvl(m.ename, '沒(méi)有上級(jí)') as mname from emp e, emp m where e.mgr = m.empno(+)

15、列出受雇日期早于其直接上級(jí)的所有員工的編號(hào),姓名,部門(mén)名稱(chēng)

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、列出部門(mén)名稱(chēng)和這些部門(mén)的員工信息,同時(shí)列出那些沒(méi)有員工的部門(mén)

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

17、列出至少有一個(gè)員工的所有部門(mén)

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"(辦事員)的姓名及其部門(mén)名稱(chēng),部門(mén)的人數(shù)

第一步：獲取工作崗位是CLERK的員工信息

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

第二步：獲取部門(mén)名稱(chēng)

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

第三步：取得每個(gè)部門(mén)的人數(shù)

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

第四步：獲取結(jié)果

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的各種工作及從事此工作的全部雇員人數(shù)

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

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

第二步：取得每種工作崗位的員工數(shù)量

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

第三步：獲取結(jié)果

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、列出在部門(mén)"SALES"<銷(xiāo)售部>工作的員工的姓名,假定不知道銷(xiāo)售部的部門(mén)編號(hào)

22、列出薪金高于公司平均薪金的所有員工,所在部門(mén),上級(jí)領(lǐng)導(dǎo),雇員的工資等級(jí)

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

Select avg(sal) from emp

第二步 獲取大于平均薪水的員工

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

第三步 和部門(mén)表進(jìn)行關(guān)聯(lián)

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

第四步 和經(jīng)理表關(guān)聯(lián)

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

第五步 和等級(jí)關(guān)聯(lián)

Select t.ename 姓名, d.dname 部門(mén)名稱(chēng), nvl(m.ename, '無(wú)') 上級(jí)經(jīng)理, grade 工資等級(jí)  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