Database Laboratory Assignment  2 Solved

Create the company database with following relational schema and instance.  Create relevant primary and foreign keys.  

 

 

 

Write SQL Statements for the following queries. 

 

  

a.       For every project located in ‘Stafford’, list the project name, the controlling department name, and the department manager’s name, address, and birth date. 

b.      If every employee working on the ‘ProductX’ project is given a 10 percent raise, show the resulting salaries along with names of the employees.

c.       Retrieve a list of employees and the projects they are working on, ordered by department and, within each department, ordered alphabetically by last name, then first name. 

d.      Retrieve the names of all employees and their direct managers in department 5 who work more than 10 hours per week on the ProductX project. 

e.      Retrieve the Essns of all employees who work the same (project, hours) combination on some project that employee ‘John Smith’ (whose Ssn =‘123456789’) works on. [Use nested query structure] 

f.        Retrieve the name of each employee who has a dependent with the same first name and is the same sex as the employee. [Use nested query structure]

g.       Find the sum of the salaries of all employees of the ‘Research’ department, as well as the maximum salary, the minimum salary, and the average salary in this department. 

h.      For each department that has more than or equal to 3 employees, retrieve the department number and the number of its employees who are making more than $30,000.

i.        Raise salaries of those employees by 10 percent whose dependent are daughter. 

j.        Transfer those employees to department located in Stafford whose date of birth is in 1965.

k.       List all the employee names who works in all the projects in which Smith works.

l.        Create a view named “Dept_empdetails” consisting deptwise employee count and total salary of employees. Then, insert few more employee records in the EMPLOYEE relation. Now, display the content of the view. 

m.     Add a new attribute called “Commission” in the Employee Table. Update the commission value for each employees. Keep some null values for some employees as they don’t possess any commission. Now display, employee name and their total salary. 

n.      Delete Newbenefits project details from PROJECT relation. This must automatically delete referenced tuples from other relations. 

o.      List the name of employees who work in all the projects located in Houston

p.      Display Age and Salary of all employees in a given deptname. The deptname must be taken as user-input.

q.      Create a view called “Payroll” with SSN, Employee Name, Dno and Salary. Display the content of the view. Now, increase salary of all employees in Dno = 5 through Payroll view. Now, Display the details of all employees from EMPLOYEE relation after the update.  [THE SALARY must be automatically updated in EMPLOYEE relation]