Momchil Kolev

What are Databases ?

Why Use Databases ?

spreadsheets vs databases

From spreadsheets to databases

Database Platform options

What is SQL ?

PgAdmin vs PostgreSQL

PostgreSQL uses a client and a server, where the server is the process managing the database and accepting connections, whereas the client is what connects to the server and through it interacts with the database

Roles in postgres are like users and groups in Linux, but combined, since postgres doesn't distringuish between users and groups and instead prefers the term *role*.

Upon installation, Postgres is set up to use *ident* authentication, meaning that it associates postgres roles with a matching Linux account. If a role exists within postgres, a Linux username with the same name is able to sign in as that role.

The installation of postgresql creates a user account called postgres that is associated with the default postgres role. In order to use postgres, you can log into that account.

At first you can run postgres by either switching to the postgres user and running psql (1) or directly running psql as that user (2) 1. sudo -i -u postgres; psql 2. sudo -u postgres psql

Creating a new role: if you are currently the user postgres, do $ createuser --interactive else $ sudo -u postgres createuser --interactive

Creating a new database Another assumption that the pstgres authentication system makes by default is that for any role used to log in, that role will have a database with the same name which it can access create a database using createdb $ createdb kolev

Change a role's password in PostgreSQL $ psql \password <role>

When restoring a database, remember to toggle the restore option *Data* -> Yes, otherwise you will probably only get empty tables (also check pre-data and post-data if you get an error)

SELECT - used to query data from a table SELECT col1, col2 FROM table_name User a comma between each column in case you want to query data from multiple columns

SQL is case insensitive, though by convention SQL keywords are uppercase - usually not great practice to use an * after the SELECT keyword - higher traffic (b/w client & server) - more time to get it done

character varying(45) - string up to 45 characters (inclusive) In a query, strings are surrounded by single quotes

SELECT DISTINCT col1, col2 FROM table_name Selects unique row values

SELECT col1, col2 FROM table_name WHERE conditions

WHERE statement operators: = - equal > - greater than < - less than >= - greater than or equal to <= - less than or equal to <> or != - not equal AND - logical operator AND OR - logical operator OR

The COUNT function returns the number of input rows that match a specific condition or query SELECT COUNT(*) FROM table_name COUNT - does not consider null values in the column

LIMIT - allows you to limit the number of rows you get back after a query - useful when wanting to get all columns but not all rows SELECT * FROM table LIMIT 1 - goes at the end of a query

When you query data from a table, PostgreSQL returns the rows in the order that they were inserted into the table ORDER BY - allows you to sort the rows returned from SELECT in ascending or descending order based on criteria specified

You can also ORDER BY columns that are not selected (in PostgreSQL, not sure about MySQL) Hence it's good practice to always select the columns you're ordering by

BETWEEN - match a value against a range of values (inclusive) SELECT payment FROM table WHERE amount >= 1 AND amount <= 10 is the same as WHERE amount BETWEEN 1 AND 10

IN - used with the WHERE caluse to check if a value matches any value in a list (array) of values value IN (val1, val2, val3) can also use NOT IN IN faster than multiple ORs

Subquery - a query within a query SELECT * from table WHERE value in (SELECT customer_id FROM table)

Pattern matching using LIKE - CASE SENSITIVE % - any sequence of characters _ - any single character SELECT first_name FROM customer WHERE first_name LIKE 'Jo%' matches any first_name starting with 'Jo'

Aggregate functions Many Inputs -> One Output AVG - average MAX - maximum MIN - minimum SUM - sum COUNT - number of inputs

ROUND ROUND(amount, 2) - rounds amount to 2 decimal points

GROUP BY - divide the rows returned from SELECT into groups SELECT col1, aggregate_func(col2) FROM table GROUP BY col1 GROUP BY without an aggregate function acts like DISTINCT

GROUP BY + an aggregate function SELECT customer_id, SUM(amount) FROM table GROUP BY customer_id will group up all repeating rows into one and sum their amounts into one

HAVING - used with the GROUP BY clause to filter out group rows that do not satisfy a specified condition (like WHERE but for GROUP BY) - having, like where but for groups SELECT col1, SUM(col2) FROM table GROUP BY col1 HAVING condition

