Built in functions

--functions
Select 5.0 * ((3 + 2) -1 )/3;
Select donationamount, donationamount * .77 AS Charity,
donationamount * .23 as Maintenance
From Donation;
Select donationamount, donationamount /9 as Charity
From Donation;
Select donationamount, round(donationamount /9,2) as Charity
From Donation;
Select donationamount, Ceiling(donationamount /9) as Charity
From Donation;
Select donationamount, floor(donationamount /9) as Charity
From Donation;
Select donationamount, to_char(donationamount /9,'$ 9,999.99') as Charity
From Donation;
Select Random() as Rand;

Select * from person where personkey=ceiling(Random() * ((132-1)+1)+1);

Select * from logintable order by personkey;
Select crypt('SmithPass', Gen_salt('bf', 8)) as password;
Select * from Logintable where personusername='janderson'
and personpassword=crypt('AndersonPass', personpassword);
           
--Datetime functions
Select current_date;
Select current_timestamp;
Select current_time;
Select donationdate, extract('year' from donationdate) as "Year"
From donation; 
Select donationdate, extract('month' from donationdate) as "Month"
From donation;            
Select donationdate, extract('day' from donationdate) as "Month"
From donation;
Select donationdate, extract('month' from donationdate) as "Month"
From donation; 
Select * from employeeposition;
Select employeekey, age(employeepositionstartdate) 
from employeeposition
order by age(employeepositionstartdate) desc;
Select grantapplicationkey, grantapplicationdate, 
grantapplicationdate + interval '2 days' "read by"
From grantapplication;
           
Select grantapplicationkey, grantapplicationdate, 
grantapplicationdate + interval '48 hours' "read by"
From grantapplication; 

--concatination
Select personlastname || ', ' || personfirstname
As "Name" 
From Person;          
Select position ('@' in personemail) from person;
Select substring(personemail from 1 for position('@' in personemail)-1)
As username from person;
--aggregate funtions
Select Avg(donationamount) from donation;
Select Sum(donationamount) from donation;
Select Max(donationamount) from donation;
Select MIN (donationamount) from donation;
Select Count(donationamount) from donation; 
           
Select granttypekey, sum(grantapplicationAmount) as Total
From grantapplication
group by granttypekey
order by granttypekey; 

Select extract('Year' from donationdate) as "year",
sum(donationamount)as total
From donation
Group by extract('Year' from donationdate); 
           
Select extract('Year' from donationdate) as "year",
sum(donationamount)as total,
avg(donationamount) as average,
count(donationamount) as "number"          
From donation
Group by extract('Year' from donationdate);            
           
Select extract('Year' from donationdate) as "year",
sum(donationamount)as total,
avg(donationamount) as average,
count(donationamount) as "number"          
From donation
Group by extract('Year' from donationdate)
having avg(donationamount) > 500; 

Select extract('Year' from donationdate) as "year",
sum(donationamount)as total,
avg(donationamount) as average,
count(donationamount) as "number"          
From donation
Where extract('Year' from donationdate) > 2017
Group by extract('Year' from donationdate)
having avg(donationamount) > 500;            
           
Select count(personkey) from donation; 
Select count(Distinct personkey)from donation;