xjezad00_xblanc01.sql

-- RUSENI PREDCHAZEJICICH TABULEK
DROP TABLE KLIENT CASCADE CONSTRAINTS;
DROP TABLE REZERVACE CASCADE CONSTRAINTS;
DROP TABLE POBYT CASCADE CONSTRAINTS;
DROP TABLE POKOJ CASCADE CONSTRAINTS;
DROP TABLE TYP_POKOJE CASCADE CONSTRAINTS;
DROP TABLE SLUZBA CASCADE CONSTRAINTS;
DROP TABLE RECEPCNI CASCADE CONSTRAINTS;
DROP TABLE VYUZITI_SLUZBY CASCADE CONSTRAINTS;
DROP TABLE POBYT_POKOJ CASCADE CONSTRAINTS;
DROP TABLE CENA CASCADE CONSTRAINTS;

-- RUSENI SEKVENCI
DROP SEQUENCE KLIENT_SEQ;
DROP SEQUENCE RECEPCNI_SEQ;
DROP SEQUENCE REZERVACE_SEQ;
DROP SEQUENCE POBYT_SEQ;
DROP SEQUENCE VYUZITI_SLUZBY_SEQ;
DROP SEQUENCE SLUZBA_SEQ;
DROP SEQUENCE CENA_SEQ;
DROP SEQUENCE POBYT_POKOJ_SEQ;


-- VYTVORENI TABULKY KLIENT
CREATE TABLE KLIENT (
  OS_CISLO NUMBER NOT NULL,
  PRIJMENI VARCHAR2(25) NOT NULL,
  JMENO VARCHAR2(25) NOT NULL,
  DATUM_NAR DATE NOT NULL,
  EMAIL VARCHAR2(40),
  TEL_CISLO VARCHAR2(16) NOT NULL);
ALTER TABLE KLIENT ADD( PRIMARY KEY (OS_CISLO));

-- VYTVORENI TABULKY REZERVACE
CREATE TABLE REZERVACE (
  ID_REZ NUMBER NOT NULL,
  DATUM DATE NOT NULL,
  VLASTNIK NUMBER NOT NULL);
ALTER TABLE REZERVACE ADD( PRIMARY KEY (ID_REZ));
ALTER TABLE REZERVACE ADD( FOREIGN KEY (VLASTNIK) REFERENCES KLIENT (OS_CISLO));

-- VYTVORENI TABULKY RECEPCNI
CREATE TABLE RECEPCNI (
  OS_CISLO NUMBER NOT NULL,
  PRIJMENI VARCHAR2(25) NOT NULL,
  JMENO VARCHAR2(25) NOT NULL,
  ADRESA VARCHAR2(50) NOT NULL);
ALTER TABLE RECEPCNI ADD( PRIMARY KEY (OS_CISLO));

-- VYTVORENI TABULKY TYP POKOJE
CREATE TABLE TYP_POKOJE (
  TYP VARCHAR2(15) NOT NULL,
  KAPACITA NUMBER NOT NULL,
  POPIS VARCHAR2(100) NOT NULL);
ALTER TABLE TYP_POKOJE ADD( PRIMARY KEY (TYP));

-- VYTVORENI TABULKY CENA
CREATE TABLE CENA (
  ID_CENA NUMBER NOT NULL,
  CENA_DEN NUMBER NOT NULL,
  DATUM_OD DATE NOT NULL,
  DATUM_DO DATE NOT NULL,
  TYP VARCHAR2(15) NOT NULL);
ALTER TABLE CENA ADD( PRIMARY KEY (ID_CENA));
ALTER TABLE CENA ADD( FOREIGN KEY (TYP) REFERENCES TYP_POKOJE (TYP));

-- VYTVORENI TABULKY SLUZBA
CREATE TABLE SLUZBA (
  ID_SLUZBA NUMBER NOT NULL,
  NAZEV VARCHAR2(20) NOT NULL,
  CENA NUMBER NOT NULL,
  POPIS VARCHAR2(100) NOT NULL);
ALTER TABLE SLUZBA ADD( PRIMARY KEY (ID_SLUZBA));

