oracle sql 练习语句 42道

练习中用到的函数

1.count 计算元组个数，count（*）计算全部个数

2.length 计算char类型长度

3.lpad(salary，15,'$')  格式调整，把salary 变为15个字长，左边用￥填充。

4.NVL(e1, e2)：如果 e1 的计算结果为 null 值，则 NVL( ) 返回 e2。如果 e1 的计算结果不是 null 值，则返回 e1。返回值功能。

5.四大计算函数 max，avg，min，sum

6.round（avg（salary），1） 取一位小数。

7.initcap 规范化字符串，首字母大写.

8.日期函数

   日期转换字符串  to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual;  

   月份间隔（oracle）months_between(sysdate,to_date('12-31-1998','MM-DD-YYYY'))

   天数间隔      sysdate - to_date('20020405','yyyymmdd')

   求某天星期几 to_char(to_date('2002-08-26','yyyy-mm-dd'),'day')

遇到的问题：

32题：报错 not a group by expression  查了资料发现是 group by 的用法问题。当使用group by 分组后 ，groupby后面的字段会相同合并为一条记录，而select 后面有些字段并不被作为分组依据，可能出现多值，因此解决方法是，要么在groupby后面加上所有select 出现的字段，要么使用聚合函数生成单值。

33题：自然连接查询，因为department-id是department表的主键，所以可以这样查询。

36题：三表查询，employees 和 department 表通过 department id 联系，department 和location 表通过location id 联系。

join on 语句通过两个表的主键连接，使用两次就可以以一个中间表当桥，查询第三个表。

37题：自身连接，还是通过join on实现。假设em1是雇员，em2是经理表，让em1的经理号=em2的雇员号，这样就形成了通过表二找到了表一对应的雇员。

39题：嵌套查询

题目

1)下面的SELECT语句是否执行成功： SELECT last_name, job_id, salary AS Sal FROM employees;

 

2)在下面的语句中有4个编码错误，请找出它们： SELECT employee_id, last_name sal x 12 ANNUAL SALARY FROMemployees;

 

3)显示DEPARTMENTS表的结构。选择表中的所有数据

 

4)显示EMPLOYEES表的结构。创建一个查询，显示每个雇员的 last name, job, code, hire date, employee. employee显示在第一列， 给HIRE_DATE列指定一个别名 STARTDATE . 

 

5)创建一个查询从EMPLOYEES表中显示唯一的工作代码

 

6)创建一个查询，显示收入超过$12,000的雇员的名字和薪水

 

7)将 SQL语句存到文件 lab2_1.sql中，运行该查询

 

8)创建一个查询，显示雇员号为176的雇员的名字和部门号

 

9)修改 lab2_1.sql文件，显示所有薪水不在 5000和 12000之间的雇员的名字和薪 水。将 SQL语句存到文件 lab2_3.sql中   10)显示受雇日期在1998年2月20日 和1998年5月1日之间的雇员的名字、岗位和受雇日期，按受雇日期顺序排序查询结果

 

11)显示所有在部门20和50中的雇员的名字和部门号，并以名字按字母顺序排序

 

12)修改 lab2_3.sql列出收入在 $5,000和 $12,000之间，并且在部门20或50工作 的雇员的名字和薪水。将列标题分别显示为 Employee 和 Monthly Salary，将 lab2_3.sql保存为 lab2_6.sql

 

13)显示每一个在 1994年受雇的雇员的名字和受雇日期

 

14)显示所有没有主管经理的雇员的名字和工作岗位

 

15)显示所有有佣金的雇员的名字、薪水和佣金。以薪水和佣金的降序排序数据

 

16)显示所有名字中第三个字母是 a的雇员的名字

 

17)显示所有名字中有一个 a和一个 e的雇员的名字

 

18)显示所有工作是销售代表或者普通职员，并且薪水不等于 $2,500、$3,500或$7,000的雇员的名字、工作和薪水。

 

19)写一个查询显示当前日期，列标签显示为DATE

 

20)对每一个雇员，显示employeenumber,last_name,salary和salary增加15%， 并且表示成整数，列标签显示为New Salary,将你的SQL语句存到名为lab3_2.sql的 文本文件中

 

21)修改查询lab3_2.sql添加一个列，该列从新薪水New Salary列中减去旧薪水，列标 签为increase,保存内容到文件lab3_4.sql中，运行修订的查询

 

22)写一个查询用首写字母大写，其它字母小写显示雇员的last name,显示名字的长度， 对所有名字开始字母是J、A或M的雇员，给每列一个适当的标签，用雇员的last name排序结果 

 

23)对每一个雇员，显示其 lastname,并计算从雇员受雇日期到今天的月数，列标签 MONTHS＿WORKED，按受雇月数排序结果，四舍五入月数到最靠近的整数月

 

24)创建一个查询显示所有雇员的lastname和salary.格式化为15个字符长度，用$左 填充，列标签SALARY

 

25)显示每一个雇员的lastname, hire date和雇员开始工作的周日，列标签DAY，用星 期一作为周的起始日排序结果

 

26)创建一个查询显示雇员的lastnames和commission(佣金)比率。如果雇员没有佣金显示“No Commission”列标签COMM

 

27)显示所有雇员的最高、最低、合计和平均薪水，列标签分别为：maximum、minmum、sum、 average。四舍五入结果为最近的整数

 

28)对上面的问题显示每种工作类型的最低、最高、合计、平均薪水

 

29)显示每一个工作岗位的人数

 

30)显示经理人数

 

31)最高与最低薪水之间的差

 

