Andmebaaside ajaveeb

Oletame, et andmebaasi kontseptuaalne struktuur on selline:

[Dept]-1---------0..*-[Emp]

Iga osakond (Dept) on seotud null või rohkem töötajaga (Emp).
Iga töötaja (Emp) on seotud täpselt ühe osakonnaga (Dept).

1. Leidke iga osakonna kohta selles müügimehe ametikohal töötavate töötajate arvu. Väljastage osakonna number, nimi ja sellel ametikohal töötavate töötajate arv.

SELECT deptno, dname, Count(*) FILTER (WHERE job='SALESMAN') AS nr_of_salesmen
FROM Dept LEFT JOIN Emp USING (deptno)
GROUP BY deptno;

Tähelepanekuid.

Pean kasutama LEFT JOIN, et tulemuses oleks ka osakonnad, kus pole ühtegi töötajat.
Saan grupeerida ainult osakonna numbri järgi, sest PostgreSQL saab antud juhul aru, et osakonna number identifitseerib üheselt osakonna nime. Seega pole osakonna nime veergu vaja GROUP BY klauslis korrata.
FILTER kasutamine on PostgreSQLi poolne SQLi laiendus ja võimaldab lauseid kompaktsemalt kirja panna. Alternatiiv oleks kasutada CASE avaldist:

SELECT deptno, dname, Count(CASE WHEN job='SALESMAN' THEN 1 ELSE NULL END) AS nr_of_salesmen
FROM Dept LEFT JOIN Emp USING (deptno)
GROUP BY deptno;

2. Leidke iga osakonna kohta müügimehe ametikohal töötajad. Väljastage osakonna number, nimi ning komadega eraldatud nimekiri sellel ametikohal töötavate töötajate nimedest sorteerituna töölevõtmise aja järgi kasvavalt. Väljastage iga osakonna kohta maksimaalselt kaks nime.

WITH hiring AS (SELECT deptno, ename, DENSE_RANK() OVER(PARTITION BY deptno ORDER BY hiredate) AS rank
FROM Emp
WHERE job='SALESMAN'),
selected AS (SELECT deptno, ename, rank
FROM hiring
WHERE rank<=2
ORDER BY rank
FETCH FIRST 2 ROWS ONLY)
SELECT deptno, dname, string_agg(ename, ',' ORDER BY rank) AS persons
FROM Dept LEFT JOIN selected USING (deptno)
GROUP BY deptno;

hiring - ühine tabeli avaldis (common table expression, CTE), millega leian müügimehe ametikohal töötajad ning nende asukoha osakonna töötajate töölevõtmise järjekorra pingereas. Kasutan tiheda pingerea leidmise funktsiooni dense_rank. Tihe pingerida tähendab, et asetustesse ei jää auke.

selected - leian eelnevalt defineeritud ühise tabeli avaldise alusel töötajad, kelle nimed tuleb väljastada.

põhipäring - leian osakonnad ja panen iga osakonna kohta kokku töötajate nimede stringi kasutades kokkuvõttefunktsiooni string_agg.

3. Kui ülesandeks oleks väljastada iga osakonna kohta töötajad, kes on tööle võetud kahel esimesel kuupäeval, siis oleks lahendus selliseid. Pange tähele, et selliseid töötajaid võib olla rohkem kui kaks.

WITH hiring AS (SELECT deptno, ename, DENSE_RANK() OVER(PARTITION BY deptno ORDER BY hiredate) AS rank

FROM Emp

WHERE job='SALESMAN')

SELECT deptno, dname, string_agg(ename, ',' ORDER BY rank) FILTER (WHERE rank<=2) AS persons

FROM Dept LEFT JOIN hiring USING (deptno)

GROUP BY deptno;

Hinda postitust:

Keskmine hinne : Pole veel hinnanguid!