6. Management is concerned about work productivity. Write a query that produces
ID: 3698304 • Letter: 6
Question
6. Management is concerned about work productivity. Write a query that produces a listing of each employee who has not worked on a sinlge project till date. The employees name (first and last names) and their department names are to be displayed.
7. The company’s senior project manager needs to access information about departments that manage projects for a specific set of projects, namely those located in Oklahoma or Texas. Create a view named department_projects that includes the department number, department name , project name and location. Write a Select statement that displays all rows that are accessible through the view.
8. Demonstrate the use of the view named department_projects(created above) for the senior manager by writing a Select statement to query the view to display all row information for projects belonging to department 3.
9. Create a table named sales_order. The table should have an identity column named so_number. There are 2 other columns – the sales order value(so_value) and the SSN for the employee who enters the sales order information (so_empSSN). Using INSERT statements insert 3 rows into the sales_order table. Write a SELECT statement that displays all the information in the sales_order table.
10 A. Write a stored procedure named replace_work_hours that updates the workHours column in the Assignment table. The procedure accepts 3 parameters corresponding to 3 columns in the table:workempSSN, workProjNumber and workHours. For a given employee SSN and project number, the work hours passed as a parameter to the procedure can replace the workHours currently stored in the table. 10 B. You have been notified by the manager of Project 20 to update the hours worked for an employee on the project. Execute the procedure replace_work_hours to store a new workHours column value of 15.5 for employee 999-55-5555 for project 20 and then display the new values in a result table.
11. Write an AFTER trigger named check_work_hours that fires whenever an UPDATE transaction executes that updates the value stored to the workHours column of the Assignment table. The trigger should check the new value to be assigned for the hours worked to enforce a business rule that an employee cannot report working in excess of 250 hours on a project. If the new value to be inserted exceeds 250 hours, then the UPDATE transaction should roll back. Display an appropriate error message.
Explanation / Answer
6.answer
Select lname,firstname,departname from employe where no of projects=0 and date= CURDATE();
7 answer
CREATE VIEW department_projects view AS
SELECT deptno,dept name,project name,location,
FROM emp
WHERE location=’Oklahoma or location= ‘Texas’;
8answer
CREATE VIEW department_projects view AS
SELECT *
FROM emp
WHERE deptno=3
9)answer
sales_order.table
CREATE TABLE sales_order. (so_number INT NOT NULL,
10a.answer
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE replace_work_hours
workProjNumber INT
workHours INT
workempSSN INT,
AS
BEGIN
SET NOCOUNT ON;
UPDATE Assignment table SET workHours = @workHours
END
10b.answer
CREATE PROCEDURE replace_work_hours
(workProjNumber INT
workHours INT
workempSSN INT,
@StatementType nvarchar(20) = ''
)
AS
BEGIN
IF @StatementType = 'Insert'
BEGIN
INSERT INTO Assignment table (workProjNumber ,
workHours,
END
IF @StatementType = 'Select'
BEGIN
select * from Assignment table
END
IF @StatementType = 'Update'
BEGIN
UPDATE employee SET
workProjNumber = @ workProjNumber, workHours
= @ workHours
, workempSSN = @ workempSSN,
city = @city
WHERE workProjNumber=20
END
11.answer
CREATE TRIGGER statement
A trigger defines a set of actions that are executed when a database event occurs on a specified table. A database event is a delete, insert, or update operation. For example, if you define a trigger for a delete on a particular table, the trigger's action occurs whenever someone deletes a row or rows from the table.
Along with constraints, triggers can help enforce data integrity rules with actions such as cascading deletes or updates. Triggers can also perform a variety of functions such as issuing alerts, updating other tables, sending e-mail, and other useful actions.
You can define any number of triggers for a single table, including multiple triggers on the same table for the same event.
You can create a trigger in any schema where you are the schema owner. To create a trigger on a table that you do not own, you must be granted the TRIGGER privilege on that table. The database owner can also create triggers on any table in any schema.
A trigger operates with the privileges of the owner of the trigger. See "Using SQL standard authorization" and "Privileges on views, triggers, and constraints" in the Java DB Developer's Guide for details.
The trigger does not need to reside in the same schema as the table on which the trigger is defined.
If a qualified trigger name is specified, the schema name cannot begin with SYS.
Syntax
CREATE TRIGGER TriggerName
{ AFTER | NO CASCADE BEFORE }
{ INSERT | DELETE | UPDATE [ OF column-Name [, column-Name]* ] }
ON table-Name
[ ReferencingClause ]
[ FOR EACH { ROW | STATEMENT } ] [ MODE DB2SQL ]
Triggered-SQL-statement
Code:
for ( opp: check_work_hours Trigger.New) {
opp.addError('error message');
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.