System Queries

Select * from information_schema.tables;
Select Table_name from information_schema.tables
Where table_schema='public';
Select * from information_schema.columns;
Select column_name, data_type from information_schema.columns
where table_name='grantapplication';
Select sequence_name from information_schema.sequences;

Select column_name, data_type, constraint_name, constraint_Type
From information_schema.columns
Join information_schema.table_constraints
on information_schema.columns.table_name=information_schema.table_constraints.table_name
where information_schema.columns.table_name='grantapplication'
Order by column_name;

Select * from pg_catalog.pg_tablespace;
Select * from pg_catalog.pg_extension;

Advanced Query code

/******************************
* Set Operations
******************************/
CREATE TEMP TABLE email
(firstname text,
lastname text,
email text);

INSERT INTO email(firstname, lastname, email)
values('Jordan', 'Lawrence', 'jordanl@gmail.com'),
('Tammy', 'Standish', 'tstandish@msn.com'),
('Lester', 'Roberts', 'lr@yahoo.com'),
('Lynn', 'Kellerman', 'kellerman@gmail.com');

SELECT lastname, firstname, email, 'temptable' as tblSource from email
UNION
SELECT PersonLastname, personfirstname, personemail, 'Persontable'
FROM person
JOIN personaddress
USING (personkey)
WHERE personaddressCity='Bellevue';

SELECT Personlastname lastname,
personfirstname firstname,
personemail email,
'donor' "role"
FROM person
JOIN donation USING(personkey)
Where donationamount >=2000
UNION
SELECT Personlastname lastname,
personfirstname firstname,
personemail email,
'client'
FROM person
JOIN grantapplication USING(personkey)
WHERE granttypekey=2;

SELECT personkey, personlastname, personfirstname
FROM person
JOIN donation USING(personkey)
INTERSECT
SELECT personkey, personlastname, personfirstname
FROM person
JOIN grantapplication USING(personkey);

SELECT personaddresscity
FROM personaddress
JOIN person USING (personkey)
JOIN donation USING (personkey)
INTERSECT
SELECT personaddresscity
FROM personaddress
JOIN person USING (personkey)
JOIN grantapplication USING (personkey);

SELECT personaddresscity
FROM personaddress
JOIN person USING (personkey)
JOIN donation USING (personkey)
EXCEPT
SELECT personaddresscity
FROM personaddress
JOIN person USING (personkey)
JOIN grantapplication USING (personkey);

SELECT granttypename FROM granttype
EXCEPT
SELECT granttypename FROM grantapplication
JOIN granttype USING (granttypekey);
/*****************************
* Windows Functions
*****************************/

SELECT granttypename, grantapplicationkey, grantapplicationamount,
RANK() OVER (PARTITION BY granttypeName ORDER BY Grantapplicationamount DESC)
FROM grantapplication
JOIN granttype ON granttype.granttypekey=grantapplication.granttypekey
WHERE granttypename='Food';

SELECT granttypename, grantapplicationkey, grantapplicationamount,
DENSE_RANK() OVER (PARTITION BY granttypeName ORDER BY Grantapplicationamount
DESC)
FROM grantapplication
JOIN granttype ON granttype.granttypekey=grantapplication.granttypekey
WHERE granttypename='Food';

SELECT grantapplicationkey, granttypename, grantapplicationamount,
ROW_NUMBER() OVER(ORDER BY grantapplicationkey)
FROM grantapplication
JOIN granttype using(granttypekey);

SELECT grantapplicationkey, granttypename, grantapplicationamount,
ROW_NUMBER() OVER(ORDER BY grantapplicationamount)
FROM grantapplication
JOIN granttype using(granttypekey);

SELECT *
FROM
(SELECT grantapplicationkey, granttypename, grantapplicationamount,
ROW_NUMBER() OVER(ORDER BY grantapplicationamount)
FROM grantapplication
JOIN granttype using(granttypekey))grants
WHERE ROW_NUMBER BETWEEN 20 and 30;

SELECT *
FROM
(SELECT grantapplicationkey, granttypename, grantapplicationamount,
ROW_NUMBER() OVER(ORDER BY grantapplicationamount)
FROM grantapplication
JOIN granttype using(granttypekey))grants
WHERE ROW_NUMBER BETWEEN 20 and 30;

SELECT granttypename, grantapplicationamount,
LAST_VALUE(grantapplicationamount) OVER
(PARTITION BY granttypekey ORDER BY Grantapplicationamount
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM grantapplication
JOIN granttype using(granttypekey);

/**************************************
* Pivot table with CROSSTAB
*************************************/

CREATE TEMP TABLE applications2018
(
GranttypeName TEXT,
applicationdate DATE,
applciationamount NUMERIC
);



INSERT INTO applications2018
SELECT Granttypename, grantapplicationDate, grantapplicationamount
FROM grantapplication
JOIN granttype ON granttype.granttypekey = grantapplication.granttypekey
WHERE EXTRACT (YEAR FROM grantapplicationdate)=2018;

SELECT *
FROM CROSSTAB('SELECT EXTRACT(MONTH FROM applicationdate)::INTEGER,
granttypename, SUM(applciationamount)
FROM applications2018
GROUP BY 1,2 ORDER BY 1, 2')
FINAL_RESULT(Month INTEGER, Food NUMERIC, Rent NUMERIC, School NUMERIC,
Dental NUMERIC, Medical NUMERIC, Childcare NUMERIC ,Misc NUMERIC );

CREATE TEMP TABLE citydonations
(
"Month" Integer,
city text,
amount numeric
);

CREATE TEMP TABLE citydonations
(
"Month" Integer,
city text,
amount numeric
);

INSERT INTO citydonations
SELECT EXTRACT(MONTH FROM Donationdate), PersonaddressCity, donationamount
FROM (
SELECT DINSTINCT ON(donationkey)
donationkey,
donationdate,
personaddresscity,
donationamount
FROM donation
JOIN personaddress
USING (personkey)
) donations;


SELECT *
FROM CROSSTAB('SELECT "Month" :: INTEGER,

City, SUM(amount) FROM citydonations
GROUP BY 1,2
ORDER BY 1, 2')
FINAL_RESULT(Month INTEGER,
Seattle NUMERIC,
Redmond NUMERIC,
"New York" NUMERIC,
Bellevue NUMERIC,
Tukwilla NUMERIC,
Kent NUMERIC);