script_69.txt

orale培训教材包括了所有的sql说明和实例

--
---------- data_insert.tx ----------
/*
 * 范例名称：建立测试数据
 * 文件名称：data_insert.tx
 */

drop table sm_emp cascade constraint;
CREATE table sm_emp
(EmpID CHAR(10) PRIMARY KEY,
Name   VARCHAR2(10),
salary NUMBER(8,2),
TelNo  CHAR(8));

INSERT INTO sm_emp VALUES('0000000001','张飞',100000,'62613546');
INSERT INTO sm_emp VALUES('0000000002','关羽',100000,'62613546');
INSERT INTO sm_emp VALUES('0000000003','刘备',100000,'62613546');
INSERT INTO sm_emp VALUES('0000000007','007',1000,'62634546');
COMMIT;

drop table sm_emp_sex;

create table sm_emp_sex
(EmpID char(10) primary key,
Name   varchar2(10),
salary number(8,2),
TelNo  char(8),
sex  char(2));

INSERT INTO sm_emp_sex VALUES('0000000001','张飞',100000,'62613546','男');

--先运行data_insert.txt建立数据

---------- concept.txt ----------
/*
 * 范例名称：逻辑运算
 * 文件名称：concept.txt
 */
--以scott/tiger登录一个sqlplus
connect;
scott/tiger;
select * from tab;
drop table person;
--建立person
create table person(
name  varchar2(10),
age   number(3));
select * from tab;

--以system/manager 登录，运行cuser.sql脚本，生成用户user1/user1
connect system/manager
@cuser.sql
--以user1/user1登录另一个sqlplus. 
connect user1/user1
select * from tab;


Select   1+1  from  emp;
Select   1+1    from  dual;
Select    *   from  dual;


---------- sm_union.txt ----------
/*
 * 范例名称：逻辑运算
 * 文件名称：sm_union.txt
 */

select * from sm_emp;

select * from sm_emp_sex;

 select * from sm_emp
 union
 select * from sm_emp_sex;

--ERROR 位于第 1 行:
--ORA-01789: 查询块具有不正确的结果列数

select * from sm_emp
intersect
select * from sm_emp_sex;


select * from sm_emp
union
select empid,name,salary,telno from sm_emp_sex;
--ok

select * from sm_emp
union
select empid,name,salary,sex from sm_emp_sex;
--也ok。但商业含义混乱。

select empid,name from sm_emp
UNION
select empid,name from sm_emp_sex;
--张飞只一次。


  
---------- sm_unionall.txt ----------
/*
 * 范例名称：逻辑运算
 * 文件名称：sm_unionall.txt
 */

select empid,name from sm_emp
UNION ALL
select empid,name from sm_emp_sex;
--张飞2次。



---------- sm_intersect.txt ----------
/*
 * 范例名称：逻辑运算
 * 文件名称：sm_intersect.txt
 */

select * from sm_emp
intersect
select empid,name,salary,telno from sm_emp_sex;

select * from sm_emp;

select empid,name,salary,telno from sm_emp_sex;

select empid,name from sm_emp
intersect
select empid,name from sm_emp_sex;
--ok

---------- char.txt----------
/*
 * 范例名称：char field intersect:char字段对intersect的影响
 * 文件名称：char.txt
 */

 --------------------------------------------------------
 --char field intersect:char字段对intersect的影响！
 drop table sm_emp_char;

 create table sm_emp_char
 (EmpID CHAR(10) PRIMARY KEY,
Name   CHAR(10),  --由varchar2变为char
salary NUMBER(8,2),
TelNo  CHAR(8));

INSERT INTO sm_emp_char VALUES('0000000001','张飞',100000,'62613546');
INSERT INTO sm_emp_char VALUES('0000000002','关羽',100000,'62613546');
--union
select * from sm_emp union select * from sm_emp_char;
--intersect
select * from sm_emp 
intersect
select * from sm_emp_char;



---------- sm_minus.txt ----------
/*
 * 范例名称：逻辑运算
 * 文件名称：sm_minux.txt
 */

