1. The management of AdventureWorks Incorporation wants that whenever the pay rate of an employee is modified, its effect on the monthly salary of that employee should be displayed. John, the Database Developer at AdventureWorks, has been asked to resolve this problem. Help John to find an appropriate solution.
Ans. https://drive.google.com/open?id=0B-yL1mP3zqTbTGZTV3Bsa1VhWTA
2.The management of AdventureWorks Incorporation has decided that no user should be able to change the prices of products. In addition, the management wants that all the attempts to change the price should be saved in a temporary table, Temp. John, the Database Developer, has been asked to make significant changes in the database to implement this policy. What should John do to achieve the same?
Ans. https://drive.google.com/open?id=0B-yL1mP3zqTbTGZTV3Bsa1VhWTA
3. Create a trigger on the Product table to ensure that if the value for the SafetyStockLevel column becomes less than or equal to the specified ReorderPoint column for a product because of an update operation, a message informing that the reorder level has been attained should be displayed. (Use the AdventureWorks database)
Ans. https://drive.google.com/open?id=0B-yL1mP3zqTbTGZTV3Bsa1VhWTA
4. Create a trigger to ensure that the average of the values in the Rate column of the EmployeePayHistory table should not be more than 20 when the value of the rate is increased. (Use the AdventureWorks database)
Ans. https://drive.google.com/open?id=0B-yL1mP3zqTbTGZTV3Bsa1VhWTA
5. The management of AdventureWorks wants that whenever the account number of a user is modified, the old account number, as well as the new account number, should be instantaneously displayed to the user. How can you accomplish the desired task?
Ans. https://drive.google.com/open?id=0B-yL1mP3zqTbTGZTV3Bsa1VhWTA
6. Create a trigger named Emp_Update on the EmployeeDetail table. This trigger should restrict a user from performing any kind of DML operation on the table before 9 A.M and after 5 P.M. (Use the AdventureWorks database)
Ans. https://drive.google.com/open?id=0B-yL1mP3zqTbTGZTV3Bsa1VhWTA
7.Create a trigger named Emp_Delete_Trg on the EmployeeDetails table. This trigger should restrict the deletion of records from the table if the designation of an employee is Manager. (Use the AdventureWorks database)
Ans. https://drive.google.com/open?id=0B-yL1mP3zqTbTGZTV3Bsa1VhWTA
8. Create a trigger named Emp_Update_Trg that will restrict the updation of salary of an employee if the new salary is less than the previous salary in the EmployeeDetails table. (Use the AdventureWorks database)
Ans. https://drive.google.com/open?id=0B-yL1mP3zqTbTGZTV3Bsa1VhWTA
9. Create a trigger named Emp_Nested_Trg on the EmployeeDetails table for an insert operation. This trigger should ensure that a new record being inserted into the EmployeeDetails table has a matching record in the DeptDetails table. Otherwise, the insert operation is rolled back. (Use the AdventureWorks database)
Ans. https://drive.google.com/open?id=0B-yL1mP3zqTbTGZTV3Bsa1VhWTA
10. Consider the following statement:
CREATE VIEW Emp_Dept
AS
SELECT E.EmployeeID, E.EmpName, E.Designation, E.Salary, D.DeptNo, D.DeptName FROM EmployeeDetails E INNER JOIN DeptDetails D ON E.DeptNo=D.DeptNo A view named Emp_Dept has been created on the base tables, EmployeeDetails and DeptDetails, by using the preceding statement. John is the Database Developer with AdventureWorks Incorporation. He wants that the users should be able to insert data in the underlying base tables by using the view. He can implement the desired functionality if the insert operation affects a single table. However, the same cannot be done if multiple tables are involved. He wants to be able to insert data in both the tables with the help of a single insert statement. Help John in resolving the problem.
Ans. https://drive.google.com/open?id=0B-yL1mP3zqTbTGZTV3Bsa1VhWTA
11. John is a Database Developer. He is not able to delete the unwanted records from the base tables by using the view, Emp_Dept. Help John in resolving the problem.
Ans. https://drive.google.com/open?id=0B-yL1mP3zqTbTGZTV3Bsa1VhWTA
Ans. https://drive.google.com/open?id=0B-yL1mP3zqTbTGZTV3Bsa1VhWTA
2.The management of AdventureWorks Incorporation has decided that no user should be able to change the prices of products. In addition, the management wants that all the attempts to change the price should be saved in a temporary table, Temp. John, the Database Developer, has been asked to make significant changes in the database to implement this policy. What should John do to achieve the same?
Ans. https://drive.google.com/open?id=0B-yL1mP3zqTbTGZTV3Bsa1VhWTA
3. Create a trigger on the Product table to ensure that if the value for the SafetyStockLevel column becomes less than or equal to the specified ReorderPoint column for a product because of an update operation, a message informing that the reorder level has been attained should be displayed. (Use the AdventureWorks database)
Ans. https://drive.google.com/open?id=0B-yL1mP3zqTbTGZTV3Bsa1VhWTA
4. Create a trigger to ensure that the average of the values in the Rate column of the EmployeePayHistory table should not be more than 20 when the value of the rate is increased. (Use the AdventureWorks database)
Ans. https://drive.google.com/open?id=0B-yL1mP3zqTbTGZTV3Bsa1VhWTA
5. The management of AdventureWorks wants that whenever the account number of a user is modified, the old account number, as well as the new account number, should be instantaneously displayed to the user. How can you accomplish the desired task?
Ans. https://drive.google.com/open?id=0B-yL1mP3zqTbTGZTV3Bsa1VhWTA
6. Create a trigger named Emp_Update on the EmployeeDetail table. This trigger should restrict a user from performing any kind of DML operation on the table before 9 A.M and after 5 P.M. (Use the AdventureWorks database)
Ans. https://drive.google.com/open?id=0B-yL1mP3zqTbTGZTV3Bsa1VhWTA
7.Create a trigger named Emp_Delete_Trg on the EmployeeDetails table. This trigger should restrict the deletion of records from the table if the designation of an employee is Manager. (Use the AdventureWorks database)
Ans. https://drive.google.com/open?id=0B-yL1mP3zqTbTGZTV3Bsa1VhWTA
8. Create a trigger named Emp_Update_Trg that will restrict the updation of salary of an employee if the new salary is less than the previous salary in the EmployeeDetails table. (Use the AdventureWorks database)
Ans. https://drive.google.com/open?id=0B-yL1mP3zqTbTGZTV3Bsa1VhWTA
9. Create a trigger named Emp_Nested_Trg on the EmployeeDetails table for an insert operation. This trigger should ensure that a new record being inserted into the EmployeeDetails table has a matching record in the DeptDetails table. Otherwise, the insert operation is rolled back. (Use the AdventureWorks database)
Ans. https://drive.google.com/open?id=0B-yL1mP3zqTbTGZTV3Bsa1VhWTA
10. Consider the following statement:
CREATE VIEW Emp_Dept
AS
SELECT E.EmployeeID, E.EmpName, E.Designation, E.Salary, D.DeptNo, D.DeptName FROM EmployeeDetails E INNER JOIN DeptDetails D ON E.DeptNo=D.DeptNo A view named Emp_Dept has been created on the base tables, EmployeeDetails and DeptDetails, by using the preceding statement. John is the Database Developer with AdventureWorks Incorporation. He wants that the users should be able to insert data in the underlying base tables by using the view. He can implement the desired functionality if the insert operation affects a single table. However, the same cannot be done if multiple tables are involved. He wants to be able to insert data in both the tables with the help of a single insert statement. Help John in resolving the problem.
Ans. https://drive.google.com/open?id=0B-yL1mP3zqTbTGZTV3Bsa1VhWTA
11. John is a Database Developer. He is not able to delete the unwanted records from the base tables by using the view, Emp_Dept. Help John in resolving the problem.
Ans. https://drive.google.com/open?id=0B-yL1mP3zqTbTGZTV3Bsa1VhWTA
No comments:
Post a Comment