Structured Query Language

1. Structured Query Language Wstęp do wprowadzenia do przedsłowia pierwszego rozdziału ;)

2. Tworzenie relacji • przykład: Stworzyć tabelę do przechowywania informacji o pracownikach ‘naszej’ księgarni. Chcemy zapamiętać: imię nazwisko, datę zatrudnienia, etat oraz płacę naszych pracowników, także ich identyfikator CREATE TABLE nazwa_relacji(nazwa_atr typ (rozmiar), nazwa_atr typ (rozmiar), ...); CREATE TABLE Pracownicy ( PracID INT, Imię VARCHAR(10), Nazwisko VARCHAR(20), Zatrudniony DATE, Etat VARCHAR(20), Płaca DECIMAL(7,2) );

3. Typy danych • Typy logiczne i całkowite

4. Typy danych (2) • Typy zmiennoprzecinkowe

5. Typy danych (3) • Typy znakowe daty i czasu

6. Przykład • podaj składnie sql, która stworzy tabelę o następującym schemacie: CREATE TABLE Podania(PodID SERIAL , Imię VARCHAR(20), Nazwisko VARCHAR , Dnia DATE, Czas TIME, IdDziekana NUMERIC(2), Decyzja BOOL, Opłata DECIMAL(7,2));

7. Ograniczenia integralnościowe CREATE TABLE nazwa_relacji( nazwa_atr typ (rozmiar) [DEFAULT wartość_domyślna] [ [CONSTRAINT nazwa_ogr] ogr_atr].., nazwa_atr typ (rozmiar) [DEFAULT wartość_domyślna] [ [CONSTRAINT nazwa_ogr] ogr_atr]..., ....[ [CONSTRAINT nazwa_ogr] ograniczenie_rel, ...] ); CREATE TABLE Podania( PodID SERIAL PRIMARY KEY, Imię VARCHAR(20), Nazwisko VARCHAR , Dnia DATE CONSTRAINT niepusty_termin NOT NULL, Czas TIME, IdDziekana NUMERIC(2) DEFAULT 11, Decyzja BOOL, Opłata DECIMAL(7,2), CHECK (Opłata>=0) );

8. Klucz podstawowy • ograniczenie atrybutu CREATE TABLE Podania( PodID INT CONSTRAINT podid_pk PRIMARY KEY, Index VARCHAR(15), IdDziekana NUMERIC(2) DEFAULT 11, Decyzja BOOL, ); • ograniczenie relacji CREATE TABLE Podania( PodID INT, Index VARCHAR(15), IdDziekana NUMERIC(2) DEFAULT 11, Decyzja BOOL, CONSTRAINT podid_pk PRIMARY KEY PodID);

9. Klucz podstawowy (2) • utwórz relację Klienci o następującym schemacie klucz podstawowy to imię i nazwisko: CREATE TABLE Klienci( Imię CHAR(15), Nazwisko VARCHAR(25), Adres VARCHAR(100), Wiek NUMERIC(3), Data_współpracy DATE, PRIMARY KEY (Imię, Nazwisko));

10. Klucz obcy CREATE TABLE Wydawnictwa( WydID INT PRIMARY KEY, Nazwa VARCHAR(40), Telefon VARCHAR(40)); CREATE TABLE Książki( ISBN VARCHAR(20) PRIMARY KEY, Tytuł VARCHAR(40), WydID INT REFERENCES Wydawnictwa(WydID), Cena DECIMAL(6,2)); CREATE TABLE Książki( ISBN VARCHAR(20) PRIMARY KEY, Tytuł VARCHAR(40), WydID INT, Cena DECIMAL(6,2), FOREIGN KEY WydID REFERENCES Wydawnictwa(WydID) );

11. Modyfikowanie schematów relacji • Istnieje relacja Pracownicy (PracID, Imię, Nazwisko, ZesID, Płaca) – należy stworzyć tabelę z zarobkiem rocznym CREATE TABLE nazwa_relacji(nazwa_atrybutu [DEFAULT wartość_domyślna] [ [CONSTRAINT nazwa_ogr] ograniczenie_atr]...,nazwa_atrybutu [DEFAULT wartość_domyślna] [ [CONSTRAINT nazwa_ogr] ograniczenie_atr]...,....[ [CONSTRAINT nazwa_ogr] ograniczenie_rel, ...] ) AS SELECT zapytanie; CREATE TABLE Roczne AS SELECT PracID, Imię, Nazwisko, 12*Płaca AS Dochód FROM Pracownicy;

12. Modyfikacja schematu relacji (2) • dodawanie kolumny ALTER TABLE tabela ADD COLUMN atrybut typ; ALTER TABLE tabela ADD COLUMN atrybut typ CHECK (atrybut <> ''); usuwanie kolumny ALTER TABLE tabelaDROP COLUMN atrybut;

