-- About the person & car relations

-- Concatenation
select concat(persfname,' ',perslname) as salesperson
	from person;

-- Multi level sort
select cartype, carcost
	from car
	order by cartype, carcost desc;

-- Aggregate operators, group by
select cartype, sum(carcost) as total_cost, avg(carcost) as average_cost
	from car
	group by cartype;

-- is null & is not null
select cartype, carcost
	from car
	where carsell is null;
	-- don't use carsell = null or carsell <> null

-- coalesce function
select carid, coalesce(carsell, 0) as sales_personid
	from car;

-- JOINS

-- inner join matches existing records from both tables
	select *
	from person INNER JOIN car
		on person.personid = car.carsell;

-- left outer join includes all records from 'left' (i.e., first) table
-- and matches with the other table
-- nulls pad out missing fields in joined table
	select *
	from person LEFT OUTER JOIN car
		on person.personid = car.carsell;

-- right outer join includes all records from 'right' (i.e., second) table
-- and matches with the other table
-- nulls pad out missing fields in joined table
	select *
	from person RIGHT OUTER JOIN car
		on person.personid = car.carsell;

-- full outer join includes all records from both tables
-- matching where possible
-- nulls pad out missing fields in joined table
	select *
	from person FULL OUTER JOIN car
		on person.personid = car.carsell;

-- MySQL does not implement FULL OUTER JOIN; work around is
	select *
	from person LEFT OUTER JOIN car
		on person.personid = car.carsell

	union

	select *
	from person RIGHT OUTER JOIN car
		on person.personid = car.carsell;

-- cross join matches every record in first table with every record in second, no keys involved
select *
	from person cross join car;
