· 9 years ago · Nov 26, 2016, 03:44 AM
1-- Lab 3 questions
2Drop Procedure BooksByAuthorName;
3Drop Procedure BooksByTitle;
4Drop Procedure AddCategory;
5Drop Procedure UpdateSuggestedPrice;
6Drop Procedure UpdateTitle;
7Drop Procedure CustomerBooks;
8Drop Procedure AddSaleDetail;
9Drop Table ArchiveEmployee;
10Drop Procedure ArchiveEmployeeTransactions;
11
12
13-- 1. Write a procedure BooksbyAuthorName that will find and display all books by a certain author. The procedure accepts author last name and first name as parameters.
14-- Display the ISBN, title, publisher name and suggested price of all books by that author. List in ascending title sequence. (3 marks)
15Create Procedure BooksByAuthorName(@FirstName varchar(30) = Null, @LastName varchar(30) = Null)
16as
17 If @FirstName Is Null or @LastName Is Null
18 Begin
19 RaisError('Error: FirstName and LastName must be provided', 16, 1);
20 End
21 Else
22 Begin
23 Select Title.ISBN, Title, Name as 'PublisherName', SuggestedPrice
24 From Publisher
25 Inner Join Title
26 on Publisher.PublisherCode = Title.PublisherCode
27 Inner Join AuthorTitle
28 on Title.ISBN = AuthorTitle.ISBN
29 Inner Join Author
30 on AuthorTitle.AuthorCode = Author.AuthorCode
31 Where FirstName = @FirstName
32 Order By Title Asc
33 End
34Return;
35Go
36
37Execute BooksByAuthorName 'George', 'Green';
38
39
40-- 2. Write a procedure BooksbyTitle that will accept part of a title as a parameter and find all books that have that part in the title. List the ISBN, title,
41-- suggested selling price and number in stock for these books. (2 marks)
42Create Procedure BooksByTitle(@Title varchar(40) = Null)
43as
44 If @Title Is Null
45 Begin
46 RaisError('Error: Title must be provided', 16, 1);
47 End
48 Else
49 Begin
50 Select ISBN, Title, SuggestedPrice, NumberInStock
51 From Title
52 Where Left(Title, 3) = @Title
53 End
54Return;
55Go
56
57Execute BooksByTitle 'Int';
58
59
60-- 3. Write a procedure AddCategory that will add a new category. The procedure will accept a description for the category as a parameter. Raise an appropriate error
61-- message if the description is already present in the table. Add this record to the Category table and if there are no errors select the new CategoryCode. (4 marks)
62Create Procedure AddCategory(@Description varchar(40) = Null)
63as
64 If @Description Is Null
65 Begin
66 RaisError('Error: The Description must be provided', 16, 1);
67 End
68 Else
69 Begin
70 Begin Transaction
71 If Exists (Select Description From Category Where Description = @Description)
72 Begin
73 RaisError('Error: This Description already exists', 16, 1);
74 Rollback Transaction
75 End
76 Else
77 Begin
78 Commit Transaction
79 ------------------
80 Insert Into Category(Description)
81 Values(@Description)
82 Select Distinct CategoryCode
83 From Title
84 End
85 End
86Return;
87Go
88
89Execute AddCategory 'Laptops';
90
91
92-- 4. Write a procedure UpdateSuggestedPrice that accepts an ISBN number and a new suggested price as parameters. If the ISBN does not exist, raise an error message.
93-- Update the suggested price to the new price if there are no errors. (4 marks)
94Create Procedure UpdateSuggestedPrice(@ISBN char(10) = Null, @NewSuggestedPrice smallmoney = Null)
95as
96 If @ISBN Is Null or @NewSuggestedPrice Is Null
97 Begin
98 RaisError('Error: ISBN and SuggestedPrice must be provided', 16, 1);
99 End
100 Else
101 Begin
102 Begin Transaction
103 If Not Exists (Select ISBN From Title Where ISBN = @ISBN)
104 Begin
105 RaisError('Error: This ISBN does not exist', 16, 1);
106 Rollback Transaction
107 End
108 Else
109 Begin
110 Commit Transaction
111 ------------------
112 Begin Transaction
113 Update Title
114 Set SuggestedPrice = @NewSuggestedPrice
115 If @@Error<>0
116 Begin
117 RaisError('Error: SuggestedPrice update failed', 16, 1);
118 Rollback Transaction
119 End
120 Else
121 Begin
122 Commit Transaction
123 ------------------
124 End
125 End
126 End
127Return;
128Go
129
130Execute UpdateSuggestedPrice '1021031071', 355.00;
131
132
133-- 5. Write a procedure UpdateTitle that accepts all the Title table fields as parameters and will update the title with those values. Raise error messages for the following:
134-- • The ISBN does not exist
135-- • The Category and/or Publisher Codes are not valid.
136-- (5 marks)
137Create Procedure UpdateTitle(@ISBN char(10) = Null, @Title varchar(40) = Null, @SuggestedPrice smallmoney = Null, @NumberInStock smallint = Null, @PublisherCode int = Null, @CategoryCode int = Null)
138as
139 If @ISBN Is Null
140 Begin
141 RaisError('Error: The ISBN must be provided', 16, 1);
142 End
143 Else
144 Begin
145 Begin Transaction
146 If Not Exists (Select ISBN From Title Where ISBN = @ISBN)
147 Begin
148 RaisError('Error: This ISBN does not exist', 16, 1);
149 Rollback Transaction
150 End
151 Else
152 Begin
153 Commit Transaction
154 ------------------
155 Begin Transaction
156 If @@Error<>0
157 Begin
158 RaisError('Error: Columns update in Table table failed', 16, 1);
159 Rollback Transaction
160 End
161 Else
162 Begin
163 Commit Transaction
164 ------------------
165 Begin Transaction
166 If Exists (Select CategoryCode From Title Where CategoryCode = @CategoryCode)
167 Begin
168 RaisError('Error: This Category Code already exists', 16, 1);
169 Rollback Transaction
170 End
171 Else
172 Begin
173 Commit Transaction
174 ------------------
175 Update Title
176 Set ISBN = @ISBN,
177 Title = @Title,
178 SuggestedPrice = @SuggestedPrice,
179 NumberInStock = @NumberInStock,
180 PublisherCode = @PublisherCode,
181 CategoryCode = @CategoryCode
182 Where ISBN = @ISBN
183 End
184 End
185 End
186 End
187Return;
188Go
189
190Execute UpdateTitle '1021031071', 'SQL in 30 days', 70.00, 8, 1, 207;
191
192
193-- 6. Write a procedure CustomerBooks that displays the amount spent on books purchased by each customer, who purchased books, during a certain month. The month is passed
194-- into the procedure as integer month and integer year parameters. Error messages are required for the following:
195-- • The month number is invalid (not 1 – 12)
196-- • The month and year are not before today’s date.
197-- If there are no errors, select the customer number, last name and the amount spent that month. DO NOT include GST in the amount spent. (4 marks)
198Create Procedure CustomerBooks (@CurrentMonth datetime = Null, @CurrentYear datetime = Null)
199as
200 If @CurrentMonth Is Null or @CurrentYear Is Null
201 Begin
202 RaisError('Error: Must provide a Month and a Year', 16, 1);
203 End
204 Else
205 Begin
206 Begin Transaction
207 Declare @FirstMonth datetime = 1;
208 Declare @LastMonth datetime = 12;
209 Select @CurrentMonth = Month(GetDate())
210 From Sale
211 Where Month(SaleDate) = @CurrentMonth
212 Select @CurrentYear = Year(GetDate())
213 From Sale
214 Where Year(SaleDate) = @CurrentYear
215 If @CurrentMonth < @FirstMonth or @CurrentMonth > @LastMonth
216 Begin
217 RaisError('Error: The Month is invalid', 16, 1);
218 Rollback Transaction
219 End
220 Else
221 Begin
222 Commit Transaction
223 ------------------
224 Begin Transaction
225 If @CurrentMonth < Month(GetDate())
226 Begin
227 RaisError('Error: This Month has already past. Please enter a current or future Month', 16, 1);
228 Rollback Transaction
229 End
230 Else
231 Begin
232 If @CurrentYear < Year(GetDate())
233 Begin
234 RaisError('Error: This Year has already past. Please enter a current or future year', 16, 1);
235 Rollback Transaction
236 End
237 Else
238 Begin
239 Commit Transaction
240 ------------------
241 Select Customer.CustomerNumber, LastName, Amount
242 From Customer
243 Inner Join Sale
244 on Customer.CustomerNumber = Sale.CustomerNumber
245 Inner Join SaleDetail
246 on Sale.SaleNumber = SaleDetail.SaleNumber
247 End
248 End
249 End
250 End
251Return;
252Go
253
254Execute CustomerBooks 11, 2016;
255
256
257-- 7. Write a procedure AddSaleDetail that will add a sale detail for a book purchased and will update the sale with that book information. The following data is passed to the
258-- procedure as parameters: sale number, ISBN and quantity. Specific error messages are required for the following:
259-- • The ISBN or sale numbers are not valid
260-- • The ISBN is already on that sale.
261-- If there are no errors:
262-- • Insert the Sale Detail record into SaleDetail table. The selling price will be the Suggested Price for that ISBN.
263-- • Update the book in the Title table to reduce the number in stock by the quantity
264-- • Update the Sale record subtotal, total and GST fields in Sale table to include the sale amount of the book purchased. (6 marks)
265Create Procedure AddSaleDetail (@SaleNumber int = Null, @ISBN char(10) = Null, @Quantity int = Null)
266as
267 If @SaleNumber Is Null or @ISBN Is Null or @Quantity Is Null
268 Begin
269 RaisError('Error: Must provide a Sale Number, ISBN, and quantity', 16, 1);
270 End
271 Else
272 Begin
273 Begin Transaction
274 Declare @MinISBN int = Min(@ISBN);
275 Declare @MaxISBN int = Max(@ISBN);
276 Declare @MinSaleNumber int = Min(@SaleNumber);
277 Declare @MaxSaleNumber int = Max(@SaleNumber);
278 If @ISBN < @MinISBN or @ISBN > @MaxISBN
279 Begin
280 RaisError('Error: The ISBN is invalid, it must be in the valid range', 16, 1);
281 Rollback Transaction
282 End
283 Else If @SaleNumber < @MinSaleNumber or @SaleNumber > @MaxSaleNumber
284 Begin
285 RaisError('Error: The Sale Number is invalid, it must be in the valid range', 16, 1);
286 Rollback Transaction
287 End
288 Else
289 Begin
290 Commit Transaction
291 ------------------
292 Begin Transaction
293 If Exists (Select ISBN From Title Where ISBN = @ISBN)
294 Begin
295 RaisError('Error: This ISBN already exists', 16, 1);
296 Rollback Transaction
297 End
298 Else
299 Begin
300 Commit Transaction
301 ------------------
302 Begin Transaction
303 If @@Error<>0
304 Begin
305 RaisError('The ISBN or Sale Number is not valid', 16, 1);
306 Rollback Transaction
307 End
308 Else
309 Begin
310 Commit Transaction
311 ------------------
312 Declare @SubTotal money;
313 Declare @GST money;
314 Declare @Total money;
315 Declare @Amount money;
316 Insert Into SaleDetail (SaleNumber, ISBN, Quantity)
317 Values(@SaleNumber, @ISBN, @Quantity);
318 Update Title
319 Set NumberInStock = NumberInStock - (Select Quantity From SaleDetail Where Quantity = @Quantity)
320 Update Sale
321 Set SubTotal = (Select SubTotal From Sale
322 Inner Join SaleDetail on Sale.SaleNumber = SaleDetail.SaleNumber
323 Where Amount = @Amount and SubTotal = @SubTotal and @SubTotal = @SubTotal + @Amount),
324 GST = (Select GST From Sale
325 Inner Join SaleDetail on Sale.SaleNumber = SaleDetail.SaleNumber
326 Where Amount = @Amount and GST = @GST and @GST = @GST + @Amount),
327 Total = (Select Total From Sale
328 Inner Join SaleDetail on Sale.SaleNumber = SaleDetail.SaleNumber
329 Where Amount = @Amount and Total = @Total and @Total = @Total + @Amount);
330 End
331 End
332 End
333 End
334Return;
335Go
336
337Execute AddSaleDetail '3003', 1021031078, 2;
338
339
340-- 8. Create table ArchiveEmployee. This table will be a duplicate table of the employee table including the following:
341-- a) use the same fields as the Employee table
342-- b) do not use the identity parameter on the EmployeeNumber of the ArchiveEmployee table
343-- c) do not put in foreign keys, check or default constraints. (1 marks)
344Create Table ArchiveEmployee
345(
346 EmployeeNumber int identity (300,1) not null
347 constraint PK_employeeNumber_employee159 Primary key,
348 SIN char (9) not null,
349 LastName varchar(30) not null,
350 FirstName varchar(30) not null,
351 Address varchar(40) not null,
352 City varchar(20) null,
353 Province char(2) null constraint df_archiveEmployee_province default 'AB',
354 PostalCode char(6) null
355 constraint CK_postal_emp159 check (postalcode like'[a-z][0-9][a-z][0-9][a-z][0-9]'),
356 HomePhone char(10) null constraint CK_archiveEmployee_Home_phone check ( HomePhone like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
357 workPhone char(10) null constraint CK_archiveEmployee_Work_phone check ( WorkPhone like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
358 Email varchar(30) null
359);
360
361
362-- 9. Write a procedure ArchiveEmployeeTransactions that will move employee information to the ArchiveEmployee table for storage. The employee will only be archived
363-- if they do not have any sales. An employee number will be passed to this procedure as a parameter. Error messages are required if the employee number does not exist
364-- or of the employee cannot be archived because they have sales. (5 marks)
365Create Procedure ArchiveEmployeeTransactions(@EmployeeNumber int = Null)
366as
367 If @EmployeeNumber Is Null
368 Begin
369 RaisError('Error: The Employee Number must be provided', 16, 1);
370 End
371 Else
372 Begin
373 Begin Transaction
374 If Not Exists (Select EmployeeNumber From Employee Where EmployeeNumber = @EmployeeNumber)
375 Begin
376 RaisError('Error: This Employee Number does not exist', 16, 1);
377 Rollback Transaction
378 End
379 Else
380 Begin
381 Declare @Sales int = (Select Count(SaleNumber) From SaleDetail);
382 If @Sales > 0
383 Begin
384 RaisError('Error: This Employee cannot be archived because it has sales', 16, 1);
385 Rollback Transaction
386 End
387 Else
388 Begin
389 Commit Transaction
390 ------------------
391 End
392 End
393 End
394Return;
395Go
396
397Execute ArchiveEmployeeTransactions '2';