Question

Consider the schemas of the following relational database for a company. The company has different departments at different cities in different states:

employee(employee-id, dept-id, name, street-num, street-name, city, state, zip, salary)

department(dept-id, dept-name, city, state)

manager(manager-id, employee-id)

NOTES:

manager-id in the manager relation is a foreign key to the employee relation.

employee-id in the manager relation is a foreign key to the employee relation.

dept-id in the employee relation is a foreign key to the department relation.

An employee belongs to a single manager only. That is, an employee only works for one manager. Some employees do not have a manager (in that case, they do not have an entry in the manager relation).

Write SQL statement (no computer work is needed) for each of the following queries.

1. Find all managers (manager-ids and names) who’s department located in California (state = ‘CA’).

1. Find all employees (IDs and names) in the database who do not work for the Research Department.

1. Anita Jones got married and she wanted to change her name to Anita Smith. Modify the database so that Anita Jones becomes Anita Smith (her employee-id is 987654321).

1. Delete all tuples in the manager relation for manager-id = 123456789.

Answer 1

Ans 1).

Select MANAGER.manager-id, EMPLOYEE.name from MANAGER NATURAL JOIN EMPLOYEE NATURAL JOIN DEPARTMENT WHERE DEPARTMENT.state="CA";

In order to fetch the manager ID and name of manager who is also the employee of the company then the interrelation of three table has been needed as MANAGER table is associated with EMPLOYEE table with the employee ID field and EMPLOYEE table in associated with DEPARTMENT table with department ID field.

Along with that, condition is applied in WHERE clause in which DEPARTMENT.STATE is CA(California).

Ans 2).

SELECT employee.employee-id, employee.name from MANAGER NATURAL JOIN EMPLOYEE NATURAL JOIN DEPARTMENT WHERE DEPARTMENT.dept-name!="Research";

In order to find the employee ID and employee name then the interrelation of three table has been needed as MANAGER table is associated with EMPLOYEE table with the employee ID field and EMPLOYEE table in associated with DEPARTMENT table with department ID field.

Along with that, condition is applied in WHERE clause in which DEPARTMENT.dept-name is not equals to "Research";

Ans 3).

UPDATE EMPLOYEE SET name="Anita Smith" where employee-id=987654321;

In order to update the name of Anita Jones to Anita Smith then UPDATE keyword is used and the command has been written as above in this update keyword is used along with the table name and then the SET KEYWORD is used.

The updation column is written along with the condition in which the updation has to be needed.

Ans 4).

DELETE FROM MANAGER where manager-id=123456789;

IN ORDER TO DELETE ALL THE TUPLES FROM THE MANAGER TABLE THEN DELETE KEYWORD IS USED.

DELETE keyword is used in order to delete the row of a table whether DROP table is used to delete the entire table and DROP word in ALTER command is used in order to modify the constraint for the the column of a particular table or its datatype.

So in order to delete multiple of manager table where manager ID is 123456789 the above command has to be implemented.