· 4 years ago · Jul 12, 2021, 03:48 AM
1USE master; -- To drop a database, you must be "using" a different database
2GO
3
4DROP DATABASE IF EXISTS Lab_4_RachelLancaster; -- Example: Lab_4_JacobCase
5CREATE DATABASE Lab_4_RachelLancaster;
6
7USE Lab_4_RachelLancaster;
8
9-- Create the Customers table
10DROP TABLE IF EXISTS Customers;
11CREATE TABLE Customers
12( CustomerID SMALLINT NOT NULL IDENTITY(1,1),
13 FirstName VARCHAR(25) NOT NULL,
14 MiddleName VARCHAR(25) DEFAULT NULL,
15 LastName VARCHAR(25) NOT NULL,
16 StreetAddress VARCHAR(35) NOT NULL,
17 City VARCHAR(25) NOT NULL,
18 State CHAR(2) NOT NULL,
19 ZipCode VARCHAR(10) NOT NULL,
20 CONSTRAINT PK_Customers_CustomerID PRIMARY KEY ( CustomerID )
21);
22
23
24-- Create the CustomerEmails table
25DROP TABLE IF EXISTS CustomerEmails;
26CREATE TABLE CustomerEmails
27( CustomerID SMALLINT NOT NULL,
28 EmailAddress VARCHAR(254) NOT NULL,
29 PrimaryEmailFlag CHAR(1) NOT NULL DEFAULT 'Y',
30 CONSTRAINT PK_CustomerEmails_CustomerID_EmailAddress PRIMARY KEY ( CustomerID, EmailAddress ),
31 CONSTRAINT FK_CustomerEmails_CustomerID FOREIGN KEY ( CustomerID ) REFERENCES Customers ( CustomerID ),
32 CONSTRAINT CHECK_CustomerEmails_PrimaryEmailFlag CHECK ( PrimaryEmailFlag IN ( 'Y', 'N' ) )
33);
34
35
36-- Create the Employees table
37DROP TABLE IF EXISTS Employees;
38CREATE TABLE Employees
39( EmployeeID SMALLINT NOT NULL IDENTITY(1,1),
40 SSN VARCHAR(10) NOT NULL,
41 FirstName VARCHAR(30) NOT NULL,
42 MiddleName VARCHAR(30) DEFAULT NULL,
43 LastName VARCHAR(30) NOT NULL,
44 StreetAddress VARCHAR(35) NOT NULL,
45 City VARCHAR(30) NOT NULL,
46 [State] CHAR(2) NOT NULL,
47 ZipCode VARCHAR(10) NOT NULL,
48 Title VARCHAR(25) NOT NULL,
49 Salary DECIMAL(8,2) NOT NULL,
50 CONSTRAINT PK_Employees_EmployeeID PRIMARY KEY ( EmployeeID )
51);
52
53
54-- Create the Orders table
55DROP TABLE IF EXISTS Orders;
56CREATE TABLE Orders
57( OrderID INT NOT NULL IDENTITY(1,1),
58 EmployeeID SMALLINT NOT NULL,
59 CustomerID SMALLINT NOT NULL,
60 OrderDate DATE NOT NULL,
61 OrderTime TIME NOT NULL,
62 CONSTRAINT PK_Orders_OrderID PRIMARY KEY ( OrderID ),
63 CONSTRAINT FK_Orders_EmployeeID FOREIGN KEY ( EmployeeID ) REFERENCES Employees ( EmployeeID ),
64 CONSTRAINT FK_Orders_CustomerID FOREIGN KEY ( CustomerID ) REFERENCES Customers ( CustomerID )
65);
66
67
68-- Create the Statuses table
69DROP TABLE IF EXISTS Statuses;
70CREATE TABLE Statuses
71( StatusID TINYINT NOT NULL IDENTITY(1,1),
72 StatusName VARCHAR(25) NOT NULL,
73 StatusDescription VARCHAR(100) NOT NULL,
74 CONSTRAINT PK_Statuses_StatusID PRIMARY KEY ( StatusID )
75);
76
77
78-- Create the OrderStatuses table
79DROP TABLE IF EXISTS OrderStatuses;
80CREATE TABLE OrderStatuses
81( OrderID INT NOT NULL,
82 StatusID TINYINT NOT NULL,
83 StatusDate DATE NOT NULL,
84 StatusTime TIME NOT NULL,
85 CONSTRAINT PK_OrderStatuses_OrderID_StatusID PRIMARY KEY ( OrderID, StatusID ),
86 CONSTRAINT FK_OrderStatuses_OrderID FOREIGN KEY ( OrderID ) REFERENCES Orders ( OrderID ),
87 CONSTRAINT FK_OrderStatuses_StatusID FOREIGN KEY ( StatusID ) REFERENCES Statuses ( StatusID )
88);
89
90
91-- Create the Dependents table
92DROP TABLE IF EXISTS Dependents;
93CREATE TABLE Dependents
94( EmployeeID SMALLINT NOT NULL IDENTITY(1,1),
95 DependentNumber INT NOT NULL,
96 FirstName VARCHAR(25) NOT NULL,
97 LastName VARCHAR(25) NOT NULL,
98 BirthDate DATE NOT NULL,
99 SSN INT NOT NULL,
100 CONSTRAINT PK_Dependents_EmployeeID PRIMARY KEY ( EmployeeID ),
101 CONSTRAINT FK_Dependents_EmployeeID FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
102
103);
104
105 -- Create the Products table
106DROP TABLE IF EXISTS Products;
107CREATE TABLE Products
108( ProductID SMALLINT NOT NULL,
109 ProductName VARCHAR(25) NOT NULL,
110 ProductDescription VARCHAR(255) NOT NULL,
111 SalesPrice DECIMAL NOT NULL,
112 InventoryOnHand SMALLINT NOT NULL,
113 CONSTRAINT PK_Products_ProductID PRIMARY KEY ( ProductID )
114);
115
116-- Create the OrderLines table
117DROP TABLE IF EXISTS OrderLines;
118CREATE TABLE OrderLines
119( OrderID INT NOT NULL,
120 ProductID SMALLINT NOT NULL,
121 Price DECIMAL NOT NULL,
122 Quantity SMALLINT NOT NULL,
123CONSTRAINT PK_OrderLines_OrderID_ProductID PRIMARY KEY ( OrderID, ProductID ),
124CONSTRAINT FK_OrderLines_OrderID FOREIGN KEY ( OrderID ) REFERENCES Orders (OrderID),
125CONSTRAINT FK_OrderLines_ProductID FOREIGN KEY (ProductID) REFERENCES Products (ProductID)
126);
127
128
129-- Create the ProductPriceHistory table
130DROP TABLE IF EXISTS ProductPriceHistory;
131CREATE TABLE ProductPriceHistory
132( ProductID SMALLINT NOT NULL,
133 PriceChangeDate DATE NOT NULL,
134 SalesPrice DECIMAL NOT NULL,
135 CONSTRAINT PK_ProductPriceHistory_ProductID_PriceChange PRIMARY KEY (ProductID, PriceChangeDate),
136 CONSTRAINT FK_ProductPriceHistory_ProductID FOREIGN KEY (ProductID) REFERENCES Products (ProductID)
137);
138-- Create the Vendors table
139DROP TABLE IF EXISTS Vendors;
140CREATE TABLE Vendors
141( VendorID SMALLINT NOT NULL,
142 VendorName VARCHAR(35) NOT NULL,
143 VendorDescription VARCHAR(255) NOT NULL,
144 CONSTRAINT PK_Vendors_VendorID PRIMARY KEY ( VendorID )
145);
146
147-- Create the ProductVendors table
148DROP TABLE IF EXISTS ProductVendors;
149CREATE TABLE ProductVendors
150( ProductID SMALLINT NOT NULL,
151 VendorID SMALLINT NOT NULL,
152 UnitCost DECIMAL NOT NULL,
153 CONSTRAINT PK_ProductVendorss_ProductID_VendorID PRIMARY KEY ( ProductID, VendorID ),
154 CONSTRAINT FK_ProductVendors_ProductID FOREIGN KEY ( ProductID ) REFERENCES Products ( ProductID ),
155 CONSTRAINT FK_ProductVendors_VendorID FOREIGN KEY ( VendorID ) REFERENCES Vendors ( VendorID )
156);
157
158ALTER TABLE Customers
159ADD EmailOptIn CHAR(1) NOT NULL DEFAULT 'Y';
160
161INSERT INTO Customers ( FirstName, MiddleName, LastName, StreetAddress, City, State, ZipCode)
162VALUES ( 'John', 'Christopher', 'Depp', '1234 Hollywood Lane', 'Burbank', 'CA', '91506');
163INSERT INTO Customers ( FirstName, MiddleName, LastName, StreetAddress, City, State, ZipCode)
164VALUES ( 'Jacob', 'Hamon', 'Hart', '194 Hollywood Lane', 'Burbank', 'CA', '91506');
165INSERT INTO Customers ( FirstName, MiddleName, LastName, StreetAddress, City, State, ZipCode)
166VALUES ( 'John', 'Christopher', 'Depp', '1234 Hollywood Lane', 'Burbank', 'CA', '91506');
167
168
169
170INSERT INTO Vendors(VendorID,VendorName, VendorDescription)
171VALUES(1,'AutoZone', 'American retailer of aftermarket automotive parts and accessories');
172INSERT INTO Vendors(VendorID,VendorName, VendorDescription)
173VALUES(2,'CarsRUs', 'American retailer of many makes/models of cars');
174INSERT INTO Vendors(VendorID,VendorName, VendorDescription)
175VALUES(3,'CarTronics', 'American retailer of aftermarket automotive electronic parts and accessories');
176
177
178
179INSERT INTO Employees(SSN, FirstName, MiddleName, LastName, StreetAddress, City, State, ZipCode, Title, Salary)
180VALUES(555, 'John', 'Jacob', 'Schmidt', '343 Middle Ave', 'New York', 'NY', 10010, 'Accountant', 78000);
181INSERT INTO Employees(SSN, FirstName, MiddleName, LastName, StreetAddress, City, State, ZipCode, Title, Salary)
182VALUES(555, 'Jacob', 'Howard', 'Smith', '348 Middle Ave', 'New York', 'NY', 10010, 'Janitor', 78000);
183INSERT INTO Employees(SSN, FirstName, MiddleName, LastName, StreetAddress, City, State, ZipCode, Title, Salary)
184VALUES(555, 'Chris', 'Jacob', 'Schmidt', '349 Middle Ave', 'New York', 'NY', 10010, 'HR', 78000);
185
186
187
188INSERT INTO Products (ProductID, ProductName, ProductDescription, SalesPrice, InventoryOnHand)
189VALUES (1, 'Toothbrush', 'Brushes your teeth', 10, 200);
190INSERT INTO Products (ProductID, ProductName, ProductDescription, SalesPrice, InventoryOnHand)
191VALUES (2, 'Hairbrush', 'Brushes your hair', 15, 230);
192INSERT INTO Products (ProductID, ProductName, ProductDescription, SalesPrice, InventoryOnHand)
193VALUES (3, 'Mop', 'Cleans your floor', 20, 201);
194
195
196
197
198INSERT INTO ProductVendors(ProductID, VendorID, UnitCost)
199VALUES(1,1,336);
200INSERT INTO ProductVendors(ProductID, VendorID, UnitCost)
201VALUES(2,2,395);
202INSERT INTO ProductVendors(ProductID, VendorID, UnitCost)
203VALUES(3,3,499);
204
205INSERT INTO Orders(CustomerID, EmployeeID,OrderDate, OrderTime)
206VALUES(1,2,'2016-04-13', '03:23')
207INSERT INTO Orders(CustomerID, EmployeeID, OrderDate, OrderTime)
208VALUES(1, 2,'2019-05-07', '05:23')
209INSERT INTO Orders(CustomerID, EmployeeID,OrderDate, OrderTime)
210VALUES(1, 2,'2019-01-11', '04:13')
211
212
213INSERT INTO Statuses(StatusName, StatusDescription)
214VALUES('IsTheToiletFixed', 'No and it never will be');
215INSERT INTO Statuses(StatusName, StatusDescription)
216VALUES('NewVendorSearch', 'We dont need nobody for nothin');
217INSERT INTO Statuses(StatusName, StatusDescription)
218VALUES('New hire search', 'We are too cheap to hire anyone else sorry');
219
220INSERT INTO OrderStatuses( StatusID,OrderID,StatusDate, StatusTime)
221VALUES(3,3,'2014-02-15', '01:45');
222INSERT INTO OrderStatuses(StatusID,OrderID,StatusDate, StatusTime)
223VALUES(2,2,'2013-03-13', '03:45');
224INSERT INTO OrderStatuses(StatusID,OrderID,StatusDate, StatusTime)
225VALUES(1,1,'2006-12-01', '07:32');
226
227INSERT INTO CustomerEmails(CustomerID, EmailAddress)
228VALUES (1,'johnny@gmail.com');
229INSERT INTO CustomerEmails(CustomerID, EmailAddress)
230VALUES (2,'jacob@gmail.com');
231INSERT INTO CustomerEmails(CustomerID, EmailAddress)
232VALUES (3,'chris@gmail.com');
233
234
235INSERT INTO Dependents(DependentNumber, FirstName, LastName, BirthDate, SSN)
236VALUES(1,'Alex', 'Smith', '07/19/96', 4343);
237INSERT INTO Dependents(DependentNumber, FirstName, LastName, BirthDate, SSN)
238VALUES(2,'MrNoodles', 'Sanders', '03/19/80', 3321);
239INSERT INTO Dependents(DependentNumber, FirstName, LastName, BirthDate, SSN)
240VALUES(3,'Jimmy', 'Schmidt','03/12/99', 4553);
241
242
243INSERT INTO ProductPriceHistory(ProductID,PriceChangeDate,SalesPrice)
244VALUES(3,'2006-12-12',370);
245INSERT INTO ProductPriceHistory(ProductID,PriceChangeDate,SalesPrice)
246VALUES(2,'2006-12-12', 370);
247INSERT INTO ProductPriceHistory(ProductID,PriceChangeDate,SalesPrice)
248VALUES(1, '2006-12-12',370);
249
250
251INSERT INTO OrderLines(ProductID,OrderID, Price, Quantity)
252VALUES(1,1,336,40);
253INSERT INTO OrderLines(ProductID,OrderID, Price, Quantity)
254VALUES(2,1,395,5);
255INSERT INTO OrderLines(ProductID,OrderID, Price, Quantity)
256VALUES(3,1,499,100);
257