BAZY DANYCH: JĘZYK SQL – WYSZUKIWANIE
Mamy już bazę, wprowadzone przykładowe dane. Dziś spróbujemy sobie te dane poprzeglądać. Odnaleźć informacje po zadanych kryteriach.
- WHERE
- GROUP BY
- HAVING
- ORDER BY
- AS
X
SELECT * FROM pasazer; +----+----------+-----------+--------------+-------------+----------------------------------+----------+ | id | imie | nazwisko | pesel | telefon | mail | adres_id | +----+----------+-----------+--------------+-------------+----------------------------------+----------+ | 1 | Jan | Kowalski | 66110012121 | 66677712345 | jank@ilovetechnikinformatyk.pl | 1 | | 2 | Jan | Nowak | 86110045455 | 12312312312 | jann@ilovetechnikinformatyk.pl | 2 | | 3 | Tadeusz | Nowak | 46120023232 | 45678901332 | tadzio@ilovetechnikinformatyk.pl | 3 | | 4 | Zdzisław | Konopka | 7602334213 | 3424325434 | zdzichu@ilovethissite.pl | 1 | | 5 | Wiesława | Zzagrobna | 201005123456 | 123456123 | podziemie@thissiteisamazing.pl | 1 | +----+----------+-----------+--------------+-------------+----------------------------------+----------+
X
SELECT * FROM pasazer WHERE adres_id = 1; +----+----------+-----------+--------------+-------------+--------------------------------+----------+ | id | imie | nazwisko | pesel | telefon | mail | adres_id | +----+----------+-----------+--------------+-------------+--------------------------------+----------+ | 1 | Jan | Kowalski | 66110012121 | 66677712345 | jank@ilovetechnikinformatyk.pl | 1 | | 4 | Zdzisław | Konopka | 7602334213 | 3424325434 | zdzichu@ilovethissite.pl | 1 | | 5 | Wiesława | Zzagrobna | 201005123456 | 123456123 | podziemie@thissiteisamazing.pl | 1 | +----+----------+-----------+--------------+-------------+--------------------------------+----------+
X
SELECT * FROM pasazer ORDER by id DESC LIMIT 1; +----+----------+-----------+--------------+-----------+--------------------------------+----------+ | id | imie | nazwisko | pesel | telefon | mail | adres_id | +----+----------+-----------+--------------+-----------+--------------------------------+----------+ | 5 | Wiesława | Zzagrobna | 201005123456 | 123456123 | podziemie@thissiteisamazing.pl | 1 | +----+----------+-----------+--------------+-----------+--------------------------------+----------+ SELECT * FROM pasazer ORDER by id ASC LIMIT 1; +----+------+----------+-------------+-------------+--------------------------------+----------+ | id | imie | nazwisko | pesel | telefon | mail | adres_id | +----+------+----------+-------------+-------------+--------------------------------+----------+ | 1 | Jan | Kowalski | 66110012121 | 66677712345 | jank@ilovetechnikinformatyk.pl | 1 | +----+------+----------+-------------+-------------+--------------------------------+----------+
X
SELECT * FROM pasazer WHERE imie LIKE 'Jan'; +----+------+----------+-------------+-------------+--------------------------------+----------+ | id | imie | nazwisko | pesel | telefon | mail | adres_id | +----+------+----------+-------------+-------------+--------------------------------+----------+ | 1 | Jan | Kowalski | 66110012121 | 66677712345 | jank@ilovetechnikinformatyk.pl | 1 | | 2 | Jan | Nowak | 86110045455 | 12312312312 | jann@ilovetechnikinformatyk.pl | 2 | +----+------+----------+-------------+-------------+--------------------------------+----------+ SELECT * FROM pasazer WHERE imie LIKE 'JAN'; +----+------+----------+-------------+-------------+--------------------------------+----------+ | id | imie | nazwisko | pesel | telefon | mail | adres_id | +----+------+----------+-------------+-------------+--------------------------------+----------+ | 1 | Jan | Kowalski | 66110012121 | 66677712345 | jank@ilovetechnikinformatyk.pl | 1 | | 2 | Jan | Nowak | 86110045455 | 12312312312 | jann@ilovetechnikinformatyk.pl | 2 | +----+------+----------+-------------+-------------+--------------------------------+----------+
X
SELECT * FROM pasazer WHERE id >=3; +----+----------+-----------+--------------+-------------+----------------------------------+----------+ | id | imie | nazwisko | pesel | telefon | mail | adres_id | +----+----------+-----------+--------------+-------------+----------------------------------+----------+ | 3 | Tadeusz | Nowak | 46120023232 | 45678901332 | tadzio@ilovetechnikinformatyk.pl | 3 | | 4 | Zdzisław | Konopka | 7602334213 | 3424325434 | zdzichu@ilovethissite.pl | 1 | | 5 | Wiesława | Zzagrobna | 201005123456 | 123456123 | podziemie@thissiteisamazing.pl | 1 | +----+----------+-----------+--------------+-------------+----------------------------------+----------+
X
SELECT * FROM pasazer WHERE id BETWEEN 2 AND 4; +----+----------+----------+-------------+-------------+----------------------------------+----------+ | id | imie | nazwisko | pesel | telefon | mail | adres_id | +----+----------+----------+-------------+-------------+----------------------------------+----------+ | 2 | Jan | Nowak | 86110045455 | 12312312312 | jann@ilovetechnikinformatyk.pl | 2 | | 3 | Tadeusz | Nowak | 46120023232 | 45678901332 | tadzio@ilovetechnikinformatyk.pl | 3 | | 4 | Zdzisław | Konopka | 7602334213 | 3424325434 | zdzichu@ilovethissite.pl | 1 | +----+----------+----------+-------------+-------------+----------------------------------+----------+
X
SELECT * FROM pasazer WHERE id <> 3; +----+----------+-----------+--------------+-------------+--------------------------------+----------+ | id | imie | nazwisko | pesel | telefon | mail | adres_id | +----+----------+-----------+--------------+-------------+--------------------------------+----------+ | 1 | Jan | Kowalski | 66110012121 | 66677712345 | jank@ilovetechnikinformatyk.pl | 1 | | 2 | Jan | Nowak | 86110045455 | 12312312312 | jann@ilovetechnikinformatyk.pl | 2 | | 4 | Zdzisław | Konopka | 7602334213 | 3424325434 | zdzichu@ilovethissite.pl | 1 | | 5 | Wiesława | Zzagrobna | 201005123456 | 123456123 | podziemie@thissiteisamazing.pl | 1 | +----+----------+-----------+--------------+-------------+--------------------------------+----------+
X
SELECT * FROM pasazer WHERE id IN (1,2,5); +----+----------+-----------+--------------+-------------+--------------------------------+----------+ | id | imie | nazwisko | pesel | telefon | mail | adres_id | +----+----------+-----------+--------------+-------------+--------------------------------+----------+ | 1 | Jan | Kowalski | 66110012121 | 66677712345 | jank@ilovetechnikinformatyk.pl | 1 | | 2 | Jan | Nowak | 86110045455 | 12312312312 | jann@ilovetechnikinformatyk.pl | 2 | | 5 | Wiesława | Zzagrobna | 201005123456 | 123456123 | podziemie@thissiteisamazing.pl | 1 | +----+----------+-----------+--------------+-------------+--------------------------------+----------+
X
SELECT * FROM pasazer ORDER by pesel; +----+----------+-----------+--------------+-------------+----------------------------------+----------+ | id | imie | nazwisko | pesel | telefon | mail | adres_id | +----+----------+-----------+--------------+-------------+----------------------------------+----------+ | 5 | Wiesława | Zzagrobna | 201005123456 | 123456123 | podziemie@thissiteisamazing.pl | 1 | | 3 | Tadeusz | Nowak | 46120023232 | 45678901332 | tadzio@ilovetechnikinformatyk.pl | 3 | | 1 | Jan | Kowalski | 66110012121 | 66677712345 | jank@ilovetechnikinformatyk.pl | 1 | | 4 | Zdzisław | Konopka | 7602334213 | 3424325434 | zdzichu@ilovethissite.pl | 1 | | 2 | Jan | Nowak | 86110045455 | 12312312312 | jann@ilovetechnikinformatyk.pl | 2 | +----+----------+-----------+--------------+-------------+----------------------------------+----------+
X
SELECT * FROM pasazer WHERE telefon LIKE '1%'; +----+----------+-----------+--------------+-------------+--------------------------------+----------+ | id | imie | nazwisko | pesel | telefon | mail | adres_id | +----+----------+-----------+--------------+-------------+--------------------------------+----------+ | 2 | Jan | Nowak | 86110045455 | 12312312312 | jann@ilovetechnikinformatyk.pl | 2 | | 5 | Wiesława | Zzagrobna | 201005123456 | 123456123 | podziemie@thissiteisamazing.pl | 1 | +----+----------+-----------+--------------+-------------+--------------------------------+----------+
X
SELECT * FROM pasazer WHERE pesel LIKE '_6%'; +----+----------+----------+-------------+-------------+----------------------------------+----------+ | id | imie | nazwisko | pesel | telefon | mail | adres_id | +----+----------+----------+-------------+-------------+----------------------------------+----------+ | 1 | Jan | Kowalski | 66110012121 | 66677712345 | jank@ilovetechnikinformatyk.pl | 1 | | 2 | Jan | Nowak | 86110045455 | 12312312312 | jann@ilovetechnikinformatyk.pl | 2 | | 3 | Tadeusz | Nowak | 46120023232 | 45678901332 | tadzio@ilovetechnikinformatyk.pl | 3 | | 4 | Zdzisław | Konopka | 7602334213 | 3424325434 | zdzichu@ilovethissite.pl | 1 | +----+----------+----------+-------------+-------------+----------------------------------+----------+
X
SELECT * FROM pasazer WHERE imie LIKE '%a'; +----+----------+-----------+--------------+-----------+--------------------------------+----------+ | id | imie | nazwisko | pesel | telefon | mail | adres_id | +----+----------+-----------+--------------+-----------+--------------------------------+----------+ | 5 | Wiesława | Zzagrobna | 201005123456 | 123456123 | podziemie@thissiteisamazing.pl | 1 | +----+----------+-----------+--------------+-----------+--------------------------------+----------+
X
SELECT count(*) as ile FROM pasazer WHERE nazwisko LIKE '%a'; +-----+ | ile | +-----+ | 2 | +-----+
X
SELECT DISTINCT imie FROM pasazer; +----------+ | imie | +----------+ | Jan | | Tadeusz | | Zdzisław | | Wiesława | +----------+
X
SELECT * FROM rezerwacja; +----+-----------------+------------------------------+------------+----------------+----------------------+ | id | numer_siedzenia | bagaz | pasazer_id | lot_numer_lotu | status_rezerwacji_id | +----+-----------------+------------------------------+------------+----------------+----------------------+ | 1 | 13 | Owca | 5 | 1 | 4 | | 2 | 123 | Cztery ogórki | 3 | 2 | 2 | | 3 | 911 | Materiały wybuchowe + granat | 2 | 1 | 3 | +----+-----------------+------------------------------+------------+----------------+----------------------+ SELECT * FROM pasazer; +----+----------+-----------+--------------+-------------+----------------------------------+----------+ | id | imie | nazwisko | pesel | telefon | mail | adres_id | +----+----------+-----------+--------------+-------------+----------------------------------+----------+ | 1 | Jan | Kowalski | 66110012121 | 66677712345 | jank@ilovetechnikinformatyk.pl | 1 | | 2 | Jan | Nowak | 86110045455 | 12312312312 | jann@ilovetechnikinformatyk.pl | 2 | | 3 | Tadeusz | Nowak | 46120023232 | 45678901332 | tadzio@ilovetechnikinformatyk.pl | 3 | | 4 | Zdzisław | Konopka | 7602334213 | 3424325434 | zdzichu@ilovethissite.pl | 1 | | 5 | Wiesława | Zzagrobna | 201005123456 | 123456123 | podziemie@thissiteisamazing.pl | 1 | +----+----------+-----------+--------------+-------------+----------------------------------+----------+ SELECT * FROM adres; +----+-------------+----------+-------------+-----------------+------------+--------------+ | id | wojewodztwo | powiat | miejscowosc | ulica | numer_domu | numer_lokalu | +----+-------------+----------+-------------+-----------------+------------+--------------+ | 1 | pomorskie | lęborski | Lębork | Marcinkowskiego | 1 | NULL | | 2 | pomorskie | lęborski | Lębork | Warszawska | 112 | NULL | | 3 | pomorskie | lęborski | Lębork | Pionierów | 997 | 999 | +----+-------------+----------+-------------+-----------------+------------+--------------+
X
SELECT nazwisko FROM pasazer WHERE adres_id IN( SELECT id FROM adres WHERE ulica LIKE 'Marcinkowskiego' ); +-----------+ | nazwisko | +-----------+ | Kowalski | | Konopka | | Zzagrobna | +-----------+
X
SELECT bagaz FROM rezerwacja WHERE pasazer_id IN ( SELECT id FROM pasazer WHERE adres_id IN ( SELECT id FROM adres WHERE ulica LIKE 'Warszawska' ) ); +------------------------------+ | bagaz | +------------------------------+ | Materiały wybuchowe + granat | +------------------------------+
X
SELECT * FROM samolot; +----+---------------+------------+ | id | producent | typ | +----+---------------+------------+ | 1 | Ąty | Ęście | | 2 | Boink | Drimlajner | | 3 | Garaż Konrada | Awionurek | | 4 | MakroHard | Doors | +----+---------------+------------+ SELECT * FROM lot; +------------+---------------------+------------+ | numer_lotu | data_wylotu | samolot_id | +------------+---------------------+------------+ | 1 | 2027-12-27 20:20:20 | 3 | | 2 | 2016-10-04 00:00:00 | 3 | | 3 | 2012-12-12 11:11:00 | 4 | | 4 | 2019-05-24 23:03:00 | 2 | +------------+---------------------+------------+
X
SELECT numer_lotu FROM lot WHERE data_wylotu > '2015-01-21 22:13:15'; +------------+ | numer_lotu | +------------+ | 1 | | 2 | | 4 | +------------+
X
SELECT typ FROM samolot where id IN ( SELECT samolot_id FROM lot WHERE data_wylotu BETWEEN '2014-01-30 14:15:55' AND '2018-09-29 10:15:55' ); +-----------+ | typ | +-----------+ | Awionurek | +-----------+
X
SELECT pasazer.imie AS Imie, pasazer.nazwisko AS Nazwisko,
adres.ulica AS Ulica
FROM pasazer
JOIN adres
ON pasazer.adres_id = adres.id;
+----------+-----------+-----------------+
| Imie | Nazwisko | Ulica |
+----------+-----------+-----------------+
| Jan | Kowalski | Marcinkowskiego |
| Zdzisław | Konopka | Marcinkowskiego |
| Wiesława | Zzagrobna | Marcinkowskiego |
| Jan | Nowak | Warszawska |
| Tadeusz | Nowak | Pionierów |
+----------+-----------+-----------------+
X
select imie, nazwisko, miejscowosc, ulica, numer_domu FROM pasazer INNER JOIN adres ON pasazer.adres_id = adres.id; +----------+-----------+-------------+-----------------+------------+ | imie | nazwisko | miejscowosc | ulica | numer_domu | +----------+-----------+-------------+-----------------+------------+ | Jan | Kowalski | Lębork | Marcinkowskiego | 1 | | Zdzisław | Konopka | Lębork | Marcinkowskiego | 1 | | Wiesława | Zzagrobna | Lębork | Marcinkowskiego | 1 | | Jan | Nowak | Lębork | Warszawska | 112 | | Tadeusz | Nowak | Lębork | Pionierów | 997 | +----------+-----------+-------------+-----------------+------------+
X
SELECT * FROM pasazer WHERE imie = 'Jan' UNION Select * FROM pasazer WHERE imie = 'Tadeusz'; +----+---------+----------+-------------+-------------+----------------------------------+----------+ | id | imie | nazwisko | pesel | telefon | mail | adres_id | +----+---------+----------+-------------+-------------+----------------------------------+----------+ | 1 | Jan | Kowalski | 66110012121 | 66677712345 | jank@ilovetechnikinformatyk.pl | 1 | | 2 | Jan | Nowak | 86110045455 | 12312312312 | jann@ilovetechnikinformatyk.pl | 2 | | 3 | Tadeusz | Nowak | 46120023232 | 45678901332 | tadzio@ilovetechnikinformatyk.pl | 3 | +----+---------+----------+-------------+-------------+----------------------------------+----------+
SELECT * FROM pasazer WHERE imie = 'Jan' UNION Select * FROM pasazer WHERE nazwisko = 'Nowak'; +----+---------+----------+-------------+-------------+----------------------------------+----------+ | id | imie | nazwisko | pesel | telefon | mail | adres_id | +----+---------+----------+-------------+-------------+----------------------------------+----------+ | 1 | Jan | Kowalski | 66110012121 | 66677712345 | jank@ilovetechnikinformatyk.pl | 1 | | 2 | Jan | Nowak | 86110045455 | 12312312312 | jann@ilovetechnikinformatyk.pl | 2 | | 3 | Tadeusz | Nowak | 46120023232 | 45678901332 | tadzio@ilovetechnikinformatyk.pl | 3 | +----+---------+----------+-------------+-------------+----------------------------------+----------+ SELECT * FROM pasazer WHERE imie = 'Jan' UNION ALL Select * FROM pasazer WHERE nazwisko = 'Nowak'; +----+---------+----------+-------------+-------------+----------------------------------+----------+ | id | imie | nazwisko | pesel | telefon | mail | adres_id | +----+---------+----------+-------------+-------------+----------------------------------+----------+ | 1 | Jan | Kowalski | 66110012121 | 66677712345 | jank@ilovetechnikinformatyk.pl | 1 | | 2 | Jan | Nowak | 86110045455 | 12312312312 | jann@ilovetechnikinformatyk.pl | 2 | | 2 | Jan | Nowak | 86110045455 | 12312312312 | jann@ilovetechnikinformatyk.pl | 2 | | 3 | Tadeusz | Nowak | 46120023232 | 45678901332 | tadzio@ilovetechnikinformatyk.pl | 3 | +----+---------+----------+-------------+-------------+----------------------------------+----------+
X
SELECT * FROM pasazer WHERE imie = 'Jan' AND id NOT IN ( Select id FROM pasazer WHERE nazwisko = 'Nowak'); +----+------+----------+-------------+-------------+--------------------------------+----------+ | id | imie | nazwisko | pesel | telefon | mail | adres_id | +----+------+----------+-------------+-------------+--------------------------------+----------+ | 1 | Jan | Kowalski | 66110012121 | 66677712345 | jank@ilovetechnikinformatyk.pl | 1 | +----+------+----------+-------------+-------------+--------------------------------+----------+
Ćwiczenia:
- Wypisz imiona pasażerów o nazwisku Nowak.
- Z jakich miejscowości są osoby z pierwszego ćwiczenia?
- Wymień imiona i nazwiska pasażerów, w których bagażu znalazł się granat.
- Wypisz numery PESEL osób, których numer domu > 100.
- Z jakiego województwa jest osoba, która jako ostatnia zarezerwowała lot?
- Podaj datę wylotu Tadeusza.
- Jaki jest producent samolotu, którym poleci Jan Nowak?
- Podaj pełen adres osoby, która poleci samolotem producenta “MakroHard”.
- Podaj numer telefonu do osoby, której status rezerwacji to: nieopłacona.
- Wypisz dane pasażerów, których pesel zawiera cyfry 5 i 6.
