· 4 years ago · Jun 07, 2021, 01:44 PM
1-- CREATE database if it doesn't exist
2if NOT EXISTS(SELECT * FROM sys.databases WHERE name = 'LexiSql_DvdShop')
3BEGIN
4 CREATE DATABASE LexiSql_DvdShop
5END
6
7-- Mark database for use
8GO
9 USE LexiSql_DvdShop
10
11-- Create tables
12IF NOT EXISTS(SELECT * FROM sysobjects WHERE name='Customers' and xtype='U')
13BEGIN
14 CREATE TABLE Customers (
15 Id INT IDENTITY(1, 1) PRIMARY KEY,
16 FirstName VARCHAR(50) NOT NULL,
17 LastName VARCHAR(50) NOT NULL,
18 BillingAddress VARCHAR(50) NOT NULL,
19 BillingZip INT NOT NULL,
20 BillingCity VARCHAR(50) NOT NULL,
21 DeliveryAddress VARCHAR(50) NOT NULL,
22 DeliveryZip INT NOT NULL,
23 DeliveryCity VARCHAR(50) NOT NULL,
24 EmailAddress VARCHAR(50) NOT NULL,
25 PhoneNo VARCHAR(15) NOT NULL
26 );
27END
28
29IF NOT EXISTS(SELECT * FROM sysobjects WHERE name='Movies' and xtype='U')
30BEGIN
31 CREATE TABLE Movies (
32 Id int IDENTITY(1, 1) PRIMARY KEY,
33 Title VARCHAR(50) NOT NULL,
34 Director VARCHAR(50) NOT NULL,
35 ReleaseYear INT NOT NULL CHECK (ReleaseYear BETWEEN 1800 AND 9999),
36 Price DECIMAL(10, 2) NOT NULL
37 );
38END
39
40GO
41IF NOT EXISTS(SELECT * FROM sysobjects WHERE name='Orders' and xtype='U')
42BEGIN
43 CREATE TABLE Orders (
44 Id int IDENTITY(1, 1) PRIMARY KEY,
45 OrderDate DATE NOT NULL,
46 CustomerId int FOREIGN KEY REFERENCES Customers(Id)
47 );
48END
49
50IF NOT EXISTS(SELECT * FROM sysobjects WHERE name='OrderRows' and xtype='U')
51BEGIN
52 CREATE TABLE OrderRows (
53 Id int IDENTITY(1, 1) PRIMARY KEY,
54 OrderId int FOREIGN KEY REFERENCES Orders(Id),
55 MovieId int FOREIGN KEY REFERENCES Movies(Id),
56 Price DECIMAL(10, 2) NOT NULL
57 );
58END
59
60--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
61-- INSERT base data into tables
62if NOT EXISTS(Select * FROM Customers WHERE Id=1)
63BEGIN
64 INSERT INTO Customers VALUES (
65 'Jonas', 'Gray', '23 Green Corner Street', 56743, 'Birmingham', '23 Green Corner Street', 56743, 'Birmingham', 'jonas.gray@hotmail.com', '0708123455'
66 )
67 INSERT INTO Customers VALUES (
68 'Jane', 'Harolds', '10 West Street', 43212, 'London', '10 West Street', 43212, 'London', 'jane_h77@gmail.com', '0701245512'
69 )
70 INSERT INTO Customers VALUES (
71 'Peter', 'Birro', '12 Fox Street', 45581, 'New York', '89 Moose Plaza', 45321, 'Seattle', 'peter_the_great@hotmail.com', '0739484322'
72 )
73END
74
75if NOT EXISTS(Select * FROM Movies WHERE Id=1)
76BEGIN
77 INSERT INTO Movies VALUES(
78 'Interstellar', 'Cristopher Nolan', 2014, 179.00
79 )
80 INSERT INTO Movies VALUES(
81 'Hobbit: Battle of the five armies', 'Peter Jackson', 2014, 179.00
82 )
83 INSERT INTO Movies VALUES(
84 'The Wolf of Wall Street', 'Martin Scorcese', 2013, 119.00
85 )
86 INSERT INTO Movies VALUES(
87 'Pulp Fiction', 'Quentin Tarantino', 1994, 49.00
88 )
89END
90
91-- Reset movie prices of 2014 movies, since it'll be altered later in the code
92UPDATE Movies
93SET Price = 179.00 WHERE ReleaseYear = 2014;
94
95-- Procedure exists checking method, answer by Geoff, May '21 from
96-- https://stackoverflow.com/questions/2072086/how-to-check-if-a-stored-procedure-exists-before-creating-it
97-- Procedure for creating Orders
98GO
99IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'p' AND OBJECT_ID = OBJECT_ID('dbo.CreatePurchaseOrder'))
100 exec('CREATE PROCEDURE [dbo].[CreatePurchaseOrder] AS BEGIN SET NOCOUNT ON; END')
101
102GO
103ALTER PROCEDURE [dbo].[CreatePurchaseOrder]
104@buyerId INT,
105@purcaseDate DATE,
106@orderId VARCHAR(50) OUTPUT
107AS
108INSERT INTO Orders VALUES(
109 @purcaseDate, @buyerId
110);
111SELECT @orderId = Id FROM Orders SELECT TOP 1 Id FROM Orders ORDER BY Id DESC
112
113-- Procedure for creating OrderRows
114GO
115IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'p' AND OBJECT_ID = OBJECT_ID('dbo.AddOrderRow'))
116 exec('CREATE PROCEDURE [dbo].[AddOrderRow] AS BEGIN SET NOCOUNT ON; END')
117
118GO
119ALTER PROCEDURE [dbo].[AddOrderRow]
120@orderId INT,
121@movieId INT
122AS
123DECLARE @price DECIMAL(10, 2)
124SELECT @price = Price FROM Movies WHERE Id = @movieId
125
126INSERT INTO OrderRows VALUES(
127 @orderId,
128 @movieId,
129 @price
130);
131
132GO
133-- INSERT queries to handle purchasing scenarios
134DECLARE @orderId INT
135
136-- A. On 2015-01-01, Jonas Gray purchases Interstellar and Pulp Fiction.
137-- Jonas Gray, Customer Id 1, makes an order.
138EXECUTE CreatePurchaseOrder 1, '2015-01-01', @orderId = @orderId OUTPUT
139EXECUTE AddOrderRow @orderId, 1 -- Jonas Gray buys Interstellar (Id 1).
140EXECUTE AddOrderRow @orderId, 4 -- Jonas Gray buys Pulp Fiction (Id 4).
141
142-- B. On 2015-01-15, Peter Birro purchases 2 copies of The Wolf of Wall Street.
143-- Peter Birro, Customer Id 3, makes an order.
144EXECUTE CreatePurchaseOrder 3, '2015-01-15', @orderId = @orderId OUTPUT
145EXECUTE AddOrderRow @orderId, 3 -- Peter Birro buys a copy of The Wolf of Wall Street (Id 3).
146EXECUTE AddOrderRow @orderId, 3 -- Peter Birro buys another copy of The Wolf of Wall Street (Id 3).
147
148-- C. On 2014-12-20, Jonas Gray purchased The Wolf of Wall Street.
149-- Jonas Gray, Customer Id 1, makes an order.
150EXECUTE CreatePurchaseOrder 1, '2014-12-20', @orderId = @orderId OUTPUT
151EXECUTE AddOrderRow @orderId, 3 -- Jonas Gray buys a copy of The Wolf of Wall Street (Id 3).
152
153--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
154-- UPDATE movie prices
155UPDATE Movies
156SET Price = 169.00 WHERE ReleaseYear = 2014;
157
158--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
159-- SELECT procedures
160
161-- A. Get Firstname, Lastname, PhoneNo and Email to all Customers.
162GO
163IF OBJECT_ID('GetAllContactInfo', 'p') IS NOT NULL
164 DROP PROCEDURE GetAllContactInfo
165GO
166CREATE PROCEDURE GetAllContactInfo
167AS
168 SELECT FirstName, LastName, PhoneNo, EmailAddress FROM Customers
169
170GO
171EXECUTE GetAllContactInfo
172
173-- B. Get all movies, ordered by Year from newest to oldest.
174GO
175IF OBJECT_ID('GetMoviesNewestFirst', 'p') IS NOT NULL
176 DROP PROCEDURE GetMoviesNewestFirst
177GO
178CREATE PROCEDURE GetMoviesNewestFirst
179AS
180 SELECT * FROM Movies ORDER BY ReleaseYear DESC
181
182GO
183EXECUTE GetMoviesNewestFirst
184
185-- C. Get all movie titles, ordered byPrice, from cheapest to most expensive.
186GO
187IF OBJECT_ID('GetMoviesCheapestFirst', 'p') IS NOT NULL
188 DROP PROCEDURE GetMoviesCheapestFirst
189GO
190CREATE PROCEDURE GetMoviesCheapestFirst
191AS
192 SELECT * FROM Movies ORDER BY Price ASC
193
194GO
195EXECUTE GetMoviesCheapestFirst
196
197-- D. Get Firstname, Lastname, DeliveryAddress, DeliveryZip, DeliveryCity for all customers who bought The Wolf of Wall Street.
198GO
199IF OBJECT_ID('GetDeliveryInfoForMovie', 'p') IS NOT NULL
200 DROP PROCEDURE GetDeliveryInfoForMovie
201GO
202CREATE PROCEDURE GetDeliveryInfoForMovie
203@movieId INT
204AS
205 SELECT FirstName, LastName, DeliveryAddress, DeliveryZip, DeliveryCity
206 FROM Customers WHERE Id
207 IN(SELECT CustomerId FROM Orders WHERE Id
208 IN(SELECT OrderId FROM OrderRows WHERE MovieId = @movieId
209 ));
210
211GO
212-- Wolf of Wall Street ID = 3
213EXECUTE GetDeliveryInfoForMovie 3
214
215-- E. Get Id, Date, Customer (Firstname, Lastname) and total cost of every individual order.
216GO
217IF OBJECT_ID('GetOrdersWithCustomerNames', 'p') IS NOT NULL
218 DROP PROCEDURE GetOrdersWithCustomerNames
219GO
220CREATE PROCEDURE GetOrdersWithCustomerNames
221AS
222 SELECT Orders.Id, Orders.OrderDate, Customers.FirstName, Customers.LastName
223 FROM Orders
224 INNER JOIN Customers ON Orders.CustomerId=Customers.Id
225
226GO
227EXECUTE GetOrdersWithCustomerNames
228
229-- F. Get Customer (Firstname, Lastname), total number of movies ordered by this customer, number of orders by this customer and total cost of all orders by this customer.
230GO
231IF OBJECT_ID('GetCustomerTotals', 'p') IS NOT NULL
232 DROP PROCEDURE GetCustomerTotals
233GO
234CREATE PROCEDURE GetCustomerTotals
235@customerId INT
236AS
237 DECLARE @moviesPurchasedCount INT, @costSum DECIMAL(10, 2)
238
239 SELECT @moviesPurchasedCount = COUNT(*), @CostSum = SUM(Price)
240 FROM OrderRows WHERE OrderId IN(
241 SELECT Id FROM Orders Where CustomerId = @customerId);
242
243 SELECT FirstName, LastName, @moviesPurchasedCount AS PurchaseCount, @costSum AS TotalCost
244 FROM Customers WHERE Id = @customerId
245GO
246-- Totals of Jonas Gray
247EXECUTE GetCustomerTotals 1;
248-- Totals of Jane Gray (No purchases expected)
249EXECUTE GetCustomerTotals 3;
250-- Totals of Peter Birro
251EXECUTE GetCustomerTotals 3;
252
253-- G. Get number of orders and total cost for all orders in the database.
254GO
255IF OBJECT_ID('GetOrdersCountAndTotalCost', 'p') IS NOT NULL
256 DROP PROCEDURE GetOrdersCountAndTotalCost
257GO
258CREATE PROCEDURE GetOrdersCountAndTotalCost
259AS
260 DECLARE @totalOrders INT, @costSum DECIMAL(10, 2)
261
262 SELECT @totalOrders = COUNT(*)
263 FROM Orders
264
265 SELECT @costSum = SUM(Price)
266 FROM OrderRows
267
268 SELECT @totalOrders AS TotalOrders, @costSum AS TotalCost
269GO
270EXECUTE GetOrdersCountAndTotalCost
271
272--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
273-- Set phone numbers to base values
274UPDATE Customers SET PhoneNo = '0708123455' WHERE ID = 1
275UPDATE Customers SET PhoneNo = '0701245512' WHERE ID = 2
276UPDATE Customers SET PhoneNo = '0739484322' WHERE ID = 3
277
278-- COPY new cellphone number column
279GO
280IF COL_LENGTH('Customers', 'CellNo') IS NULL
281BEGIN
282 ALTER TABLE Customers
283 ADD CellNo VARCHAR(15)
284END
285
286GO
287-- Copy PhoneNo to CellNo
288UPDATE Customers SET CellNo = PhoneNo
289
290GO
291-- Clear PhoneNo
292UPDATE Customers SET PhoneNo = ''