cheat sheet
RESIDES (PERSON_NAME, STREET, CITY, STATE)WORKS (PERSON_NAME, COMPANY_NAME, SALARY)ADDRESS (COMPANY_NAME, CITY, STATE, ZIP)MANAGES (PERSON_NAME, MANAGER_NAME)
3. Find all employees who live in the same city and street as their manager (3 pts)
SELECT R.PERSON_NAME, M.MANAGER_NAME, R.STREET, R.CITY, R2.STREET, R2.CITY
FROM RESIDES R, MANAGES M, RESIDES R2
WHERE R.PERSON_NAME = M.PERSON_NAME AND M.MANAGER_NAME =
R2.PERSON_NAME AND R.STREET = R2.STREET AND R.CITY = R2.CITY
4. Find all persons who do not work for any company (3 pts)
SELECT R.PERSON_NAME
FROM RESIDES R
EXCEPT
SELECT W.PERSON_NAME
FROM WORKS W
5. Find the number of employees for each of the managers (3 pts)
SELECT M.MANAGER_NAME, COUNT(*) AS ‘# of Employees’
FROM MANAGES M
GROUP BY M.MANAGER_NAME
6. Give all employees of ‘Nutiva’ a salary raise of 10% (2 pts)
UPDATE WORKS W SET SALARY = SALARY * 1.1
WHERE W.COMPANY_NAME = ‘Nutiva’
7. Layoff all employees of ‘LiveChat’ (3 pts)
DELETE FROM MANAGES
WHERE PERSON_NAME IN
(SELECT PERSON_NAME
FROM WORKS
WHERE COMPANY_NAME = 'LiveChat')
DELETE FROM WORKS
WHERE COMPANY_NAME = ‘LiveChat’
8. Give all managers of ‘Zubecon’ a 10 percent raise, unless their salary becomes greater than $120,000. In such cases, give only a 4% raise. (5 pts)
UPDATE MANAGES M, WORKS W SET W.SALARY = W.SALARY*1.04
WHERE M.MANAGER_NAME = W.PERSON_NAME AND W.COMPANY_NAME =
‘Zubecon’
AND W.SALARY*1.1 > 120000;
UPDATE MANAGES M, WORKS W SET W.SALARY = W.SALARY*1.1
WHERE M.MANAGER_NAME = W.PERSON_NAME AND W.COMPANY_NAME =
‘Zubecon’
AND W.SALARY*1.1 <= 120000;
4. Find name, city, state of all persons who do not work for any company (5 pts)
P [R.PERSON_NAME, R.CITY, R.STATE] ( S [N.PERSON_NAME=R.PERSON_NAME] (R[N](P[PERSON_NAME] RESIDES ─ P[PERSON_NAME] WORKS) X R[R] RESIDES) )
The actual specification of attributes for a given relation is driven by an analysis of “Functional Dependencies” amongst attributes of the proposed relation
A “Functional Dependency” is a constraint between two
3. Find all employees who live in the same city and street as their manager (3 pts)
SELECT R.PERSON_NAME, M.MANAGER_NAME, R.STREET, R.CITY, R2.STREET, R2.CITY
FROM RESIDES R, MANAGES M, RESIDES R2
WHERE R.PERSON_NAME = M.PERSON_NAME AND M.MANAGER_NAME =
R2.PERSON_NAME AND R.STREET = R2.STREET AND R.CITY = R2.CITY
4. Find all persons who do not work for any company (3 pts)
SELECT R.PERSON_NAME
FROM RESIDES R
EXCEPT
SELECT W.PERSON_NAME
FROM WORKS W
5. Find the number of employees for each of the managers (3 pts)
SELECT M.MANAGER_NAME, COUNT(*) AS ‘# of Employees’
FROM MANAGES M
GROUP BY M.MANAGER_NAME
6. Give all employees of ‘Nutiva’ a salary raise of 10% (2 pts)
UPDATE WORKS W SET SALARY = SALARY * 1.1
WHERE W.COMPANY_NAME = ‘Nutiva’
7. Layoff all employees of ‘LiveChat’ (3 pts)
DELETE FROM MANAGES
WHERE PERSON_NAME IN
(SELECT PERSON_NAME
FROM WORKS
WHERE COMPANY_NAME = 'LiveChat')
DELETE FROM WORKS
WHERE COMPANY_NAME = ‘LiveChat’
8. Give all managers of ‘Zubecon’ a 10 percent raise, unless their salary becomes greater than $120,000. In such cases, give only a 4% raise. (5 pts)
UPDATE MANAGES M, WORKS W SET W.SALARY = W.SALARY*1.04
WHERE M.MANAGER_NAME = W.PERSON_NAME AND W.COMPANY_NAME =
‘Zubecon’
AND W.SALARY*1.1 > 120000;
UPDATE MANAGES M, WORKS W SET W.SALARY = W.SALARY*1.1
WHERE M.MANAGER_NAME = W.PERSON_NAME AND W.COMPANY_NAME =
‘Zubecon’
AND W.SALARY*1.1 <= 120000;
4. Find name, city, state of all persons who do not work for any company (5 pts)
P [R.PERSON_NAME, R.CITY, R.STATE] ( S [N.PERSON_NAME=R.PERSON_NAME] (R[N](P[PERSON_NAME] RESIDES ─ P[PERSON_NAME] WORKS) X R[R] RESIDES) )
The actual specification of attributes for a given relation is driven by an analysis of “Functional Dependencies” amongst attributes of the proposed relation
A “Functional Dependency” is a constraint between two