Just giving credit where credit is due: these are my notes from A udemy course I am taking

For the time/date/timestamp/etc you can remove these fields if you want, but you can’t add it later on.

Extract()

Allows you to pull a part of the date info: YEAR, MONTH, DAY, WEEK, QUARTER

EXTRACT(YEAR from date_column)

SELECT count(*) FROM payment WHERE EXTRACT(dow FROM payment_date) = 1;

AGE

Calculates the current age of the timestamp

AGE(date_column)

This would return 15 years 6 months 9 days 11:11:11.11

TO_CHAR

Converts date types to text

SELECT DISTINCT( TO_CHAR (payment_date,’MONTH’)) from payment

Below Are time/date field types

TIME

DATE

TIMESTAMP: Time and Date

TIMESTAMPZ: Time, Date, Timezone

TIMEZONE

NOW

TIMEOFDAY

CURRENT_TIME

CURRENT_DATE

MATH STUFF!

There are tons of operators for this: Here is the documentation

You can do math based on the columns. For example this will divide one table by the other, then round to 2 decimal places. We want this to be a percent so multiply by 100, then assign this column as the percent cost.

SELECT ROUND(rental_rate/replacement_cost, 2)*100 as percent_cost from table_1

String Work

Tons of stuff of these: Here is the documentation

Find the length of a word: SELECT LENGTH(column_name) FROM table

Concatenate 2 columns with a space between them: SELECT first_name || ‘ ‘ || last_name AS customer_full_name FROM customer

SELECT LEFT(first_name, 1) || last_name AS first_inital_last_name

SELECT LOWER(LEFT(first_name,1) || last_name) AS first_inital_last_name FROM customer;

SubQuery

Run a query, then use that query as a part of another query

So to find the sales that are above the average would be

SELECT salesman, dollars FROM sales WHERE dollars > (SELECT AVG(dollars) FROM sales)