PostgreSQL Exercise

Create the following tables for ERD (in postgresql): see image.

Insert the data into each of the tables for the survey data - these are ordered by 1-side first, many-side next. (schema= sso)

BEGIN TRANSACTION;
INSERT INTO SSO.Site VALUES('DR-1',-49.850000000000001419,-128.56999999999999317);
INSERT INTO SSO.Site VALUES('DR-3',-47.149999999999998578,-126.71999999999999886);
INSERT INTO SSO.Site VALUES('MSK-4',-48.869999999999997443,-123.40000000000000568);
INSERT INTO SSO.Visited VALUES('619','DR-1','1927-02-08');
INSERT INTO SSO.Visited VALUES('622','DR-1','1927-02-10');
INSERT INTO SSO.Visited VALUES('734','DR-3','1930-01-07');
INSERT INTO SSO.Visited VALUES('735','DR-3','1930-01-12');
INSERT INTO SSO.Visited VALUES('751','DR-3','1930-02-26');
INSERT INTO SSO.Visited VALUES('752','DR-3',NULL);
INSERT INTO SSO.Visited VALUES('837','MSK-4','1932-01-14');
INSERT INTO SSO.Visited VALUES('844','DR-1','1932-03-22');
INSERT INTO SSO.Person VALUES('dyer','William','Dyer');
INSERT INTO SSO.Person VALUES('pb','Frank','Pabodie');
INSERT INTO SSO.Person VALUES('lake','Anderson','Lake');
INSERT INTO SSO.Person VALUES('roe','Valentina','Roerich');
INSERT INTO SSO.Person VALUES('danforth','Frank','Danforth');
INSERT INTO SSO.Survey VALUES(619,'dyer','rad',9.8200000000000002842);
INSERT INTO SSO.Survey VALUES(619,'dyer','sal',0.13000000000000000444);
INSERT INTO SSO.Survey VALUES(622,'dyer','rad',7.7999999999999998223);
INSERT INTO SSO.Survey VALUES(622,'dyer','sal',0.089999999999999996669);
INSERT INTO SSO.Survey VALUES(734,'pb','rad',8.4100000000000001421);
INSERT INTO SSO.Survey VALUES(734,'lake','sal',0.050000000000000002775);
INSERT INTO SSO.Survey VALUES(734,'pb','temp',-21.5);
INSERT INTO SSO.Survey VALUES(735,'pb','rad',7.2199999999999997513);
INSERT INTO SSO.Survey VALUES(735,NULL,'sal',0.059999999999999997779);
INSERT INTO SSO.Survey VALUES(735,NULL,'temp',-25.999999999999999999);
INSERT INTO SSO.Survey VALUES(751,'pb','rad',4.3499999999999996447);
INSERT INTO SSO.Survey VALUES(751,'pb','temp',-18.5);
INSERT INTO SSO.Survey VALUES(751,'lake','sal',0.10000000000000000555);
INSERT INTO SSO.Survey VALUES(752,'lake','rad',2.1899999999999999467);
INSERT INTO SSO.Survey VALUES(752,'lake','sal',0.089999999999999996669);
INSERT INTO SSO.Survey VALUES(752,'lake','temp',-16.0);
INSERT INTO SSO.Survey VALUES(752,'roe','sal',41.600000000000001421);
INSERT INTO SSO.Survey VALUES(837,'lake','rad',1.4599999999999999644);
INSERT INTO SSO.Survey VALUES(837,'lake','sal',0.20999999999999999222);
INSERT INTO SSO.Survey VALUES(837,'roe','sal',22.5);
INSERT INTO SSO.Survey VALUES(844,'roe','rad',11.25);
COMMIT;

#10-confirm counts for each table

select count(*) as survey_count FROM SSO.Survey;
select count(*) as visited_count FROM SSO.Visited;
select count(*) as person_count FROM SSO.Person;
select count(*) as site_count FROM SSO.Site;

1. Create a view that will show the person details, site name, and "quant" measurement with accompying count, and average readings for each person, site name and "quant" sorted by the average reading, person, site name and "quant

We are going to name this view all_person_site_q_measurements

(hint: 14 rows affected)

2. Create a view that list all readings from the Survey table along with the associated Latitude and Longitude position from the Site table and the date of the reading for data collected by Anderson Lake.

Name this view: anderson_lake_collections

(hint: 11 rows affected)

3. Create a view that list each person, location, and associated measurements for readings that are undated.

Name this view: undated_survey_measurements

(hint: 4 rows affected)

Audit the Survey Table for Deletes

1. Define and create your trigger function to audit deletes on the survey table.

2. Create your trigger on the survey table.

3. Remove Rows

Note: In the practice we executed this multi-insert statement to insert these 4 rows into the survey table.

INSERT INTO SSO.survey VALUES
(619,'lake','rad',8.72),
(619,'lake','sal',2.03),
(622,'lake','rad',8.8),
(622,'lake','sal',1.9)
;

You will delete these 4 records using seperate delete statements for the first 2 records and a single delete statement for the last two records.

In the cells below,

• Write two separate/distinct DELETE Statements for the first two 619/lake rows of data shown above.
  • removing this data (619,'lake','rad',8.72)
  • removing this data (619,'lake','sal',2.03)
• Show the data in the survey table.
• Show the resulting audit rows.

4. Remove the last two rows with a single delete statement.

• Write a single DELETE Statement for the last two 622/lake rows as shown above.
  • remove this data (622,'lake','rad',8.8)
  • remove this data (622,'lake','sal',1.9)
• Show the data in the survey table.
• Show the resulting audit rows.