The HAVING clause sets the condition for group rows created by the GROUP BY clause after the GROUP BY clause applied while the WHERE clause sets the condition for individual rows before the GROUP BY clause applies

AS - allows us to rename columns or table selections with an alias SELECT payment as my_payment_column FROM table SELECT payment_id, SUM(amount) AS sum FROM payment GROUP BY payment_id

JOINS - allow you to relate data from one table to data in other tables 3 types of joins: - INNER JOIN - OUTER JOIN - self-join

On most SQL engines, you can type JOIN instead of INNER JOIN and it will default to INNER

SELECT language.name, film.title FROM film INNER JOIN language AS lang ON lang_id = film.language_id

Practice INNER JOIN

INNER JOIN - produces only the set of records that match in both Table A and Table B

FULL OUTER JOIN - produces the set of all records in tableA and tableB, with matching records from both sides where available. If there is no match, the missing side will contain null

LEFT OUTER JOIN - produces a complete set of records from tableA, with the matching records (where available) in tableB. If there is no match, the right side will contain null

LEFT OUTER JOIN with WHERE - produce the set of records only available in tableA, by excluding the records we don't want from the right side using a WHERE clause

FULL OUTER JOIN with WHERE - produce the set of records unique to tableA and tableB, by performing a full outer join and then excluding the records we don't want from both sides via a where clause

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

In SQL JOINS - LEFT signifies what you are selecting and RIGHT denotes what you are joining into

FULL OUTER JOIN SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.name = table2.name

LEFT JOIN = LEFT OUTER JOIN RIGHT JOIN = RIGHT OUTER JOIN

UNION - the UNION operator combines result sets of two or more SELECT statements into a single result set SELECT col1, col2 FROM table1 UNION SELECT col1, col2 FROM table2

UNION rules - both queries need t return the same number of columns - the corresponding columns in the queries must have compatible data types

UNION is used to combine data from similar tables that are not perfectly normalized UNION concatenates two tables

Timestamps SQL allows us to use the timestamp data type to retain time information

extract - allows us to extract parts of a date SELECT extract(dow from payment_date) AS dow FROM payment GROUP BY dow LIMIT 1

you can also use +-*/ operators on dates date '2001-10-30' + integer '1'

age(timestamp) - subtract from current date at midnight

mathematical functions galore just look them up if you need to use them SELECT customer_id + rental_id FROM payment / - integer division

extract( unit from date ) - extracts parts from a date EXTRACT (day FROM date)

add days to your timestamp date '2018-10-30' + integer '1' can also

timestamp data type - used to retain time information

String functions concatenation using || SELECT first_name || ' ' || last_name FROM customer - John Doe

Subquery - allows us to use multiple SELECT statements, where we basically have a query within a query SELECT * FROM film WHERE rental_rate > (SELECT AVG(rental_rate) FROM film)

Self-Join - used to combine rows with other rows in the same table To use self join, you must use a table alias to help SQL distinguish the left table from the right table of the same table no need to use the word JOIN, just have 2 tables as aliases in FROM

Self join is faster than doing a query + a subquery You can use INNER, LEFT, RIGHT, OUTER JOIN inside a self join

SELECT c1.first_name || ' ' || c1.last_name, c2.first_name || ' ' || c2.last_name FROM customer AS c1, customer AS c2 WHERE c1.first_name = c2.last_name

Interview question - google "manager employee self join"

When you want to query a table outside of the public schema, you need to select the schema SELECT * FROM cd.bookings

Data Types in Postgre - boolean - character - number - temporal (i.e. date and time related) - special types - array

Boolean data type - hold either true or false - in case the value is unknown, the NULL value is used You can use boolean or bool keyword when you declare a column that has a Boolean data type

Character type char - a single character char(n) - fixed length character strings (if you insert a too-short string, postgre will pad it with spaces, too long -> error) varchar(n) - store up to n characters with variable-length strings (no space padding if the stored string is shorter than col.len)

