DBMS SQL Module Problems from University Question Papers

DBMS SQL Problems from University Question Papers

Q.4 b) December 2016 10 Marks

Consider the following database schema:

Employee (employee_name, street, city, date_of_join);

Works (employee_name, company_name, salary);

Company (company_name, city);

Manages (employee_name, manager_name);

1) Give all employees of ABC company a 25% rise.

Ans:

SQL> update works set salary=salary*1.25 where company_name= 'ABC'

2) Find all employees who live in the same cities and on the same street as their manager.

Ans:

SQL> SELECT e1.employee_name, e1.street, e1.city

FROM Employee e1, Employee e2, Manages m

WHERE e1.employee_name = m.employee_name

AND e2.employee_name = m.manager_name

AND e1.city = e2.city

AND e1.street = e2.street

3) Find all employees who joined in the month of April.

Ans:

SQL> SELECT employee_name FROM Employee WHERE date_of_join LIKE '%-04-%'

OR

SQL>  SELECT employee_name FROM Employee WHERE date_of_join LIKE '%-APR-%'

4) Delete the employee Smith belonging to XYZ company.

Ans:

SQl> delete from employee where employee_name=(select employee_name from works where employee_name= 'Smith' and company_name= 'XYZ') 

OR

DELETE FROM Employee WHERE employee_name = 'Smith' AND EXISTS (SELECT * FROM Works WHERE employee_name = 'Smith' AND company_name = 'XYZ')