PostgreSQL

--SELECT SELECT * FROM film;

--DISTINCT SELECT DISTINCT release_year FROM film; SELECT DISTINCT rental_rate FROM film; SELECT DISTINCT rating FROM film;

--COUNT SELECT COUNT (*) FROM payment; SELECT COUNT (amount) FROM payment; SELECT amount FROM payment; SELECT DISTINCT amount FROM payment; SELECT COUNT (DISTINCT amount) FROM payment;

--WHERE SELECT * FROM customer WHERE first_name = 'Jared';

SELECT COUNT (title) FROM film WHERE rental_rate > 4 AND replacement_cost >= 19.99 AND rating = 'R';

SELECT COUNT (title) FROM film WHERE rating = 'R' OR rating = 'PG-13';

SELECT * FROM film WHERE rating != 'R';

SELECT email FROM customer WHERE first_name = 'Nancy' AND last_name = 'Thomas';

SELECT description FROM film WHERE title = 'Outlaw Hanky';

SELECT phone FROM address WHERE address = '259 Ipoh Drive';

--ORDER BY SELECT * FROM customer ORDER BY first_name DESC; --malej膮co (od Z do A)

SELECT FROM customer ORDER BY first_name ASC; --rosn膮co (od A do Z) mo偶na u偶ywa膰 bez ASC i w domy艣le ORDER BY b臋dzie rosn膮co

SELECT store_id, first_name, last_name FROM customer ORDER BY store_id DESC, first_name ASC;

--LIMIT SELECT * FROM payment WHERE amount != 0.00 ORDER BY payment_date DESC LIMIT 5;

SELECT * FROM payment -- TIP do zobaczenia szybkiego jak wygl膮da dana tabela LIMIT 1;

--ORDER BY Challenge SELECT * FROM payment LIMIT 1;

SELECT customer_id FROM payment ORDER BY payment_date ASC LIMIT 10;

SELECT COUNT (length) FROM film WHERE length <= 50;

--BETWEEN SELECT * FROM payment WHERE amount NOT BETWEEN 8 AND 9;

SELECT * FROM payment WHERE payment_date BETWEEN '2007-02-01' AND '2007-02-15'; --daty musz膮 by膰 tak podawane 'YYYY-MM-DD')

--trzeba uwa偶a膰 na dni tygodnia, bo w przypadku tego nie mamy podanych 偶adnych dat do 14.02.2007 SELECT * FROM payment WHERE payment_date BETWEEN '2007-02-01' AND '2007-02-14';

--IN SELECT COUNT(*) FROM payment WHERE amount IN (0.99,1.98,1.99); -- 0.99 OR 1.98 OR 1.99

SELECT COUNT(*) FROM payment WHERE amount NOT IN (0.99,1.98,1.99);

SELECT * FROM customer WHERE first_name IN ('John','Jake','Julie');

--LIKE and ILIKE --LIKE - case-sensitive (odr贸偶nia du偶e i ma艂e litery) --ILIKE - case-insensitive (nie odr贸偶nia wielkich i ma艂ych liter) -- nazwy zaczynaj膮ce si臋 od wielkiej litery A -- WHERE name LIKE 'A%' -- nazwy ko艅cz膮ce si臋 liter膮 a -- WHERE name LIKE '%a' -- jaki艣 znak '' << 3 niewiadome znaki -- WHERE title LIKE 'Mission Impossible ' <偶eby sprawdzi膰 cz臋艣膰 filmu -- WHERE value LIKE 'VERSION#' -- WHERE name LIKE '_her%' -- Cheryl -- Theresa -- Sherri -- wszystkie 3 pasuj膮 :)

SELECT FROM customer WHERE first_name LIKE 'J%' AND last_name LIKE 'S%';

SELECT * FROM customer WHERE first_name ILIKE 'j%' AND last_name ILIKE 's%';

SELECT * FROM customer WHERE first_name LIKE '%er%'; --% procent mo偶e by膰 te偶 pustym znakiem przyk艂adowo ko艅czy si臋 Imi臋 na er

