Oracle: 四、对scott用户的基本查询操作（上篇）

内容简介

　　1，Scott实例用户（Oracle官方提供）。2，Scott用户单词备注。3，对scott用户的查询操作。

技术与环境

操作系统:	windows	语言类别:	SQL之PL/SQL
thankyou:	sunshine, 谢谢你的默默付出	数据库:	Oracle
学习软件:	Oracle 10g
课程总策划:	yuanbo	English name:	sunshine
个人主页:	http://www.cnblogs.com/ylbtech/
科研团队:	ylbtech	教研团队:	ylbtech

1，scott实例使用（Oracle官方提供）

--==================================
---ylb：Oracle
---13:54 2011-12-30
---Oracle官方提供的用户
--==================================
 
Rem Copyright (c) 1990 by Oracle Corporation
Rem NAME
REM    UTLSAMPL.SQL
Rem  FUNCTION
Rem  NOTES
Rem  MODIFIED
Rem gdudey    06/28/95 -  Modified for desktop seed database
Rem glumpkin   10/21/92 -  Renamed from SQLBLD.SQL
Rem blinden   07/27/92 -  Added primary and foreign keys to EMP and DEPT
Rem rlim    04/29/91 -       change char to varchar2
Rem mmoore    04/08/91 -       use unlimited tablespace priv
Rem pritto    04/04/91 -       change SYSDATE to 13-JUL-87
Rem   Mendels  12/07/90 - bug 30123;add to_date calls so language independent
Rem
rem
rem $Header: utlsampl.sql 7020100.1 94/09/23 22:14:24 cli Generic<base> $ sqlbld.sql
rem
SET TERMOUT OFF
SET ECHO OFF
rem CONGDON    Invoked in RDBMS at build time.  29-DEC-1988
rem OATES:     Created: 16-Feb-83
GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY TIGER;
ALTER USER SCOTT DEFAULT TABLESPACE USERS;
ALTER USER SCOTT TEMPORARY TABLESPACE TEMP;
CONNECT SCOTT/TIGER
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;
SET TERMOUT ON
SET ECHO ON

2，Scott用户内容单词备注

emp员工表字段内容如下:

empno  员工号
ename  员工姓名
job  工作
mgr  上级编号
hiredate 受雇日期
sal  薪金
comm  佣金
deptno  部门编号

dept 部门表字段内容如下：
deptno 部门号
ename 部门名称
loc 地方

bonus 奖金表字段内容如下：

ename 员工姓名
job 工作名称
sal 薪金
comm 佣金

3，Demo scott用户操作

--======================================
--YLB：ORACLE
--15:23 2011-12-30
--1，ORACLE查询操作
--======================================
 
clear screen;
connect system/system
drop user scott cascade;
create user scott identified by tiger;
grant connect,resource to scott;
connect scott/tiger
show user;
--创建用员工表
create table emp
(
empid number(4),--编号
ename varchar(20),--姓名
job varchar(20), --工作 
mgr number(4),  --上级编号
hiredate date,  --受雇日期
sal number(7,2), --薪金
comm number(7,2), --佣金
deptno number(2) --部门编号
 );
insert into emp values(7369,'SMITH','CLERK',7902,to_date('12/17/1980','mm/dd/yyyy'),800,null,20);
insert into emp values(7499,'ALLEN','SALESMAN',7698,to_date('02/20/1981','mm/dd/yyyy'),1600,300,30);
insert into emp values(7521,'WARD','SALESMAN',7698,to_date('02/22/1981','mm/dd/yyyy'),1250,500,30);
insert into emp values(7566,'JONES','MANAGER',7839,to_date('04/02/1981','mm/dd/yyyy'),2975,null,20);
insert into emp values(7654,'MARTIN','SALESMAN',7698,to_date('09/28/1981','mm/dd/yyyy'),1250,1400,30);
insert into emp values(7698,'BLAKE','MANAGER',7839,to_date('05/01/1981','mm/dd/yyyy'),2850,null,30);
insert into emp values(7782,'CLARK','MANAGER',7839,to_date('06/09/1981','mm/dd/yyyy'),2450,null,10);
insert into emp values(7788,'SCOTT','ANALYST',7566,to_date('04/19/1987','mm/dd/yyyy'),3000,null,20);
insert into emp values(7839,'KING','PRESIDENT',null,to_date('11/17/1981','mm/dd/yyyy'),5000,null,10);
insert into emp values(7844,'TURNER','SALESMAN',7698,to_date('09/08/1981','mm/dd/yyyy'),1500,0,30);
insert into emp values(7876,'ADAMS','CLERK',7788,to_date('05/23/1987','mm/dd/yyyy'),1100,null,20);
insert into emp values(7900,'JAMES','CLERK',7698,to_date('12/03/1981','mm/dd/yyyy'),950,null,30);
insert into emp values(7902,'FORD','ANALYST',7566,to_date('12/03/1981','mm/dd/yyyy'),3000,null,20);
insert into emp values(7934,'MILLER','CLERK',7782,to_date('01/23/1982','mm/dd/yyyy'),1300,null,10);
commit;
 
 
 ---下面是查询操作
 
