Admin commands

--System queries
Select * from information_Schema.tables
Where table_schema='public';

Select table_schema, table_name, is_updatable
from information_schema.views
Where Not table_schema='pg_catalog'
And not table_schema='information_schema';

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;

--index
Create index on Person(personlastname);

Alter index person_personlastname_idx 
rename to idx_lastname;

Select Grantapplicationkey, grantapplicationdate, personlastname, grantapplicationamount
From grantapplication
join person
using (personkey)
Where personlastname = 'Blake';

Create unique index on person(personprimaryphone);

Create index on grantapplication (granttypekey, personkey);

Create index on donation(donationamount)
Where donationamount > 500;

--roles
Create role employeerole;

Grant connect on database communityassistpg to employeerole;
Grant usage on Schema public to employeerole;
Grant SELECT on All Tables in schema public to employeerole;
Grant usage on Schema employeeschema to employeerole;
Grant SELECT on All Tables in schema employeeschema to employeerole;

Create Role janderson with password 'P@ssw0rd1' Login inherit;

Grant employeerole to janderson;