zadania

  Spis wszystkich zadan, ktore robilem z MySQL

  1) SORTOWANIE (dodajac DESC na koncu, po first_name posortuje na odwrot)
  USE sql_store;
  SELECT customer_id, first_name
  SELECT *
  FROM customers
  ORDER BY first_name
  ORDER BY state, first_name DESC

  2) WYBIERA DANE KOLUMNY I ROBI NOWA KOLUMNE Z PUNKTY+10
  SELECT 
    first_name,
    last_name,
     points,
     points + 10 AS 'discount factor'
    
  FROM customers

  3) UNIQUE STATES (distinct)
  SELECT distinct state
  SELECT state
  FROM customers

  4) TWORZENIE NOWEGO (TYMCZASOWEGO) KOLUMNY 'new_price' W CELU PODGLADU
  SELECT
 	name,
     unit_price,
     unit_price * 1.1 AS 'new_price'
  FROM products

  5) SZUKANIE >< i ! 
  SELECT *
  FROM customers
  WHERE state != 'TX'
  WHERE birth_date >= '1990-01-01'   Born after 1990

  6) SZUKANIE W OKRESLONYCH ZAKRESACH CZASOWYCH
  SELECT *
  FROM orders
  WHERE order_date > '2018-01-01' and order_date < '2019-01-31'

  7) GET ALL PRODUCT_IDS IN ORDER 6 WHERE THE TOTAL IS OVER 30
  SELECT *
  FROM order_items
  WHERE order_id = 6 and unit_price * quantity > 30

  8) WHERE item IN dziala podobnie do OR, ale jest krotsze i szybsze
  SELECT *
  FROM Customers
  WHERE state IN ('VA', 'FL', 'GA')
  WHERE state NOT IN ('VA', 'FL', 'GA')

  9) ZNAJDZ PRODUKTY Z ILOSCIA 49, 38 lub 72
  SELECT *
  FROM products
  WHERE quantity_in_stock IN (49, 38, 72)

  10) WHERE item BETWEEN x AND y - SZUKANIE Z PRZEDZIALU
  SELECT *
  FROM customers
  WHERE points BETWEEN 1000 AND 3000

  11) RETURN CUSTOMERS FROM BETWEEN 01.01.1990 i 01.01.2000
  SELECT *
  FROM Customers
  WHERE birth_date BETWEEN '1990-01-01' AND '2000-01-01'

  12) SZUKANIE DANYCH ZNAKOW Z "LIKE" (REGEXP LEPSZY w 13)
  SELECT *
  FROM customers
  WHERE last_name LIKE 'b%'	  Zaczyna sie od "B" i ma nieokreslona liczbe znakow po B
  WHERE last_name LIKE '%b%'   "B" gdziekolwiek
  WHERE last_name LIKE '_____y'   "_" oznacza znak

  13) GET CUSTOMERS WHOSE ADDRESSES HAVE TRAIL OR AVENUE IN THEM
  AND THEN GET PHONE NUMBERS WHICH END WITH "9"
  JESLI DODAMY "NOT" PRZEZ LIKE TO BEDZIE SZUKALO NP TYLKO NUMEROW BEZ 9 NA KONCU
  SELECT *
  FROM customers
  WHERE address LIKE '%trail%' OR '%avenue%'
  WHERE phone LIKE '%9' 

  14) REGEXP - Szukanie expressions
  SELECT *
  FROM customers
  WHERE last_name REGEXP 'field|mac'

  ^ na poczatku oznacza, ze string musi zaczynac sie od danych znakow
  $ na koncu oznacza, ze musi konczyc sie danym stringiem
  | oznacza OR

  Jesli chcemy np zeby imie zawieralo znak "e" a przed "e" byly jakies inne okreslone znaki to
  WHERE last_name REGEXP '[gim]e'	bedzie to szukalo osob ktore maja znaki ge ie me po sobie
  Mozna tez np [a-h]e, czyli od a do h, a po tym ma byc e

  15) EXERCISE WITH REGEXP
  SELECT *
  FROM customers
  WHERE first_name REGEXP 'elka|ambur'
  WHERE last_name REGEXP 'EY$|ON$'
  WHERE last_name REGEXP '^my|se'
  WHERE last_name REGEXP 'b[ru]'

  Get customers whose
  First name are ELKA or AMBUR
  Last name end with EY or ON
  Last name starts with MY or contains SE
  Last name contains B followed by R or U

  16) Wyswietla wszystkich, ktorzy nie maja podanego nr telefonu
  Mozna tez zrobic "IS NOT NULL"
  SELECT *
  FROM customers
  WHERE phone IS NULL

  17) FIND orders that were not shipped
  SELECT *
  FROM orders
  WHERE shipped_date IS NULL

  18) EXERCISE - Posegreguj order_items dla id = 2 malejaco, dla ceny calkowitej
  SELECT *, quantity * unit_price AS total_price
  FROM order_items
  WHERE order_id = 2 ORDER BY total_price DESC

  19) LIMIT - okreslanie ilosci wynikow jaka ma sie wyswietlic
  SELECT *
  FROM customers
  LIMIT 3
  Jesli chcemy wysietlic od np 7tego klienta i np tylko 3 zeby pokazalo to 
  LIMIT 6,3 

  20) Get top 3 the most loyal customers (the most points)
  SELECT *
  FROM customers
  ORDER BY points DESC
  LIMIT 3

  21) Laczenie kolumn z kilku tabel
  SELECT o.customer_id, order_id, first_name, last_name
  FROM orders o
  JOIN customers c ON o.customer_id = c.customer_id

  22) EXERCISE Wyswietl order_id, product_id, name of the product, quantity and unit_price
  SELECT order_id, p.product_id, p.name, quantity, oi.unit_price
  FROM order_items oi
  JOIN products p ON oi.product_id = p.product_id
  ORDER BY order_id

  23) JOINING ACROSS DATABASES chcemy polaczyc z sql_store tabele order_items
  z sql_inventory tabela products
  SELECT *
  FROM sql_store.order_items oi
  JOIN sql_inventory.products p ON p.product_id = oi.product_id

  24) SELF JOINS w sql_hr mamy pracownikow. chcemy zeby pozalo kto przed kim odpowiada (manager)
  USE sql_hr;
  SELECT
  	e.first_name,
  	e.last_name,
  	m.first_name AS manager
  FROM sql_hr.employees e
  JOIN sql_hr.employees m ON e.reports_to = m.employee_id

  25) JOINING MULTIPLE TABLES - Chcemy polaczyc zamowienie z klientem i status zamowienia
  USE sql_store;
  SELECT
  	o.order_id,
  	o.order_date,
  	c.first_name,
  	c.last_name,
  	os.name AS status
  FROM orders o
  JOIN customers c
  	ON o.customer_id = o.customer_id
  JOIN order_statuses os
  	ON o.status = os.order_status_id

  26) EXERCISE - W sql_invoicing mamy client_id, payment_method i reszte danych. 
  Chcemy to tak polaczyc zeby za PM wyswietlalo np Credit Card, a za clien_id jego imie i nazwisko
  USE sql_invoicing;
  SELECT
  	p.payment_id,
  	c.name,
  	p.client_id,
  	p.invoice_id,
  	p.date,
  	p.amount,
  	pm.name AS Payment_Method
  FROM payments p
  JOIN clients c
  	ON p.client_id = c.client_id
  JOIN payment_methods pm
  	ON pm.payment_method_id = p.payment_method

  27) ZLOZONE WARUNKI JOIN (COMPOSITE JOIN CONDITIONS) np JOIN z AND
  USE sql_store;
  SELECT *
  FROM order_items oi
  JOIN order_item_notes oin
  	ON oi.product_id = oin.product_id
  	AND oi.order_id = oin.order_id

  28) Implicit Join Syntax (Nie trzeba JOIN, ale trzeba WHERE(krocej z WHERE) Not recommended tho
  SELECT *
  FROM order o, clients c
  WHERE o.customer_id = c.customer_id

  29) OUTER JOINS 
  LEFT JOIN - Wszystko z lewej tabeli czyli z FROM jest przenoszone nawet jesli nie spelnia wymagan
  RIGHT JOIN - Wszystki z prawej tabeli czyli JOIN jest przenoszone        ||     -
  USE sql_store;
  SELECT
  	c.customer_id,
      c.first_name,
      o.order_id
  FROM customers c
  LEFT JOIN orders o
  	ON c.customer_id = o.customer_id
  ORDER BY c.customer_id

  30) EXERCISE Write a query that produces: product_id, name, quantity form orders_item table
  so we can see how many times each item product was bought. MA POKAZAC NAWET TE NIGDY NIE KUPIONE
  USE sql_store;
  SELECT
  	oi.product_id,
  	p.name,
  	oi.quantity
  FROM order_items oi
  RIGHT JOIN products p
  	ON oi.product_id = p.product_id

  31) OUTER JOINS BETWEEN MULTIPLE TABLES
  USE sql_store;
  SELECT
  	c.customer_id,
  	c.first_name,
  	o.order_id,
   sh.name AS shipper
  FROM customers c
  LEFT JOIN orders o
  	ON c.customer_id = o.customer_id
  LEFT JOIN shippers sh
  	ON o.shipper_id = sh.shipper_id
  ORDER BY c.customer_id


  32) EXERCISE Wyswietl order_date, order_id, first_name, shipper (null tez) i status
  USE sql_store;
  SELECT
  	o.order_date,
   o.order_id,
  	sh.name AS Shipper,
  	c.first_name AS CUSTOMER,
  	os.name AS Status
  FROM orders o
  JOIN customers c 
  	ON o.customer_id = c.customer_id
  LEFT JOIN shippers sh
  	ON sh.shipper_id = o.shipper_id
  LEFT JOIN order_statuses os
  	ON o.status = os.order_status_id
  ORDER BY os.name

  33) Wyswietl z sql_hr kto komu reportuje i tych ktorzy nikomu nie reportuja
  USE sql_hr;
  SELECT
  	e.employee_id,
   e.first_name,
   m.first_name
  FROM employees e
  JOIN employees m
  	ON e.reports_to = m.employee_id

  34) USING - Jesli dwie tabele MAJA TA SAMA NAZWE np customer_id (np w ordersach i customersach)
  to mozna uzyc USING
  USE sql_store;
  SELECT
  	o.order_id,
   c.first_name,
   sh.name AS shipper
  FROM orders o
  JOIN customers c
  	USING (customer_id)
  LEFT JOIN shippers sh
  	USING (shipper_id)


  35) USING Z warunkami AND
  USE sql_store;
  SELECT *
  FROM order_items oi
  JOIN order_item_notes oin
  	USING (order_id, product_id)

  36) EXERCISE z sql_invoicing wyswietl: date, client, amount, payment type
  USE sql_invoicing;
  SELECT
  	p.date,
  	c.name AS Customer,
  	p.amount,
  	pm.name AS Payment
  FROM payments p
  JOIN clients c
  	USING (client_id)
  LEFT JOIN payment_methods pm
  	ON p.payment_method = pm.payment_method_id

  37) CROSS JOIN - We use this to combine or join every record from the first table with every
  record in the second table. Masz tabele z rozmiarami i tabele z kolorami. I np mozesz polaczyc
  obie tabele zeby sprawdzic jaki kolor masz jeszcze dla jakiegos rozmiaru
  SELECT
  	c.first_name AS Customer
    p.name AS Product
  FROM customers c
  LUB //FROM customers c, products p (wtedy juz bez cross join)
  CROSS JOIN products p
  ORDER BY first_name

  38) UNIONS - Laczenie wynikow z wielu zapytan (querries) WAZNE!!!!!!!!!!
  USE sql_store;
  SELECT
  	order_id,
  	order_date,
  	'Active' AS Status
  FROM orders
  WHERE order_date >= '2019-01-01'
  UNION
  SELECT
  	order_id,
  	order_date,
   'Archived' AS Status
  FROM orders
  WHERE order_date < '2019-01-01'

