SHARE
TWEET

Untitled

a guest Mar 15th, 2017 42 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -----------------------------------------------------------
  2. -- Sams Teach Yourself SQL in 10 Minutes
  3. -- http://forta.com/books/0672336073/
  4. -- Example table creation scripts for Microsoft SQL Server.
  5. -----------------------------------------------------------
  6.  
  7.  
  8. -------------------------
  9. -- Create Customers table
  10. -------------------------
  11. CREATE TABLE Customers
  12. (
  13.   cust_id      char(10)  NOT NULL ,
  14.   cust_name    char(50)  NOT NULL ,
  15.   cust_address char(50)  NULL ,
  16.   cust_city    char(50)  NULL ,
  17.   cust_state   char(5)   NULL ,
  18.   cust_zip     char(10)  NULL ,
  19.   cust_country char(50)  NULL ,
  20.   cust_contact char(50)  NULL ,
  21.   cust_email   char(255) NULL
  22. );
  23.  
  24. --------------------------
  25. -- Create OrderItems table
  26. --------------------------
  27. CREATE TABLE OrderItems
  28. (
  29.   order_num  int          NOT NULL ,
  30.   order_item int          NOT NULL ,
  31.   prod_id    char(10)     NOT NULL ,
  32.   quantity   int          NOT NULL ,
  33.   item_price decimal(8,2) NOT NULL
  34. );
  35.  
  36. ----------------------
  37. -- Create Orders table
  38. ----------------------
  39. CREATE TABLE Orders
  40. (
  41.   order_num  int      NOT NULL ,
  42.   order_date datetime NOT NULL ,
  43.   cust_id    char(10) NOT NULL
  44. );
  45.  
  46. ------------------------
  47. -- Create Products table
  48. ------------------------
  49. CREATE TABLE Products
  50. (
  51.   prod_id    char(10)      NOT NULL ,
  52.   vend_id    char(10)      NOT NULL ,
  53.   prod_name  char(255)     NOT NULL ,
  54.   prod_price decimal(8,2)  NOT NULL ,
  55.   prod_desc  varchar(1000) NULL
  56. );
  57.  
  58. -----------------------
  59. -- Create Vendors table
  60. -----------------------
  61. CREATE TABLE Vendors
  62. (
  63.   vend_id      char(10) NOT NULL ,
  64.   vend_name    char(50) NOT NULL ,
  65.   vend_address char(50) NULL ,
  66.   vend_city    char(50) NULL ,
  67.   vend_state   char(5)  NULL ,
  68.   vend_zip     char(10) NULL ,
  69.   vend_country char(50) NULL
  70. );
  71.  
  72. ----------------------
  73. -- Define primary keys
  74. ----------------------
  75. ALTER TABLE Customers WITH NOCHECK ADD CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (cust_id);
  76. ALTER TABLE OrderItems WITH NOCHECK ADD CONSTRAINT PK_OrderItems PRIMARY KEY CLUSTERED (order_num, order_item);
  77. ALTER TABLE Orders WITH NOCHECK ADD CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED (order_num);
  78. ALTER TABLE Products WITH NOCHECK ADD CONSTRAINT PK_Products PRIMARY KEY CLUSTERED (prod_id);
  79. ALTER TABLE Vendors WITH NOCHECK ADD CONSTRAINT PK_Vendors PRIMARY KEY CLUSTERED (vend_id);
  80.  
  81. ----------------------
  82. -- Define foreign keys
  83. ----------------------
  84. ALTER TABLE OrderItems ADD
  85. CONSTRAINT FK_OrderItems_Orders FOREIGN KEY (order_num) REFERENCES Orders (order_num),
  86. CONSTRAINT FK_OrderItems_Products FOREIGN KEY (prod_id) REFERENCES Products (prod_id);
  87. ALTER TABLE Orders ADD
  88. CONSTRAINT FK_Orders_Customers FOREIGN KEY (cust_id) REFERENCES Customers (cust_id);
  89. ALTER TABLE Products ADD
  90. CONSTRAINT FK_Products_Vendors FOREIGN KEY (vend_id) REFERENCES Vendors (vend_id);
RAW Paste Data
Top