Write the sql queries

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

1 Answer

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)

Please log in to add an answer.