· 4 years ago · Jan 19, 2021, 08:44 AM
1DROP TRIGGER IF EXISTS Song_Insert_Trigger ON Song;
2DROP TABLE IF EXISTS Song CASCADE;
3DROP TABLE IF EXISTS Album CASCADE;
4DROP TABLE IF EXISTS Artist CASCADE;
5DROP TABLE IF EXISTS Grammy CASCADE;
6
7CREATE TABLE Song (
8 SongId VARCHAR(64),
9 SongName VARCHAR(128),
10 AlbumId VARCHAR(64),
11 Genre VARCHAR(64),
12 Views INT
13);
14
15CREATE TABLE Album (
16 AlbumId VARCHAR(64),
17 AlbumName VARCHAR(128),
18 ArtistId VARCHAR(64),
19 Release_Date DATE,
20 Sales INT
21);
22
23CREATE TABLE Artist (
24 ArtistId VARCHAR(64),
25 ArtistName VARCHAR(128),
26 Country VARCHAR(64)
27);
28
29CREATE TABLE Grammy (
30 Year INT,
31 SongId VARCHAR(64)
32);
33
34
35-- ## 1 ##
36ALTER TABLE Song ADD CONSTRAINT SongId_fmt CHECK (SongId SIMILAR TO 'ID[0-9]*');
37ALTER TABLE Album ADD CONSTRAINT AlbumId_fmt CHECK (AlbumId SIMILAR TO 'AL[0-9]*');
38ALTER TABLE Artist ADD CONSTRAINT ArtistId_fmt CHECK (ArtistId SIMILAR TO 'AR[0-9]*');
39
40INSERT INTO Song VALUES('ID001', 'Panic', 'AL004', 'Pop', 650);
41INSERT INTO Song VALUES('ID002', 'Dignity', 'AL001', 'Rap', 1900);
42INSERT INTO Song VALUES('ID003', 'Expressed', 'AL002', 'Pop', 1500);
43INSERT INTO Song VALUES('ID004', 'Science', 'AL005', 'Rock', 1920);
44INSERT INTO Song VALUES('ID005', 'Illusion', 'AL003', 'Rock', 760);
45INSERT INTO Song VALUES('ID006', 'Bomber', 'AL002', 'Rap', 2000);
46INSERT INTO Song VALUES('ID007', 'Agenda', 'AL004', 'Jazz', 1260);
47INSERT INTO Song VALUES('ID008', 'River', 'AL002', 'Pop', 920);
48
49INSERT INTO Album VALUES('AL001', 'Jurisdiction', 'AR004', '2004-03-16', 570);
50INSERT INTO Album VALUES('AL002', 'Limitless', 'AR003', '2007-05-24', 650);
51INSERT INTO Album VALUES('AL003', '69 cents', 'AR001', '2005-09-06', 400);
52INSERT INTO Album VALUES('AL004', 'Confession', 'AR002', '2012-11-28', 120);
53INSERT INTO Album VALUES('AL005', 'Hero', 'AR004', '2002-07-07', 900);
54
55INSERT INTO Artist VALUES('AR001', 'Natalia', 'France');
56INSERT INTO Artist VALUES('AR002', 'Suman', 'India');
57INSERT INTO Artist VALUES('AR003', 'Hill', 'USA');
58INSERT INTO Artist VALUES('AR004', 'Sham', 'France');
59
60INSERT INTO Grammy VALUES(2003, 'ID004');
61INSERT INTO Grammy VALUES(2007, 'ID006');
62INSERT INTO Grammy VALUES(2009, 'ID003');
63INSERT INTO Grammy VALUES(2004, 'ID002');
64INSERT INTO Grammy VALUES(2012, 'ID007');
65
66
67-- ## 2 ##
68SELECT Artist.ArtistName, Song.*
69FROM (
70(Song INNER JOIN Album ON Song.AlbumId=Album.AlbumId)
71INNER JOIN Artist ON Album.ArtistId=Artist.ArtistId AND Artist.ArtistName='Hill');
72
73
74-- ## 3 ##
75SELECT Song.*
76FROM (
77(Song INNER JOIN Album ON Song.AlbumId=Album.AlbumId)
78INNER JOIN Song S2 ON Album.AlbumId=S2.AlbumId AND S2.SongName='Bomber');
79
80
81-- ## 4 ##
82SELECT SUM(Song.Views)
83FROM Song
84INNER JOIN Album ON Song.AlbumId=Album.AlbumID AND Album.Sales>350;
85
86
87-- ## 5 ##
88SELECT AlbumId, COUNT(SongId) AS "Songs Without Grammy"
89FROM (
90 SELECT AlbumId, SongId
91 FROM Song
92 EXCEPT
93 SELECT AlbumId, Song.SongId
94 FROM
95 Song INNER JOIN Grammy ON Grammy.SongId=Song.SongId
96) AS SongsWithoutGrammy
97GROUP BY AlbumID ORDER BY AlbumId;
98
99
100-- ## 6 ##
101CREATE OR REPLACE FUNCTION Song_Insert() RETURNS TRIGGER AS
102$$ BEGIN
103 if (NEW.Views > 1000) then
104 RAISE NOTICE 'Song % has more than 1000 views. Adding to Grammy Winners', NEW.SongName;
105 INSERT INTO Grammy VALUES(to_char(NOW(), 'YYYY')::INT, NEW.SongId);
106 end if;
107 RETURN NULL;
108END; $$ LANGUAGE plpgsql;
109
110CREATE TRIGGER Song_Insert_Trigger AFTER INSERT ON Song
111FOR EACH ROW EXECUTE PROCEDURE Song_Insert();
112
113
114-- ## 7 ##
115CREATE OR REPLACE VIEW "Grammy Winners" AS (
116 SELECT Song.SongName, Album.AlbumName, Artist.ArtistName, Grammy.Year
117 FROM Song
118 INNER JOIN Grammy ON Grammy.SongId=Song.SongId
119 INNER JOIN Album ON Album.AlbumId=Song.AlbumId
120 INNER JOIN Artist ON Artist.ArtistId=Album.ArtistId
121);
122
123
124-- ## 8 ##
125CREATE OR REPLACE FUNCTION INCR_SALES() RETURNS VOID AS $$
126DECLARE
127 Release_Year INT;
128 alb_rec RECORD;
129 alb_cur CURSOR FOR SELECT * FROM Album;
130BEGIN
131 OPEN alb_cur;
132 loop
133 fetch alb_cur into alb_rec;
134 exit when not found;
135 Release_Year := to_char(alb_rec.Release_Date, 'YYYY')::INT;
136 if Release_Year >= 2010 then
137 RAISE NOTICE 'Updating Sales of % released in %', alb_rec.AlbumName, Release_Year;
138 UPDATE Album SET Sales=1.05*Sales WHERE AlbumId=alb_rec.AlbumId;
139 end if;
140 end loop;
141END; $$ LANGUAGE PLPGSQL;
142
143-- call the function
144select INCR_SALES();
145
146
147-- ## 9 ##
148DROP SCHEMA IF EXISTS SS CASCADE;
149CREATE SCHEMA SS;
150
151CREATE OR REPLACE FUNCTION SS.multiplySales() RETURNS VOID
152LANGUAGE PLPGSQL AS $$
153DECLARE
154 count INT;
155 id VARCHAR(64);
156 alb_rec RECORD;
157 alb_cur CURSOR FOR SELECT * FROM Album;
158BEGIN
159 OPEN alb_cur;
160 loop
161 fetch alb_cur into alb_rec;
162 exit when not found;
163 id := alb_rec.AlbumId;
164 SELECT COUNT(Song.*) FROM (Song INNER JOIN Grammy ON Grammy.SongId=Song.SongId) WHERE Song.AlbumId=id INTO count;
165 if (count > 0) then
166 RAISE NOTICE 'Album % has % Grammies. Updating Sales', id, count;
167 UPDATE Album SET Sales=count*Sales WHERE AlbumId=id;
168 end if;
169 end loop;
170END; $$;
171
172CREATE OR REPLACE PROCEDURE SS.bestGenre(INOUT g RECORD)
173LANGUAGE PLPGSQL AS $$
174BEGIN
175 SELECT Song.Genre, COUNT(Song.*)
176 FROM Song INNER JOIN Grammy ON Song.SongId=Grammy.SongId
177 GROUP BY Song.Genre ORDER BY COUNT(Song.*) DESC LIMIT 1 INTO g;
178END; $$;
179
180DO $$
181DECLARE g RECORD;
182BEGIN
183 CALL SS.bestGenre(g);
184 RAISE NOTICE 'BEST GENRE: %', g;
185END; $$;
186
187SELECT
188
189