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
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
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;
Last updated