sql 语句练习

sql语句练习
sql

1,查询名字为 steven的员工信息.
select employee_id salary from employees wherelast_name='steven';
2,查找员工名字长度为5个字符的员工信息
select last_name employee_id from employees wherelength(last_name)=5;
3,查询工资不足15位,不足位用"$"代替 左侧补位
select last_name "name",lpad(salary,,'$') as salary fromemployees;
4,查询员工姓名,和在公司工作的天数.用整数表示.

select last_name ,hire_date from employees;

5,按照  年月日格式和 - - 格式显示员工入职时间
select last_name ,hire_date,to_char(hire_date,'yyyy')||'-'||to_char

(hire_date,'mm')||'-'||to_char(hire_date,'dd')||'-' from employees ;

6,创建订单表,建立在命名空间mydata上. 字段分别为订单编号,订单日期,发货日期(先给权限)

create tablespace db1 datafile 'e:/orcli/db1.dbf' size 50m;
create table orderS(sid number,sdate number,ssdate number)tablespace db1;
7,向订单表插入5条数据;
insert into orderS(sid,sdate,ssdate)values(001,20170601,20170701);
insert into orderS(sid,sdate,ssdate) select * from orderS;
8,查询一个订单,从下单开始到启运时间需要多久.

//select sid ，round(time_between(sdate,ssdate)) sdate fromorderS;

9,创建客户表,字段为 客户编号,客户姓名,客户生日,客户账号余额... (插入一些测试数据,尽量真

实)
create table client(id number not null,namevarchar2(30),birthday  number null,balance

number null) tablespace db1;

10,列出客户表所有用户信息,如果客户没有提供生日 就显示not available,如果没余额就显示 no

account;
select replace(nvl(birthday,'0'),'0','not available') fromclient;
select replace(nvl(balance,'0'),'0','no count') from client;
select * from client;
11,列出生日在1995年的客户全部信息.
select * from client where birthday like '1985%';





过滤和排序数据
1.查询工资大于1600的员工姓名和工资select last_name,salary from employeeswhere

salary>1600;
2.查询员工号为7369的员工的姓名和部门号select last_name,department_id fromemployees

where employee_id=7369;
3.选择工资不在4000到5000的员工的姓名和工资select first_name,salary from employeeswhere

salary not between4000 and 5000；
4.选择雇用时间在1981-2-19到1981-12-3之间的员工姓名，JOB和雇用时间HIREDATE
select employee_id,salary,hire_date from employees where hire_datebetween to_date

(‘1981-02-19’,’yyyy-mm-dd’) andto_date(‘1981-12-03’,yyyy-mm-dd);
5.选择在20和30号部门工作的员工姓名和部门号
6.Select employee_id, department_id from employees wheredepartment_id in (20, 30);
7.选择在1987年雇用的员工的姓名和雇用时间
8.select employee_id, hire_date from employees whereto_char(hire_date, 'yyyy')='1987';
9.选择公司中没有管理者的员工姓名及JOB
10.select  employee_id, job from employees wheremanager_id is null;
11.选择公司中有奖金的员工姓名，工资和奖金级别
12.Select last_name,salary,commission_pct from employees wherecommission_pct is not

null;
13.选择员工姓名的第三个字母是A的员工姓名
14.   Select last_name fromemployees where last_name like ‘__A%’; 
15.选择姓名中有字母A和E的员工姓名
16.Select last_name || first_name from employees where last_name ||first_name like

‘%A%E%’;

单行函数
1.显示系统时间
Select sysdate from dual；
2.查询员工表emp中员工号empno，姓名ename，工资sal，以及工资提高百分之20%后的结果

Select employee_id,last_name,salary*1.2 from employees;
3.将员工的姓名按首字母排序，并写出姓名的长度（length）

Select last_name,length(last_name) from employees order bylast_name;
4.查询各员工的姓名ename，并显示出各员工在公司工作的月份数(即:与当前日期比较,该员工已经

工作了几个月)。
Select last_name,round(months_between(sysdate,hire_date))hire_months from employees;
5.查询员工的姓名和工资，按下面的形式显示
Name   Salary
SMITH   $$$$$$$$$$24000
Select last_name “Name”,lpad(salary,15，‘$’) as Sarary fromemployees;
6.查询员工的姓名ename和工资数sal,条件限定为工资数必须大于1200，并对查询结果按月份数降序

方式进行排列
select last_name,salary from employees where salary>1200 orderby salary desc;
7.做一个查询，产生下面的结果
earns monthly but wants
Dream Salary
King earns $24000 monthly but wants $72000

select last_name || ' earns' || to_char(salary,'$9999,9999') || 'monthly but wants

