Apuntes SQL

General functions

SELECT

Use the order SELECT to start to specify diferents columns. If you want to specify different columns, use ; and then the columns you want to select. You can also use * to select all columns.

Example select:

          `SELECT column_1, column_2,...,column_n FROM table_name;`

If you want to select all data frame (tabe):

          `SELECT * FROM table_name;`

SELECT DISTINCT

SELECT DISTINCT helps to group duplicates form a column. Example:

          `SELECT DISTINCT column_1, column_2
            FROM table_name;`

SELECT WHERE

SELECT [..] WHERE example:

          `SELECT column_1,column_2,...,column_n
            FROM table_name,
            WHERE conditions;`

Operators in SQL

Signo en la consola	Significado
=	Equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than equal
<>or!=	Not equal
AND	Logical operator AND
OR	Logical operator OR

Ejemplo para uso de operadores con SQL:

          `SELECT column_1,column_2,...,column_n
            FROM data_frame
            WHERE column_1 = "column_1_valueX" 
              AND column_2 = "column_2_valueY;"`

• AND -> condicion para diferentes columnas
• OR -> condicion para la misma columna

COUNT

SELECT COUNT() return number of input rows that match a specific query:

          `SELECT COUNT (column_1,column_2,...,column_n)
            FROM data_frame;`

You can also use SELECT COUNT() with DISTINCT. Example:

          `SELECT COUNT(DISTINCT column_1,...,column_n)
            FROM data_frame;`

LIMIT

LIMIT limits the number of rows you get back after a query. Example:

          `SELECT *
            FROM table
            LIMIT number;`

The LIMIT function will always go to the end.

ORDER BY

ORDER BY helps to order the data in a specific way. Example:

          `SELECT column_1,column_2,...,column_n
            FROM table_name
            ORDER BY column_1 ASC/DESC;`

BETWEEN / NOT BETWEEN

BETWEEN and NOT BETWEEN is a function used with WHERE, helps to match the value againt a range of value. Example:

          `SELECT column_1,column_2,...,column_n
            FROM  table_name
            WHERE column_1 NOT BETWEEN value_1 AND value_2;`

IN / NOT IN

**ÌN** and **NOT IN** are used withWHERE`, and we use it if a value matches any value in a list of values. Example:

          `SELECT column_1,column_2,...,column_n
            FROM table_name
            WHERE column_1 IN (value_1,value_2,...);`

The previous case will be the same than:

          `SELECT column_1,column_2
            FROM table_name
            WHERE column_1 = value_1 
              OR column_1 = value_2
            ORDER BY column_3 DESC;' 

LIKE / NOT LIKE

LIKE and NOT LIKE use pattern matching to find what are you looking for.

          `SELECT column_1,column_2
            FROM table_name
            WHERE column_1 LIKE 'column_value%';`

We have 3 types of operater to work with:

• _% matching any sequence of characters begining with the values previous to %.

• %_ matching any sequence of characters ending with the values after %.

• %_% matching any sequence of characters between %.

ILIKE

ILIKE is more insenisble, so you can use to find also uppercase. Example:

          `SELECT column_1,column_2
            FROM table_name
            WHERE column_1 ILIKE 'column_VaLuE%';`

Aggregate Functions

AVG functions heps to select specific amounts of date and combine it to get a single value. Example:

          `SELEC AVG(column_1)
            FROM table_name;`

If you want to add 5 decimals to the column values:

          `SELECT (AVG(column_1), 5)
            FROM table_name;`

Or if you want to round to 2 decimas the column values:

          `SELECT ROUND (AVG(coumn_1), 2)
            FROM table_name;`

How can I select the MIN of one column? Example:

          `SELECT MIN(column_1)
            FROM table_name;`

Use off MAX function to select the maximum value:

          `SELECT MAX(column_1)
            FROM table_name;`

Sum all the values of one column, using the function SUM:

          `SELECT SUM(column_1)
            FROM table_name;`

GROUP BY

