Chapter 6 (Query Questions)

Question 1

Give all employees in the ‘Research’ department a 10% raise in salary.

Answer 1

UPDATE EMPLOYEE
SET SALARY = SALARY *1.1
WHERE DNO IN (SELECT DNUMBER FROM DEPARTMENT WHERE DNAME=’Research’);

Question 2

Change the location and controlling department number of project number 10 to ‘Bellaire’ and 5, respectively.

Answer 2

UPDATE PROJECT
SET PLOCATION = ‘Bellaire’, DNUM = 5
WHERE PNUMBER=10;

Question 3

Show the resulting salaries if every employee working on
the ‘ProductX’ project is given a 10 percent raise.

Answer 3

SELECT E.Fname, E.Lname, 1.1 * E.Salary AS Increased_sal
FROM EMPLOYEE AS E, WORKS_ON AS W, PROJECT AS P
WHERE E.Ssn=W.Essn AND W.Pno=P.Pnumber AND
P.Pname=‘ProductX’;

Question 4

Answer 4

Question 5

Answer 5

Question 6

Answer 6

Question 7

Query 8. For each employee, retrieve the employee’s first and last name and the first and last name of his or her immediate supervisor.

Answer 7

SELECT E.Fname, E.Lname, S.Fname, S.Lname
FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.Super_ssn=S.Ssn;

Question 8

Answer 8

Question 9

Answer 9

Question 10

Answer 10