SQL

See all the tables in a database in sqlite

.tables

Comparison Operators

=, >, <. >=, <=, !=

HAVING clause

Allows for filtering after an aggregation has already taken place. Goes after GROUP BY statments. ex: SELECT company, SUM(sales) FROM finance_table WHERE company != 'Google' GROUP BY company HAVING SUM(sales) > 1000

GROUP BY

Allows to aggregate data and apply functions to better understand how data is distributed per category. Most common aggregate functions: AVG(), COUNT(), MAX(), MIN(), SUM() Aggregate function calls happen only in the SELECT clause or the HAVING clause Must appear right after a FROM or WHERE statement In the SELECT statement, columns must either have an aggregate function or be in the GROUP BY call. ex: SELECT category_col, AGG(data_col) FROM table_name GROUP BY category_col - It's important to choose category columns to call GROUP BY on. They can be numerical values but not continuous.

LIMIT command

Allows to limit the number of rows returned for a query. Goes at the end of a query request and is the last command to be executed. ex: SELECT * FROM payment ORDER BY payment_date DESC LIMIT 10;

FULL OUTER JOINS

Allows to specify how to deal with values only present in one of the tables being joined. This will essentially give you everything whether it's present in both or just one table. ex: SELECT * FROM tableA FULL OUTER JOIN tableB on tableA.col_match = tableB.col_match

Subquery

Allows you to construct complex queries, essentially performing a query on the results of another query. The subquery is usually calculated first. ex: SELECT student, grade FROM test_scores WHERE grade > (SELECT AVG(grade) FROM test_scores); You can also use the IN operator in conjunction with a subquery to check against multiple results returned: SELECT student, grade FROM test_scores WHERE student IN (SELECT student FROM honor_roll_table); Example above uses two different tables without using a JOIN statment.

PRIMARY KEY (entire table)

Allows you to define the primary key that consists of multiple columns.

EXTRACT

Allows you to extract or obtain a sub-component of a date value YEAR, MONTH, DAY, WEEK, QUARTER ex: SELECT EXTRACT(YEAR FROM payment_date) FROM payment;

Aggregating in SQL

grouping by one column, then counting or performing some other operation on another column.

LEFT()

helps you grab the first n characters in a string ex: SELECT LOWER(LEFT(first_name, 1)) || LOWER(last_name) || '@gmail.com' AS email FROM customer;

UPDATE join

setting a column's values based on the values from a column in another table. There's no actual join in the syntax ex: UPDATE tableA SET original_col = tableB.new_col FROM tableB WHERE tableA.id = tableB.id;

SELECT DISTINCT

simple way to return all unique values of a given field. it is not meant to be used with aggregate functions; instead, its typical purpose is to remove duplicates ex: SELECT DISTINCT column_name FROM table_name; Can also be used with parenthesis ex: SELECT DISTINCT(column_name) FROM table_name;

What do you need if a subquery after a WHERE statement returns multiple values

the IN operator ex: SELECT film_id, title FROM film WHERE film_id IN (SELECT inventory.film_id from rental INNER JOIN inventory ON inventory.inventory_id = rental.Inventory_id WHERE return_date BETWEEN '2005-05-29' AND '2005-05-30');

Constraints

the rules enforced on data columns on a table. The rules are used to prevent invalid data from being entered into the database. This ensures the accuracy and reliability of the data in the database. most common constraints used: NOT NULL, UNIQUE, PRIMARY key, FOREIGN key

JOIN

three types: inner, full outer, and left or right outer using only JOIN is the default type, inner join

UNION

used to combine the result-set of two or more SELECT statements. They should be logical and should match up in a way where you can stack the results right on top of each other. ex: SELECT * FROm sales2021_q1 UNION SELECT * FROM sales2021_q2 ORDR BY name;

CASE statement

used to execute SQL code when certain conditions are met. Similar to IF/ELSE statements used in other languages two main ways to use a CASE statement: General CASE, CASE expression BOTH methods can lead to the same results.

COALESCE

