USE CASE - Projekt z baz danych, polecenia sql, przykladowe przypadki użycia. PDF

Preview

Summary

USE CASE’Y Wyświetl imię, nazwisko, pesel oraz liczbę posiadanych pojazdów dla osób, które posiadają prawo jazdy kategorii B, a ich pojazdy posiadają ważne ubezpieczenie. SELECT IMIĘ, NAZWISKO, PESEL, COUNT(DR_DOWÓD) AS LICZBA_POJAZDÓW FROM OSOBY O JOIN PRAWA_JAZDY PJ ON (O = PJ_PESEL) JOIN DATY D O...

Description

USE CASE’Y

1. Wyświetl imię, nazwisko, pesel oraz liczbę posiadanych pojazdów dla osób, które posiadają prawo jazdy kategorii B, a ich pojazdy posiadają ważne ubezpieczenie. SELECT IMIĘ, NAZWISKO, PESEL, COUNT(DR.ID_DOWÓD) AS LICZBA_POJAZDÓW FROM OSOBY O JOIN PRAWA_JAZDY PJ ON (O.PESEL = PJ.OSOBY_PESEL) JOIN DATY D ON (PJ.ID_PJ = D.PRAWA_JAZDY_ID_PJ) JOIN KATEGORIE K ON (D.KATEGORIE_NAZWA = K.NAZWA) JOIN OSOBA_DOWÓD OD ON (O.PESEL = OD.OSOBY_PESEL) JOIN DOWODY_REJESTRACYJNE DR ON(OD.DOWODY_REJESTRACYJNE_ID_DOWÓD = DR.ID_DOWÓD) JOIN UBEZPIECZENIA U ON (DR.ID_DOWÓD = U.DOWODY_REJESTRACYJNE_ID_DOWÓD) WHERE U.DATA_WAŻNOŚCI_POLISY >= SYSDATE AND K.NAZWA = 'B' GROUP BY IMIĘ, NAZWISKO, PESEL;

2. Wyświetl datę, rodzaj i opis zdarzenia oraz dodatkową kolumnę zawierającą nazwę służby mundurowej i trzy ostatnie cyfry numeru służbowego dla zdarzeń do których przyjechała straż miejska w roku 2019. Jeżeli opis zdarzenia jest pusty, zastąp go frazą ‘brak opisu’. SELECT DATA_ZDARZENIA, RODZAJ_ZDARZENIA, NVL(OPIS_ZDARZENIA, 'brak opisu') AS OPIS_ZDARZENIA, CONCAT(S.RODZAJ_SŁUŻBY_MUNDUROWEJ,CONCAT(' ', SUBSTR(S.NR_SŁUŻBOWY,4,6))) AS PRZEDSTAWICIEL_SŁUŻB FROM ZDARZENIA Z JOIN PRZED_ZDARZ PZ ON(Z.ID_ZDARZENIA = PZ.ZDARZENIA_ID_ZDARZENIA) JOIN SŁUŻBY S ON(PZ.SŁUŻBY_NR_SŁUŻBOWY = S.NR_SŁUŻBOWY) WHERE S.RODZAJ_SŁUŻBY_MUNDUROWEJ = 'Straż Miejska' AND EXTRACT(YEAR FROM Z.DATA_ZDARZENIA) = 2019;

3. Utwórz nicki dla kierowców z kategorią prawa jazdy A składające się z pierwszej litery imienia, trzech pierwszych liter zapisanych małymi znakami, podkreślnika oraz roku urodzenia. Obok nicków wyświetl wiek uzyskania uprawnień na tę kategorię.

SELECT SUBSTR(IMIĘ,1,1) || LOWER(SUBSTR(NAZWISKO,1,3)) || '_' ||EXTRACT(YEAR FROM DATA_URODZENIA) AS NICK, FLOOR(MONTHS_BETWEEN(D.DATA_UZYSKANIA, DATA_URODZENIA)/12) AS WIEK_ZDANIA_NA_KAT_A FROM OSOBY O JOIN PRAWA_JAZDY PJ ON (O.PESEL = PJ.OSOBY_PESEL) JOIN DATY D ON (PJ.ID_PJ = D.PRAWA_JAZDY_ID_PJ) JOIN KATEGORIE K ON (D.KATEGORIE_NAZWA = K.NAZWA) WHERE K.NAZWA = 'A';

4. Wyświetl markę, typ pojazdu, numer rejestracyjny oraz datę zdarzenia dla pojazdów skontrolowanych w 2021 roku z ważnym badaniem technicznym. Posortuj dane od najstarszej daty zdarzenia. Daty zapisz w formacie obsługującym skrótową nazwę miesiąca. SELECT MARKA, TYP_POJAZDU, DR.NR_REJESTRACYJNY AS "TABLICA REJESTRACYJNA", TO_CHAR(Z.DATA_ZDARZENIA, 'DD-Mon-yyyy', 'nls_date_language= Polish') as "DATA KONTROLI" FROM POJAZDY PJ JOIN PRZEBIEGI PG ON(PJ.ID_POJAZDU = PG.POJAZDY_ID_POJAZDU) JOIN ZDARZENIA Z ON(PG.ZDARZENIA_ID_ZDARZENIA = Z.ID_ZDARZENIA) JOIN DOWODY_REJESTRACYJNE DR ON(PJ."DOWODY_REJESTRACYJNE_ID_DOWÓD" = DR."ID_DOWÓD") JOIN BADANIA_TECHNICZNE BT ON(DR."ID_DOWÓD" = BT."DOWODY_REJESTRACYJNE_ID_DOWÓD") WHERE EXTRACT(YEAR FROM Z.DATA_ZDARZENIA) = 2021 AND Z.RODZAJ_ZDARZENIA='kontrola drogowa' AND BT."DATA_WAŻNOŚCI_BADANIA" >= SYSDATE ORDER BY DATA_ZDARZENIA;

