Výstup SQL příkazů k semestrální práci předmětu AD7B36DBS.

Zpracoval: Michal Rathouzský

3. semestr, zima 2010/2011

1. Z tabulky JEDNOTKY zobraz sloupce JEDNOTKA, PODIL_CITTEL, PODIL_JMENOVATEL pro všechny záznamy.

SQL>
SELECT JEDNOTKA, PODIL_CITATEL, PODIL_JMENOVATEL
FROM JEDNOTKY ORDER BY JEDNOTKA ASC; 		01

2. Z tabulky JEDNOTKY zobraz sloupce ID_TYP_JEDNOTKY, JEDNOTKA, PODIL_CITTEL, PODIL_JMENOVATEL pro záznamy,
kde ID_TYP_JEDNOTKY = 1 (byt) setříděné podle sloupce JEDNOTKA.

SQL>
SELECT ID_TYP_JEDNOTKY, JEDNOTKA, PODIL_CITATEL, PODIL_JMENOVATEL
FROM JEDNOTKY WHERE ID_TYP_JEDNOTKY = 1
ORDER BY JEDNOTKA ASC; 		02

3. Z tabulky JEDNOTKY zobraz sloupce ID_TYP_JEDNOTKY, JEDNOTKA, PODIL_CITTEL, PODIL_JMENOVATEL pro záznamy,
kde ID_TYP_JEDNOTKY nerovná se 1 (byt).

SQL>
SELECT ID_TYP_JEDNOTKY, JEDNOTKA, PODIL_CITATEL, PODIL_JMENOVATEL
FROM JEDNOTKY WHERE ID_TYP_JEDNOTKY != 1; 		03

4. Zobrazí tabulku JEDNOTKY, kam přidá sloupec PODIL_DES. V tomto sloupci bude podíl jednotky vyjádřený jako desetinné číslo.

SQL>
SELECT JEDNOTKY.VCHOD, JEDNOTKY.JEDNOTKA, JEDNOTKY.PODIL_CITATEL, JEDNOTKY.PODIL_JMENOVATEL,
PODIL_CITATEL/PODIL_JMENOVATEL AS PODIL_DES
FROM JEDNOTKY
ORDER BY JEDNOTKA ASC; 		04

5. Vyber všechny jednotky a vlastníky, jejichž jednotka, kterou vlastní, se dělí na díly s podílem 1/4 (parkovací stání s parkliftem pro 4 vozy).
Záznamy omezte pouze na platná vlastnictví.

SQL>
SELECT JEDNOTKY.JEDNOTKA, DIL.CISLO_STANI, OSOBY.OSOBA
FROM OSOBY
INNER JOIN ((JEDNOTKY
INNER JOIN DIL ON
JEDNOTKY.ID_JEDNOTKY = DIL.ID_JEDNOTKY)
INNER JOIN VLASTNICTVI ON
DIL.ID_DIL = VLASTNICTVI.ID_DIL)
ON OSOBY.ID_OSOBY = VLASTNICTVI.ID_OSOBY
WHERE ((VLASTNICTVI.Platnost=1)
AND (DIL.PODIL_CITATEL=1)
AND (DIL.PODIL_JMENOVATEL=4))
ORDER BY JEDNOTKY.JEDNOTKA; 		05

6. spojení - JOIN ON: spojení tabulek JEDNOTKY a TYP_JEDNOTKY_CIS, kde se rovnají záznamy ve sloupcích ID_TYP_JEDNOTKY.

SQL>
SELECT JEDNOTKY.JEDNOTKA, JEDNOTKY.PODIL_CITATEL, JEDNOTKY.PODIL_JMENOVATEL, TYP_JEDNOTKY_CIS.TYP_JEDNOTKY
FROM TYP_JEDNOTKY_CIS
INNER JOIN JEDNOTKY ON
TYP_JEDNOTKY_CIS.ID_TYP_JEDNOTKY = JEDNOTKY.ID_TYP_JEDNOTKY; 		06

7. spojení - NATURAL JOIN: Všem jednotkám je přiřazen typ jednotky z tabulky TYP_JEDNOTKY_CIS, která je používána jako číselník.

