SQL语句作业

更新时间：2024-06-17 03:32:01 阅读量：综合文库文档下载

说明：文章内容仅供预览，部分内容可能不全。下载后的文档，内容与下面显示的完全一致。下载之前请确认下面内容是否您想要的，是否完整无缺。

SQL语句作业：（使用orcl数据库）

1、用SCOTT用户的部门表DEPT和员工表EMP，写出完成如下任务的SQL语句：

（1）列出所有员工的姓名及其直接上司的姓名。

SQL> select a.ename,b.ename mgr from emp a,emp b where a.empno=b.mgr;

ENAME MGR ---------- ---------- FORD SMITH BLAKE ALLEN BLAKE WARD KING JONES BLAKE MARTIN KING BLAKE KING CLARK JONES SCOTT BLAKE TURNER SCOTT ADAMS BLAKE JAMES

ENAME MGR ---------- ---------- JONES FORD CLARK MILLER

已选择13行。

（2）列出受雇日期早于其直接上司的员工的姓名、员工编号、部门号。

SQL> select a.ename,a.empno,a.deptno from emp a,emp b where a.empno=b.mgr and mo nths_between(a.hiredate,b.hiredate)<0;

ENAME EMPNO DEPTNO ---------- ---------- ----------

BLAKE 7698 30 JONES 7566 20 BLAKE 7698 30

SCOTT 7788 20 BLAKE 7698 30 JONES 7566 20 CLARK 7782 10

已选择7行。

（3）列出在部门“SALES”工作的员工姓名。

SQL> select emp.ename from dept,emp where dept.deptno=emp.deptno and dept.dname= 'SALES'; ENAME --------- ALLEN WARD MARTIN BLAKE TURNER JAMES

已选择6行。

（4）列出工资高于公司平均工资的所有员工的姓名、员工编号、部门号。

SQL> select emp.ename,emp.empno,emp.deptno from emp where sal>(select avg(sal) f rom emp);

ENAME EMPNO DEPTNO ---------- ---------- ----------

JONES 7566 20 BLAKE 7698 30 CLARK 7782 10 SCOTT 7788 20 KING 7839 10 FORD 7902 20

已选择6行。

（5）列出在每个部门的员工数量、平均工资和平均工作月数。

SQL> select dept.deptno,dname,cou from dept left join (select deptno,count(*) co

u from emp group by deptno) a on a.deptno=dept.deptno order by dept.deptno desc;

DEPTNO DNAME COU ---------- -------------- ---------- 40 OPERATIONS

30 SALES 6 20 RESEARCH 5 10 ACCOUNTING 3

SQL> select dept.deptno,dname,average_sal from dept left join (select deptno,avg (sal) average_sal from emp group by deptno) a on a.deptno=dept.deptno order by dept.deptno desc;

DEPTNO DNAME AVERAGE_SAL ---------- -------------- ----------- 40 OPERATIONS

30 SALES 1566.66667 20 RESEARCH 2175 10 ACCOUNTING 2916.66667

SQL> select dept.deptno,dname,average from dept left join (select deptno,avg(mon ths_between(sysdate,hiredate)) average from emp group by deptno) a on a.deptno=d ept.deptno order by dept.deptno desc;

DEPTNO DNAME AVERAGE ---------- -------------- ---------- 40 OPERATIONS

30 SALES 363.408044 20 RESEARCH 336.136001 10 ACCOUNTING 359.822023

（6）列出所有部门的详细信息和部门人数。

SQL> select dept.deptno,dname,loc,cou from dept left join (select deptno,count(* ) cou from emp group by deptno) a on a.deptno=dept.deptno order by dept.deptno d esc;

DEPTNO DNAME LOC COU ---------- -------------- ------------- ---------- 40 OPERATIONS BOSTON

30 SALES CHICAGO 6 20 RESEARCH DALLAS 5 10 ACCOUNTING NEW YORK 3

（7）列出各种职位的最低工资。

SQL> select job,min(sal) from emp group by job;

JOB MIN(SAL) --------- ----------

CLERK 800 SALESMAN 1250 PRESIDENT 5000 MANAGER 2450 ANALYST 3000

（8）列出部门经理中工资最低的那个经理的姓名、工资、部门号。

SQL> select ename,sal,deptno from emp where sal<=all(select sal from emp where j ob='MANAGER') and job='MANAGER';

ENAME SAL DEPTNO ---------- ---------- ----------

CLARK 2450 10

（9）列出所有员工的年工资（不含佣金），按年薪从低到高排序。

SQL> select empno,ename,sal*12 年薪 from emp order by sal asc;