select * from sm_emp
minus
select empid,name,salary,telno from sm_emp_sex;

select * from sm_emp;

select empid,name,salary,telno from sm_emp_sex;


---------------------------------------------------------------------------
练习：超市库存商品资料表SM_ItemList ，超市预定商品资料表SM_ItemBooked。
SM_ItemList：商品编号，商品名称 ，商品价格 ，商品单位 
SM_ItemBooked：商品编号，商品名称 

求出：
商品库中已有，并且已被预定的商品
求商品库中已有，但没被预定的商品
已被预定,但商品库中尚没有的商品
商品库中已有及被预定的商品一共有哪些？ 




---------- sm_subquery.txt ----------
/*
 * 范例名称：编写子查询
 * 文件名称：sm_subquery.txt
 */

select * from sm_emp;
select * from sm_emp_sex;

--单行子查询
select empid,name from sm_emp where empid=
(select empid from sm_emp_sex where sex='男');


--如果在sm_emp_sex 再insert 一条sex为‘男’，结果如何？
--INSERT INTO sm_emp_sex VALUES('0000000002','关羽',100000,'62613546','男');


select empid,name from sm_emp where (empid,name) 
NOT IN select empid,name from sm_emp_sex;
--ERROR 位于第 2 行:
--ORA-00920: 无效的关系运算符
--原因？

--查找sm_emp中有的，但sm_emp_sex中没有的人员
select empid,name from sm_emp where (empid,name) 
NOT IN (select empid,name from sm_emp_sex);

--()问题

--取出雇员中工资高于007的
select * from sm_emp where salary >
 (select salary from sm_emp where name='007'); 

--取出雇员中工资高于平均水平的
select empid,name,salary from sm_emp where salary >
 (select avg(salary) from sm_emp);
--ok
select empid,name,salary from sm_emp where salary > select avg(salary) from sm_emp;
--Err.()问题



 
---------- GROUP_BY_后用函数.txt ----------
/*
 * 范例名称：分析查询效率：having  ,where
 * 文件名称：GROUP_BY_后用函数.txt
 */

--求采购信息表（plsql101_purchase）
名称      采购信息表plsql101_purchase                     
----------------------------
PRODUCT_NAME        商品名称           
SALESPERSON         经手人(编号)           
PURCHASE_DATE       采购日期           
QUANTITY            采购数量        

--求各商品：采购总量,每次采购的平均数量，一共有多少次采购，单次采购的最低数量，单次采购的最高数量


--按年按产品统计采购信息：并取出2002年的采购情况
--在group语句中使用函数，完成对求各年的统计结果。由此，可以实现年报月报等。
--使用
SELECT  TO_CHAR(PURCHASE_DATE,'YY') 年, product_name,
       SUM(quantity), --采购总量
       AVG(quantity) , --每次采购的平均数量
       COUNT(quantity), --一共有采购多少次
       MIN(quantity) , --单次采购的最低数量
       MAX(quantity)  --单次采购的最高数量
FROM   plsql101_purchase
GROUP BY product_name,(TO_CHAR(PURCHASE_DATE,'YY'))
having TO_CHAR(PURCHASE_DATE,'YY') ='02';


--方法2：先使用where子句选择出符合要求的纪录再进行group  by 分组
SELECT  TO_CHAR(PURCHASE_DATE,'YY') 年, product_name,
       SUM(quantity),
       AVG(quantity) ,
       COUNT(quantity),
       MIN(quantity) ,
       MAX(quantity) 
FROM   plsql101_purchase
where TO_CHAR(PURCHASE_DATE,'YY') = '03'
GROUP BY product_name,(TO_CHAR(PURCHASE_DATE,'YY'));
--哪个方法效率高？

SELECT  product_name,
       SUM(quantity),
       AVG(quantity) ,
       COUNT(quantity),
       MIN(quantity) ,
       MAX(quantity) 
FROM   plsql101_purchase
where TO_CHAR(PURCHASE_DATE,'YY') = '03'
GROUP BY product_name;

select empid,name from sm_emp where empid=
(select sex from sm_emp_sex where sex='男' group by sex);