Functions and Operators:

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

Logical Operators: AND OR NOT

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

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