SQL>
SELECT JEDNOTKY.JEDNOTKA, JEDNOTKY.PODIL_CITATEL, JEDNOTKY.PODIL_JMENOVATEL, TYP_JEDNOTKY_CIS.TYP_JEDNOTKY
FROM TYP_JEDNOTKY_CIS
NATURAL JOIN JEDNOTKY; 		07

8. Kombinace všech jednotek a osob.

SQL>
SELECT JEDNOTKY.JEDNOTKA, OSOBY.OSOBA
FROM JEDNOTKY
CROSS JOIN OSOBY
WHERE JEDNOTKY.JEDNOTKA < 5; 		08

9. Vybere všechna vlastnictví, která nevlastní žádná osoba - takové záznamy v databázi nejsou (nemají smysl).

SQL>
SELECT OSOBY.ID_OSOBY, VLASTNICTVI.ID_VLASTNICTVI, VLASTNICTVI.ID_DIL
FROM OSOBY
RIGHT JOIN VLASTNICTVI ON OSOBY.ID_OSOBY = VLASTNICTVI.ID_OSOBY
WHERE OSOBY.ID_OSOBY Is Null; 		09

10. Vybere všechny osoby, které nemají žádné vlastnictví - takové záznamy v databázi teké nejsou (opět nemají smysl).

SQL>
SELECT OSOBY.ID_OSOBY, VLASTNICTVI.ID_VLASTNICTVI, VLASTNICTVI.ID_DIL
FROM OSOBY LEFT
JOIN VLASTNICTVI ON OSOBY.ID_OSOBY = VLASTNICTVI.ID_OSOBY
WHERE VLASTNICTVI.ID_VLASTNICTVI Is Null; 		10

11. Vybere všechny osoby, které mají nebo nemají nějaké vlastnictví a vlastnictví, která mají nebo nemají vlastníky.
Vzhledem k výsledkům dotazů 9. a 10., tak tento dotaz nedá jiné výsledky než přirozené spojení.

SQL>
SELECT OSOBY.ID_OSOBY,OSOBY.OSOBA, VLASTNICTVI.ID_VLASTNICTVI, VLASTNICTVI.ID_DIL
FROM OSOBY
FULL JOIN VLASTNICTVI ON OSOBY.ID_OSOBY = VLASTNICTVI.ID_OSOBY; 		11

12. Ke každé jednotce zobrazí aktuálního vlastníka.

SQL>
SELECT JEDNOTKY.VCHOD, JEDNOTKY.JEDNOTKA, DIL.CISLO_STANI,
VLASTNICTVI.DATUM_PREVZETI, VLASTNICTVI.DATUM_PREDANI,
VLASTNICTVI.PLATNOST, OSOBY.OSOBA
FROM ((JEDNOTKY INNER JOIN DIL ON JEDNOTKY.ID_JEDNOTKY = DIL.ID_JEDNOTKY)
INNER JOIN VLASTNICTVI ON DIL.ID_DIL = VLASTNICTVI.ID_DIL )
INNER JOIN OSOBY ON VLASTNICTVI.ID_OSOBY = OSOBY.ID_OSOBY
WHERE VLASTNICTVI.PLATNOST = 1
ORDER BY JEDNOTKY.JEDNOTKA, DIL.CISLO_STANI;
12

13. Vybere jednotku s největším podílem na budově. Podíl je nutno převést na desetinné číslo a vybrat to největší.

SQL>
SELECT DISTINCT VCHOD, JEDNOTKA, PODIL_CITATEL, PODIL_JMENOVATEL
FROM JEDNOTKY
WHERE
PODIL_CITATEL/PODIL_JMENOVATEL
IN (SELECT Max(PODIL_CITATEL/PODIL_JMENOVATEL)
FROM
JEDNOTKY); 		13

14. Vybere jednotky, jejichž ID je menší než 15 a k nim přidá jednotky, jejichž číslo jednotky je vyšší než 100.

SQL>
SELECT ID_JEDNOTKY, JEDNOTKA
FROM JEDNOTKY
WHERE ID_JEDNOTKY < 15
UNION
SELECT ID_JEDNOTKY, JEDNOTKA
FROM JEDNOTKY
WHERE JEDNOTKA > 100; 		14

15. Od jednotek jejichž ID je menší než 150 odečte všechny jednotky jejichž ID je větší než 100.

