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;

Functions and triggers

--functions
Create or replace function myCube(num Integer)
Returns integer
As $$
Begin
RETURN num * num * num;
End;
$$ language plpgsql;



Select myCube(5);

create or replace function employeeschema.donationpercentages
(amount numeric, percentage numeric)
Returns numeric
AS $$
Begin
--semicolons required
Return amount * percentage;
End;
$$ language plpgsql;

select donationkey, donationamount, 
employeeschema.donationpercentages(donationamount, .76)"to charity"
From donation;

select donationkey, donationamount, 
employeeschema.donationpercentages(donationamount, 76)"to charity"
From donation;

create orreplace function employeeschema.donationpercentages
(amount numeric, percentage numeric)
Returns numeric
AS $$
Begin
if percentage > 1
   Then percentage = percentage/100;
end if;
--semicolons required
Return round((amount * percentage),2);
End;
$$ language plpgsql;

Create or replace function makeusername(firstname text, lastname text)
returns text
AS $$
Begin
Return Lower(substring(firstname,1,1)|| lastname); 
end;
$$ language plpgsql;

Select personfirstname, personlastname, 
makeusername(personfirstname, personlastname) as username
From person;

create schema donorschema;

Create or replace function donorschema.getdonations
(donorkey integer)
Returns table
(
  "Date" timestamp,
  amount numeric,
  validation UUID
)
AS $$
BEGIN
Return query
Select donationdate, donationamount, donationvalidation
From donation
Where personkey=donorkey;
End;
$$ language plpgsql;
Select * from donation;

Select * from donorschema.getdonations(6);

Alter table person
add personannouncements boolean default true;

Create or replace function addperson
(
 firstname text,
 lastname text,
 email text,
 phone text,
 announce boolean,
 apartment text,
 street text,
 city text,
 "state" char(2),
 zipcode char(11),
 passwd varchar(50)
) returns void
as $$
Insert into person(
personfirstname, personlastname, personemail,
personprimaryphone, persondateadded, personannouncements)
values(firstname, lastname, email, phone, 
current_timestamp, announce);

Insert into personaddress(personkey, personaddressstreet, 
personaddresscity, personaddressstate, personaddresszipcode,
personaddressapt)
values(currval('person_personkey_seq'), street, city, 
"state",zipcode, apartment);

Insert into logintable(personkey, personusername, personpassword)
Values(currval('person_personkey_seq'), makeusername(firstname, lastname),
createpassword(passwd));
$$ language sql;

Select addperson('Joseph,','Rogers', 'jrogers@gmail.com',
'2065552345', True, '641', '222 8th avenue', 'Seattle', 'WA',
'98100', 'RogersPass');

Select * from person where personkey =138
Select * from personaddress where personkey=138;
Select * from logintable where personkey=138;

Create or replace function updateperson
(
 pkey integer,
 firstname text,
 lastname text,
 email text,
 phone text,
 announce boolean,
 apartment text,
 street text,
 city text,
 "state" char(2),
 zipcode char(11)
 
) returns void
as $$
Update person
set personlastname=lastname,
personfirstname=firstname,
personemail=email,
personprimaryphone=phone,
personannouncements=announce
Where personkey=pkey;

update personaddress
Set personaddressstreet=street,
personaddresscity=city,
personaddressstate="state",
personaddresszipcode=zipcode,
personaddressapt=apartment
where personkey = pkey;

$$ language sql;
      
Select updateperson(138,'Joseph,','Rogers', 'jrogers@gmail.com',
'2065552345', False, '641', '222 8th avenue', 'Bellevue', 'WA',
'98100');

alter table grantapplication
add isflagged boolean default False; 
      
Create or replace function flagrequest()
Returns trigger as
$Body$
Begin
If NEW.grantapplicationamount >
   (Select granttypeonetimemax
    from granttype
    where granttypekey = new.granttypekey)
Then
 Update grantapplication
    Set isflagged =True
    where grantapplicationkey=new.grantapplicationkey;
end if;
Return new;
End;
$Body$ language plpgsql;

Create trigger flag_over_amounts
After Insert
on grantapplication
For each row
Execute procedure flagrequest();
      
Insert into grantapplication(Grantapplicationdate, personkey, granttypekey,
    grantapplicationamount, grantapplicationexplain)
     values(current_timestamp, 138, 1, 150.00, 'hungry');
      
Select * from grantapplication where grantapplicationkey = 
      (select max(grantapplicationkey) from grantapplication);
     
     
      

     

     
      
     
      
   

