From spreadsheets to databases — Spreadsheet | Database Tab | Table Column | Column Row | Row

What are Databases ?

Why Use Databases ?

spreadsheets vs 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