ASO kolo test, WAT, semestr III, Bazy danych


Zadania z SQL'a

0x01 graphic

  1. Przekształcić model konceptualny danych do modelu fizycznej bazy danych (relacyjna).

  1. Lista ASO (IdASO, NazwaASO) z liczbą serwisowanych pojazdów i łączną kwotą wykonanych usług serwisowych za ostatni rok (posortować wg liczby serwisowanych pojazdów malejąco).

SELECT aso.idaso, aso.nazwaaso,count (s.VIN) as liczba_pojazdow, sum(us.cenauslugi*us.liczbauslug) as kwota

FROM aso, serwis s, uslugiserwisowe us

WHERE aso.idaso = s.idaso and us.idserwisu = s.idserwisu and datediff(year, s.dataserwisu, today()) = 1

GROUP BY aso.idaso, aso.nazwaaso ORDER BY liczba_pojazdow desc

  1. Lista 10 najczęściej wymienianych części w ciągu ostatnich 2 latach (KodCzesci, Nazwa Czesci, liczba wymienionych części) z podziałem na sieciASO, listę uporządkować malejąco wg liczby wymienionych części.

SELECT top 10 c.kodczesci, c.nazwaczesci, count(wc.kodczesci) as liczba, sa.idsaso

FROM czesc c, wymianaczesci wc, serwis s, aso, siecaso sa

WHERE c.kodczesci = wc.kodczesci and wc.idserwisu = s.idserwisu and datediff(year, s.dataserwisu, today()) < 2 and s.idaso = aso.idaso and sa.idsaso = aso.idsaso

GROUP BY c.kodczesci, c.nazwaczesci, sa.idsaso

ORDER BY liczba desc

  1. Lista modeli (IdModelu, NazwaModelu, IdMarki, NazwaMarki) wraz z liczbą wykonanych serwisów w ciągu ostatnich 3 lat (listę uporządkować wg liczby wykonanych serwisów malejąco).

SELECT m.idmodelu, m.nazwamodelu, ma.idmarki, ma.nazwamarki, count(s.idserwisu) liczba_serwisow

FROM model m, marka ma, pojazd p, serwis s

WHERE m.idmarki = ma.idmarki and p.idmarki =m.idmarki and p.idmodelu = m.idmodelu and p.vin = s.vin and datediff(year, s.dataserwisu, today()) < 3

GROUP BY m.idmodelu, m.nazwamodelu, ma.idmarki, ma.nazwamarki ORDER BY liczba_serwisow desc

  1. Lista 5 najbardziej dochodowych rodzajów serwisu (nazwaRodzajuSerwisu, łączna kwota), których łączny koszt wyniósł ponad 100 tys. zł w ciągu ostatnich 720 dni.

SELECT rs.nazwarodzajuserwisu, sum(wc.cenaczesci*wc.liczbaczesci)+sum(us.cenauslugi*us.liczbauslug) kwota

FROM rodzajserwisu rs, serwis s, wymianaczesci wc, uslugiserwisowe us

WHERE rs.idrs = s.idrs and s.idserwisu = wc.idserwisu and s.idserwisu = us.idserwisu and datediff(day, s.dataserwisu, today())<720

GROUP BY rs.nazwarodzajuserwisu

HAVING kwota > 100000

  1. (Dodatkowe)Lista ASO (idASO, NazwaASO), liczba wymienionych części, liczba usług serwisowych, całkowity koszt serwisów (koszt wymiany części oraz koszt usług serwisowych), ale tylko tych ASO, które są Autoryzowanymi ASO dla Marki OPEL.

SELECT idaso , nazwaaso, l_serwisow, l_czesci , cena_czesci+us_cena, cena_czesci, us_cena FROM(

SELECT aso.idaso, aso.nazwaaso, count(wc.idserwisu) l_czesci, sum(wc.cenaczesci*wc.liczbaczesci) as cena_czesci,

(SELECT count(us.idserwisu) as us_ser

FROM serwis s , uslugiserwisowe us, aso aso1, autoryzowaneaso aa, marka m

WHERE aso1.idaso = aa.idaso and aa.idmarki = m.idmarki and m.nazwamarki = 'opel' and aso1.idaso = s.idaso and us.idserwisu = s.idserwisu and aso1.idaso = aso.idaso ) l_serwisow

,

(SELECT sum(us.cenauslugi) as us_cena

FROM serwis s , uslugiserwisowe us, aso aso1, autoryzowaneaso aa, marka m

WHERE aso1.idaso = aa.idaso and aa.idmarki = m.idmarki and m.nazwamarki = 'opel' and aso1.idaso = s.idaso and us.idserwisu = s.idserwisu and aso1.idaso = aso.idaso ) us_cena

FROM aso, autoryzowaneaso aa, marka m, wymianaczesci wc, serwis s

WHERE aso.idaso = aa.idaso and aa. idmarki = m.idmarki and m.nazwamarki = 'opel' and aso.idaso = s.idaso and s.idserwisu = wc.idserwisu

GROUP BY aso.idaso, aso.nazwaaso ) zap1



Wyszukiwarka

Podobne podstrony:
bdsciaga, WAT, semestr III, Bazy danych
bdsprawko, WAT, semestr III, Bazy danych
pytania, WAT, semestr III, Bazy danych
caerm, WAT, semestr III, Bazy danych
5 Zadania PL-SQL1a, WAT, semestr III, Bazy danych
bd wyklad1, WAT, semestr III, Bazy danych
sprawko BD, WAT, semestr III, Bazy danych
5 2 PLSQL-Basic, WAT, semestr III, Bazy danych
sprawozdanie SKLEP SPOŻYWCZY Rafał Sylwestrzuk(1), WAT, semestr III, Bazy danych
wyrazenia, WAT, semestr III, Bazy danych
5 1 TRG, WAT, semestr III, Bazy danych
bdsprawko(2), WAT, semestr III, Bazy danych
moja, WAT, semestr III, Bazy danych
sciaga bazd danych, WAT, semestr III, Bazy danych

więcej podobnych podstron