Number data type 2 types of numbers: - integers - floating-point numbers

Integer data types smallint - 2 byte signed integer - from -32768 to 32767 *signed - support both positive and negative numbers *unsigned - support only positive numbers int - 4 byte signed -214783648 to 214783647

Floating-point number data types float(n) - precision up to 8 bytes real or float8 - double precision (8-byte) numeric or numeric(p, s) - a real number with p digits with s number after the decimal point - numeric(p,) is the exact number

Temporal data types date - stores date data time - stores time date timestamp - stores date and time interval - stores the difference in timestamps timestamptz - store both timestamp and timezone data

Primary key - a column or a group of columns that is used to identify a row uniquely in a table You define primary keys through primary key constraints

So in essence a primary key is a column where each value is unique (uniquely identifies each row in the table) Often applied to the serial data type

Foreign key - a field or group of fields in a table that uniquely identifies a row in another table I.e. a foreign key is defined in a table that refers to the primary key of the other table

A foreign key constraint indicates that values in a column or a group of columns in the child table match with the values in a column or a group of columns of the parent table We say that a foreign key constraint maintains referential integrity between child and parent tables

Create table CREATE TABLE table_name (col_name TYPE col_constraint, table_constraint) INHERITS existing_table_name This will create a table with whatever columns and constraints + table constraint and will inherit all the columns from existing_table_name

Column constraints NOT NULL - the value of the record cannot be NULL UNIQUE - the value of the column must be unique across the whole column - however the column can have many NULL values because PostgreSQL treats each NULL as a unique

Col Constraints 2 PRIMARY KEY - a combination of NOT NULL and UNIQUE You can define one col as PRIMARY KEY by using a column-level constraint. In case the PRIMARY KEY uses multiple cols, you must use the table-level constraint

COL Constraints 3 CHECK - enables to check a condition when you insert or update data - e.g. that the values in a col must be positive REFERENCES - constrains the value of the column that exists in a column in another table

PostgreSQL Table Constraints UNIQUE (col_list) - force the value stored in the columns listed inside the parentheses to be unique PRIMARY KEY (col_list) - define the primary key that consists of multiple columns CHECK (condition) - check a condition when inserting or updating data

INSERT - used to insert data into a table INSERT INTO table(col1, col2) VALUES (val1, val2), (val3, val4)

Insert data that comes from another table INSERT INTO table SELECT col1, col2,... FROM table_name WHERE condition

If you don't specify a value for a column when inserting rows, it will (if it's not constrained by NOT NULL) take up NULL as a default value

Create a table with the same structure as an existing table using LINK CREATE TABLE table_copy (LIKE table) - copies only the schema, not the data within

UPDATE - used to change the values of columns in a table UPDATE table SET col1 = val1, col2 = val2 WHERE condition

Set a record's value to the value of a different column (will work as long as the data types match) UPDATE link SET description = name will set the values of description to the values of name

add RETURNING at the end of the query to get the changed/inserted rows

DELETE - delete rows from a table DELETE FROM table WHERE condition If you omit the WHERE clause, all the rows in the table will be deleted DELETE FROM table

ALTER - used to alter the structure of a table ALTER TABLE table_name action

Postgre action examples - add, remove, or rename column - set default value for column - add CHECK constraint to column - rename table

ALTER examples ALTER TABLE link ADD COLUMN active BOOLEAN ALTER TABLE link DROP COLUMN active ALTER TABLE link RENAME COLUMN title TO new_title

ALTER TABLE link RENAME TO url_table

DROP TABLE - remove an existing table from the database DROP TABLE [IF EXISTS] table_name use IF EXISTS to avoid errors if the table doesn't already exist in the db

CASCADE vs RESTRICT RESTRICT doesn't allow you to drop a table if anything depends on it CASCADE will drop the table and anything that depends on it

CHECK constraint - a constraint that allows you to specify if a value in a column must meet a specific requirement - uses a boolean expression to evaluate the values of a column - if the values of the column pass the check, PostgreSQL will insert or update those values

PostgreSQL automatically names constraints like so: table_column_constraint example users_age_check table + _ + column + _ + constraint