Views and misc

Create schema employeeSchema;

Create view employeeschema.Employeeview
As
Select 
employeekey,
personfirstname firstname, 
personlastname lastname,
personemail email,
positionname,
employeepositionstartdate startdate
From employee
inner join Person
using(personkey)
inner join employeeposition
using(employeekey)
inner join jobposition
using (positionkey);

Select * From employeeschema.employeeview;
Select personfirstname, personlastname from employeeschema.employeeview;
Select firstname, lastname from employeeschema.employeeview;

--updateable
--if there are no joins, no calculated fields, 

Create view employeeschema.emailview
As
Select personlastname,
personfirstname,
personemail
From person;

Create or replace view employeeschema.emailview
As
Select personlastname,
personfirstname,
personemail,
persondateadded
From person
Where personlastname like 'J%'
with check option;

Select * from employeeschema.emailview;
Select * from person where personkey=(select max(personkey)from person);



Update employeeschema.emailview
Set personfirstname='Jason'
where personemail='jAnderson@gmail.com';

Insert into employeeschema.emailview(personlastname, personfirstname, personemail, 
          persondateadded)
values('Brown','Nelson', 'nb@hotmail.com', current_timestamp);

Create materialized view employeeschema.grantview
As
Select Grantapplicationkey, grantapplicationdate, 
granttypename, personlastname, grantapplicationamount
From person
join grantapplication
using (personkey)
join granttype
using (granttypekey);

Select * from employeeschema.grantview where grantapplicationkey =
(Select max(grantapplicationkey) from grantapplication);

Insert into grantapplication(grantapplicationdate, granttypekey, personkey, 
 grantapplicationamount, grantapplicationexplain )
values (current_timestamp, 1, 50, 400.00, 'lunch time');

refresh materialized view employeeschema.grantview;

SQL 2

SELECT * FROM pythonclub3.officer;
Use Sakila;
Select * from Actor;

Select first_name, last_name, title, description
From actor
inner join film_actor
using(actor_id)
inner Join Film
using(film_id)
Where last_name='Guiness'
And first_name='Sean'
Or title Like 'S%';

Select first_name, last_name, title, description
From actor
inner join film_actor
On actor.actor_id=film_actor.actor_id
inner Join Film
On Film.film_id=film_actor.film_id
Where last_name='Guiness';

Select * from payment
Where payment_date between '2005-06-01' and '2005-06-30';

Select * from payment where amount > 5;

Use Pythonclub3;
Insert into Member(memberlastName, memberFirstName, memberEmail, DateJoined)
Values('Jones','Dale','dj@gmail.com','2019-02-19');
Select * from member;
Insert into officermember(StartDate, member_idmember, Officer_idOfficer)
values('2019-02-20',2,2);
Insert into Location(LocationName, Address, City, zipcode, locationPhone)
values('Central','1701 Broadway','Seattle','98122', '2065551234');
Insert into meeting(MeetingDate, MeetingTime, MeetingTopic, OfficerMember_idOfficerMember, Location_idLocation)
Values('2019-02-20', '16:00:00', 'Stuff and more stuff', 2,1);

Update member
Set memberfirstname='Bill'
Where idmember=2;

Delete from member where idmember=2;

Insert into Member(memberlastName, memberFirstName, memberEmail, DateJoined)
Values('Mann','Doug','dm@gmail.com','2019-02-19'),
('Doe','jane','jd@gmail.com','2019-02-19'),
('Nelson','Anne','an@gmail.com','2019-02-19'),
('Brown','Sue','sb@gmail.com','2019-02-19');

Create and alter tables

Create table location
(
 locationkey serial primary key,
 locationname text not null,
 locationaddress text not null,
 locationcity text default 'Seattle',
 locationstate char(2) default 'WA',
 locationzip varchar(11) Not Null,
 locationphone varchar(13) Not null,
 Locationemail text not null unique,
 locationURL text

);

Create table event
(
 eventkey serial primary key,
 eventname text not null,
 eventdate Date not null,
 eventtime Time not null,
 eventdescription text,
 employeekey int references employee(Employeekey)
);

Create table locationroom
(
 locationroomkey serial,
 locationkey int,
 roomnumber int not null,
 eventkey int
);

Alter table locationroom
add primary key(locationroomkey);

Alter table locationroom
Add constraint fk_location Foreign key(locationkey)
    references location(locationkey);
 
Alter table locationroom
Add constraint fk_eventForeign Foreign Key(eventkey)
    references Event(eventkey);
 
