【SQL开源代码栏目提醒】:网学会员为需要SQL开源代码的朋友们搜集整理了createshirtsdbforch10.sql相关资料,希望对各位网友有所帮助!
-- CREATE USER
CREATE USER "TSHIRTS" PROFILE "DEFAULT" IDENTIFIED BY "tshirts" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "USERS" ACCOUNT UNLOCK;
GRANT "CONNECT" TO "TSHIRTS";
-- Give rols to the user
ALTER USER "TSHIRTS" DEFAULT ROLE ALL;
GRANT "DBA" TO "TSHIRTS";
ALTER USER "TSHIRTS" DEFAULT ROLE ALL;
-- Give Tablespace to
GRANT UNLIMITED TABLESPACE TO "TSHIRTS";
-- Grant resource per
mission to TSHIRTS
GRANT "RESOURCE" TO "TSHIRTS";
connect TSHIRTS/TSHIRTS@TSHIRTS;
-- CREATE TABLE CUSTOMER
CREATE TABLE TSHIRTS.CUSTOMER
(
CUSTOMER_ID INTEGER NOT NULL,
FIRST_NAME VARCHAR(30) NOT NULL,
LAST_NAME VARCHAR(30) NOT NULL,
MIDDLE_NAME VARCHAR(20) ,
ADDRESS_1 VARCHAR(50) NOT NULL,
ADDRESS_2 VARCHAR(50) ,
CITY VARCHAR(50) NOT NULL,
STATE CHAR(2) NOT NULL,
ZIP_CODE VARCHAR(5),
PHONE VARCHAR(12),
EMAIL VARCHAR(50) NOT NULL UNIQUE,
PRIMARY KEY(CUSTOMER_ID)
);
-- create table STATE
CREATE TABLE TSHIRTS.STATE
(
CODE CHAR(2) NOT NULL,
STATE_NAME VARCHAR(50) NOT NULL
) ;
-- create table ORDERS
CREATE TABLE TSHIRTS.ORDERS(
ORDER_ID INTEGER NOT NULL,
CUSTOMER_ID_FK INTEGER NOT NULL,
ORDER_DATE DATE,
SHIP_INSTRUCT VARCHAR(100),
SHIP_DATE DATE,
SHIP_WEIGHT FLOAT,
SHIP_CHARGE FLOAT,
PAID_DATE DATE,
PRIMARY KEY(ORDER_ID),
FOREIGN KEY(CUSTOMER_ID_FK) REFERENCES TSHIRTS.CUSTOMER(CUSTOMER_ID)
);
-- create table ITEM
CREATE TABLE TSHIRTS.ITEM(
ITEM_ID INTEGER NOT NULL,
ORDER_ID_FK INTEGER NOT NULL,
QUANTITY INTEGER,
TOTAL_PRICE FLOAT,
PRIMARY KEY(ITEM_ID),
FOREIGN KEY(ORDER_ID_FK) REFERENCES TSHIRTS.ORDERS(ORDER_ID)
);
-- create table SUPPLIER
CREATE TABLE TSHIRTS.SUPPLIER(
SUPPLIER_ID INTEGER NOT NULL,
NAME VARCHAR(30) ,
ADDRESS_1 VARCHAR(30) ,
ADDRESS_2 VARCHAR(30) ,
CITY VARCHAR(40) ,
STATE VARCHAR(2) ,
ZIP VARCHAR(5) ,
PRIMARY KEY(SUPPLIER_ID)
);
--- CREATE TABLE TSHIRT
CREATE TABLE "TSHIRTS"."TSHIRT"
("ITEM_ID_FK" NUMBER(8),
"SHIRT_ID" NUMBER(8),
"SIZE" VARCHAR2(5),
UNIT_PRICE FLOAT,
"COLOR" VARCHAR2(10),
"DESIGN_FRONT" VARCHAR2(10),
"DESIGN_BACK" VARCHAR2(10),
"PICTURE_FRONT" BLOB,
"PICTURE_BACK" BLOB,
"SUPPLIER_ID_FK" NUMBER(8),
FOREIGN KEY("ITEM_ID_FK") REFERENCES "TSHIRTS"."ITEM"("ITEM_ID"),
FOREIGN KEY("SUPPLIER_ID_FK") REFERENCES "TSHIRTS"."SUPPLIER"("SUPPLIER_ID"),
PRIMARY KEY("SHIRT_ID")
) TABLESPACE "USERS";
-- create type CARD_TYPE
CREATE TYPE CHARGE_CARD_TYPE AS OBJECT(
"CARD_TYPE" VARCHAR2(6) ,
"CARD_NAME" VARCHAR2(8) ,
"CARD_NUMBER" VARCHAR2(20) ,
"EXPIRE_DATE" DATE ,
"CARD_CONFIRMATION_NUMBER" VARCHAR2(20)
);
/
--- CREATE TABLE CREDIT
CREATE TABLE "TSHIRTS"."CREDIT"(
"CUSTOMER_ID_FK" NUMBER(8),
"ORDER_ID_FK" NUMBER(8),
"PAYMENT_INFORMATION" CHARGE_CARD_TYPE,
FOREIGN KEY("CUSTOMER_ID_FK") REFERENCES "TSHIRTS"."CUSTOMER"("CUSTOMER_ID"),
FOREIGN KEY("ORDER_ID_FK") REFERENCES "TSHIRTS"."ORDERS"("ORDER_ID")
) TABLESPACE "USERS";
--- CREATE TABLE AUTHENTICATION
CREATE TABLE TSHIRTS.AUTHENTICATION( CUSTOMER_ID_FK INTEGER NOT NULL, PASSWORD VARCHAR(20) NOT NULL, AUTH_TYPE VARCHAR(20) NOT NULL , FOREIGN KEY(CUSTOMER_ID_FK) REFERENCES TSHIRTS.CUSTOMER(CUSTOMER_ID)
);
-- Insert data into Tables
--- Load data into CUSTOMER table
INSERT INTO TSHIRTS.CUSTOMER VALUES( '101','Rajiv','Pauli','','213 Erstwild Court','Apt 30','Sunnyvale','CA','94086','408-789-8075','rp@rpuli.com');
INSERT INTO TSHIRTS.CUSTOMER VALUES( '102','Carole','Sandler','S','785 Geary St','','San Francisco','CA','94117','415-822-1289','cs@sandler.com');
INSERT INTO TSHIRTS.CUSTOMER VALUES( '103','Philip','Currie','','654 Poplar','Apt 50','Wahsington','CA','94303','415-328-4543','pc@pCurrie.com');
INSERT INTO TSHIRTS.CUSTOMER VALUES( '104','Tony','Higgins','H','East Shopping Cntr.','422 Bay Road','Altanta','CA','94026','415-368-1100','th@tHiggins.com');
INSERT INTO TSHIRTS.CUSTOMER VALUES( '105', 'Rio','Vector','','1899 La Loma Drive','','Los Altos','CA','94022','415-776-3249','rv@rvictor.com');
INSERT INTO TSHIRTS.CUSTOMER VALUES( '106','Erick','Watson','','1143 Carver Place','','Mountain View','CA','94063','415-389-8789','ew@ewaston.com');
INSERT INTO TSHIRTS.CUSTOMER VALUES( '107','Charles','Ream','','41 Jordan Avenue','','Palo Alto','CA','94304','415-356-9876','cr@cReam.com');
INSE