SQL Course (PART-1)

First of all I want to say that all the queries than has been written and the table on which different queries has been executed are done on a table that has already been uploaded in the "postgreSQL". Later on we will see all the queries that how to create and manipulate the tables according to the user needs.  

SELECT Statement:    

SELECT is the most common statement used, and it allows us to retrieve information from the table.

* is used to query everything, which increases the traffic between the database server and application and also slows down the retrieval of results.

Syntax:

SELECT column_name1,.... FROM table_name;

SELECT * FROM city;

SELECT DISTINCT:

DISTINCT is used to query the unique data from the table, will not show the duplicate values.

 

Syntax:

SELECT DISTINCT (column_name1,column_name2,…) FROM table_name;

SELECT DISTINCT (release_year, rental_rate) FROM film;

 

COUNT:

COUNT() func. returns the number of input rows that match the specific condition of a query.

 

Syntax:

SELECT COUNT (column_name) FROM table_name;

Both will return same output since it will count all the rows from table

SELECT COUNT (rental_rate) FROM film;

SELECT COUNT (*) FROM film;

It will count specific unique data

SELECT COUNT(DISTINCT rental_rate) FROM film;

 

SELECT WHERE:

WHERE clause appears immediately after the FROM clause of the SELECT statement, the conditions are used to filter the rows returned from the SELECT statement. AND,OR and NOT operator for multiple conditions.

 

Syntax:

SELECT column_name1,… FROM table_name

WHERE conditions;

 

SELECT * FROM customer

WHERE first_name = 'Jared';

 

SELECT * FROM film

WHERE rental_rate > 4 AND replacement_cost >= 19;

 

ORDER BY:

ORDER BY is used to sort rows based on a column value,in either ascending(ASC) or descending order(DESC).

ORDER BY is applied at the end of the query, since we want to do any selection and filtering first, before finally sorting.

 

Syntax:

SELECT column_name1,column_name2,….. FROM table_name

ORDER BY column_name ASC;

 

SELECT store_id,first_name FROM customer

ORDER BY store_id,first_name ASC;

LIMIT:

LIMIT command allows us to limit the number of rows returned for a query. Useful for not wanting to return every single row in a table, but only view the top few rows to get an idea of the table layout.

LIMIT goes at the very end of the query request and to be the last command to be executed.

Syntax:

SELECT column_name1,…. FROM table_name

LIMIT (1,2,3,…..,n);

 

SELECT * FROM payment

WHERE amount != 0

ORDER BY payment_date DESC

LIMIT 5;

BETWEEN:

BETWEEN operator can be used to match a value against a range of values. You can also combine BETWEEN with the NOT logical operator.

 

Syntax:

SELECT column_name1,………. FROM payment

WHERE column_name BETWEEN data_1 AND data_2;

 

SELECT * FROM payment

WHERE payment_date BETWEEN '2007-02-01' AND '2007-02-15'

LIMIT 10;

IN:

IN operator to create a condition that checks to see if a value is included in a list of multiple options.

Syntax:

SELECT column_name1,…. FROM table_name

WHERE column_name IN (values);

 

SELECT * FROM payment

WHERE amount IN (0.99,1.99,6.99);

SELECT * FROM payment

WHERE amount NOT IN (0.99,1.99,6.99);

 

LIKE and ILIKE:

LIKE is case sensitive.

ILIKE is case in-sensitive.

LIKE operator allows us to perform pattern matching against string data with use of wildcards:
1) % : matches any sequence of character(A% : all names start with capital A, %a : all names end with

 small a)

2) _ : matches any single character

Syntax:

SELECT column_name1,…. FROM table_name

WHERE column_name1 LIKE 'expression' AND column_name2 LIKE 'expression';

 

SELECT first_name,last_name FROM customer

WHERE first_name LIKE 'J%' AND last_name LIKE 'S%';

SELECT first_name FROM customer

WHERE first_name LIKE '_her%';

SELECT first_name FROM customer

WHERE first_name NOT LIKE '_her%';

AGGREGATION FUNCTION:

Main idea behind aggregate function is that to take multiple inputs and return single output. Always called only in the SELECT clause or the HAVING clause.

