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 — REFERENCES - to constrain the value stored in the column that must exist in a column in another table

Momchil Kolev

109

Complete SQL Bootcamp

Momchil Kolev

Get

Explore more quotes

What are Databases ?

Momchil Kolev

Why Use Databases ?

Momchil Kolev

spreadsheets vs databases

Momchil Kolev

From spreadsheets to databases

Momchil Kolev

Database Platform options

Momchil Kolev

What is SQL ?

Momchil Kolev

PgAdmin vs PostgreSQL

Momchil Kolev

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

Momchil Kolev

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*.

Momchil Kolev

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.

Momchil Kolev

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.

Momchil Kolev

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

Momchil Kolev

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

Momchil Kolev

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

Momchil Kolev

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

Momchil Kolev

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)

Momchil Kolev

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

Momchil Kolev

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

Momchil Kolev

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

Momchil Kolev

SELECT DISTINCT col1, col2 FROM table_name Selects unique row values

Momchil Kolev

SELECT col1, col2 FROM table_name WHERE conditions

Momchil Kolev

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

Momchil Kolev

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

Momchil Kolev

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

Momchil Kolev

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

Momchil Kolev

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

Momchil Kolev

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

Momchil Kolev

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

Momchil Kolev

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

Momchil Kolev

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'

Momchil Kolev

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

Momchil Kolev

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

Momchil Kolev

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

Momchil Kolev

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

Momchil Kolev

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

Momchil Kolev

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

Momchil Kolev

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

Momchil Kolev

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

Momchil Kolev

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

Momchil Kolev

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

Momchil Kolev

Practice INNER JOIN

Momchil Kolev

Device:f63d0868 d7c1 4db8 bb63 39892d705ef7119165 frjrwr.y9iu9xusor

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

Momchil Kolev

Device:f63d0868 d7c1 4db8 bb63 39892d705ef7119165 ho39jb.ywo6iggb9

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

Momchil Kolev

Device:f63d0868 d7c1 4db8 bb63 39892d705ef7124805 1l9o1i9.s5ugd86w29

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

Momchil Kolev

Device:f63d0868 d7c1 4db8 bb63 39892d705ef7127908 14vuj7f.xnbbeuq5mi

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

Momchil Kolev

Device:f63d0868 d7c1 4db8 bb63 39892d705ef7127908 iyfflr.pczvcp7gb9

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

Momchil Kolev

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

Momchil Kolev

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

Momchil Kolev