3 basic questions
what kind of thing are we storing?
what properties does this thing have?
what type of data does each of those properties contain?
calculated fields
select name, population/area as density from cities;
math operators
+ addd
- substract
* multiply
/ divide
^ pow
|/ sqrt
@ absolute
% remainder
string operators and functions
|| join two strings
concat() join two strings
lower() lower case
uper() upper case
lengt() length of string
query structure
joins produces values by merging together rows from different related tables;
use a join most times that you are asked to find data that involves multiple resources;
aggregation look at rows and calculate a single value (ex: most, average, least)
Join (inner:default)
left join take all the rows from the first table, and fill the non filling values from the second table with null values
right outer join
take all the values that match the second table, even if they dont match to the first table.
full join
take both tables and fill null on both sides
Aggregates min, max, sum, count, avg
Grouping
- when grouping rows, you cant select non group fields, but you can perform aggregated functions
select count(*) from table (* will include null values)
having is mandatory to filter within groups
union
return non duplicated rows of multiple queries returns 7 out of 8
(select * FROM products order BY price desc LIMIT 4)
union
(select * FROM products order BY price/weight desc LIMIT 4);
union all return all rows of multiple queries
intersect return values that are common on both queries returns 1 out of 8
(select * FROM products order BY price desc LIMIT 4)
INTERSECT
(select * FROM products order BY price/weight desc LIMIT 4);
except
returns values that are present in first query but not on second. (removes duplicates on the first table)
except all remove duplicates on both tables
sub queries
you can use subqueries to obtain data and set it as input parameters for your main query
select name, department, price
FROM products WHERE price > (
select max(price) FROM products WHERE department = 'Toys'
);