Dzisiejsze laboratorium kontynuuje przykład z lab. 4. Przykład wiodący jest ten sam: opis przykładu, pliki do przykładu.
Problemy do rozważenia i rozwiązania są zawarte w kolejnych małych plikach, podobnie jak w poprzednim tygodniu. Celem jest zapoznanie się z przykładami prostych zapytań SQL i przygotowaniem analogicznych zapytań.
Pewnym problemem jest format daty, domyślnie obcy nam format amerykański. Proszę zacząć od analizy następujących przykładów: SHOW DATESTYLE; SET DATESTYLE oraz bardziej skomplikowany SET DATESTYLE.
SET client_encoding='utf-8';
-- 3. Sprawdź format przechowywania daty
SHOW DATESTYLE;
-- 4. Zmień na format 'Postgres, European': 01-12-1998
SET DATESTYLE TO 'Postgres, European';
-- 4. Zmie& na format 'Postgres, European': 01-12-1998
SET DATESTYLE TO 'German, European';
SELECT CAST('2008-02-12' AS DATE) AS dzisiaj;
SELECT CAST('12/02/2008' AS DATE) AS dzisiaj;
SELECT CAST('13/02/2008' AS DATE) AS dzisiaj;
SET DATESTYLE TO 'Postgres,US';
SET DATESTYLE TO 'Iso,US';
SELECT CAST('12-02-2008' AS DATE) AS dzisiaj;
SELECT CAST('13-02-2008' AS DATE) AS dzisiaj;
1. użycie warunków, IS NOT NULL, dopasowanie wzorców tekstowych, warunki dotyczące dat, funkcja agregująca count
-- wyświetl klientów z Sopotu,
-- towary z podaną ceną,
-- dane o donicach z podaną ceną,
-- liczbę zamówień złożonych w marcu 2008
SELECT * FROM klient WHERE miasto='Sopot'
;
SELECT * FROM towar WHERE cena IS NOT NULL
SELECT * FROM towar WHERE opis LIKE '%donica%' AND cena IS NOT NULL
SELECT count(*) FROM zamowienie WHERE data_zlozenia BETWEEN '2008/03/01' AND '2008/03/31'
-- zad P4: wyświetl w kolejnych zapytaniach:
-- dane klientów spoza Gdańska,
-- klientów bez telefonu,
-- klientów spoza Gdańska i bez telefonu,
-- dane o układankach,
-- zamówienia niezrealizowane (bez daty wysyłki),
-- zrealizowane w kwietniu 2008
2. grupowanie i warunki dotyczące grup, funkcje agregujące
-- sprawdź czy nazwiska powtarzają się, które?
SELECT nazwisko FROM klient
SELECT DISTINCT nazwisko FROM klient
SELECT nazwisko FROM klient GROUP BY nazwisko HAVING count (nazwisko) > 1
SELECT imie, nazwisko, miasto FROM klient
WHERE nazwisko IN (
GROUP BY nazwisko HAVING count (nazwisko) > 1
)
-- zad P5: sprawdź czy koszty/ceny towarów powtarzają się, które (opis
-- i koszt/cena)?
-- sprawdź numery zamówień klientów
SELECT imie, nazwisko, zamowienie.nr AS zamowienie_nr
FROM klient, zamowienie
WHERE klient.nr=zamowienie.klient_nr
ORDER BY nazwisko
-- INNER JOIN daje lepszą wydajność zapytania
-- zamień dalsze zapytania używając INNER JOIN
FROM klient INNER JOIN zamowienie
ON klient.nr=zamowienie.klient_nr
-- sprawdź jacy klienci w ogóle złożyli zamówienia
SELECT imie, nazwisko
-- zad P6: spowoduj, by nie wyświetlały się podwójnie nazwiska, użyj INNER JOIN
-- znajdź klientów którzy nie złożyli żadnych zamówień
-- (używając wcześniejszego obliczenia)
SELECT imie, nazwisko FROM klient
WHERE klient.nr NOT IN (
SELECT klient.nr
FROM klient INNER JOIN zamowienie ON klient.nr=zamowienie.klient_nr
-- zad P7: sprawdż,
-- czy istnieją zamówienia nie mające żadnych pozycji,
-- czy istnieją towary niezamawiane,
-- czy istnieją towary z nieokreślonym kodem kreskowym
-- sprawdź jacy klienci cokolwiek zamówili
FROM klient, zamowienie, pozycja
WHERE klient.nr=zamowienie.klient_nr AND pozycja.zamowienie_nr=zamowienie.nr
GROUP BY imie, nazwisko
-- wypisz zamówienia klientów o więcej niż jednej pozycji
SELECT imie, nazwisko, zamowienie.nr as zamowienie_nr, count(*) as "ile pozycji"
FROM (( klient INNER JOIN zamowienie
ON klient.nr = zamowienie.klient_nr
) INNER JOIN pozycja
ON zamowienie.nr = pozycja.zamowienie_nr
GROUP BY imie, nazwisko, zamowienie.nr
HAVING count(*)>1
-- sprawdź jacy klienci nie zamówili niczego
WHERE NOT EXISTS (
SELECT 1
FROM zamowienie INNER JOIN pozycja
AND pozycja.zamowienie_nr=zamowienie.nr
-- zad P8: użyj INNER JOIN szukając klientów, którzy złożyli zamówienia,
-- znajdż klientów, którzy złożyli puste zamówienia
6. wielokrotne złączenia, bardziej skomplikowane obliczenia
/* z tabel klientów, zamówienień i ich pozycji oblicz zysk
na poszczególnych towarach dla poszczególnych klientów
*/
SELECT imie, nazwisko, opis, ilosc,
ilosc * (cena - koszt) AS zysk
FROM (( klient
INNER JOIN zamowienie
) INNER JOIN towar
ON pozycja.towar_nr = towar.nr
-- zad P9: uporządkuj powyższy wydruk grupując te same towary
-- zamawiane przez tych samych klientów, odrzuć wiersze z
-- nieokreślonymi danymi
-- zad P10: z tabel klientów i zamówień oblicz minimalny, maksymalny i
-- średni czas oczekiwania przez poszczególnych klientów (zamówienia
-- niezrealizowane nie liczą się do średniej)
-- sprawdź jacy klienci złożyli zamówienia na układanki
SELECT DISTINCT imie, nazwisko, opis
FROM ( ( klient INNER JOIN zamowienie
WHERE towar.opis LIKE 'układanka%'
-- zad P11: sprawdż zamówienia na chusteczki higieniczne, kto
-- zamawiał, ile, jaki jest średni czas oczekiwania na realizację
-- zamówienie na chusteczki
-- wypisz liczbę pozycji zamówionych towarów przez poszczególnych klientów
SELECT imie, nazwisko, count(*) AS pozycji
FROM ( klient INNER JOIN zamowienie
/* zmodyfikuj poprzednie zapytanie tak by pokazywało
liczbę różnych zamówionych towarów, a nie pozycji
SELECT imie, nazwisko, count(*) AS towarów FROM (
GROUP BY klient.nr, imie, nazwisko, pozycja.towar_nr
) AS foo
/* wypisz liczbę różnych pozycji zamówionych towarów oraz wartości
zamówień i osiągniętego zysku w rozbiciu na klientów
SELECT imie, nazwisko, count(*) AS pozycji,
sum(ilosc * cena) AS suma,
sum(ilosc * (cena - koszt)) AS zysk
FROM ( ( ( klient
INNER JOIN pozycja
INNER JOIN towar
GROUP BY klient.nr, imie, nazwisko
-- zad P12: zmodyfikuj podobnie zapytanie tak, by dotyczyło towarów
-- zamiast pozycji
-- wymień pary zamówień pochodzących od tego samego klienta
SELECT Z1.nr AS zam1, Z2.nr AS zam2, Z2.klient_nr
FROM zamowienie AS Z1, zamowienie Z2
WHERE Z1.klient_nr=Z2.klient_nr
AND z1.nr<z2.nr;
-- ????
beziak