erUN7bGM

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 plpgsql AS
26$$
27BEGIN
28    IF(IS_criteria(new) == TRUE) THEN
29        Update Applications
30        set decision = "Y"
31        WHERE sID = new.sID
32    ELSE
33        Update Applications
34        set decision = "Y"
35        WHERE sID = new.sID
36    END IF;
37    RETURN null;
38END
39$$
40-- Write a function that returns a boolean after testing
41-- admission criteria for CMU/IS
42
43-- Criteria:  a student is admited to CMU/IS if his/her
44--     1. high school GPA >= 3.7
45--     2. SAT score >= 1450  (by the new scoring system which has a max of 1600)
46--
47-- These are, of course, not real criteria used.
48--
49
50CREATE OR replace FUNCTION IS_criteria(app Applications) RETURNS boolean
51LANGUAGE plpgsql AS
52$$
53BEGIN
54    return (SELECT GPA >= 3.7 AND sat >= 1450 From Students Where Sid=app.sid);
55END
56$$
57
58DROP TRIGGER IF EXISTS tr_auto_accept_IS ON Applications;
59
60CREATE TRIGGER tr_auto_accept_IS AFTER INSERT ON Applications
61    FOR EACH ROW
62    EXECUTE PROCEDURE fn_auto_accept_IS();
63
64-- Write the actual trigger
65-- That will update the 'decision' field of Applications based on the
66-- result of 'fn_auto_accept_IS'.  If the function returns True the decision is 'Y'
67-- otherwise the decision of 'N'.
68
69-- Look up the boiler plate synax from the lab
70
71
72-- Insert student data
73
74insert into Students values (123, 'Jack', 3.9, 1000);
75insert into Students values (234, 'Jill', 3.9, 1500);
76insert into Students values (345, 'Pat', 3.9, 1450);
77insert into Students values (567, 'Bob', 3.8, 1550);
78
79-- For each insrt into Application the trigger should run
80
81insert into Applications values (123, 'CMU', 'IS');
82insert into Applications values (234, 'CMU', 'IS');
83insert into Applications values (234, 'Stanford', 'CS');
84insert into Applications values (345, 'CMU', 'BZxx');
85insert into Applications values (345, 'CMU', 'IS');
86insert into Applications values (345, 'Berkeley', 'EECS');
87insert into Applications values (567, 'CMU', 'IS');
88
89-- checking the output
90
91SELECT s.sid, s.sname, s.gpa, s.sat, a.cname, a.major, a.decision
92  FROM Applications AS a
93  JOIN Students AS s
94    ON  s.sid = a.sid;