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)