32)查询显示每个部门的名字、地点、人数和部门中所有雇员的平均薪水。四舍五入薪水到两位小数

 

33)写一个查询显示所有雇员的lastname, department number and department name

 

34)创建一个在部门80中的所有工作岗位的唯一列表，在输出中包括部门的地点 

 

35)显示所有在其last name中有一个小写a的雇员的last name和departmentname

 

36)写一个查询显示那些工作在Toronto的所有雇员的 last name、job、department number和department name

 

37)显示雇员的 last name和 employee number连同他们的经理的 lastname和 manager number，列标签为employee、emp、manager和mgr

 

38)创建一个查询显示所有与被指定雇员工作在同一部门的雇员的 last names、 department numbers。给每列一个适当的标签

 

39)写一个查询显示与zlotkey在同一部门的雇员的last name和hire date,结果中不 包括zlotkey

 

40)创建一个查询显示所有薪水高于平均薪水的雇员的雇员号和名字，按薪水的升序排序

 

41)显示所有部门地点号（departmentlocation ID）是1700的雇员的last name、 departtment number和jobID

 

42)显示在Executive部门的每个雇员的department number、last name和jobID

**********************************1-10
select last_name,job_id,salary as sal
from employees;

select employee_id ,last_name, salary*12 "ANNUAL SALARY"
from employees;

3.Null

select employee_id,last_name, job_id, hire_date STARTDATE from employees;

SELECT distinct job_id from employees  ;

SELECT first_name, salary from employees where salary >12000;

7.Null

SELECT last_name, department_id from employees where employee_id=176;

SELECT last_name, salary from employees WHERE salary not between 5000 and 12000;

SELECT last_name, job_id, hire_date from employees
WHERE hire_date between to_date('2008-02-20 00:00:00','yyyy-mm-dd hh24:mi:ss') 
and to_date('2008-05-01 00:00:00','yyyy-mm-dd hh24:mi:ss');

************************************11-20

SELECT last_name, department_id from employees WHERE department_id in (20,50) order BY last_name;

SELECT last_name  Employee, salary "MONTHLY Salary" from employees WHERE salary between
5000 and 12000 and department_id in (20,50);

SELECT last_name, first_name, hire_date from employees
WHERE hire_date like '%08';

select last_name, first_name, job_id from employees where manager_id is null;

select last_name, first_name, salary, commission_pct from employees 
order by salary, commission_pct;

select last_name, first_name from employees where first_name like '__a%'; 

select last_name, first_name from employees where first_name like '%a%'
and first_name like '%e%';

select last_name, first_name, salary from employees where
job_id like 'SA_REP' or job_id = 'ST_CLERK' and salary not in (2500,3500,7000);

select sysdate DATA from dual; 

select employee_id, salary, salary*1.15 "NEW SAlary",last_name, first_name 
from employees;
**********************************21-30
select employee_id, salary, salary*1.15 "NEW SAlary", salary*1.15 -salary as "ins",last_name, first_name 
from employees;

select initcap(last_name) "name",length(last_name) "length" from employees
WHERE last_name like 'J%' or last_name like 'J%' or last_name LIKE 'A%' order by last_name;


select last_name, hire_date,round(months_between(SYSDATE, hire_date)) MONTHS_WORKED from employees 
order by months_between(SYSDATE,hire_date);

select last_name, lpad(salary, 15, '$') "SALARY" from employees ;

select last_name,hire_date, to_char(hire_date, 'DAY') "DAY" from employees
order by to_char(hire_date-1, 'D');

select last_name, nvl(to_char(commission_pct),'No Commission') "COMM" from employees;

select round(max(salary),0) maximum,min(salary) minumum,sum(salary) "sum",round(avg(salary),0) average from employees;

select job_id,round(max(salary),0) maximum,min(salary) minumum,sum(salary) "sum",round(avg(salary),0) average from employees
group by job_id;

select job_id,count (*) from employees group by job_id;

select count(distinct manager_id) number1 from employees;

*************************************31-40

SELECT max(salary)-min(salary) "maxn-minn" from employees;

select sec.department_name, count(*),round(avg(salary),2), sec.location_id
from employees fst, departments sec
where fst.department_id = sec.department_id
group by sec.department_name ;

select last_name, sec.department_name, sec.department_id  
from employees fir , departments sec
where fir.department_id= sec.department_id;

select distinct sec.location_id, fir.job_id
from employees fir , departments sec
where sec.department_id = 80 and fir.department_id = sec.department_id;

select fir.last_name , sec.department_name
from employees fir , departments sec
where fir.department_id = sec.department_id and fir.last_name like '%a%';

select em.last_name , de.department_name , em.job_id, em.department_id
from employees em
join departments de
on (em.department_id = de.department_id)
join locations lo
on (de.location_id = lo.location_id)
where lo.city = 'Toronto';

select em1.last_name "employee",em1.employee_id "emp" ,
em2.last_name "manager", em2.employee_id "mgr"
from employees em1
join employees em2
on (em1.manager_id= em2.employee_id);


select em.last_name "name",em.department_id
from employees em
join employees ea
on (em.department_id = ea.department_id)
where em.employee_id != ea.employee_id;

select last_name,hire_date
from employees
where last_name != 'Zlotkey' and department_id=
( select department_id from employees where last_name= 'Zlotkey');

select first_name, employee_id,salary from employees
where salary > (select avg(salary) from employees)
order by salary;
************************************41-42
select em.last_name, em.job_id, de.department_id, de.location_id
from employees em, departments de;

select em.last_name, em.job_id, em.department_id 
from employees em, departments de
where em.department_id = de.department_id and de.department_name = 'Executive';