Ex:- AVG(column_name),COUNT(*),MAX(column_name),MIN(column_name),SUM(column_name), ROUND (column_name, decimal places you want.

Syntax:

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

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

SELECT SUM(replacement_cost) FROM film;

GROUP BY:

GROUP BY will allow us to aggregate data and apply function to better understand how data is distributed per category.

Syntax:

SELECT category_column, AGG(data_column) FROM table_name

GROUP BY category_column;

 

SELECT customer_id, SUM(amount) FROM payment

GROUP BY customer_id

ORDER BY SUM(amount) ASC;

 

SELECT customer_id, COUNT(amount) FROM payment

GROUP BY customer_id

ORDER BY COUNT(amount) DESC;

 

SELECT customer_id,staff_id,SUM(amount) FROM payment

GROUP BY staff_id,customer_id

ORDER BY customer_id,staff_id;

 

SELECT DATE(payment_date),SUM(amount) FROM payment

GROUP BY DATE(payment_date)

ORDER BY SUM(amount);

 

HAVING:

HAVING clause allows us to filter after an aggregation has already taken place.

HAVING allows us to use the aggregate result as a filter along with the GROUP BY.

 

Syntax:

SELECT category_column, AGG(data_column) FROM table_name

GROUP BY category_column;

HAVING AGG(data_column);

 

SELECT customer_id, SUM(amount) FROM payment

GROUP BY customer_id

HAVING SUM(amount) > 100

ORDER BY SUM(amount);

 

SELECT store_id, COUNT(customer_id) FROM customer

GROUP BY store_id

HAVING COUNT(customer_id) > 300;

 

SELECT customer_id,SUM(amount) FROM payment

WHERE staff_id = 2

GROUP BY customer_id

HAVING SUM(amount) > 100;

 

JOINS:

JOINS will allow us to combine information from multiple tables.

Creating alias with the AS clause.

Different kinds of joins:

1.     1. INNER JOINS

2.    2.  OUTER JOINS

3.     3. FULL JOINS

4.     4. UNIONS

 

AS statement:

AS clause allows us to create an 'alias(alternative name)' for a column or result.

AS operator gets executed at the very end of the query, meaning that we can not use the ALIAS inside a WHERE operator.

Syntax:

SELECT column_name1 AS new_name FROM table_name

GROUP BY category_column;

 

SELECT customer_id,SUM(amount) AS total_spent FROM payment

GROUP BY customer_id;

SELECT customer_id,SUM(amount) AS total_spent FROM payment

GROUP BY customer_id

HAVING SUM(amount) > 100;

INNER JOIN:

INNER JOIN will result with the set of records that match in both tables.Table order won't matter in a INNER JOIN, also if we just use JOIN without the INNER JOIN, PostgreSQL will treat it as an INNER JOIN.

Syntax:

SELECT * FROM table_A

INNER JOIN table_B

ON table_A.column_match = table_B.column_match

 

SELECT payment.payment_id,payment.customer_id,customer.first_name FROM payment

INNER JOIN customer

ON payment.customer_id = customer.customer_id;

 

FULL OUTER JOIN:

They will allow us to specify how to deal with the values only present in one of the tables being joined.

If the value don't match in both the tables then NULL value is inserted in that slot.

Syntax:

SELECT * FROM table_A

INNER JOIN table_B

ON table_A.column_match = table_B.column_match

 

SELECT * FROM payment

FULL OUTER JOIN customer

ON customer.customer_id = payment.payment_id;

SELECT * FROM payment

FULL OUTER JOIN customer

ON customer.customer_id = payment.customer_id

WHERE customer.customer_id IS null OR payment.payment_id IS null;

LEFT JOIN:

LEFT OUTER JOIN result in the set of records that are in the left table, if there is no match with the right table, the results are NULL.

Order of table matters. 

 

Syntax:

SELECT * FROM table_A                     (table_A is Left table)

LEFT JOIN table_B

ON table_A.column_match = table_B.column_match

 

SELECT film.film_id,film.title,inventory.inventory_id,inventory.store_id FROM film

LEFT JOIN inventory

ON inventory.film_id = film.film_id;

SELECT film.film_id,film.title,inventory.inventory_id,inventory.store_id FROM film

LEFT JOIN inventory

ON inventory.film_id = film.film_id

WHERE inventory.film_id IS null;

RIGHT JOIN:

RIGHT OUTER JOIN result in the set of records that are in the right table, if there is no match with the left table, the results are NULL.

Similar to the LEFT OUTER JOIN just the table are switched.

Syntax:

SELECT film.film_id,film.title,inventory.inventory_id,inventory.store_id FROM inventory

RIGHT JOIN film

ON inventory.film_id = film.film_id;

SELECT film.film_id,film.title,inventory.inventory_id,inventory.store_id FROM inventory

RIGHT JOIN film

ON inventory.film_id = film.film_id

WHERE inventory.film_id IS null;

UNION:

UNION operator is used to combine the result- set of two or more SELECT statements.

 

Syntax:

SELECT * FROM table_A                                                              

UNION

SELECT * FROM table_B                                                              

ADVANCE SQL COMMANDS:

Section Overview:

1. Timestamps and EXTRACT

2. Math Functions

3. String Functions

4. Sub-query

5. Self-join

TIMESTAMPS AND EXTRACT:

These wil be more useful when creating our own tables and databases, rather than when querying a database.

TIME - Contains only time

DATE - Contains only date

TIMESTAMP - Contains date and time

TIMESTAMPTZ - Contains date, time and time zone

Some functions are as follows:

1. TIMEZONE  

SHOW TIMEZONE --- Asia/Calcutta 

2. NOW

            SELECT NOW() --- 2020-09-15 14:10:36.458666+05:30

3. TIMEOFDAY

            SELECT TIMEOFDAY() --- Tue Sep 15 14:10:57.879363 2020 IST

4. CURRENT_TIME

      SELECT CURRENT_TIME --- 14:11:26.492059+05:30

5. CURRENT_DATE

            SELECT CURRENT_DATE --- 2020-09-15

 

1. EXTRACT( )

Allows you to extract or obtain a sub-component of a date value.(year, month, day, week, quarter)

Quarter means Jan,Feb,March.

Syntax:

EXTRACT (YEAR FROM date_column)

SELECT EXTRACT(YEAR FROM payment_date) FROM payment;

SELECT EXTRACT(MONTH FROM payment_date) FROM payment;

SELECT EXTRACT(QUARTER FROM payment_date) FROM payment;

2. AGE( )

Calculates and returns the current age given a timestamp

Syntax:

AGE (date_column)

SELECT AGE(payment_date) FROM payment;

3. TO_CHAR( )

General function to convert data types to text, useful for timestamp formatting.

Check out the link if you want to play with TO_CHAR()

https://www.postgresql.org/docs/current/functions-formatting.html

 

Syntax:

TO_CHAR (date_column,’mm-dd-yyyy’)

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

SELECT TO_CHAR(payment_date,'mon/dd/yyyy') FROM payment;

 

MATHEMATICAL FUNCTIONS AND OPERATOR:

Mathematical operators are provided for types without standard mathematical conventions (e.g., date/time types) we describe the actual behaviour in subsequent sections.

Check out the link if you want to play with Mathematical Function

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

Ex:-

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

 

STRING FUNCTIONS AND OPERATIONS:

PostgreSQL provides a variety of string and operators that allow us to edit, combine and alter text data columns.

Check out the link if you want to play with String Function

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

SELECT LENGTH(first_name) AS len_first,LENGTH(last_name) AS len_last

FROM customer;

 

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

"JARED ELY"

"MARY SMITH"

"PATRICIA JOHNSON"

SELECT LOWER(LEFT(first_name,1)) || LOWER(last_name) || '@gmail.com' AS full_name

FROM customer;

"jely@gmail.com"

"msmith@gmail.com"

"pjohnson@gmail.com"

"lwilliams@gmail.com"                                    

 

SUB QUERY:

Sub query allows you to construct complex queries, essential performing a query on the result of another query. The syntax is straight forward and involves two SELECT statements.

The sub query is performed first since it is inside the parenthesis, we can also use IN operator in conjunction with the sub query to check against multiple result returned.

EXISTS operator is used to test for existence of row in a subquery.

Ex:-

SELECT title,rental_rate FROM film

WHERE rental_rate > (SELECT AVG(rental_rate) FROM film);

"Chamber Italian"                                            4.99

"Grosse Wonderful"                                         4.99

"Airport Pollock"                                              4.99

 

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 rental.return_date BETWEEN '2005-05-29' AND '2005-05-30')

