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