CMT221 2018
CMT221 2018
CMT221 2018
3. Answer the questions based on the following tables. Provide the SQL statements for
the following questions:
(a)
i. Add a new column MANAGER_ID to table EMPLOYEE. Set the data
type for MANAGER_ID to integer. Define MANAGER_ID as a foreign
key referencing EMP_ID.
ALTER EMPLOYEE
ADD MANAGER_ID INTEGER,
FOREIGN KEY (MANAGER_ID) REFERENCES
EMPLOYEE ( EMP_ID);
ii. Update MANAGER_ID column in EMPLOYEE table following the
conditions below.
UPDATE EMPLOYEE
SET MANAGER_ID = ‘100’
WHERE EMP_ID = ‘101 AND EMP_ID = ‘102’;
UPDATE EMPLOYEE
SET MANAGER_ID = ‘103’
WHERE EMP_ID = ‘104’ AND EMP_ID = ‘105’ AND
EMP_ID = ‘106’;
iii. Find the IDs and salary of all employees who do not work for company
with the ID 900. Sort by employee salary in descending order.
SELECT EMP_ID, SALARY
FROM WORK
WHERE COM_ID = ‘901’AND COM_ID = ‘902’ AND
COM_ID = ‘903’
ORDER BY SALARY DESC;
iv. Find the names, streets, and cities of residence for all employees who work
for 'Second Bank Corporation' and earn more than $20,000.00.
SELECT EMP_NAME, EMP_STREET, EMP_CITY
FROM EMPLOYEE
WHERE EMP_ID IN (SELECT EMP_ID FROM WORK
WHERE COM_ID = ‘900’ AND SALARY>’20000.00’);
v. List the company ID and the number of employees with total salary of
employees above or equal to $50,000.00 for each company.
SELECT COMP_ID FROM WORK WHERE