sql basics# 12 Feb 2017
from a table.
select all fields:
select * from customers
select specific fields:
select name, species from pets
where clause is used for filtering data.
get all gerbils:
select * from pets where species = 'gerbil'
get all gerbils that are older than 5:
select * from pets where species = 'gerbil' and age > 5
get all pets that are either a gerbil or a dog:
select * from pets where species = 'gerbil' or species = 'dog'
sum(), count(), and group by
aggregate data using an aggregate function like
count() in combination with the
group by clause.
count number of each species:
select species, count(*) from pets group by species
get total quantity sold of each
item_id from all transactions:
select item_id, sum(quantity) from transaction_items group by item_id
sort your results.
sort customer names:
select first, last from customers order by last
sort items by price:
select name, price from items order by price desc
limit the amount of results you return.
select * from customers limit 5
alias your field names
as a more descriptive name.
select first as first_name, last as last_name from customers
the meat of SQL.
join data from one table to another
on a certain condition. specifically, we’ll use
inner joins for now. you can
join multiple tables together by repeating the clause.
note the need to preface field names with the table they belong to.
select customers.first, customers.last, pets.species, pets.name from customers inner join pets on customers.id = pets.owner_id
putting it all together!
here’s a mega query that uses all of the above concepts.
give a summary of the first 8 transactions Queen Bey made at the pet store since 2010
select transactions.date as transaction_date, sum(items.price) as total_cost, count(*) as number_of_items from transactions inner join customers on customers.id = transactions.customer_id inner join transaction_items on transactions.id = transaction_items.transaction_id inner join items on items.id = transaction_items.item_id where transactions.date >= '2010-01-01' and customers.first = 'beyonce' group by transactions.date order by transactions.date limit 8