SELECT * FROM customer WHERE first_name LIKE '_her%';

SELECT * FROM customer WHERE first_name NOT LIKE '_her%';

SELECT * FROM customer WHERE first_name LIKE 'A%' AND last_name NOT LIKE 'B%' ORDER BY last_name;

--GENERAL CHALLENGE (TEST) --1. Ile transakcji by艂o wi臋kszych ni偶 5 dolar贸w? SELECT COUNT(amount) FROM payment WHERE amount > 5;

--2. Ilu aktor贸w ma Imi臋 zaczynaj膮ce si臋 na liter臋 P SELECT COUNT(first_name) FROM actor WHERE first_name LIKE 'P%';

--3. Ile unikalntych dzielnic maj膮 nasi klienci (districts - dzielnice) SELECT COUNT(DISTINCT district) FROM address;

--4. Retrieve the list of names for those distinct districts from the previous question. SELECT DISTINCT district FROM address;

--5. How many films have a rating of R and a replacement cost between $5 and $15? SELECT COUNT(*) FROM film WHERE rating = 'R' AND replacement_cost BETWEEN 5 AND 15;

--6. How many films have the word Truman somewhere in the title? SELECT COUNT(title) FROM film WHERE title LIKE '%Truman%';

--Aggregate function--

--AVG() -艣rednia zwracana jest jako float np. (2.342418...) jak u偶yjemy ROUND() to mo偶emy okre艣li膰 liczb臋 po przeicnku --COUNT() -liczba --MAX() -max warto艣膰 --MIN() -min warto艣膰 --SUM() -suma --Dzia艂aj膮 tylko w klasie SELECT lub HAVING

SELECT MIN(replacement_cost) FROM film;

SELECT MAX(replacement_cost) FROM film;

SELECT MAX(replacement_cost), MIN(replacement_cost) FROM film;

SELECT COUNT(*) FROM film;

SELECT AVG(replacement_cost) FROM film;

SELECT AVG(replacement_cost) --ogromna liczba po przecinku FROM film;

SELECT ROUND(AVG(replacement_cost),2) --zaokr膮glenie do 2 miejsc po przecinku FROM film;

SELECT ROUND(AVG(replacement_cost),4) FROM film;

SELECT SUM(replacement_cost) FROM film;

--GROUP BY--

SELECT customer_id,SUM(amount) FROM payment GROUP BY customer_id ORDER BY SUM(amount) DESC;

SELECT customer_id,COUNT(amount) FROM payment GROUP BY customer_id ORDER BY COUNT(amount) DESC;

SELECT staff_id,customer_id,SUM(amount) FROM payment GROUP BY staff_id,customer_id ORDER BY SUM(amount);

--DATE-- --zmiana daty z czasem na sam膮 dat臋 YYYY-MM-DD SELECT DATE(payment_date),SUM(amount) FROM payment GROUP BY DATE(payment_date) ORDER BY SUM(amount) DESC;

--GROUP BY CHALLENGES--

/*1. We have two staff members, with Staff IDs 1 and 2. We want to give a bonus to the staff member that handled the most payments. (Most in terms of number of payments processed, not total dollar amount).

How many payments did each staff meember handle and who gets the bonus?*/ SELECT staff_id,COUNT(payment_id) FROM payment GROUP BY staff_id ORDER BY COUNT(payment_id) DESC;

/*2. Corporate HQ is conducting a study on the relationship between replecement cost and a movie MPAA rating (e.g G, PG, R, etc...).

What is the average replacement cost per MPAA rating? Note: You may need to expand the AVG column to view correct results */ SELECT rating, ROUND(AVG(replacement_cost),2) FROM film GROUP BY rating ORDER BY AVG(replacement_cost) DESC;

/*3. We are running a promotion to reward our top 5 customers with coupons.

What are the customer ids of the top 5 customers by total spend?*/ SELECT customer_id, SUM(amount) FROM payment GROUP BY customer_id ORDER BY SUM(amount) DESC LIMIT 5;

--HAVING--