EMPNO ENAME 年薪 ---------- ---------- ----------

7369 SMITH 9600 7900 JAMES 11400 7876 ADAMS 13200 7521 WARD 15000 7654 MARTIN 15000 7934 MILLER 15600 7844 TURNER 18000

7499 ALLEN 19200 7782 CLARK 29400 7698 BLAKE 34200 7566 JONES 35700

EMPNO ENAME 年薪 ---------- ---------- ----------

7788 SCOTT 36000 7902 FORD 36000 7839 KING 60000

已选择14行。

（10）将“SALES”部门所有员工的佣金提高至其月薪的30%。

SQL> update emp set comm=sal*0.3 where deptno=(select deptno from dept where dna me='SALES');

已更新6行。

SQL> select * from emp where deptno=(select deptno from dept where dname='SALES' );

EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- -------------- ---------- ---------- DEPTNO ----------

7499 ALLEN SALESMAN 7698 20-2月 -81 1600 480 30

7521 WARD SALESMAN 7698 22-2月 -81 1250 375 30

7654 MARTIN SALESMAN 7698 28-9月 -81 1250 375 30

EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- -------------- ---------- ---------- DEPTNO ----------

7698 BLAKE MANAGER 7839 01-5月 -81 2850 855 30

7844 TURNER SALESMAN 7698 08-9月 -81 1500 450 30

7900 JAMES CLERK 7698 03-12月-81 950 285 30

已选择6行。

2、对HR用户的EMPLOYEES表中的每个雇员，查询显示雇员名字LAST_NAME，并计算每个雇员从被聘用起（HIRE_DATE）到今天的工作月数，对该列设置别名MONTH_WORKED。工作月数保留到整数位数，结果按工作月数升序排序。

SQL> select last_name,floor(months_between(sysdate,hire_date)) month_worked from employees order by month_worked asc;

LAST_NAME MONTH_WORKED ------------------------- ------------

Banda 137 Kumar 137 Ande 138 Markle 139 Lee 139 Geoni 140 Marvins 140 Zlotkey 140 Philtanker 140 Grant 140 Perkins 141

LAST_NAME MONTH_WORKED ------------------------- ------------

Johnson 141 Gee 141 Popp 142 Mourgos 142 Tuvault 142 Cambrault 143 Colmenares 146 Sullivan OConnell Grant Greene

LAST_NAME ------------------------- ------------

Olson Bates Jones Smith Lorentz Cabrio Landry Cambrault Himuro Sewall Mikkilineni

LAST_NAME ------------------------- ------------

Rogers Gates Vargas Dellinger McCain Feeney Livingston Walsh Patel Taylor Matos

LAST_NAME ------------------------- ------------

147 147 148 150 MONTH_WORKED 150 150 150 151 152 152 152 154 154 155 156 MONTH_WORKED 157 158 159 159 159 160 161 161 162 162 162 MONTH_WORKED

Olsen 162 Bloom 162 Seo 163 Urman 163 Fleaur 163 Pataballa 164 Fox 164 Taylor 164 Baida 165 Doran Vishney

LAST_NAME ------------------------- ------------

Stiles Atkinson Chen Vollman Sciarra Bissot Fay Dilly Hall Nayer Tobias

LAST_NAME ------------------------- ------------

Chung Austin Bernstein Hutton Fripp Ozer Errazuriz Smith Marlow Bull Everett

LAST_NAME ------------------------- ------------

Tucker Davies

165 166 MONTH_WORKED 167 167 168 168 168 169 169 169 169 170 170 MONTH_WORKED 171 171 174 174 174 174 175 175 175 175 175 MONTH_WORKED 176 176

Partners 177 Russell 180 McEwen 182 Weiss 182 Mallin 183 Abel 184 Sully 187 Hartstein 187 King 188

LAST_NAME MONTH_WORKED ------------------------- ------------

Sarchand 188 Bell 188 Rajs 191 Ladwig 194 Khoo 196 Kaufling 197 Raphaely 202 Greenberg 205 Faviet 205 Higgins 208 Baer 208

LAST_NAME MONTH_WORKED ------------------------- ------------

Mavris 208 Gietz 208 De Haan 224 Ernst 244 Hunold 261 Kochhar 264 Whalen 288 King 291

已选择107行。

3、从HR用户的EMPLOYEES表查询晚于Davies被聘用的雇员名字LAST_NAME和聘用日期HIRE_DATE。

SQL> select last_name,hire_date from employees where months_between(hire_date,(s elect hire_date from employees where last_name='Davies'))>0 order by hire_date a

sc;