SQL>
SELECT ID_JEDNOTKY, JEDNOTKA
FROM JEDNOTKY
WHERE ID_JEDNOTKY < 150
MINUS
SELECT ID_JEDNOTKY, JEDNOTKA
FROM JEDNOTKY
WHERE ID_JEDNOTKY > 100; 		15

16. Vybere jednotky, které jsou v množině jednotek, jejichž ID je menší než 150 a zároveň jsou v množině jednotek,
jejichž ID je větší než 100.

SQL>
SELECT ID_JEDNOTKY, JEDNOTKA
FROM JEDNOTKY
WHERE ID_JEDNOTKY < 150
INTERSECT
SELECT ID_JEDNOTKY, JEDNOTKA
FROM JEDNOTKY
WHERE ID_JEDNOTKY > 100; 		16

17. Vybere jednotky, jejichž podíl na budově je menší než průměrný podíl všech jednotek.
Podíl je nutno převést na desetinné číslo.

SQL>
SELECT DISTINCT VCHOD, JEDNOTKA, PODIL_CITATEL, PODIL_JMENOVATEL
FROM JEDNOTKY
WHERE PODIL_CITATEL/PODIL_JMENOVATEL < (
SELECT AVG(PODIL_CITATEL/PODIL_JMENOVATEL)
FROM JEDNOTKY); 		17

18. Jaký je počet jednotek?

SQL>
SELECT Count(ID_JEDNOTKY) AS POCET_JEDNOTEK
FROM JEDNOTKY; 		18

19. Počet velikostí jednotek (různých podílů).

SQL>
CREATE VIEW SEZNAM_PODILU AS
SELECT PODIL_CITATEL, PODIL_JMENOVATEL
FROM JEDNOTKY
GROUP BY PODIL_CITATEL, PODIL_JMENOVATEL;

SELECT Count(PODIL_CITATEL)
FROM SEZNAM_PODILU; 		19a 19b

20. Stejné jako předchozí.

SQL>
SELECT Count (*)
FROM (
SELECT PODIL_CITATEL, PODIL_JMENOVATEL
FROM JEDNOTKY
GROUP BY PODIL_CITATEL, PODIL_JMENOVATEL)
POCET_PODILU; 		20

21. Vyber všechny osoby, které vlastní všechny typy jednotek.
SEZNAM OSOB,KTERÉ MAJÍ VŠECHNO

SQL>
SELECT DISTINCT OSOBA
FROM(
-- SEZNAM VŠECH OSOB
(SELECT DISTINCT OSOBA
FROM OSOBY)
MINUS
-- SEZNAM OBOB, KTERÉ NĚCO NEMAJÍ
(SELECT DISTINCT OSOBA
FROM(
-- SEZNAM CO KDO NEMÁ
SELECT OSOBA, TYP_JEDNOTKY
FROM
-- OD MOŽNÉHO ODEČTE CO JE A DOSTANE CO NENÍ
(SELECT DISTINCT OSOBA, TYP_JEDNOTKY_CIS.TYP_JEDNOTKY
FROM
-- KARTÉZSKÝ SOUČIN, TJ. CO JE MOŽNÉ
(SELECT DISTINCT OSOBY.OSOBA, TYP_JEDNOTKY_CIS.TYP_JEDNOTKY
FROM TYP_JEDNOTKY_CIS
INNER JOIN (JEDNOTKY
INNER JOIN (DIL
INNER JOIN (OSOBY
INNER JOIN VLASTNICTVI
ON OSOBY.ID_OSOBY = VLASTNICTVI.ID_OSOBY)
ON DIL.ID_DIL = VLASTNICTVI.ID_DIL)
ON JEDNOTKY.ID_JEDNOTKY = DIL.ID_JEDNOTKY)
ON TYP_JEDNOTKY_CIS.ID_TYP_JEDNOTKY = JEDNOTKY.ID_TYP_JEDNOTKY),
TYP_JEDNOTKY_CIS
ORDER BY OSOBA)
MINUS
-- ODEČTE CO JE V TABULCE (SKUTEČNOST)
(SELECT DISTINCT OSOBY.OSOBA, TYP_JEDNOTKY_CIS.TYP_JEDNOTKY
FROM TYP_JEDNOTKY_CIS
INNER JOIN (JEDNOTKY
INNER JOIN (DIL
INNER JOIN (OSOBY
INNER JOIN VLASTNICTVI
ON OSOBY.ID_OSOBY = VLASTNICTVI.ID_OSOBY)
ON DIL.ID_DIL = VLASTNICTVI.ID_DIL)
ON JEDNOTKY.ID_JEDNOTKY = DIL.ID_JEDNOTKY)
ON TYP_JEDNOTKY_CIS.ID_TYP_JEDNOTKY = JEDNOTKY.ID_TYP_JEDNOTKY)))); 		21