accepts an unlimited number of arguments. It returns the first argument that is not null. If all arguments are null, the COALESCE function will return null. This becomes useful when querying a table that contains null values and substituting it with another value. ex: SELECT item, (price - COALESCE(discount, 0)) AS final FROM table;

UPDATE

allows for the changing of values of the columns in a table. general syntax: UPDATE account SET last_login = CURRENT_TIMESTAMP WHERE last_login IS NULL; you can also set the values of one column based on another one: UPDATE account SET last_login = created_on;

DROP table

allows for the complete removal of a column in a table In postgreSQL this will also automatically remove all of its indices and constraints involving the column. It will not remove columns used in views, triggers, or stored procedures without the additional CASCADE clause

CHECK constraint

allows us to create more customized constraints that adhere to a certain condition. ex: making sure all inserted integer values fall below a certain threshold general syntax: CREATE TABLE example( ex_id SERIAL PRIMARY KEY, age SMALLINT CHECK (age > 21), parent_age SMALLINT CHECK (parent_age > age) );

INSERT

allows you to add rows to a table. Inserted row values must match up for the table, including constraints SERIAL columns do not need to be provided a value.

general CASE

better for multiple types of conditions. ex: SELECT a, CASE WHEN a = 1 THEN 'one' WHEN a = 2 THEN 'two' ELSE 'other' AS label END FROM test;

DELETE

can be used to remove rows from a table Similar to the UPDATE command, you can also add in a RETURNING call to return rows that were removed ex: DELTE FROM table_name WHERE row_id = 1

Assembling string/text data based on other columns

concatenation will be needed for this. ex: SELECT first_name || ' ' || last_name AS full_name FROM customer; Can also use functions like UPPER() and LOWER()

PRIMARY

constraint Uniquely identifies each row/record in a database table.

FOREIGN

constraint data based on columns in other tables.

UNIQUE

constraint. Ensures that all values in a column are different. ex: customer ID column.

NOT NULL

constraint. ensures that a column cannot have a NULL value. ex: requiring an email for each customer.

CHECK

constraint. ensures that all values in a column satisfy certain conditions ex: has to be a certain value.

Clear terminal in sqlite3

ctrl + l

UNIQUE (entire table)

Forces the values stored in the columns listed inside the parentheses to be unique.

ORDER BY

ex: SELECT name, city, state FROM customers ORDER BY state; You can order by a column even if that column doesn't appear: SELECT id, name FROM products ORDER BY price;

ROUND

ex: SELECT ROUND(AVG(replacement_cost), 2) FROM film;

SELECT WHERE statment

The WHERE statement allows us to specify conditions on columns for the rows to be returned. ex: SELECT column1, column2 FROM table WHERE conditions; Logical operators can also be used: AND, OR, NOT another example: SELECT name, choice FROM table_name WHERE name = 'David' AND choice = 'Red'; Can also use comparison operators: =, >, <, >=, <=, !=

Foreign Key

Defined in a table that references to the primary key of another table. A field or group of fields in a table that uniquely identifies a row in another table. A table can have multiple foreign keys depending on its relationships with other tables.

RIGHT JOINS

Just don't do them lol

Does the Import command create a table?

NO it assumes the table is already created. Currently there is no automated way within pgAdmin to create a table directly from a .csv file.

Functions and operations related to date/time specific data types

Not particularly useful for querying, but they will be helpful when creating tables. TIMEZONE NOW TIMEOFDAY CURRENT_TIME CURRENT_DATE

how to see all the column names of a table

PRAGMA table_info(table_name)

String functions and operators

PostgreSQL provides a variety of string functions and operators that allows for editing, combining, and altering text data columns. (check the link in the word document)

Examples of time based datetypes

TIME - contains only time DATE - contains only date TIMESTAMP - contains date and time TIMESTAMPZ = contains, date, time, and time zone useful in creating tables/databases

SQL: IN

The IN keyword will return True if the value matches any value in the list options. ex: SELECT id, custid, prodid, date FROM purchases WHERE custid IN (1001, 1003, 1005);

Mathematical functions