--U偶ywamy tylko do filtrowania po u偶yciu GROUP BY-- SELECT customer_id, SUM(amount) FROM payment GROUP BY customer_id HAVING SUM(amount) > 100;

SELECT store_id, COUNT(customer_id) FROM customer GROUP BY store_id;

SELECT store_id, COUNT(customer_id) FROM customer GROUP BY store_id HAVING COUNT(customer_id) > 300;

--CHALLANGE HAVING-- /*1. We are launching a platinum service for our most loyal customers. We will assign platinum status to customers that have had 40 or more transaction payments.

What customer_ids are eligible for platinum status?*/ SELECT customer_id, COUNT(payment_id) FROM payment GROUP BY customer_id HAVING COUNT(payment_id) >= 40 ORDER BY COUNT(payment_id) DESC;

/2 What are the customer ids of customers who have spent more than $100 in payment transactions with our staff_id member 2? / SELECT customer_id, SUM(amount) FROM payment WHERE staff_id =2 GROUP BY customer_id HAVING SUM(amount) >100 ORDER BY SUM(amount) DESC;

--Assessment Test1-- /1. Return the customer IDs of customers who have spent at least $110 with the staff member who has an ID of 2./ SELECT customer_id, SUM(amount) FROM payment WHERE staff_id = 2 GROUP BY customer_id HAVING SUM(amount) >110; --The answer should be customers 187 and 148.

/2. How many films begin with the letter J?/ SELECT COUNT(title) FROM film WHERE title LIKE 'J%'; -- The answer should be 20.

/3. What customer has the highest customer ID number whose name starts with an 'E' and has an address ID lower than 500?/

SELECT first_name, last_name FROM customer WHERE first_name LIKE 'E%' AND address_id < 500 ORDER BY customer_id DESC LIMIT 1; --The answer is Eddie Tomlin

--JOINS--

/INNER JOINS OUTER JOINS FULL JOINS UNIONS (LEFT JOINS) - pytanie mia艂em na rozmowie o to /

--AS Statement-- ----------------TYMCZASOWA zmiana nazwy kolumny do jednorazowego wy艣wietlania

SELECT COUNT(*) AS num_transactions FROM payment;

SELECT customer_id, SUM(amount) AS tqotal_spentotal_spent FROM payment GROUP BY customer_id;

SELECT customer_id, SUM(amount) FROM payment GROUP BY customer_id HAVING SUM(amount) > 100;

SELECT customer_id, SUM(amount) AS total_spent FROM payment GROUP BY customer_id HAVING SUM(amount) > 100;

SELECT customer_id, amount AS new_name FROM payment WHERE amount > 2;

--Inner Joins-----

SELECT * FROM payment INNER JOIN customer ON payment.customer_id = customer.customer_id;

--szukamy tylko klient贸w, kt贸rzy wykonali jak膮艣 p艂atno艣膰 SELECT payment_id,payment.customer_id,first_name FROM payment INNER JOIN customer ON payment.customer_id = customer.customer_id;

--Full Outer Joins--

--FULL OUTER JOIN --suma tabeli lewej z tabel膮 praw膮 SELECT * FROM customer FULL OUTER JOIN payment ON customer.customer_id = payment.customer_id;

--szukamy w tabeli warto艣ci null po zsumowaniu lewej z praw膮 -- czy dane s膮 kompletne. Nie ma kogo艣 kto p艂aci艂 i nie poda艂 maila SELECT * FROM customer FULL OUTER JOIN payment ON customer.customer_id = payment.customer_id WHERE customer.customer_id IS null OR payment.payment_id IS null;

SELECT COUNT(DISTINCT customer_id) FROM payment; -- 599 unique_id

SELECT COUNT(DISTINCT customer_id) FROM customer; -- 599 unique_id --nie koniecznie to nam m贸wi, 偶e 599 i 599 wynik贸w 艂膮cz膮 si臋 w jedno.

--LEFT OUTER JOIN--

--LEFT OUTER JOIN == LEFT JOIN - mo偶na pisa膰 dwie wersje!

