day03_scott用户练习
练习一：
【构造实现环境----在任意用户下执行都成】
DROP TABLE DEPT;
CREATE TABLE DEPT
(DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
DNAME VARCHAR2(14) ,
LOC VARCHAR2(13)
) ;
DROP TABLE EMP;
CREATE TABLE EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
);
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
DROP TABLE BONUS;
CREATE TABLE BONUS
(
ENAME VARCHAR2(10) ,
JOB VARCHAR2(9) ,
SAL NUMBER,
COMM NUMBER
);
DROP TABLE SALGRADE;
CREATE TABLE SALGRADE
( GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER
);
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
COMMIT;
【Scott用户内容单词备注】
emp员工表 字段内容如下:
empno 员工号
ename 员工姓名
job 工作
mgr 上级编号
hiredate 受雇日期
sal 薪金
comm 佣金
deptno 部门编号
dept 部门表 字段内容如下：
deptno 部门号
ename 部门名称
loc 地方
bonus 奖金表 字段内容如下：
ename 员工姓名
job 工作名称
sal 薪金
comm 佣金
【scott用户操作】
--1.选择部门30中的所有员工.
select * from emp where deptno=30;
--2.列出所有办事员(CLERK)的姓名,编号和部门编号.
select ename,empno,deptno from emp where job='CLERK'
--3.找出佣金高于薪金的员工.
select ename,empno,comm,sal from emp where comm>sal;
--4.找出佣金高于薪金的60%的员工.
select empno,ename,comm,sal from emp where comm>sal*0.6;
--5.找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料.
select * from emp where deptno=10 and job='MANAGER' or (deptno=20 and job='CLERK');
--6.找出既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料.
select * from emp where job not in('MANAGER','CLERK') and sal>=2000;
--7.找出收取佣金的员工的不同工作.
SELECT DISTINCT JOB FROM EMP WHERE COMM IS NOT NULL;
--8.找出不收取佣金或收取的佣金低于100的员工.
SELECT * FROM EMP WHERE COMM<100 OR COMM IS NULL;
--9.找出各月倒数第3天受雇的所有员工.
SELECT * FROM EMP WHERE HIREDATE =LAST_DAY(HIREDATE)-2;
--10.找出早于12年前受雇的员工.
SELECT ENAME FROM EMP WHERE HIREDATE <ADD_MONTHS(SYSDATE,-12*12);
SELECT ENAME FROM EMP WHERE ADD_MONTHS(SYSDATE,12*12)> SYSDATE;
--11.以首字母大写的方式显示所有员工的姓名.
select initcap(ename) from emp;
--12.显示正好为5个字符的员工的姓名.
select ename from emp where ename like '_____';
通配符
% 0或多个通配符
_ 1个通配符
[1-9] 代表1-9的数字
[^1-9] 以1-9的数字开头
SELECT ENAME FROM EMP WHERE ENAME LIKE '_____';
SELECT ENAME FROM EMP WHERE LENGTH(ENAME)=5;
--13.显示不带有"R"的员工的姓名.
select ename from emp where ename not like '%R%';
--14.显示所有员工姓名的前三个字符.
SELECT SUBSTR(ENAME,1,3) FROM EMP;
--15.显示所有员工的姓名,用a替换所有"A"
SELECT REPLACE(ENAME,'A','a') FROM EMP;
--16.显示满10年服务年限的员工的姓名和受雇日期(ADD_MONTHS系统函数).
SELECT ENAME,HIREDATE FROM EMP WHERE HIREDATE < ADD_MONTHS(SYSDATE,-10*12);
--17.显示员工的详细资料,按姓名排序【ASC默认,升序|DESC降序】
SELECT * FROM EMP ORDER BY ENAME;
--18.显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面.
SELECT ENAME,HIREDATE FROM EMP ORDER BY HIREDATE ASC;
--19.显示所有员工的姓名、工作和薪金,按工作的降序排序,若工作相同则按薪金排序.
SELECT * FROM EMP ORDER BY JOB DESC,SAL DESC;
--20.显示所有员工的姓名、加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年份的员工排在最前面.
SELECT ename,TO_CHAR(HIREDATE,'YYYY'),TO_CHAR(HIREDATE,'MM') FROM EMP ORDER BY TO_CHAR(HIREDATE,'MM') ASC,TO_CHAR(HIREDATE,'YYYY') ASC;
--21.显示在一个月为30天的情况所有员工的日薪金,忽略余数.
SELECT FLOOR(SAL/30) FROM EMP;
SELECT TRUNC(SAL/30) FROM EMP;
--22.找出在(任何年份的)2月受聘的所有员工。
SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'MM')='02';
--23.对于每个员工,显示其加入公司的天数(trunc取整,不进行四舍五入,全舍).
SELECT '加入公司的天数: '||trunc(SYSDATE - HIREDATE) FROM emp;
--24.显示姓名字段的任何位置包含"A"的所有员工的姓名.
SELECT ENAME FROM EMP WHERE ENAME LIKE '%A%';
--25.对于每个员工,显示其加入公司多少年了？？多少个月了？？？
SELECT '加入公司的年数: '||trunc((SYSDATE - HIREDATE)/365),'加入公司的月数: '||trunc((SYSDATE - HIREDATE)/30) FROM emp;
(一)、
1、平年大月31天 SELECT ..........
2、平年小月30天 SELECT ..........
2、平年二月28天 SELECT ..........
1、闰年大月31天 SELECT ..........
2、闰年小月30天 SELECT ..........
2、闰年二月28天 SELECT ..........
(二)、
SELECT '加入公司的年数: '||trunc((SYSDATE - HIREDATE)/365),'加入公司的月数: '||trunc((SYSDATE - HIREDATE)/30) FROM emp;
【我写的是平年，并且规定了每个月30天，闰年的情况有考虑，只是没有写】
-------------------------------------------------------------------------------------------------------------------------------------------------------
练习二：
【函数练习】
--1,例:提取 7654 号雇员姓名的 2－4位
select substr(ename,2,3),ename from emp where empno=7654;
--2,round函数的使用【四舍五入】
SELECT ROUND(2.3),ROUND(2.6) FROM DUAL;
--3,to_char
--以货币形式显示sal列
SELECT TO_CHAR(SAL,'$99,999,999'),TO_CHAR(SAL,'L99,999,999'),TO_CHAR(SAL,'L00,000,000') FROM EMP;
--显示当前系统时间
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MI:SS'),TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日"') FROM DUAL;
--4,to_date函数
--查找入职时间在1981-4-01后的员工：
SELECT * FROM EMP WHERE HIREDATE > TO_DATE('1981-4-01','YYYY-MM-DD');
--5,nvl函数
SELECT NVL(COMM,1111),comm FROM EMP;
--8.找出早于27年前受雇的员工.
SELECT * FROM EMP WHERE FLOOR(FLOOR(MONTHS_BETWEEN(SYSDATE,HIREDATE))/12)>27;
SELECT ENAME,HIREDATE FROM EMP WHERE HIREDATE < ADD_MONTHS(SYSDATE,-27*12);
--9.以首字母大写的方式显示所有员工的姓名.
SELECT INITCAP(ENAME) FROM EMP;
--10.显示正好为5个字符的员工的姓名.
SELECT ENAME FROM EMP WHERE LENGTH(ENAME)=5;
--11.显示不带有"T"的员工的姓名.
SELECT ENAME FROM EMP WHERE ENAME NOT LIKE '%T%';
--12.显示所有员工的姓名,用a替换所有"A"
SELECT REPLACE(ENAME,'A','a') FROM EMP;
--13.显示满10年服务年限的员工的姓名和受雇日期.
SELECT * FROM EMP WHERE FLOOR(FLOOR(MONTHS_BETWEEN(SYSDATE,HIREDATE))/12)>10;
--14.显示所有员工的姓名、加入公司的年份和月份
SELECT TO_CHAR(HIREDATE,'YYYY-MM') FROM EMP;
--15.对于每个员工,显示其加入公司的天数.
SELECT TRUNC(SYSDATE-HIREDATE) FROM EMP;
--16 decode()函数:
显示员工编号,姓名,月薪及月薪等级:
要求800= 初级
1600= 中级
3000= 高级
其余月薪显示其他
SELECT DECODE(SAL,800,'初级','其它') FROM EMP;
--
SELECT DECODE(SAL,800,'初级',DECODE(SAL, 1600, '中级',DECODE(SAL, 3000, '高级', '其它'))) FROM EMP;
SELECT DECODE(SAL,800,'初级',1600,'中级',3000,'高级','其它') FROM EMP;
--17sign函数:
显示员工编号,姓名,月薪及月薪等级:
要求1----999 初级
1000----2999 中级
3000----3999 高级
4000----超级
操作：SELECT SIGN(1000),SIGN(-1000),SIGN(0) FROM dual;
结果：
SIGN(1000) SIGN(-1000) SIGN(0)
---------- ----------- ----------
1 -1 0
SELECT DECODE(SIGN(SAL-1000),-1,'初级',DECODE(SIGN(SAL-3000),-1,'中级',DECODE(SIGN(SAL-4000),-1,'高级','超级'))) FROM EMP;
【聚合函数、分组、字符串连接】
(1) ||：字符串连接
select '编号:'||empno||', 姓名:'||ename 情况 from emp;
(2) count : 计数(不计算null)
a.统计表记录数
select count(*) from emp;
b.统计comm字段不为空的记录数
select count(comm) from emp;
(3) group by ... having .... 分组筛选
一旦涉及group by,记住两点：
1,select后面只能跟分组列及聚合函数
2,where前 group by中 having在后,顺序不能变,where筛选的是记录,having筛选的是分组后的记录。
学生表(姓名,成绩,年龄,性别)
create table student(name varchar2(100),score number,age number,sex varchar2(10));
insert into student values('zhangsan',100,20,'nan');
insert into student values('lisi',10,30,'nan');
insert into student values('wangwu',0,40,'nv');
insert into student values('xiaoming',90,10,'nan');
insert into student values('laowang',100,50,'nan');
insert into student values('xiaohong',100,60,'nv');
insert into student values('shuangshuang',100,80,'nv');
insert into student values('ribenren',100,80,'weizhi');
commit;
--例子: 将班级中及格的同学按性别分组,找到平均分在80分以上的组
select sex,count(*) from student where score>=60 group by sex having avg(score)>80;
统计每个部门的最高工资
select deptno,max(sal) from emp group by deptno;
统计每个部门的最高工资 ,显示其中最高工资大于2900的部门
select max(sal) ,deptno from emp group by deptno having max(sal)>2900;
统计工资在2900元以上的所有员工所在部门的最高工资 , 显示其中最高工资大于4000的部门
select deptno,max(sal) from emp where sal>2900 group by deptno having max(sal)>4000;
显示所有员工中的最高工资
select max(sal) from emp;
显示不同工种的最高工资
select max(sal),job from emp group by job;
显示不同部门的平均工资,并保留两位小数
select deptno,round(avg(sal),2) from emp group by deptno;
显示一共有多少工种，去掉重复值
select distinct job from emp;
练习三：
--问题：什么时候使用连接查询？
--当需要显示的数据来源于多个表时,使用连接查询。
1,查询员工姓名和所在部门的名称(2种写法)
--等值连接
select emp.ename,dept.dname from emp ,dept where emp.deptno=dept.deptno;
--内联接 inner join on
select a.ename,b.dname from emp a inner join dept b on a.deptno=b.deptno;
2,查询员工姓名和所在部门的名称,要求部门编号为30(2种 )
select a.ename,b.dname from emp a inner join dept b on a.deptno=b.deptno where a.deptno=30;
select a.ename,b.dname from emp a,dept b where a.deptno=b.deptno and a.deptno=30;
3,查询员工姓名和其直接上级的姓名,要求没有经理的员工也查询出来(3种写法)
emp员工表 字段内容如下:
empno 员工号
ename 员工姓名
job 工作
mgr 上级编号
hiredate 受雇日期
sal 薪金
comm 佣金
deptno 部门编号
例如："SMITH"的上级是"ORD"
empno：7369 ename：SMITH mgr：7902
empno：7902 ename：FORD
select '员工'||a.ename||'的上级是'||b.ename from emp a left outer join emp b on a.mgr=b.empno;
select '员工'||a.ename||'的上级是'||b.ename from emp a,emp b where a.mgr=b.empno(+)
select '员工'||a.ename||'的上级是'||b.ename from emp a,emp b where a.mgr=b.empno;
--1,查询工资高于SMITH工资的所有员工
select * from emp where sal>(select sal from emp where enAme='SMITH');
--2,查询工资高于公司平均工资的所有员工
select * from emp where sal>(select avg(sal) from emp);
--附加题
--> >= < <= = != <> ^= 后面只能跟一个值,
--3,查询工资高于本部门平均工资的所有员工
select * from emp a where a.sal>(select avg(sal) from emp b where b.deptno=a.deptno);
--4,查询本部门最高工资的员工(2种方法)
方法1,使用嵌套子查询（非关联子查询)
select * from emp a where (a.deptno,a.sal) in (select deptno,max(sal) from emp group by deptno);
方法2,使用关联子查询
select * from emp a where a.sal=(select max(sal) from emp where deptno=a.deptno);
--5,统计每个部门的信息和人数
select a.*,(select count(*) from emp where deptno=a.deptno) 人数 from dept a;
select a.deptno,b.dname,b.loc,count(*) 人数 from emp a,dept b where a.deptno=b.deptno group by a.deptno,b.dname,b.loc;
--6,统计每个部门工资在(500-1000)、(1000-3500)、(3500-7000) 的人数
select a.*,
(select count(*) from emp b where b.deptno=a.deptno and sal>500 and sal<=1000) "工资范围：500-1000 的人数",
(select count(*) from emp b where b.deptno=a.deptno and sal>1000 and sal<=3500) "工资范围：1000-3500 的人数",
(select count(*) from emp b where b.deptno=a.deptno and sal>3500 and sal<=7000) "工资范围：3500-7000 的人数"
from dept a ;
