CREATE TABLE kniha

(id INTEGER,

nazev VARCHAR(50),

PRIMARY KEY (id));


CREATE TABLE autori

(id INTEGER,

jmeno VARCHAR(10),

prijmeni VARCHAR(20),

titul VARCHAR(7),

prostredni VARCHAR(10),

PRIMARY KEY (id));


CREATE TABLE napsane_knihy

(kniha_id INTEGER,

autor_id INTEGER,

PRIMARY KEY (kniha_id, autor_id),

FOREIGN KEY (kniha_id) REFERENCES kniha (id),

FOREIGN KEY (autor_id) REFERENCES autori (id));



CREATE TABLE nakladatelstvi

(id INTEGER,

nazev VARCHAR(20),

adr_ulice VARCHAR(20),

adr_cislo INTEGER,

adr_mesto VARCHAR(15),

adr_psc NUMERIC(5,0),

telefon VARCHAR(15),

email VARCHAR(40),

web_stranka VARCHAR(60),

PRIMARY KEY (id));



CREATE TABLE vytisk

(id INTEGER,

kniha_id INTEGER,

nakladatel_id INTEGER,

vazba VARCHAR(10) DEFAULT 'pevná',

cena NUMERIC(8,2) DEFAULT 0,

pocet_stran INTEGER,

hmotnost NUMERIC(6,1),

vydani INTEGER,

rok_vydani INTEGER,

PRIMARY KEY (id),

FOREIGN KEY (kniha_id) REFERENCES kniha (id),

FOREIGN KEY (nakladatel_id) REFERENCES nakladatelstvi (id));





1

Názvy všech nakladatelství, která jsou z Brna:

SELECT nazev

FROM nakladatelstvi

WHERE adr_mesto = ‘Brno’


2

Název a e-mail všech nakladatelství, která mají svou webovskou stránku:

SELECT nazev, email

FROM nakladatelstvi

WHERE web_stranka IS NOT NULL


3

Příjmení všech autorů, jejichž křestní jména začínají na písmeno 'T', a kteří nemají žádný titul:

SELECT prijmeni

FROM autori

WHERE jmeno LIKE 'T%'

AND titul IS NULL


4

Seznam jmen všech brněnských nakladatelství s jejich kompletními adresami, tříděno dle názvů ulic
(pakliže sídlí ve stejné ulici, dle jejich čísla):

SELECT nazev, adr_ulice, adr_cislo, adr_mesto, adr_psc

FROM nakladatelstvi

WHERE adr_mesto = ‘Brno’

ORDER BY adr_ulice, adr_cislo


5

Názvy všech knih, jejichž výtisky byly vydány v roce 1998:

SELECT nazev

FROM kniha, vytisk

WHERE kniha.id = vytisk.kniha_id

AND rok_vydani = 1998


6

Kteří autoři napsali knihu "Jak publikovat články"?

SELECT prijmeni, jmeno

FROM autori, kniha, napsane_knihy

WHERE kniha.id = napsane_knihy.kniha_id

AND napsane_knihy.autor_id = autori.id

AND kniha.nazev LIKE 'Jak publikovat články'


7

Názvy všech knih, které napsal Alois Jirásek:

SELECT nazev

FROM kniha, autori, napsane_knihy

WHERE kniha.id = napsane_knihy.kniha_id

AND autori.id = napsane_knihy.autor_id

AND jmeno = 'Alois'

AND prijmeni = 'Jirásek'



8

Názvy všech děl, která vydalo nakladatelství 'Iota':

SELECT kniha.nazev

FROM kniha, vytisk, nakladatelstvi

WHERE kniha.id = vytisk.kniha_id

AND vytisk.nakladatel_id = nakladatelstvi.id

AND nakladatelstvi.nazev = 'Iota'


9

Ve kterých nakladatelstvích byla v roce 1996 vydána díla autora Robina Cooka?

SELECT nakladatelstvi.nazev

FROM nakladatelstvi, vytisk, kniha, napsane_knihy, autori

WHERE nakladatelstvi.id = vytisk.nakladatel_id

AND vytisk.kniha_id = kniha.id

AND kniha.id = napsane_knihy.kniha_id

AND napsane_knihy.autor_id = autori.id

AND vytisk.rok_vydani = 1996

AND autori.jmeno = 'Robin'

AND autori.prijmeni = 'Cook'


10

Vypište seznam měst, ve kterých sídlí naše nakladatelství, u každého města uveďte, kolik z
nakladatelstvích má kontaktní e-mail nebo webovskou stránku:

SELECT adr_mesto, COUNT(*)

FROM nakladatelstvi

WHERE web_stranka IS NOT NULL

OR email IS NOT NULL

GROUP BY adr_mesto


11

Seznam všech názvů knih a nakladatelství a pro každou dvojici kniha - nakladatelství součet cen
výtisků daného díla v daném nakladatelství, napsali bychom následující příkaz:

SELECT kniha.nazev, nakladatelstvi.nazev, SUM(cena)

FROM kniha, vytisk, nakladatelstvi

WHERE kniha.id = vytisk.kniha_id

AND vytisk.nakladatel_id = nakladatelstvi.id

GROUP BY kniha.nazev, nakladatelstvi.nazev


12

Pouze brněnská nakladatelství a pro všechny knihy, které byly těmito nakladatelstvími vydány,

zjistěte průměrnou hmotnost:

SELECT kniha.nazev, nakladatelstvi.nazev, AVG(hmotnost)

FROM kniha, vytisk, nakladatelstvi

WHERE nakladatelstvi.id = vytisk.nakladatel_id

AND vytisk.kniha_id = kniha.id

AND adr_mesto = ‘Brno’

GROUP BY kniha.nazev, nakladatelstvi.nazev






13

Seznam všech děl, které napsal Alois Jirásek nebo Vítězslav Nezval.

SELECT nazev

FROM kniha, napsane_knihy, autori

WHERE kniha.id = napsane_knihy.kniha_id

AND napsane_knihy.autor_id = autori.id

AND (

(jmeno = 'Alois' AND prijmeni = 'Jirásek')

OR

(jmeno = 'Vítězslav' AND prijmeni = 'Nezval')

)


13b

Pomocí sjednocení předchozí dotaz lze přepsat následovně (nedělali jsme ve cvičeních, ale můžeme si
taky vyzkoušet…)

SELECT nazev

FROM kniha, napsane_knihy, autori

WHERE kniha.id = napsane_knihy.kniha_id

AND napsane_knihy.autor_id = autori.id

AND jmeno = 'Alois' AND prijmeni = 'Jirásek'

UNION

SELECT nazev

FROM kniha, napsane_knihy, autori

WHERE kniha.id = napsane_knihy.kniha_id

AND napsane_knihy.autor_id = autori.id

AND jmeno = 'Vítězslav' AND prijmeni = 'Nezval'


14

Zjistěte název a cenu nejlevnější knihy.

SELECT nazev, cena

FROM kniha, vytisk

WHERE kniha.id = vytisk.kniha_id

AND cena = (SELECT MIN(cena) FROM vytisk)


15

Vypište jména všech autorů, kteří se podíleli při psaní nejdelší knihy (=s největším počtem stran)

SELECT jmeno, prijmeni

FROM autori, napsane_knihy, vytisk

WHERE autori.id = napsane_knihy.autor_id

AND napsane_knihy.kniha_id = vytisk.kniha_id

AND pocet_stran = (SELECT MAX(pocet_stran) FROM vytisk)