-- VYTVORENI TABULKY POKOJ
CREATE TABLE POKOJ (
  CISLO NUMBER NOT NULL,
  TYP VARCHAR2(15) NOT NULL);
ALTER TABLE POKOJ ADD( PRIMARY KEY (CISLO));
ALTER TABLE POKOJ ADD( FOREIGN KEY (TYP) REFERENCES TYP_POKOJE (TYP));

-- VYTVORENI TABULKY POBYT
CREATE TABLE POBYT (
  ID_POBYT NUMBER NOT NULL,
  DATUM_OD TIMESTAMP NOT NULL,
  DATUM_DO TIMESTAMP NOT NULL,
  SLEVA NUMBER DEFAULT 0,
  PLATBA NUMBER DEFAULT 0,
  PREVZAL_RECEP NUMBER,
  ID_REZ NUMBER,
  VLASTNIK NUMBER NOT NULL);
ALTER TABLE POBYT ADD( PRIMARY KEY(ID_POBYT));
ALTER TABLE POBYT ADD( FOREIGN KEY (PREVZAL_RECEP) REFERENCES RECEPCNI (OS_CISLO));
ALTER TABLE POBYT ADD( FOREIGN KEY (ID_REZ) REFERENCES REZERVACE (ID_REZ));
ALTER TABLE POBYT ADD( FOREIGN KEY (VLASTNIK) REFERENCES KLIENT (OS_CISLO));

-- VYTVORENI TABULKY POBYT POKOJ
CREATE TABLE POBYT_POKOJ (
  ID_POBYT_POKOJ NUMBER NOT NULL,
  POBYT NUMBER NOT NULL,
  POKOJ NUMBER NOT NULL,
  LIDI NUMBER NOT NULL);
ALTER TABLE POBYT_POKOJ ADD( PRIMARY KEY(ID_POBYT_POKOJ));
ALTER TABLE POBYT_POKOJ ADD( FOREIGN KEY(POBYT) REFERENCES POBYT(ID_POBYT));
ALTER TABLE POBYT_POKOJ ADD( FOREIGN KEY(POKOJ) REFERENCES POKOJ(CISLO));

-- VYTVORENI TABAULKY VYUZITI SLUZBY
CREATE TABLE VYUZITI_SLUZBY (
  ID_VYUZITI NUMBER NOT NULL,
  POBYT_POKOJ NUMBER NOT NULL,
  SLUZBA NUMBER NOT NULL,
  DATUM_OD TIMESTAMP NOT NULL,
  DATUM_DO TIMESTAMP,
  POCET NUMBER);
ALTER TABLE VYUZITI_SLUZBY ADD( PRIMARY KEY(ID_VYUZITI));
ALTER TABLE VYUZITI_SLUZBY ADD( FOREIGN KEY(SLUZBA) REFERENCES SLUZBA(ID_SLUZBA));
ALTER TABLE VYUZITI_SLUZBY ADD( FOREIGN KEY(POBYT_POKOJ) REFERENCES POBYT_POKOJ(ID_POBYT_POKOJ));


-- VYTVORENI SEKVENCI
CREATE SEQUENCE SLUZBA_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE MINVALUE 1 NOCYCLE;
CREATE SEQUENCE KLIENT_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE MINVALUE 1 NOCYCLE;
CREATE SEQUENCE RECEPCNI_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE MINVALUE 1 NOCYCLE;
CREATE SEQUENCE REZERVACE_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE MINVALUE 1 NOCYCLE;
CREATE SEQUENCE POBYT_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE MINVALUE 1 NOCYCLE;
CREATE SEQUENCE VYUZITI_SLUZBY_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE MINVALUE 1 NOCYCLE;
CREATE SEQUENCE CENA_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE MINVALUE 1 NOCYCLE;
CREATE SEQUENCE POBYT_POKOJ_SEQ INCREMENT BY 1 START WITH 1 NOMAXVALUE MINVALUE 1 NOCYCLE;


