ORACLE SQL练习（一）
CREATE TABLE EMPLOYEE_TBL(
EMP_ID VARCHAR2(9) NOT NULL,
LAST_NAME VARCHAR2(15) NOT NULL,
FIRST_NAME VARCHAR2(15) NOT NULL,
MIDDLE_NAME VARCHAR2(15),
ADDRESS VARCHAR2(30) NOT NULL,
CITY VARCHAR2(15) NOT NULL,
STATE CHAR(2) NOT NULL,
ZIP INTEGER NOT NULL,
PHONE CHAR(10),
PAGER CHAR(10),
CONSTRAINT EMP_PK PRIMARY KEY(EMP_ID)
);
CREATE TABLE ORDERS_TBL
(
ORD_NUM VARCHAR2(10) NOT NULL PRIMARY KEY,
CUST_ID VARCHAR2(10) NOT NULL,
PROD_ID VARCHAR2(10) NOT NULL,
QTY INTEGER NOT NULL,
ORD_DATE DATE NOT NULL
);
CREATE TABLE PRODUCTS_TBL
(
PROD_ID VARCHAR2(10) NOT NULL PRIMARY KEY,
PROD_DESC VARCHAR2(40) NOT NULL,
COST DECIMAL(6,2) NOT NULL
);
CREATE TABLE EMPLOYEE_PAY_TBL
(
EMP_ID VARCHAR2(9) NOT NULL PRIMARY KEY,
POSITION VARCHAR2(15) NOT NULL,
DATE_HIRE DATE,
PAY_DATE DECIMAL(4,2),
DATE_LAST_RAISE DATE,
SALARY DECIMAL(8,2),
BNOUS DECIMAL(6,2),
CONSTRAINT EMP_FK FOREIGN KEY(EMP_ID) REFERENCES EMPLOYEE_TBL(EMP_ID)
);
CREATE TABLE CUSTOMER_TBL
(
CUST_ID VARCHAR2(10) NOT NULL PRIMARY KEY,
CUST_NAME VARCHAR2(30) NOT NULL,
CUST_ADDRESS VARCHAR2(20) NOT NULL,
CUST_CITY VARCHAR2(15) NOT NULL,
CUST_STATE CHAR(2) NOT NULL,
CUST_ZIP INTEGER NOT NULL,
CUST_PHONE CHAR(10),
CUST_FAX VARCHAR2(10)
);
INSERT INTO EMPLOYEE_TBL VALUES
('311549902', 'STEPHENS', 'TINA', 'DAWN','RR 3 BOX 17A', 'GREENWOOD',
'IN', '47890', '3178784465',NULL);
INSERT INTO EMPLOYEE_TBL VALUES
('442346889', 'PLEW', 'LINDA', 'CAROL', '3301 BEACON', 'INDIANAPOLIS',
'IN', '46224', '3172978990', NULL);
INSERT INTO EMPLOYEE_TBL VALUES
('213764555', 'GLASS', 'BRANDON', 'SCOTT', '1710 MAIN ST', 'WHITELAND',
'IN', '47885', '3178984321', '3175709980');
INSERT INTO EMPLOYEE_TBL VALUES
('313782439', 'GLASS', 'JACOB', NULL, '3789 WHITE RIVER BLVD',
'INDIANAPOLIS', 'IN', '45734', '3175457676','8887345678');
INSERT INTO EMPLOYEE_TBL VALUES
('220984332', 'WALLACE', 'MARIAH', NULL, '7889 KEYSTONE AVE',
'INDIANAPOLIS', 'IN', '46741', '3173325986', NULL);
INSERT INTO EMPLOYEE_TBL VALUES
('443679012', 'SPURGEON', 'TIFFANY', NULL, '5 GEORGE COURT',
'INDIANAPOLIS', 'IN', '46234', '3175679007', NULL);
EMPLOYEE_PAY_TBL
INSERT INTO EMPLOYEE_PAY_TBL VALUES
('311549902', 'MARKETING', TO_DATE('1999-05-23','YYYY-MM-DD'),NULL,TO_DATE('2009-05-01','YYYY-MM-DD'),'40000', NULL);
INSERT INTO EMPLOYEE_PAY_TBL VALUES
('442346889', 'TEAM LEADER', TO_DATE('2000-06-17','YYYY-MM-DD'), '14.75',
TO_DATE('2009-06-01','YYYY-MM-DD'), NULL, NULL);
INSERT INTO EMPLOYEE_PAY_TBL VALUES
('213764555', 'SALES MANAGER', TO_DATE('2004-08-14','YYYY-MM-DD'),NULL,
TO_DATE('2009-08-01','YYYY-MM-DD'), '30000', '2000');
INSERT INTO EMPLOYEE_PAY_TBL VALUES
('313782439', 'SALESMAN', TO_DATE('2007-06-28','YYYY-MM-DD'), NULL, NULL,
'20000', '1000');
INSERT INTO EMPLOYEE_PAY_TBL VALUES
('220984332', 'SHIPPER', TO_DATE('2006-07-22','YYYY-MM-DD'), '11.00',
TO_DATE('2009-07-01','YYYY-MM-DD'), NULL, NULL);
INSERT INTO EMPLOYEE_PAY_TBL VALUES
('443679012', 'SHIPPER', TO_DATE('2001-01-14','YYYY-MM-DD'), '15.00',
TO_DATE('2009-01-01','YYYY-MM-DD'), NULL, NULL);
INSERT INTO CUSTOMER_TBL VALUES
('232', 'LESLIE GLEASON', '798 HARDAWAY DR', 'INDIANAPOLIS',
'IN', '47856', '3175457690', NULL);
INSERT INTO CUSTOMER_TBL VALUES
('109', 'NANCY BUNKER', 'APT A 4556 WATERWAY', 'BROAD RIPPLE',
'IN', '47950', '3174262323', NULL);
INSERT INTO CUSTOMER_TBL VALUES
('345', 'ANGELA DOBKO', 'RR3 BOX 76', 'LEBANON', 'IN', '49967',
'7658970090', NULL);
INSERT INTO CUSTOMER_TBL VALUES
('090', 'WENDY WOLF', '3345 GATEWAY DR', 'INDIANAPOLIS', 'IN',
'46224', '3172913421', NULL);
INSERT INTO CUSTOMER_TBL VALUES
('12', 'MARYS GIFT SHOP', '435 MAIN ST', 'DANVILLE', 'IL', '47978',
'3178567221', '3178523434');
INSERT INTO CUSTOMER_TBL VALUES
('432', 'SCOTTYS MARKET', 'RR2 BOX 173', 'BROWNSBURG', 'IN',
'45687', '3178529835', '3178529836');
INSERT INTO CUSTOMER_TBL VALUES
('333', 'JASONS AND DALLAS GOODIES', 'LAFAYETTE SQ MALL',
'INDIANAPOLIS', 'IN', '46222', '3172978886', '3172978887');
INSERT INTO CUSTOMER_TBL VALUES
('21', 'MORGANS CANDIES AND TREATS', '5657 W TENTH ST',
'INDIANAPOLIS', 'IN', '46234', '3172714398', NULL);
INSERT INTO CUSTOMER_TBL VALUES
('43', 'SCHYLERS NOVELTIES', '17 MAPLE ST', 'LEBANON', 'IN',
'48990', '3174346758', NULL);
INSERT INTO CUSTOMER_TBL VALUES
('287', 'GAVINS PLACE', '9880 ROCKVILLE RD', 'INDIANAPOLIS',
'IN', '46244', '3172719991', '3172719992');
INSERT INTO CUSTOMER_TBL VALUES
('288', 'HOLLYS GAMEARAMA', '567 US 31 SOUTH', 'WHITELAND',
'IN', '49980', '3178879023', NULL);
INSERT INTO CUSTOMER_TBL VALUES
('590', 'HEATHERS FEATHERS AND THINGS', '4090 N SHADELAND AVE',
'INDIANAPOLIS', 'IN', '43278', '3175456768', NULL);
INSERT INTO CUSTOMER_TBL VALUES
('610', 'REGANS HOBBIES INC', '451 GREEN ST', 'PLAINFIELD', 'IN',
'46818', '3178393441', '3178399090');
INSERT INTO CUSTOMER_TBL VALUES
('560', 'ANDYS CANDIES', 'RR 1 BOX 34', 'NASHVILLE', 'IN',
'48756', '8123239871', NULL);
INSERT INTO CUSTOMER_TBL VALUES
('221', 'RYANS STUFF', '2337 S SHELBY ST', 'INDIANAPOLIS', 'IN',
'47834', '3175634402', NULL);
INSERT INTO ORDERS_TBL VALUES
('56A901', '232', '11235', '1', TO_DATE('2009-10-22','YYYY-MM-DD'));
INSERT INTO ORDERS_TBL VALUES
('56A917', '12', '907', '100', TO_DATE('2009-09-30','YYYY-MM-DD'));
INSERT INTO ORDERS_TBL VALUES
('32A132', '43', '222', '25', TO_DATE('2009-10-10','YYYY-MM-DD'));
INSERT INTO ORDERS_TBL VALUES
('16C17', '090', '222', '2', TO_DATE('2009-10-17','YYYY-MM-DD'));
INSERT INTO ORDERS_TBL VALUES
('18D778', '287', '90', '10', TO_DATE('2009-10-17','YYYY-MM-DD'));
INSERT INTO ORDERS_TBL VALUES
('23E934', '432', '13', '20', TO_DATE('2009-10-15','YYYY-MM-DD'));
INSERT INTO PRODUCTS_TBL VALUES
('11235', 'WITCH COSTUME', '29.99');
INSERT INTO PRODUCTS_TBL VALUES
('222', 'PLASTIC PUMPKIN 18 INCH', '7.75');
INSERT INTO PRODUCTS_TBL VALUES
('13', 'FALSE PARAFFIN TEETH', '1.10');
INSERT INTO PRODUCTS_TBL VALUES
('90', 'LIGHTED LANTERNS', '14.50');
INSERT INTO PRODUCTS_TBL VALUES
('15', 'ASSORTED COSTUMES', '10.00');
INSERT INTO PRODUCTS_TBL VALUES
('9', 'CANDY CORN', '1.35');
INSERT INTO PRODUCTS_TBL VALUES
('6', 'PUMPKIN CANDY', '1.45');
INSERT INTO PRODUCTS_TBL VALUES
('87', 'PLASTIC SPIDERS', '1.05');
INSERT INTO PRODUCTS_TBL VALUES
('119', 'ASSORTED MASKS', '4.95');
页:
[1]