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')

遇到的问题：

x.distinct 只能跟在select 后面使用

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-10select last_name,job_id,salary as salfrom employees;select employee_id ,last_name, salary*12 "ANNUAL SALARY"from employees;3.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--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 employeesWHERE 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-20SELECT 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 between5000 and 12000 and department_id in (20,50);SELECT last_name, first_name, hire_date from employeesWHERE 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 wherejob_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-30select employee_id, salary, salary*1.15 "NEW SAlary", salary*1.15 -salary as "ins",last_name, first_name from employees;-----22----select initcap(last_name) "name",length(last_name) "length" from employeesWHERE 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 employeesorder 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 employeesgroup by job_id;select job_id,count (*) from employees group by job_id;select count(distinct manager_id) number1 from employees;31-40SELECT max(salary)-min(salary) "maxn-minn" from employees;select sec.department_name, count(*),round(avg(salary),2), sec.location_idfrom employees fst, departments secwhere fst.department_id = sec.department_idgroup by sec.department_name ;select last_name, sec.department_name, sec.department_id  from employees fir , departments secwhere fir.department_id= sec.department_id;select distinct sec.location_id, fir.job_idfrom employees fir , departments secwhere sec.department_id = 80 and fir.department_id = sec.department_id;select fir.last_name , sec.department_namefrom employees fir , departments secwhere fir.department_id = sec.department_id and fir.last_name like '%a%';select em.last_name , de.department_name , em.job_id, em.department_idfrom employees emjoin departments deon (em.department_id = de.department_id)join locations loon (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 em1join employees em2on (em1.manager_id= em2.employee_id);--38select em.last_name "name",em.department_idfrom employees emjoin employees eaon (em.department_id = ea.department_id)where em.employee_id != ea.employee_id;select last_name,hire_datefrom employeeswhere last_name != 'Zlotkey' and department_id=( select department_id from employees where last_name= 'Zlotkey');select first_name, employee_id,salary from employeeswhere salary > (select avg(salary) from employees)order by salary;select em.last_name, em.job_id, de.department_id, de.location_idfrom employees em, departments de;select em.last_name, em.job_id, em.department_id from employees em, departments dewhere em.department_id = de.department_id and de.department_name = 'Executive';