-- VLOZENI DAT
-- KLIENT
INSERT INTO KLIENT (OS_CISLO, PRIJMENI, JMENO, DATUM_NAR, EMAIL, TEL_CISLO) VALUES(KLIENT_SEQ.NEXTVAL, 'Jež', 'Adam', TO_DATE('11-07-1992','DD-MM-YYYY'), 'xjezad00@stuf.fit.vutbr.cz', '+420736182739');
INSERT INTO KLIENT (OS_CISLO, PRIJMENI, JMENO, DATUM_NAR, EMAIL, TEL_CISLO) VALUES(KLIENT_SEQ.NEXTVAL, 'Blanco', 'Roman', TO_DATE('05-01-1992','DD-MM-YYYY'), 'xblanc00@stuf.fit.vutbr.cz', '+420732265489');
INSERT INTO KLIENT (OS_CISLO, PRIJMENI, JMENO, DATUM_NAR, EMAIL, TEL_CISLO) VALUES(KLIENT_SEQ.NEXTVAL, 'Tichý', 'Jaroslav', TO_DATE('20-03-1974','DD-MM-YYYY'), 'JaroslavTichy@seznam.cz', '+420739457315');
INSERT INTO KLIENT (OS_CISLO, PRIJMENI, JMENO, DATUM_NAR, EMAIL, TEL_CISLO) VALUES(KLIENT_SEQ.NEXTVAL, 'Kubìnová', 'Blanka', TO_DATE('25-10-1989','DD-MM-YYYY'), 'blanka007@atlas.cz', '+420726356456');
INSERT INTO KLIENT (OS_CISLO, PRIJMENI, JMENO, DATUM_NAR, EMAIL, TEL_CISLO) VALUES(KLIENT_SEQ.NEXTVAL, 'Záruba', 'Martin', TO_DATE('16-12-1976','DD-MM-YYYY'), 'Zaruba@centrum.cz', '+420736182456');

-- SLUZBA
INSERT INTO SLUZBA (ID_SLUZBA, NAZEV, CENA, POPIS) VALUES (SLUZBA_SEQ.NEXTVAL, 'Bazén', '50', 'Krytý bazen poskytován s cenou za hodinu');
INSERT INTO SLUZBA (ID_SLUZBA, NAZEV, CENA, POPIS) VALUES (SLUZBA_SEQ.NEXTVAL, 'Pingpong', '30', 'Pingpongový stùl je umístìný v telocviènì. Pálky si mùžete pùjèit na recepci. Cena je za jednu hru.');
INSERT INTO SLUZBA (ID_SLUZBA, NAZEV, CENA, POPIS) VALUES (SLUZBA_SEQ.NEXTVAL, 'Trampolína', '40', 'Venkovní trampolína se platí po 15 minutách');
INSERT INTO SLUZBA (ID_SLUZBA, NAZEV, CENA, POPIS) VALUES (SLUZBA_SEQ.NEXTVAL, 'Ètyøkolka', '250', 'Pùjèení ètyøkolky na 30 minut');
INSERT INTO SLUZBA (ID_SLUZBA, NAZEV, CENA, POPIS) VALUES (SLUZBA_SEQ.NEXTVAL, 'Kolo', '120', 'Zapùjèení kola je možné na recepci. Cena je za 2 hodiny.');

-- TYP POKOJE
INSERT INTO TYP_POKOJE (TYP, KAPACITA, POPIS) VALUES (1, 4, 'Standardní pokoj pro 4 lidi');
INSERT INTO TYP_POKOJE (TYP, KAPACITA, POPIS) VALUES (2, 6, 'Standardní pokoj pro rodinu s kapacitou 6 lidí');
INSERT INTO TYP_POKOJE (TYP, KAPACITA, POPIS) VALUES (3, 2, 'Aparartmán se strešním oknem a barem pro 2 lidi');

-- POKOJ
INSERT INTO POKOJ (CISLO, TYP) VALUES (101, 1);
INSERT INTO POKOJ (CISLO, TYP) VALUES (102, 2);
INSERT INTO POKOJ (CISLO, TYP) VALUES (103, 1);
INSERT INTO POKOJ (CISLO, TYP) VALUES (201, 3);
INSERT INTO POKOJ (CISLO, TYP) VALUES (202, 2);