The function ’GROUP BY` dividesthe raws returned from the SELECT statement into groups. You can apply different stratements (SUM,MAX, …):

          `SELECT column_1, aggregate_function(column_2)
            FROM table_name
            GROUP BY column_1;`

HAVING

The function HAVING is used with conjunction with GROUP BY to filter group rows that do no satisfy a specified condition. Example:

          `SELECT column1, aggregate_function(column_2)
            FROM table_name
            GROUP BY column_1
            HAVING condition;`

The JOINS

We use the AS a statment, it allows us to rename columns or table selections with an alias. Example:

          `SELECT column_1 AS column_1_differentName
            FROM table_name;`

There are different kinds of joins, and they help us to relate the data from one table to another one. There are three types:

• INNER JOIN

• OUTER JOIN

• SELF JOIN

Examples of how we use this function. First we specify the columns in both tables:

          `SELECT table_1.column_1, table_1.column_2, table_2.column_1, table_2.column_2
            FROM table_1
            INNER JOIN table_2 ON table_1.column_1 = table_2.column_4;`

In the INNER JOIN function you specify the table which the two tables share the key variable. When the two tables have teh samecolumn name, we just had to put table_name.column_name.

General joins view

FULL OUTER JOIN

The function FULL OUTER JOIN produces the set of ALL records in table_A and table_B, with matching records from both sides where available. If there is no match the missing side will contain NULL. Example:

          `SELECT * FROM table_A
            FULL OUTER JOIN table_B ON table_A.name = table_B.name
            WHERE table_A.id IS null OR table_B.id IS null;`

Full outer join graphic representation

LEFT OUTER JOIN

The function LEFT OUTER JOIN produces a complete set of records from table_A, with the matching records in table_B, then exclude the records we don’t want from the right side via a WHERE clause. Example:

          `SELECT table_A.column_1,table_A.column_2,table_B.column_id
            FROM table_A
            LEFT OUTER JOIN table_B ON table_B.column_id = table_A.column_id
            WHERE table_B.column_id IS NULL;`

With the parameter NULL you select all the values of the table_A that NOT appear into the table_B.

Left outer join graphic representation

UNION / UNION ALL

The functions UNION and UNION ALL combines resut sets of two or more SELECTstatementes into a single result set.

          `SELECT column_1, column_2
            FROM table_name_1
            UNION
            SELECT column_1, column_2
            FROM table_name_2;`

There are two rules to work with these functions:

1. Both queries must return the same number of columns

2. The corresponding columns in the queries must have compatible data types

UNION also helps you to remove all the duplicates. If you use UNION ALL, you will join all the data.

TIMESTAMPS

It us used to return time information.

EXTRACT FUNCTIONS

Helps to extract parts from a date:

Function	Meaning
day	day of the month
dow	day of the week
doy	day of the year
epoch	number of seconds from the “epoch”
hour	hour
microseconds	seconds multiplied by 1.000.000
millenium	millenium value
milliseconds	seconds multiplied by 1.000
minute	minute
month	number of month
quarter	quarter 1/4
second	seconds
week	number of the week
year	year as 4-digits

Example:

        `SELECT SUM (column_1), extract(month FROM column_2)
          FROM table_A
          GROUP BY month
          ORDER BY column_1 ASC;`

MATHEMATICAL FUNCTIONS

Operator	Meaning
+	addition
-	subtraction
*	multiplication
/	division
%	modulo(reminder)
^	exponent
/	square root
	/
!	factorial
!!	facctorial
@	absolute value
&	bitwise AND
	bitwise OR
#	bitwise NOT

There are more operators, you can check online on offical PgAdmin webste. Exampe:

        `SELECCT column_1 + column_2 AS column_NewAddition
          FROM table_A;`

STRING FUNCTIONS

SUBQUERY

This function SUBQUERY allows us to use a multiple SELECTstatements, where we basically have a query within a query. Example:

        `SELECT column_1,column_2,column_3
          FROM table_A
          WHERE column_3 >(SELECT AVG(column_3)
                            FROM table_A)

SELF JOIN

The function SELLF JOIN combine rows with other rows in the sambe table.