SQL Bez Tajemnic - Pytania i Odpowiedzi

Krzysztof Bury Zdjęcie Profilowe

Krzysztof Bury

Twój Przewodnik

Cześć!

Przygotowujesz się do rozmowy rekrutacyjnej lub chcesz sprawdzić swoją wiedzę z SQL-a?

Jesteś w dobrym miejscu, przygotowaliśmy dla Ciebie zbiór pytań rekrutacyjnych, dzięki którym język zapytań do baz danych nie będzie miał przed Tobą tajemnic.


Dla Twojej wygody pytania oznaczone są kategoriami, co pozwoli Ci na łatwiejsze wyszukiwanie. 

Kategorie: #podstawy, #skladnia #join #wydajnosc, #antywzorce, #teoria, #praktyka


Miłej Nauki!


Schemat tabel użytych w pytaniach: 

CREATE TABLE products

CREATE TABLE IF NOT EXISTS products (
   product_id SERIAL,
   product_name varchar(500),
   product_category varchar(250),
   random_desc text
);

CREATE TABLE sales

CREATE TABLE IF NOT EXISTS sales (
   id SERIAL,
   sales_date TIMESTAMP,
   sales_amount NUMERIC(38,2),
   sales_qty INTEGER,
   discount NUMERIC(38,2),
   product_id INTEGER
);

CREATE TABLE contacts

CREATE TABLE IF NOT EXISTS contacts(
   imie_nazwisko TEXT,
gender TEXT,
city TEXT,
country TEXT,
id_company INTEGER,
company TEXT,
added_by TEXT,
verified_by TEXT
);//UWAGA: ta tabele świadomie zawiera błędy strukturalne (nie trzyma się zasad normalizacji itp.) związane są z tym ćwiczenia poniżej

Przykładowe zasilenie danymi (na podstawie PostgreSQL):

INSERT INTO products

INSERT INTO products (product_name, product_category, random_desc)
SELECT 'Product '||floor(random() * 10 + 1)::int,
       'Category '||floor(random() * 10 + 1)::int,
       left(md5(i::text), 15)
FROM generate_series(1, 1000) s(i);

INSERT INTO sales

INSERT INTO sales (sales_date, sales_amount, sales_qty, product_id)
SELECT NOW() + (random() * (interval '90 days')) + '30 days',
random() * 10 + 1,
floor(random() * 10 + 1)::int,
floor(random() * 100)::int
FROM generate_series(1, 10000) s(i);

INSERT INTO contacts

INSERT INTO contacts VALUES ('Krzysztof, Bury', 'M', 'Kraków', 'Polska', 1, 'ACME', 'Osoba A', 'Osoba B'),
('Magda, Kowalska', 'F', 'New York', 'USA', 2, 'CDE', 'Osoba C', 'Osoba A');

Schemat tabel dla zadań praktycznych i czystą kartę do pracy znajdziesz również na SQL Fiddle, tutaj: http://sqlfiddle.com/#!17/d89b5 (kliknij Build Schema, żeby móc pracować z panelem wykonywania zapytań).


Odpowiedzi znajdziesz po kliknięciu pytania. 


Przykładowe rozwiązania zostały przygotowane na podstawie bazy danych PostgreSQL.

Co oznacza skrót SQL? Z jakich elementów składa się język zapytań do baz danych?   #podstawy #teoria

SQL (Structure Query Language) to język zapytań do baz danych. 


SQL możemy podzielić na kilka części:

  • DDL - Data Definition Language - cześć języka SQL, odpowiadające za tworzenie, modyfikacje czy usuwanie obiektów bazy danych, operacje takie jak CREATE, ALTER, DROP.
  • DML - Data Manipluation Langugage - zbiór instrukcji języka SQL, pozwalający na wykonanie operacji INSERT, UPDATE,  DELETE (dodawanie, aktualizacja, usuwanie wierszy). 
  • DQL - Data Query Language - podstawa pobierania danych, czyli SELECT.
  • DCL - Data Control Language - element pozwalające na dodawanie lub odbieranie uprawnień, GRANT / REVOKE.
  • DTL / TCL - Data Transaction Language / Transaction Control Language - cześć odpowiedzialna za obsługę transakcji: COMMIT, ROLLBACK , SAVEPOINT.

Czym jest system zarządzania bazą danych (DBMS - Database Management System)?   #podstawy #teoria

DBMS - Database Management System - jest zbiorem reguł (system) opisanych przez programy komputerowe, które pozwalają na manipulację (manage) wartości atrybutów kilku różnych typów jednostek uporządkowanych w sensowny sposób (database).

Czym w kontekście SQL-a jest DDL?   #podstawy #teoria

DDL - Data Definition Language, jest elementem języka zapytań do baz danych, który ma na celu, tworzenie, modyfikację oraz usuwanie elementów struktury bazy danych - tabele, schematy, indeksy etc.


Do kluczowych elementów składni należą operacje: CREATE, ALTER oraz DELETE.


Pomimo, że powyższe operacje są elementem standardu, to ich dodatkowe elementy są różnie implementowane przez różnych dostawców rozwiązań baz danych. Poniżej przykładowe definicje instrukcji CREATE TABLE u różnych dostawców


CREATE TABLE:

Podaj po 1 przykładzie użycia składni dla każdego z elementów DDL.   #podstawy #teoria

CREATE:

CREATE TABLE sales ( id SERIAL );


ALTER:

ALTER TABLE sales ADD COLUMN sales_amount NUMERIC(10,2);


DROP:

DROP TABLE sales;

Czym jest DCL w kontekście SQL-a.   #podstawy #teoria

DCL (Data Control Language) jest elementem języka zapytań do baz danych, który ma na celu, nadawanie i odbieranie dostępu do obiektów bazy danych.


Do kluczowych elementów składni należą operacje: GRANT / REVOKE (DENY)

  • GRANT - nadaję dostępy do obiektów bazodanowych
  • REVOKE (DENY) - odbiera dostęp do obiektów bazodanowych

Czym jest TCL w kontekście SQL-a.   #podstawy #teoria

TCL (Transaction Control Language) - jest elementem języka zapytań do baz danych, który ma na celu zarządzanie transakcjami w bazie danych.


Do kluczowych elementów składni należą operacje: COMMIT, ROLLBACK, SAVEPOINT.

  • COMMIT - potwierdza transakcje i wszystkie akcje w niej wykonane.
  • ROLLBACK - wycofuje transakcje i wszystkie akcje w niej wykonane mające wpływ na struktury bazy danych i samych danych.
  • SAVEPOINT - zapisuje stan, do którego można się odwołać wewnątrz transakcji.

Jakie elementy struktury bazy danych, możemy wyróżnić (elementy jako warstwy bazy, np. tabele, indeksy etc.)?   #podstawy #teoria

Do typowych elementów struktury bazy danych zaliczamy:

  • schemat bazy danych
  • tabele
  • kolumny (atrybuty / pola)
  • wiersze (rekordy)
  • klucze / ograniczenia (constraints)
  • widoki
  • indeksy
  • procedury / funkcje

Można to łatwo przedstawić na grafice:

Typowe Elementy Bazy Danych
Transakcja 1:

UPDATE products SET product_name = 'Product 999' where product_name = 'Product 1';

UPDATE products SET product_name = 'Product 991' where product_name = 'Product 2';
Transakcja 2:


UPDATE products SET product_name = 'Product 998' where product_name = 'Product 2';


UPDATE products SET product_name = 'Product 001' where product_name = 'Product 1';

Normalizacja bazy danych to technika organizacji danych w bazie, jest ona systematycznym metodycznym podejście "rozbijania" tabel w celu eliminowania powtórzeń i niechcianych cech operacji dodawania, usuwania i aktualizacji wierszy.


Dwa kluczowe cele normalizacji:

  1. Reprezentacja faktów o rzeczywistym świecie w zrozumiały sposób;
  2. Ograniczenie nadmiarowego przechowywania faktów i zapobiegania występowaniu nieprawidłowych lub niespójnych danych;

INSERT - problemy przy braku normalizacji

Kolumna miejscowość (CITY) w tabeli CONTACTS, jest dobrym przykładem, żeby rozpatrzeć problemy braku normalizacji w kontekście operacji INSERT.


W przypadku dodawania 100 kontaktów z Poznania wszystkim rekordom przypisujemy taką samą informację tekstową. Z drugiej strony, gdy jakiś kontakt nie podał informacji o miejscowości (nie jest ona wymagana), będziemy musieli wstawić w to pole wartość nieokreśloną NULL.


UPDATE - problemy przy braku normalizacji

Kolumna firma (COMPANY) w tabeli CONTACTS, jest dobrym przykładem, żeby rozpatrzeć problemy braku normalizacji w kontekście operacji UPDATE.


Jeżeli wiele kontaktów, pracuje dla tej samej firmy - ACME - i firma ta zmieni nazwę, lub zniknie z rynku. Wszystkie rekordy będą musiały mieć zaktualizowaną wartość, pominięcie jakiegoś kontaktu i pozostawienie mu wartości ACME, będzie skutkować niespójnością w danych.


DELETE - problemy przy braku normalizacji

Załóżmy, że tabela CONTACTS, to jedyne miejsce, w którym przechowujemy informacje o miejscowości (CITY). Jeżeli z jakiegoś powodu usuniemy wszystkie kontakty z Poznania, nie będziemy w stanie odnieść się do tej wartości w przypadku innych zapytań.

1NF - rozdzielamy pole NAME_SURNAME.| NAME| SURNAME | GENDER | CITY | COUNTRY | ID_COMPANY | COMPANY | ADDED_BY | VERIFIED_BY |
|---|---|---|---|---|---|---|---|---|
| Krzysztof | Bury | M |  Kraków | Polska | 1 | ACME | OsobaA  | OsobaB |
| Magda| Kowalski | F | New York | USA | 2 | CDE | OsobaC  | OsobaA |2NF - wydzielamy tabelę GENDER na podstawie kolumny GENDER z CONTACTS.CONTACTS
| NAME| SURNAME | CITY | COUNTRY | ID_COMPANY | COMPANY | COMPANY | ADDED_BY | VERIFIED_BY |
|---|---|---|---|---|---|---|---|
| Krzysztof | Bury | Kraków | Polska | 1 | ACME | OsobaA  | OsobaB |
| Magda| Kowalski | New York | USA | 2 | CDE | OsobaC  | OsobaA |GENDER
| NAME| GENDER|
|---|---|
| Krzysztof | M |
| Magda | F |3NF - wydzielamy tabelę COMPANY na podstawie kolumn ID_COMPANY i COMPANY z CONTACTS.CONTACTS
| NAME| SURNAME | CITY | COUNTRY | ID_COMPANY | ADDED_BY | VERIFIED_BY |
|---|---|---|---|---|---|---|
| Krzysztof | Bury | Kraków | Polska | 1 | OsobaA  | OsobaB |
| Magda| Kowalski | New York | USA | 2 | OsobaC  | OsobaA |GENDER
| NAME| GENDER|
|---|---|
| Krzysztof | M |
| Magda | F |COMPANY
| ID_COMPANY | COMPANY|
|---|---|
| 1 | ACME |
| 2 | CDE |BCNF - tworzymy nową tabelę CITY_COUNTRY na podstawie kolumn CITY i COUNTRY z CONTACTS.CONTACTS
| NAME| SURNAME | CITY | ID_COMPANY | ADDED_BY | VERIFIED_BY |
|---|---|---|---|---|---|---|
| Krzysztof | Bury | Kraków | 1 | OsobaA  | OsobaB |
| Magda| Kowalski | New York | 2 | OsobaC  | OsobaA |GENDER
| NAME| GENDER|
|---|---|
| Krzysztof | M |
| Magda | F |COMPANY
| ID_COMPANY | COMPANY|
|---|---|
| 1 | ACME |
| 2 | CDE |CITY_COUNTRY
| CITY | COUNTRY |
|---|---|
| Kraków | Polska |
| New York | USA |4NF - tworzymy nową tabelę CONTACTS_VERIFIED_BY i CONTACTS_ADDED_BY na podstawie kolumn VERIFIED_BY i ADDED_BY z tabeli CONTACTS.CONTACTS
| NAME| SURNAME | CITY | ID_COMPANY |
|---|---|---|---|
| Krzysztof | Bury | Kraków | 1 |
| Magda | Kowalski | New York | 2 |GENDER
| NAME| GENDER|
|---|---|
| Krzysztof | M |
| Magda | F |COMPANY
| ID_COMPANY | COMPANY|
|---|---|
| 1 | ACME |
| 2 | CDE |CITY_COUNTRY
| CITY | COUNTRY |
|---|---|
| Kraków | Polska |
| New York | USA |CONTACTS_VERIFIED_BY
| CONTACT_NAME | VERIFIED_BY|
|---|---|
| Krzysztof | OsobaB |
| Magda | OsobaA |CONTACTS_ADDED_BY
| CONTACT_NAME  | ADDED_BY|
|---|---|
| Krzysztof | OsobaA |
| Magda | OsobaC |

Obie komendy służą do usuwania wierszy z tabeli, ale są między nimi pewne subtelne różnice.


DELETE
jest operacją DML-ową (Data Manipulation Language) działa na poziomie wiersza tabeli. Z jej pomocą możemy usunąć wszystkie wierszy lub jeden konkretny (dokładając WHERE).