-- CENA (ZA TYP POKOJE)
INSERT INTO CENA (ID_CENA, CENA_DEN, DATUM_OD, DATUM_DO, TYP) VALUES (CENA_SEQ.NEXTVAL, 110, TO_DATE('01-07','DD-MM'), TO_DATE('31-08','DD-MM'), 1);
INSERT INTO CENA (ID_CENA, CENA_DEN, DATUM_OD, DATUM_DO, TYP) VALUES (CENA_SEQ.NEXTVAL, 100, TO_DATE('01-09','DD-MM'), TO_DATE('30-06','DD-MM'), 1);
INSERT INTO CENA (ID_CENA, CENA_DEN, DATUM_OD, DATUM_DO, TYP) VALUES (CENA_SEQ.NEXTVAL, 150, TO_DATE('01-07','DD-MM'), TO_DATE('31-08','DD-MM'), 2);
INSERT INTO CENA (ID_CENA, CENA_DEN, DATUM_OD, DATUM_DO, TYP) VALUES (CENA_SEQ.NEXTVAL, 140, TO_DATE('01-09','DD-MM'), TO_DATE('30-06','DD-MM'), 2);
INSERT INTO CENA (ID_CENA, CENA_DEN, DATUM_OD, DATUM_DO, TYP) VALUES (CENA_SEQ.NEXTVAL, 400, TO_DATE('01-07','DD-MM'), TO_DATE('31-08','DD-MM'), 3);
INSERT INTO CENA (ID_CENA, CENA_DEN, DATUM_OD, DATUM_DO, TYP) VALUES (CENA_SEQ.NEXTVAL, 350, TO_DATE('01-09','DD-MM'), TO_DATE('30-06','DD-MM'), 3);

-- RECEPCNI
INSERT INTO RECEPCNI (OS_CISLO, PRIJMENI, JMENO, ADRESA) VALUES (RECEPCNI_SEQ.NEXTVAL, 'Dorazilová', 'Iveta', 'Námoøní 10, Brno');
INSERT INTO RECEPCNI (OS_CISLO, PRIJMENI, JMENO, ADRESA) VALUES (RECEPCNI_SEQ.NEXTVAL, 'Bednáøová', 'Vlasta', 'Lidická 5, Brno');
INSERT INTO RECEPCNI (OS_CISLO, PRIJMENI, JMENO, ADRESA) VALUES (RECEPCNI_SEQ.NEXTVAL, 'Nováková', 'Jana', 'Osvoboditelù 47, Ostrava');
INSERT INTO RECEPCNI (OS_CISLO, PRIJMENI, JMENO, ADRESA) VALUES (RECEPCNI_SEQ.NEXTVAL, 'Køivá', 'Lucie', 'Alšova 12, Olomouc');
INSERT INTO RECEPCNI (OS_CISLO, PRIJMENI, JMENO, ADRESA) VALUES (RECEPCNI_SEQ.NEXTVAL, 'Trefná', 'Kateøina', 'Horákova 8, Pøerov');

-- REZERVACE
INSERT INTO REZERVACE (ID_REZ, DATUM, VLASTNIK) VALUES (REZERVACE_SEQ.NEXTVAL, TO_DATE('01-01-2014','DD-MM-YYYY'), 4);
INSERT INTO REZERVACE (ID_REZ, DATUM, VLASTNIK) VALUES (REZERVACE_SEQ.NEXTVAL, TO_DATE('15-01-2014','DD-MM-YYYY'), 5);

