/*
 * 范例名稱：INSERT  與	子查詢基礎
 * 文件名稱：sm_emp_promote.txt
 */
create table sm_emp_promote
(PromEmpID char(10) primary key,
Name   varchar2(10),
salary number(8,2)
);

--從sm_emp向sm_emp_promote表插入數據

insert into sm_emp_promote (PromEmpID,Name,salary)
select EmpID,Name,salary from sm_emp where Name in ('張飛','關羽','劉備');

select * from sm_emp_promote;



---------- sm_emp_sale_data.txt ----------
/*
 * 范例名稱：INSERT  與	子查詢
 * 文件名稱：sm_emp_sale_data.txt
 */

--insert data need in chap10 sm_saleorderlist,sm_emp

DROP TABLE sm_saleorderlist CASCADE CONSTRAINT;
CREATE TABLE sm_saleorderlist(
    TransactionID NUMBER(10) NOT NULL UNIQUE,
    TotalPrice NUMBER(7,2) NOT NULL,
    EmployID CHAR(10) NOT NULL,
    SaleTime DATE NOT NULL,
    PRIMARY KEY(TransactionID));

--if sm_emp exists,
DROP TABLE sm_emp;

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',100000,'62634546');
COMMIT;

--data sm_emp
EMPID      NAME           SALARY TELNO
---------- ---------- ---------- --------
000000001  張飛            10000 62613546
000000002  關羽              100 62613547
0000000003 劉備            10000 62613547
0000000007 007                   62634567

--data sm_saleorderlist
INSERT INTO sm_saleorderlist VALUES(1,100.10,'0000000001','09_9月_02');
INSERT INTO sm_saleorderlist VALUES(2,222.20,'0000000001','09_9月_02');
INSERT INTO sm_saleorderlist VALUES(3,300.10,'0000000002','09_9月_02');
INSERT INTO sm_saleorderlist VALUES(4,100.10,'0000000003','09_9月_02');
COMMIT;

--sm_saleorderlog:用于保存售貨信息.sm_saleorderlist + 對應銷售人員的信息
DROP TABLE sm_saleorderlog ;
CREATE TABLE sm_saleorderlog (
    TransactionID NUMBER(10) NOT NULL UNIQUE,
    TotalPrice NUMBER(7,2) NOT NULL,
    EmployID CHAR(10) NOT NULL,
    EmpName   VARCHAR2(10),
    TelNo  CHAR(8),
    SaleTime DATE NOT NULL,
    PRIMARY KEY(TransactionID));



 
  
---------- sm_emp_promote_update.txt ----------
/*
 * 范例名稱：UPDATE  與	子查詢
 * 文件名稱：sm_emp_promote_update.txt
 */


--首先插入提升人員
drop table sm_emp_promote;
create table sm_emp_promote
(PromEmpID char(10) primary key,
Name   varchar2(10),
salary number(8,2)
);


insert into sm_emp_promote (PromEmpID,Name,salary)
select EmpID,Name,salary from sm_emp where Name in ('張飛','關羽','劉備');

SELECT * FROM sm_emp;


--更改提升人員工資為所有員工平均工資的兩倍
SELECT AVG(salary)*2 FROM sm_emp;

UPDATE sm_emp_promote SET salary
= (SELECT AVG(salary)*2 FROM sm_emp);

UPDATE sm_emp_promote SET salary
= SELECT AVG(salary)*2 FROM sm_emp;
ERROR 位于第 2 行:
ORA-00936: 缺少表達式.()問題！

SELECT * FROM sm_emp_promote;

--多字段ok
UPDATE sm_emp_promote SET (salary,name)
=( SELECT AVG(salary)*2,max(name) FROM sm_emp);

---------- sm_emp_update.txt ----------
/*
 * 范例名稱：UPDATE  與	子查詢練習
 * 文件名稱：sm_emp_update.txt
 */

