· 6 years ago · Nov 12, 2019, 06:06 PM
1-- chapter 16 in class
2
3/* 1. Write a set of action queries coded as a transaction to reflect the following change:
4United Parcel Service has been purchased by Federal Express Corporation and the new company is
5named FedUP. Rename one of the vendors and delete the other after updating the VendorID column
6in the Invoices table.
7*/
8USE AP;
9GO
10
11
12BEGIN TRY
13 BEGIN TRAN;
14 UPDATE Invoices
15 SET VendorID = 123
16 WHERE VendorID = 122
17 DELETE Vendors WHERE VendorName = 'United Parcel Service'
18 UPDATE Vendors
19 SET VendorName = 'FedUP'
20 WHERE VendorName = 'Federal Express Corporation'
21 COMMIT TRAN;
22END TRY
23BEGIN CATCH
24 ROLLBACK TRAN;
25END CATCH;
26
27/*2. Write a set of action queries coded as a transaction to move rows from the Invoices
28table to the InvoiceArchive table. Insert all paid invoices from Invoices into InvoiceArchive,
29but only if the invoice doesn’t already exist in the InvoiceArchive table. Then, delete all
30paid invoices from the Invoices table, but only if the invoice exists in the InvoiceArchive table.
31*/
32
33USE AP;
34
35BEGIN TRAN;
36 INSERT InvoiceArchive
37 SELECT Invoices.*
38 FROM Invoices LEFT JOIN InvoiceArchive
39 ON Invoices.InvoiceID = InvoiceArchive.InvoiceID
40 WHERE Invoices.InvoiceTotal - Invoices.CreditTotal - Invoices.PaymentTotal = 0 AND InvoiceArchive.InvoiceID IS NULL;
41
42 DELETE Invoices
43 WHERE InvoiceID IN
44 (SELECT InvoiceID
45 FROM InvoiceArchive);
46COMMIT TRAN;
47
48
49
50---------------------------------------------------------------------------
51
52-- chapter 15 in class
53
54------------------------------- Stored Procedures ------------------------------------
55
56/*1. Create a stored procedure named spBalanceRange that accepts three optional parameters.
57The procedure should return a result set consisting of VendorName, InvoiceNumber, and Balance
58for each invoice with a balance due, sorted with largest balance due first.
59The parameter @VendorVar is a mask that’s used with a LIKE operator to filter by vendor name,
60as shown in figure 15-5. @BalanceMin and @BalanceMax are parameters used to specify the requested
61range of balances due. If called with no parameters or with a maximum value of 0, the procedure
62should return all invoices with a balance due.
63*/
64USE AP;
65GO
66
67CREATE OR ALTER PROC spBalanceRange
68 @VendorVar varchar(40) = '%',
69 @BalanceMin money = 0,
70 @BalanceMax money = 0
71AS
72IF @BalanceMax = 0
73 SELECT VendorName, InvoiceNumber, (InvoiceTotal - CreditTotal - PaymentTotal) AS Balance
74 FROM Vendors join Invoices
75 on Vendors.VendorID = Invoices.VendorID
76 WHERE (InvoiceTotal - CreditTotal - PaymentTotal) > 0
77 ORDER BY Balance DESC
78ELSE
79 SELECT VendorName, InvoiceNumber, (InvoiceTotal - CreditTotal - PaymentTotal) AS Balance
80 FROM Vendors join Invoices
81 on Vendors.VendorID = Invoices.VendorID
82 WHERE (InvoiceTotal - CreditTotal - PaymentTotal) > 0 AND VendorName Like @VendorVar AND (InvoiceTotal - CreditTotal - PaymentTotal) BETWEEN @BalanceMin AND @BalanceMax
83
84GO
85
86/*2. Code three calls to the procedure created in exercise 1:
87(a) passed by position with @VendorVar='M%' and no balance range
88(b) passed by name with @VendorVar omitted and a balance range from $200 to $1000
89(c) passed by position with a balance due that’s less than $200 filtering for vendors whose names
90 begin with C or F
91*/
92EXEC spBalanceRange 'M%';
93EXEC spBalanceRange @BalanceMin = 200, @BalanceMax = 1000;
94EXEC spBalanceRange '[C,F]%', 0, 200;
95
96
97/*3. Create a stored procedure named spDateRange that accepts two parameters, @DateMin and
98@DateMax, with data type varchar and default value null. If called with no parameters or with
99null values, raise an error that describes the problem. If called with non-null values,
100validate the parameters. Test that the literal strings are valid dates and test that @DateMin
101is earlier than @DateMax. If the parameters are valid, return a result set that includes the
102InvoiceNumber, InvoiceDate, InvoiceTotal, and Balance for each invoice for which the InvoiceDate
103is within the date range, sorted with earliest invoice first.
104*/
105
106USE AP;
107GO
108
109CREATE OR ALTER PROC spDataRange
110 @DateMin varchar(15) = null,
111 @DateMax varchar(15) = null
112AS
113IF @DateMin is null
114 THROW 50001, 'DateMin cannot be null', 1;
115IF @DateMax is null
116 THROW 50001, 'DateMax cannot be null', 1;
117IF ISDATE(@DateMin) = 0
118 THROW 50001, 'DateMin is not a valid date', 1;
119IF ISDATE(@DateMax) = 0
120 THROW 50001, 'DateMax is not a valid date', 1;
121IF CONVERT(datetime, @DateMax) < CONVERT(datetime, @DateMin)
122 THROW 50001, 'DateMax cannot be less than DateMin', 1;
123 SELECT InvoiceNumber, InvoiceDate, InvoiceTotal, (InvoiceTotal - CreditTotal - PaymentTotal) AS Balance
124 FROM Invoices
125 WHERE InvoiceDate BETWEEN @DateMin AND @DateMax;
126
127
128
129--EXEC spDataRange '2001-01-01', '2000-01-01';
130
131/*4. Code a call to the stored procedure created in exercise 3 that returns invoices with an
132InvoiceDate between December 10 and December 20, 2015. This call should also catch any errors
133that are raised by the procedure and print the error number and description.
134*/
135EXEC spDataRange '2015-12-10', '2015-12-20';
136
137
138--------------------------------------- Functions -------------------------------------------
139
140/*5. Create a scalar-valued function named fnUnpaidInvoiceID that returns the InvoiceID of the earliest invoice with an unpaid balance.
141Test the function in the following SELECT statement:
142SELECT VendorName, InvoiceNumber, InvoiceDueDate,
143 InvoiceTotal - CreditTotal - PaymentTotal AS Balance
144FROM Vendors JOIN Invoices
145 ON Vendors.VendorID = Invoices.VendorID
146WHERE InvoiceID = dbo.fnUnpaidInvoiceID();
147*/
148GO
149IF OBJECT_ID('fnUnpaidInvoiceID') IS NOT NULL
150 DROP FUNCTION fnUnpaidInvoiceID
151GO
152
153CREATE FUNCTION fnUnpaidInvoiceID()
154RETURNS INT
155BEGIN
156RETURN (
157 SELECT InvoiceID
158 FROM Invoices
159 WHERE InvoiceTotal - CreditTotal - PaymentTotal > 0 AND InvoiceDate = (
160 SELECT MIN(InvoiceDate)
161 FROM Invoices
162 WHERE InvoiceTotal - CreditTotal - PaymentTotal > 0))
163END
164GO
165
166
167/*6. Create a table-valued function named fnDateRange, similar to the stored procedure of exercise 3. The function requires two parameters
168of data type smalldatetime. Don't validate the parameters. Return a result set that includes the InvoiceNumber, InvoiceDate, InvoiceTotal,
169and Balance for each invoice for which the InvoiceDate is within the date range. Invoke the function form within a SELECT statement to
170return those invoices with InvoiceDate between December 10 and December 20, 2015.
171*/
172GO
173USE AP;
174GO
175IF OBJECT_ID('fnDateRange') IS NOT NULL
176 DROP FUNCTION fnDateRange
177GO
178
179CREATE FUNCTION fnDateRange
180 (@DateMin smalldatetime, @DateMax smalldatetime)
181RETURNS table
182
183RETURN
184(SELECT InvoiceNumber, InvoiceDate, InvoiceTotal,
185 InvoiceTotal - CreditTotal- PaymentTotal AS Balance
186FROM Invoices
187WHERE InvoiceDate BETWEEN @DateMin AND @DateMax);
188GO
189
190SELECT *
191FROM dbo.fnDateRange('12/10/15','12/20/15');
192
193/*7. Use the function created in exercise 6 in a SELECT statement that returns five columns: VendorName and the four columns
194returned by the function.
195*/
196SELECT VendorName, FunctionTable.*
197FROM Vendors JOIN Invoices
198 ON Vendors.VendorID = Invoices.VendorID
199JOIN dbo.fnDateRange('12/10/11','12/20/11') AS FunctionTable
200 ON Invoices.InvoiceNumber = FunctionTable.InvoiceNumber;
201-------------------------------------- Triggers -----------------------------------------------
202
203/*8. Create a trigger for the Invoices table that automatically inserts the vendor name and address for a paid invoice into a table named ShippingLabels.
204The trigger should fire any time the PaymentTotal column of the Invoices table is updated. The structure of the ShippingLabels table is as follows:
205CREATE TABLE ShippingLabels
206(VendorName varchar(50),
207VendorAddress1 varchar(50),
208VendorAddress2 varchar(50),
209VendorCity varchar(50),
210VendorState char(2),
211VendorZipCode varchar(20));
212
213Use this UPDATE statement to test the trigger:
214 UPDATE Invoices
215 SET PaymentTotal = 67.92, PaymentDate = '2016-04-23'
216 WHERE InvoiceID = 100;
217*/
218GO
219USE AP
220GO
221IF OBJECT_ID('ShippingLabels') IS NOT NULL
222 DROP TABLE ShippingLabels
223GO
224CREATE TABLE ShippingLabels
225(VendorName varchar(50),
226VendorAddress1 varchar(50),
227VendorAddress2 varchar(50),
228VendorCity varchar(50),
229VendorState char(2),
230VendorZipCode varchar(20));
231
232GO
233
234
235CREATE OR ALTER TRIGGER ShippingLabels_Insert
236 ON Invoices
237 AFTER UPDATE, INSERT
238AS
239 IF EXISTS
240 (SELECT *
241 FROM Invoices
242 WHERE ((InvoiceTotal - CreditTotal - PaymentTotal) = 0))
243 INSERT INTO ShippingLabels
244 (VendorName, VendorAddress1, VendorAddress2, VendorCity, VendorState, VendorZipCode)
245 SELECT *
246 FROM ShippingLabels
247GO
248
249
250UPDATE Invoices
251SET PaymentTotal = 67.92, PaymentDate = '2016-04-23'
252WHERE InvoiceID = 100;
253
254SELECT *
255FROM ShippingLabels;
256
257/*9. Write a trigger that prohibits duplicate values except for nulls in the NoDupName column of the following table:
258 CREATE TABLE TestUniqueNulls
259 (RowID int IDENTITY NOT NULL,
260 NoDupName varchar(20) NULL);
261 If an INSERT or UPDATE statement creates a duplicate value, roll back the statement and return an error message.
262 Write a series of INSERT statements that tests the duplicate null values are allowed but duplicates of other values are not.
263*/
264IF OBJECT_ID('TestUniqueNulls') IS NOT NULL
265 DROP TABLE TestUniqueNulls;
266GO
267
268CREATE TABLE TestUniqueNulls
269 (RowID int IDENTITY NOT NULL,
270 NoDupName varchar(20) NULL);
271
272GO
273CREATE OR ALTER TRIGGER NoDuplicates
274ON TestUniqueNulls
275AFTER INSERT, UPDATE
276AS
277BEGIN
278 IF
279 (Select Count(Distinct A.NoDupName)
280 FROM TestUniqueNulls AS A join TestuniqueNulls AS B
281 ON A.NoDupName = B.noDupName) > 1
282 BEGIN
283 ROLLBACK TRAN
284 RAISERROR ('Duplicate value', 11, 1)
285 END
286END
287GO
288
289
290INSERT INTO TestUniqueNulls
291Values('name3');
292
293SELECT *
294FROM TestUniqueNulls;
295
296
297-------------------------------------------------------------------------------------------------
298
299-- chapter 14 in class
300
301/*1. Write a script that declares and sets a variable that’s equal to the
302total outstanding balance due. If that balance due is greater than $10,000.00,
303the script should return a result set consisting of VendorName, InvoiceNumber, InvoiceDueDate,
304and Balance for each invoice with a balance due, sorted with the oldest due date first.
305If the total outstanding balance due is less than $10,000.00, the script should return the
306message “Balance due is less than $10,000.00.”
307*/
308 USE AP;
309
310 DECLARE @TotalDue money;
311
312 SET @TotalDue = (SELECT SUM(InvoiceTotal - PaymentTotal - CreditTotal)
313 FROM Invoices);
314
315IF @TotalDue > 10000.00
316 SELECT VendorName, InvoiceNumber, InvoiceDueDate, @TotalDue AS Balance
317 FROM Invoices join Vendors
318 on Invoices.VendorID = Vendors.VendorID
319 ORDER BY InvoiceDueDate DESC;
320ELSE
321 PRINT 'Balance due is less than $10,000.00.';
322GO
323
324/*
3252. The following script uses a derived table to return the date and invoice total of the earliest
326invoice issued by each vendor. Write a script that generates the same result set but uses a
327temporary table in place of the derived table. Make sure your script tests for the existence
328of any objects it creates.
329
330USE AP;
331
332SELECT VendorName, FirstInvoiceDate, InvoiceTotal
333FROM Invoices
334JOIN
335 (SELECT VendorID, MIN(InvoiceDate) AS FirstInvoiceDate
336 FROM Invoices
337 GROUP BY VendorID) AS FirstInvoice
338ON (Invoices.VendorID = FirstInvoice.VendorID AND
339 Invoices.InvoiceDate = FirstInvoice.FirstInvoiceDate)
340JOIN Vendors
341ON Invoices.VendorID = Vendors.VendorID
342ORDER BY VendorName, FirstInvoiceDate;
343*/
344
345IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
346 DROP TABLE #TempTable;
347
348SELECT VendorName, FirstInvoiceDate, InvoiceTotal
349INTO #TempTable
350FROM Invoices
351JOIN
352 (SELECT VendorID, MIN(InvoiceDate) AS FirstInvoiceDate
353 FROM Invoices
354 GROUP BY VendorID) AS FirstInvoice
355ON (Invoices.VendorID = FirstInvoice.VendorID AND
356 Invoices.InvoiceDate = FirstInvoice.FirstInvoiceDate)
357JOIN Vendors
358ON Invoices.VendorID = Vendors.VendorID
359ORDER BY VendorName, FirstInvoiceDate;
360
361SELECT *
362FROM #TempTable;
363--THIS DOESN"T WORK FIX
364
365
366
367/*
3683. Write a script that generates the same result set as the code shown in exercise 2,
369but uses a view instead of a derived table. Also write the script that creates the view.
370Make sure that your script tests for the existence of the view.
371*/
372DROP VIEW IF EXISTS VIEW1
373GO
374
375CREATE VIEW VIEW1
376AS
377(SELECT VendorName, FirstInvoiceDate, InvoiceTotal
378FROM Invoices
379JOIN
380 (SELECT VendorID, MIN(InvoiceDate) AS FirstInvoiceDate
381 FROM Invoices
382 GROUP BY VendorID) AS FirstInvoice
383ON (Invoices.VendorID = FirstInvoice.VendorID AND
384 Invoices.InvoiceDate = FirstInvoice.FirstInvoiceDate)
385JOIN Vendors
386ON Invoices.VendorID = Vendors.VendorID);
387
388GO
389SELECT *
390FROM VIEW1;
391
392/*
3934. Write a script that uses dynamic SQL to return a single column that represents the
394number of rows in the first table in the current database. The script should automatically
395choose the table that appears first alphabetically, and it should exclude tables named
396dtproperties and sysdiagrams. Name the column CountOfTable, where Table is the chosen table name.
397
398Hint: Use the sys.tables catalog view.
399*/
400DECLARE @Table1 VARCHAR(30)
401
402SELECT @Table1 = MIN(NAME)
403FROM sys.tables
404WHERE NAME <> 'dtproperties' and NAME <> 'sysdiagrams'
405
406exec ('SELECT COUNT(*) AS CountOf'+ @Table1+' '+'FROM'+' '+ @Table1 );