DZUQS2a1

1-- Pavan Gollapalli
2-- a2 
3
4\pset border 2
5
6\c postgres
7
8DROP database If EXISTS a2;
9CREATE database a2;
10
11\c a2
12
13drop table if exists Colleges cascade;
14drop table if exists Students cascade;
15drop table if exists Applications cascade;
16
17create table Colleges(cName text, state text, enrollment integer);
18create table Students(sID integer, sName text, GPA real, sat integer);
19create table Applications(sID integer, cName text, major text, decision text DEFAULT '');
20
21-- Write a trigger function to update Applications
22-- everytime a new applicaton for a student is inserted
23
24CREATE OR REPLACE FUNCTION fn_auto_accept_IS() RETURNS TRIGGER 
25LANGUAGE pl/pgsql
26$$
27BEGIN
28
29END
30$$
31-- Write a function that returns a boolean after testing
32-- admission criteria for CMU/IS
33
34-- Criteria:  a student is admited to CMU/IS if his/her
35--     1. high school GPA >= 3.7
36--     2. SAT score >= 1450  (by the new scoring system which has a max of 1600)
37--
38-- These are, of course, not real criteria used.
39--
40
41CREATE OR replace FUNCTION IS_criteria(app Applications) RETURNS boolean
42LANGUAGE pl/pgsql
43$$
44BEGIN
45    IF new.GPA >= 3.7 and new.sat >= 1450 THEN
46        RETURN TRUE;
47    ELSE
48        RETURN FALSE;
49    END IF;
50END
51$$
52
53DROP TRIGGER IF EXISTS tr_auto_accept_IS ON Applications;
54
55CREATE TRIGGER IF EXISTS tr_application_update AFTER INSERT ON Applications
56    FOR EACH ROW
57    when()
58    EXECUTE PROCEDURE fn_auto_accept_IS();
59
60-- Write the actual trigger
61-- That will update the 'decision' field of Applications based on the
62-- result of 'fn_auto_accept_IS'.  If the function returns True the decision is 'Y'
63-- otherwise the decision of 'N'.
64
65-- Look up the boiler plate synax from the lab
66
67
68-- Insert student data
69
70insert into Students values (123, 'Jack', 3.9, 1000);
71insert into Students values (234, 'Jill', 3.9, 1500);
72insert into Students values (345, 'Pat', 3.9, 1450);
73insert into Students values (567, 'Bob', 3.8, 1550);
74
75-- For each insrt into Application the trigger should run
76
77insert into Applications values (123, 'CMU', 'IS');
78insert into Applications values (234, 'CMU', 'IS');
79insert into Applications values (234, 'Stanford', 'CS');
80insert into Applications values (345, 'CMU', 'BZxx');
81insert into Applications values (345, 'CMU', 'IS');
82insert into Applications values (345, 'Berkeley', 'EECS');
83insert into Applications values (567, 'CMU', 'IS');
84
85-- checking the output
86
87SELECT s.sid, s.sname, s.gpa, s.sat, a.cname, a.major, a.decision
88  FROM Applications AS a
89  JOIN Students AS s
90    ON  s.sid = a.sid;