以0000000007員工總銷售額的1/8作為
0000000007員工的工資。
說明：2個表。
sm_saleorderlist：銷售信息
Sm_emp




---------- sm_createtab.txt ----------
/*
 * 范例名稱：CREATE 與子查詢
 * 文件名稱：sm_createtab.txt
 */

CREATE TABLE sm_saleorderlogwk AS
SELECT * FROM sm_saleorderlog;

SELECT * FROM sm_saleorderlogwk;

DROP TABLE sm_saleorderlogwk;



---------- sm_rename.txt ----------
/*
 * 范例名稱：重新命名表
 * 文件名稱：sm_rename.txt
 */

RENAME sm_saleorderlogWK TO sm_saleorderlogtemp;


  
---------- sm_emp_alter.txt ----------
/*
 * 范例名稱：增加列
 * 文件名稱：sm_emp_alter.txt
 */

--增加雇員住址字段
ALTER TABLE sm_emp ADD address  VARCHAR2(50) NOT NULL;

--如果表有紀錄，出
--ERROR 位于第 1 行:
--ORA-01758: 要添加法定 (NOT NULL) 列，則表必須為空

ALTER TABLE sm_emp ADD address  VARCHAR2(50) ;



---------- sm_emp_altertab_modify.txt ----------
/*
 * 范例名稱：更改列的數據類型
 * 文件名稱：sm_emp_altertab_modify.txt
 */

ALTER TABLE sm_emp MODIFY address VARCHAR2(20);
DESC sm_emp;

--insert data
INSERT INTO sm_emp VALUES('0000000008','008',100,'65341234','12345678901234567890');

SELECT * FROM sm_emp;

--減少長度, column必須為empty
ALTER TABLE sm_emp MODIFY address VARCHAR2(10);
-- update sm_emp set address='123' where address='12345678901234567890';
--是不是紀錄過長，改為小，試試
update sm_emp set address=null where address='123';
-- update sm_emp set address='' where address='123';

--ALTER TABLE sm_emp MODIFY address VARCHAR2(10);


--將address 改為null;
update sm_emp set address= null where address='123';
ALTER TABLE sm_emp MODIFY address VARCHAR2(10);
--ok.null是empty
--改回20為下一步測試
alter table sm_emp modify address varchar2(20);

--將null改為'';
update sm_emp set address='' where address is null;
ALTER TABLE sm_emp MODIFY address VARCHAR2(10);
--ok!
--小結:
--null ,''都是empty

ALTER TABLE sm_emp MODIFY address DATE;
--ORA-01439: 要更改數據類型，則要修改的列必須為空 (empty)
--什么是empty？
update sm_emp set address=null where empid='0000000008';
update sm_emp set address='' where empid='0000000008'


--改回為VARCHAR2(20);為改變字段的null屬性做準備
ALTER TABLE sm_emp MODIFY address VARCHAR2(20);
----------

--null
update sm_emp set address= null;


--update sm_emp set address='';在此時null 與''認為相同。

ALTER  TABLE  sm_emp  
MODIFY address  NOT    NULL;
--ORA-02296: 無法啟用  - 找到空值

UPDATE sm_emp SET address='北京';
ALTER  TABLE  sm_emp  
MODIFY address  NOT    NULL;

DESC sm_emp;

 

---------- sm_rename_column.txt ----------
/*
 * 范例名稱：更改字段名
 * 文件名稱：sm_rename_column.txt
 */

--modify 不能用于更改字段名
--alter table sm_emp modify address sm_address；err!
drop table sm_temp;

CREATE TABLE  sm_temp AS
SELECT empid,name,salary,telno,address sm_address
FROM sm_emp;


CREATE TABLE  sm_temp1(id,name,sal,telno) AS
SELECT empid,name,salary,telno 
FROM sm_emp;

desc sm_temp;

DROP TABLE sm_emp;

RENAME sm_temp TO sm_emp;

DESC sm_emp;