Untitled

                Never    
SQL
       
--CUSTOMERS TABLE
DROP TABLE CUSTOMERS CASCADE CONSTRAINTS;
CREATE TABLE CUSTOMERS
(
CUSTOMERID CHAR(5),
COMPANYNAME VARCHAR2(40) NOT NULL,
CONTACTNAME VARCHAR2(30),
ADDRESS VARCHAR2(60),
REGION VARCHAR2(15),
POSTCALCODE VARCHAR2(10),
COUNTRY VARCHAR2 (15),
PHONE VARCHAR2(24),
FAX VARCHAR2(24),
EMAIL VARCHAR2(50),
CONSTRAINT CUSOMERS_CUSTOMERID_pk PRIMARY KEY (CUSTOMERID),
CONSTRAINT CUSTOMERS_eMAIL_uk UNIQUE (EMAIL)
);

--EMPLOYEES TABLE
DROP TABLE EMPLOYEES CASCADE CONSTAINTS;
CREATE TABLE EMPLOYEES
(
EMPLOYEEID NUMBER(8,0) NOT NULL,
LASTNAME VARCHAR2 (20) NOT NULL,
FIRSTNAME VARCHAR2(10) NOT NULL,
TITLE VARCHAR2 (20) NOT NULL,
TITLEOFCOURTEST VARCHAR2 (25),
BIRTHDATE DATE,
HIREDATE DATE,
ADDRESS VARCHAR2 (60),
CITY VARCHR2(15),
REGION VARCHAR2(15),
REPORTSTO NUMBER(8,0),
PHOTOPATH VARCHAR2 (255),
SINNUMB CHAR(9),
CONSTRAINT EMPLOYEES_EMPLOYEEID_pk PRIMARY KEY (EMPLOYEEID),
CONSTRAINT EMPLOYEES_REPORTSTOFK FOREIGN KEY (REPORTSTO REFERENCES EMPLOYEES (EMPLOYEEID),
CONSTRAINT EMPLOYEES_SINNUMB_UK UNIQUE (SINNUMB)
);
--SHIPPERS TABLE
DROP TABLE SHIPPERS CASCADE CONSTRAINTS;
CREATE TABLE SHIPPERS
(
SHIPPERID NUMBER (8,0) NOT NULL,
COMPANYNAME VARCHAR2(40) NOT NULL,
PHONE VARCHAR2(24),
CONSTRAINT SHIPPERS_SHIPPERID_pk PRIMARY KEY (SHIPPERID)
);
--SUPPLIERS TABLE
DROP TABLE SUPPLIERS CASCADE CONSTRAINTS;
CREATE TABLE SUPPLIERS
(
SUPPLIERID NUMBER(8,0) NOT NULL,
COMPANYNAME VARCHAR2(40) NOT NULL,
CONTACTNAME VARCHAR2(30),
CONTACTTITLE VARCHAR2(30),
ADDRESS VARCHAR2(60),
CITY VARCHAR2(15),
REGION VARCHAR2(15),
POSTALCODE VARCHAR2 (10),
COUNTRY VARCHAR2(15),
PHONE VARCHAR2(24),
FAX VARCHAR2(24),
HOMEPAGE VARCHAR2(200),
CONSTRAINT SUPPLIERS_SUPPLIERID_pk PRIMARY KEY (SUPPLIERID)
);
--CATAGORIES TABLE
DROP TABLE CATAGORIES CASCADE CONSTRAINTS;
CREATE TABLE CATAGORIES
(
CATAGORYID NUMBER(8,0) NOT NULL,
CATAGORYNAME VARCHAR2(15) NOT NULL.
CATAGORYCODE NUMBER (6,0),
DESCRIPTION VARCHAR2(300),
CONSTRAINT CATAGORIES_CATAGORYid_pk PRIMARY KEY (CATAGORYID),
CONSTRAINT CATAGORIES_CATAGORYCODE_UK UNIQUE (CATAGORYCODE)
);
--ORDERS TABLE
DROP TABLE ORDERS CASCADE CONSTRAINTS;
CREATE TABLE ORDERS
(
ORDERID NUMBER (8,0) NOT NULL,
CUSTOMERID CHAR(5),
EMPLOYEEID NUMBER (22),
TERRITORYID VARCHAR2(20)
ORDERDATE DATE,
REQUIREDDATE DATE,
SHIPPEDDATE DATE,
SHIPVIA NUMBER (8,0),
FREIGHT NUMBER (8,2),
SHIPNAME VARCHAR2 (40),
SHIPADDRESS VARCHAR2(60),
SHIPCITY VARCHAR2(15),
SHIPREGION VARCHAR2(15),
SHIPPOSTALCODE VARCHAR2(10),
SHIPCOUNTRY VARCHAR2(15),
CONSTRAINT ORDERS_ORDERID_pk PRIMARY KEY (ORDERID),
CONSTRAINT ORDERS_CUSTOMERID_FK FOREIGN KEY (CUSTOMERID) REFERENCES CUSTOMERS(CUSTOMERID),
CONSTRAINT ORDERS_EMPLOYEEID_FK FOREIGN KEY (EMPLOYEEID) REFERENCES EMPLOYEES (EMPLOYEEID),
CONSTRAINT ORDERS_SHIPVIA_FK FOREIGN KEY (SHIPVIA) REFERENCES SHIPPERS(SHIPPERID)
);
--PRODUCTS TABLE
DROP TABLE PRODUCTS CASCADE CONSTRAINTS;
CREATE TABLE PRODUCTS
(
PRODUCTID NUMBER (8,0) NOT NULL,
PRODUCTNAME VARCHAR2(40) NOT NULL,
SUPPLIERID NUNBER (8,0),
CATAGORYID NUMBER(8,0),
QUANTITYPERUNIT VARCHAR2(20),
UNITPRICE NUMBER(8,2),
UNITSINSTOCK NUMBER (6,0),
UNITSONORDER NUMBER (6,0),
REORDER LEVEL NUMBER (6,0),
DISCONTINUED NUMBER (1,0) NOT NULL,
CONSTRAINT PRODUCTS_PRODUCTID_pk PRIMARY KEY (PRODUCTID),
CONSTRAINT PRODUCTS_SUPPLIERID_FK FOREIGN KEY (SUPPLIERID) REFERENCES SUPPLIERS (SUPPLIERID),
CONSTRAINT PRODUCTS_CATAGORYID_FK FOREIGN KEY (CATAGORYID) REFERENCES CATAGORIES (CATAGORYID),
CONSTRAINT PRODUCTS_UNITPRICE_ck CHECK (UNITPRICE >= 0),
CONSTRAINT PRODUCTS_REORDERLEVEL_ck CHECK (REORDERLEVEL >= 0),
CONSTRAINT PRODUCTS_UNITSINSTOCK_ck CHECK (UNITSINSTOCK >= 0),
CONSTRAINT PRODUCTS_UNITSONORDER_ck CHECK (UNITSONORDER>=0),
);
--ORDERDETAILS TABLE
DROP TABLE ORDERDETAILS CASCADE CONSTRAINTS;
CREATE TABLE ORDERDETAILS
(
PRODUCTID NUMBER (8,0) NOT NULL,
ORDERID NUMBER (8,0) NOT NULL,
PRODUCT NUMBER (8,0),
UNITPRICE NUMBER (8,2) NOT NULL,
QUANTITY NUMBER (6,0) NOT NULL,
DISCOUNT NUMBER (2,2) NOT NULL,
CONSTRAINT ORDERDETAILS_OID_PID_pk PRIMARY KEY (ORDERID, PRODUCTID),
CONSTRAINT ORDERDETAILS_DISCOUNT_CK CHECK (DISCOUNT>=0 and DISCOUNT <=1),
CONSTRAINT ORDERDETAILS_PRODUCTID_FK FOREIGN KEY(PRODUCTID) REFERENCES PRODUCTS (PRODUCTID),
CONSTRAINT ORDERDETAILS_ORDERID_fk FOREIGN KEY (ORDERID) REFERENCES ORDERS(ORDERID),
CONSTRAINT ORDERDETAILS_QUANTITY_CK CHECK (QUANTITY >0),
CONSTRAINT ORDERDETAILS_UNITPRICE_CK CHECK (UNITPRICE >=0)
);

);

Raw Text