--Tabela pierwsza (LEWA) 艂膮czy si臋 z cz臋艣ci膮 wsp贸ln膮 prawej i zostaje LEWA plus dodatkowe informacje -- z prawej tabeli jako cz臋艣膰 wsp贸lna

/odpowiada na pytanie jakie filmy s膮 mo偶liwe do wypo偶yczenia aktualnie/ SELECT film.film_id,film.title,inventory_id,store_id FROM film LEFT JOIN inventory ON inventory.film_id = film.film_id;

--LEFT OUTER JOIN WHERE - Tylko lewa tabela BEZ cz臋艣ci wsp贸lnej i prawej tabeli

/ odpowiadamy na pytanie jakich film贸w z naszej wypo偶yczalni aktualnie nie mo偶na wypo偶yczy膰 bo s膮 poza wypo偶yczalni膮 tzn s膮 wypo偶yczone/ SELECT film.film_id,film.title,inventory_id,store_id FROM film LEFT JOIN inventory ON inventory.film_id = film.film_id WHERE inventory.film_id IS null;

--RIGHT OUTER JOIN -- to samo co LEFT JOIN, ale odwr贸cona czyli prawa tabela jest brana pod uwag臋

SELECT film.film_id,film.title,inventory_id,store_id FROM film RIGHT JOIN inventory ON inventory.film_id = film.film_id;

SELECT film.film_id, film.title, inventory_id,store_id FROM film RIGHT JOIN inventory ON inventory.film_id = film.film_id WHERE inventory.film_id IS null;

--UNION--

--Union 艂膮czenie dw贸ch tabel przyk艂adowo zarobki firm z Q1 i Q2 /SELECT FROM Sales2021_Q1 UNION SELECT FROM Sales20201_Q2 /

--JOIN CHALLENGES--

/ 1.California sales tax laws have changed and we need to alert our customers to this through email What are emails of the customers who live in California?/

SELECT district, email FROM customer INNER JOIN address ON address.address_id = customer.address_id WHERE district = 'California';

/ 2. A cusomer walks in and is a huge fan of the actor "Nick Wahlberg" and wants to know which movie he is in. Get a list of all the movies "Nick Wahlberg" has been in./

SELECT title, first_name, last_name FROM actor INNER JOIN film_actor ON actor.actor_id = film_actor.actor_id INNER JOIN film ON film_actor.film_id = film.film_id WHERE first_name = 'Nick' AND last_name = 'Wahlberg';

--Timestampt--

/ TIME contains only time DATE contains only date TIMESTAMP contains date and time TIMESTAMPTZ contains date,time and timezone / -- dane mo偶na zawsze usun膮膰, lecz jak niezapisali艣my daty a j膮 potrzebuje to przepad艂o. /* TIMEZONE NOW TIMEOFDAY CURRENT_TIME CURRENT_DATE

*/

SHOW ALL; -- pokazanie r贸偶nych danych pgadmin

SHOW TIMEZONE; -- pokazanie strefy czasowej

SELECT NOW(); -- pokazuje aktualny timestamp with time zone

SELECT TIMEOFDAY(); -- pokazuje aktualny dzie艅 i czas jako string + timezone

SELECT CURRENT_TIME; -- sam aktualny czas

SELECT CURRENT_DATE; -- aktualna data

--EXTRACT-- (wyci膮g)

/ EXTRACT() AGE() TO_CHAR() /

/ EXTRACT: 'zawiera' YEAR MONTH DAY WEEK QUARTER /

SELECT EXTRACT(YEAR FROM payment_date) AS my_year FROM payment;

SELECT EXTRACT(MONTH FROM payment_date) AS pay_month FROM payment;

SELECT EXTRACT(QUARTER FROM payment_date) AS quarter_year FROM payment;

SELECT AGE(payment_date) FROM payment;

SELECT TO_CHAR(payment_date, 'MONTH-YYYY') --FEBUARY-2007 FROM payment;

SELECT TO_CHAR(payment_date, 'mon/dd/YYYY') --feb/15/2007 FROM payment;

