SQL Bez Tajemnic - Pytania i Odpowiedzi.

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

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);

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
);

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);

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
);
INSERT INTO contacts VALUES
    ('Krzysztof, Bury', 'M', 'Kraków', 'Polska', 1, 'ACME', 'Person A', 'Person B')
  , ('Magda, Kowalska', 'F', 'New York', 'USA', 2, 'CDE', 'Person C', 'Person A');

CREATE: CREATE TABLE sales ( id SERIAL );

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

DROP: DROP TABLE sales;

BEGIN [TRANSACTION]
    AccountStateA = AccountStateA - 100;
    AccountBan = AccountB + 100;
END [TRANSACTION] (COMMIT)

T1: UPDATE products SET product_name = 'Product 999' where product_name = 'Product 1';
T2: UPDATE products SET product_name = 'Product 998' where product_name = 'Product 2';
T1: UPDATE products SET product_name = 'Product 001' where product_name = 'Product 1';
T2: UPDATE products SET product_name = 'Product 991' where product_name = 'Product 2';

1NF - oddzielamy pole NAME_SURNAME.

| NAME| SURNAME | GENDER | CITY | COUNTRY | ID_COMPANY | COMPANY | ADDED_BY | VERIFIED_BY |
|---|---|---|---|---|---|---|---|---|
| Krzysztof | Bury | M | Krakow | Poland | 1 | ACME | PersonA | PersonB |
| Magda| Kowalski | F | New York | USA | 2 | CDE | PersonC | OsobaA |

2NF - wyodrębniamy tabelę GENDER na podstawie kolumny GENDER z tabeli CONTACTS.

CONTACTS
| NAME| SURNAME | CITY | COUNTRY | ID_COMPANY | COMPANY | COMPANY | ADDED_BY | VERIFIED_BY |
|---|---|---|---|---|---|---|---|
| Krzysztof | Bury | Krakow | Poland | 1 | ACME | PersonA | PersonB |
| Magda| Kowalski | New York | USA | 2 | CDE | PersonC | PersonA |

GENDER
| NAME| GENDER |
|---|---|
| Krzysztof | M |
| Magda | F |

3NF - oddzielamy tabelę COMPANY od tabeli CONTACTS na podstawie kolumn ID_COMPANY i COMPANY.

CONTACTS
| NAME| SURNAME | CITY | COUNTRY | ID_COMPANY | ADDED_BY | VERIFIED_BY |
|---|---|---|---|---|---|---|
| Krzysztof | Bury | Krakow | Poland | 1 | PersonA | PersonB |
| Magda| Kowalski | New York | USA | 2 | PersonC | PersonA |

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 tabeli CONTACTS.

CONTACTS
| NAME| SURNAME | CITY | ID_COMPANY | ADDED_BY | VERIFIED_BY |
|---|---|---|---|---|---|---|
| Krzysztof | Bury | Krakow | 1 | PersonA | PersonB |
| Magda| Kowalski | New York | 2 | PersonC | PersonA |

GENDER
| NAME| GENDER |
|---|---|
| Krzysztof | M |
| Magda | F |

COMPANY
| ID_COMPANY | COMPANY |
|---|---|
| 1 | ACME |
| 2 | CDE |

CITY_COUNTRY
| CITY | COUNTRY |
|---|---|
| Krakow | Poland |
| 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 | Krakow | 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 |
|---|---|
| Krakow | Poland |
| New York | USA |

CONTACTS_VERIFIED_BY
| CONTACT_NAME | VERIFIED_BY |
|---|---|
| Krzysztof | PersonB |
| Magda | PersonA |

CONTACTS_ADDED_BY
| CONTACT_NAME | ADDED_BY |
|---|---|
| Krzysztof | PersonA |
| Magda | PersonC |

Syntax:
CREATE OR REPLACE VIEW view_name AS SELECT ...
DROP VIEW view_name;

DELETE FROM TABLE_NAME WHERE …

TRUNCATE TABLE TABLE_NAME …

SELECT 8.28 as output_value,
       CEILING(8.28) as positive_value,
       CEILING(-8.28) as negative_value;

Wynik:
wartość_wyjściowa: 8,28
wartość_dodatnia: 9
wartość_ujemna: -8

SELECT 8.28 as output_value,
       FLOOR(8.28) as positive_value,
       FLOOR(-8.28) as negative_value;

Wynik:
wartość_wyjściowa: 8,28
wartość_dodatnia: 8
wartość_ujemna: -9

SELECT 8.49 as output_value,
        FLOOR(8.28) as floor_value,
        CEIL(8.28) as cel_value,
        ROUND(8.49) as rounded_value

wartość_wyjściowa: 8,49
wartość_podłogowa: 8
wartość_docelowa: 9
wartość_zaokrąglona: 8 (brak zaokrąglenia po przecinku, domyślnie 0 miejsc dziesiętnych)