ORDER BY film_id ASC;

15                                                                        "Alien Center"

19                                                                        "Amadeus Holy"

45                                                                        "Attraction Newton"

 

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

"Karen"                                                             "Jackson"

"Victoria"                                                          "Gibson"

"Vanessa"                                                         "Sims"

SELF-JOIN:

Self-Join is a query in which a table is joined to itself. Self-Join are useful for comparing values in a column of rows within the same tables.

The self-join can be viewed as a join of two copies of the same table, the table is not actually copied, but SQL performs the command as though it were.

When using a self-join it is necessary to use an ALIAS for the table, otherwise the table names would be ambiguous.

Syntax:

SELECT table_A.column, table_B.column

FROM table AS table_A

JOIN table AS table_B

ON table_A.some_column = table_B.other_column

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;

"Chamber Italian"                                            "Resurrection Silverado" 117

"Chamber Italian"                                            "Magic Mallrats"  117

"Chamber Italian"                                            "Graffiti Love"     117

"Chamber Italian"                                            "Affair Prejudice" 117

"Grosse Wonderful"                                         "Hurricane Affair" 49

"Grosse Wonderful"                                         "Hook Chariots"   49

"Grosse Wonderful"                                         "Heavenly Gun"    49

"Grosse Wonderful"                                         "Doors President" 49

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------