Solutions for Exercise no. 6

1. Retrival Facilities

SELECT fname, bdate
FROM employees
WHERE '1950-01-01' <= bdate AND bdate <= '1959-12-31';

SELECT 'DROP ' || object_type || ' ' || object_name || ';' AS Command
FROM user_objects;

CREATE TABLE reservation (no integer PRIMARY KEY,
                                               available CHAR (1) NOT NULL);

SELECT  r1.no AS FIRST_FREE, r2.no AS LAST_FREE
FROM     reservation r1, reservation r2
WHERE   r1.available = 'Y' AND r2.available = 'Y' AND r1.no < r2.no AND
                FLOOR (r1.no/20) = FLOOR (r2.no/20) AND
                NOT EXISTS (SELECT * FROM reservation r3
                                       WHERE r1.no < r3.no AND r3.no < r2.no AND r3.available = 'N') AND
                EXISTS (SELECT *
                              FROM reservation r4, reservation r5
                              WHERE ((r4.no = r1.no - 1 AND r4.available = 'N') OR MOD (r1.no, 20) = 0) AND
                                            ((r5.no = r2.no + 1 AND r5.available = 'N') OR MOD (r2.no, 20) = 19))
ORDER BY FIRST_FREE;

2. Aggregation

SELECT dno, COUNT(*)
FROM employees
GROUP BY dno
HAVING dno IN (SELECT dnum
                            FROM projects
                            GROUP BY dnum
                            HAVING COUNT (*) >= ALL (SELECT COUNT (*)
                                                                             FROM projects
                                                                             GROUP BY dnum));

SELECT d.dname, COUNT(*)
FROM employees e, departments d
WHERE d.dnumber = e.dno AND e.sex = 'M' AND
              e.dno IN (SELECT e2.dno
                             FROM employees e2
                             GROUP BY e2.dno
                             HAVING AVG (e2.salary) > 27000)
GROUP BY d.dname;

SELECT cpr, dno, att
FROM employees, (SELECT dno dno1, MAX (salary) att
                              FROM employees
                              GROUP BY dno)
WHERE dno = dno1;