itka204_demo4

ITKA204
Demo 4:

1.
SELECT pname
FROM project
WHERE
budget >= 14000 AND
budget <= 50000 AND
city <> 'Jyväskylä' OR
projmgr = '';

2.
SELECT depname
FROM department d, project p
WHERE
d.manager LIKE p.projmgr;

3.
SELECT DISTINCT fname, lname
FROM employee
WHERE empno IN (
SELECT empno
FROM works_on
WHERE hours > 20 AND pno IN (
SELECT pno
FROM project
WHERE city LIKE 'Jyväskylä'));

4.
SELECT pname
FROM project p
WHERE p.pno IN
(SELECT pno
FROM works_on w
WHERE EXISTS
(SELECT *
FROM employee e
WHERE w.empno = e.empno AND e.depno <> p.depno ));

5.
SELECT DISTINCT lname, fname
FROM employee
WHERE lname >= 'h' AND lname < 'i' AND sex LIKE 'female'
ORDER BY lname;

6.
SELECT DISTINCT lname, fname
FROM employee
WHERE empno IN
(SELECT empno
FROM salaries
GROUP BY empno, YEAR
HAVING YEAR = 2000 AND SUM(salary) > 25000);

7.
SELECT lname
FROM employee
WHERE empno IN
(SELECT empno
FROM salaries
GROUP BY empno, YEAR
HAVING YEAR = 2000 AND MIN(salary) <> MAX(salary));

8.
SELECT pname
FROM project
WHERE pno IN
(SELECT pno
FROM works_on
GROUP BY pno
HAVING COUNT(*) = 5);

9.
SELECT depname
FROM department d
WHERE EXISTS
(SELECT *
FROM employee e
WHERE d.depno = e.depno AND
empno NOT IN
(SELECT empno
FROM works_on));

10.
SELECT depname
FROM department d
WHERE city LIKE 'Helsinki' AND EXISTS
(SELECT *
FROM employee e
WHERE d.depno = e.depno AND
empno IN
(SELECT empno
FROM works_on
WHERE pno IN
(SELECT pno
FROM project
WHERE city LIKE 'Oulu' OR city LIKE 'Vaasa')));