SELECT TO_CHAR(payment_date, 'MM-dd-YYYY') --02-15-2007 FROM payment;

SELECT TO_CHAR(payment_date, 'dd-MM-YYYY') --15-02-2007 FROM payment;

--w dokumentacji pokazane jest co i jak mo偶na stosowa膰 -- https://www.postgresql.org/docs/12/functions-formatting.html

--CHALLENGE TASKS

--Timestamps and Extract

/[occur - pojawi膰 si臋] 1. During which months did payments occur? Format your answer to return back the full month name. / SELECT DISTINCT(TO_CHAR(payment_date, 'MONTH')) FROM payment;

/ 2. How many payments occurred on a Monday? / SELECT COUNT(*) FROM payment WHERE TO_CHAR(payment_date, 'DAY') LIKE 'MONDAY%';

--albo SELECT COUNT(*) FROM payment WHERE EXTRACT(dow FROM payment_date) = 1; -- =1 odpowiada poniedzia艂kowi =0 niedzieli, =2 wtorkowi itp itd... -- dow to skr贸t day of the week

--Matchematical Functions and Operators

--https://www.postgresql.org/docs/12/functions-math.html

SELECT ROUND(rental_rate/replacement_cost,2)*100 AS percent_cost FROM film;

SELECT 0.1 * replacement_cost AS deposit FROM film;

--STRING Functions and Operators

--https://www.postgresql.org/docs/12/functions-string.html

SELECT LENGTH(first_name) FROM customer;

SELECT first_name || ' ' || last_name AS full_name FROM customer; --laczenie imienia i nazwiska

SELECT UPPER(first_name) || ' ' || LOWER(last_name) AS full_name FROM customer;

--tworzenie maila ze wzoru 1 litera imienia i nazwisko + @gmail.com --creating customer email addresses SELECT LOWER(LEFT(first_name, 1)) || LOWER(last_name) || '@gmail.com' AS custom_email FROM customer;

--SubQuery--

/ 1. How can we get a list of students who scored better than the average grade? /

--najpierw tworzymy subquery i tak samo jest odczytywane przez SQL SELECT title,rental_rate FROM film WHERE rental_rate > (SELECT AVG(rental_rate) FROM film);

SELECT film_id, title FROM film WHERE film_id IN (SELECT inventory.film_id FROM rental INNER JOIN inventory ON inventory.inventory_id = rental.inventory_id WHERE return_date BETWEEN '2005-05-29' AND '2005-05-30') ORDER BY film_id;

/ We wanted to find customers who have at least one payment whose amount is greater than 11. We want to grab the first name and last name of those customers. / SELECT first_name,last_name FROM customer AS c WHERE EXISTS (SELECT * FROM payment as p WHERE p.customer_id = c.customer_id AND amount > 11);

SELECT first_name,last_name FROM customer AS c WHERE NOT EXISTS -- roznica jako NOT EXSISTS (SELECT * FROM payment as p WHERE p.customer_id = c.customer_id AND amount > 11);

--Selft-Join----

/*uzywamy wtedy kiedy chcemy w jednej tabeli uzy膰 tej samej kolumny. Przyk艂ad || emp_id || name || report_id|| | 1 | Andrew | 3 | | 2 | Bob | 3 | | 3 | Charlie | 4 | | 4 | David | 1 |

> Chcemy wypisa膰 kto komu musi wys艂a膰 raport z imienia || name || rep || | Andrew | Charlie | | Bob | Charlie | | Charlie | David | | David | Andrew |

Syntax SELECT tableA.col, tableB.col FROM table AS tableA JOIN table AS tableB ON tableA.some_col = tableB.other_col /

-- 1. Find all the pairs of films that have the same lenght SELECT title,length FROM film WHERE length = 117;

SELECT f1.title, f2.title, f1.length FROM film AS f1 INNER JOIN film AS f2 ON f1.film_id != f2.film_id AND f1.length = f2.length;

PreviousVirtualBoxNextInstall DBeaver

Last updated

Was this helpful?