13. Modyfikacja schematu relacji (3) • dodawanie ograniczeń ALTER TABLE tabela ADD CHECK (atrybut <> ''); ALTER TABLE tabela ADD CONSTRAINT nazwa_ogr UNIQUE (atrybut); ALTER TABLE tabela ADD FOREIGN KEY (atrybut) REFERENCES tab_inna(atrybut_inny); ALTER TABLE tabela ALTER COLUMN atrybut SET NOT NULL; usuwanie ograniczeń ALTER TABLE tabela DROP CONSTRAINT naza_ogr; ALTER TABLE tabela ALTER COLUMN atrybut DROP NOT NULL;

14. Modyfikacja schematu relacji (4) • zmiana wartości domyślnej ALTER TABLE tabela ALTER COLUMN atr SET DEFAULT 7.77 ALTER TABLE tabela ALTER COLUMN atr DROP DEFAULT; zmiana nazwy kolumn ALTER TABLE tabela RENAME COLUMN atrybut1 TO arybut2; zmiana nazwy tabeli ALTER TABLE tabela RENAME TO tabela2;

15. Logowanie do bazy danych • psql nazwa_bazy_danych _login • nazwa_bazy_danych: doktoranci • login: {d1, d2, d3 …. d20}

16. Zadania 1. Utwórz tabelę ksiazki, zawierającą następujące atrybuty: • ksiazki (id, imie, nazwisko, tytul, cena, ilosc_sprzedanych, adres) • id – liczba stałoprzecinkowa, klucz główny • imię nazwisko, tytuł, adres typ znakowy zmiennej długości, maksymalnie 20, 30, 50 i 30 znaków • cena, liczba, 2 znaki po przecinku • ilość sprzedanych, liczba stałoprzecinkowa

17. ilosc_osob koszt nazwa czas_trwania adres id regon wycieczka klient Zadania • Utwórz tabele opisane następującym schematem wycieczka(id,koszt,czas_trwania,ilosc_osob,regon), id klucz główny, regon – klucz obcy klient(nazw,adres,regon) , regon – klucz główny

18. ilosc_osob koszt nazwa czas_trwania adres id wycieczka klient Zadania • Zmodyfikuj poprzednie zadanie usuwając pole regon z tabeli klient i nakładając klucz złożony na dwa atrybuty tej tabeli adres i nazwa wycieczka(id,koszt,czas_trwania,ilosc_osob, nazwa,adres), id klucz główny, nazwa,adres – klucz obcy klient(nazw,adres) , nazwa,adres – klucz główny

19. Wstawianie krotek do relacji • schemat instrukcji: INSERT INTO nazwa_relacji VALUES (wartość1 [ DEFAULT ] [ NULL ], ..., wartośćN); dodać informację o kolejnym wydawnictwie ‘Gamma Press’ – nic jeszcze nie wiadomo i jego nr telefonu INSERT INTO Wydawnictwa VALUES (6,NULL,'Gamma Press');

20. Wstawianie krotek do relacji (2) • schemat instrukcji: INSERT INTO nazwa_relacji (atrybut1, ..., atrybutN) VALUES(wartość1 [ DEFAULT ][ NULL ], ..., wartośćN); dodać informację o kolejnym wydawnictwie znany jest numer telefonu tego wydawnictwa, nie wiadomo jak ono się nazywa INSERT INTO Wydawnictwa(WydID, Telefon) VALUES(7, '666-66-66');

21. 1 Jan Mrozek Opetanie 20.50 3 Krakow 2 Maria Niec Lot nad Azją 50.50 0 Warszawa 3 Ala Nomab Week 20.50 1 Krakow 4 Pawel Knab Wakacje 20.50 3 Krakow 5 Janusz Koza Obcy 25.50 3 Krakow 6 Janina Andrycz Marzenie 99.50 4 Krakow 7 Jerzy Michnik Tomahawk 20.00 2 Krakow 8 Jadwiga Talin Auta 20.00 1 Krakow 9 Kaja Marab Wiersze 40.50 0 Krakow 10 Maria Mrozik Analiza 20.50 2 Krakow Zadanie • Wprowadź do relacji poniższe dane:

22. Proste zapytanie Odczytaj wszystkie dane z tabeli Książki: SELECT * FROM Wydawnictwa; SELECT * FROM Książki;

23. Projekcja • Odczytaj nazwę i telefon z tabeli Wydawnictwa SELECT Nazwa, Telefon FROM Wydawnictwa; Odczytaj tytuł i cenę wszystkich książek z tabeli Książki SELECT Tytuł, Cena FROM Książki;