22. Vyber všechny osoby, které vlastní všechny typy jednotek.
Použito zda vlastní jednotky tolika typů jednotek, kolik jich je v číselníku typu jednotky.
ZJISTÍ OBOBY, KTERÉ VLASTNÍ VŠECHNY TYPY JEDNOTEK, JINÝ ZPŮSOB.

SQL>
SELECT OSOBA, POCET
FROM (
-- Spojeni TYP_JEDNOTKY - JEDNOTKA - DIL - VLASTNICTVI - OSOBA:
SELECT OSOBA, COUNT(OSOBA) AS POCET
FROM (
SELECT DISTINCT OSOBY.OSOBA, TYP_JEDNOTKY_CIS.TYP_JEDNOTKY
FROM TYP_JEDNOTKY_CIS
INNER JOIN (JEDNOTKY
INNER JOIN (DIL
INNER JOIN (OSOBY
INNER JOIN VLASTNICTVI
ON OSOBY.ID_OSOBY = VLASTNICTVI.ID_OSOBY)
ON DIL.ID_DIL = VLASTNICTVI.ID_DIL)
ON JEDNOTKY.ID_JEDNOTKY = DIL.ID_JEDNOTKY)
ON TYP_JEDNOTKY_CIS.ID_TYP_JEDNOTKY = JEDNOTKY.ID_TYP_JEDNOTKY)
GROUP BY OSOBA)
-- POROVNÁ POČET S POČTEM ZÁZNAMŮ V TYP_JEDNOTKY_CIS
WHERE POCET = (
-- ZJISTÍ POČET ZÁZNAMŮ V TYP_JEDNOTKY_CIS
SELECT COUNT (TYP_JEDNOTKY_CIS.ID_TYP_JEDNOTKY)
FROM TYP_JEDNOTKY_CIS); 		22

23. Vyber OSOBY, které nevlastní žádný sklep.

SQL>
SELECT DISTINCT OSOBY.OSOBA
FROM TYP_JEDNOTKY_CIS
INNER JOIN (JEDNOTKY
INNER JOIN (DIL
INNER JOIN (OSOBY
INNER JOIN VLASTNICTVI
ON OSOBY.ID_OSOBY = VLASTNICTVI.ID_OSOBY)
ON DIL.ID_DIL = VLASTNICTVI.ID_DIL)
ON JEDNOTKY.ID_JEDNOTKY = DIL.ID_JEDNOTKY)
ON TYP_JEDNOTKY_CIS.ID_TYP_JEDNOTKY = JEDNOTKY.ID_TYP_JEDNOTKY
WHERE TYP_JEDNOTKY <> 'sklep'; 		23

24. O kolik se liší počet osob, které jsou SJM a které nejsou SJM.
ABSOLUTNÍ ROZDÍL MEZI SJM A NESJM.

SQL>
SELECT DISTINCT
-- ABSOLUTNÍ HODNOTA
ABS((
-- ROZDÍL SJM MÍNUS NESJM
SELECT COUNT(SJM) AS POCET_SJM
FROM(
-- OSOBY, KTERÉ JSOU SJM
SELECT OSOBA AS SJM
FROM OSOBY
WHERE OSOBA LIKE 'SJM%'))

-- POČET NESJM
(SELECT COUNT(NESJM) AS POCET_NESJM
FROM(
-- OSOBY, KTERÉ NEJSOU SJM
SELECT OSOBA AS NESJM
FROM OSOBY
WHERE OSOBA NOT LIKE 'SJM%')))
AS ROZDIL
FROM OSOBY; 		24

25. Seznam osob, které nemají žádné platné vlastnictví.