Create table personevent
(
  personkey int not null,
  eventkey int not null
);

Alter table personevent 
Add primary key (personkey, eventkey);

Alter table personevent
Add constraint fk_personevent_person Foreign key (personkey)
   references person(personkey),
Add constraint fk_personevent_event Foreign key (eventkey)
   references event(eventkey);  
   
 Alter table person
 add announcement Boolean Default true;
 
 Select * from person;
 
 Alter table person
drop column announcement;

--temp tables
Create temp table emaillist
(
 lastname text,
 firstname text,
 email text
);

Insert into emaillist(lastname, firstname, email)
Select personlastname, personfirstname, personemail from person;

select * from emaillist;

Drop table emaillist;

Select personlastname, personfirstname, personemail 
into temp emaillist from person;

Insert Update Delete

--inserts updates and deletes

Insert into person(personlastname, personfirstname, 
       personemail, personprimaryphone,persondateadded)
Values('Mouse', 'Mickey', 'mm@disney.com', '2065551470', current_timestamp);

Insert into personaddress(personkey, personaddressstreet, 
        personaddresszipcode)
values(CURRVAL('person_personkey_seq'),'100 South enchanted','98001' );

Insert into logintable(personkey, personusername, personpassword)
Select CURRVAL('person_personkey_seq'), 
LOWER(SUBSTRING(personfirstname, 1,1)|| personlastname), 
createpassword(personlastname || 'Pass')
From person Where personkey=CURRVAL('person_personkey_seq')

Select * from person; 
Select * from personaddress where personkey=133;
Select * from Logintable where personkey=133;

--Update

Select * from person where personkey=2;

Update person
Set personlastname='Hamilton',
personemail='lindahamilton@gmail.com'
Where personkey=2;

Select personlastname, personfirstname, personemail into emaillist from person ;

Select * from emaillist;

Begin transaction;

Update emaillist
Set personlastname='Smith';

Rollback transaction;
Commit Transaction;

Drop table emaillist;

Select * from granttype;

Update Granttype
Set granttypeonetimemax=granttypeonetimemax * 1.05,
granttypelifetimemax=granttypelifetimemax * 1.05

Delete from personaddress where personkey=133;
Delete from logintable where personkey=133;
Delete From Person where personkey =133;

Select * from person where personkey=153;

Select * from jobposition;

Insert into jobposition(positionname)
Values('vice president'),
('cook and bottle washer'),
('garbage dumper')

SQL

--SQL Ansi ISO

Use VincentVinyl;
Select * from Album
Select albumtitle, albumstudion from album
--first generation binary machine language
--2nd assembler
--3rd generation python, c++, C#, Java, C, Fortran, Cobal procedural
--4th generation what you to do not how

Select * from Person
order by personlastname desc;

Select * from employee;

Select * from Album where AlbumID=3

Select * from Album where AlbumStudion='columbia'

Select * from purchase where purchasedate='12/20/2018'

Select purchaseID, year(purchasedate) AS "YEAR"
From purchase

Select * from Saledetail where SaleDetailDiscount > 0

--joins
Select * from Employee

Select EmployeeID, [PersonLastName],[PersonFirstName]
From Employee
Inner Join Person
on employee.PersonID=person.PersonID

Select * from inventory

Select InventoryID, purchasedetail.purchasedetailid, 
Albumtitle, conditionName, purchaseprice,InventorySalePrice,
Inventorysaleprice - purchaseprice AS "Difference"
From Inventory
inner join condition
on Inventory.ConditionID=Condition.ConditionID 
inner join purchaseDetail
on purchaseDetail.PurchaseDetailID=inventory.PurchaseDetailID
inner join Album
on Album.AlbumID=PurchaseDetail.AlbumID

--insert update delete

Insert into album(AlbumTitle, AlbumYear, AlbumStudion)
Values('American Idiot',2004, '80080')

Select * from Album

Insert into purchase(PurchaseDate, PersonID)
values(GetDate(), 2)
Select * from purchase

Insert into PurchaseDetail(purchaseID, AlbumID, PurchasePrice)
Values(4, 6, 10.00)
select * from purchaseDetail

--updates are dangerous

Select * from Person



Insert into inventory(PurchaseDetailID, ConditionID, InventorySalePrice)
Values(8, 1, 20.00)

Select * from Inventory

Update person
Set PersonFirstName='Debby',
personemail='debbybrown@gmail.com'
Where personid=2

begin tran

Update Person
Set personlastname='Smith'

rollback tran

Delete from Person 
Where personid=1