24. Wyrażenia SELECT ISBN||' '||Tytuł, 0.1*Cena FROM Książki; Oblicz przychód księgarni od sprzedaży każdej książki po uwzględnieniu zysku odprowadzanego do wydawnictwa SELECT Tytuł, Cena-0.1*Cena FROM Książki;

25. Aliasy SELECT ISBN||' '||Tytuł AS Pozycja, 0.1*Cena AS Wydawnictwu FROM Książki;

26. Funkcje skalarne i arytmetyczne Wybieranie wyliczonych wartości. • W zapytaniu SQL możemy używać następujących operatorów arytmetycznych w celu obliczenia wartości: • + (dodawanie) • - (odejmowanie) • * (mnożenie) • / (dzielenie) • użyciem nawiasów dla zaznaczenia kolejności wykonywania działań • wartości null nie mogą brać udziału w wyliczeniach

27. Eliminacja duplikatów • Wyświetl imiona Autorów SELECT Imię FROM Autorzy; SELECT DISTINCT Imię FROM Autorzy; Znajdź wszystkie identyfikatory wydawnictw, których książki są zarejestrowane w bazie danych; identyfikatory nie powinny się powtarzać

28. Sortowanie wyników zapytań SELECT Nazwisko FROM Autorzy; SELECT Nazwisko FROM Autorzy ORDER BY Nazwisko; SELECT [DISTINCT] {wyrażenie1 [AS alias1], wyrażenie2 [AS alias2],.....} FROM {nazwa relacji} ORDER BY {wyrażenie3 [ASC|DESC], wyrażenie4 [ASC|DESC], alias3 [ASC|DESC]};

29. Selekcja SELECT Tytuł FROM Książki WHERE WydID=1 ORDER BY Tytuł; SELECT [DISTINCT] {wyrażenie1 [AS alias1], wyrażenie2 [AS alias2],.....} FROM {nazwa relacji}WHERE warunek_elementarny ORDER BY {wyrażenie5 [ASC|DESC], wyrażenie6 [ASC|DESC], alias1 [ASC|DESC]…};

30. Selekcja – operatory logiczne = != <> > >= < <= SELECT Tytuł FROM Książki WHERE Cena>50; SELECT ISBN FROM Książki WHERE Tytuł='c++'; SELECT ISBN FROM Książki WHERE Tytuł='C++';

31. Selekcja – operatory logiczne (2) • BETWEEN … AND… SELECT Tytuł, Cena FROM Książki WHERE Cena BETWEEN 25 AND 29 ORDER BY Cena;

32. Selekcja – operatory logiczne (3) • IN SELECT Tytuł, Cena FROM Książki WHERE CENA IN(20, 51, 29) ORDER BY Cena;

33. Selekcja – operatory logiczne (4) • LIKE SELECT Tytuł FROM Książki WHERE ISBN LIKE '0-999-9999-99';

34. Selekcja – operatory logiczne (5) • LIKE • _ - dopasowanie do dowolnego znaku • % - dopasowanie do dowolnej ilości znaków (w tym również do zerowej ilości wystąpień) PRZYKŁAD: 'abc' LIKE 'abc' 'abc' LIKE 'a%' 'abc' LIKE '_b_' 'abc' LIKE 'c‘ 'abc' LIKE 'abc_' 'abc' LIKE 'abc%'

35. Zanegowane operatory logiczne • NOT BETWEEN ... AND ... • NOT IN • NOT LIKE • IS NOT NULL SELECT * FROM AUTORZY WHERE Telefon IS NOT NULL; SELECT * FROM AUTORZY WHERE Nazwisko NOT LIKE 'H%r';

36. Podsumowanie SELECT [DISTINCT] {wyrażenie1 [AS alias1], wyrażenie2 [AS alias2],.....} FROM {nazwa_relacji} WHERE {warunek_złożony} ORDER BY {wyrażenie3 [ASC|DESC], wyrażenie4 [ASC|DESC], alias1 [ASC|DESC], alias2 [ASC|DESC].....};

37. Zadanie • Wyświetl: • tabelę w porządku rosnącej ceny, • tabelę w porządku malejącej ceny i ilości sprzedanych książek (rosnąco), • jakie kwoty cen występują (bez powtórzeń), • tytuły i ceny tych książek, które kosztują ponad 30zł, • imię i nazwisko autorów oraz tytuły pisarzy mieszkających w Warszawie • tytuły dzieł i imię i nazwisko osób z Krakowa, które piszą książki sprzedawana za ponad 50 zł, • tytuły, ceny dzieł oraz imię i nazwisko osób które nie są z Krakowa lub te tytuły które są sprzedawane za mniej niż 50 zł, • książki, których cena jest z przedziału <30, 100> • osoby, których nazwisko zaczyna się od litery K • osoby, których nazwisko nie zaczyna na literę M i kończy się na literę b