sql练习

第二章
1.显示部门表的全部信息
SELECT *
FROM departments;
2.显示部门号码，部门名称
SELECT department_id,
department_name
FROM departments;
3.显示以下字段及字符串的连接：员工名 ，" 1个月的工资为： " ，工资 。如：Amy 1个月的工资为：8000
SELECT first_name||' '||last_name||'一个月的工资为：'||salary
FROM employees;
4.显示员工姓名，参加工作时间，工作
SELECT first_name,
hire_date,
job_id
FROM employees emp;
5.显示员工姓名：别名为Name，年薪（13个月的月薪）：别名为annual salary。 注：别名大小写
SELECT first_name "Name",
salary*13 "annual salary"
FROM employees;
6.把员工姓名，工作名称(job)作为一个字符串显示
SELECT first_name||' '||last_name||job_id
FROM employees;
7.显示以下字段及字符串的连接：员工名 ，" 's job is " ，工作名称 。如：Amy's job is MANAGER 注：单引号需显示出
SELECT first_name||'.'||last_name||'''s job is'||job_id "MANAGER"
FROM employees;
8.显示员工表中部门号，工作名称，要求去掉重复值
SELECT DISTINCT department_id,
job_id
FROM employees;

第三章
1.显示10号部门号码，部门名称
SELECT department_id,
department_name
FROM departments
WHERE department_id=10
2.显示82年之前参加工作的员工姓名，参加工作时间，工作名称
SELECT first_name,
last_name,
hire_date,
job_id
FROM employees
WHERE hire_date < to_date('98','yy');
3.显示姓名为"Ellen"的员工的员工姓名，参加工作时间，工作名称
SELECT first_name,
hire_date,
job_id
FROM employees
WHERE first_name='Ellen'
4.显示工资在2000到4000之间的员工姓名，工资
SELECT employee_id,
salary
FROM employees
WHERE salary BETWEEN 2000 AND 4000
5.显示出总经理的姓名
SELECT first_name||' '||last_name
FROM employees
WHERE manager_id IS NULL
6.显示出姓名中第三个字母为"a"的员工姓名
SELECT last_name
FROM employees
WHERE last_name LIKE '__a%'
7.显示姓名为"SMITH"或为"KING"的员工的员工姓名，参加工作时间，工作名
SELECT first_name||' '||last_name,
hire_date,
job_id
FROM employees
WHERE last_name IN('Smith','King')
OR first_name IN('Smith','King')
8.显示工作名称中包含"MAN"并且工资大于3000的员工姓名，工作
SELECT first_name,
job_id
FROM employees
WHERE job_id LIKE '%MAN%'
AND salary>3000
9.名字中包含两个’ll’，部门号是30号，或经理号为7782的员工
SELECT *
FROM employees
WHERE (first_name LIKE '%L%' AND last_name LIKE '%L%')
OR (first_name LIKE '%ll%' OR LAST_name LIKE

'%ll%')
AND department_id = 30
OR manager_id = 7782



第四章
1.显示以下字段及字符串的连接："The job id for "，姓名(大写)，" is " ,工作(小写) 如：The job id for ALLEN
is salesman
SELECT concat('The job id for ',upper('allen')||' is ' ||lower('SALEman'))
FROM dual
2.显示出姓名中第三个字母为"A"的员工姓名,要求分别使用两个函数试一
方法一：SELECT first_name||' '||last_name
FROM employees
WHERE first_name LIKE '__a%'
OR last_name LIKE '__a%
方法二：SELECT last_name
FROM employees
WHERE instr(last_name,'a') = 3

3.显示出姓名中最后一个字母为"N"的员工姓名
方法一：
SELECT first_name||' '||last_name
FROM employees
WHERE substr(first_name,-1)='n'
OR substr(last_name,-1)='n'
方法二：
SELECT *
FROM employees
WHERE last_name LIKE '%n'

4.显示员工应交的税金：工资1000元以下为0，大于等于1000并小于2000为工资的10%，大于等于2000并小于3000为工资的15%，大于等于3000为工资的20%
SELECT last_name,
salary,
CASE
WHEN (salary<1000)THEN 0
WHEN (salary>=1000) AND (salary<2000) THEN salary*0.1
WHEN (salary>=2000)AND (salary<3000) THEN salary*0.15
WHEN (salary>=3000) THEN salary*0.2
END
FROM employees;

5.参加工作时间在每月15日之后的员工姓名，参加工作时间
SELECT last_name,
hire_date
FROM employees
WHERE extract(DAY FROM hire_date)>15
6.显示2000年1月1日到现在为止有多少个星期，多少个月，要求保留到整数
SELECT round(months_between(SYSDATE,to_date('2000/01/01','yyyy/mm/dd'))),
round(months_between(SYSDATE,to_date('2000/01/01','yyyy/mm/dd'))*30/7)
FROM dual
7.用"*"个数表示员工的工资中包含多少"千"，如3500，显示'***',5600,显示'*****'
SELECT salary,
rpad(' ',ceil(salary/1000),'*')
FROM employees
8.显示员工名，参加工作时间，参加工作6个月后的第一个周一
SELECT FIRST_name||' '||LAST_name,
hire_date,
next_day(add_months(hire_date,6),'星期一')
FROM employees

9.显示员工姓名，月薪，年薪（13个月的月薪+10000元奖金+comm）要求别名为annual_salary,要求所有人的年薪都显示出来
SELECT FIRST_name||' '||LAST_name,
salary,
salary*13+10000+nvl2(commission_pct,commission_pct*salary,0)
AS annual_salary
FROM employees


第五章
1.显示所有部门在"NEW YORK"(dept表 loc字段)的员工姓名
SELECT first_name||' '||last_name
FROM employees emp,departments dep,locations loc
WHERE emp.department_id=dep.department_id
and dep.location_id = loc.location_id
AND loc.city = 'New York'
2.显示员工

信息包括部门名称，员工的姓名，要求所有部门都显示出来，包含没有员工的部门
SELECT d.department_name,
https://www.wendangku.net/doc/c65575690.html,st_name
FROM employees e,
departments d
WHERE e.department_id(+)=d.department_id
3.显示员工"SMITH"的姓名，部门名称
SELECT first_name||' '||last_name,
department_name
FROM employees e,
departments d
WHERE e.department_id=d.department_id
AND l
ast_name='Smith'
4.显示员工姓名，部门名称，工资，工资级别(salgrade表 grade字段),要求工资级别大于4级
5.显示员工"KING"和"FORD"管理的员工姓名及其经理姓名
SELECT first_name||last_name
FROM employees
WHERE manager_id in(SELECT employee_id
FROM employees
WHERE last_name='King')
6.显示员工名，参加工作时间，经理名，参加工作时间：参加工作时间比他的经理早
SELECT first_name,
hire_date,
manager_id
FROM employees e
WHERE hire_date < ( SELECT hire_date
FROM employees a
where a.employee_id = e.manager_id
)
第六章
1.显示出工作名称(job)中包含"King"的员工平均工资，最高工资，最低工资及工资的和
SELECT AVG(salary),
MAX(salary),
MIN(salary),
SUM(salary)
FROM employees
WHERE job_id LIKE '%MAN%'
2.显示出20号部门的员工人数
SELECT COUNT(1)
FROM employees
WHERE department_id=20
3.显示出平均工资大于2000的部门名称及平均工资
SELECT AVG(salary),
department_name
FROM employees e,
departments d
GROUP BY department_name
HAVING AVG(salary)>2000
4.显示每个部门每种工作平均工资大于2500的部门及工作
SELECT department_id,
job_id
FROM employees
GROUP BY department_id,
job_id
HAVING AVG(salary)>2500
5.显示出工作名称中包含"MAN"，并且平均工资大于1000的工作名称及平均工资
SELECT job_id,
AVG(salary)
FROM employees
GROUP BY job_id
HAVING AVG(salary)>1000
AND job_id LIKE '%MAN%'
6.显示出平均工资最高的的部门平均工资
SELECT *
FROM (
SELECT ROWNUM AS rown,
avgsal,
department_id
FROM(
SELECT AVG(salary) AS avgsal,
department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC
)
ORDER BY avgsal DESC
)
WHERE rown=1
---方法二
SELECT max(avg(salary))
FROM employees
GROUP BY department_id

7.列出最低工资大于1500的各种工作
SELECT job_id
FROM employees
GROUP BY job_id
HAVING MIN(salary)>1500
8.列出各部门的员工数量及平均工作年限
SELECT COUNT(employee_id),
round(AVG(months_between(sysdate,hire_date)/12))
FROM employees
GROUP BY department_id

第七章
1.显示出和员工号7369部门相同的员工姓名，工资
SELECT last_name,
salary
FROM employees
WHERE department_id=(SELECT department_id
FROM employees
WHERE employee_id=200
)
2.显示出和姓名中包含"W"

的员工相同部门的员工姓名
FROM employees
WHERE department_id IN(
SELECT department_id
FROM employees
WHERE last_name LIKE '%a%'
)
3.显示出工资
大于平均工资的员工姓名，工资
SELECT last_name,
salary
FROM employees
WHERE salary>(SELECT AVG(salary)
FROM employees
)
4.显示出工资大于本部门平均工资的员工姓名，工资
SELECT last_name,
salary
FROM employees
WHERE salary> ANY(SELECT AVG(salary)
FROM employees
GROUP BY department_id
)
5.显示员工"KING"所管理的员工姓名
SELECT first_name||last_name NAME,
salary,
department_id
FROM employees
WHERE manager_id IN(SELECT employee_id
FROM employees
WHERE last_name = 'King'
)
6.显示每位经理管理员工的最低工资，及最低工资者的姓名
SELECT emp.employee_id,
emp.salary,
emp.department_id,
emp.manager_id
FROM employees emp
WHERE NOT EXISTS(SELECT man.salary
FROM employees man
WHERE man.manager_id=emp.manager_id
AND man.salary<emp.salary)
7.显示比工资最高的员工参加工作时间晚的员工姓名，参加工作时间
SELECT last_name,
hire_date
FROM employees
WHERE hire_date>(SELECT hire_date
FROM employees
WHERE salary=(SELECT MAX(salary)
FROM employees))
8.显示出平均工资最高的的部门平均工资及部门名称
SELECT ROWNUM,
department_name,
avg_sal
FROM(
SELECT
department_name,
AVG(salary) AS avg_sal

FROM employees e,
departments d
WHERE e.department_id=d.department_id
GROUP BY department_name
ORDER BY avg_sal DESC
)
WHERE ROWNUM=1


SELECT avg_sal,
department_name

FROM (
SELECT AVG(salary) AS avg_sal,
department_name
FROM departments dep
JOIN employees emp
ON dep.department_id = emp.department_id
GROUP BY department_name
)
WHERE avg_sal = (
SELECT MAX(avg_sal)
FROM (
SELECT AVG(salary) AS avg_sal,
department_name
FROM departments dep
JOIN employees emp
ON dep.department_id = emp.department_id
GROUP BY department_name
)
)
教材练习


1.查询工资高于编号为113的员工工资，并且和102号员工从事
相同工作的员工的编号、姓名及工资。
SELECT employee_id,
last_name,
salary
FROM employees
WHERE salary>(SELECT salary
FROM employees
WHERE employee_id=113)
AND job_id=(SELECT job_id
FROM employees
WHERE employee_id=102)
? 2.查询工资最高的员工姓名和工资。
SELECT
last_name,
salary
FROM employees
WHERE salary=(SELECT MAX(salary)
FROM employees)
? 3.查询部门最低工资高于100号部门最低工资的部门的编号、
名称及部门最低工资。

? 4.查询员工工资为其部门最低工资的员工的编号和姓名
及工资。
方法一、
SELECT department_id,
last_name,
salary
FROM employees e
WHERE NOT EXISTS(
SELECT 1
FROM employees s
WHERE s.department_id=e.

department_id
AND s.salary<e.salary
)
方法二：
SELECT employee_id,
first_name,
last_name,
salary
FROM employees e
WHERE salary=(SELECT MIN(salary)
FROM employees
WHERE department_id=e.department_id);

? 5.显示经理是KING的员工姓名，工资。
SELECT last_name,
salary
FROM employees
WHERE manager_id IN(SELECT manager_id
FROM employees
WHERE last_name='King'
AND manager_id IS NOT NULL)
? 6.显示比员工‘Abel’参加工作时间晚的员工姓名，工资，参加
工作时间。

第八章

CREATE TABLE copy_emp (
empno number(4),
ename varchar2(20),
hiredate date default sysdate ,
deptno number(2),
sal number(8,2))
1.表copy_emp中插入数据，要求sal字段插入空值，部门号50，参加工作时间为2000年1月1日，其他字段随意
INSERT INTO copy_emp
VALUES(1,'fengbing','01-1月-2000',50,NULL)
2.表copy_emp中插入数据，要求emp表中部门号为10号部门的员工信息插入
INSERT INTO copy_emp
SELECT employee_id,
last_name,
hire_date,
department_id,
salary
FROM employees
WHERE department_id=10
3.修改copy_emp表中数据，要求10号部门所有员工涨20%的工资
UPDATE copy_emp
SET salary=salary+salary*0.2
WHERE department_id=10
4.修改copy_emp表中sal为空的记录，工资修改为平均工资
UPDATE copy_emp
SET salary=(SELECT AVG(salary)
FROM copy_emp
)
WHERE salary=NULL
5.工资为平均工资的员工，工资修改为空，参加工作时间修改为默认值
UPDATE copy_emp
SET salary=NULL,
hire_date=DEFAULT
WHERE salary=(SELECT AVG(salary)
FROM copy_emp)
6.另外打开窗口2查看以上修改
7.执行commit，窗口2中再次查看以上信息
8.删除工资为空的员工信息
DELETE FROM copy_emp
WHERE salary=NULL
9.执行rollback
ROLLBACK;

第九章
1.创建copy_dept，要求格式同dept表完全一样，不包含数据

2.设置copy_emp表中外键deptno，参照copy_dept中deptno,语句能否成功为什么？
3.设置copy_dept表中主键deptno
4.重复执行第二步，语句能否成功为什么？修改直到语句成功

第十章
1.创建视图，要求包含字段：部门名，部门平均工资
2.针对以上视图执行insert,update,delete,语句能否成功，为什么？
3.显示出员工工资排在第五
到第十名的员工信息
4.创建序列，起始值为50，每次增加5
5.在表copy_dept中插入记录，其中部门号码采用第一步中创建的序列生成
6.创建表，采用"create table copy_emp_index as select * from emp"，生成500万条数据，把其中的"员工号"字段修改为唯一
7.查询表copy_emp_index表中员工号为200001的员工姓名，工资，记录执行时间
8.创建索引后，再次执行第7步语句，记录执行时间并做对比