39) -- Write a query to produce result: customer_id, first_name, points, type, gdzie type to
-- kolumna, ktora ma bronze/silver/gold values zaleznie od punktow, ktore ma customer
-- <2000 - Bronze / 2000-3000 Silver / 3000+ Gold
-- I sorted by first_name
  USE sql_store;
  SELECT
  c.customer_id,
  c.first_name,
  c.points,
  'Bronze' AS type
  FROM customers c
  WHERE points < 2000
  UNION
  SELECT
  c.customer_id,
  c.first_name,
  c.points,
  'Silver' AS type
  FROM customers c
  WHERE points BETWEEN 2000 AND 3000
  UNION
  SELECT
  	c.customer_id,
  	c.first_name,
  	c.points,
  	'Gold' AS type
  FROM customers c
  WHERE points > 3000
  ORDER BY first_name

  40) Column attributes AND Inserting a single row
  -- Dodajemy po prostu customera nowego. id sie samo inkrementuje, bo jest tak zaznaczone
  -- wiec go nie podajemy, a pozniej podajemy tylko reszte zmiennych, jakie ma customer
  INSERT INTO customers (
  	first_name,
  	last_name,
     birth_date,
    address,
    city,
    state)
VALUES (
	'John',
    'Smith',
    '1990-01-01',
    'address',
    'city',
    'CA')
    