Regular mathematical operators and more advanced mathematical functions can be used in queries. (check the link in the word document) ex: SELECT ROUND(rental_rate / replacement_cost, 2) * 100 AS percent_cost FROM film;

SELECT CURRENT_TIME()

Returns the current time

COUNT function

Returns the number of input rows that match a specific condition of a query. You can apply COUNT on a specific column or just pass COUNT(*), which should generally return the same result. But it's often best to just pass in a column name just in case you need to look back and determine the question you were originally trying to answer. ex: SELECT COUNT(column_name) FROM table_name

SELECT TIMEOFDAY()

Returns timestamp information in string form

Schemas

Roadmaps that tell you how your data fit together -- how it all relates

Import and Export

The import/export functionality of pgAdmin allows you to import data from a .csv file to an already existing table. not every outside data file will work: variations in formatting, macros, data types, etc may prevent the import command from reading the file, at which point, you must edit the file to be compatible with SQL.

LIKE and ILIKE

This is about pattern matching with string data ex: all emails ending in '@gmail.com', All names that begin with an 'A' Makes use of special Wildcard characters: %, _ LIKE = case sensitive ILIKE = case insensitive ex: SELECT COUNT(first_name) FROM customer WHERE first_name ILIKE 'J%' AND last_name ILIKE 'S%' The same can be used with the NOT operator to get back everything that doesn't apply to the conditions specified.

REFERENCES (entire table)

To constrain the value stored in the column that must exist in a column in another table.

SQL: Pattern Matching

To search for matching patterns in strings, you will generally use LIKE, with _ and % serving as wildcards. _ refers to a single character, while % refers to any number of characters (including none). ex: SELECT name, state FROM customers WHERE name LIKE 'P%';

Example of returning the effected rows from an updated

UPDATE account SET last_login = created_on RETURNING account_id, last_login You can also just return all the columns if you want.

AS clause

Used to create an alias for a column or result. The AS operator gets executed at the very end of a query, meaning that you cannot use an alias inside of a WHERE operator ex: SELECT customer_id, SUM(amount) AS total_spent FROM payment GROUP BY customer_id;

Self-join

a self-join is a query in which a table is joined on itself. Can be viewed as a join of two copies of the same table. The table isn't actually copied, but SQL performs the command as though it were. Useful for comparing values in a column of rows within the same table. There is no special keyword for self-join, it's simply standard JOIN syntax with the same table in both parts. It is necessary to use an alias for the table, otherwise the table names would be ambiguous ex: SELECT emp.name, report.name AS rep FROM employees AS emp JOIN employees AS report ON emp.emp_id = report.report_id;

Where in pgAdmin can you determine which columns are foreign keys?

constraints

SELECT CURRENT_DATE()

returns the current date

NULLIF function

takes in 2 inputs and returns NULL if both are equal, otherwise it returns the first argument passed. ex: SELECT ( SUM(CASE WHEN department = 'A' then THEN 1 ELSE 0 END) / NULLIF(SUM(CASE WHEN department = 'B' THEN 1 ELSE 0 END), 0) ) AS department_ratio FROM depts; The NULLIF in this example is basically saying that if the SUM of department B instance = 0, return null.

ALTER

the ALTER clause allows for changes to an existing table structure, such as: adding, dropping, or renaming columns changing a column's data type set DEFAULT values for a column Add CHECK constraints Rename table

EXISTS

used to test for the existence of rows in a subquery. Typically, a subquery is passed in the EXISTS() function to check if any rows are returned with the subquery. Will return a True or False ex: SELECT column_name FROM table_name WHERE EXISTS(SELECT column_name FROM table_name WHERE condition);

example of altering a VIEW

CREATE OR REPLACE VIEW customer_info AS SELECT first_name, last/_name, address, district FROM customer INNER JOIN address ON customer.address_id = address.address_id;

CREATE TABLE general syntax

CREATE TABLE table_name ( column_name TYPE column_constraint, column_name TYPE column_constraint, table_constraint, table_constraint ) INHERITS existing_table_name; usually pick the first column to be primary key and usually a SERIAL datatype. use commas to separate columns Then add any table level constraint you want. If there's a relationship to another table, use INHERITS