DELETE FROM <TABLE NAME> WHERE …

TRUNCATE
jest operacją DDL-ową (Data Definition Language) działa na poziomie całej tabeli. Jeżeli chcesz usunąć wszystkie wiersze z tabeli (a dodatkowo Twoja tabela nie ma kluczy obcych), to prawdopodobnie to będzie komenda, której szukasz.

TRUNCATE TABLE <TABLE NAME> …

Trochę o detalach obu komend.

Transakcyjność

Odzyskiwanie Miejsca 

Lockowanie

Wyzwalacze / Triggers

Pomiędzy TRUNCATE-em a DELETE-em jest jeszcze kilka różnic, które są związane z ich obsługą pomiędzy bazami danych np. kwestie indeksów, kluczy obcych, kolumn typu IDENTITY.

Jednak są to już dość specyficzne kwestie, jak chcesz zgłębić temat, odwołałbym się do konkretnych dokumentacji systemów, na których pracujesz.

Do czego służą funkcje CEIL oraz FLOOR? Wyjaśnij zasadę działania  obu funkcji na przykładzie.   #podstawy #teoria

Funkcje CEIL oraz FLOOR pozwalają na zaokrąglenie danej liczby do części całkowitej. 


Pamiętasz pewnie z matematyki kwestie zaokrąglania. Mając liczbę 254 gdy chcemy zaokrąglić ją do pełnych dziesiątek, dostaniemy w wyniku liczbę 250. Gdy mamy np. 255 to dostaniemy 260. 


CEIL (lub CEILING) oraz FLOOR. W “wolnym” tłumaczeniu sufit i podłoga i już się domyślasz co do czego.


Funkcja CEIL (CEILING) zwraca najmniejszą liczbę całkowitą większą lub równą, względem podanej na wejściu wartości.


SELECT 8.28 as wartosc_wyjsciowa, 

               CEILING(8.28) as wartosc_pozytywna, 

               CEILING(-8.28) as wartosc_negatywna;


Wynik:

wartosc_wyjsciowa: 8.28

wartosc_pozytywna: 9

wartosc_negatywna: -8


Funkcja FLOOR zwraca największą liczbę całkowitą mniejszą lub równą, względem podanej na wejściu wartości.


SELECT 8.28 as wartosc_wyjsciowa, 

               FLOOR(8.28) as wartosc_pozytywna, 

               FLOOR(-8.28) as wartosc_negatywna;


Wynik:

wartosc_wyjsciowa: 8.28

wartosc_pozytywna: 8

wartosc_negatywna: -9

Czym jest funkcja ROUND? Jaka jest różnica w wyniku ROUND vs CEIL / FLOOR.   #podstawy #teoria

Funkcja ROUND podobnie jak CEIL i FLOOR pozwala na zaokrąglenie wartości. Jednak zaokrąglenie bardziej "po matematycznemu” (czyli jak 5 to w górę a jak mniej niż 5 to w dół). 


Dodatkowo przy tej funkcji możesz doprecyzować, do ilu miejsc po przecinku, wartość wyjściowa ma zostać zaokrąglona. 


SELECT 8.49 as wartosc_wyjsciowa, 

               FLOOR(8.28) as wartosc_floor, 

               CEIL(8.28) as wartosc_ceil,

               ROUND(8.49) as wartosc_zaokraglona


wartosc_wyjsciowa: 8.49

wartosc_floor: 8

wartosc_ceil: 9

wartosc_zaokraglona: 8 (niepodane zostało miejsce zaokrągleń po przecinku, domyślnie 0 miejsc po przecinku)


SELECT 8.51 as wartosc_wyjsciowa, 

               FLOOR(8.51) as wartosc_floor, 

               CEIL(8.51) as wartosc_ceil,

               ROUND(8.51) as wartosc_zaokraglona


wartosc_wyjsciowa: 8.49

wartosc_floor: 8

wartosc_ceil: 9

wartosc_zaokraglona: 9 (niepodane zostało miejsce zaokrągleń po przecinku, domyślnie 0 miejsc po przecinku)


UWAGA:

Jakiego typu jest wartość zwracana przez daną funkcję (CEIL / FLOOR / ROUND)?


To zależy od silnika bazodanowego, który wykorzystujesz.

W przypadku PostgreSQL zwracane typy dla funkcji to:

  • CEIL - taki sam typ jak typ wejściowy
  • FLOOR - taki sam typ jak typ wejściowy
  • ROUND - typ numeryczny

Zerknij do dokumentacji silnika, z którego korzystasz na co dzień, żeby mieć pewność.

Czym jest operator ANY? Opisz zasadę działania na przykładzie.   #podstawy #teoria

Operator ANYsłuży do sprawdzenia, czy w przeszukiwanym podzbiorze danych (podzapytaniu) znajduje się przynajmniej jedna wartość spełniająca wykorzystany operator. Możesz go użyć w sekcji WHERE oraz / lub w sekcji HAVING.

Składnia:


operator - to jeden z dostępnych operatorów logicznych =, <>, !=, >, >=, <, or <=


Przykład:

Wyświetlenie diet, wśród których dla posiłków jednym ze składników jest owoc pomarańczy.

CREATE TABLE diet_menu (
diet_name text,
diet_ingredients text[]
);INSERT INTO diet_menu VALUES ('diet1', '{apple, orange, banana}'); INSERT INTO diet_menu VALUES ('diet2', '{apple, banana}');SELECT * FROM diet_menu WHERE 'orange' = ANY (SELECT UNNEST(diet_ingredients));

Uwaga:

Z operatorem ANY wiąże się również operator SOME, który uwaga jest takim samym operatorem jak ANY.


Co do zasady działania operatory są takie same. Pierwszy standard SQL wszedł w życie w 1986, a sam SQL swoje początki datuje na lata 1970+, stąd 2 operatory, być może jakaś wsteczna kompatybilność?

Czym jest operator ALL? Opisz zasadę działania na przykładzie.   #podstawy #teoria

Operator ALLsłuży do sprawdzenia, czy wszystkie wiersze przeszukiwanego podzbioru danych (podzapytania) spełniają wykorzystany operator. Możesz go użyć w sekcji WHERE oraz / lub w sekcji HAVING.

Składnia:



operator - to jeden z dostępnych operatorów logicznych =, <>, !=, >, >=, <, or <=


Przykład:

Wyświetlenie diet, wśród których dla posiłków jednym ze składników nie jest arbuz.

CREATE TABLE diet_menu (
diet_name text,
diet_ingredients text[]
);INSERT INTO diet_menu VALUES ('diet1', '{apple, orange, banana}');
INSERT INTO diet_menu VALUES ('diet2', '{apple, banana}');SELECT *
FROM diet_menu
WHERE 'watermelon' <> ALL (SELECT UNNEST(diet_ingredients));

Uwaga:

Z operatorem ANY wiąże się również operator SOME, który uwaga jest takim samym operatorem jak ANY.


Co do zasady działania operatory są takie same. Pierwszy standard SQL wszedł w życie w 1986, a sam SQL swoje początki datuje na lata 1970+, stąd 2 operatory, być może jakaś wsteczna kompatybilność?

Czym jest operator EXISTS? Opisz zasadę działania na przykładzie. Czy składnie EXISTS i = ANY są równoważne?   #podstawy #teoria

Operator EXISTS, służy do wyszukiwania wierszy, które spełniają warunek użyty w podzapytaniu. Enigmatyczny opis, a sprowadza się do tego, że w wyniku dostaniesz rekordy istniejące w podzapytaniu użytym do zbudowania EXIST-a. Operator EXISTS zwraca TRUE, gdy podzapytanie zwróci 1 lub więcej rekordów.

Składnia:


Dlaczego nie JOIN?
JOIN według swojej definicji, służy do łączenia tabel i wyświetlania w wyniku kolumn z różnych obiektów (tabel). EXISTS ma za zadanie "odfiltrować" finalny wynik na podstawie jakiegoś podzapytania.


Przykład:

Wyświetl wszystkie produkty (tabela PRODUCTS), które wzięły udział w transakcji sprzedażowej (tabel SALES)

SELECT p.*
FROM products p
WHERE EXISTS (SELECT 1
FROM sales s
WHERE s.product_id = p.product_id);

Co EXISTS ma wspólnego z ANY / SOME.

Załóżmy, że chcemy wyświetlić tylko produkty, dla których istnieje transakcja sprzedażowa. Możemy to zrobić za pomocą EXIST-a, ale również za pomocą ANY.

-- EXISTS
SELECT p.*
FROM products p
WHERE EXISTS (SELECT 1
FROM sales s
WHERE s.product_id = p.product_id);-- ANY
SELECT p.*
FROM products p
WHERE p.product_id = ANY (SELECT s.product_id FROM sales s);

Jaka jest wiec różnica?
Plan zapytania jest identyczny w obu przypadkach. Jeżeli warunek logiczny jest ten sam, ANY / SOME i EXISTS są zamienne.