41) Inserting multiple rows -- shipper_id sie samo dodaje wiec go nie podajemy i pozniej tylko
-- jako inna zmienna mamy "name"
INSERT INTO shippers (name)
VALUES 
	('Shipper1'),
    ('Shipper2'),
    ('Shipper3')

42) Inserting hierarchical rows
INSERT INTO orders (customer_id, order_date, status)
VALUES (1, '2020-01-02', 1);
-- U gory najpierw dodajemy order (order_id sie samo dodaje) i podajemy reszte wartosci
INSERT INTO order_items
VALUES
	(LAST_INSERT_ID(), 1, 1, 2.95),
    (LAST_INSERT_ID(), 2, 1, 4.95)
-- A tutaj do ostatnio dodanego id dodajemy inne wartosci w innej tabeli

43) Creating a copy of a table "how to copy data from one table to another"
CREATE TABLE orders_archived AS
SELECT *
FROM orders

44) Usuwanie danych z tabeli
Prawym na tabele i truncate

45) Po usunieciu wszystkiego z orders_archived dodajemy np tylko zamowienia przed 2019r
INSERT INTO orders_archived
SELECT *
FROM orders
WHERE order_date < '2019-01-01'

46) Z invoices przekopiuj wszystko do invoiced archived, ale zamiast client_id ma byc podane 
jego imie i ma przekopiowac tylko te invoices, ktore maja payment_date
USE sql_invoicing;
-- 3) na koncu kopiujemy
CREATE TABLE invoices_archived AS
-- 1) najpierw select i join
SELECT
	i.invoice_id,
    i.number,
	c.name,
    i.invoice_total,
    i.payment_total,
    i.invoice_date,
    i.due_date,
    i.payment_date
FROM invoices i
JOIN  clients c 
	USING (client_id)
-- 2) pozniej not null
WHERE i.payment_date IS NOT NULL 

47) Updating a single row
USE sql_invoicing;
UPDATE invoices
SET payment_total = 10, payment_date = '2020-03-03'
WHERE invoice_id = 1
-- //
UPDATE invoices
SET
	payment_total = invoice_total * 0.5
    payment_date = due_date
WHERE invoice_id = 3

48) Updating multiple rows
UPDATE invoices
SET
	payment_total = invoice_total * 0.5,
    payment_date = due_date
WHERE client_id IN (3,4)

49) Write a query to give everyone born before 1990 extra 50points
USE sql_store;
UPDATE customers
SET points = points + 50
WHERE birth_date <= '1990-01-01'

50) Using subqueries in updates