Example syntax of very basic table creation

CREATE TASBLE players( player_id SERIAL PRIMARY KEY, age SMALLINT NOT NULL );

VIEW example

CREATE VIEW customer_info AS SELECT first_name, last_name, address FROM customer INNER JOIN address ON customer.address_id = address.address_id; You you can just run a query on that view, and It will return everything you need: SELECT * FROM customer_info;

AGE()

Calculates and returns the current age given a timestamp depending on when it's being queried. ex: SELECT AGE(payment_date) FROM payment;

BETWEEN command

Can be used to match a value against a range of values. value BETWEEN low AND high Can combine WHERE and NOT logical operator if you want. Can also be used with dates, but need to format in ISO 8601 format: ex: date BETWEEN '2007-01-01' AND '2007-02-01'

LEFT OUTER JOIN

Can use LEFT JOIN or LEFT OUTER JOIN. You have to specify what the left table is going to be so order matters. ex: SELECT * FROM tableA LEFT JOIN tableB ON tableA.col_match = tableB.col_match In the above example, you're not returning anything exclusive to table B. You can also specify that you want values unique to table A by using a WHERE clause.

ORDER BY

Can use ORDER BY to sort rows based on a column value in either ascending or descending order. Goes at the end of a query (this is one of the last things that SQL performs). Can use ORDER BY on multiple columns ex: SELECT * FROM customer ORDER BY first_name, last_name; You can also sort by columns that you don't actually request in the SELECT statement, but it's usually best practice to also select the column that you're ordering by. ex: SELECT first_name, last_name FROM customer ORDER BY store_id DESC, first_name ASC;

IN operator

Creates a condition that checks to see if a value is included in a list of multiple options. Good for checking multiple possible value options. ex: SELECT color FROM table WHERE color NOT IN ('red', 'blue'); ex: SELECT * FROM payment WHERE amount IN(0.99, 1.98, 1.99)

How to delete all rows from a table

DELETE FROM table

example of deleting a VIEW

DROP VIEW IF EXISTS customer_info;

Wildcard characters

% - matches on any sequence of characters _ - matches any single character, you can use multiple underscores

ALTER to add columns

ALTER TABLE table_name ADD COLUMN new_col TYPE

What is the order of ORDER BY?

Ascending You can append the DESC keyword if you want it descending: SELECT name, price FROM products ORDER BY price DESC;

CHECK (entire table)

To check a condition when inserting or updating data.

Pipe operator

|| used to concatenate strings.

Primary Key

A column or a group of columns used to identify a row uniquely in a table Typically a number or auto-generated character sequence Primary keys are super important because they allow us to easily discern what columns should be used for joining tables together.

VIEWS

A database object that is a stored query. often there are specific combinations of tables and conditions that you find yourself using often for a project. Instead of having to perform the same query over and over again as a starting point, you can create a VIEW to quickly see this query with a simple call. A VIEW can be accessed as a virtual table in PostgreSQL A VIEW does not store data physically, just the query.

DROP column and all dependencies

ALTER TABBLE table_name DROP COLUMN col_name CASCADE You're essentially cascading the removal to all dependencies.

ALTER to change constraints

ALTER TABLE table_name ALTER COLUMN col_name SET DEFAULT value

Check for existence to avoid error when dropping general syntax

ALTER TABLE table_name DROP COLUMN IF EXISTS col_name

ALTER to delete columns

ALTER TABLE table_name DROP COLUMN col_name

DROP multiple columns

ALTER TABLE table_name DROP COLUMN col_one, DROP COLUMN col_two

General ALTER syntax

ALTER TABLE table_name action

other altering VIEW example

ALTER VIEW customer_info RENAME TO c_info;

Logical Operators

AND, OR, NOT

CASE expression

Best for when you're just using equality. First evaluates an expression then compares the result with each value in the WHEN clauses sequentially. ex: SELECT a, CASE a WHEN 1 THEN 'one' WHEN 2 THEN 'two' END FROM test;