5. Wyświetl marki, modele oraz numery VIN pojazdów, których przebiegi zarejestrowane podczas badań technicznych przeprowadzonych w 2020 roku, były niższe niż średnia zarejestrowanych przebiegów podczas przeglądów w roku poprzednim. SELECT MARKA, MODEL, NR_VIN FROM POJAZDY PJ JOIN DOWODY_REJESTRACYJNE DR ON(PJ."DOWODY_REJESTRACYJNE_ID_DOWÓD" = DR."ID_DOWÓD") JOIN BADANIA_TECHNICZNE BT ON(DR."ID_DOWÓD" = BT."DOWODY_REJESTRACYJNE_ID_DOWÓD") WHERE BT.WPIS_PRZEBIEGU < (SELECT AVG(BT.WPIS_PRZEBIEGU) FROM BADANIA_TECHNICZNE BT WHERE EXTRACT(YEAR FROM BT.DATA_BADANIA) = 2019) AND EXTRACT(YEAR FROM BT.DATA_BADANIA) = 2020;

6. Wyświetl marki, numery vin, masę własną oraz dopuszczalną ładowność pojazdów, których masa własna jest większa od masy własnej pojazdu o numerze vin W0L0AHL4868070738.

SELECT MARKA, NR_VIN, MASA_WŁASNA, (DR.DOP_MASA_CAŁKOWITA-PJ.MASA_WŁASNA) AS DOP_ŁADOWNOŚĆ FROM POJAZDY PJ JOIN DOWODY_REJESTRACYJNE DR ON(PJ."DOWODY_REJESTRACYJNE_ID_DOWÓD" = DR."ID_DOWÓD") WHERE (PJ.MASA_WŁASNA) > (SELECT (PJ.MASA_WŁASNA) FROM POJAZDY PJ WHERE PJ.NR_VIN = 'W0L0AHL4868070738')

7. Wyświetl imiona, nazwiska oraz sumy punktów karnych osób posiadających pojazd osobowy o mocy wyższej niż 150 KM. Posortuj dane od największej do najmniejszej ilości punktów.

SELECT IMIĘ, NAZWISKO, SUM(W.LICZBA_PUNKTÓW_KARNYCH) AS SUMA_PUNKTÓW_KARNYCH FROM OSOBY O JOIN OSOBA_DOWÓD OD ON (O.PESEL = OD.OSOBY_PESEL) JOIN DOWODY_REJESTRACYJNE DR ON(OD.DOWODY_REJESTRACYJNE_ID_DOWÓD = DR.ID_DOWÓD) JOIN POJAZDY P ON(DR.ID_DOWÓD = P.DOWODY_REJESTRACYJNE_ID_DOWÓD) JOIN OSOBA_ZDARZ OZ ON(O.PESEL = OZ.OSOBY_PESEL) JOIN ZDARZENIA Z ON(OZ.ZDARZENIA_ID_ZDARZENIA = Z.ID_ZDARZENIA) JOIN ZDARZ_WYKRO ZW ON(Z.ID_ZDARZENIA = ZW.ZDARZENIA_ID_ZDARZENIA) JOIN WYKROCZENIA W ON(ZW.WYKROCZENIA_ID_WYKROCZENIA = W.ID_WYKROCZENIA) WHERE P.MOC_SILNIKA_KM > 150 AND P.TYP_POJAZDU = 'osobowy' GROUP BY IMIĘ, NAZWISKO, DATA_URODZENIA, P.MOC_SILNIKA_KM ORDER BY SUMA_PUNKTÓW_KARNYCH DESC;

8. Wyświetl marki, modele, roczniki i daty ważności polis pojazdów oraz określ dla każdego z nich aktualność ubezpieczenia. Dane posortuj malejąco po roczniku pojazdu.

SELECT MARKA, MODEL, ROCZNIK, U.DATA_WAŻNOŚCI_POLISY, CASE WHEN U.DATA_WAŻNOŚCI_POLISY > SYSDATE THEN 'aktualne' WHEN U.DATA_WAŻNOŚCI_POLISY = SYSDATE THEN 'do dzisiaj' ELSE 'nieaktualne' END AS Ważność FROM POJAZDY P JOIN DOWODY_REJESTRACYJNE DR ON(P.ID_POJAZDU = DR.POJAZDY_ID_POJAZDU) JOIN UBEZPIECZENIA U ON (DR.ID_DOWÓD = U.DOWODY_REJESTRACYJNE_ID_DOWÓD) ORDER BY ROCZNIK DESC;

Tworzenie perspektyw: CREATE VIEW USE_CASE1 AS {polecenie sql} (bez order by)

Tworzenie synonimów: CREATE SYNONYM UC1 FOR USE_CASE1;

Select * from UC1;...