1CIS 111 Chapter 13 Homework Using the SQL Server Management Studio, or the Azure Data Studio, complete the following tasks, in order You will be able to see how a script works, inserting...

y


1 CIS 111 Chapter 13 Homework Using the SQL Server Management Studio, or the Azure Data Studio, complete the following tasks, in order You will be able to see how a script works, inserting data, see how views, procedures and triggers work. Even though we have not covered procedures and triggers, completing this lab will help in understanding how to accomplish project 2. Upload your output and answers to questions, along with your working scripts. NOTE: Please create the database, and include your initials in place of my initials. Please note: Please change the all of the “USE” statements below to reflect the use of your initials instead of mine to: CIS111Asset CIS 111 Chapter 13 Homework STEP1, Create the database, using your initials. Note: You would need to change all other references to my initials also. CREATE DATABASE CIS111AssetRRJ GO STEP 2, create the tables USE CIS111AssetRRJ /* drop the tables */ IF OBJECT_ID('Employee') IS NOT NULL DROP TABLE Employee IF OBJECT_ID('Asset') IS NOT NULL DROP TABLE Asset IF OBJECT_ID('Agency') IS NOT NULL DROP TABLE Agency GO USE CIS111AssetRRJ /* create the agency table */ CREATE TABLE Agency (AgencyID INT NOT NULL IDENTITY PRIMARY KEY, AgencyName VARCHAR(30) NOT NULL, City VARCHAR(30) NOT NULL, State CHAR(2) NOT NULL) GO use CIS111AssetRRJ /* Create the employee table */ CREATE TABLE Employee (EmployeeID INT NOT NULL IDENTITY PRIMARY KEY, EmpFirstName VARCHAR(50) NOT NULL, EmpLastName VARCHAR(50) NOT NULL, DateOfBirth DATETIME NOT NULL, PhoneNumber VARCHAR(14), ReportTo INT NOT NULL, Pay MONEY NOT NULL, PayType CHAR(1) NOT NULL, CHECK (PayType IN ('Y', 'H', 'y', 'h')), AgencyID INT NOT NULL REFERENCES Agency(AgencyID)) GO USE CIS111AssetRRJ /* Create the Assets table */ CREATE TABLE Asset (AssetID INT NOT NULL IDENTITY PRIMARY KEY, PurchaseDate DATETIME NOT NULL, Description VARCHAR(50) NOT NULL, PurchaseAmount Money NOT NULL, SerialNo VARCHAR(14) NULL, AgencyID INT NOT NULL REFERENCES Agency(AgencyID)) --GO not necessary, as this is the last statement in the batch CIS 111 Chapter 13 Homework STEP 3, Create a Trigger A trigger is a database object used to make sure that incoming data is within the proper parameters as outlined by the database architect. We create the trigger before inserting data to make sure the data is correct. You can verify by running a select * from employees after the data has been entered. Please open a new query window and insert this statement. We will further explore triggers in a couple of weeks. USE CIS111AssetRRJ GO -- This trigger will verify an input is stored as uppercase IF OBJECT_ID('Payroll_INSERT_UPDATE') IS NOT NULL DROP TRIGGER Payroll_INSERT_UPDATE GO CREATE TRIGGER Payroll_INSERT_UPDATE ON Employee AFTER INSERT,UPDATE AS UPDATE Employee SET Paytype = UPPER(Paytype) WHERE EmployeeID IN (SELECT EmployeeID FROM Inserted) GO CIS 111 Chapter 13 Homework STEP 4, Add the data Please open a new query window and insert the following statements. USE CIS111AssetRRJ /* add data to Agency table */ INSERT INTO Agency (AgencyName, City, State) VALUES ('One', 'Dearborn', 'MI'), ('Two', 'Southfield', 'MI'), ('Three', 'Royal Oak', 'MI') GO USE CIS111AssetRRJ /* add data to Employees table */ INSERT INTO Employee (EmpFirstName, EmpLastName, DateOfBirth, PhoneNumber, ReportTo, Pay, PayType, AgencyID) VALUES ('Bob', 'James', '1965/10/08', '3138456356', 1, 250000, 'y', 1 ), ('Fred', 'Sanford', '1960/12/10', '3138456357', 1, 75000, 'y', 1 ), ('Amy', 'Jones', '1988/08/30', '3138456358', 1, 75000, 'y', 2 ), ('Sam', 'Temm', '1960/11/22', '3138456359', 3, 75000, 'y', 2 ), ('Nicole', 'Simmons', '1972/01/01', '3138456301', 1, 10, 'h', 2 ), ('Andy', 'Donny', '1980/09/22', '3138456302', 2, 75000, 'h', 3 ), ('Debbie', 'Andrews', '1990/06/30', '3138456303', 3, 75000, 'y', 3 ), ('George', 'Frank', '1984/05/12', '3138456304', 2, 12, 'h', 3 ) GO USE CIS111AssetRRJ /* add data to Employees table */ INSERT INTO Asset (PurchaseDate, Description, PurchaseAmount, SerialNo, AgencyID) VALUES ('2010/03/03', 'Computer', 500, '94ew33098', 1 ), ('2011/03/03', 'Desk', 750, 'None', 1 ), ('2010/03/03', 'Microwave', 200, '22305', 2 ), ('2011/03/03', 'Painting', 3000, 'None', 2 ), ('2012/03/01', 'Painting', 1000, 'None', 2 ), ('2010/03/03', 'Desk', 2000, 'None', 3 ), ('2013/03/03', 'Microwave', 200, '8544rb', 3 ), ('2011/03/03', 'computer', 500, '1234ww30256', 3 ) Question 1 - Write a query that will verify that the data has been inserted. CIS 111 Chapter 13 Homework STEP 5, Create a view to show payroll Please open a new query window and insert this statement. USE CIS111AssetRRJ GO --Create the view to show yearly pay /* drop the object */ IF OBJECT_ID('YearlyPay') IS NOT NULL DROP VIEW YearlyPay GO Create VIEW YearlyPay AS SELECT upper(right(EmpFirstname,1)) + '. ' + EmpLastName AS [Employee Name], '$' + cast (CASE WHEN Paytype = 'H' THEN Pay * 2080 ELSE Pay END as VARCHAR) AS [Yearly Pay] FROM employee Question 2: Run the view and submit the first 5 lines of the output results. SELECT * FROM YearlyPay CIS 111 Chapter 13 Homework STEP 6, Create a view to show the inventory Please open a new query window and insert this statement. USE CIS111AssetRRJ GO --Create the view to show the assets and other information /* drop the object */ IF OBJECT_ID('Inventory') IS NOT NULL DROP VIEW Inventory GO CREATE VIEW Inventory AS SELECT *, convert(Money, PurchaseAmount * (1 - (.2 * datediff(year, PurchaseDate, getdate()))), 1) as [Current Value], year( PurchaseDate) + 5 AS [Depreciated Year] FROM Asset Question 3: Run the view and submit the first 5 lines of the output results. SELECT * FROM Inventory CIS 111 Chapter 13 Homework STEP 7, Create a view to show yearly pay and the supervisor Please open a new query window and insert this statement. USE CIS111AssetRRJ GO --Create the view to show yearly pay -- and supervisor /* drop the object */ IF OBJECT_ID('YearlyPayS') IS NOT NULL DROP VIEW YearlyPayS GO CREATE VIEW YearlyPayS AS SELECT upper(right(e2.EmpFirstname,1)) + '. ' + e2.EmpLastName AS [Employee Name], '$' + cast (CASE WHEN e2.Paytype = 'H' THEN e2.Pay * 2080 ELSE e2.Pay END as VARCHAR) AS [Yearly Pay], e1.EmpFirstName + ' ' + e1.EmpLastName AS Supervisor FROM employee AS e1 join employee AS e2 ON e1.employeeID = e2.Reportto Question 4: Run the view and submit the first 5 lines of the output results. SELECT * FROM YearlyPayS CIS 111 Chapter 13 Homework STEP 8, Create a procedure to get the supervisors name A procedure is a database object used similar to a view, to control access to data. The procedure does have more capabilities since we can have incoming and outgoing variables. You can verify by running a procedure below. Please open a new query window and insert this statement. We will further explore procedures in a couple of weeks. . --Procedure USE CIS111AssetRRJ GO /* create Stored procedure that will take a name and show the supervisor */ IF OBJECT_ID('spBossInfo') IS NOT NULL DROP PROC spBossInfo GO CREATE PROC spBossInfo @InName varchar(30) = '%', @OutBoss varchar(30) OUTPUT AS SET @OutBoss = ( SELECT EmpFirstName + ' ' + EmpLastName FROM Employee WHERE employeeID = (Select ReportTo from employee where EmpLastName = @InName) ) Question 5 --Run the procedure --Please open a new query window and insert this statement. - submit the output results. USE CIS111AssetRRJ -- Set a variable to hold incoming information Declare @LName VARCHAR(30) -- Set a variable to hold incoming information Declare @LBoss VARCHAR(30) set @LName = 'Sanford' exec spBossInfo @LName, @LBoss OUTPUT print 'The supervisor of' + @LName +' is' + @LBoss 1 CIS111 - Chapter 15 Homework Using the AP database, complete the following tasks. This lab will walk you thru the process of creating a procedure and trigger. Please run each of the queries in the order as outlined in this lab. Upload your Completed queries, results and answers to each of the questions. NOTE: Please put your initials in each object name. -- ============================================= -- Author: Bob James -- Create date: 12/4/2021 -- Description: Chapter 15 Homework -- for each table, please add your initials to the -- table name in place of my initials -- ================================================ use CIS111_AP -- ================================================ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Step 1 - Create tables /* Create the 4 Tables needed for this lab Make sure you use your initials  */ use CIS111_AP IF OBJECT_ID('VendorsRRJ') IS NOT NULL DROP TABLE VendorsRRJ IF OBJECT_ID('InvoicesRRJ') IS NOT NULL DROP TABLE InvoicesRRJ IF OBJECT_ID('VendorCopyRRJ') IS NOT NULL DROP TABLE VendorCopyRRJ IF OBJECT_ID('InvoiceCopyRRJ') IS NOT NULL DROP TABLE InvoiceCopyRRJ GO SELECT * INTO VendorsRRJ FROM Vendors SELECT * INTO InvoicesRRJ FROM Invoices SELECT * INTO VendorCopyRRJ FROM Vendors SELECT * INTO InvoiceCopyRRJ FROM Invoices GO 2 -- ============================================= -- Step 2 Part A Create procedure use CIS111_AP go /* drop if exists in database */ drop proc spVendorStatementRRJ go CREATE PROCEDURE spVendorStatementRRJ AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT VendorsRRJ.VendorID, VendorName, InvoiceNumber, InvoiceDate, InvoiceTotal from VendorsRRJ join InvoicesRRJ on VendorsRRJ.VendorID = InvoicesRRJ.VendorID END GO ____________________________________________________________________ Question 1: Run this procedure. Record the result? ____________________________________________________________________ use CIS111_AP GO /* Step 2 Part B create Stored procedure */ IF OBJECT_ID('spInvoiceReportRRJ') IS NOT NULL DROP PROC spInvoiceReportRRJ GO CREATE PROC spInvoiceReportRRJ AS