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...

1 answer below »

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 XXXXXXXXXXNOT 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 XXXXXXXXXXINT 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 co
ect. You can verify by
unning 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', 'Dea
orn', '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', ' XXXXXXXXXX', 1, 250000, 'y', 1 ),
('Fred', 'Sanford', '1960/12/10', ' XXXXXXXXXX', 1, 75000, 'y', 1 ),
('Amy', 'Jones', '1988/08/30', ' XXXXXXXXXX', 1, 75000, 'y', 2 ),
('Sam', 'Temm', '1960/11/22', ' XXXXXXXXXX', 3, 75000, 'y', 2 ),
('Nicole', 'Simmons', '1972/01/01', ' XXXXXXXXXX', 1, 10, 'h', 2 ),
('Andy', 'Donny', '1980/09/22', ' XXXXXXXXXX', 2, 75000, 'h', 3 ),
('De
ie', 'Andrews', '1990/06/30', ' XXXXXXXXXX', 3, 75000, 'y', 3 ),
('George', 'Frank', '1984/05/12', ' XXXXXXXXXX', 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, '8544
', 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 * XXXXXXXXXX * datediff(year, PurchaseDate, getdate()))), 1) as [Cu
ent
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,
XXXXXXXXXXInvoiceNumber, 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

chapter13homework-tv105byl.pdf chapter-15-homework-iggopsqr.pdf cis111databases-3-fgr5thov-no1d5e4f.zip chapter13homeworksolution-converted-qolpzi0d-mtvbrhcf.docx

Answered 1 days After Apr 29, 2022

Solution

Aditya answered on Apr 30 2022

105 Votes

SOLUTION.PDF

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...

Solution

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment