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 -

Marks: 10 M

Year: Dec 2013

Here the following queries:

i) UPDATE Employee

SET city=’Mumbai’

Where empname=’john’

ii) UPDATE Works

SET salary=salary*1.10

WHERE company_name=’ABC corporation’

iii) 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)

iv) 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)