EXCLUSION constraint

Ensures that if any two rows are compared on the specific column or expression using the specified operator, not all of these comparisons will return TRUE.

TO_CHAR()

General function to convert data types to text. Useful for timestamp formatting. ex: SELECT TO_CHAR(payment_date, 'mm-dd-yyyy') FROM payment; ex: SELECT TO_CHAR(payment_date, 'MONTH-YYYY') FROM payment; There are various patterns that can be used - check the documentation in the link in the word document.

General syntax for INSERT

INSERT INTO table_name ( column1, column2, ... ) VALUES (value1, value2, ...), (value1, value2, ...), ...; another example: INSERT INTO account ( username, password, email, created_on ) VALUES ('Daron', 'password', '[email protected]', CURRENT_TIMESTEMP);

How to find out which table a foreign key references

If you select the foreign key in pgAdmin and navigate to dependencies on the menu bar on the top of the page, you can see what table it references.

SERIAL datatype

In postgreSQL, a sequence is a special kind of database object that generates a sequence of integers. A sequence is often used as the primary key column in a table. SERIAL creates a sequence object and sets the next value generated by the sequence as the default value for the column. This is perfect for a primary key b/c it logs unique integer entries for you automatically upon insertion. If a row is later removed, the column with the SERIAL data type will not adjust, marking the fact that the row was removed from the sequence.

Example of finding all pairs of films that have the same length using a self-join

SELECT f1.title, f2.title, f1.length FROM film AS f1 INNER JOIN film as f2 ON f1.film_id != f2.film_Id AND f1.length = f2.length;

SQL: select rows from the products table where the prices is greater than $10.00

SELECT * FROM products WHERE price > 10.00;

SQL: column >= x AND column <= y

SELECT * FROM table WHERE column BETWEEN x AND y;

What if you want to count the number of unique values in a particular column?

SELECT COUNT(DISTINCT column_name) FROM table_name;

Selecting columns from a table using SQL

SELECT column name, column name FROM table name;

SHOW command

SHOW ALL - will provide a bunch of runtime information SHOW TIMEZONE - Will show which timezone you're in.

SQL: aggregate functions

SUM, COUNT, AVG, MAX, MIN SUM and AVG can be used with number types MAX and MIN can be used for numbers, date/times, and strings COUNT is available to any data type (returns the number of non-null values in that column)

Main ways to perform a CAST operations

Syntax for CAST function: SELECT CAST('5' AS INTEGER) PostgreSQL CAST operator: SELECT '5'::INTEGER

CAST

The cast operator lets you convert from one data type into another. Not every instance of a data type can be CAST to another data type. It must be reasonable to convert the data. ex: '5' to an integer will work, 'five' to an integer will not work

assigning foreign/primary keys

When creating tables and defining columns, you can use constraints to define columns as being a primary key or attaching a foreign key relationship to another table.

When you create a table can you only run it once?

Yes. You'll get an error if you try to run it more than once.

INNER JOIN

Will return a set or records that match in both tables. ex: SELECT title, actor.first_name, actor.last_name FROM film JOIN film_actor ON film.film_id = film_actor.film_id WHERE actor.first_name = 'Nick' AND actor.last_name = 'Wahlberg'; With INNER JOIN, the order of tables you're calling FROM and INNER JOIN on don't actually matter because you'll be returning the data that only exists in both tables anyway.

SELECT NOW()

Will return the timestamp information for right now.

Does PostgreSQL support full regex capabilities?

Yes

Does each column need to have an explicit data type?

Yes

Does every row in a table have to be unique?

Yes

Is it common practice to have a field explicitly called 'id' in every SQL table?

Yes

Can you apply constraints to an entire table?

Yes CHECK, REFERENCES, UNIQUE, PRIMARY KEY

Can you delete rows based on their presence in other tables?

Yes ex: DELTE FROM tableA USING tableB WHER tableA.id = tableB.id where there's a match between the id columns in the two tables, the rows in tableA will be deleted.