Please see those 5 question in the exercise and see the example to understand how you will answer them

Anonymous
timer Asked: Oct 20th, 2018
account_balance_wallet $9.99

Question Description

If you are not computer majoring especially in SQL and Database dont take this assignment

this assignment for building queries in database

and thank you

Unformatted Attachment Preview

Exercise 5questions The exercise associated with this lab will require you to develop a number of stored procedures for the employees database. Some of the questions will build on the queries that you created in Lab 4. For each question, you will have to develop a simple stored procedure based on the supplied requirements. 1. Create a stored procedure to produce a list the current manager for a single department that is supplied by the user. The procedure should be named uspDeptManager and should include the department name as an input parameter. 2. Create a stored procedure to calculate the current average salary for a given position title and return a user defined variable @avgSalary that can be used in subsequent SQL statements. The procedure should be named uspAvgSalaryByPosition and should include the position title as an input parameter. (hint: this requires two parameters - one input (position title) and one output (average salary)). 3. Create a stored procedure to raise the salary for a user-supplied employee name by a given percentage. The procedure should be named uspEmpRaiseSalary and include two input parameters including a string parameter for the employee name and a double precision parameter for the percentage. The percentage parameter should be a decimal number between 0 and 1 where for example 20% would be represented as 0.2. 4. Create a stored procedure to add a new employee to the database based on the answer to question 9 in Lab 4. This stored procedure should be named uspAddNewEmp and should include input parameters to populate the database for all required tables. 5. Create a trigger that automatically changes the from_date in the dept_manager table such that when a new manager is inserted into the table, the from_date of the old manager is changed to the current date. Examples The objective of this lab is to give students experience with developing T-SQL stored procedures. Some Examples to get You Started In this example, we will use the purchasing database from the Advanced SQL Query database. If you don’t have this database, the script to create it is in the Lab 5 folder on Blackboard. Once the database is created, open a new query window and run the following SQL command: USE Purchasing; GO A Simple Stored Procedure Before you create a stored procedure, you need to have an idea of what your query will be doing. For the purpose of this simple example, we will select everything from the Client table as follows: SELECT * FROM client To create a stored procedure to run this query, we would use the CREATE PROCEDURE statement. This can also be abbreviated to CREATE PROC. Here is the code to create a stored procedure out of the above query: --Drop procedure if it exists IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'uspGetClientList') DROP PROCEDURE uspGetClientList GO --Create stored procedure CREATE PROCEDURE uspGetClientList AS SELECT * FROM client GO Notice that we named the procedure uspGeClientList. The usp is a naming convention that stands for user stored procedure. This name is used to call the stored procedure as follows: EXEC uspGetClientList Using Input Parameters When using stored procedures, you will often want to supply parameters to the stored procedure. For example, let’s say that we have a query with a WHERE clause such as this: SELECT * FROM client WHERE full_name = 'Kieran Keller' Hard coding the client name in the stored procedure defeats the purpose of the stored procedure because you will have to change the stored procedure every time you want to query a new client. Because of this, you will want to include client as a parameter in the stored procedure as follows. To do this, you use the @ symbol to signify the parameter and you give the parameter a name and a data type such as this: --Drop procedure if it exists IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'uspGetClientInfo') DROP PROCEDURE uspGetClientInfo GO --Create stored procedure CREATE PROCEDURE uspGetClientInfo @Name nvarchar(50) AS SELECT * FROM Client WHERE full_name = @Name GO Here we have created the parameter @City with a data type of nvarchar(30). When you want to query a city, you simply run the stored procedure and include the parameter in the EXEC statement as follows: EXEC uspGetClientInfo @Name = 'Kieran Keller' To make life easier on your users, you can also use the LIKE operator in the stored procedure instead of the =. --Drop procedure if it exists IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'uspGetClientInfo') DROP PROCEDURE uspGetClientInfo GO --Create stored procedure CREATE PROCEDURE uspGetClientInfo @Name nvarchar(50) AS SELECT * FROM client WHERE full_name LIKE @Name + '%' GO In the above procedures, a parameter must be supplied or the query will not run. You can get around this by using the NULL option when creating the parameter and the ISNULL term within the query. For example, in the following stored procedure, if @Name is not supplied, it runs the WHERE clause as Name=Name: --Drop procedure if it exists IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'uspGetClientInfo') DROP PROCEDURE uspGetClientInfo GO --Create stored procedure CREATE PROCEDURE uspGetClientInfo @Name nvarchar(50) = NULL AS SELECT * FROM client WHERE full_name = ISNULL(@Name,full_name) GO You can also supply multiple parameters to a stored procedure. Let’s say that we want to select the client with the client name and email. To do this, we could write the following procedure: --Drop procedure if it exists IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'uspGetClientInfo') DROP PROCEDURE uspGetClientInfo GO --Create stored procedure CREATE PROCEDURE uspGetClientInfo @Name nvarchar(50) = NULL, @Email nvarchar(60) = NULL AS SELECT * FROM client WHERE full_name LIKE ISNULL(@Name,full_name) AND email LIKE ISNULL(@Email ,email) + '%' GO You can run this procedure with any of the following: EXEC uspGetClientInfo @Name = 'Kieran' --or EXEC uspGetClientInfo @Name = 'Kieran', @Email = 'k' --or EXEC uspGetClientInfo @Email = 'kkeller' Using Output Parameters You can also set output parameters in stored procedures. You would do this when you would want to pass a value back from a stored procedure. For example, we might want to count how many orders that a client has placed. The query for this would be SELECT count(*) FROM client AS c INNER JOIN purchase as p ON p.client_id = c.client_id WHERE c.full_name = 'Kieran Keller' If we want to return this value from the stored procedure as a parameter, we would use the OUTPUT option when declaring the parameter: --Drop procedure if it exists IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'uspGetPurchaseCount') DROP PROCEDURE uspGetPurchaseCount GO --Create stored procedure CREATE PROCEDURE uspGetPurchaseCount @Name nvarchar(50), @PurchaseCount int OUTPUT AS SELECT @PurchaseCount = count(*) FROM client AS c INNER JOIN purchase as p ON p.client_id = c.client_id WHERE full_name LIKE ISNULL(@Name,full_name) + '%' Notice here that we have added the OUTPUT option to the @AddressCount parameter. To call this stored procedure, we would call it using the DECLARE term to first declare a parameter @AddressCount. Then in the EXEC command, we set the @AddressCount parameter equal to the @AddressCount OUTPUT. We then would SELECT @AddressCount to return the parameter: DECLARE @PC int EXEC uspGetPurchaseCount @Name = 'Kieran Keller', @PurchaseCount = @PC OUTPUT SELECT @PC Trigger Example In this example, we will add a new table called purchase_audit. This table will keep track of all purchase information in a denormalized table that will act as a log for any purchases made for the purpose of tracking potential purchase errors. We will then create a trigger that will add data to this table when a new purchase is made. First, let’s create our new table: CREATE TABLE dbo.purchase_audit (client_id int NOT NULL, purchase_id int NOT NULL, purchase_item_id int NOT NULL, product_id int NOT NULL, full_name varchar(255) NOT NULL, amount int NOT NULL, sku varchar(12) NOT NULL, name varchar(255) NOT NULL, price decimal NOT NULL CONSTRAINT PK_product_audit PRIMARY KEY CLUSTERED (client_id,purchase_id,purchase_item_id, product_id)); GO IF EXISTS (SELECT * FROM sys.objects WHERE type = 'TR' AND name = 'PurchaseAudit_Trigger') DROP TRIGGER PurchaseAudit_Trigger GO CREATE TRIGGER PurchaseAudit_Trigger on purchase_item AFTER INSERT AS BEGIN INSERT INTO purchase_audit ([client_id], [purchase_id], [purchase_item_id], [product_id], [full_name], [amount], [sku], [name], [price]) SELECT c.client_id, pu.purchase_id, i.purchase_item_id, prod.product_id, c.full_name, i.amount, prod.sku, prod.name, prod.price FROM inserted AS i INNER JOIN purchase AS pu ON i.purchase_id = pu.purchase_id INNER JOIN client AS c ON c.client_id = pu.client_id INNER JOIN product AS prod ON prod.product_id = i.product_id END GO Notice that we are using a table called inserted in the FROM clause of the trigger. This allows us to only use the inserted data in the select query for the insert. Now let’s test the trigger: INSERT INTO purchase([purchase_id],[purchase_no],[client_id]) VALUES(21,000000000021,2); GO INSERT INTO purchase_item ([purchase_item_id], [purchase_id], [product_id], [amount]) VALUES(101,21,10,4); GO SELECT * FROM purchase_audit ...
Purchase answer to see full attachment

Tutor Answer

Rimmer
School: Duke University

At...

flag Report DMCA
Review

Anonymous
Thanks, good work

Similar Questions
Related Tags

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors