Bazy danych, zajęcia nr 1
słowa kluczowe:
SELECT, ||, order by ASC/DESC, distinct, działania (+, -, *, /), nazywanie kolumn,
wydobywanie części daty, round(), lower(), upper(), ltrim(), rtrim(), trim()
1. Wypisz zawartość tabeli Puby.
select * from Puby;
2. Wypisz nazwę piwa i jego cenę hurtową (t. Piwa)
select nazwa, cena_hurtowa from Piwa;
3. Wypisz imiona i nazwiska (w tej kolejności) piwoszy z tabeli Osoby. Uporządkuj rekordy
w kolejności alfabetycznej wg nazwiska.
select imie, nazwisko from Osoby order by nazwisko;
4. Jw. tylko nazwiska wyświetl wielkimi literami.
select imie, upper(nazwisko) from Osoby order by nazwisko;
5. Wypisz identyfikatory ulubionych piw piwoszy bez powtórzeń (t. Osoby)
select distinct id_ulubionego from osoby;
6. Wypisz nazwę piwa i cenę hurtową zaokrąglając do jednego miejsca po przecinku (t. Piwa)
select nazwa, round(cena_hurtowa,1) from Piwa;
7. Wypisz imiona, nazwiska i datę urodzenia piwoszy. Imiona i nazwiska (oddzielone spacją)
wypisz w jednej kolumnie o nazwie "Piwosz". Rekordy uporządkuj od osoby urodzonej
najpóźniej (tzn. najmłodszej) do osoby urodzonej najwcześniej.
select imie||' `||nazwisko as Piwosz, data_urodz from osoby order by data_urodz desc;
8. J.w. tylko zamiast pełnej daty urodzenia wyświetl jedynie rok. Rekordy uporządkuj wg
roku rosnąco i wg kolumny "Piwosz" malejąco.
-- sqlite: http://www.sqlite.org/lang_datefunc.html - funkcja strftime("%Y", ...)
-- MySQL i MS SQL Server: funkcja year(...) (np. year('20098-01-10') daje 2009)
select imie||' `||nazwisko as Piwosz, strftime(`%Y',data_urodz) from osoby order by data_urodz;
9. Wypisz nazwy pubów oraz długość ich istnienia (zakładamy, Ŝe aktualny rok to 2009).
select nazwa, 2009-rok_otwarcia from Puby;
10. J.w. tylko długość istnienia pubów podaj w miesiącach (zakładamy, Ŝe kaŜdy rok istnienia to 12 m-cy).
select nazwa, (2009-rok_otwarcia)*12 from Puby;
W domu:
11. Wypisz nazwy piw, cenę hurtową w złotówkach oraz cenę hurtową w dolarach (przyjmij kurs dolara 2 zł, wynik zaokrąglij do dwóch miejsc po przecinku). Przy wartościach w złotówkach powinien znajdować się napis "zl", natomiast przy dolarach "USD".
select nazwa, cena_hurtowa||' `||'zl', round(cena_hurtowa/2,2)||' `||'USD' from Piwa;
12. Zmodyfikuj zapytanie z punktu 9 tak, aby jego wynik był poprawny niezależnie od roku,
w którym zostanie wywołane. W tym celu zapoznaj się z działaniem funkcji date() (sqlite:
http://www.sqlite.org/lang_datefunc.html) i sprawdź wynik następującego zapytania:
SELECT date();
select nazwa, date()-rok_otwarcia from Puby;
UWAGA! W MySQL tę samą role spełnia funkcja curdate(), natomiast w MS SQL Server
funkcja getdate().
13. Wypisz wszystkie RÓśNE imiona z tabeli Osoby. Wszystkie wielkie litery zamień na
małe. Wynik uporządkuj w odwrotnej kolejności alfabetycznej.
select distinct lower(imie) from Osoby order by imie desc;
14. Sprawdź działanie funkcji ltrim(), rtrim(), trim(), wykonując kolejno zapytania (przed oraz
po wyrazie 'politechnika' jest pięć spacji):
SELECT ' Politechnika ' as 'bez trim';
SELECT ltrim(' Politechnika ') as 'z ltrim';
SELECT rtrim(' Politechnika ') as 'z rtrim';
SELECT trim(' Politechnika ') as 'z trim'; -- nie występuje w MS SQL Server2005
Słowa kluczowe:
WHERE, <, =, >, <=, >=, BETWEEN, IS (NOT) LIKE, IS (NOT) NULL, (NOT) IN
wypisz wszystkie informacje o osobie o identyfikatorze 10 (tab. Osoby).
select * from osoby where id=10;
Wypisz wszystkich piwoszy o imieniu Jan (trzy sposoby: ='Jan', LIKE 'JAN1, LIKE'jan')
select * from osoby where imie like 'jan';
Wypisz wszystkich piwoszy o imieniu Jan lub Adam (dwa sposoby: OR, IN)
select * from osoby where imie like 'jan' or imie like 'adam';
wypisz nazwy piw, których cena hurtowa jest większa od 2 zł
select nazwa from Piwa where cena_hurtowa>2;
Wypisz piwa, których cena zawiera się między 2 a 2,3 zł (dwa sposoby: OR, BETWEEN)
select nazwa from Piwa where cena_hurtowa between 2 and 2.3;
wypisz piwa, których nazwy zawierają słowo 'Mocne'
select nazwa from Piwa where nazwa like '%mocne%';
wypisz piwa, których nazwy zaczynają się na literę 'T' i których cena jest niższa od 2 zł
select nazwa from Piwa where nazwa like 'T%' and cena_hurtowa<2;
wypisz imiona i nazwiska osób, które nie mają ulubionego piwa
select imie, nazwisko from osoby where id_ulubionego is null;
zadania dodatkowe:
wypisz nazwy pubów, które istnieją przynajmniej 10 lat
select nazwa from puby where date()-rok_otwarcia>=10;
wypisz imiona, nazwiska oraz datę urodzenia wszystkich kobiet urodzonych w latach 80-tych
select * from osoby where imie like'%a' and data_urodz like '198%';
Wypisz imiona wszystkich mężczyzn, którzy nie mają ulubionego piwa
select * from osoby where id_ulubionego is null and imie not like '%a';
Słowa kluczowe:
COUNT, SUM, MAX, MIN, AVG
wyświetl liczbę wszystkich rekordów z tabeli Osoby,
select count(id) from osoby;
Wyświetl maksymalny rok otwarcia pubu.
select max(data_urodz) from puby;
wyświetl sumę ceny hurtowej wszystkich piw z browaru o identyfikatorze 2.
select sum(cena_hurtowa) from piwa where id_browaru=2;
Wyświetl średni wiek (aktualny rok - rok otwarcia) wszystkich pubów otwartych po 2000 roku.
select avg(date()-rok_otwarcia) from puby where rok_otwarcia>2000;
Zadania dodatkowe:
Wypisz maksymalną, minimalną i średnią cenę piwa oraz ilość piw.
select max(cena_hurtowa), min(cena_hurtowa), avg(cena_hurtowa), count(id) from piwa;
ile piw jest droższych od 2zł?
select count(id) from piwa where cena_hurtowa>2;
wyświetl liczbę wszystkich kobiet z tabeli osoby.
select count(imie) from osoby where imie like '%a';
wyświetl średni wiek wszystkich mężczyzn, którzy posiadają ulubioną markę piwa.
select avg(date()-data_urodz) from osoby where imie not like '%a' and id_ulubionego is not null;
słowa kluczowe:
GROUP BY, HAVING
Dla każdego browaru wypisz średnią cenę produkowanego piwa.
select id_browaru, avg(cena_hurtowa) from piwa group by id_browaru;
Dla każdego browaru wypisz ilość produkowanych marek piwa.
select id_browaru, count(nazwa) from piwa group by id_browaru;
ile każdy z browarów produkuje marek piwa tańszych niż 2zł?
select id_browaru, count(cena_hurtowa<2) from piwa group by id_browaru;
Wypisz Browary, które produkują co najmniej 7 marek piwa.
select id_browaru, count(id) from piwa group by id_browaru having count(id)>=7;
zadania dodatkowe:
wyświetl identyfikatory wszystkich ulubionych piw oraz liczbę osób, które uważają to piwo za najlepsze.
select id_ulubionego, count(imie) from osoby where id_ulubionego is not null group by id_ulubionego;
Dla każdego imienia z tabeli Osoby wypisz średni wiek ich posiadaczy.
select imie, avg(date()-data_urodz) from osoby group by imie;
SELECT IN SELECT
Wypisz nazwy piw, których ceny są wyższe od piwa Tyskie.
select nazwa from piwa where cena_hurtowa>(select cena_hurtowa from piwa where nazwa like 'tyskie');
Wypisz nazwę piwa o najniższej cenie.
select nazwa from piwa where cena_hurtowa=(select min(cena_hurtowa) from piwa);
Podaj liczbę piw o ceme powyżej średniej.
select count(nazwa) from piwa where cena_hurtowa>(select avg(cena_hurtowa) from piwa);
wypisz wszystkie piwa, których cena jest niższa od ceny piw zawierających w nazwie słowo 'Żywiec'.
select nazwa from piwa where cena_hurtowa<(select min(cena_hurtowa) from piwa where nazwa like '%zywiec%');
Zadania dodatkowe:
Wypisz nazwy wszystkich piw rozlewanych przez browar, który produkuje piwo Karmi.
select * from piwa where id_browaru=(select id_browaru from piwa where nazwa like 'karmi');
Wypisz imiona, nazwiska oraz datę urodzenia wszystkich osób, które urodziły się w tym samym roku, co osoba najmłodsza.
select imie, nazwisko, data_urodz from osoby where data_urodz=(min(date()-strftime('%Y',data_urodz));
zadanie domowe:
1. Wypisz nazwy wszystkich pubów zaczynających się na literę C i powstałych przed 2000 roki em.
select nazwa from puby where nazwa like 'c%' and rok_otwarcia<2000;
2. Wypisz nazwy wszystkich piw z browaru o identyfikatorze 1 i tańszych niż 1,8 zł lub z browaru o identyfikatorze 3 i droższych niż 2 zł.
select nazwa from piwa where id_browaru=1 and cena_hurtowa<1.8 or id_browaru=3 and cena_hurtowa<2;
3. Ile osób nie ma ulubionego piwa?
select count(imie) from osoby where id_ulubionego is null;
4. Dla każdego browaru wypisz średnią cenę produkowanego piwa korzystając z funkcji COUNT i SUM.
select id_browaru, avg(cena_hurtowa) from piwa group by id_browaru having avg(cena_hurtowa);
5. Wypi sz imię i nazwisko najstarszej osoby z tabel i Osoby
select imie, nazwisko from osoby where data_urodz=(select min(data_urodz) from osoby;
Słowa kluczowe:
(INNER/LEFT/RIGHT) JOIN, UNION, INTERSECT, EXCEPT
1. Wypisz imię i nazwisko piwoszy, którzy posiadają ulubione piwo, oraz nazwy ich ulubionych piw.
select nazwisko, imie, piwa.nazwa from osoby join piwa on (osoby.id_ulubionego=piwa.id) where id_ulubionego >0;
2. Wypisz nazwy piw i odpowiadające im nazwy browarów.
select piwa.nazwa, browary.nazwa from piwa join browary on piwa.id_browaru=browary.id;
3. Wypisz nazwy browarów i liczbę piw naleŜących do kaŜdego z nich.
(złączając kilka tabel tworzymy tak jakby jedną nową tabelę, na której moŜemy wykonywać
wszystkie dotychczas poznane operacje (np. grupowanie))
select browary.nazwa, count(browary.id) from browary join piwa on browary.id=piwa.id_browaru group by browary.id;
4. Wypisz imię i nazwisko piwoszy oraz nazwy i ceny hurtowe ich ulubionych piw
(uwzględnić takŜe tych piwoszy, którzy nie mają ulubionego piwa)
select imie, nazwisko, piwa.cena_hurtowa, piwa.nazwa from osoby join piwa on (osoby.id_ulubionego=piwa.id);
5. Wypisz osoby, których ulubionym piwem jest Tyskie.
select imie, nazwisko, piwa.nazwa from osoby join piwa on osoby.id_ulubionego=piwa.id where piwa.nazwa like 'tyskie';
6. Wypisz największą cenę hurtową piwa, które naleŜy do ulubionych piw.
select max(cena_hurtowa) from piwa join osoby on piwa.id=osoby.id_ulubionego where osoby.id_ulubionego not null and cena_hurtowa=(select max(cena_hurtowa)from piwa);
7. Wypisz nazwę piwa, którego cena hurtowa jest największa spośród wszystkich ulubionych piw.
select nazwa from piwa join osoby on piwa.id=osoby.id_ulubionego where osoby.id_ulubionego not null and cena_hurtowa=(select max(cena_hurtowa) from piwa);
8. Wypisz imiona, nazwiska osób, nazwy piw oraz to, czy dana osoba lubi czy nie lubi tego
Piwa (połącz tabele Osoby, Preferencje i Piwa)
select imie, nazwisko, piwa.nazwa, preferencje.status from osoby join preferencje on osoby.id=preferencje.id_osoby join piwa on preferencje.id_piwa=piwa.id;
9. Jw. ale wypisz tylko te piwa, które są lubiane.
select imie, nazwisko, piwa.nazwa, preferencje.status from osoby join preferencje on osoby.id=preferencje.id_osoby join piwa on preferencje.id_piwa=piwa.id where id_ulubionego not null;
10. J.w. ale dla kaŜdej osoby wypisz tylko liczbę piw, które lubi.
select imie, nazwisko, count(piwa.nazwa), preferencje.status from osoby join preferencje on osoby.id=preferencje.id_osoby join piwa on preferencje.id_piwa=piwa.id where status like 'lubi' group by nazwisko;
11. J.w. ale dla kaŜdej osoby wypisz liczbę piw, które lubi oraz liczbę piw, które nie lubi.
select imie, nazwisko, count(piwa.nazwa), preferencje.status from osoby join preferencje on osoby.id=preferencje.id_osoby join piwa onpreferencje.id_piwa=piwa.id group by osoby.id, preferencje.status;
12. Wypisz nazwy piw oraz liczbę osób, które to piwo lubią. Posortuj (malejąco) po liczbie lubiących dane piwo.
select piwa.nazwa, count(imie) from osoby join preferencje on osoby.id=preferencje.id_osoby join piwa on preferencje.id_piwa=piwa.id where status like 'lubi' group by piwa.nazwa order by count(imie) desc;
13. Wypisz nazwy piw oraz liczbę osób, które to piwo nie lubią. Posortuj (malejąco) po liczbie nie lubiących dane piwo.
select piwa.nazwa, count(imie) from osoby join preferencje on osoby.id=preferencje.id_osoby join piwa on preferencje.id_piwa=piwa.id where status like 'nie lubi' group by piwa.nazwa order by count(imie) desc;
14. UŜywając sumy relacji (UNION) wypisz w jednej tabeli nazwy piw oraz liczbę osób,
które dane piwo lubią oraz nie lubią (z zaznaczeniem, która liczba odpowiada lubiącym, która
nie lubiącym).
select imie, nazwisko, count(piwa.nazwa), preferencje.status from osoby join preferencje on osoby.id=preferencje.id_osoby join piwa on preferencje.id_piwa=piwa.id where status='nie lubi' group by osoby.id union select imie, nazwisko, count(piwa.nazwa), preferencje.status from osoby join preferencje on osoby.id=preferencje.id_osoby join piwa on preferencje.id_piwa=piwa.id where status='lubi' group by osoby.id;
---------------------------
Zadania domowe:
15. Wyświetl puby, w których bywa Jan Kos.
select id_pubu from osoby join bywalcy on osoby.id=bywalcy.id_osoby where imie like 'jan' and nazwisko like 'kos';
16. Wyświetl puby, w których bywa Anna Adamowicz.
select id_pubu from osoby join bywalcy on osoby.id=bywalcy.id_osoby where imie like 'anna' and nazwisko like 'adamowicz';
17. Za pomocą iloczynu zbiorów INTERSECT sprawdź, do jakich pubów powinien chodzić Jan Kos, Ŝeby spotkać Annę Adamowicz.
select id_pubu from osoby join bywalcy on osoby.id=bywalcy.id_osoby where imie like 'jan' and nazwisko like 'kos' intersect select id_pubu from osoby join bywalcy on osoby.id=bywalcy.id_osoby where imie like 'anna' and nazwisko like 'adamowicz';
18. Za pomocą odejmowania zbiorów EXCEPT sprawdź, do jakich pubów moŜe chodzić
Anna Adamowicz, Ŝeby nie spotkać Jana Kosa.
-- UWAGA, niektóre SZBD zamiast EXCEPT uŜywają słowa MINUS
-- Zamiast słów INTERSECT oraz MINUS moŜna zastosować podzapytania uŜywając słów
IN i NOT IN
select id_pubu from osoby join bywalcy on osoby.id=bywalcy.id_osoby where imie like 'jan' and nazwisko like 'kos' except select id_pubu from osoby join bywalcy on osoby.id=bywalcy.id_osoby where imie like 'anna' and nazwisko like 'adamowicz';
19. Wyświetl ilu piwoszy bywa w kaŜdym z barów.
select count(id_osoby), puby.nazwa from bywalcy join puby on bywalcy.id_pubu=puby.id group by puby.nazwa having count(id_osoby);
20. Wyświetl cenę piwa Tyskie w kaŜdym z barów (jeśli jest sprzedawane).
select piwa.nazwa, cena_w_pubie, puby.nazwa from piwa join asortyment on piwa.id=id_piwa join puby on asortyment.id_pubu=puby.id where piwa.nazwa like 'tyskie' group by puby.nazwa;
21. Wyświetl ceny wszystkich lanych piw sprzedawanych w pubach (wraz z informacją jakie
piwo i w którym pubie)
select piwa.nazwa, cena_w_pubie, puby.nazwa from piwa join asortyment on piwa.id=id_piwa join puby on asortyment.id_pubu=puby.id where asortyment.rodzaj like 'l';
Zapytania nr 4
1. Dla kaŜdego baru wyświetl browary, z których są sprzedawane w tym barze piwa.
select distinct p.nazwa, b.nazwa from puby p join asortyment a on p.id=a.id_pubu join piwa pi on a.id_piwa=pi.id join browary b on pi.id_browaru=b.id group by p.nazwa, b.nazwa;
2. W którym z pubów jest najwięcej gatunków piw?
select p.nazwa, count(distinct a.id_piwa) from puby p join asortyment a on p.id=a.id_pubu group by p.nazwa order by count(a.id_piwa) desc limit 1;
3. Wyświetl średnią marŜę w kaŜdym z pubów na piwa butelkowe
select round(avg(a.cena_w_pubie-p.cena_hurtowa),2), pu.nazwa from asortyment a join piwa p on a.id_piwa=p.id join puby pu on a.id_pubu=pu.id where a.rodzaj like 'b' group by pu.nazwa;
4. Wyświetl średnią cenę w pubach poszczególnych piw butelkowych
select round(avg(a.cena_w_pubie-p.cena_hurtowa),2), p.nazwa from asortyment a join piwa p on a.id_piwa=p.id join puby pu on a.id_pubu=pu.id where a.rodzaj like 'b' group by pu.nazwa;
5. Wyświetl ceny w pubach piw (butelkowych i lanych) Zubr lub Harnas
select a.cena_w_pubie from puby p join asortyment a on p.id=a.id_pubu join piwa pi on a.id_piwa=pi.id join browary b on pi.id_browaru=b.id where pi.nazwa like 'harnas' or pi.nazwa like 'zubr';