Pytanie egzaminacyjne z przedmiotu: Bazy Danych

1. Opracuj projekt koncepcyjny (logiczny model) bazy danych dla potrzeb zarządzania hotelem. W BD mają się znaleźć informacje o gościach, pokojach podzielonych na kategorie, rezerwacjach oraz informacje niezbędne do realizacji w SQL nw. transakcji (zapytań).

• dokonaj rezerwacji, przypisując wolny pokój zgodnie z życzeniami gościa, jeżeli nie można spełnić oczekiwania gościa zaproponuj porównywalne rozwiązanie (Potrzeby gościa zdefiniuj sam)

/* Krok 1 - sprawdzenei czy klient już jest w bazie */

SELECT id FROM Klienci, Kraje WHERE imie='Jan' AND nazwisko='Bak' AND nazwa_kraju='Polska';

/* Krok 2 - w przypadku braku id dodać klienta do bazy */

INSERT INTO Klienci (imie, nazwisko, kraj_id) VALUES (`Jan', `Bak', 1);

/* Krok 3 - wyszukanie wolnego pokoju */

SELECT MIN(numer) FROM Pokoje, Typy_pokoi WHERE ilość_osob=1 AND numer NOT IN (SELECT numer FROM Rezerwacje WHERE data_przyjazdu >= `2007-07-07'AND data_wyjazdu < `2007-07-07'

/* Krok 4 - dodanie rezerwacji */

INSERT INTO Rezerwacje (id_klienta, id_pokoju, cena_calk, data_rezerwacji, data_przyjazdu, data_wyjazdu, status_id) VALUES (432, 12, 1200, NOW(), `2007-07-07', `2007-07-07', 1 /* Status Id 1 = Oczekiwanie na klienta */)

• sprawdź ile razy dana osoba była gościem w ostatnich dwóch latach

SELECT COUNT(id_rezerwacji)

FROM Rezerwacje, Klienci, Statusy

WHERE imie='Jan' AND nazwisko='Nowak' AND data_przyjazdu > `2005-01-01' AND Statusy.nazwa='Zakonczona';

• godz. 23 sprawdź - którzy goście przybyli do hotelu zgodnie z rezerwacją

SELECT imie, nazwisko, numer

FROM Rezerwacje, Klienci, Statusy

WHERE data_przyjazdu =`2007-02-02' AND Statusy.nazwa='Klient w hotelu'

• zwolnij wolny pokój i wypisz rachunek

UPDATE Rezerwacje SET status_id=3 /* Wyjechal */ WHERE id_rezerwacji=4;

SELECT cena FROM Rezerwacje WHERE id_rezerwacji=4;

• pokaż wszystkie wolne i zajęte pokoje wg kategorii

/* Krok 1 - lista wolnych pokoi wg. kategorii */

INSERT INTO Wolne_pokoje (typ_id, liczba)

SELECT typ_id, COUNT(*) FROM Rezerwacje, Pokoje, Statusy

WHERE `2007-02-02' NOT BETWEEN data_przyjazdu AND data_wyjazdu AND Statusy.nazwa='Klient w hotelu' GROUP BY typ_id

/* Krok 2 - lista wolnych pokoi wg. kategorii */

INSERT INTO Zajete_pokoje (typ_id, liczba)

SELECT typ_id, COUNT(*) FROM Rezerwacje, Pokoje, Statusy

WHERE `2007-02-02' BETWEEN data_przyjazdu AND data_wyjazdu AND Statusy.nazwa='Klient w hotelu' GROUP BY typ_id

/* Krok 3 - zestawieni kategorii */

SELECT nazwa_typu, w.liczba AS liczba_wolnych, z.liczba AS liczba_zajetych

FROM Typy_pokoi t, Wolne_pokoje w, Zajete_pokoje z

WHERE t.typ_id=w.typ_id AND t.typ_id=w.typ_id

• sporządź zestawienie opłat za poszczególne kategorie pokoi za ostatni miesiąc

SELECT Typy_pokoi.nazwa SUM(cena)

FROM Rezerwacje, Pokoje, Typy_pokoi

WHERE data_przyjazdu>='2007-01-01' AND data_wyjazdu<='2007-01-31'

GROUP BY Typy_pokoi.nazwa

• sporządź zestawienie wykorzystania poszczególnych kategorii za ostatni kwartał

/* Krok 1 - policzenie liczby pokoi w każdej kategorii */

INSERT INTO Typ_ile_pokoi (typ_id, suma)

SELECT typ_id, COUNT(numer) FROM Pokoje GROUP BY typ_id

/* Krok 2 - policzenie ile dni były zajęte poszczególne typy pokoi */

INSERT INTO Typ_ile_dob (typ_id, suma)

SELECT typ_id, SUM(data_wyjazdu-data_przyjazdu)

FROM Rezerwacje, Pokoje

WHERE data_przyjazdu>='2006-09-01' AND data_wyjazdu<='2006-12-31'

/* Krok 3 - Policzenie obłożenia procentowego */

SELECT Typy_pokoi.nazwa, Typ_ile_dob.suma/(Typ_ile_pokoi.suma*92/*dni*/)*100+'%' AS wykrzystanie

FROM Typy_pokoi, Typ_ile_pokoi, Typ_ile_dob

WHERE Typy_pokoi.typ_id= Typ_ile_pokoi.typ_id AND Typy_pokoi.typ_id= Typ_ile_dob.typ_id

• wpisz nazwiska wszystkich gości, którzy mieszkali w pokojach jednoosobowych w okresie od 12 XI 05 do 21 I 06

SELECT DISTINCT Klienci.nazwisko

FROM Rezerwacje, Statusy, Klienci, Pokoje, Typy_pokoi

WHERE data_przyjzdu >='2005-12-12' AND data_wyjazdu>'2006-01-21' AND Status.nazwa='Klient wyjechal' AND Typy_pokoi.ilosc_osob=1

• sporządź listę pokoi które były wolne więcej niż 10 dni w miesiącu maju 2005

SELECT numer

FROM Rezerwacje

WHERE data_przyjazdu>='2005-05-01' AND data_wyjazdu<='2005-05-31'

GROUP BY numer HAVING SUM (data_wyjazdu-data_przyjazdu) <=21

2. Szkoła muzyczna. Uczniowie wypożyczają sprzęt ze szkolnej wypożyczalni. Trwają prace nad BD o wypożyczonym sprzęcie - aktualnie BD jest w następującej postaci:

• kod instrument

• nazwa instrumentu

• typ instrumentu

• rok produkcji

• kod studenta

• nazwisko studenta

• wydział

• rok studiów

• klasa muzyczna

• data wypożyczenia

• data zwrotu

• opis typu instrumentu

Doprowadź BD do 2NF i 3NF wskazując typ, rodzaj i charakter relacji, klucze - pokazując graficznie wszystkie zależności funkcyjne.

2. Baza danych korporacji biznesowej składa się z czterech następujących tabel omówionych na wykładzie:

- PRAC, DZIALY, PREMIE, GRUPY ZAROBKOWE

Dla tak zdefiniowanych tabel sformułuj następujące zapytania SQL:

1. Wypisz działy (NUMDZ, NAZWADZ, i LOK), które mają kierownika (kierowników) zatrudnionego w firmie po swoim podwładnych i zarabiających więcej niż wynosi średnia pensja w dziale;

/* Krok 1 - wyliczenie średniej pensji oraz date ost. zatrudnia w dz. */

INSERT INTO DZIAŁ_SR (NUMDZ, D_SR_ZAROB, D_OSTATNIE_ZATR)

SELECT NUMDZ, AVG(ZAROB), MAX(ZATRUD) FROM PRAC GROUP BY NUMDZ

/* Krok 2 - wyszukanie pracowników i odpowiadającym im działów */

SELECT DZIAŁ.NUMDZ, DZIAŁ.NAZWADZ, DZIAŁ.LOK

FROM PRAC, DZIAŁ, DZIAŁ_SR

WHERE PRAC.NUMDZ=DZIAŁ.NUMDZ AND PRAC.NUMDZ=DZIAŁ_SR.NUMDZ AND

PRAC.ZAROB > DZIAŁ_SR.D_SR_ZAROB AND PRAC.ZATRUD=DZIAŁ_SR.D_OSTATNIE_ZATR

2. Oblicz, jaki procent sumy wszystkich pensji (pensji ogółem) w firmie stanowi pensja każdego pracownika;

/* Krok 1 - wyliczenie sumy zarobków */

INSERT INTO SUMA_ZAROB (SUMA_ZAROB) SELECT SUM(ZAROB) FROM PRAC;

/* Krok 2 - wyliczenie procentów */

SELECT PRAC.NAZWAP, PRAC.ZAROB/SUMA_ZAROB*100+'%' AS PROCENT

FROM PRAC, SUMA_ZAROB

3. Wypisz stanowiska w poszczególnych działach obsadzone przez dwóch lub więcej pracowników, którzy zarabiają więcej niż wynosi średnia wszystkich pracowników i którzy zostali zatrudnieni po 12.02.2002 roku.

/* Krok 1 - wyliczenie średniej wszystkich pracowników */

INSERT INTO PRAC_SR_ZAROB (SR_ZAROB) SELECT AVG(ZAROB) FROM PRAC;

/* Krok 2 - wyszukanie stanowisk

SELECT DZIAŁ.NAZWADZ, PRAC.STANOWISKO

FROM PRAC, DZIAŁ, PRAC_SR_ZAROB

WHERE PRAC.NUMDZ=DZIAŁ.NUMDZ AND

PRAC.ZAROB > PRAC_SR_ZAROB. SR_ZAROB AND PRAC.ZATRUD > `2002-02-12'

GROUP BY DZIAŁ.NAZWADZ, PRAC.STANOWISKO

HAVING COUNT(*) >= 2

4. Wypisz nazwy wszystkich działów wraz z nazwiskami ich kierowników (pracownik działu, którego stanowisko = KIEROWNIK, w każdym dziale pracuje tylko jeden kierownik)

SELECT PRAC.NAZWP, DZIAŁ.NAZWADZ

FROM DZIAŁ, PRAC

WHERE PRAC.NUMDZ=DZIAŁ.NUMDZ AND PRAC.STANOWISKO='KIEROWNIK'

5. Ustaw pracowników zarabiających ponad 3000$ we wszystkie możliwe pary w ten sposób, aby po lewej stronie w parze znajdował się pracownik z pensją większą niż pensja pracownika po prawej stronie. Wypisz wartości NAZWP i ZAROB dla obu pracowników w parze. Kolumny nazwij następująco NAZWP GÓRNE, GÓRNE, NAZWP_DOLNE, DOLNE. Posortuj tak, aby wartości w kolumnie GÓRNE były ustawione od najwyższej do najniższej.

SELECT P1.NAZWP AS [NAZWP GÓRNE], P1.ZAROB AS GÓRNE,

P2.NAZWP AS [NAZWP DOLNE], P2.ZAROB AS DOLNE

FROM PRAC P1, PRAC P2

WHERE P1.ZAROB > 3000 AND P2.ZAROB > 3000 AND P1.ZAROB > P2.ZAROB

ORDER BY 2 DESC

6. Wskaż wszystkich urzędników, którzy pracują w dziale 10 i których nazwisko nie rozpoczyna się na literę Z oraz których pensja jest większa niż pensja sprzedawców. Podaj ID pracownika, nazwisko, stanowisko i zarobki.

/* Krok 1 - wyliczenie średniej pensji sprzedawców */

INSERT INTO SPRZEDAWCY_SR_ZAROB (SR_ZAROB)

SELECT AVG(ZAROB) FROM PRAC WHERE STANOWISKO='SPRZEDAWCY';

/* Krok 2 - wyszukanie urzędników */

SELECT PRAC.NUMP, PRAC.NAZWP, PRAC.STANOWISKO, PRAC.ZAROB

FROM PRAC, SPRZEDAWCY_SR_ZAROB

WHERE PRAC.NUMDZ=10 AND PRAC.NAZWP NOT LIKE `Z%' AND

PRAC.ZAROB > SPRZEDAWCY_SR_ZAROB.SR_ZAROB

7. Wskaż pracowników na stanowisku KIEROWNIK, którzy nie maja podwładnych na stanowisku ANALITYK i których zarobki są większe od zarobków średnich ANALITYKÓW.

/* Krok 1 - wyliczenie AVG(ZAROB) analityków */

INSERT INTO ANALITYK_SR_ZAROB (SR_ZAROB)

SELECT AVG(ZAROB) FROM PRAC WHERE STANOWISKO='ANALITYK';

/* Krok 2 - policzenie analitykow w kazdym dziale */

INSERT INTO DZIAŁ_ANALIT (NUMDZ, ILE_ANALIT)

SELECT NUMDZ, COUNT(NUMP) FROM PRAC

WHERE STANOWISKO='ANALITYK' GROUP BY NUMDZ

/* Krok 3 - wyszukanie kierowników */

SELECT PRAC.NUMP, PRAC.NAZWP, PRAC.STANOWISKO

FROM PRAC, DZIAŁ_ANALIT, ANALITYK_SR_ZAROB

WHERE PRAC.STANOWISKO='KIEROWNIK' AND

PRAC.NUMDZ=ANALITYK_SR_ZAROB.NUMDZ AND ANALITYK_SR_ZAROB.ILE_ANALIT = 0 AND

PRAC.ZAROB > ANALITYK_SR_ZAROB.SR_ZAROB

8. Wyświetli listę zarówno tych pracowników, którzy dostają premię, jak i tych, którzy mają pensje powyżej średniej.

/* Krok 1 - wyliczenie średniej pensji */

INSERT INTO PRAC_SR_ZAROB (SR_ZAROB) SELECT AVG(ZAROB) FROM PRAC;

/* Krok 2 - wyszukanie pracowników */

SELECT PRAC.NUMP, PRAC.NAZWP, PRAC.STANOWISKO

FROM PRAC, PREMIE, PRAC_SR_ZAROB

WHERE PREMIE.PROW > 0 OR PRAC.ZAROB > PRAC_SR_ZAROB.SR_ZAROB;

Opracował: Grz3g0rz http://mrlewy.wrzuta.pl/audio/ik6Hqa4kZp/stu_det_-_wierze_ze_moge_zdac

---