SELECT 8.51 as output_value,
        FLOOR(8.51) as floor_value,
        CEIL(8.51) as cel_value,
        ROUND(8.51) as rounded_value

wartość_wyjściowa: 8,49
wartość_podłogowa: 8
wartość_docelowa: 9
wartość_zaokrąglona: 9 (brak zaokrąglenia po przecinku, domyślnie 0 miejsc dziesiętnych)

  SELECT column_name
       , ...
    FROM table_name
   WHERE expression / column operator ANY (subquery)
GROUP BY column_name, ...
  HAVING expression operator ANY (subquery)

operator - jest jednym z dostępnych operatorów logicznych =, <>, !=, >, >=, < lub <=

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));

  SELECT column_name
       , ...
    FROM table_name
   WHERE expression / column operator ALL (subquery)
GROUP BY column_name, ...
  HAVING expression operator ALL (subquery)

operator - jest jednym z dostępnych operatorów logicznych =, <>, !=, >, >=, < lub <=

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));

  SELECT column_name
       , ...
    FROM table_name
   WHERE EXISTS (SELECT column_name
                   FROM table_name WHERE conditions);

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

--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);

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

 SELECT * FROM tab LIMIT 10;

 SELECT * FROM tab WHERE ROWNUM <= 10;

 SELECT TOP 10 * FROM tab;

SELECT * FROM tab FETCH FIRST 10 ROWS ONLY;

IF < WARUNEK LOGICZNY >
    THEN < DZIAŁANIE, KTÓRE MA ZOSTAĆ WYKONANE, GDY WARUNEK JEST USTAWIONY >
    ELSE < DZIAŁANIE, KTÓRE MA ZOSTAĆ WYKONANE, GDY WARUNEK NIE JEST SPEŁNIONY >
END

CASE
    WHEN condition1 THEN action1
    WHEN condition2 THEN action2
    ...
    ELSE action_if_others_not_met
END

<b>warunek</b> - dowolna operacja, której wynikiem jest wartość logiczna PRAWDA / FAŁSZ (operator logiczny >, <, =, <> itd.; wynik podzapytania PRAWDA / FAŁSZ itd.)

<b>akcja</b> - akcja, która ma zostać wykonana, gdy spełniony jest warunek, np. wyświetlenie kolumny, dodanie czegoś do wartości itd.

  SELECT …
    FROM …
ORDER BY < COLUMN/FUNCTION_NAME > < DESC | AS C> < NULLS FIRST | NULLS LAST >

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

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

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

PRODUCT_NAME | PRODUCT_CATEGORY
  Product 1  |    Category 1
  Product 1  |    Category 1

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_id: 1
product_name: Product New Special One
product_category: < unknown >
random_desc: Special Constraint Product

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

inne rozwiązanie:

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;

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

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;

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;

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
*/

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

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

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

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
*/

        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;

    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;

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

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

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

   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;

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

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;

    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;

   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;

       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;

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

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

    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';

  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';

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

    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;

 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);

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

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

-- 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);

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

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;

TRUNCATE TABLE sales;

-- or when other objects references SALES
TRUNCATE TABLE sales CASCADE;

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;

CREATE VIEW sales_with_amount_more_then_10 AS
     SELECT *
       FROM sales
      WHERE sales_amount >10;

DROP TABLE sales; //błąd, ponieważ istnieje zależny widok

DROP TABLE sales CASCADE; // sukces, ale widok sales_with_amount_more_then_10 również zostanie usunięty

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;

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;

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

    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;

    GRANT SELECT ON sales TO user_limited_access;

    GRANT ALL PRIVILEGES ON sales TO user_limited_access;

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

    REVOKE SELECT ON sales FROM user_limited_access;

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

    GRANT limited_access TO user_limited_access;

    REVOKE limited_access TO user_limited_access;

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

    INSERT INTO products
         SELECT * FROM products_history;

    SELECT p.*,
           COALESCE(p.product_name,'')||' - '||COALESCE(p.product_category,'') as product_name_category
      FROM products p;

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

    SELECT product_id
      FROM products
  ORDER BY product_id LIMIT 1;

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

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

    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;

    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;

    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;

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

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

-- ORDER BY ostatni element
    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 wewnątrz podzapytania
    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 po 'sztucznej' kolumne 'orderCol'
    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;

    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;

    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;

    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;

    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;

    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;

    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;

    ALTER TABLE products ADD COLUMN limited_description varchar(250);

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

    ALTER TABLE products ADD COLUMN limited_description varchar(250);

    UPDATE products SET limited_description = left(random_desc,5);

    ALTER TABLE sales DROP COLUMN discount;

    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;

    CREATE INDEX sales_prd_id_idx ON sales USING btree (product_id);

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

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

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

    DROP INDEX IF EXISTS sales_prd_id_idx;

    -- 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

    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

    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

    -- 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

    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

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

    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;