View difference between Paste ID: kM0mMnDN and ZcBziC0u

 [NOTATKI]
 Tworzenie tabeli
 Insert into wprowadzenie danych
 Selectem pokazać tych co mają pesel null np
 Utwórz kopie np jako wynik select
 (na podstawie kryteriów)
 Join w każdej postaci
 Plus złączenia zwykłe, bez join. Uzyskać dane z 2 tabel np
 Funkcje average min max count
 Zapytania typu (głównie)from, where
 Np. "ile było zamówień płaskowników"
 jak masz towar-id to trzeba `towar-id`
 Różnice w joinach, null zamiast nazwy to wartość
 Group by
 
 
 [KOD]
 
 student@linux:~> mysql -u stud1
 ERROR 1045 (28000): Access denied for user 'stud1'@'localhost' (using password: NO)
 student@linux:~> mysql -u stud1 -p
 Enter password:
 Welcome to the MariaDB monitor.  Commands end with ; or \g.
 Your MariaDB connection id is 5
 Server version: 10.0.31-MariaDB SLE 12 SP1 package
 
 Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
 
 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 MariaDB [(none)]> use hw1
 Reading table information for completion of table and column names
 You can turn off this feature to get a quicker startup with -A
 
 Database changed
 MariaDB [hw1]> show tables
     -> ;
 +---------------+
 | Tables_in_hw1 |
 +---------------+
 | Miasta        |
 | Osoby         |
 | UczelniaA     |
 | UczelniaB     |
 | klienci       |
 | pracownicy    |
 | pracownicyN   |
 | stanowiskaN   |
 | towary        |
 | zamowienia    |
 +---------------+
 10 rows in set (0.00 sec)
 
 MariaDB [hw1]> select * from zamowienia;
 +----+-----------+----------+------------+---------+
 | id | klient-id | towar-id | data       | wartosc |
 +----+-----------+----------+------------+---------+
 |  1 |         1 |        2 | 2012-01-01 |   12.44 |
 |  2 |         1 |        4 | 2012-01-01 |   10.22 |
 |  3 |         1 |        2 | 2012-02-12 |   15.88 |
 |  4 |         2 |        1 | 2012-01-01 |   22.35 |
 |  5 |         2 |        1 | 2012-02-12 |   28.00 |
 |  6 |         2 |        4 | 2012-03-01 |    2.28 |
 |  7 |         3 |        1 | 2012-02-11 |   18.48 |
 |  8 |         3 |        4 | 2012-01-01 |   12.44 |
 |  9 |         4 |        1 | 2012-03-11 |   15.26 |
 | 10 |         5 |        4 | 2012-03-02 |    6.11 |
 +----+-----------+----------+------------+---------+
 10 rows in set (0.00 sec)
 
 MariaDB [hw1]> select * from zamowienia where wartosc>avg(wartosc);
 ERROR 1111 (HY000): Invalid use of group function
 MariaDB [hw1]> select * from zamowienia where wartosc> select avg(wartosc) from zamowienia);
 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'select avg(wartosc) from zamowienia)' at line 1
 MariaDB [hw1]> select * from zamowienia where wartosc> (select avg(wartosc) from zamowienia);
 +----+-----------+----------+------------+---------+
 | id | klient-id | towar-id | data       | wartosc |
 +----+-----------+----------+------------+---------+
 |  3 |         1 |        2 | 2012-02-12 |   15.88 |
 |  4 |         2 |        1 | 2012-01-01 |   22.35 |
 |  5 |         2 |        1 | 2012-02-12 |   28.00 |
 |  7 |         3 |        1 | 2012-02-11 |   18.48 |
 |  9 |         4 |        1 | 2012-03-11 |   15.26 |
 +----+-----------+----------+------------+---------+
 5 rows in set (0.01 sec)
 
 MariaDB [hw1]> select * from zamowienia;
 +----+-----------+----------+------------+---------+
 | id | klient-id | towar-id | data       | wartosc |
 +----+-----------+----------+------------+---------+
 |  1 |         1 |        2 | 2012-01-01 |   12.44 |
 |  2 |         1 |        4 | 2012-01-01 |   10.22 |
 |  3 |         1 |        2 | 2012-02-12 |   15.88 |
 |  4 |         2 |        1 | 2012-01-01 |   22.35 |
 |  5 |         2 |        1 | 2012-02-12 |   28.00 |
 |  6 |         2 |        4 | 2012-03-01 |    2.28 |
 |  7 |         3 |        1 | 2012-02-11 |   18.48 |
 |  8 |         3 |        4 | 2012-01-01 |   12.44 |
 |  9 |         4 |        1 | 2012-03-11 |   15.26 |
 | 10 |         5 |        4 | 2012-03-02 |    6.11 |
 +----+-----------+----------+------------+---------+
 10 rows in set (0.00 sec)
 
 MariaDB [hw1]> select * from klienci;
 +----+-----------+------------+
 | id | imie      | nazwisko   |
 +----+-----------+------------+
 |  1 | Jan       | Kowalski   |
 |  2 | Andrzej   | Nowak      |
 |  3 | Janusz    | Malinowski |
 |  4 | Adam      | Kowalski   |
 |  5 | Krzysztof | Nowicki    |
 +----+-----------+------------+
 5 rows in set (0.01 sec)
 
 MariaDB [hw1]> select * from towary;
 +----+--------------+-------+-------+
 | id | nazwa        | grupa | cena  |
 +----+--------------+-------+-------+
 |  1 | Śruby        |     1 |  2.00 |
 |  2 | Nakrętki     |     1 |  3.00 |
 |  3 | Kątowniki    |     2 |  8.00 |
 |  4 | Płaskowniki  |     2 |  9.00 |
 |  5 | Gwoździe     |     1 |  1.00 |
 |  6 | Panele       |     3 | 15.00 |
 |  7 | Wkręty       |     1 |  4.00 |
 |  8 | Deski        |     3 | 12.00 |
 |  9 | Płyty        |     3 | 19.00 |
 +----+--------------+-------+-------+
 9 rows in set (0.00 sec)
 
 MariaDB [hw1]>
 MariaDB [hw1]>
 MariaDB [hw1]>
 MariaDB [hw1]>
 MariaDB [hw1]> select * from zamowienia join towary AS (select * from towary join klienci AS wartosc > (select avg(wartosc) from zamowienia));
 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(select * from towary join klienci AS wartosc > (select avg(wartosc) from zamowi' at line 1
 MariaDB [hw1]> select * from zamowienia, towary, klienci where wartosc > (select avg(wartosc) from zamowienia) AND `towar-id` = towary.id AND `klient-id` = klienci.id;
 +----+-----------+----------+------------+---------+----+-----------+-------+------+----+---------+------------+
 | id | klient-id | towar-id | data       | wartosc | id | nazwa     | grupa | cena | id | imie    | nazwisko   |
 +----+-----------+----------+------------+---------+----+-----------+-------+------+----+---------+------------+
 |  3 |         1 |        2 | 2012-02-12 |   15.88 |  2 | Nakrętki  |     1 | 3.00 |  1 | Jan     | Kowalski   |
 |  4 |         2 |        1 | 2012-01-01 |   22.35 |  1 | Śruby     |     1 | 2.00 |  2 | Andrzej | Nowak      |
 |  5 |         2 |        1 | 2012-02-12 |   28.00 |  1 | Śruby     |     1 | 2.00 |  2 | Andrzej | Nowak      |
 |  7 |         3 |        1 | 2012-02-11 |   18.48 |  1 | Śruby     |     1 | 2.00 |  3 | Janusz  | Malinowski |
 |  9 |         4 |        1 | 2012-03-11 |   15.26 |  1 | Śruby     |     1 | 2.00 |  4 | Adam    | Kowalski   |
 +----+-----------+----------+------------+---------+----+-----------+-------+------+----+---------+------------+
 5 rows in set (0.00 sec)
 
 MariaDB [hw1]> select imie, nazwisko, wartosc,  from zamowienia, towary, klienci where wartosc < (select 0.5*max(wartosc) from zamowienia) AND `towar-id` = towary.id AND `klient-id` = klienci.id;
 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'from zamowienia, towary, klienci where wartosc < (select 0.5*max(wartosc) from z' at line 1
 MariaDB [hw1]> select imie, nazwisko, wartosc,  from zamowienia, towary, klienci where wartosc < (select max(wartosc) from zamowienia)*0.5 AND `towar-id` = towary.id AND `klient-id` = klienci.id;
 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'from zamowienia, towary, klienci where wartosc < (select max(wartosc) from zamow' at line 1
 MariaDB [hw1]> select imie, nazwisko, wartosc,  from zamowienia, towary, klienci where wartosc < (select max(wartosc) from zamowienia)*0.5 AND `towar-id` = towary.id AND `klient-id` = klienci.id;
 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'from zamowienia, towary, klienci where wartosc < (select max(wartosc) from zamow' at line 1
 MariaDB [hw1]> select * from zamowienia, towary, klienci where wartosc < (select max(wartosc) from zamowienia)*0.5 AND `towar-id` = towary.id AND `klient-id` = klienci.id;
 +----+-----------+----------+------------+---------+----+--------------+-------+------+----+-----------+------------+
 | id | klient-id | towar-id | data       | wartosc | id | nazwa        | grupa | cena | id | imie      | nazwisko   |
 +----+-----------+----------+------------+---------+----+--------------+-------+------+----+-----------+------------+
 |  1 |         1 |        2 | 2012-01-01 |   12.44 |  2 | Nakrętki     |     1 | 3.00 |  1 | Jan       | Kowalski   |
 |  2 |         1 |        4 | 2012-01-01 |   10.22 |  4 | Płaskowniki  |     2 | 9.00 |  1 | Jan       | Kowalski   |
 |  6 |         2 |        4 | 2012-03-01 |    2.28 |  4 | Płaskowniki  |     2 | 9.00 |  2 | Andrzej   | Nowak      |
 |  8 |         3 |        4 | 2012-01-01 |   12.44 |  4 | Płaskowniki  |     2 | 9.00 |  3 | Janusz    | Malinowski |
 | 10 |         5 |        4 | 2012-03-02 |    6.11 |  4 | Płaskowniki  |     2 | 9.00 |  5 | Krzysztof | Nowicki    |
 +----+-----------+----------+------------+---------+----+--------------+-------+------+----+-----------+------------+
 5 rows in set (0.00 sec)
 
 MariaDB [hw1]> select * from towary;
 +----+--------------+-------+-------+
 | id | nazwa        | grupa | cena  |
 +----+--------------+-------+-------+
 |  1 | Śruby        |     1 |  2.00 |
 |  2 | Nakrętki     |     1 |  3.00 |
 |  3 | Kątowniki    |     2 |  8.00 |
 |  4 | Płaskowniki  |     2 |  9.00 |
 |  5 | Gwoździe     |     1 |  1.00 |
 |  6 | Panele       |     3 | 15.00 |
 |  7 | Wkręty       |     1 |  4.00 |
 |  8 | Deski        |     3 | 12.00 |
 |  9 | Płyty        |     3 | 19.00 |
 +----+--------------+-------+-------+
 9 rows in set (0.00 sec)
 
 MariaDB [hw1]> slect avg(cena), grupa from towary group by grupa
     -> ;
 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'slect avg(cena), grupa from towary group by grupa' at line 1
 MariaDB [hw1]> select avg(cena), grupa from towary group by grupa
     -> ;
 +-----------+-------+
 | avg(cena) | grupa |
 +-----------+-------+
 |  2.500000 |     1 |
 |  8.500000 |     2 |
 | 15.333333 |     3 |
 +-----------+-------+
 3 rows in set (0.00 sec)
 
 MariaDB [hw1]> select nazwa, cena, grupa from towary where cena > (select cena from towary where cena > 10);
 ERROR 1242 (21000): Subquery returns more than 1 row
 MariaDB [hw1]> select nazwa, cena, grupa from towary where cena > (select cena from towary where cena > cena=10);
 Empty set (0.00 sec)
 
 MariaDB [hw1]> select nazwa, cena, grupa from towary where cena > 10;
 +--------+-------+-------+
 | nazwa  | cena  | grupa |
 +--------+-------+-------+
 | Panele | 15.00 |     3 |
 | Deski  | 12.00 |     3 |
 | Płyty  | 19.00 |     3 |
 +--------+-------+-------+
 3 rows in set (0.00 sec)
 
 MariaDB [hw1]> select nazwa, cena, grupa from towary where cena > 10 group by grupa;
 +--------+-------+-------+
 | nazwa  | cena  | grupa |
 +--------+-------+-------+
 | Panele | 15.00 |     3 |
 +--------+-------+-------+
 1 row in set (0.00 sec)
 
 MariaDB [hw1]> select nazwa, cena, grupa from towary as XX where cena > (select avg(cena) from towary where towary.grupa = XX.grupa);
 +--------------+-------+-------+
 | nazwa        | cena  | grupa |
 +--------------+-------+-------+
 | Nakrętki     |  3.00 |     1 |
 | Płaskowniki  |  9.00 |     2 |
 | Wkręty       |  4.00 |     1 |
 | Płyty        | 19.00 |     3 |
 +--------------+-------+-------+
 4 rows in set (0.00 sec)
 
 MariaDB [hw1]> select nazwa, cena, grupa from towary as towaryNew where cena > (select avg(cena) from towary where towary.grupa = towaryNew.grupa);
 +--------------+-------+-------+
 | nazwa        | cena  | grupa |
 +--------------+-------+-------+
 | Nakrętki     |  3.00 |     1 |
 | Płaskowniki  |  9.00 |     2 |
 | Wkręty       |  4.00 |     1 |
 | Płyty        | 19.00 |     3 |
 +--------------+-------+-------+
 4 rows in set (0.00 sec)
 
 MariaDB [hw1]> select nazwa, cena, grupa from towary where cena > (select avg(cena) from towary as towaryNew where towary.grupa = towaryNew.grupa);
 +--------------+-------+-------+
 | nazwa        | cena  | grupa |
 +--------------+-------+-------+
 | Nakrętki     |  3.00 |     1 |
 | Płaskowniki  |  9.00 |     2 |
 | Wkręty       |  4.00 |     1 |
 | Płyty        | 19.00 |     3 |
 +--------------+-------+-------+
 4 rows in set (0.00 sec)
 
 MariaDB [hw1]> select * from zamowienia;
 +----+-----------+----------+------------+---------+
 | id | klient-id | towar-id | data       | wartosc |
 +----+-----------+----------+------------+---------+
 |  1 |         1 |        2 | 2012-01-01 |   12.44 |
 |  2 |         1 |        4 | 2012-01-01 |   10.22 |
 |  3 |         1 |        2 | 2012-02-12 |   15.88 |
 |  4 |         2 |        1 | 2012-01-01 |   22.35 |
 |  5 |         2 |        1 | 2012-02-12 |   28.00 |
 |  6 |         2 |        4 | 2012-03-01 |    2.28 |
 |  7 |         3 |        1 | 2012-02-11 |   18.48 |
 |  8 |         3 |        4 | 2012-01-01 |   12.44 |
 |  9 |         4 |        1 | 2012-03-11 |   15.26 |
 | 10 |         5 |        4 | 2012-03-02 |    6.11 |
 +----+-----------+----------+------------+---------+
 10 rows in set (0.00 sec)
 
 MariaDB [hw1]> select count(*) as ilosc from zamowienia where wartosc > (select avg(wartosc) from zamowienia);
 +-------+
 | ilosc |
 +-------+
 |     5 |
 +-------+
 1 row in set (0.00 sec)
 
 MariaDB [hw1]> select * from zamowienia;
 +----+-----------+----------+------------+---------+
 | id | klient-id | towar-id | data       | wartosc |
 +----+-----------+----------+------------+---------+
 |  1 |         1 |        2 | 2012-01-01 |   12.44 |
 |  2 |         1 |        4 | 2012-01-01 |   10.22 |
 |  3 |         1 |        2 | 2012-02-12 |   15.88 |
 |  4 |         2 |        1 | 2012-01-01 |   22.35 |
 |  5 |         2 |        1 | 2012-02-12 |   28.00 |
 |  6 |         2 |        4 | 2012-03-01 |    2.28 |
 |  7 |         3 |        1 | 2012-02-11 |   18.48 |
 |  8 |         3 |        4 | 2012-01-01 |   12.44 |
 |  9 |         4 |        1 | 2012-03-11 |   15.26 |
 | 10 |         5 |        4 | 2012-03-02 |    6.11 |
 +----+-----------+----------+------------+---------+
 10 rows in set (0.00 sec)
 
 MariaDB [hw1]> select `towar-id`, id, cena from zamowienia where (id,cena) in (select id, min(wartosc) from zamowienia);
 ERROR 1054 (42S22): Unknown column 'cena' in 'field list'
 MariaDB [hw1]> select `towar-id`, id, cena from zamowienia where (id,wartosc) in (select id, min(wartosc) from zamowienia);
 ERROR 1054 (42S22): Unknown column 'cena' in 'field list'
 MariaDB [hw1]> select `towar-id`, id, wartosc from zamowienia where (id,wartosc) in (select id, min(wartosc) from zamowienia);
 Empty set (0.00 sec)
 
 MariaDB [hw1]> select `towar-id`, id, wartosc from zamowienia where (6,wartosc) in (select id, min(wartosc) from zamowienia);
 Empty set (0.00 sec)
 
 MariaDB [hw1]> select `towar-id`, id, wartosc from zamowienia where (1,wartosc) in (select id, min(wartosc) from zamowienia);
 +----------+----+---------+
 | towar-id | id | wartosc |
 +----------+----+---------+
 |        4 |  6 |    2.28 |
 +----------+----+---------+
 1 row in set (0.00 sec)
 
 MariaDB [hw1]>