How it works

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

1
Momchil Kolev
Random
C
picture loading error handler
109 thought(s)109

Complete SQL Bootcamp

Momchil Kolev
Get

Explore more quotes

What are Databases ?
Momchil Kolev
2
Why Use Databases ?
Momchil Kolev
1
spreadsheets vs databases
Momchil Kolev
1
From spreadsheets to databases
Momchil Kolev
1
Database Platform options
Momchil Kolev
1
What is SQL ?
Momchil Kolev
2
PgAdmin vs PostgreSQL
Momchil Kolev
1
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
1
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
1
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
1
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
1
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
1
Creating a new role: if you are currently the user postgres, do $ createuser --interactive else $ sudo -u postgres createuser --interactive
Momchil Kolev
1
Change a role's password in PostgreSQL $ psql \password <role>
Momchil Kolev
1
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
1
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
1
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
1
character varying(45) - string up to 45 characters (inclusive) In a query, strings are surrounded by single quotes
Momchil Kolev
1
SELECT DISTINCT col1, col2 FROM table_name Selects unique row values
Momchil Kolev
1
SELECT col1, col2 FROM table_name WHERE conditions
Momchil Kolev
1
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
1
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
1
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
1
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
1
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
1
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
1
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
1
Subquery - a query within a query SELECT * from table WHERE value in (SELECT customer_id FROM table)
Momchil Kolev
1
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
1
Aggregate functions Many Inputs -> One Output AVG - average MAX - maximum MIN - minimum SUM - sum COUNT - number of inputs
Momchil Kolev
1
ROUND ROUND(amount, 2) - rounds amount to 2 decimal points
Momchil Kolev
1
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
1
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
1
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
1
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
1
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
1
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
1
On most SQL engines, you can type JOIN instead of INNER JOIN and it will default to INNER
Momchil Kolev
1
SELECT language.name, film.title FROM film INNER JOIN language AS lang ON lang_id = film.language_id
Momchil Kolev
1
Practice INNER JOIN
Momchil Kolev
1
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
1
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
1
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
1
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
1
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
1
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
1
In SQL JOINS - LEFT signifies what you are selecting and RIGHT denotes what you are joining into
Momchil Kolev
1
FULL OUTER JOIN SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.name = table2.name
Momchil Kolev
1