SQL>
-- SEZNAM VŠECH OSOB
SELECT *
FROM OSOBY
WHERE NOT EXISTS (
-- SEZNAM OBOS, KTERÉ NEMAJÍ PLATNÉ VLASTNICTVÍ
SELECT *
FROM VLASTNICTVI
WHERE OSOBY.ID_OSOBY = VLASTNICTVI.ID_OSOBY AND VLASTNICTVI.PLATNOST = 1);
25

26. Seznam osob, které nemají žádné platné vlastnictví.

SQL>
SELECT OSOBA
FROM(
SELECT DISTINCT OSOBA
FROM OSOBY
NATURAL JOIN VLASTNICTVI
WHERE VLASTNICTVI.PLATNOST = 0)
MINUS(
SELECT DISTINCT OSOBA
FROM OSOBY
NATURAL JOIN VLASTNICTVI
WHERE VLASTNICTVI.PLATNOST = 1); 		26

27. Seznam osob, které nemají žádné platné vlastnictví.

SQL>
SELECT OSOBA
FROM(
SELECT DISTINCT OSOBA
FROM OSOBY)
MINUS(
SELECT DISTINCT OSOBA
FROM OSOBY
NATURAL JOIN VLASTNICTVI
WHERE VLASTNICTVI.PLATNOST = 1); 		27

28. Vytvořit pohled platných vlastnictví.

SQL>
CREATE VIEW PLATNA_VLASTNICTVI AS
SELECT *
FROM VLASTNICTVI
WHERE VLASTNICTVI.PLATNOST = 1; 		28

29. Které OSOBY vlastní které jednotky - jen platná vlastnictví.

SQL>
SELECT JEDNOTKY.JEDNOTKA, DIL.CISLO_STANI, OSOBY.OSOBA
FROM(
JEDNOTKY
INNER JOIN (DIL
INNER JOIN (PLATNA_VLASTNICTVI
INNER JOIN OSOBY
ON PLATNA_VLASTNICTVI.ID_OSOBY = OSOBY.ID_OSOBY)
ON DIL.ID_DIL = PLATNA_VLASTNICTVI.ID_DIL)
ON JEDNOTKY.ID_JEDNOTKY = DIL.ID_JEDNOTKY)
ORDER BY OSOBY.OSOBA, JEDNOTKY.JEDNOTKA; 		29

30. Vytvoří tabulku platných vlastnictví. Do ní zkopíruje záznamy platných vlastnictví a do sloupce SUMA vloží nuly.

SQL>
DROP TABLE VLASTNICTVI_PLATNA_TAB;

Create table VLASTNICTVI_PLATNA_TAB (
ID_DIL Integer,
ID_TYP Integer,
ID_OSOBY Integer,
DATUM_PREVZETI Date,
DATUM_PREDANI Date,
PLATNOST Integer,
SUMA Integer);

INSERT INTO VLASTNICTVI_PLATNA_TAB
(ID_OSOBY, ID_TYP, ID_DIL, DATUM_PREVZETI, DATUM_PREDANI, PLATNOST, SUMA)
SELECT VLASTNICTVI.ID_OSOBY,
VLASTNICTVI.ID_TYP,
VLASTNICTVI.ID_DIL,
VLASTNICTVI.DATUM_PREVZETI,
VLASTNICTVI.DATUM_PREDANI,
VLASTNICTVI.PLATNOST,
0 AS SUMA
FROM VLASTNICTVI
WHERE PLATNOST = 1; 		30

31. Do sloupce SUMA vloží součet hodnot soupce ID_TYP.

SQL>
UPDATE VLASTNICTVI_PLATNA_TAB
SET VLASTNICTVI_PLATNA_TAB.ID_TYP = (
SELECT SUM(VLASTNICTVI_PLATNA_TAB.ID_TYP) AS SUMA
FROM VLASTNICTVI_PLATNA_TAB); 		31

32. Z tabulky .VLASTNICTVI_PLATNA_TAB odstraní záznamy o vlastnictví osoby, které jsou SJM.

SQL>
DELETE
FROM VLASTNICTVI_PLATNA_TAB
WHERE VLASTNICTVI_PLATNA_TAB.ID_OSOBY IN(
SELECT ID_OSOBY
FROM OSOBY
WHERE OSOBA LIKE 'SJM%');
32