Saturday, November 23, 2019

SQL for Postgresql

/*
List all the tenants.
List all the current maintenance requests.
Which apartments are currently occupied? join apartment and lease
Which apartments are empty? Left outer join with Apartment and Lease
Update maintenance request 2 to give it a completion date of today.
Insert a new tenant: Tammy Benedict, tbenedict@gmail.com, phone: 2065551200. She has no sub tenants
Add a new 6 month lease starting today on apartment 205, standard deposit, no pets
Record her first rent payment
Turn in the SQL and screenshots of the the answers
You may have to put the column and table names in double quotes if you made your database
from lucidcharts
*/
Select * from "person";
Select * from donation;
Select personlastname, personfirstname, personaddressStreet, personaddressCity
From person
inner join personaddress
on person.personkey=personaddress.personkey;

--outer join
Select granttypename, grantapplication.granttypekey
From granttype
Left outer join grantapplication
On granttype.granttypekey=grantapplication.grantTypekey
Where grantapplication.granttypekey is null;

--updates are to change existing records
Select * from person where personkey=2;
Update person
Set Personlastname='Caroll',
personemail='lindaCaroll@gmail.com'
WHERE personkey=2;

Insert into person(personlastname, personfirstname, personemail, personprimaryphone, persondateadded)
Values('Nelson','Tom', 'tom.nelson@msn.com', '2065553233', current_date);

select * from person where personlastname='Nelson';

Insert into Donation(personkey, donationamount, donationDate)
Values(134, 500.00, current_date);
Select * from donation where personkey =134;

No comments:

Post a Comment