amadr-95/sql-advanced

ADVANCED DATABASES

JOINS

- (INNER) JOIN

Select records that have matching values in both tables

- (OUTER) LEFT JOIN

Select records from the left table with matching right table records

- (OUTER) RIGHT JOIN

Select records from the right table with matching left table records

- FULL JOIN

Select all records that match either left or right table records

TRANSACTIONS (A.C.I.D. PRINCIPLES)

1. ATOMICITY:
   - Atomicity ensures that a transaction is performed completely or not performed at all.
   - If any part of the transaction fails, the entire transaction is rolled back to its previous state.
   - It guarantees that data remains consistent even in cases of failure.

2. CONSISTENCY:
   - Consistency ensures that only changes to data that respect all system integrity constraints are made.
   - Before and after a transaction, data must be in a valid state.

3. ISOLATION:
   - Isolation principle ensures that the effects of a transaction are not visible to other transactions until it is completely finished.
   - Transactions are executed in isolation from one another, preventing operations in one transaction from interfering with concurrent transactions.

4. DURABILITY:
   - Durability guarantees that once a transaction is successfully completed and committed, the changes will persist even in the event of a system failure.
   - Data modified by the transaction is permanently saved and will not be lost, even in system failure situations.

BEGIN, COMMIT, ROLLBACK

• Begin: starts a transaction
• Commit: commits a transaction
• Rollback: reverts the data to its previous state if anything goes wrong

INDEXES

Using indexes may increases the performance when retriving many rows.

• View indexes

SELECT tablename, indexname, indexdef FROM pg_indexes WHERE schemaname = 'public';

• Create indexes

CREATE INDEX table_colum_name_idx ON table(column_name);

• Delete indexes

DROP INDEX index_name;

Multi-column indexes

Combining two or more columns.

CREATE INDEX table_column1_column2_idx ON table(column1, column2);

... WHERE column1 = '' AND column2 = '' ✅
... WHERE column1 = '' ✅
... WHERE column2 = '' ❌ (index does not have any effect)

Unique indexes

CREATE UNIQUE INDEX table_column_name_unique_idx
ON table(column_name);

Partial indexes

CREATE UNIQUE INDEX table_column_name_partial_idx
ON table(column_name)
WHERE column_name = condition (boolean for example)

FUNCTIONS

• Create functions

CREATE OR REPLACE FUNCTION function_name(param1 TYPE, param2 TYPE...)
    RETURNS TYPE
    LANGUAGE plpgsql
AS
$$
DECLARE
--variable declaration
    variable_name TYPE;
BEGIN
--logic
    RETURN variable_name;
END;
$$;

• Use functions

SELECT function_name(param1, param2, ...);

• View functions in terminal

\df

• Delete functions

DROP FUNCTION function_name;

ROLES

• View roles in terminal

\du

• Create role (rol = user)

CREATE ROLE/USER name; --basic

CREATE ROLE/USER name (WITH) LOGIN PASSWORD 'password';

• Modify role

ALTER ROLE rol_name [options]

Options:

- SUPERUSER | NOSUPERUSER
- CREATEDB | NOCREATEDB
- CREATEROLE | NOCREATEROLE
- INHERIT | NOINHERIT
- LOGIN | NOLOGIN
- REPLICATION | NOREPLICATION
- BYPASSRLS | NOBYPASSRLS
- CONNECTION LIMIT limit
- PASSWORD 'password' | PASSWORD NULL
- VALID UNTIL 'timestamp'

• Delete role

DROP ROLE name;

Privileges

Grant

GRANT privilege_list | ALL 
ON  table_name | ALL TABLES IN SCHEMA schema_name
TO  role_name;

Privileges:

- SELECT
- INSERT
- UPDATE
- DELETE
- TRUNCATE
- ...

Revoke

REVOKE privilege_list | ALL
ON table_name | ALL TABLES IN SCHEMA schema_name
FROM role_name;

SCHEMAS

• View schemas

\dn

SELECT current_schema(); --public by default

• Create schema

CREATE SCHEMA schema_name;

• Create tables in custom schemas

CREATE TABLE schema_name.table_name;

Search path

SHOW search_path;

• Change the default search path

SET search_path TO new_schema, default_schema (public);

BACKUP AND RESTORE DATABASES

Backup

pg_dump --help //see all options

• Dump a database

pg_dump -U username -d db_name -F format_file -f path/file_name.sql 

• Dump all databases

pg_dumpall -U username -f path/file_name.sql 

Restore

pg_restore --help //see all options

• Using psql (.sql files)

psql -U username -d db_name -f path/backup_file_name.sql

• Using pg_restore (.tar files)

pg_restore -U username -d db_name path/backup_file_name.tar