-- POBYT
INSERT INTO POBYT (ID_POBYT, DATUM_OD, DATUM_DO, SLEVA, VLASTNIK) VALUES (POBYT_SEQ.NEXTVAL, TO_DATE('01-04-2014','DD-MM-YYYY'), TO_DATE('14-04-2014','DD-MM-YYYY'), 5, 1);
INSERT INTO POBYT (ID_POBYT, DATUM_OD, DATUM_DO, SLEVA, VLASTNIK) VALUES (POBYT_SEQ.NEXTVAL, TO_DATE('07-04-2014','DD-MM-YYYY'), TO_DATE('14-04-2014','DD-MM-YYYY'), 8, 2);
INSERT INTO POBYT (ID_POBYT, DATUM_OD, DATUM_DO, SLEVA, PLATBA, PREVZAL_RECEP, VLASTNIK) VALUES (POBYT_SEQ.NEXTVAL, TO_DATE('01-03-2014','DD-MM-YYYY'), TO_DATE('14-03-2014','DD-MM-YYYY'), 0,8000, 1, 3);
INSERT INTO POBYT (ID_POBYT, DATUM_OD, DATUM_DO, SLEVA, PLATBA, PREVZAL_RECEP, ID_REZ, VLASTNIK) VALUES (POBYT_SEQ.NEXTVAL, TO_DATE('04-03-2014','DD-MM-YYYY'), TO_DATE('14-03-2014','DD-MM-YYYY'), 0,7000, 2, 1, 4);
INSERT INTO POBYT (ID_POBYT, DATUM_OD, DATUM_DO, SLEVA, PLATBA, PREVZAL_RECEP, ID_REZ, VLASTNIK) VALUES (POBYT_SEQ.NEXTVAL, TO_DATE('25-02-2014','DD-MM-YYYY'), TO_DATE('04-03-2014','DD-MM-YYYY'), 0,9000, 4, 1, 4);
INSERT INTO POBYT (ID_POBYT, DATUM_OD, DATUM_DO, SLEVA, PLATBA, PREVZAL_RECEP, ID_REZ, VLASTNIK) VALUES (POBYT_SEQ.NEXTVAL, TO_DATE('04-02-2014','DD-MM-YYYY'), TO_DATE('10-03-2014','DD-MM-YYYY'), 0,4000, 1, 2, 5);

-- POBYT POKOJ
INSERT INTO POBYT_POKOJ (ID_POBYT_POKOJ, POBYT, POKOJ, LIDI) VALUES (POBYT_POKOJ_SEQ.NEXTVAL, 1, 101, 4);
INSERT INTO POBYT_POKOJ (ID_POBYT_POKOJ, POBYT, POKOJ, LIDI) VALUES (POBYT_POKOJ_SEQ.NEXTVAL, 2, 103, 4);
INSERT INTO POBYT_POKOJ (ID_POBYT_POKOJ, POBYT, POKOJ, LIDI) VALUES (POBYT_POKOJ_SEQ.NEXTVAL, 3, 102, 5);
INSERT INTO POBYT_POKOJ (ID_POBYT_POKOJ, POBYT, POKOJ, LIDI) VALUES (POBYT_POKOJ_SEQ.NEXTVAL, 4, 202, 6);
INSERT INTO POBYT_POKOJ (ID_POBYT_POKOJ, POBYT, POKOJ, LIDI) VALUES (POBYT_POKOJ_SEQ.NEXTVAL, 4, 201, 2);
INSERT INTO POBYT_POKOJ (ID_POBYT_POKOJ, POBYT, POKOJ, LIDI) VALUES (POBYT_POKOJ_SEQ.NEXTVAL, 5, 201, 1);

-- VYUZITI SLUZBY
INSERT INTO VYUZITI_SLUZBY (ID_VYUZITI, POBYT_POKOJ, SLUZBA, DATUM_OD, DATUM_DO) VALUES (VYUZITI_SLUZBY_SEQ.NEXTVAL, 1, 1, TO_TIMESTAMP('07-04-2014 16:00','DD-MM-YYYY HH24:MI'),TO_TIMESTAMP('07-04-2014 20:00','DD-MM-YYYY HH24:MI'));
INSERT INTO VYUZITI_SLUZBY (ID_VYUZITI, POBYT_POKOJ, SLUZBA, DATUM_OD, POCET) VALUES (VYUZITI_SLUZBY_SEQ.NEXTVAL, 2, 2, TO_TIMESTAMP('10-04-2014 10:25','DD-MM-YYYY HH24:MI'), 10);
INSERT INTO VYUZITI_SLUZBY (ID_VYUZITI, POBYT_POKOJ, SLUZBA, DATUM_OD, POCET) VALUES (VYUZITI_SLUZBY_SEQ.NEXTVAL, 4, 2, TO_TIMESTAMP('28-02-2014 14:00','DD-MM-YYYY HH24:MI'), 2);
INSERT INTO VYUZITI_SLUZBY (ID_VYUZITI, POBYT_POKOJ, SLUZBA, DATUM_OD, POCET) VALUES (VYUZITI_SLUZBY_SEQ.NEXTVAL, 5, 2, TO_TIMESTAMP('27-02-2014 10:00','DD-MM-YYYY HH24:MI'), 4);
INSERT INTO VYUZITI_SLUZBY (ID_VYUZITI, POBYT_POKOJ, SLUZBA, DATUM_OD, DATUM_DO) VALUES (VYUZITI_SLUZBY_SEQ.NEXTVAL, 3, 3, TO_TIMESTAMP('08-04-2014 10:00','DD-MM-YYYY HH24:MI'),TO_TIMESTAMP('09-04-2014 14:00','DD-MM-YYYY HH24:MI'));

