SPN - Labs

Baza danych PostgreSQL - DBaaS ElephantSQL

W ramach tego punktu utworzymy relacyjną bazę danych PostgreSQL w serwisie DBaaS ElephantSQL [4]. Zarządzanie bazą danych ( tworzenie struktur danych, wprowadzanie i modyfikacja danych ) możliwa jest z wykorzystaniem interfejsu serwisu ElephantSQL, terminala znakowego uruchomionego w systemie operacyjnym lub z wykorzystaniem dedykowanych aplikacji. W kolejnych punktach zostaną przedstawione dwie aplikacje: SQL Manager Lite for Postgresql i DBeaver.

Link do serwisu DBaaS ElephantSQL (https://www.elephantsql.com/)
Utworzenie instancji bazy danych, zapoznanie z parametrami uwierzytenienia do bazy danych
- utworzenie konta w serwisie Elephantsql
- utworzenie instancji bazy danych w planie "Tiny Turtle" (rys.2 i 3)
  
  Rys.2 Utworzenie instancji bazy danych, wybór planu
  
  Rys.3 Instancja bazy danych w serwisie ElephantSQL
- Zapoznanie z parametrami połączenia z utworzoną bazą danych (rys.4) ( zaznaczone na czerwono )
  - nazwa serwera na którym znajduje się baza danych - dumbo.db.elephantsql.com
  - nazwa użytkownika i również bazy danych - qxqeypvx
  - hasło dostępu do bazy danych - generowane losowo
  Rys.4 Parametry utworzonej instancji bazy danych w serwisie ElephantSQL

SQL Manager Lite for PostgreSQL

Link do pobrania programu SQL Manager Lite for PostgreSQL (https://www.sqlmanager.net/en/products/postgresql/manager)
Konfiguracja programu SQL Manager do połączenia z bazą danych w serwisie ElephantSQL. Na początek należy zarejestrować bazę danych utowrzoną w chmurze ElephantSQL w aplikacji. Należy wybrać przycisk Database a następnie opcję Register Database.. (rys.5 i 6). Wypełniamy pola zaznaczone na czerwono zgodnie z informacją pobraną z serwisu ElepantDB.

Rys.5. Konfiguracja połączenia z bazą danych w serwisie ElephantSQL

Rys.6. Konfiguracja połączenia z bazą danych w serwisie ElephantSQL
Na koniec sprawdzimy czy nasza baza jest dostępna w programie. Na zarejestrowanej bazie danych (lewy panel, rys.7) klikamy prawy klawisz myszki i wybieramy opcję Connect to Database. Poprawne połączenie z bazą danych zaowocuje drzewem obiektów dostępnych w bazie danych (zawartość czerwonej ramki).

Rys.7. Drzewo obiektów bazy danych w serwisie ElephantSQL

DBeaver

Link do pobrania programu DBeaver (https://dbeaver.io/files/dbeaver-ce-latest-x86_64-setup.exe)
Konfiguracja programu DBeaver do połączenia z bazą danych w serwisie ElephantSQL. Na początek należy zarejestrować bazę danych utworzoną w chmurze ElephantSQL w aplikacji. Należy wybrać przycisk Database a następnie wybrać typ bazy danych Postgresql ( rys.8) i wypełnić następujące pola formularza: Host, Database, Username i Password ( rys. 9). Dane pobieramy z informacją pobraną z serwisu ElephantSQL.

Rys.8. Dodanie bazy danych do narzędzia DBeaver

Rys.9. Konfiguracja połączenia z bazą danych w serwisie ElephantSQL
Na koniec sprawdzimy czy nasza baza jest dostępna w programie. Na zarejestrowanej bazie danych (lewy panel, rys10) klikamy i rozwijamy strukturę bazy danych. Poprawne połączenie z bazą danych zaowocuje drzewem obiektów dostępnych w bazie danych.

Rys.10. Drzewo obiektów bazy danych w serwisie ElephantSQL

Tworzenie i modyfikacja struktury tabeli w relacyjnej bazie danych

W ramach tego punktu zapoznamy się z poleceniami języka SQL tworzących lub modyfikujących struktury w bazie danych, podzbiór poleceń DDL (Data Definition Language). Poniżej przedstawiono polecenia umożliwiające tworzenie struktury tabeli w bazie danych, jej modyfikację oraz usunięcie z bazy danych.
- CREATE - polecenie tworzące strukturę (tabelę) w bazie danych,
```
CREATE TABLE nazwa_tabeli ( nazwa_atr1 typ_danych, nazwa_atr2 typ_danych, nazwa_atr3 typ_danych, ... ) ;   
```
- ALTER - polecenie modyfikujące strukturę,
```
ALTER TABLE nazwa_tabeli ... ;   
```
- DROP - polecenie usuwające strukturę.
```
DROP TABLE nazwa_tabeli ;   
```

Relacyjna baza danych SQLite - SQLiteManager.

Tworzenie, modyfikacja i usnięcie tabeli w relacyjnej bazie danych - SQLite

--=================================================================================
-- Cwiczenie 01 SQLite - język DDL - przetwarzanie stryktury tabeli w bazie danych
--=================================================================================
-- ******************************************
-- Utworzenie tabeli CREATE TABLE
-- ******************************************
-- Utworzenie tabeli "uczestnik" 
-- Atrybuty tabeli:
-- id int - identyfikator uczestnika, klucz główny
-- imie varchar(30) - imie uczestnika
-- nzawisko varchar(30) - nazwisko uczestnika
CREATE TABLE uczestnik ( id int primary key, imie varchar(30), nazwisko varchar(30) ) ;
-- Modyfikacja tabeli uczestnik - dodanie atrybutu rok
ALTER TABLE uczestnik ADD rok char(4);
-- Usuniecie tabeli "uczestnik"
DROP TABLE uczestnik;

Interaktywny edytor w aplikacji SQLiteManager (rys.11). Po wklejeniu zapytania (napisaniu) w oknie Zapytanie wybieramy przycisk Wykonaj (czerwona ramka) lub korzystamy z klawisza funkcyjnego F9.

Rys.11. Przetwarzanie zapytań w narzędziu SQLiteStudio

Relacyjna baza danych PostgreSql (DBaaS - ElephantSQL).
- Tworzenie, modyfikacja i usuwanie tabeli w relacyjnej bazie danych - Postgresql
```
--=========================================================================
-- Cwiczenie 01 Postgresql - język DDL - tworzenie struktur w bazie danych
--=========================================================================
-- *****************************************
-- Utworzenie schematu CREATE SCHEMA
-- *****************************************
CREATE SCHEMA kurs;
-- Ustawienie schematu domyslnego
SET SEARCH_PATH TO kurs;
-- ******************************************
-- Utworzenie tabeli CREATE TABLE
-- ******************************************
-- Utworzenie tabeli "uczestnik" w schemacie "kurs"
-- Atrybuty tabeli:
-- id int - identyfikator uczestnika
-- imie varchar(30) - imie uczestnika
-- nzawisko varchar(30) - nazwisko uczestnika
CREATE TABLE lab01.uczestnik ( id int, imie varchar(30), nazwisko varchar(30) ) ;
-- Modyfikacja tabeli uczestnik - ustawienie klucza glownego na atrybucie id
ALTER TABLE lab01.uczestnik ADD primary key (id);
-- Modyfikacja tabeli uczestnik - dodanie atrybutu rok
ALTER TABLE lab01.uczestnik ADD rok char(4);
-- Usuniecie tabeli "uczestnik"
DROP TABLE lab01.uczestnik;
  
```
- Interaktywny edytor w aplikacji SQL Manager Lite for PostgreSQL. Po połączeniu z bazą danych w serwisie ElephantSQL, otwieramy interaktywny edytor poleceń SQL wybierając opcję Tools, a następnie Query Data lub klawisz funkcyjny F12 (rys.12). Rys.13 przedstawia funkcjonalność uruchomionego edytora. W ramach zajęć będziemy wykorzystywać możliwość podświetlenia odpowiedniej części skopiowanygo kodu i jego uruchomienia poprzez wybranie ikony zakreślonej na czerwono na rysunku 10 lub przy pomocy kombinacji klawiszy [Alt][F9].
  
  Rys.12. Uruchomienie interaktywnego edytora poleceń SQL
  
  Rys.13. Realizacja zadań z laboratorium
- Interaktywny edytor w aplikacji DBeaver. Po połączeniu z bazą danych w serwisie ElephantSQL, otwieramy interaktywny edytor poleceń SQL wybierając opcję SQL Editor lub klawisz funkcyjny F3 (rys.14). Rys.15 przedstawia funkcjonalność uruchomionego edytora. W ramach zajęć będziemy wykorzystywać możliwość podświetlenia odpowiedniej części skopiowanego kodu i jego uruchomienia poprzez wybranie ikony zakreślonej na czerwono na rysunku 15 lub przy pomocy kombinacji klawiszy [Ctrl][Enter].
  
  Rys.14. Uruchomienie interaktywnego edytora poleceń SQL
  
  Rys.15. Realizacja poleceń SQL w ramach edytora

Modyfikacja danych w relacyjnej bazie danych

W ramach tego punktu zapoznamy się z poleceniami języka SQL przetwarzającymi dane składowane w bazie danych, podzbiór poleceń DML (Data Manipulation Language):

INSERT - wprowadzanie danych do utworzonych tabel,

INSERT INTO nazwa_tabeli ( atr1, atr2, atr3, ... ) VALUES ( val1, val2, val3, ... ) ; 
INSERT INTO nazwa_tabeli VALUES ( val1, val2, val3, ... );

UPDATE - poprawa danych zawartych w tabelach,

UPDATE nazwa_tabeli SET  atr1 = val1, atr2 = val2, ...  WHERE atr = val ;

DELETE - usuwanie danych z tabel,

DELETE FROM nazwa_tabeli WHERE atr = val ;

SELECT - przeglądanie danych w tabelach.

SELECT * FROM nazwa_tabeli ;
SELECT atr1, atr2, ... FROM nazwa_tabeli WHERE warunek_wyboru ;

Baza danych SQLite - SQLiteManager.

Wprowadzanie, poprawianie, usuwanie i przeglądanie danych w tabeli uczestnik w bazie danych SQLite.

--======================================================================================
-- Cwiczenie 02 SQLite - jezyk DML - wprowadzanie, poprawianie, usuwanie i przegladanie
--                       danych w tabeli "uczestnik"
--======================================================================================
-- *************************************************************
-- Tworzymy tabelę uczestnik
-- *************************************************************
CREATE TABLE uczestnik ( id int PRIMARY KEY, imie varchar(30), nazwisko varchar(30) ) ;
-- *************************************************************
-- Wprowadzanie danych do tabeli "uczestnik"
-- Polecenie INSERT INTO table ( atrybuty ) VALUES ( wartosci )
-- *************************************************************
INSERT INTO uczestnik ( id, imie, nazwisko ) VALUES ( 1, 'Adam', 'Abacki' );
-- Wprowadamy dane zgodnie z kolejnoscia atrybutow w poleceniu CREATE 
-- (wprowadzamy wszystkie atrybuty )
INSERT INTO uczestnik VALUES ( 2, 'Bogdan', 'Babacki' );
-- Wprowadzamy jenym poleceniem INSERT kilka rekordow do tabeli
INSERT INTO uczestnik values 
  ( 3, 'Marek', 'Cabacki' ),
  ( 4, 'Edward', 'Dadacki' ),
  ( 5, 'Zygmunt', 'Ebacki' );
-- Wprowadzamy dane niekompletne ( brak wszystkich atrybutow )
INSERT INTO uczestnik ( id, nazwisko ) VALUES ( 6, 'Sosnowski') ;
-- Wprowadzamy atrybuty w innej kolejnosci niz w poleceniu CREATE TABLE
INSERT INTO uczestnik ( imie, nazwisko, id ) values ( 'Anna', 'Mamacka', 7);  
-- ***************************************************************
-- Wyswietlenie zawartosci tabeli
-- Polecenie SELECT [atrybuty] FROM tabela [ WHERE warunek ]
-- ***************************************************************
-- Wyswietlenie zawartosci tabeli
-- SELECT * FROM tabela
-- Wybor atrybutow do wyswietlnenia (projekcja)
-- SELECT atrybuty FROM tabela
-- Wybor okreslonych rekordow (selekcja)
-- SELECT atrybuty FROM tabela WHERE warunek wyboru
-- ***************************************************************
-- Wyswietlenie wszystkich atrybutow i wszystkich rekordow w tabeli
SELECT * FROM uczestnik;
-- Wyswietlenie wybranych atrybutow z tabeli
SELECT imie, nazwisko FROM uczestnik;
-- Wysietlenie wybranych rekordow 
SELECT imie, nazwisko FROM uczestnik WHERE id=5;
-- ***************************************************************
-- Modyfikacja zawartosci tabeli 
-- UPDATE tabela SET atytbut=nowa wartosc [ WHERE warunek ] 
-- ****************************************************************
-- Dodajemy do tabeli dodatkowy atrybut rok
ALTER TABLE uczestnik ADD rok char(4);
SELECT * FROM uczestnik;
-- Poprawa atrybutu we wszystkich rekordach
UPDATE uczestnik SET rok='2001';
SELECT * FROM uczestnik;
-- Poprawa atrybutu w wybranym wierszu
UPDATE uczestnik SET rok='2003' WHERE id=4;
SELECT * FROM uczestnik;
-- ****************************************************************
-- Usuniecie rekordow z tabeli 
-- DELETE FROM tabela [ WHERE warunek ] 
-- ****************************************************************
-- Usuniecie wybranego rekordu
DELETE FROM uczestnik WHERE id=3;
SELECT * FROM uczestnik;
-- Usuniecie wszystkich rekordow z tabeli
DELETE FROM uczestnik;
SELECT * FROM uczestnik;
DROP TABLE uczestnik;

Baza danych Postgresql (DBaaS - ElephantSQL).

Wprowadzanie, poprawianie, usuwanie i przeglądanie danych w tabeli lab01.uczestnik w bazie danych Postgresql.

--==========================================================================================
-- Cwiczenie 02 Postgresql - jezyk DML - wprowadzanie, poprawianie, usuwanie i przegladanie
--                           danych w tabeli "uczestnik"
--==========================================================================================
-- *************************************************************
-- Tworzymy tabelę lab01.uczestnik
-- *************************************************************
CREATE TABLE lab01.uczestnik ( id int, imie varchar(30), nazwisko varchar(30) ) ;
ALTER TABLE lab01.uczestnik ADD primary key (id);
-- *************************************************************
-- Wprowadzanie danych do tabeli "uczestnik"
-- Polecenie INSERT INTO table ( atrybuty ) VALUES ( wartosci )
-- *************************************************************
INSERT INTO lab01.uczestnik ( id, imie, nazwisko ) VALUES ( 1, 'Adam', 'Abacki' );
-- Wprowadamy dane zgodnie z kolejnoscia atrybutow w poleceniu CREATE 
-- (wprowadzamy wszystkie atrybuty )
INSERT INTO lab01.uczestnik VALUES ( 2, 'Bogdan', 'Babacki' );
-- Wprowadzamy jenym poleceniem INSERT kilka rekordow do tabeli
INSERT INTO lab01.uczestnik values 
  ( 3, 'Marek', 'Cabacki' ),
  ( 4, 'Edward', 'Dadacki' ),
  ( 5, 'Zygmunt', 'Ebacki' );
-- Wprowadzamy dane niekompletne ( brak wszystkich atrybutow )
INSERT INTO lab01.uczestnik ( id, nazwisko ) VALUES ( 6, 'Sosnowski') ;
-- Wprowadzamy atrybuty w innej kolejnosci niz w poleceniu CREATE TABLE
INSERT INTO lab01.uczestnik ( imie, nazwisko, id ) VALUES ( 'Anna', 'Mamacka', 7);  
-- ***************************************************************
-- Wyswietlenie zawartosci tabeli
-- Polecenie SELECT [atrybuty] FROM tabela [ WHERE warunek ]
-- ***************************************************************
-- Wyswietlenie zawartosci tabeli
-- SELECT * FROM tabela
-- Wybor atrybutow do wyswietlnenia (projekcja)
-- SELECT atrybuty FROM tabela
-- Wybor okreslonych rekordow (selekcja)
-- SELECT atrybuty FROM tabela WHERE warunek wyboru
-- ***************************************************************
-- Wyswietlenie wszystkich atrybutow i wszystkich rekordow w tabeli
SELECT * FROM lab01.uczestnik;
-- Wyswietlenie wybranych atrybutow z tabeli
SELECT imie, nazwisko FROM lab01.uczestnik;
-- Wysietlenie wybranych rekordow 
SELECT imie, nazwisko FROM lab01.uczestnik WHERE id=5;
-- ***************************************************************
-- Modyfikacja zawartosci tabeli 
-- UPDATE tabela SET atrybut=nowa wartosc [ WHERE warunek ] 
-- ****************************************************************
-- Dodajemy do tabeli dodatkowy atrybut rok
ALTER TABLE lab01.uczestnik ADD rok char(4);
SELECT * FROM lab01.uczestnik;
-- Poprawa atrybutu we wszystkich rekordach
UPDATE lab01.uczestnik SET rok='2001';
SELECT * FROM lab01.uczestnik;
-- Poprawa atrybutu w wybranym wierszu
UPDATE lab01.uczestnik SET rok='2003' WHERE id=4;
SELECT * FROM lab01.uczestnik;
-- ****************************************************************
-- Usuniecie rekordow z tabeli 
-- DELETE FROM tabela [ WHERE warunek ] 
-- ****************************************************************
-- Usuniecie wybranego rekordu
DELETE FROM lab01.uczestnik WHERE id=3;
SELECT * FROM lab01.uczestnik;
-- Usuniecie wszystkich rekordow z tabeli
DELETE FROM lab01.uczestnik;
SELECT * FROM lab01.uczestnik;
DROP TABLE lab01.uczestnik;

Zadania do ćwiczenia - modyfikacja danych w relacyjnej bazie danych DBaaS ElephantSQL.

Utworzyć tabelę KSIAZKA zawierające pola ISBN, autor, tytul i rok_wydania, klucz główny na atrybucie ISBN oraz atrybut tytul nie może być pusty.

-- przykładowe polecenie
CREATE TABLE lab01.ksiazka (
ISBN char(12) PRIMARY KEY,
autor varchar(50),
tytul varchar(100) NOT NULL,
rok_wydania char(4) ) ;

Wprowadzić 5 rekordów danych.

-- przykładowe polecenie
INSERT INTO lab01.ksiazka VALUES 
 ( 'isbn', 'autor', 'tytul', 'rok' ) ,
-- kolejne rekokordy
 ( 'isbn', 'autor', 'tytul', 'rok' ) ;

Zmodyfikować rok wydania dla wybranych trzech pozycji.

-- przykładowe polecenie
UPDATE lab01.ksiazka SET rok_wydania='....' WHERE ... ;

Usunąć wybraną jedną pozycję.

-- przykładowe polecenie
DELETE FROM lab01.ksiazka WHERE ... ;

Dodać do tabeli dodatkową kolumnę wydawnictwo.

-- przykładowe polecenie
ALTER TABLE lab01.ksiazka ADD wydawnictwo varchar(50) ;

Wprowadzić nazwę wydawnictw dla wszystkich rekordów.

-- przykładowe polecenie ( wszystkie rekordy beda mialy to samo wydanictwo )
UPDATE lab01.ksiazka SET wydawnictwo = '...' ;

Wprowadzić dodatkową komlumnę Cena.

  
ALTER TABLE lab01.ksiazka ADD cena decimal(10,2) ;

Wprowadzić dane do kolumny cena.

-- przykładowe polecenie ( wszystkie rekordy beda mialy tę samą cenę )
UPDATE lab01.ksiazka SET cena = '...' ;

Dodać do tabeli rekordy, aby końcowa ich liczba wynosiła 10, oraz liczba wydawnicytw była 3-4 i liczba autorów była nie więkasza niż 5.

Wyszukiwanie danych w relacyjnej bazie danych

W ramach tego punktu zapoznamy się z poleceniem SELECT umożliwiającym przeglądanie danych zawartych w tabelach relacyjnej bazy danych.

SELECT atr1, atr2, atr3, ... 
FROM nazwa_tabeli1, nazwa_tabeli2, ...
WHERE warunki_wyboru
GROUP BY atrybuty_grupujace
HAVING warunki_wyboru
ORDER BY atrybuty_sortujace ;

Baza danych SQLite - SQLiteManager.

Przeglądanie danych w tabeli uczestnik w bazie danych SQLite.

--==============================================================================
-- Cwiczenie 03 SQLite - jezyk DQL - wyszukiwanie danych w tabeli
--==============================================================================
-- *****************************************************************************
-- Przygotowanie danych do cwiczenia 3 
CREATE TABLE uczestnik 
  ( id int PRIMARY KEY, imie varchar(30), nazwisko varchar(30), rok char(4) ) ;
INSERT INTO uczestnik ( id, imie, nazwisko, rok ) VALUES 
  ( 1, 'Adam', 'Abacki', '2001' ),
  ( 2, 'Bogdan', 'Babacki', '2001' ),
  ( 3, 'Marek', 'Cabacki', '2002' ),
  ( 4, 'Edward', 'Dadacki', '2002' ),
  ( 5, 'Zygmunt', 'Ebacki', '2002' ),
  ( 6, 'Anna', 'Mamacka', '2001' );
INSERT INTO uczestnik ( id, nazwisko, rok ) VALUES ( 7, 'Sosnowski', '2001') ;
SELECT * FROM uczestnik;
-- **********************************************************************************
-- Aliasy atrybutow
SELECT id AS numer, imie AS im, nazwisko AS na FROM uczestnik;
-- Sortowanie rekordow po nazwisku
SELECT id, nazwisko, imie FROM uczestnik ORDER BY nazwisko;
-- Wybor rekordow, predykat IN
SELECT id, nazwisko, imie FROM uczestnik WHERE id IN ( 2,3,4,5);
-- Wybor rekordow, predykat BETWEEN
SELECT id, nazwisko, imie FROM uczestnik WHERE id BETWEEN 2 AND 4;
-- Wybor rekordow wyszukujac fraze w tekscie wedlug wzorca
SELECT id, nazwisko, imie FROM uczestnik WHERE nazwisko LIKE 'A%' ;
-- Wyszukiwanie rekordow z wartoscia pusta - NULL
SELECT id, nazwisko, imie FROM uczestnik WHERE imie IS NULL;
-- Wyszukiwanie rekordow nie posiadajach watosci pustej NOT NULL
SELECT id, nazwisko, imie FROM uczestnik WHERE imie IS NOT NULL;
-- Wyszukiwanie wartosci unikalnych DISTINCT
SELECT DISTINCT(rok) FROM uczestnik;

Baza danych Postgresql (DBaaS - ElephantSQL).

Przeglądanie danych w tabeli lab01.uczestnik w bazie danych Postgresql.

--==============================================================================
-- Cwiczenie 03 Postgresql - jezyk DQL - wyszukiwanie danych w tabeli
--==============================================================================
-- *****************************************************************************
-- Przygotowanie danych do cwiczenia 3 
CREATE TABLE lab01.uczestnik 
    ( id int, imie varchar(30), nazwisko varchar(30), rok char(4) ) ;
ALTER TABLE lab01.uczestnik ADD primary key (id);
INSERT INTO lab01.uczestnik ( id, imie, nazwisko, rok ) VALUES 
  ( 1, 'Adam', 'Abacki', '2001' ),
  ( 2, 'Bogdan', 'Babacki', '2001' ),
  ( 3, 'Marek', 'Cabacki', '2002' ),
  ( 4, 'Edward', 'Dadacki', '2002' ),
  ( 5, 'Zygmunt', 'Ebacki', '2002' ),
  ( 6, 'Anna', 'Mamacka', '2001' );
INSERT INTO lab01.uczestnik ( id, nazwisko, rok ) VALUES ( 7, 'Sosnowski', '2001') ;
SELECT * FROM lab01.uczestnik;
-- **********************************************************************************
-- Aliasy atrybutow
SELECT id AS numer, imie AS im, nazwisko AS na FROM lab01.uczestnik;
-- Sortowanie rekordow po nazwisku
SELECT id, nazwisko, imie FROM lab01.uczestnik ORDER BY nazwisko;
-- Wybor rekordow, predykat IN
SELECT id, nazwisko, imie FROM lab01.uczestnik WHERE id IN ( 2,3,4,5);
-- Wybor rekordow, predykat BETWEEN
SELECT id, nazwisko, imie FROM lab01.uczestnik WHERE id BETWEEN 2 AND 4;
-- Wybor rekordow wyszukujac fraze w tekscie wedlug wzorca
SELECT id, nazwisko, imie FROM lab01.uczestnik WHERE nazwisko LIKE 'A%' ;
-- Wyszukiwanie rekordow z wartoscia pusta - NULL
SELECT id, nazwisko, imie FROM lab01.uczestnik WHERE imie IS NULL;
-- Wyszukiwanie rekordow nie posiadajach watosci pustej NOT NULL
SELECT id, nazwisko, imie FROM lab01.uczestnik WHERE imie IS NOT NULL;
-- Wyszukiwanie wartosci unikalnych DISTINCT
SELECT DISTINCT(rok) FROM lab01.uczestnik;

Zadania do ćwiczenia - wyszukiwanie danych w relacyjnej bazie danych DBaaS ElephantSQL.
1. Wyszukać ksiązki w tabeli Ksiazka określonego autora.
```
  
SELECT * FROM lab01.ksiazka WHERE autor = ' .. ' ;
```
2. Wyszukać ksiązki w tabeli Ksiazka o określonej cenie.
```
  
SELECT * FROM lab01.ksiazka WHERE cena = ' .. ' ;
```
3. Wygenerować raport książek w tabeli Ksiazka o posortowany po tytule, autorze, roku wydania, wydawnictwie (osobono dla każdego atrybutu).
```
  
SELECT * FROM lab01.ksiazka ORDER BY ...  ;
```
4. Wyszukać ksiązki w tabeli Ksiazka w określonym przedziale cen.
```
  
SELECT * FROM lab01.ksiazka WHERE cena between ...  ;
```
5. Wyszukać ksiązki w tabeli Ksiazka w określonym przedziale roku wydania.
```
  
SELECT * FROM lab01.ksiazka WHERE rok_wydania  ...  ;
```
6. Wyszukać ksiązki w tabeli Ksiazka dla określonych wydawnictw.
```
  
SELECT * FROM lab01.ksiazka WHERE wydawnictwo in ...  ;
```
7. Wyszukać ksiązki w tabeli Ksiazka dla określonych autorów.
```
  
SELECT * FROM lab01.ksiazka WHERE autor in  ... ;
```
8. Wyszukać ksiązki w tabeli Ksiazka po fragmencie tytułu.
```
  
SELECT * FROM lab01.ksiazka WHERE tytul like ... ;
```

Agregacja danych, grupowanie i filtrowanie wyników

Baza danych SQLite - SQLiteManager.

Funkcje agregujące na tabeli uczestnik w bazie danych SQLite.

--==============================================================================
-- Cwiczenie 04 - jezyk DQL - funkcje agregujace
--==============================================================================
-- *****************************************************************************
-- Przygotowanie danych do cwiczenia 4 
DROP TABLE uczestnik;
CREATE TABLE uczestnik 
  ( id int PRINMARY KEY, imie varchar(30), nazwisko varchar(30), rok char(4) ) ;
ALTER TABLE uczestnik ADD ocena int;
ALTER TABLE uczestnik ADD oplata numeric(10,2);
INSERT INTO uczestnik ( id, imie, nazwisko, rok, ocena, oplata ) VALUES 
  ( 1, 'Adam', 'Abacki', '2001', 5, 100.00 ),
  ( 2, 'Bogdan', 'Babacki', '2001', 4, 120.00 ),
  ( 3, 'Marek', 'Cabacki', '2002', 4, 130.00 ),
  ( 4, 'Edward', 'Dadacki', '2002', 5, 100.00 ),
  ( 5, 'Zygmunt', 'Ebacki', '2002', 4, 100.00 ),
  ( 6, 'Anna', 'Mamacka', '2001', 4, 150.00 ),
  ( 7, 'Szymon', 'Zawacki', '2003', 5, 100.00 ),
  ( 8, 'Krystian', 'Nanacki', '2003', 4, 120.00 ),
  ( 9, 'Ewa', 'Cabacka', '2001', 5, 130.00 ),
  (10, 'Andrzej', 'Tadacki', '2002', 5, 100.00 ),
  (11, 'Zofia', 'Kowalska', '2002', 4, 140.00 ),
  (12, 'Maria', 'Mamacka', '2003', 5, 150.00 );
INSERT INTO uczestnik ( id, nazwisko, rok ) VALUES (13, 'Sosnowski', '2001') ;
-- **********************************************************************************
-- Suma wniesionych oplat SUM()
SELECT SUM(oplata) AS oplata FROM uczestnik ;
-- Liczba wszystkich uczestnikow COUNT()
SELECT COUNT(*) AS liczba_uczestnikow FROM uczestnik ;
-- Srednia wartosc oplaty dla wszystkich uczestnikow AVG()
SELECT AVG(oplata) AS srednia_oplat FROM uczestnik ;
-- Wartosc maksymalna i minimalna oplaty za kurs
SELECT MIN(oplata) AS min_oplata, MAX(oplata) AS max_oplata FROM uczestnik ; 
-- Liczba uczestnikow w poszczegolnych latach
SELECT rok, COUNT(*) FROM uczestnik GROUP BY rok;
-- Suma oplat w poszczegolnych latach
SELECT rok, SUM(oplata) AS oplata FROM uczestnik GROUP BY rok;
-- Lista kursow w poszczegolnych latach w ktorych liczba uczestnikow wieksza niz podana wartosc
SELECT rok, COUNT(*) FROM uczestnik GROUP BY rok HAVING COUNT(*) > 4;

Baza danych Postgresql (DBaaS - ElephantSQL).

Funkcje agregujące na tabeli lab01.uczestnik w bazie danych Postgresql.

--==============================================================================
-- Cwiczenie 04 - jezyk DQL - funkcje agregujace
--==============================================================================
-- *****************************************************************************
-- Przygotowanie danych do cwiczenia 4 
DROP TABLE lab01.uczestnik;
--
CREATE TABLE lab01.uczestnik 
  ( id int PRIMARY KEY, imie varchar(30), nazwisko varchar(30), rok char(4) ) ;
--
ALTER TABLE lab01.uczestnik ADD ocena int;
--
ALTER TABLE lab01.uczestnik ADD oplata numeric(10,2);
--
INSERT INTO lab01.uczestnik ( id, imie, nazwisko, rok, ocena, oplata ) VALUES 
  ( 1, 'Adam', 'Abacki', '2001', 5, 100.00 ),
  ( 2, 'Bogdan', 'Babacki', '2001', 4, 120.00 ),
  ( 3, 'Marek', 'Cabacki', '2002', 4, 130.00 ),
  ( 4, 'Edward', 'Dadacki', '2002', 5, 100.00 ),
  ( 5, 'Zygmunt', 'Ebacki', '2002', 4, 100.00 ),
  ( 6, 'Anna', 'Mamacka', '2001', 4, 150.00 ),
  ( 7, 'Szymon', 'Zawacki', '2003', 5, 100.00 ),
  ( 8, 'Krystian', 'Nanacki', '2003', 4, 120.00 ),
  ( 9, 'Ewa', 'Cabacka', '2001', 5, 130.00 ),
  (10, 'Andrzej', 'Tadacki', '2002', 5, 100.00 ),
  (11, 'Zofia', 'Kowalska', '2002', 4, 140.00 ),
  (12, 'Maria', 'Mamacka', '2003', 5, 150.00 );
--
INSERT INTO lab01.uczestnik ( id, nazwisko, rok ) VALUES (13, 'Sosnowski', '2001') ;
-- **********************************************************************************
-- Suma wniesionych oplat SUM()
SELECT SUM(oplata) AS oplata FROM lab01.uczestnik ;
--
-- Liczba wszystkich uczestnikow COUNT()
SELECT COUNT(*) AS liczba_uczestnikow FROM lab01.uczestnik ;
--
-- Srednia wartosc oplaty dla wszystkich uczestnikow AVG()
SELECT AVG(oplata) AS srednia_oplat FROM lab01.uczestnik ;
--
-- Wartosc maksymalna i minimalna oplaty za kurs
SELECT MIN(oplata) AS min_oplata, MAX(oplata) AS max_oplata FROM lab01.uczestnik ; 
--
-- Liczba uczestnikow w poszczegolnych latach
SELECT rok, COUNT(*) FROM lab01.uczestnik GROUP BY rok;
--
-- Suma oplat w poszczegolnych latach
SELECT rok, SUM(oplata) AS oplata FROM lab01.uczestnik GROUP BY rok;
--
-- Lista kursow w poszczegolnych latach w ktorych liczba uczestnikow wieksza niz podana wartosc
SELECT rok, COUNT(*) FROM lab01.uczestnik GROUP BY rok HAVING COUNT(*) > 4;

Zadania do ćwiczenia - agregacja danych, grupowanie i filtrowanie wyników.
- Liczba rekordów w tabeli Ksiazka
- Liczba rekordów w tabeli Ksiazka dla wybranego autora, wydawnictwa.
- Maksymalna, minimalna wartość ceny ksiązki w tabeli.
- Wartość średnia ceny ksiązki w tabeli.
- Liczba książek w określonym przedziale cen.
- Wydawnictwa dla których liczba książek w bazie jest większa niż 2.
- Liczba ksiązek dla każdego wydawnictwa.

Lab01 - Bazy danych cw.01 [ lab:24.03.2023, ver. SPN.2023.03.21.003 ]

Bazy danych

Baza danych SQLite - SQLiteStudio

Baza danych PostgreSQL - DBaaS ElephantSQL

SQL Manager Lite for PostgreSQL

DBeaver

Tworzenie i modyfikacja struktury tabeli w relacyjnej bazie danych

Modyfikacja danych w relacyjnej bazie danych

Wyszukiwanie danych w relacyjnej bazie danych

Agregacja danych, grupowanie i filtrowanie wyników

Zadania do realizacji

Linki do dodatkowych materiałów (dostęp na dzień 21.03.2023)