Advanced SQL - PostgreSQL

Functions and Operators:

Operators: Logical, Comparison, String, Date Time
Functions: Mathematical, String, Date Time

Logical Operators: AND OR NOT

a	b	a AND b	a OR b
TRUE	TRUE	TRUE	TRUE
TRUE	FALSE	FALSE	TRUE
TRUE	NULL	NULL	TRUE
FALSE	FALSE	FALSE	FALSE
FALSE	NULL	FALSE	NULL
NULL	NULL	NULL	NULL

a	NOT a
TRUE	FALSE
FALSE	TRUE
NULL	NULL

SELECT * from film
WHERE release_year = 2006
AND NOT rental_duration = 6;

SELECT * from film
WHERE release_year in (2006, 2007)
AND NOT in rental_duration (6,5);

Comparison Operators

Operator	Description
<	less than
>	greater than
<=	less than or equal to
>=	greater than or equal to
=	equal
<>	not equal

SELECT * from film
WHERE release_year `BETWEEN` 2002 and 2006;

SELECT * from film
WHERE length = 160
OR release_year `BETWEEN` 2002 and 2006;

Mathematical Operators

SELECT tile, length, round((length/60),2) length_in_hour_round
FROM film;

SELECT title, rental_rate, 
ceiling`(rental_rate) rental_rate_new
FROM film;

ceiling is used to convert decimal numbers to nearest higher integer.

Formulas and Functions
Area of a circle: PI * (radius)^2

SELECT PI() * (500)^2 `AS AreaOfCircle;

TO combine data from two column into a single column:

SELECT firstName, lastName,
firstName || ' ' || lastName
FROM Actor;

SELECT firstName, lastName,
CONCAT(firstName, ' ', lastName) AS Full_name
FROM Actor;

TO get initials of the actor:

SELECT firstName, lastName,
LEFT(firstName, 1) || LEFT(lastName, 1) AS Initials
FROM Actor;

Other function:
LENGTH - Length of a string
UPPER - Uppercase
REPLACE - 3 parameter (firstName, 'a', '@')

Date Time Operators
now()
current_date

Aggregate Functions
count() min() max() sum()

TYPE CONVERSION
When a data stored in one datatype is converted to another datatype, it is known as Type Conversion. There are 2 types: Explicit and Implicit

Implicit Conversion: When a data stored in one datatype is converted to another datatype automatically, it is known as Implicit Conversion

Explicit Conversion: When a data stored in one datatype is converted to another datatype using conversion functions, it is known as Implicit Conversion

	Explicit	Implicit
Readability	Clear and Easy	Ambiguous
Performance	Better	Negative Impact
Portability	High	Low

Explicit conversion is preferred over Implicit

TRANSACTION
Transaction bundles multiple steps into a single, all-or-nothing operation.
Significance of Transaction

• Atomic - all or nothing
• Guarantee of record
• Visibility of updates

BEGIN;
Update Valid_statement;
SAVEPOINT first_savepoint;
Update error_statement;
ROLLBACK TO first_savepont;
Update correct_statement;
COMMIT;