SHARE
TWEET

exposure email: online.sfsu.edu

FreeInternet Jan 16th, 2019 960 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. disclosure: http://online.sfsu.edu/pbeckman/student_work/sephora.sql
  2.  
  3. DROP TABLE CUSTOMERS CASCADE CONSTRAINTS;
  4. CREATE TABLE CUSTOMERS
  5.     (CUSTOMERID CHAR(12) NOT NULL,
  6.      FNAME  VARCHAR(15) NOT NULL,
  7.      LNAME  VARCHAR(15) NOT NULL,
  8.      MINIT  CHAR,
  9.      EMAILADD VARCHAR(30),
  10.      PHONENUM VARCHAR(15),
  11.      STREETADD VARCHAR(30) NOT NULL,
  12.      CITY VARCHAR(30) NOT NULL,
  13.      ZIPCODE VARCHAR(10) NOT NULL,
  14.      STATE   CHAR(2) NOT NULL,
  15.      PRIMARY KEY(CUSTOMERID));
  16.  
  17. INSERT INTO CUSTOMERS VALUES
  18.     ('456-12-3325','Jack','Adams', 'A', 'adjack@earthlink.net','(415)111-1111','123 1st Street','SAN MATEO','94132-2652','CA');
  19. INSERT INTO CUSTOMERS VALUES
  20.     ('555-77-5556','Fong','lan-tin','', '', '(510)454-2131','5231 21st Avenue Apt#3','LOS VEGAS','94106-0000','CA');
  21. INSERT INTO CUSTOMERS VALUES
  22.     ('812-21-4156','Andy','Christensen','D','Ac@omni.net','(212)526-5161','421 Madison Blvd Floor 20','NEW YORK CITY','10021-0812','NY');
  23. INSERT INTO CUSTOMERS VALUES
  24.     ('585-11-1516','Ryan','Leman','','Ryl@aol.com','(561)515-2132','6542 50th Street','MIAMI','25120-0000','FL');
  25. INSERT INTO CUSTOMERS VALUES
  26.     ('622-22-8888','Mikiko','Kawamoto','','mikoo@yahoo.com','(456)513-2133','83218 Disney Drive','PORLAND','52163-2122','WA');
  27. INSERT INTO CUSTOMERS VALUES
  28.     ('345-35-6758','David','Yu','','dyu@appliedbiosystem.com','(650)638-6888','664 Pilrgirm Dr.','FOSTER CIYT','94404-2311','CA');
  29. INSERT INTO CUSTOMERS VALUES
  30.     ('312-36-5648','Linda','Chang','L','lchang@sfsu.edu','(415)564-6548','464 43th Ave.','San Fracisco','94122-4561','CA');
  31. INSERT INTO CUSTOMERS VALUES
  32.     ('456-45-4123','Sau Wei','Lam','W','swl@eathlink.com','(460)587-5689','142 Main Street','Chiago','89456-1345','IL');
  33. INSERT INTO CUSTOMERS VALUES
  34.     ('589-46-3568','Kathleen','Yu','','kyu@yahoo.com','(906)532-4651','1345 Waikiki Ave.','Honolulu','95861-5467','HW');
  35. INSERT INTO CUSTOMERS VALUES
  36.     ('965-56-5461','Beck','Paul','E','bpe@hotmail.com','(561)897-9865','156 Peach Street','Seattle','92456-4567','WA');
  37.  
  38.  
  39. DROP TABLE CATEGORIES CASCADE CONSTRAINTS;
  40. CREATE TABLE CATEGORIES
  41.     (CATEGORYID VARCHAR(4) NOT NULL,
  42.      CATEGORYNAME VARCHAR(20) NOT NULL,
  43.     PRIMARY KEY(CATEGORYID),
  44.     UNIQUE(CATEGORYNAME));
  45.  
  46.     INSERT INTO CATEGORIES VALUES
  47.     ('FRAG','FRAGRANCES');
  48.     INSERT INTO CATEGORIES VALUES  
  49.     ('MKUP','MAKEUP');
  50.     INSERT INTO CATEGORIES VALUES
  51.     ('SKIN','SKINCARE');
  52.     INSERT INTO CATEGORIES VALUES
  53.     ('TOOL','MAKE UP TOOLS');
  54.     INSERT INTO CATEGORIES VALUES
  55.     ('GIFT','GIFT ACCESSORIES');
  56.     INSERT INTO CATEGORIES VALUES
  57.     ('BATH','BATHCARE');
  58.     INSERT INTO CATEGORIES VALUES
  59.     ('HAIR','HAIRCARE');
  60.    
  61. DROP TABLE VENDOR CASCADE CONSTRAINTS;
  62. CREATE TABLE VENDOR
  63.     (VENDORID INT NOT NULL,
  64.      VENDORNAME VARCHAR(50) NOT NULL,
  65.      CONTACTNAME VARCHAR(20) NOT NULL,
  66.      CONTACTPHONE VARCHAR(15) NOT NULL,
  67.      CONTACTFAX VARCHAR(15) NOT NULL,
  68.      CONTACTEMAIL VARCHAR(30),
  69.      VENDORWEBADD VARCHAR(40),
  70.     PRIMARY KEY(VENDORID),
  71.     UNIQUE(VENDORNAME));
  72.  
  73.     INSERT INTO VENDOR VALUES
  74.     (500280,'PHILOSOPHY','KARYN ABRAHAM','480-523-5313','480-523-5168','kabraham@ephilosophy.com','www.philosophy.com');
  75.     INSERT INTO VENDOR VALUES
  76.     (500281,'PERRICONE','SUSAN RAMUS','561-842-0505','561-842-0717','susanramus@aol.com','www.perricone.com');
  77.     INSERT INTO VENDOR VALUES
  78.     (500282,'CHRISTIAN DIOR','LATIA OUTEN','212-523-0213','212-523-0211','LOUTEN@DIOR.COM','');
  79.     INSERT INTO VENDOR VALUES
  80.     (500283,'SEPHORA','DARLENE SINGSON','415-348-3333','415-348-3444','darlenesingson@sephora.com','www.sephora.com');
  81.     INSERT INTO VENDOR VALUES
  82.     (500284,'STILA','KATIE WHOREN','212-223-8282','212-223-8283','KTWHOREN@stila.com','www.stila.com');
  83.     INSERT INTO VENDOR VALUES
  84.     (500285,'CLINIQUE','FRANCOIS RUOHA','212-525-5255','212-525-2382','FRANCOISR@clinique.com','www.clinique.com');
  85.     INSERT INTO VENDOR VALUES
  86.     (500286,'JOJO CANDLES','Mehan Chung','415-358-2033','510-382-1133','mehanchung@jojo.com','');
  87.     INSERT INTO VENDOR VALUES
  88.     (500287,'VERSACE','Christophe Kiten','561-823-3133','521-226-2563','ckiten@versace.com','www.versace.com.fr');
  89.  
  90. DROP TABLE PRODUCT CASCADE CONSTRAINTS;
  91. CREATE TABLE PRODUCT
  92.     (PRODUCTID INT NOT NULL,
  93.      CATEGORYID VARCHAR(4) NOT NULL,
  94.      PRICE DEC(12,2) NOT NULL,
  95.      AVAILABILITY CHAR(1) NOT NULL,
  96.      VENDORID INT NOT NULL,
  97.      PRODUCTNAME VARCHAR(30) NOT NULL,
  98.     PRIMARY KEY(PRODUCTID),
  99.     UNIQUE(PRODUCTNAME),
  100.     CHECK(AVAILABILITY IN ('Y','N')),
  101.     FOREIGN KEY(CATEGORYID) REFERENCES CATEGORIES(CATEGORYID),
  102.     FOREIGN KEY(VENDORID) REFERENCES VENDOR(VENDORID));
  103.  
  104.     INSERT INTO PRODUCT VALUES
  105.     (288068,'FRAG',47.00,'Y',500280,'FALLING IN LOVE4ML');
  106.     INSERT INTO PRODUCT VALUES
  107.     (288069,'GIFT',125.00, 'Y',500281,'DRP ACNE BAG');
  108.     INSERT INTO PRODUCT VALUES
  109.     (288070,'SKIN',78.00,'N',500282,'DAY MOISTURIZER SPF15');
  110.     INSERT INTO PRODUCT VALUES
  111.     (288071,'TOOL',40.00,'Y',500283,'EXTREME PRECISE TWEEZER');
  112.     INSERT INTO PRODUCT VALUES
  113.     (288072,'MKUP',16.00,'Y',500284,'MULTI-COLOR LIPS');
  114.     INSERT INTO PRODUCT VALUES
  115.     (288073,'BATH',25.00,'Y',500286,'JOJO BATH KIT');
  116.     INSERT INTO PRODUCT VALUES
  117.     (288074,'FRAG',60.00,'N',500287,'VERSACE FOR MEN EDT');
  118.     INSERT INTO PRODUCT VALUES
  119.     (288075,'SKIN',90.00,'Y',500285,'LIQUID FOUNDATION LIGHT');
  120.     INSERT INTO PRODUCT VALUES
  121.     (288076,'HAIR',15.00,'Y',500285,'SHAMPOO FOR MEN');
  122.     INSERT INTO PRODUCT VALUES
  123.     (288077,'HAIR',20.00,'Y',500280,'ALL IN YOUR HEAD SHAMPOO');
  124.     INSERT INTO PRODUCT VALUES
  125.     (288078,'BATH',45.00,'Y',500282,'BEAUTY BATH SOAP');
  126.     INSERT INTO PRODUCT VALUES
  127.     (288079,'GIFT',25.00,'Y',500284,'COLOR PALETTES');
  128.     INSERT INTO PRODUCT VALUES
  129.     (288080,'TOOL',35.00,'Y',500283,'BLEMISH DOUBLE ENDS');
  130.     INSERT INTO PRODUCT VALUES
  131.     (288081,'MKUP',45.00,'N',500280,'LIP GLOSS RED');
  132.  
  133. DROP TABLE SHIPMENT CASCADE CONSTRAINTS;
  134. CREATE TABLE SHIPMENT
  135.     (SHIPTYPEID INT NOT NULL,
  136.      SHIPTYPE VARCHAR(25) NOT NULL,
  137.      PROCESSTIME VARCHAR(25) NOT NULL,
  138.      SHIPRATE DEC(12,2) NOT NULL,
  139.      PRIMARY KEY(SHIPTYPEID),
  140.      CHECK(SHIPTYPE IN('FedEx 2-Day','UPS Ground','USPS Priority Mail')),
  141.      CHECK(PROCESSTIME IN('3 business days','5 - 7 business days','3 - 7 business days')),
  142.      CHECK(SHIPRATE IN(9.95,5.25,3.25)));
  143.  
  144.     INSERT INTO SHIPMENT VALUES
  145.     (1,'FedEx 2-Day','3 business days',9.95);
  146.     INSERT INTO SHIPMENT VALUES
  147.     (2,'UPS Ground','5 - 7 business days',5.25);
  148.     INSERT INTO SHIPMENT VALUES
  149.     (3,'USPS Priority Mail','3 - 7 business days',3.25);
  150.  
  151.  
  152. DROP TABLE ORDERS CASCADE CONSTRAINTS;
  153. CREATE TABLE ORDERS
  154.     (ORDERNUM INT NOT NULL,
  155.      CUSTOMERID CHAR(12) NOT NULL,
  156.      ORDERDATE DATE NOT NULL,
  157.      PROMOCODE VARCHAR(6),
  158.      PRIMARY KEY(ORDERNUM),
  159.      FOREIGN KEY(CUSTOMERID) REFERENCES CUSTOMERS(CUSTOMERID));
  160.  
  161.     INSERT INTO ORDERS VALUES
  162.     (532162353,'456-12-3325','2-MAY-02','SHOWE2');
  163.     INSERT INTO ORDERS VALUES
  164.     (532161355,'555-77-5556','2-MAY-02','');
  165.     INSERT INTO ORDERS VALUES
  166.     (532161356,'812-21-4156','1-FEB-03','STRW34');
  167.     INSERT INTO ORDERS VALUES
  168.     (532161111,'622-22-8888','22-FEB-03','');
  169.     INSERT INTO ORDERS VALUES
  170.     (532161113,'345-35-6758','1-APRIL-03','');
  171.     INSERT INTO ORDERS VALUES
  172.     (532161114,'312-36-5648','21-APRIL-03','BEEE12');
  173.     INSERT INTO ORDERS VALUES
  174.     (532161118,'456-45-4123','22-APRIL-03','');
  175.     INSERT INTO ORDERS VALUES
  176.     (532161122,'589-46-3568','30-APRIL-03','WASH24');
  177.     INSERT INTO ORDERS VALUES
  178.     (532161125,'965-56-5461','1-MAY-03','');
  179.  
  180.  
  181. DROP TABLE ORDERITEM CASCADE CONSTRAINTS;
  182. CREATE TABLE ORDERITEM
  183.     (ORDERITEMID INT NOT NULL,
  184.      PRODUCTID INT NOT NULL,
  185.      ORDERNUM INT NOT NULL,
  186.      QUANTITY INT NOT NULL,
  187.      AMOUNT DECIMAL(8,2) NOT NULL,
  188.      DISCOUNT VARCHAR(4),
  189.      ORDERSTATUS VARCHAR(20) NOT NULL,
  190.      SHIPTYPEID INT NOT NULL,
  191.      PRIMARY KEY(ORDERITEMID),
  192.      FOREIGN KEY(ORDERNUM) REFERENCES ORDERS(ORDERNUM),
  193.      FOREIGN KEY(PRODUCTID) REFERENCES PRODUCT(PRODUCTID),
  194.      FOREIGN KEY(SHIPTYPEID) REFERENCES SHIPMENT(SHIPTYPEID),
  195.      CHECK(ORDERSTATUS IN ('IN PROCESS','SHIPPED','BACKORDER')),
  196.      CHECK(DISCOUNT IN('0%','10%','20%','30%')));
  197.  
  198.     INSERT INTO ORDERITEM VALUES
  199.     (1,288068,532162353,3,112.80,'20%','IN PROCESS',3);
  200.     INSERT INTO ORDERITEM VALUES
  201.     (2,288075,532161355,1,90.00,'0%','BACKORDER',1);
  202.     INSERT INTO ORDERITEM VALUES
  203.     (3,288075,532161111,1,90.00,'0%','IN PROCESS',3);
  204.     INSERT INTO ORDERITEM VALUES
  205.     (4,288072,532162353,1,12.80,'20%','BACKORDER',3);
  206.     INSERT INTO ORDERITEM VALUES
  207.     (5,288073,532161355,1,25.00,'0%','BACKORDER',1);
  208.     INSERT INTO ORDERITEM VALUES
  209.     (6,288081,532161111,1,45.00,'0%','BACKORDER',3);
  210.     INSERT INTO ORDERITEM VALUES
  211.     (7,288076,532161356,2,24.00,'20%','SHIPPED',2);
  212.     INSERT INTO ORDERITEM VALUES
  213.     (8,288074,532161113,2,120.00,'0%','IN PROCESS',2);
  214.     INSERT INTO ORDERITEM VALUES
  215.     (9,288078,532161114,1,40.50,'10%','SHIPPED',1);
  216.     INSERT INTO ORDERITEM VALUES
  217.     (10,288080,532161118,2,70.00,'0%','SHIPPED',2);
  218.     INSERT INTO ORDERITEM VALUES
  219.     (11,288070,532161122,1,49.00,'30%','SHIPPED',3);
  220.         INSERT INTO ORDERITEM VALUES
  221.     (12,288076,532161125,3,45.00,'0%','SHIPPED',1);
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top