earn ' || salary*3 || '!' from employees where last_name ='King';
8.做一个查询，产生类似下面的结果
Ename   HireDate   reiew
SMITH   1980-12-17   1980年12月17日
select last_name as Ename,hire_date asHireDate,to_char(hire_date,'yyyy')||'年'||

to_char(hire_date,'mm')||'月'||to_char(hire_date,'dd')||'日' ASreview from employees

where last_name = 'King';
9.使用decode函数，按照下面的条件：
job                 grade
PRESIDENT           A
MANAGER            B
ANALYST             C
SALESMAN           D
CLERK               E
产生类似下面形式的结果
ENAME   Job   Grade
SMITH   CLERK    E
Selectjob_id,decode

(JOB_id,'PRESIDENT','A','MANAGER','B','ANALYST','C','SALESMAN','D','CLERK','E')GRADE

FROM employees where last_name = 'King';
10.将第9题的查询用case函数重新实现。
select last_name,job_id, case job_id when 'PRESIDENT' then'A'
                                    WHEN 'MANAGER' THEN  'B'
                                    WHEN 'ANALYST' THEN  'C'
                                    WHEN 'SALESMAN' THEN  'D'
                                    WHEN 'CLERK'   THEN 'E'
                                    END "Grade"
from employees;


分组函数
1.查询公司员工工资的最大值，最小值，平均值，总和
select max(salary),min(salary),avg(nvl(salary),0),sum(salary) fromemployees;
2.查询各job的员工工资的最大值，最小值，平均值，总和
SELECT MIN(salary), MAX(salary),avg(salary),sum(salary)
FROM   employees;
3.选择具有各个job的员工人数(提示:对job进行分组)
select job_id,count(job_id) from employees group by job_id;
4.查询员工最高工资和最低工资的差距（DIFFERENCE）
select max(salary)-min(salary) difference from employyee;
5.查询各个管理者手下员工的最低工资，其中最低工资不能低于800，没有管理者的员工不计算在内
select manager_id,min(salary) from employees where manager_id idnot null group by

manager_id having min(salary)>=6000;
6.查询所有部门的名字dname，所在位置loc，员工数量和工资平均值

Select department_name, location_id, count(employee_id),avg(salary) From employees e

join departments d On e.department_id = d.department_id Group bydepartment_name,

location_id

7.查询公司的人数，以及在1980-1987年之间，每年雇用的人数，结果类似下面的格式
total   1980   1981   1982   1987
30   3   4   6    7

SELECT COUNT(*) "total",
   SUM(DECODE(TO_CHAR(HIRE_DATE,'YYYY'),'1980',1,0)) AS "1980",
   SUM(DECODE(TO_CHAR(HIRE_DATE,'YYYY'),'1981',1,0)) AS "1981",
   SUM(DECODE(TO_CHAR(HIRE_DATE,'YYYY'),'1982',1,0)) AS "1982",
   SUM(DECODE(TO_CHAR(HIRE_DATE,'YYYY'),'1987',1,0)) AS "1987"
FROM employees;

多表查询05
1.显示所有员工的姓名ename，部门号deptno和部门名称dname。
selecte.employee_id,e.department_id,d.department_id,d.department_namefrom
employees e,departments d where e.department_id(+) =d.department_id;
2.查询20号部门员工的job和20号部门的loc
select e.job_id,d.location_id from employees e join departmentsd  on (e.department_id

= d.department_id) where e.department_id=20;
3.选择所有有奖金comm的员工的
ename , dname , loc

select e.last_name,e.department_id,d.department_id,d.location_idfrom employees e join

departments d on(e.department_id = d.department_id) wheree.commission_pct is not null;

4.选择在DALLAS工作的员工的
ename , job , deptno, dname
 
selecte.last_name,e.job_id,e.department_id,d.department_id,d.department_name
from employees e join departments d on (e.department_id =d.department_id) where

d.department_name = 'DALLAS';

5.选择所有员工的姓名ename，员工号deptno，以及他的管理者mgr的姓名ename和员工号deptno，结

果类似于下面的格式
employees   Emp#   manager   Mgr#
SMITH   7369   FORD   7902

selectw.last_name,w.employee_id,w.department_id,m.last_name,m.manager_id；

,m.department_id
from employees w join employees m on (w.manager_id =m.employee_id);
6. 查询各部门员工姓名和他们所在位置，结果类似于下面的格式
Deptno   Ename   Loc
20   SMITH   DALLAS
select e.department_id,e.last_name,d.department_id,d.location_idfrom employees e

,departments d where e.department_id = 20 and e.department_id =d.department_id(+);