sm_emp_sale_data答案.txt

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

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

INSERT INTO sm_saleorderlog
SELECT sm_saleorderlist.TransactionID,sm_saleorderlist.TotalPrice ,sm_saleorderlist.EmployID ,
sm_emp.Name,sm_emp.TelNo,
sm_saleorderlist.SaleTime
FROM sm_saleorderlist,sm_emp
WHERE sm_saleorderlist.EmployID=sm_emp.empid;

select * from sm_saleorderlog;