【SQL开源代码栏目提醒】:以下是网学会员为您推荐的SQL开源代码-createshirtsdb.sql,希望本篇文章对您学习有所帮助。
-- Create Schema
CREATE SCHEMA TSHIRTS;
-- Create TABLES
-- 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) NOT NULL,
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 INTEGER,
SHIRT_ID INTEGER,
SHIRT_SIZE VARCHAR(5),
UNIT_PRICE FLOAT,
COLOR VARCHAR(10),
DESIGN_FRONT VARCHAR(10),
DESIGN_BACK VARCHAR(10),
PICTURE_FRONT VARCHAR(50), -- this type is not in Cloud Scape
PICTURE_BACK VARCHAR(50), -- this type is not in Cloud Scape
SUPPLIER_ID_FK INTEGER,
-- Unit price need to enter here.
FOREIGN KEY(ITEM_ID_FK) REFERENCES TSHIRTS.ITEM(ITEM_ID),
FOREIGN KEY(SUPPLIER_ID_FK) REFERENCES TSHIRTS.SUPPLIER(SUPPLIER_ID),
PRIMARY KEY(SHIRT_ID)
);
-- create type CARD_TYPE
CREATE TABLE TSHIRTS.CHARGE_CARD_TYPE (
CARD_TYPE VARCHAR(6) NOT NULL,
CARD_NAME VARCHAR(8) NOT NULL,
CARD_NUMBER VARCHAR(20) NOT NULL,
EXPIRE_DATE DATE NOT NULL ,
CARD_CONFIRMATION_NUMBER VARCHAR(20),
PRIMARY KEY(CARD_NUMBER)
);
--- CREATE TABLE CREDIT
CREATE TABLE TSHIRTS.CREDIT(
CUSTOMER_ID_FK INTEGER NOT NULL,
ORDER_ID_FK INTEGER NOT NULL,
CARD_NUMBER_FK VARCHAR(20) NOT NULL ,
FOREIGN KEY(CUSTOMER_ID_FK) REFERENCES TSHIRTS.CUSTOMER(CUSTOMER_ID),
FOREIGN KEY(ORDER_ID_FK) REFERENCES TSHIRTS.ORDERS(ORDER_ID),
FOREIGN KEY(CARD_NUMBER_FK) REFERENCES TSHIRTS.CHARGE_CARD_TYPE(CARD_NUMBER)
);
--- 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)
);
--- 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');
INSERT INTO TSHIRTS.CUSTOMER VALUES( '108','Donald','Duck','','587 Alvarado','','Redwood City','CA','94063','415-544-8729','dd@dDuck.com');
INSERT INTO TSHIRTS.CUSTOMER VALUES( '109','Ray','Miller','','Mayfair Mart','7345 Ross Blvd.','San Diago','CA','94086','408-723-8789','rm@rmiller.com');
INSERT IN