COMMIT;


-- SELECTY


-- SPOJENI DVOU TABULEK
-- u aktualnich registraci vypise jmeno a datum registrace
SELECT JMENO, PRIJMENI, DATUM 
FROM KLIENT, REZERVACE
WHERE (KLIENT.OS_CISLO = REZERVACE.VLASTNIK);

-- vypis kolik a ktery(a) recepcni prevzal(a) platbu a kolik
SELECT JMENO, PRIJMENI, PLATBA 
FROM POBYT 
  INNER JOIN RECEPCNI ON (POBYT.PREVZAL_RECEP = RECEPCNI.OS_CISLO);

-- SPOJENI TRI TABULEK
-- vypise zaregistrovane vyuziti sluzeb, jijich casove/poctove vymezeni a na ktery pokoj se registrace vztahuje
SELECT NAZEV, DATUM_OD, DATUM_DO, POCET, POKOJ
FROM VYUZITI_SLUZBY
  INNER JOIN SLUZBA ON (SLUZBA.ID_SLUZBA = VYUZITI_SLUZBY.SLUZBA)
  INNER JOIN POBYT_POKOJ ON (POBYT_POKOJ.ID_POBYT_POKOJ = VYUZITI_SLUZBY.POBYT_POKOJ);

-- GROUP BY A AGREGACNI FUNKCE
-- vypise nejlevnejsi (v aktualni den) neobsazene pokoje
SELECT POKOJ, MIN(CENA_DEN) AS MINIMALNI_CENA
FROM POBYT_POKOJ
  INNER JOIN POBYT ON (POBYT.ID_POBYT = POBYT_POKOJ.ID_POBYT_POKOJ)
  INNER JOIN POKOJ ON (POKOJ.CISLO = POBYT_POKOJ.POKOJ)
  INNER JOIN CENA ON (POKOJ.TYP = CENA.TYP)
WHERE (SYSDATE NOT BETWEEN POKOJ.DATUM_OD AND POKOJ.DATUM_DO)
GROUP BY POKOJ;

-- pocet aktualne ubytovanych lidi
SELECT SUM(LIDI) AS POCET_UBYTOVANYCH
FROM POBYT_POKOJ 
  INNER JOIN POBYT ON (POBYT.ID_POBYT = POBYT_POKOJ.POBYT)
WHERE (SYSDATE BETWEEN POBYT.DATUM_OD AND POBYT.DATUM_DO)
GROUP BY LIDI;

-- SELECT (EXISTS)
-- vypise aktualne obyvane pokoje, ktere vyuzivaji sluzeb hotelu
SELECT POKOJ
FROM POBYT_POKOJ
  INNER JOIN POBYT ON (POBYT.ID_POBYT = POBYT_POKOJ.POBYT)
WHERE (SYSDATE BETWEEN POBYT.DATUM_OD AND POBYT.DATUM_DO) AND EXISTS (
  SELECT POBYT_POKOJ
  FROM VYUZITI_SLUZBY
  WHERE VYUZITI_SLUZBY.POBYT_POKOJ = POBYT_POKOJ.POBYT 
);

-- SELECT (IN)
-- mezi ubytovanymi vyhleda osobu se jmenem 'Martin'
SELECT PRIJMENI, JMENO
FROM KLIENT
  INNER JOIN POBYT ON (KLIENT.OS_CISLO = POBYT.VLASTNIK)
WHERE (SYSDATE BETWEEN POBYT.DATUM_OD AND POBYT.DATUM_DO)
AND JMENO IN (
    SELECT JMENO
    FROM KLIENT
    WHERE JMENO = 'Martin'
);