Consider the following employee database

Employee (empname, street, city, date_of_joining)

Works (empname, company_name, salary)

Company (company_name, city)

Manages (empname, manager_name)

Write the sql queries for the following statements:

i. Modify the database so that ‘john’ now lives in ‘mumbai’

ii. Give all the employees of ‘ABC corporation’ a 10% raise

iii. List all the employees who live in same city as their managers

iv. Find all the employees who earn more than average salary of all the employees of their company

Mumbai University > Information Technology > Sem 3 > Database Management System

Marks: 10M

Year: Dec 2013

Here the following queries:

• UPDATE Employee

  SET city=’mumbai’

  Where empname=’john’

• UPDATE Works

  SET salary=salary*1.10

  WHERE company_name=’ABC corporation’

• SELECT empname

  FROM employee_details e1, manages m, employee_details e2

  WHERE e1.empname = m.empname &&

  m.manager_name = e2.empname &&

  e1.city = e2.city

  (Note: This query works on Join concept. First Join the employee_details table with manages table where empname is manager_name and then Join again with employee_details table to get manager’s detail. Finally the condition, employee_details city are name)

• SELECT empname FROM employee_details

  WHERE salary >(SELECT avg(salary ) FROM employee_details)

  (Note: This is a sub-query. First we get the average salary and then use result as subquery to outer query on condition salary>average_salary)