Joins

--joins

Select * from employee;
Select * from jobposition;

--Cross Join
Select * from Employee
Cross join jobposition
order by employeekey;

Select * from Employee, jobposition
Order by employeekey

--inner Join
Select EmployeeKey, employee.personkey, Personlastname, Personfirstname
From Person
Inner Join Employee
On Employee.personkey=Person.personkey;

Select EmployeeKey, employee.personkey, Personlastname, Personfirstname
From Person
Join Employee
On Employee.personkey=Person.personkey;

Select Employee.EmployeeKey, employee.personkey, Personlastname, Personfirstname,
EmployeePositionStartDate, PositionName
From Person
Inner Join Employee
On Employee.personkey=Person.personkey
Inner join EmployeePosition
On EmployeePosition.employeekey=employee.employeekey
Inner Join JobPosition
On Jobposition.positionkey=employeeposition.positionkey;

Select e.EmployeeKey, e.personkey, Personlastname, Personfirstname,
EmployeePositionStartDate, PositionName
From Person p
Inner Join Employee e
On p.personkey=e.personkey
Inner join EmployeePosition ep
On ep.employeekey=e.employeekey
Inner Join JobPosition jp
On ep.positionkey=jp.positionkey;

--older syntax
Select employee.EmployeeKey, employee.personkey, Personlastname, Personfirstname,
EmployeePositionStartDate, PositionName
From person, Employee, employeeposition, jobposition
Where person.personkey=employee.personkey
And employee.employeekey=employeeposition.employeekey
and jobposition.positionkey =employeeposition.positionkey;

--accidental cross join by leaving out a relationship
Select employee.EmployeeKey, employee.personkey, Personlastname, Personfirstname,
EmployeePositionStartDate, PositionName
From person, Employee, employeeposition, jobposition
Where person.personkey=employee.personkey
and jobposition.positionkey =employeeposition.positionkey;

--aggregate
Select extract('year' from grantapplicationdate)"Year",
GrantTypeName, Sum(Grantapplicationamount) "total",
avg(Grantapplicationamount) Average
From Grantapplication
Inner Join Granttype
On GrantType.granttypekey=grantapplication.granttypekey
Group by extract('year' from grantapplicationdate), granttypename
Order by extract('year' from grantapplicationdate), granttypeName;

Select extract('year' from grantapplicationdate)"Year",
Sum(grantapplicationamount) from grantapplication
Group by extract('year' from grantapplicationdate);

---Danger!!
--total donation by city(person personaddress-donation)

Select personaddresscity "City", Sum(donationamount) "total"
From Person
Inner Join personaddress
On person.personkey=personaddress.personkey
Inner Join donation
on person.personkey=donation.personkey
Group by personaddresscity;

Select Sum(donationamount) from Donation;

--outer joins about not matching data

Select distinct granttypename, grantapplication.granttypekey
From GrantType
left outer join Grantapplication
On granttype.granttypekey=grantapplication.granttypekey;

Select distinct granttypename, grantapplication.granttypekey
From GrantType
left outer join Grantapplication
On granttype.granttypekey=grantapplication.granttypekey
Where grantapplication.granttypekey is null;

Select distinct granttypename, grantapplication.granttypekey
From GrantApplication
Right outer join GrantType
On granttype.granttypekey=grantapplication.granttypekey;
--

Select personaddressCity, donationamount from personaddress
Inner join Donation
on personaddress.personkey=donation.personkey
where donation.personkey=7

--fulljoin
Select distinct granttypename, grantapplication.granttypekey
From GrantApplication
Full join GrantType
On granttype.granttypekey=grantapplication.granttypekey;

--using
Select EmployeeKey, employee.personkey, Personlastname, Personfirstname
From Person
Inner Join Employee
using(personkey);

Select EmployeeKey, employee.personkey, Personlastname, Personfirstname, personaddressCity
From Person
natural join Employee
natural join personaddress;

SELECT Personlastname, personfirstname, Donationdate, Donationamount, grantapplicationdate,
grantapplicationamount
From donation
Join person
On person.personkey=donation.personkey
Join grantapplication
On grantapplicationdate between donationdate And DonationDate + interval '7 day'
Where personlastname='Baker'
order by personlastname, personfirstname;