/* ********* 1.1 ********/
SELECT




/* ******** 1.2 ********/
SELECT g.nazwisko
FROM goscie AS g, rezerwacje AS r, pokoje AS p
WHERE g.id_goscia = r.id_goscia
AND r.nr_pokoju = p.nr_pokoju
AND NOT (
(
r.od < '2007-01-01'
AND r.do < '2007-01-01'
)
OR (
r.od > '2007-01-29'
AND r.do > '2007-01-29'
)
)
AND p.ilosc_miejsc ='1'
GROUP BY g.nazwisko

/* ******** 1.3 *********/
SELECT count( * ) AS rezerwacji, p.kategoria
FROM pokoje AS p, rezerwacje AS r
WHERE p.nr_pokoju = r.nr_pokoju
AND NOT (
(
r.od < '2008-03-01'
AND r.do < '2008-03-01'
)
OR (
r.od > '2008-06-30'
AND r.do > '2008-06-30'
)
)
GROUP BY p.kategoria

/* ******** 1.4 ********/
INSERT INTO rezerwacje as r (r.id_goscia, r.nr_pokoju, r.od, r.do, zrealizowana) VALUES (
'?ID_KLIENTA?',
(SELECT p.nr_pokoju FROM pokoje as p WHERE p.ilosc_miejsc='?CHCIANA_ILOSC?' AND p.kategoria='?CHCIANA KATEGORIA?' LIMIT 1),
'?CHCIANY CZAS REZERWACJI OD?',
'?CHCIANY CZAS REZERWACJI DO?',
'nie')

/* ******* 1.5 ********/
SELECT count( * )
FROM rezerwacje AS r
WHERE NOT (
(
r.od < '2006-01-01'
AND r.do < '2006-01-01'
)
OR (
r.od > '2007-12-31'
AND r.do > '2007-12-31'
)
)
AND r.id_goscia ='?ID KLIENTA?'

/* ******** 1.6 *********/
SELECT g . *
FROM goscie AS g, rezerwacje AS r
WHERE g.id_goscia = r.id_goscia
AND r.od = DATE( now( ) )
AND r.zrealizowana = 'tak'

/* ******** 1.7 *********/
SELECT sum( p.cena * ( TO_DAYS( r.do ) - TO_DAYS( r.od ) ) ) , p.kategoria
FROM rezerwacje AS r, pokoje AS p
WHERE r.nr_pokoju = p.nr_pokoju
AND NOT (
(
r.od < '2008-08-01'
AND r.do < '2008-08-01'
)
OR (
r.od > '2008-08-31'
AND r.do > '2008-08-31'
)
)
GROUP BY p.kategoria

/* ******* 1.8 *******/
SELECT *
FROM pokoje
ORDER BY kategoria, stan

/* ******** 1.9 ********/
UPDATE pokoje SET stan = 'wolny' WHERE nr_pokoju = '?PODANY NR POKOJU POKOJU?'

/* ******* 2.a ********/
SELECT *
FROM prac AS p, premie AS pr
WHERE p.nump = pr.nump
AND (
p.zarob > (
SELECT avg( p1.zarob )
FROM PRAC AS p1 )
OR pr.`pr_10%` >0
OR pr.`pr_20%` >0
)

/* ******** 2.b *******/
SELECT *
FROM prac AS p
WHERE p.nump NOT
IN (
SELECT p1.kier
FROM prac AS p1
WHERE stanowisko = 'SPRZEDAWCA'
)
AND p.zarob > (
SELECT avg( p2.zarob )
FROM prac AS p2
WHERE stanowisko = 'ANALITYK' )
AND stanowisko = 'KIEROWNIK'

/* ******** 2.c *******/
SELECT p1.nazwp AS 'NAZWP GÓRNE', p1.zarob AS 'GÓRNE', p2.nazwp AS 'NAZWP DOLNE', p2.zarob AS 'DOLNE'
FROM prac AS p1
JOIN prac AS p2 ON p1.zarob > p2.zarob
WHERE p1.zarob >3000
AND p2.zarob >3000

/* ******** 2.d *******/
SELECT p.nump, p.nazwp, p.stanowisko, p.zarob
FROM prac AS p
WHERE p.stanowisko = 'ANALITYK'
AND p.zarob > (
SELECT max( p1.zarob )
FROM prac AS p1
WHERE p1.stanowisko = 'URZEDNIK' )
AND p.nazwp NOT LIKE 'Z%'

/* ******** 2.e ********/
SELECT d.nazwdz, p.nazwp
FROM dział AS d, prac AS p
WHERE p.numdz = d.numdz
AND p.stanowisko = 'KIEROWNIK'

/* ******* 2.f ********/
SELECT p.stanowisko, d.nazwdz
FROM prac AS p, dział AS d
WHERE p.zarob > (
SELECT avg( p1.zarob )
FROM prac AS p1 )
AND p.zatrud > '2007-01-12'
AND d.numdz = p.numdz
GROUP BY d.nazwdz, p.stanowisko

/* ******* 2.g *******/
SELECT *
FROM dział AS d, prac AS p
WHERE p.zatrud = (
SELECT min( p1.zatrud )
FROM prac AS p1
WHERE p1.numdz = d.numdz )
AND p.zarob < (
SELECT avg( p2.zarob )
FROM prac AS p2 )
AND p.stanowisko = 'KIEROWNIK'