--1.选择部门30中的所有员工.
select * from emp where deptno=30;
--2.列出所有办事员(CLERK)的姓名，编号和部门编号.
select ename,empid,deptno from emp 
where job='CLERK'
--3.找出佣金高于薪金的员工.
select ename,empid from emp
where comm>sal;
--4.找出佣金高于薪金的60%的员工.
select empid,ename 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.找出部门10中所有经理(MANAGER),部门20中所有办事员(CLERK)
--,既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详
细资料.
--分析
select * from emp
where deptno=10 and job='MANAGER' or deptno=20 and 
job='CLERK';
select * from emp
where job not in('MANAGER','CLERK') and sal>=2000;
--结论
select * from emp
where deptno=10 and job='MANAGER' or deptno=20 and 
job='CLERK'
or job not in('MANAGER','CLERK') and sal>=2000;

--7.找出收取佣金的员工的不同工作.
SELECT DISTINCT JOB FROM EMP 
WHERE COMM IS NOT NULL;
--p: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 hiredate from emp where hiredate < sysdate-;
select ename from emp where hiredate < add_months(sysdate,-
12*12);
SELECT EMPID,ENAME FROM EMP
WHERE HIREDATE <ADD_MONTHS(SYSDATE,-12*12);
SELECT EMPID,ENAME FROM EMP
WHERE ADD_MONTHS(SYSDATE,12*12)> SYSDATE;
--z:11.以首字母大写的方式显示所有员工的姓名.
select initcap(ename) from emp;
--12.显示正好为5个字符的员工的姓名.
select ename from emp where ename like '_____';
--通配符
--%
--_
--[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%';
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年服务年限的员工的姓名和受雇日期.
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 * FROM EMP
ORDER BY TO_CHAR(HIREDATE,'MM') ASC,TO_CHAR(HIREDATE,'YYYY')
ASC;
SELECT * FROM EMP;
--21.显示在一个月为30天的情况所有员工的日薪金,忽略余数.
SELECT FLOOR(SAL/30) FROM EMP;
--22.找出在(任何年份的)2月受聘的所有员工。
SELECT * FROM EMP
WHERE TO_CHAR(HIREDATE,'MM')=2;

--P:23.对于每个员工,显示其加入公司的天数.
SELECT TO_CHAR(SYSDATE,'DDD') FROM DUAL;
--24.显示姓名字段的任何位置包含"A"的所有员工的姓名.
SELECT ENAME FROM EMP
WHERE ENAME LIKE '%A%';
--25.以年月日的方式显示所有员工的服务年限. (大概)
--年
SELECT FLOOR(FLOOR(MONTHS_BETWEEN(SYSDATE,HIREDATE))/12) FROM 
EMP;
--月
2011-2-15
-
2011-12-30
SELECT MOD(FLOOR(MONTHS_BETWEEN(SYSDATE,HIREDATE)),12) FROM 
EMP;
--日
SELECT TO_CHAR(HIREDATE,'DD') FROM EMP;
SELECT TO_CHAR(SYSDATE,'DD') FROM DUAL;
--
SELECT (TO_CHAR(SYSDATE,'DD')-TO_CHAR(HIREDATE,'DD')) FROM 
EMP;
--字符串链接
SELECT FLOOR(FLOOR(MONTHS_BETWEEN(SYSDATE,HIREDATE))/12)||'年
' FROM EMP;

作者：ylbtech
出处：http://ylbtech.cnblogs.com/
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。