Hash Join (cost=201.25..226.00 rows=100 width=41) (actual time=4.312..4.597 rows=99 loops=1)
Hash Cond: (p.product_id = s.product_id)
-> Seq Scan on products p (cost=0.00..20.00 rows=1000 width=41) (actual time=0.013..0.138 rows=1000 loops=1)
-> Hash (cost=200.00..200.00 rows=100 width=4) (actual time=4.278..4.278 rows=100 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 12kB
-> HashAggregate (cost=199.00..200.00 rows=100 width=4) (actual time=4.231..4.240 rows=100 loops=1)
Group Key: s.product_id
-> Seq Scan on sales s (cost=0.00..174.00 rows=10000 width=4) (actual time=0.012..2.071 rows=10000 loops=1)
Planning time: 0.431 ms
Execution time: 4.677 ms

W jaki sposób ograniczyć ilość wierszy wynikowych do np. 10 dowolnych elementów. Podaj przykłady na podstawie znanych Ci systemów baz danych, jak również składnie SQL według standardu ISO.   #podstawy #teoria

Prawdopodobnie znasz z relacyjnej bazy danych, na której pracujesz na co dzień składnie:

  • LIMIT (m.in. MySQL, PostgreSQL, SQLite, H2)
  • TOP (m.in. MS SQL Server)
  • ROWNUM (Oracle) - pseudo kolumna w bazie Oracle, tworzona w momencie generowania wyników określająca kolejność pobranych wierszy

Przykłady:

    SELECT * FROM tab LIMIT 10;

    SELECT * FROM tab WHERE ROWNUM <= 10;

    SELECT TOP 10 * FROM tab;


Dlaczego, pomiędzy bazami danych są różnice, w takim wydawałoby się prostym elemencie?


Poniekąd byli do tego zmuszeni. Pierwszy raz taka funkcjonalność została wprowadzona do standardu razem z wersją SQL:2008.


FETCH FIRST n ROWS ONLY bo o niej mowa, pozwala na pobranie jedynie n (gdzie n to liczba lub wynik operacji np. (2+2)) wierszy z całego zbioru wynikowego.


Przykład:

    SELECT * FROM tab FETCH FIRST 10 ROWS ONLY;

Jaki jest odpowiednik instrukcji warunkowej IF w SQL-u. Podaj przykład i wyjaśnij zasadę działania. Rozwiązanie powinno opierać się o standard, nie o implementację dla danej bazy danych.   #podstawy #teoria

Instrukcja warunkowa (IF) to popularna konstrukcja w językach programowania, pozwalająca na sprawdzenie warunku i wykonanie odpowiedniej akcji w zależności od tego czy warunek jest spełniony, czy też nie.


Składnia (podstawowa - w językach programowania - jako pseudokod)


IF <WARUNEK LOGICZNY>

THEN  <AKCJA DO WYKONANIA JAK WARUNEK JEST SPEŁNIONY>

ELSE  <AKCJA DO WYKONANIA JAK WARUNEK JEST NIESPEŁNIONY>

END 


Warunkiem logicznym może być dowolna funkcja / operator, który w wyniku da wartość TRUE lub FALSE. 


Konstrukcja jak powyżej w standardowym SQL-u nie istnieje. Istnieje natomiast w Proceduralnym SQL, tj. w procedurach składowanych i funkcjach wykonywanych w bazie danych.


 IF w SQL-u istnieje, kryje się pod operatorem CASE

  • warunek - to dowolna operacja, której wynikiem jest wartość logiczna TRUE / FALSE (operator logiczny >, <, =, <> etc.; wynik z podzapytania TRUE / FALSE itp.)
  • akcja - czynność która ma zostać wykonana po spełnieniu warunku np. wyświetlenie jakiejś kolumny, dodanie czegoś do wartości etc.

W operatorze CASE warunki są sprawdzane po kolei, po napotkaniu pierwszego warunku, który jest spełniony, wykonywana jest akcja, pozostałe warunki są pomijane. Jeżeli żaden z warunków nie jest spełniony, wykonywana jest akcja z sekcji ELSE


Jeżeli ELSE nie został zadeklarowany, zwrócona zostanie wartość nieokreślona - NULL.


Przykład:



Operator CASE możesz wykorzystać w samej składni SELECT, w WHERE, w ORDER BY, GROUP BY etc. Jednak taki operator to zawsze dodatkowy narzut w momencie wykonania (na wykonanie instrukcji sprawdzenia + wykonanie akcji), co będzie miało wpływ na wydajność. W niektórych przypadkach warto rozważyć wyliczanie kolumn z operatorem CASE wcześniej (jako element w procesie poprzedzającym wykorzystanie), dodając odpowiednie indeksowanie i następnie wykorzystując wyliczoną kolumnę w SELECT-ie. 


Uwaga: W niektórych baza danych IF istnieje np. MySQL, lub jako IIF - Firebird / SQL Server. Jednak nie jest standardem SQL a nakładką na konkretnej bazie danych.

Do czego służy składnia ORDER BY? Jakie dodatkowe opcje wykonania może zawierać (obsługa wartości NULL / kierunek)?   #podstawy #teoria

Operacja ORDER BY służy do sortowania danych wynikowych. 


Czy wyniki mają jakiekolwiek sortowanie bez ORDER BY.

Nie. W przypadku niepodania klauzuli ORDER BY wyniki zapytania zostaną zwrócone w nieokreślonej formie. Może się zdarzyć, że pojawią się w tej samej kolejności, jak zostały dodane do tabeli źródłowej, ale uważaj i nie bierz tego za pewnik.

Chcesz otrzymać wiersze w żądanej kolejności, używaj ORDER BY.

Składnia


SELECT 
FROM
ORDER BY < NAZWA_KOLUMNY/FUNKCJI> < DESC | ASC>
< NULLS FIRST | NULLS LAST >


  • NAZWA_KOLUMNY/FUNKCJI - nazwa / nazwy kolumn lub funkcji, po których ma zostać wykonane sortowanie
  • DESC - sortuj w porządku malejącym
  • ASC - sortuj w porządku rosnącym
  • NULLS FIRST - pokazuj wartości nieokreślone na początku zbioru wynikowego
  • NULLS LAST - pokazuj wartości nieokreślone na końcu zbioru wynikowego

Porządek sortowania DESC / ASC możesz określić dla każdej kolumny / funkcji użytej przy sortowaniu osobno. Domyślnym porządkiem sortowania jest sortowanie rosnące - ASC.

Elementy użyte w składni ORDER BY nie muszą być w części SELECT.

ORDER BY jest jednym z ostatnich logicznych elementów, które zostaną wykonane. Dlatego przy sortowaniu możemy sobie pozwolić na używanie aliasów - zamiast rzeczywistych nazw kolumn lub kolejności pól zamiast ich nazw (np. ORDER BY 1, 2, 3, gdzie 1, 2, 3 to kolejne kolumny po składni SELECT)

Czym jest złączenie (JOIN) w języku zapytań do baz danych SQL? Jakie znasz rodzaje złączeń?   #podstawy #teoria #join

Złączenie to powiązanie tabel (lub tabeli), na podstawie pewnego warunku (tych samych kolumn, operatorów logicznych >, < etc.).

W najprostszym przypadku złączenia używamy do odwzorowania relacji pomiędzy tabelami, gdy chcemy dostać określony wynik.

Rodzaje złączeń:

  • (INNER) JOIN
  • LEFT (OUTER) JOIN
  • RIGHT (OUTER) JOIN
  • FULL (OUTER) JOIN

(INNER) / (OUTER) w nawiasach oznaczają fakt, że ten fragment jest opcjonalny dla SQL tzn. LEFT OUTER JOIN = LEFT JOIN.

Czym jest łączenie zbiorów za pomocą operacji UNION UNION ALL? Jaka jest różnica pomiędzy tymi operacjami (UNION vs UNION ALL)?   #podstawy #teoria #union #unionall

UNION - jest operacją łączenia zbiorów danych. Możesz jej użyć, do połączenia rekordów z różnych tabel w 1 zbiór rekordów.

Po co?
Zwykle, gdy potrzebujesz zbudować listę wartości z różnych zbiorów (tabel).

Przykład:

  • ilość kolumn w obu zapytaniach musi być identyczna
  • typy danych muszą być kompatybilne w odpowiadających sobie kolumnach - (w przykładzie powyżej car_manufacturer nie może być raz wartością numeryczną, a raz tekstową)
  • w celu uzyskania posortowanego rezultatu użyj ORDER BY - możesz go użyć tylko w ostatnim SELECT-cie. ORDER BY zadziała na całym zbiorze wynikowym. Jeżeli chcesz otrzymać posortowane wyniki tylko z pierwszego zapytania, zastosuj podzapytanie dla 1 SELECT-a

UNION vs UNION ALL

  • UNION - złączy wyniki usuwając przy tym powielające się wiersze (taki DISTINCT)
  • UNION ALL - złączy wyniki nie usuwając powielających się wierszy

Różnica jest znacząca i będzie zauważalna szczególnie na większych zbiorach danych. Operacja usuwania duplikatów jest kosztowna, dlatego warto przed użyciem operacji UNION sprawdzić i zweryfikować czy istnieje możliwość pojawienia się tych samych wartości w złączanych zbiorach.


Uwaga:

W niektórych bazach danych UNION oprócz usuwania duplikatów sortuje również wynikowy zbiór danych (np. SQL Server).

Jaka jest różnica między INNER JOIN a NATURAL JOIN?   #podstawy #teoria #join

INNER JOIN jest typem złączenia, w którym z dwóch zbiorów (tabel czy podzapytań) bierzemy te wartości (wiersze), dla których zdefiniowany klucz złączenia jest spełniony. Oznacza to, że wartości w kolumnach ze zbiorów użytych w złączeniu mają te same wartości (w przypadku wykorzystania “=” kolumn w JOIN-ie).



Wizualizacja Inner JOIN

NATURAL JOIN jest typem złączenia, w którym z dwóch zbiorów (tabel czy podzapytań) bierzemy tylko ich część wspólną, ale jedynie w sytuacji gdy w obu zbiorach istnieje tak samo nazwana kolumna (lub więcej tak samo nazwanych kolumn).

Wizualizacja Natural JOIN

Jaka jest różnica między LEFT JOIN a RIGHT JOIN?   #podstawy #teoria #join

RIGHT OUTER JOIN jest typem złączenia, w którym z dwóch zbiorów (tabel czy podzapytań) bierzemy wszystkie elementy z 2 zbioru i elementy pasujące (według użytego klucza złączenia) z pierwszego zbioru. Stąd nazwa RIGHT (w odniesieniu do kierunku złączenia) i OUTER mówiące o typie złączenia zewnętrznego (nie tylko część wspólna).


Wizualizacja RIGHT OUTER JOIN

LEFT OUTER JOIN jest typem złączenia, w którym z dwóch zbiorów (tabel czy podzapytań) bierzemy wszystkie elementy z 1 zbioru i elementy pasujące (według użytego klucze złączenia) z drugiego zbioru. Stąd nazwa LEFT (w odniesieniu do kierunku złączenia) i OUTER mówiące o typie złączenia zewnętrznego (nie tylko część wspólna).


Wizualizacja LEFT OUTER JOIN

Czym jest złączenie CROSS JOIN?   #podstawy #teoria #join

Używając CROSS JOIN, w wyniku dostaniemy iloczyn kartezjański zbiorów (tabel). Każdy wiersz z tabeli A zostanie „połączony” z każdym wierszem z tabeli B.

Sprawdzi się w sytuacjach - świadomego połączenia wszystkich wierszy między sobą np. wyświetlenie każdego z produktów w każdym z regionów.

Wizualizacja Cross JOIN

Co oznaczają złączenia EQUI / NON-EQUI i SELF JOIN?   #podstawy #teoria #join

EQUI JOIN - dowolny typ złączenia, w którym w kluczu złączenia używamy znaku =.

NON-EQUI JOIN
- dowolny typ złączenia, w którym w kluczu złączenia nie używamy znaku = (ale np. <. >, >=, =<, !=).

SELF JOIN
- typ złączenia, w którym łączymy między sobą ten sam zbiór danych / tabele.

Jakie znasz wewnętrzne typy złączeń zbiorów przy wykonywaniu zapytania SQL (algorytmy złączeń). Patrząc, z perspektywy planu wykonania zapytania?   #podstawy #teoria #join

Wewnętrzne czy czasami nazywane fizycznymi typami złączeń, lub ogólniej algorytmy złączenia. To złączenia, które są wykorzystywane przez silnik bazy danych do faktycznego łączenia danych (abstrahując od tego, co użytkownik użył w zapytaniu LEFT / INNER JOIN).


Trzy podstawowe typy:

  • NESTED LOOP
  • MERGE JOIN
  • HASH JOIN

Czym jest typ łączenia danych metodą NESTED LOOP?   #podstawy #teoria #join

Mając dwa zbiory do złączenia N i M, w najprostszym (najgorszym) scenariuszu, każdy rekord ze zbioru N, musi zostać sprawdzony względem rekordów w zbiorze M.


Stąd nazwa "nested loop", przechodzimy przez elementy zbioru M, iterując elementy zbioru N.


W zależności od sytuacji złożoność tego algorytmu może być:

  • O(N*M) gdy na kluczu złączenia zbiorów nie mamy indeksu
  • O(M*logN) gdy na jednym z kluczy złączenia mamy założony indeks typu B-Tree
  • O(M) gdy na jednym z kluczy złączenia mamy założony hash indeks.

Zwykle wykorzystywany gdy jeden ze zbiorów złączenia jest "zdecydowanie" mniejszy od drugiego.


Przykład na podstawie tabel PRODUCTS i SALES:

CREATE INDEX product_id_sales_idx ON sales (product_id);
CREATE INDEX product_name_products_idx ON products (product_name);EXPLAIN ANALYZE
SELECT *
FROM products p
JOIN sales s ON s.product_id = p.product_id
AND p.product_name = 'Product 1';QUERY PLAN
Nested Loop (cost=4.77..68.41 rows=28 width=1148) (actual time=0.369..2.142 rows=1236 loops=1)
-> Index Scan using product_name_products_idx on products p (cost=0.27..8.29 rows=1 width=1068) (actual time=0.029..0.098 rows=101 loops=1)
Index Cond: ((product_name)::text = 'Product 1'::text)
-> Bitmap Heap Scan on sales s (cost=4.50..59.85 rows=28 width=80) (actual time=0.005..0.016 rows=12 loops=101)
Recheck Cond: (product_id = p.product_id)
Heap Blocks: exact=685
-> Bitmap Index Scan on product_id_sales_idx (cost=0.00..4.49 rows=28 width=0) (actual time=0.003..0.003 rows=12 loops=101)
Index Cond: (product_id = p.product_id)
Planning time: 0.498 ms
Execution time: 2.281 ms

Czym jest typ łączenia danych metodą MERGE JOIN?   #podstawy #teoria #join

Chcąc złączyć dwa zbiory N i M. Algorytm MERGE JOIN zostanie wykorzystany w momencie gdy zbiory użyte w złączenie są posortowane według klucza złączenia i zostanie wykorzystany operator równości w kluczu złączenia.


Co to oznacza w praktyce? Gdy mamy posortowane dwa zbiory sprawdzanie równości jest bardzo uproszczone.


Patrząc na zbiór N i mając wartość 1, szukamy tej wartości w posortowanym zbiorze M. Bierzemy pierwszy element czy jest on równy 1, nie, czy jest on większy, tak - nie sprawdzamy kolejnych elementów. Dalej bierzemy kolejny rekord ze zbioru N. Taka operacja powtarza się, dopóki wszystkie elementy z jednego ze zbiorów nie zostaną sprawdzone (Dlaczego z jednego? Skoro zbiory są posortowane i przeszukaliśmy wszystko ze zbioru np. N to zbiór M, już nas niczym nowym nie zaskoczy, bo niesprawdzone wartości i tak nie spełnią warunków złączenia).


Złożoność tego algorytmu to O(N+M).


Świetny przy złączeniach "dużych" tabel, gdy oba zbiory są posortowane według klucza złączenia i warunku złączenia użyty jest operator równości "=".


Przykład na podstawie tabel PRODUCTS i SALES:

EXPLAIN ANALYZE
SELECT *
FROM (SELECT *
FROM products
ORDER BY product_id) p
JOIN (SELECT *
FROM sales
ORDER BY product_id) s ON s.product_id = p.product_id;QUERY PLAN
Merge Join (cost=909.82..1184.70 rows=10000 width=97) (actual time=3.885..8.134 rows=9893 loops=1)
Merge Cond: (products.product_id = sales.product_id)
-> Sort (cost=69.83..72.33 rows=1000 width=41) (actual time=0.249..0.260 rows=100 loops=1)
Sort Key: products.product_id
Sort Method: quicksort Memory: 103kB
-> Seq Scan on products (cost=0.00..20.00 rows=1000 width=41) (actual time=0.014..0.110 rows=1000 loops=1)
-> Materialize (cost=838.39..988.39 rows=10000 width=56) (actual time=3.556..6.009 rows=10000 loops=1)
-> Sort (cost=838.39..863.39 rows=10000 width=56) (actual time=3.550..4.883 rows=10000 loops=1)
Sort Key: sales.product_id
Sort Method: quicksort Memory: 1166kB
-> Seq Scan on sales (cost=0.00..174.00 rows=10000 width=56) (actual time=0.009..1.003 rows=10000 loops=1)
Planning time: 1.770 ms
Execution time: 8.658 ms

Czym jest typ łączenia danych metodą HASH JOIN?   #podstawy #teoria #join

Chcąc złączyć dwa zbiory N i M, gdy wykorzystany zostanie algorytmy HASH JOIN.


Baza danych dla mniejszego zbioru, wyznaczy skróty (hash-e) dla kluczy złączenia i zbuduję tabelę skrótów (połączenie wyznaczonego skrótu z rzeczywistym rekordem tabeli N).

Następnie w pętli dla każdego rekordu z tabeli M, dla klucza złączenia zostanie wyznaczony skrót (hash) i porównany ze zbudowaną tabelą skrótów.


Tabela skrótów zwykle jest przechowywana w pamięci, jednak gdy zbiór danych jest bardzo dużo, ilość miejsca w pamięci może się okazać nie wystarczająco, przez co część tabeli skrótów, zostanie zrzucona na dysk i stamtąd będzie odczytywana (co znacznie spowolni odczyt / złączenie).


Złożoność obliczeniowa tego algorytmu przy założeniu, że tabela skrótów zmieści się w pamięci i dodatkowe zasoby nie będą wykorzystane to: O(N+M).


Przykład na podstawie tabel PRODUCTS i SALES:

EXPLAIN ANALYZE
SELECT *
FROM products p
JOIN sales s ON s.product_id = p.product_id
AND p.product_name = 'Product 1';QUERY PLAN
Hash Join (cost=23.65..244.35 rows=920 width=97) (actual time=0.231..2.355 rows=996 loops=1)
Hash Cond: (s.product_id = p.product_id)
-> Seq Scan on sales s (cost=0.00..174.00 rows=10000 width=56) (actual time=0.011..0.959 rows=10000 loops=1)
-> Hash (cost=22.50..22.50 rows=92 width=41) (actual time=0.193..0.193 rows=92 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 15kB
-> Seq Scan on products p (cost=0.00..22.50 rows=92 width=41) (actual time=0.016..0.181 rows=92 loops=1)
Filter: ((product_name)::text = 'Product 1'::text)
Rows Removed by Filter: 908
Planning time: 0.404 ms
Execution time: 2.445 ms

Od czego może zależeć wybór metody złączenia (NESTED LOOP, MERGE JOIN, HASH JOIN) przez optymalizator w bazie danych?   #podstawy #teoria #join

Od magii wykorzystywanej przez Ciebie bazy danych.


A tak serio to od wielu, możemy do nich zaliczyć:

  • wielkości zbiorów danych użytych w złączeniu
  • operatora klucza złączenia (czy jest to równość "=", czy inny operator)
  • od tego czy na kluczach złączenia są założonego indeksy
  • czy statystyki tabel użytych w złączeniu są aktualne (przeliczone)

Nie ma niestety, jednej doskonałej odpowiedzi na to, który typ złączenia jest najlepszy. Można oczywiście wskazać te z lepszą złożonością obliczeń, jednak w rzeczywistym świecie, każdy przypadek należy rozpatrywać osobno.


Warto analizować plan wykonania zapytania (EXPLAIN / EXPLAIN ANALYZE) i momentami poddawać w wątpliwość przedstawiony przez optymalizator bazy danych sposób łączenia zbiorów.

Czym jest duplikat w danych?   #podstawy #teoria

Duplikatem w danych nazywamy przynajmniej 2 takie wiersze wynikowe, dla których wszystkie kolumny mają takie same wartości.


Przykład:
PRODUCT_NAME | PRODUCT_CATEGORY
       Product 1        |           Category 1
       Product 1        |           Category 1

Czym jest NULL w relacyjnych bazach danych? Jakie znasz sposoby radzenia sobie z NULL-em przy ograniczaniu danych wynikowych (WHERE)?  #podstawy #teoria

NULL to swego rodzaju znacznik w języku SQL i w relacyjnych bazach danych, wskazujący na fakt, że dana wartość jest nieokreślona (nie ma wartości w danych źródłowych).


Uwaga: NULL nie jest 0 dla liczb czy pustym tekstem dla wartości tekstowych.


Do sposobów radzenia sobie z NULL-em możemy zaliczyć:

  • porównania warunkowe IS NULL / IS NOT NULL
  • COALESCE() - funkcja, która w przypadku wystąpienia wartości NULL, zamieni ją na podaną wartość np. COALESCE(product_name, 'unknown_product');
  • CASE - składnia pozwalająca na zbudowanie warunkowego podejścia do wartości kolumny / funkcji CASE WHEN product_name IS NULL THEN 'unknown_product' ELSE product_name END

Zwróć uwagę, że pomimo faktu, iż COALESCE czy CASE są elementu standardu ISO dla SQL-a, nie muszą być wspierane przez wszystkie bazy danych.


Są też inne alternatywy np. NULLIF(), IFNULL(), ISNULL(), NVL() - których zadaniem jest spełnianie tej samej lub podobnej funkcji tj. uporanie się z wartością nieokreśloną.

Co, można zastosować w sytuacji gdy w tabeli nie chcemy, by wartości w kolumnie były nieokreślone, a jednocześnie nie pojawiał się błąd w trakcie próby INSERT-u wartości NULL?  #podstawy #teoria

Dodanie ograniczenia (CONSTRAINT) typu NOT NULL z dodatkową wartością domyślną DEFAULT ''.


Uwaga: nie zawsze musi to być pożądane rozwiązanie. Czasem wartości NULL są jak naj bardziej OK i takie ograniczenie pozwoli nam sobie z nimi radzić w tabeli, ale przy budowaniu SELECT-a ze złączeniami i tak możemy otrzymać wartości nieokreślone.

CREATE TABLE products_constraint (
product_id SERIAL,
product_name varchar(500),
product_category varchar(250) NOT NULL DEFAULT '<unknown>',
random_desc text
);
INSERT INTO products_constraint (product_name,random_desc)
VALUES ('Product New Special One', 'Special Constraint Product');
SELECT * FROM products_constraint;
product_idproduct_nameproduct_categoryrandom_desc
1Product New Special One<unknown>Special Constraint Product

Czym jest operator LIKE? W jakich sytuacjach go używamy i jakie są podstawowe znaki specjalne wykorzystywane przez ten operator?  #podstawy #teoria

Operatora LIKE używamy w sekcji WHERE zapytania, aby znaleźć / odfiltrować wiersze wynikowe według wybranego wzoru w danych tekstowych.

Wynik operatora na danym wierszu to TRUE lub FALSE w zależności od tego czy dany wzór pasuje do wyniku (TRUE) lub nie pasuje (FALSE). W sytuacji odwrotnej, gdy chcemy znaleźć dane niepasujące do wzoru, skorzystamy z operatora NOT LIKE.


Operator LIKE, może korzystać z 2 znaków specjalnych:

  • % - znak procenta oznacza wystąpienie 0,1 lub więcej znaków (dowolnych) w szukanym wzorze
  • _ - podkreślenie (dolna kreska), oznacza wystąpienie 1 znaku (dowolnego)

Niektóre bazy danych rozszerzają zbiór znaków specjalnych o inne, np.:

  • [] - nawiasy kwadratowe - dowolny ze znaków zawartych w nawiasach kwadratowych (SQL Server)
  • ^ - “daszek” oznacza zaprzeczenie np. [^cb] wszystkie znaki z wyjątkiem znaku c lub b w wyrazie
  • - - myślnik oznacza zakres znaków, np. [a - e]

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql?view=sql-server-ver15


Lub dodają inne zbliżone operatory np. ILIKE (PostgreSQL) czy SIMILAR (PostgreSQL).

https://www.postgresql.org/docs/12/functions-matching.html#FUNCTIONS-LIKE

Na podstawie danych sprzedażowych (tabela SALES), znajdź listę unikatowych istniejących nazw produktów (tabela PRODUCTS, kolumna PRODUCT_NAME) #praktyka #skladnia

SELECT DISTINCT
p.product_name as unq_product_name
FROM sales s
JOIN products p on p.product_id< = s.product_id;

Inne podejście

SELECT count(*) as unq_product_count
FROM (SELECT DISTINCT
p.product_name
FROM sales s
JOIN products p on p.product_id = s.product_id
) sq;

Na podstawie danych sprzedażowych (tabela SALES), znajdź liczbę unikatowych istniejących nazw produktów (tabela PRODUCTS, kolumna PRODUCT_NAME) #praktyka #skladnia #antywzorce

SELECT count(distinct p.product_name) as unq_product_count
FROM sales s
JOIN products p on p.product_id = s.product_id;

Typowy błąd: DISTINCT poza COUNT-em nie uwzględni zduplikowanych wierszy wynikowych, pokazując tylko product_name nie widzimy całości wiersza. COUNT jest robiony na całym zbiorze wynikowym.

SELECT DISTINCT
count(p.product_name) as not_so_unq_product_count
FROM sales s
JOIN products p on p.product_id = s.product_id;

Nie korzystając z klauzuli DISTINCT, znajdź listę unikatowych produktów (kolumna PRODUCT_NAME) na podstawie danych sprzedażowych (tabela SALES) #praktyka #skladnia

SELECT product_name as unq_product_name
FROM (SELECT p.product_name,
row_number() over (partition by p.product_name) as rn
FROM sales s
JOIN products p on p.product_id = s.product_id) sq
WHERE rn = 1;

Wykorzystanie funkcji okna (WINDOW FUNCTION - OVER()) + iteratora wiersza row_number()

DISTINCT vs ROW_NUMBER() OVER(), które rozwiązanie na podstawie dostępnych danych okaże się szybsze? Sprawdź wyniki z wykorzystaniem planu zapytania. #praktyka #wydajnosc

EXPLAIN ANALYZE
SELECT DISTINCT
p.product_name as unq_product_name
FROM sales s
JOIN products p on p.product_id = s.product_id;
/* QUERY PLAN
HashAggregate (cost=369.00..369.10 rows=10 width=10) (actual time=5.632..5.633 rows=10 loops=1)
Group Key: p.product_name
-> Hash Join (cost=32.50..344.00 rows=10000 width=10) (actual time=0.386..4.036 rows=9899 loops=1)
Hash Cond: (s.product_id = p.product_id)
-> Seq Scan on sales s (cost=0.00..174.00 rows=10000 width=4) (actual time=0.012..1.236 rows=10000 loops=1)
-> Hash (cost=20.00..20.00 rows=1000 width=14) (actual time=0.348..0.348 rows=1000 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 55kB -> Seq Scan on products p (cost=0.00..20.00 rows=1000 width=14) (actual time=0.010..0.180 rows=1000 loops=1)
Planning time: 0.298 ms
Execution time: 5.694 ms
*/
EXPLAIN ANALYZE
SELECT product_name as unq_product_name
FROM (SELECT p.product_name,
row_number() over (partition by p.product_name) as rn
FROM sales s
JOIN products p on p.product_id = s.product_id) sq
WHERE rn = 1;
/* QUERY PLAN
Subquery Scan on sq (cost=1008.39..1308.39 rows=50 width=10) (actual time=12.051..18.867 rows=10 loops=1)
Filter: (sq.rn = 1)
Rows Removed by Filter: 9889
-> WindowAgg (cost=1008.39..1183.39 rows=10000 width=18) (actual time=12.045..17.678 rows=9899 loops=1)
-> Sort (cost=1008.39..1033.39 rows=10000 width=10) (actual time=12.039..13.206 rows=9899 loops=1)
Sort Key: p.product_name
Sort Method: quicksort Memory: 849kB
-> Hash Join (cost=32.50..344.00 rows=10000 width=10) (actual time=0.531..4.770 rows=9899 loops=1)
Hash Cond: (s.product_id = p.product_id)
-> Seq Scan on sales s (cost=0.00..174.00 rows=10000 width=4) (actual time=0.009..1.103 rows=10000 loops=1)
-> Hash (cost=20.00..20.00 rows=1000 width=14) (actual time=0.493..0.493 rows=1000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 55kB
-> Seq Scan on products p (cost=0.00..20.00 rows=1000 width=14) (actual time=0.008..0.225 rows=1000 loops=1)
Planning time: 0.342 ms
Execution time: 19.018 ms
*/

Na podstawie danych sprzedażowych (tabela SALES), znajdź listę produktów, które nie zostały zakupione z wykorzystaniem składni IS NULL. #praktyka #skladnia

 SELECT *
FROM products p
LEFT JOIN sales s on s.product_id = p.product_id
WHERE s.id IS NULL;

Na podstawie danych sprzedażowych (tabela SALES), znajdź listę produktów, które nie zostały zakupione bez wykorzystania składni IS NULL. #praktyka #skladnia

SELECT *
FROM products p
WHERE product_id NOT IN (SELECT DISTINCT product_id FROM sales);

Lub z wykorzystaniem składni EXCEPT (MINUS w niektórych bazach danych).

SELECT p.*
FROM products p
EXCEPT
SELECT DISTINCT p.*
FROM sales s
JOIN products p ON s.product_id = p.product_id;

IS NULL vs NOT IN vs EXCEPT, zweryfikuj na podstawie dostępnych danych z wykorzystaniem planu zapytania, które rozwiązanie okaże się szybsze. Cel we wszystkich przypadkach jest ten sam, znalezienie listy produktów (tabela PRODUCTS), które nie zostały zakupione (tabel SALES) #praktyka #wydajnosc

EXPLAIN ANALYZE
SELECT p.*
FROM products p
LEFT JOIN sales s ON s.product_id = p.product_id
WHERE s.id IS NULL;/*
Hash Right Join (cost=3.25..35.00 rows=1 width=41) (actual time=0.451..0.453 rows=1 loops=1)
Hash Cond: (s.product_id = p.product_id)
Filter: (s.id IS NULL)
Rows Removed by Filter: 984
-> Seq Scan on sales s (cost=0.00..18.00 rows=1000 width=8) (actual time=0.007..0.117 rows=1000 loops=1)
-> Hash (cost=2.00..2.00 rows=100 width=41) (actual time=0.057..0.057 rows=100 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 16kB
-> Seq Scan on products p (cost=0.00..2.00 rows=100 width=41) (actual time=0.014..0.021 rows=100 loops=1)
Planning time: 0.425 ms
Execution time: 0.515 ms
*/EXPLAIN ANALYZE
SELECT *
FROM products p
WHERE product_id NOT IN (SELECT DISTINCT product_id FROM sales);/*
Seq Scan on products p (cost=21.75..24.00 rows=50 width=41) (actual time=0.555..0.555 rows=1 loops=1)
Filter: (NOT (hashed SubPlan 1))
Rows Removed by Filter: 99
SubPlan 1
-> HashAggregate (cost=20.50..21.50 rows=100 width=4) (actual time=0.445..0.464 rows=100 loops=1)
Group Key: sales.product_id
-> Seq Scan on sales (cost=0.00..18.00 rows=1000 width=4) (actual time=0.008..0.124 rows=1000 loops=1)
Planning time: 0.142 ms
Execution time: 0.600 ms
*/EXPLAIN ANALYZE
SELECT p.*
FROM products p
EXCEPT
SELECT DISTINCT p.*
FROM sales s
JOIN products p ON s.product_id = p.product_id;/*
HashSetOp Except (cost=0.00..52.00 rows=100 width=1072) (actual time=1.142..1.142 rows=1 loops=1)
-> Append (cost=0.00..50.00 rows=200 width=1072) (actual time=0.012..1.034 rows=199 loops=1)
-> Subquery Scan on "*SELECT* 1" (cost=0.00..3.00 rows=100 width=45) (actual time=0.011..0.037 rows=100 loops=1)
-> Seq Scan on products p (cost=0.00..2.00 rows=100 width=41) (actual time=0.009..0.016 rows=100 loops=1)
-> Subquery Scan on "*SELECT* 2" (cost=45.00..47.00 rows=100 width=45) (actual time=0.954..0.977 rows=99 loops=1)
-> HashAggregate (cost=45.00..46.00 rows=100 width=41) (actual time=0.953..0.967 rows=99 loops=1)
Group Key: p_1.product_id, p_1.product_name, p_1.product_category, p_1.random_desc
-> Hash Join (cost=3.25..35.00 rows=1000 width=41) (actual time=0.052..0.515 rows=984 loops=1)
Hash Cond: (s.product_id = p_1.product_id)
-> Seq Scan on sales s (cost=0.00..18.00 rows=1000 width=4) (actual time=0.006..0.108 rows=1000 loops=1)
-> Hash (cost=2.00..2.00 rows=100 width=41) (actual time=0.028..0.028 rows=100 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 16kB
-> Seq Scan on products p_1 (cost=0.00..2.00 rows=100 width=41) (actual time=0.003..0.010 rows=100 loops=1)
Planning time: 0.149 ms
Execution time: 1.272 ms
*/

Wyświelt listę wszystkich produktów (tabel PRODUCTS), wraz z datą ich sprzedaży (tabela SALES). W przypadku braku sprzedaży danego produktu datę przedstaw jako wartość 1900-01-01. #praktyka #skladnia

 SELECT p.*,
COALESCE(s.sales_date, '1900-01-01') as sales_date
FROM products p
LEFT JOIN sales s on s.product_id = p.product_id
ORDER BY s.sales_date NULLS FIRST;

Lub z wykorzystaniem składni CASE WHEN THEN

 SELECT p.*,
CASE WHEN s.sales_date IS NULL THEN '1900-01-01'
ELSE s.sales_date
END as sales_date
FROM products p
LEFT JOIN sales s on s.product_id = p.product_id
ORDER BY s.sales_date NULLS FIRST;

Znajdź wszystkie produkty (z tabeli PRODUCTS), których nazwa produktu (kolumna PRODUCT_NAME) kończy się na 1. #praktyka #skladnia

SELECT *
FROM products
WHERE product_name LIKE '%1';

Znajdź wszystkie produkty (z tabeli PRODUCTS), których opis (kolumna RANDOM_DESC) zaczyna się od a51. #praktyka #skladnia

SELECT *
FROM products
WHERE random_desc LIKE 'a51%';

Znajdź wszystkie produkty (z tabeli PRODUCTS), których nazwa produktu (kolumna PRODUCT_NAME) zawiera na końcu dokładnie 1 znak (dla zrozumienia, nazwa produktu składa się z części "Product " i cyfry lub liczby oznaczającej numer produktu, np. Product 1). #praktyka #skladnia

SELECT *
FROM products
WHERE product_name LIKE 'Product _';

Znajdź wszystkie identyfikatory produktów (kolumna PRODUCT_ID) oraz ich nazwy (kolumna PRODUCT_NAME), zarówno dla istniejących produktów jak i nieistniejących w tabeli słownikowej (PRODUCTS). Dla nieistniejących produktów, wstaw jako wartość nazwy produktu '<unknown>'. #praktyka #skladnia #join

 SELECT COALESCE(p.product_id, s.product_id) as product_id,
COALESCE(p.product_name,'<unknown>') as product_name
FROM sales s
LEFT JOIN products p on p.product_id = s.product_id;

Na podstawie danych sprzedażowych (tabela SALES), znajdź wszystkie unikatowe identyfikatory produktów (kolumna PRODUCT_ID) oraz ich nazwy (kolumna PRODUCT_NAME), tylko dla produktów istniejących w tabeli słownikowej PRODUCTS. #praktyka #skladnia #join

SELECT DISTINCT s.product_id, p.product_name
FROM sales s
JOIN products p on p.product_id = s.product_id;

Wyświetl 10 dowolnych wierszy na podstawie danych sprzedażowych (tabela SALES), dodając dla każdego wiersza informację o opisie produktu na podstawie 1 wybranego przez Ciebie produktu z tabeli słownikowej PRODUCTS. Dodatkowo wyświetl opis rzeczywistego produktu, który został sprzedany. Kolumnę z opisem produktu wybranego przez Ciebie nazwij, selected_product_description. Kolumnę z rzeczywistym opisem produktu sprzedane, nazwij valid_product_description. #praktyka #skladnia #join

WITH selected_product AS (
SELECT random_desc as selected_product_description
FROM products
WHERE product_name = 'Product 1'
LIMIT 1
)
SELECT s.*,
sp.selected_product_description,
p.random_desc as valid_product_description
FROM sales s
JOIN selected_product sp on 1=1
LEFT JOIN products p on p.product_id = s.product_id
LIMIT 10;

Na podstawie danych sprzedażowych (tabela SALES), wyświetl listę nazw produktów (kolumna PRODUCT_NAME), oraz liczbę ile razy zostały sprzedane. Dla produktów, które nie są dostępne w danych sprzedażowych, wyświetl nazwę produktu i wartość 0. Wyniki posortuj alfabetycznie według nazwy produktu. #praktyka #skladnia  #join

SELECT p.product_name,
count(s.id) as amount_sold
FROM sales s
RIGHT JOIN products p on p.product_id = s.product_id
GROUP BY p.product_name
ORDER BY p.product_name;

Wyświetl dane sprzedażowe, data sprzedaży (kolumna SALES_DATE), wartość sprzedaży (kolumna SALES_AMOUNT) oraz informację o produktach, nazwa produktu (kolumna PRODUCT_NAME), kategoria produktu (kolumna PRODUCT_CATEGORY), opis produktu (kolumna RANDOM_DESC) dla wszystkich dostępnych danych. Wynik posortuj malejąco według wartości sprzedaży. #praktyka #skladnia  #join

SELECT s.sales_date, s.sales_amount,
p.product_name, p.product_category
FROM sales s
FULL JOIN products p on p.product_id = s.product_id
ORDER BY p.product_name;

Wyświetl wszystkie produkty (tabela PRODUCTS), razem z informacją o dacie sprzedaży. Dane wynikowe posortuj według daty sprzedaży (kolumna SALES_DATE) od ostatniej (najbliższej dniu dzisiejszemu) do pierwszej sprzedaży. Wartości nieokreślone dla daty sprzedaży (kolumna SALES_DATE) powinny się znaleźć na końcu zbioru wynikowe. #praktyka #skladnia  #join #orderby

 SELECT s.sales_date, p.*
FROM products p
LEFT JOIN sales s ON s.product_id = p.product_id
ORDER BY s.sales_date DESC NULLS LAST;

Na podstawie danych sprzedażowych (tabela SALES), wyświetl wszystkie unikatowe nazwy produktów (kolumna PRODUCT_NAME), które przynajmniej raz brały udział w transakcji sprzedażowej. Dane wynikowe posortuje według alfabetycznie według nazwy produktu (kolumna PRODUCT_NAME).    #praktyka #skladnia #join #orderby

 SELECT DISTINCT p.product_name
FROM sales s
JOIN products p ON s.product_id = p.product_id
ORDER BY p.product_name;

Wyświetl wszystkie dane sprzedażowe (tabela SALES). Dane wynikowe posortuj według miesiąca sprzedaży na podstawie daty sprzedaży (kolumna SALES_DATE). W celu weryfikacji poprawności wyników dodaj do zbioru wynikowego kolumnę month_of_sales, która będzie miesiącem sprzedaży na podstawie daty sprzedaży.    #praktyka#skladnia  #orderby

 SELECT s.*,
EXTRACT(MONTH FROM s.sales_date) as month_of_sales
FROM sales s
ORDER BY month_of_sales;

Na podstawie danych sprzedażowych (tabela SALES) oblicz całkowitą sumę sprzedaży (kolumna SALES_AMOUNT) dla produktu o nazwie (kolumna PRODUCT_NAME) "Product 1". Wartość wynikową zaokrąglij do 1 miejsca po przecinku.    #praktyka #skladnia  #join #rounding

SELECT ROUND(SUM(s.sales_amount),1) as sum_of_sales
FROM sales s
JOIN products p ON p.product_id = s.product_id
AND p.product_name = 'Product 1';

Na podstawie danych sprzedażowych (tabela SALES) oblicz całkowitą liczbę sprzedanych towarów (kolumna SALES_QTY) dla produktu o nazwie (kolumna PRODUCT_NAME) "Product 2".
W wyniku przedstaw wartość zaokrąglona do najmniejszej liczby całkowitej większej lub równej od wartości wyjściowej - total_sales_qty_plus, oraz wartość zaokrągloną do największej liczby całkowitej mniejszej lub równej, od wartości wyjściowej - total_sales_qty_minus.    #praktyka #skladnia #join #rounding

SELECT CEIL(SUM(s.sales_qty)) as total_sales_qty_plus,
FLOOR(SUM(s.sales_qty)) as total_sales_qty_minus
FROM sales s
JOIN products p ON p.product_id = s.product_id
AND p.product_name = 'Product 2';

Na podstawie danych sprzedażowych (tabela SALES), pobierz dowolnych 10 wierszy, dla których wartość sprzedaży (kolumn SALES_AMOUNT) jest większa od 5.    #praktyka #skladnia

SELECT s.*
FROM sales s
WHERE s.sales_amount > 5
FETCH FIRST 10 ROWS ONLY;

Wyświetl wszystkie dane sprzedażowe (tabela SALES), dodając w wyniku kolumne sales_meets_expectations, która w zależności od wartości sprzedaży (kolumna SALES_AMOUNT), będzie miała wartość 'Y' - dla wartości sprzedaży większych od 5, 'N' - dla wartości sprzedaży mniejszych lub równych 5 oraz 'N/A' w pozostałych przypadkach.    #praktyka #skladnia #case

SELECT s.*,
CASE
WHEN s.sales_amount > 5 THEN TRUE
WHEN s.sales_amount <= 5 THEN FALSE
ELSE 'N/A'
END as sales_meets_expectations
FROM sales s;

Na podstawie danych sprzedażowych (tabela SALES), wyświetl liczbę sprzedanych produktów (kolumna SALES_QTY) dla produktu o nazwie 'Product 1', w podziale na miesiące w roku 2020 (miesiąc z kolumny SALES_DATE). W kolumnach wynikowych dodaj kolumnę exceeded_expected_needs o wartości 'Y' dla wierszy, w których całkowita liczba sprzedanych produktów jest większa niż 1500, w pozostałych przypadkach wartości dla tej kolumna powinna być równa 'N'.    #praktyka #skladnia #join 

 SELECT EXTRACT(MONTH FROM s.sales_date),
SUM(s.sales_qty),
CASE
WHEN sum(s.sales_qty) > 1500 THEN 'Y'
ELSE 'N'
END as exceeded_expected_needs
FROM sales s
JOIN products p ON p.product_id = s.product_id
AND p.product_name = 'Product 1'
WHERE EXTRACT(YEAR FROM s.sales_date) = 2020
GROUP BY EXTRACT(MONTH FROM s.sales_date);

Korzystając z operatora EXISTS, podaj liczbę unikatowych identyfikatorów produktów (tabel PRODUCTS, kolumn PRODUCT_ID), które nie wzięły udziału w transakcji sprzedażowej (tabela SALES).    #praktyka #skladnia #exists

  SELECT COUNT(DISTINCT p.product_id)
FROM products p
WHERE NOT EXISTS (SELECT 1
FROM sales s
WHERE s.product_id = p.product_id);

Korzystając z operatora EXISTS, wyświetl wszystkie produkty (tabela PRODUCTS), które wzięły udział w transakcji sprzedażowej (tabela SALES).    #praktyka #skladnia #exists

 SELECT p.*
FROM products p
WHERE EXISTS (SELECT 1
FROM sales s
WHERE s.product_id = p.product_id);

Korzystając z komendy DELETE, usuń z tabeli produktów (tabela PRODUCTS), wszystkie produkty, które do tej pory nie wzięły udziału w transakcji sprzedażowej (tabela SALES).    #praktyka #skladnia #dml

-- opcja 1
WITH products_to_delete AS (
SELECT p.product_id
FROM products p
LEFT JOIN sales s ON s.product_id = p.product_id
WHERE s.id IS NULL
)
DELETE
FROM products p
USING products_to_delete pd
WHERE p.product_id = pd.product_id;-- opcja 2
DELETE
FROM products p
WHERE p.product_id IN (SELECT p.product_id
FROM products p
LEFT JOIN sales s ON s.product_id = p.product_id
WHERE s.id IS NULL);

Wykorzystując komendę DELETE usuń wszystkie dane z tabeli produktów (tabela PRODUCTS), gdzie opis produktu (kolumn RANDOM_DESC) zaczyna się od 'c4c'.    #praktyka #skladnia #dml

DELETE
FROM products p
WHERE p.random_desc LIKE 'c4c%';

Na podstawie danych sprzedażowych (tabela SALES) stwórz dwa zbiory danych (2x SELECT), dla produktów, które zostały sprzedane w miesiącu A i miesiącu B tego samego roku (miesiące wybierz według uznania na podstawie kolumny SALES_DATE). W wyniku potrzebna jest informacja jedynie o unikatowych nazwach produktów (kolumna PRODUCT_NAME). Następnie korzystając z operacji UNION, połącz oba zbiory i wyświetl nazwy produktów (kolumna PRODUCT_NAME), które brały udział w transkacjach sprzedażowych.    #praktyka #skladnia #union

SELECT DISTINCT p.product_name
FROM sales s
JOIN products p ON s.product_id = p.product_id
AND EXTRACT(MONTH FROM s.sales_date) = 7
AND EXTRACT(YEAR FROM s.sales_date) = 2020
UNION
SELECT DISTINCT p.product_name
FROM sales s
JOIN products p ON s.product_id = p.product_id
AND EXTRACT(MONTH FROM s.sales_date) = 8
AND EXTRACT(YEAR FROM s.sales_date) = 2020

Usuń wszystkie dane sprzedażowe (tabel SALES), zakładając, że operacja ma być nieodwracalna.    #praktyka #skladnia #ddl

TRUNCATE TABLE sales;-- lub w przypadku istniejących referencji do tabeli SALES
TRUNCATE TABLE sales CASCADE;

Stwórz nową tabelę PRODUCTS_OLD_WAREHOUSE o takich samych kolumnach jak istniejąca tabela produktów (tabela PRODUCTS). Wstaw do nowej tabeli kilka wierszy - dowolnych według Twojego uznania. Korzystając z operacji UNION oraz UNION ALL połącz tabelę PRODUCTS_OLD_WAREHOUSE z 10 dowolnym produktami z tabeli PRODUCTS, w wyniku wyświetl jedynie nazwę produktu (kolumna PRODUCT_NAME) oraz kategorię produktu (kolumna PRODUCT_CATEGORY). Czy w przypadku wykorzystania UNION jakieś wierszy zostały pominięte?    #praktyka #skladnia #unionvsunionall

CREATE TABLE products_old_warehouse AS
SELECT *
FROM products;TRUNCATE TABLE products_old_warehouse;INSERT INTO products_old_warehouse
SELECT *
FROM products
LIMIT 5;SELECT pod.product_name,
pod.product_category
FROM products_old_warehouse pod
UNION
SELECT p.product_name,
p.product_category
FROM products p
LIMIT 10;SELECT pod.product_name,
pod.product_category
FROM products_old_warehouse pod
UNION ALL
SELECT p.product_name,
p.product_category
FROM products p
LIMIT 10;

Stwórz widok sales_with_amount_more_then_10 (CREATE VIEW) w oparciu o tabelę z danymi sprzedażowymi takimi gdzie sprzedaż jest większa niż 10 (tabela SALES). Następnie spróbuj usunąć tabelę SALES zwykłą operacją DROP oraz z opcją CASCADE?    #praktyka #skladnia #ddl

CREATE VIEW sales_with_amount_more_then_10 AS
SELECT *
FROM sales
WHERE sales_amount >10;DROP TABLE sales; //błąd bo istnieje widok zależnyDROP TABLE sales CASCADE; // sukces ale widok sales_with_amount_more_then_10 również zostanie usunięty

Stwórz widok products_sold w oparciu o tabele produktów PRODUCTS i tabele danych sprzedażowych SALES. Widok ma posiadać 4 kolumny data sprzedaży sales_date, wartość sprzedaży sales_amount, nazwa produktu product_name i kategoria produktu product_category?    #praktyka #skladnia #ddl

CREATE OR REPLACE VIEW products_sold AS
SELECT s.sales_date, s.sales_amount,
p.product_name, p.product_category
FROM sales s
JOIN products p on p.product_id = s.product_id;

Stwórz widok zmaterializowany products_sold w oparciu o tabele produktów PRODUCTS i tabele danych sprzedażowych SALES. Widok ma posiadać 4 kolumny data sprzedaży sales_date, wartość sprzedaży sales_amount, nazwa produktu product_name i kategoria produktu product_category?    #praktyka #skladnia #ddl

CREATE MATERIALIZED VIEW products_sold AS
SELECT s.sales_date, s.sales_amount,
p.product_name, p.product_category
FROM sales s
JOIN products p on p.product_id = s.product_id;

Na podstawie wszystkich produktów (tabela PRODUCTS), stwórz listę nazw produktów (kolumna PRODUCT_NAME) i tablicę unikatowych kategorii (kolumna PRODUCT_CATEGORY) do których te produkty należą?    #praktyka #skladnia

SELECT product_name,
array_agg(DISTINCT product_category) as categories_list
FROM products
GROUP BY product_name;

Na podstawie danych sprzedażowych (tabel SALES), wyświetl wszystkie transakcje sprzedażowe z kolejnego miesiąca (dane testowe tworzone jako INTERVAL stąd kolejny miesiąc), tak żeby w danych wynikowych, widoczna była całkowita wartość sprzedaży (kolumn SALES_AMOUNT), całkowita liczba sprzedanych egzemplarzy (kolumna SALES_QTY), dla nazw produktów (kolumna PRODUCT_NAME), razem z tablicą unikatowych kategorii do których te produkty należą.    #praktyka #skladnia

SELECT ROUND(SUM(sales_amount)) as total_sales,
ROUND(SUM(sales_qty)) as total_qty_sold,
p.product_name,
ARRAY_AGG(DISTINCT p.product_category) as product_categories_list
FROM sales s
JOIN products p on p.product_id = s.product_id
WHERE EXTRACT(MONTH FROM sales_date) = 8
AND EXTRACT(YEAR FROM sales_date) = 2020
GROUP BY EXTRACT(YEAR FROM sales_date)||''||EXTRACT(MONTH FROM sales_date),
p.product_name;

Jakim poleceniem można nadać użytkownikowi user_limited_access uprawnienia odczytywania danych z tabeli SALES.    #praktyka #skladnia #dcl

GRANT SELECT ON sales TO user_limited_access;

Jakim poleceniem można nadać użytkownikowi user_limited_access wszystkie uprawnienia do tabeli SALES..    #praktyka #skladnia #dcl

GRANT ALL PRIVILEGES ON sales TO user_limited_access;

Jakim poleceniem można nadać użytkownikowi user_limited_access uprawnienia odczytu kolumny sales_date  i uprawnienia aktualizacji danych w kolumnie discount w tabeli SALES.    #praktyka #skladnia #dcl

GRANT SELECT (sales_date), UPDATE (discount) ON sales TO user_limited_access;

Jakim poleceniem można odebrać użytkownikowi user_limited_access uprawnienia odczytywania danych z tabeli SALES.    #praktyka #skladnia #dcl

REVOKE SELECT ON sales FROM user_limited_access;

Jakim poleceniem można odebrać użytkownikowi user_limited_access uprawnienia do wszystkich tabel w schemacie PUBLIC_SALES (zakładając, że taki schemat istnieje w bazie danych).    #praktyka #skladnia #dcl

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public_sales FROM user_limited_access;

Mając w bazie danych role (zbiór uprawnień) o nazwie limited_access (dającą ograniczony dostęp do bazy danych), nadaj użytkownikowi user_limited_access tę rolę.    #praktyka #skladnia #dcl

GRANT limited_access TO user_limited_access;

Użytkownik user_limited_access jest przypisany do roli limited_access (dającej ograniczony dostęp do bazy danych), odbierz użytkownikowi dostęp do tej roli.    #praktyka #skladnia #dcl

REVOKE limited_access TO user_limited_access;

Zakładając, że pracujesz w sesji bazodanowej, bez aktywnego automatycznego zatwierdzania operacji COMMIT (bez opcji AUTOCOMMIT), stwórz dwa zapytania. Pierwsze do usunięcia wszystkich rekordów z tabeli PRODUCTS takich, że nazwa produktu (kolumna PRODUCT_NAME) jest równa Product 1. Druga, która wycofa zmiany wprowadzone przez pierwsze zapytanie.    #praktyka #skladnia #dcl

DELETE FROM products p WHERE p.product_name = 'Product 1';
ROLLBACK;

Mając tabelę z historycznymi produktami, PRODUCTS_HISTORY, o takiej samej strukturze (te same kolumny i typy danych) jak tabela PRODUCTS. Wstaw jednym zapytaniem wszystkie dane z tabeli PRODUCTS_HISTORY do tabeli PRODUCTS.    #praktyka #skladnia

INSERT INTO products SELECT * FROM products_history;

Wyświetl wszystkie informacje o produktach (tabel PRODUCTS), dodając do zbioru wynikowe kolumnę product_name_category, która będzie złączeniem nazwy produktu (kolumna PRODUCT_NAME) i kategorii produktu (kolumna PRODUCT_CATEGORY). Wartości rozdziel znakiem ' - '. W przypadku wartości nieokreślonej dla jednego lub obu atrybutów, wstaw pusty tekst ''.    #praktyka #skladnia

SELECT p.*,
COALESCE(p.product_name,'')||' - '||COALESCE(p.product_category,'') as product_name_category
FROM products p;--- lub z użyciem funkcji CONCATSELECT p.*,
CONCAT(COALESCE(p.product_name,''),
' - ',
COALESCE(p.product_category,'')) as product_name_category
FROM products p;

Jaki będzie wynik tego zapytania? SELECT product_name FROM products ORDER BY 2 DESC;   #praktyka #skladnia

ORDER BY 2 jak najbardziej zadziała w momencie gdy w zbiorze wynikowym są co najmniej 2 kolumny wtedy zapytanie użyje 2 kolumny do wykonania sortowania.


Natomiast w przykładzie powyżej, baza danych zwróci błąd, gdyż w zbiorze wynikowym nie ma 2 elementu.

Bez użycia funkcji MIN wyświetl najmniejszy identyfikator produktu (kolumna PRODUCT_ID) z tabeli PRODUCTS.   #praktyka #skladnia

SELECT product_id
FROM products
ORDER BY product_id LIMIT 1;

Bez użycia funkcji MAX wyświetl drugi największy identyfikator produktu (kolumna PRODUCT_ID) z tabeli PRODUCTS.   #praktyka #skladnia

SELECT product_id
FROM products
ORDER BY product_id DESC
LIMIT 1
OFFSET 1;

Na podstawie danych produktowych (tabela PRODUCTS), znajdź wszystkie produkty, powtarzające się w obrębie innych atrybutów produktów (produkty, które występują więcej niż 1 raz).   #praktyka #skladnia

SELECT product_name, count(*)
FROM products
GROUP BY product_name
HAVING count(*) > 1;

Wyświetl wszystkie nazwy produktów (kolumna PRODUCT_NAME) razem z całkowitą wartością sprzedaży (kolumna SALES_AMOUNT), takich gdzie średnia liczba sprzedanych sztuk (kolumna SALES_QTY) jest większa od 5.   #praktyka #skladnia

SELECT p.product_name,
SUM(s.sales_amount)
FROM sales s
JOIN products p ON p.product_id = s.product_id
GROUP BY p.product_name
HAVING AVG(s.sales_qty) > 5;

Wyświetl całkowitą sumę sprzedaży (kolumna SALES_AMOUNT) dla produktu 'Product 1' (kolumna PRODUCT_NAME), należącego do kategorii 'Category 1' (kolumna PRODUCT_CATEGORY), grupując dane według nowego atrybutu YEAR_MONTH. Atrybut powinien być złączeniem roku i miesiąca dla daty sprzedaży (kolumna SALES_DATE).   #praktyka #skladnia

SELECT p.product_name,
EXTRACT(YEAR FROM s.sales_date)
||''||
EXTRACT(MONTH FROM s.sales_date) as year_month,
SUM(s.sales_amount) as total_sales
FROM sales s
JOIN products p ON p.product_id = s.product_id
AND p.product_name = 'Product 1'
AND p.product_category = 'Category 1'
GROUP BY p.product_name, 2
ORDER BY 2 DESC;

Wyświetl sumę wzrostową sprzedaży (kolumna SALES_AMOUNT) dla produktu 'Product 1' (kolumna PRODUCT_NAME), należącego do kategorii 'Category 1' (kolumna PRODUCT_CATEGORY), grupując dane według nowego atrybutu YEAR_MONTH. W taki sposób by kolejne wartości sprzedaży wskazywały na sumę obecnej i poprzednich wartości sumy sprzedaży dla złączenia roku i miesiąca.   #praktyka #skladnia

SELECT DISTINCT p.product_name,
EXTRACT(YEAR FROM s.sales_date)
||''||
EXTRACT(MONTH FROM s.sales_date) as year_month,
SUM(s.sales_amount)
OVER (ORDER BY EXTRACT(YEAR FROM s.sales_date)
||''||
EXTRACT(MONTH FROM s.sales_date) desc
) as rolling_sum_of_sales
FROM sales s
JOIN products p ON p.product_id = s.product_id
AND p.product_name = 'Product 1'
AND p.product_category = 'Category 1'
ORDER BY 2 DESC;

Czy i w jaki sposób można skorzystać z kolejności kolumn składni SELECT w części ORDER BY zapytania.   #praktyka #skladnia

Składnia ORDER BY jest ostatnim elementem "logicznego" planu wykonania zapytania. Stąd gdy w składni SELECT korzystamy z aliasów dla kolumn (bo np. są to kolumny zbudowane na funkcjach) możemy z nich tychże aliasów skorzystać w ORDER BY.

SELECT p.product_name,
LEFT(p.random_desc, 5) as desc_small
FROM products p
ORDER BY desc_small;

Inna ciekawostka, kolejność kolumn wymieniona w części SELECT również może posłużyć w sekcji ORDER BY. Zamiast używać aliasu lub nazwy drugiej kolumny, można użyć miejsca jej wystąpienia w SELECT, czyli cyfry 2.

  SELECT p.product_name,
LEFT(p.random_desc, 5) as desc_small
FROM products p
ORDER BY 2;

Czy możliwe jest sortowanie osobno zbiorów użytych w składni UNION lub UNION ALL.   #praktyka #skladnia

Tak i Nie 🙂 


Domyślnie ORDER BY służy do sortowania całego zbioru wynikowego, więc będzie on ostatnim element składni SELECT po UNION czy UNION ALL.


Można jednak osobno sortować zbiory użyte w UNION / UNION ALL korzystając z podzapytań (czyli oba zbiory traktować jako podzapytania) lub wstawiając sztuczną kolumnę i sortując początkowo po niej.

Przy podejściu ze sztuczną kolumną uważaj na wyniki, UNION, które przy okazji złączenia eliminuje wiersze o tych samych wartościach nie będzie miał zastosowania bo sztuczna kolumna zawsze będzie różna w obrębie zbiorów (czyli na koniec dodatkowo podzapytanie + distinct).

-- ORDER BY ostatnim elementem
SELECT p.product_name
FROM products p
WHERE p.product_category = 'Category 1'
UNION
SELECT p.product_name
FROM products p
WHERE p.product_category = 'Category 2'
ORDER BY product_name;-- ORDER BY w podzapytaniu
SELECT sq.product_name
FROM (SELECT p.product_name
FROM products p
WHERE p.product_category = 'Category 1'
ORDER BY p.product_name) sq
UNION
SELECT p.product_name
FROM products p
WHERE p.product_category = 'Category 2';-- ORDER BY sztuczna kolumna
SELECT p.product_name, 1 as orderCol
FROM products p
WHERE p.product_category = 'Category 1'
UNION
SELECT p.product_name, 2 as orderCol
FROM products p
WHERE p.product_category = 'Category 2'
ORDER BY orderCol, product_name;

Korzystając z funkcji okna rank() over(), wyświetl nazwy produktów (kolumna PRODUCT_NAME) i ich całkowitą sprzedaż (kolumn SALES_AMOUNT) w sierpniu 2020 (kolumna SALES_DATE) oraz ich ranking od najlepiej sprzedającego się produktu.   #praktyka #skladnia

SELECT *,
rank() over (order by sales_total desc) as rank
FROM (
SELECT p.product_name,
sum(s.sales_amount) as sales_total
FROM sales s
JOIN products p on p.product_id = s.product_id
WHERE extract(month from s.sales_date) = 8
AND extract(year from s.sales_date) = 2020
GROUP BY p.product_name) sq;

Korzystając z funkcji okna  lag() over(), oblicz różnicę całkowitej sprzedaży (kolumna SALES_AMOUNT), pomiędzy kolejnymi następującymi po sobie (według wartości sprzedaży od największej) produktami w miesiącu sierpień 2020 (kolumn SALES_DATE). W wyniku wyświetl nazwę produktu (kolumna PRODUCT_NAME), całkowitą sprzedaż i różnicę sprzedaży. Przykład: Product 1, sprzedaż 100 (największa) Różnica: 0. Product 3, Sprzedaż 80, Różnica: 20 (względem Product1). Product 5, Sprzedaż: 50, Różnica: 30 (względem Product 3) itd.   #praktyka #skladnia

SELECT *,
COALESCE(lag(sales_total) over (order by sales_total desc) - sales_total
,0) as sales_diff
FROM (
SELECT p.product_name,
sum(s.sales_amount) as sales_total
FROM sales s
JOIN products p on p.product_id = s.product_id
WHERE extract(month from s.sales_date) = 8
AND extract(year from s.sales_date) = 2020
GROUP BY p.product_name) sq;

Korzystając z funkcji okna first_value() over(), oblicz różnicę całkowitej sprzedaży (kolumna SALES_AMOUNT), pomiędzy najlepszą sprzedażą i sprzedażą dla danego produktu w miesiącu sierpień 2020 (kolumn SALES_DATE). W wyniku wyświetl nazwę produktu (kolumna PRODUCT_NAME), całkowitą sprzedaż i różnicę sprzedaży. Przykład: Product 1, sprzedaż 100 (największa) Różnica: 0. Product 3, Sprzedaż 80, Różnica: 20 (względem Product1). Product 5, Sprzedaż: 50, Różnica: 50 (względem Product 1) itd.   #praktyka #skladnia

SELECT *,
first_value(sales_total) over (order by sales_total desc)
- sales_total as sales_diff
FROM (
SELECT p.product_name,
sum(s.sales_amount) as sales_total
FROM sales s
JOIN products p ON p.product_id = s.product_id
WHERE extract(month from s.sales_date) = 8
AND extract(year from s.sales_date) = 2020
GROUP BY p.product_name) sq;

Korzystając z funkcji okna first_value() over() i last_value() over() oblicz różnicę całkowitej sprzedaży (kolumna SALES_AMOUNT) według nazw produktów (kolumna PRODUCT_NAME), pomiędzy największą sprzedażą i najmniejszą sprzedażą w miesiącu sierpień 2020 (kolumn SALES_DATE). W wyniku wyświetl tylko tę różnicę.   #praktyka #skladnia

SELECT first_value(sales_total) over (order by sales_total desc)
- last_value(sales_total) over () as sales_diff
FROM (
SELECT p.product_name,
sum(s.sales_amount) as sales_total
FROM sales s
JOIN products p on p.product_id = s.product_id
WHERE extract(month from s.sales_date) = 8
AND extract(year from s.sales_date) = 2020
GROUP BY p.product_name) sq
LIMIT 1;

Korzystając z funkcji MIN() i MAX() oblicz różnicę całkowitej sprzedaży (kolumna SALES_AMOUNT) według nazw produktów (kolumna PRODUCT_NAME), pomiędzy największą i najmniejszą sprzedażą w miesiącu sierpień 2020 (kolumn SALES_DATE). W wyniku wyświetl tylko tę różnicę.   #praktyka #skladnia

SELECT max(sales_total) - min(sales_total) as sales_diff
FROM (
SELECT p.product_name,
sum(s.sales_amount) as sales_total
FROM sales s
JOIN products p on p.product_id = s.product_id
WHERE extract(month from s.sales_date) = 8
AND extract(year from s.sales_date) = 2020
GROUP BY p.product_name) sq;

Nie korzystając z funkcji min() i max() ani z last_value() i first_value() oblicz różnicę całkowitej sprzedaży (kolumna SALES_AMOUNT) według nazw produktów (kolumna PRODUCT_NAME), pomiędzy największa i najmniejszą sprzedażą w miesiącu sierpień 2020 (kolumn SALES_DATE). W wyniku wyświetl tylko tę różnicę.   #praktyka #skladnia

WITH sales_total_august_2020 AS (
SELECT p.product_name,
sum(s.sales_amount) as sales_total
FROM sales s
JOIN products p on p.product_id = s.product_id
WHERE extract(month from s.sales_date) = 8
AND extract(year from s.sales_date) = 2020
GROUP BY p.product_name)
SELECT mas.max_sales - mis.min_sales as sales_diff
FROM (SELECT sales_total as max_sales
FROM sales_total_august_2020
ORDER BY sales_total desc
LIMIT 1) mas
JOIN (SELECT sales_total as min_sales
FROM sales_total_august_2020
ORDER BY sales_total asc
LIMIT 1) mis ON 1=1;

Do tabeli produktów (tabela PRODUCTS) dodaj nową kolumnę tekstową limited_description o limicie 250 znaków.   #praktyka #skladnia #ddl

ALTER TABLE products ADD COLUMN limited_description varchar(250);

Do tabeli z danymi sprzedażowymi (tabela SALES) dodaj nową kolumnę o type TRUE / FALSE, sales_over_200k, która oznacza sprzedaż na ponad 200 000. Kolumna nie może posiadać wartości nieokreślonych, domyślnie każda wartość powinna być ustawiana na FALSE.   #praktyka #skladnia #ddl

ALTER TABLE sales ADD COLUMN sales_over_200k bool DEFAULT FALSE NOT NULL;

Do tabeli produktów (tabela PRODUCTS) dodaj nową kolumnę tekstową limited_description o limicie 250 znaków. Dla istniejących danych w tabeli PRODUCTS, przygotuje takie zapytanie UPDATE, które na bazie istniejących opisów (kolumna RANDOM_DESC), weźmie dla każdego wiersza 5 pierwszych znaków opisu produktu i wstawi go w nowe pole limited_description.   #praktyka #skladnia #ddl

ALTER TABLE products ADD COLUMN limited_description varchar(250);UPDATE products SET limited_description = left(random_desc,5);

Z tabeli zawierającej dane sprzedażowe (tabela SALES) usuń kolumnę rabat (kolumna DISCOUNT).   #praktyka #skladnia #ddl

ALTER TABLE sales DROP COLUMN discount;

Korzystając z konstrukcji CTAS (Create Table As SELECT), utwórz kopię tabeli produktów (tabela PRODUCTS) i nazwij ją PRODUCTS_BP. Do tabeli produktowej, dodaj nową kolumnę limited_description typu tekstowego 100 znaków. Usuń z tabeli produktowej kolumnę random_desc i wszystkie dane korzystając ze składni TRUNCATE. Z kopii tabeli produktowej PRODUCTS_BP, wstaw dane do tabeli PRODUCTS, do nowej kolumny LIMITED_DESCRIPTION wstaw dane ze starej kolumny RANDOM_DESC tak, że nowa kolumna będzie złączeniem 5 pierwszych i 5 ostatnich znaków z poprzedniej kolumny. Po zakończonej operacji usuń tabelę PRODUCTS_BP.   #praktyka #skladnia #ddl

CREATE TABLE products_bp AS SELECT * FROM products;ALTER TABLE products ADD COLUMN limited_description varchar(100);ALTER TABLE products DROP COLUMN random_desc;TRUNCATE TABLE products;INSERT INTO products (product_id, product_name, product_category, limited_description)
SELECT product_id,
product_name,
product_category,
left(random_desc,5)||right(random_desc,5) as limited_description
FROM products_bp;DROP TABLE products_bp;

Stwórz indeks sales_prd_id_idx dla kolumny PRODUCT_ID w tabeli z danymi sprzedażowymi (tabela SALES). Indeks powinien być typu B-TREE.   #praktyka #skladnia #ddl

CREATE INDEX sales_prd_id_idx ON sales USING btree (product_id);

Stwórz indeks sales_prd_id_idx dla kolumny PRODUCT_ID w tabeli z danymi sprzedażowymi (tabela SALES). Indeks powinien być typu B-TREE, tabela nie powinna być blokowana w trakcie tworzenia indeksu. Uwaga: specyficzne dla bazy danych PostgreSQL.   #praktyka #skladnia #ddl

CREATE INDEX CONCURRENTLY sales_prd_id_idx ON sales USING btree (product_id);

Stwórz indeks sales_prd_id_idx dla kolumny PRODUCT_ID w tabeli z danymi sprzedażowymi (tabela SALES). Indeks powinien być typu B-TREE, sposób sortowania danych dla indeksu powinien wskazywać na to by wartości nieokreślone (NULL) były brane pierwsze do sortowania.   #praktyka #skladnia #ddl

CREATE INDEX sales_prd_id_idx ON sales USING btree (product_ID NULLS FIRST);

Stwórz indeks limited_prd_desc_idx, który będzie określał wyrażenie, wyboru 5 pierwszych znaków opisu produktu (kolumna RANDOM_DESC) dla danych produktowych (tabela PRODUCTS). Wyrażenie powinno korzystać z funkcji LEFT.   #praktyka #skladnia #ddl

CREATE INDEX limited_prd_desc_idx ON products (left(random_desc,5));

Usuń indeks sales_prd_id_idx dla kolumny PRODUCT_ID z tabeli z danymi sprzedażowymi (tabela SALES), jeżel itaki indeks istnieje. W taki sposób, by zapytanie nie zwróciło błędu w przypadku gdy indeks nie istnieje.   #praktyka #skladnia #ddl

DROP INDEX IF EXISTS sales_prd_id_idx;

Korzystając z planu zapytania, sprawdź jaka będzie różnica dla zapytania, którego celem jest pokazanie danych produktowych dla produktu o nazwie Product 1 (kolumna PRODUCT_NAME) z istniejącym indeksem typu B-TREE na atrybucie nazwa produktu i bez takiego indeksu.   #praktyka #skladnia #queryplan

-- BEZ INDEKSU
EXPLAIN ANALYZE
SELECT *
FROM products
WHERE product_name = 'Product 1';
QUERY PLAN
Seq Scan on products (cost=0.00..10.88 rows=1 width=1068) (actual time=0.014..0.141 rows=104 loops=1)
Filter: ((product_name)::text = 'Product 1'::text)
Rows Removed by Filter: 896
Planning time: 0.049 ms
Execution time: 0.156 ms
-- Z INDEKSEM
CREATE INDEX prd_product_name_idx ON products (product_name);EXPLAIN ANALYZE
SELECT *
FROM products
WHERE product_name = 'Product 1';
QUERY PLAN
Index Scan using prd_product_name_idx on products (cost=0.27..8.29 rows=1 width=1068) (actual time=0.022..0.057 rows=99 loops=1)
Index Cond: ((product_name)::text = 'Product 1'::text)
Planning time: 0.248 ms
Execution time: 0.083 ms

Sprawdź korzystając z planu zapytania jaki rodzaj fizycznego złączenia zostanie wykorzystany w momencie łączenia danych sprzedażowych (tabela SALES) z danymi produktowymi (tabela PRODUCTS), po kluczu złączenia PRODUCT_ID, korzystając ze złączenia INNER JOIN.   #praktyka #skladnia #queryplan

EXPLAIN ANALYZE
SELECT *
FROM sales s
JOIN products p ON p.product_id = s.product_id;
QUERY PLAN
Hash Join (cost=11.57..181.31 rows=1942 width=1148) (actual time=0.274..4.998 rows=9894 loops=1)
Hash Cond: (s.product_id = p.product_id)
-> Seq Scan on sales s (cost=0.00..129.50 rows=5550 width=80) (actual time=0.021..1.291 rows=10000 loops=1)
-> Hash (cost=10.70..10.70 rows=70 width=1068) (actual time=0.224..0.224 rows=1000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 80kB
-> Seq Scan on products p (cost=0.00..10.70 rows=70 width=1068) (actual time=0.007..0.091 rows=1000 loops=1)
Planning time: 0.213 ms
Execution time: 5.598 ms

Przygotuj zapytanie w którym dla złączenia danych sprzedażowych (tabela SALES) z danymi produktowymi (tabela PRODUCTS), po kluczu złączenia PRODUCT_ID, korzystając ze złączenia INNER JOIN, wykorzystane zostanie łączenie danych metodą MERGE JOIN.   #praktyka #skladnia #queryplan

EXPLAIN ANALYZE
SELECT *
FROM (SELECT *
FROM products
ORDER BY product_id) p
JOIN (SELECT *
FROM sales
ORDER BY product_id) s ON s.product_id = p.product_id;
QUERY PLAN
Merge Join (cost=909.80..1184.70 rows=10000 width=97) (actual time=3.176..7.206 rows=9894 loops=1)
Merge Cond: (products.product_id = sales.product_id)
-> Sort (cost=69.83..72.33 rows=1000 width=41) (actual time=0.227..0.239 rows=100 loops=1)
Sort Key: products.product_id
Sort Method: quicksort Memory: 103kB
-> Seq Scan on products (cost=0.00..20.00 rows=1000 width=41) (actual time=0.011..0.104 rows=1000 loops=1)
-> Materialize (cost=838.39..988.39 rows=10000 width=56) (actual time=2.896..5.254 rows=10000 loops=1)
-> Sort (cost=838.39..863.39 rows=10000 width=56) (actual time=2.892..4.305 rows=10000 loops=1)
Sort Key: sales.product_id
Sort Method: quicksort Memory: 1166kB
-> Seq Scan on sales (cost=0.00..174.00 rows=10000 width=56) (actual time=0.007..0.835 rows=10000 loops=1)
Planning time: 0.359 ms
Execution time: 7.712 ms

Korzystając z planu zapytania, sprawdź jaka będzie różnica dla zapytania, którego celem jest pokazanie danych produktowych (tabela PRODUCTS) dla produktów, których nazwa jest inna niż (≠ lub <>) Product 1 (kolumna PRODUCT_NAME) z istniejącym indeksem typu B-TREE na atrybucie nazwa produktu i bez takiego indeksu. Czy indeks zostanie wykorzystany?   #praktyka #skladnia #queryplan

-- BEZ INDEKSU
EXPLAIN ANALYZE
SELECT *
FROM products
WHERE product_name <> 'Product 1';
QUERY PLAN
Seq Scan on products (cost=0.00..22.50 rows=896 width=41) (actual time=0.022..0.646 rows=896 loops=1)
Filter: ((product_name)::text <> 'Product 1'::text)
Rows Removed by Filter: 104
Planning time: 0.811 ms
Execution time: 0.908 ms
-- Z INDEKSEM
CREATE INDEX prd_product_name_idx ON products (product_name);EXPLAIN ANALYZE
SELECT *
FROM products
WHERE product_name <> 'Product 1';
QUERY PLAN
Seq Scan on products (cost=0.00..22.50 rows=901 width=41) (actual time=0.016..0.286 rows=901 loops=1)
Filter: ((product_name)::text <> 'Product 1'::text)
Rows Removed by Filter: 99
Planning time: 0.335 ms
Execution time: 0.353 ms

Korzystając z planu zapytania, sprawdź czy dla zapytania łączącego dane sprzedażowe (tabela SALES) z danymi o produktach (tabela PRODUCTS) według klucza złączenia PRODUCT_ID i dla sprzedaży większej od 5 (kolumna SALES_AMOUNT), wykorzystane zostaną indeksy założone na kolumny PRODUCT_ID obu tabel (SALES i PRODUCTS).   #praktyka #skladnia #queryplan

EXPLAIN ANALYZE
SELECT *
FROM sales s
JOIN products p ON s.product_id = p.product_id
AND s.sales_amount > 5;
QUERY PLAN
Hash Join (cost=11.57..168.37 rows=648 width=1148) (actual time=0.443..7.010 rows=6031 loops=1)
Hash Cond: (s.product_id = p.product_id)
-> Seq Scan on sales s (cost=0.00..143.38 rows=1850 width=80) (actual time=0.018..3.469 rows=6086 loops=1)
Filter: (sales_amount > '5'::numeric)
Rows Removed by Filter: 3914
-> Hash (cost=10.70..10.70 rows=70 width=1068) (actual time=0.388..0.388 rows=1000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 80kB
-> Seq Scan on products p (cost=0.00..10.70 rows=70 width=1068) (actual time=0.007..0.155 rows=1000 loops=1)
Planning time: 0.494 ms
Execution time: 7.424 ms

Na podstawie danych produktowych (tabela PRODUCTS), znajdź wszystkie produkty, których opis produktu (kolumna RANDOM_DESC) jest dłuższy niż 10 znaków.   #praktyka #skladnia 

SELECT p.*
FROM products p
WHERE length(p.random_desc) > 10;

Stwórz, dwa równoważne (w kontekście zbiorów wynikowych) zapytania. Oba zapytania powinny być złączeniem danych sprzedażowych (tabela SALES) i danych o produktach (tabela PRODUCTS). W pierwszym zapytaniu złączenie powinno być typu INNER JOIN po kluczu złączenia PRODUCT_ID. W drugim zapytaniu złączenie powinno być typu LEFT OUTER JOIN po kluczu złączenia PRODUCT_ID, jednak odfiltrowujące te dane sprzedażowe, dla których nie istnieją informacji o produkcie z tabeli PRODUCTS.   #praktyka #skladnia 

SELECT s.*, p.*
FROM sales s
JOIN products p ON p.product_id = s.product_id;
SELECT s.*, p.*
FROM sales s
LEFT JOIN products p ON p.product_id = s.product_id
WHERE p.product_ID IS NOT NULL;

Korzystając z planu zapytania, sprawdź, które z zapytań (według estymat planu zapytania) wykona się szybciej. Oba zapytania powinny być złączeniem danych sprzedażowych (tabela SALES) i danych o produktach (tabela PRODUCTS). W pierwszym zapytaniu złączenie powinno być typu INNER JOIN po kluczu złączenia PRODUCT_ID. W drugim zapytaniu złączenie powinno być typu LEFT OUTER JOIN po kluczu złączenia PRODUCT_ID, jednak odfiltrowujące te dane sprzedażowe, dla których nie istnieją informacji o produkcie z tabeli PRODUCTS. Zauważ, że w takiej sytuacji oba zapytania powinny zwracać ten sam zbiór wynikowy.   #praktyka #skladnia 

EXPLAIN ANALYZE
SELECT s.*, p.*
FROM sales s
JOIN products p ON p.product_id = s.product_id;
QUERY PLAN
Hash Join (cost=32.50..344.00 rows=10000 width=97) (actual time=0.260..3.872 rows=9893 loops=1)
Hash Cond: (s.product_id = p.product_id)
-> Seq Scan on sales s (cost=0.00..174.00 rows=10000 width=56) (actual time=0.010..0.789 rows=10000 loops=1)
-> Hash (cost=20.00..20.00 rows=1000 width=41) (actual time=0.228..0.228 rows=1000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 80kB
-> Seq Scan on products p (cost=0.00..20.00 rows=1000 width=41) (actual time=0.007..0.084 rows=1000 loops=1)
Planning time: 0.316 ms
Execution time: 4.302 ms
EXPLAIN ANALYZE
SELECT s.*, p.*
FROM sales s
LEFT JOIN products p ON p.product_id = s.product_id
WHERE p.product_ID IS NOT NULL;
QUERY PLAN
Hash Join (cost=32.50..344.00 rows=10000 width=97) (actual time=0.267..3.655 rows=9893 loops=1)
Hash Cond: (s.product_id = p.product_id)
-> Seq Scan on sales s (cost=0.00..174.00 rows=10000 width=56) (actual time=0.009..0.852 rows=10000 loops=1)
-> Hash (cost=20.00..20.00 rows=1000 width=41) (actual time=0.241..0.241 rows=1000 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 80kB
-> Seq Scan on products p (cost=0.00..20.00 rows=1000 width=41) (actual time=0.006..0.139 rows=1000 loops=1)
Filter: (product_id IS NOT NULL)
Planning time: 0.084 ms
Execution time: 4.072 ms

Sprawdź, korzystając z planu zapytania, jaki typ fizycznego złączenia danych, zostanie użyty dla zapytania którego celem jest połączenie danych sprzedażowych (tabela SALES) i produktowych (tabela PRODUCTS) złączeniem typu CROSS JOIN.   #praktyka #skladnia 

EXPLAIN ANALYZE
SELECT s.*, p.*
FROM sales s
JOIN products p ON 1=1;
QUERY PLAN
Nested Loop (cost=0.00..125196.50 rows=10000000 width=97) (actual time=0.028..1980.528 rows=10000000 loops=1)
-> Seq Scan on sales s (cost=0.00..174.00 rows=10000 width=56) (actual time=0.013..2.092 rows=10000 loops=1)
-> Materialize (cost=0.00..25.00 rows=1000 width=41) (actual time=0.000..0.054 rows=1000 loops=10000)
-> Seq Scan on products p (cost=0.00..20.00 rows=1000 width=41) (actual time=0.009..0.097 rows=1000 loops=1)
Planning time: 0.322 ms
Execution time: 2400.225 ms
Data Craze Newsletter Obraz Dokumenty

Dołącz do naszego newslettera!

Co miesiąc otrzymasz zestaw informacji z obszaru przetwarzania, analizy i wizualizacji danych. Dodatkowo po dołączeniu otrzymasz 2 dokumenty w formacie PDF - Wizualizacje Dobrze Dopasowane oraz 10 Trików SQL, O Których Nie Masz Pojęcia.

Administratorem danych osobowych niezbędnych w procesie przetwarzania, w tym podanych powyżej, jest Data Craze - Krzysztof Bury, ul. Piaski 50, 30-199 Rząska, NIP: 7922121365. Zapisując się na newsletter wyrażasz zgodę na przetwarzanie swoich danych osobowych (imię, e-mail) w ramach działań DataCraze.