forked from leonardean/MovieDB
-
Notifications
You must be signed in to change notification settings - Fork 0
/
query.sql
executable file
·267 lines (265 loc) · 10.7 KB
/
query.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
--1.Find all the users who have a particular item in their collection
--(in out case, item name set to be 'MOVIENAME0')
SELECT USERNAME FROM
COLLECTION INNER JOIN USERS ON COLLECTION.USERID=USERS.USERID
INNER JOIN MOVIES ON COLLECTION.MOVIEID=MOVIES.MOVIEID
WHERE MOVIES.NAME='MOVIENAME0';
--2.Automatically update an item’s average rating whenever a user adds
--or updates their rating for that item
CREATE TRIGGER CHANGERATING
AFTER INSERT OR UPDATE OF RATING ON COLLECTION
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE MOVIES SET AVGRATING=(SELECT AVG(RATING) FROM COLLECTION WHERE COLLECTION.MOVIEID=:NEW.MOVIEID)
WHERE MOVIES.MOVIEID=:NEW.MOVIEID;
COMMIT;
END CHANGERATING;
/
CREATE OR REPLACE TRIGGER CHANGERATING
AFTER INSERT OR UPDATE OF RATING ON COLLECTION
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE MOVIES SET AVGRATING=(SELECT AVG(RATING) FROM COLLECTION WHERE COLLECTION.MOVIEID=:NEW.MOVIEID)
WHERE MOVIES.MOVIEID=:NEW.MOVIEID;
INSERT INTO RATINGS VALUES(:NEW.USERID,:NEW.MOVIEID,:NEW.RATING);
COMMIT;
END CHANGERATING;
/
--3.Change the database so that an item’s average ratings only appears
--if it has been rated by 10 or more users
--*************************
CREATE OR REPLACE TRIGGER APPEARRATING
AFTER INSERT OR UPDATE OF RATING ON COLLECTION
FOR EACH ROW
DECLARE
TMP INT;
BEGIN
SELECT COUNT(*) INTO TMP FROM RATINGS WHERE RATINGS.MOVIEID=:NEW.MOVIEID;
IF (TMP>=10) THEN
UPDATE MOVIES SET AVGRATING=(SELECT AVG(VALUE) FROM RATINGS WHERE RATINGS.MOVIEID=:NEW.MOVIEID)
WHERE MOVIES.MOVIEID=:NEW.MOVIEID;
ELSE
UPDATE MOVIES SET AVGRATING=NULL
WHERE MOVIES.MOVIEID=:NEW.MOVIEID;
END IF;
END CHANGERATING;
/
--4. At Christmas, give a present of 5 coins to all users
CREATE OR REPLACE PROCEDURE CHRISTMASPRESENT
IS
CURRENTDATE CHAR(20);
BEGIN
SELECT TO_CHAR(SYSDATE, 'DD-MM') INTO CURRENTDATE FROM DUAL;
IF CURRENTDATE='25-12' THEN
UPDATE ACCESSORY
SET COINS=COINS+3;
END IF;
END;
/
--5.CREATE LIST FOR EACH GENRE
SELECT GENRE, RANK, MOVIES.NAME
FROM GENRE, MOVIES
WHERE GENRE.MOVIEID = MOVIES.MOVIEID AND RANK <=10
ORDER BY GENRE, RANK;
--6.Allow the user to buy a year’s subscription with 500 coins of virtual cash
CREATE OR REPLACE PROCEDURE SUBSCRIBE (CURRENTUSERID IN NUMBER)
IS
CURRENTCOIN NUMBER;
CURRENTDATE DATE;
CURRENTPAIDUNTILDATE DATE;
INSUFFICIENT_COINS EXCEPTION;
BEGIN
SELECT COINS INTO CURRENTCOIN FROM ACCESSORY WHERE USERID=CURRENTUSERID;
SELECT PAIDUNTILDATE INTO CURRENTPAIDUNTILDATE FROM USERS WHERE USERID=CURRENTUSERID;
SELECT SYSDATE INTO CURRENTDATE FROM DUAL;
IF (CURRENTCOIN>=500) THEN
IF (CURRENTPAIDUNTILDATE=NULL OR CURRENTPAIDUNTILDATE<CURRENTDATE) THEN
UPDATE ACCESSORY SET COINS=COINS-500 WHERE USERID=CURRENTUSERID;
UPDATE USERS SET PAIDUNTILDATE=(SELECT ADD_MONTHS(CURRENTDATE,12) FROM DUAL) WHERE USERID=CURRENTUSERID;
ELSE
UPDATE ACCESSORY SET COINS=COINS-500 WHERE USERID=CURRENTUSERID;
UPDATE USERS SET PAIDUNTILDATE=(SELECT ADD_MONTHS(CURRENTPAIDUNTILDATE,12) FROM DUAL) WHERE USERID=CURRENTUSERID;
END IF;
ELSE
RAISE INSUFFICIENT_COINS;
END IF;
EXCEPTION
WHEN INSUFFICIENT_COINS THEN
DBMS_OUTPUT.PUT_LINE('insufficient coin');
END;
/
--7.Check new usernames against a list of obscene or offensive terms
--(if possible check for sub-strings as well as the whole name).
--If such a username exists, lock the account
CREATE OR REPLACE TRIGGER CHECKNAME
BEFORE INSERT ON USERS
FOR EACH ROW
DECLARE
CURRENTBADNAME BADNAMES%ROWTYPE;
CURSOR BD IS SELECT * FROM BADNAMES;
BEGIN
OPEN BD; LOOP
FETCH BD INTO CURRENTBADNAME;
EXIT WHEN BD%NOTFOUND;
IF(INSTR(:NEW.USERNAME, CURRENTBADNAME.BADNAME) !=0) THEN
:NEW.LOCKED:='Y';
END IF;
END LOOP;
CLOSE BD;
END CHECKNAME;
/
--9.The client wants to allow users to send private messages to one another.
--What additional fields/tables would you need to add to allow for this functionality?
--TO SEND A MESSAGE
CREATE OR REPLACE PROCEDURE SEND(SENDER IN NUMBER, RECEIVER IN NUMBER, TEXT IN VARCHAR2)
IS
CURRENTTIME TIMESTAMP;
BEGIN
SELECT CURRENT_TIMESTAMP INTO CURRENTTIME FROM DUAL;
INSERT INTO MESSAGE VALUES(
TEXT, SENDER, RECEIVER, CURRENTTIME, 'N'
);
END;
/
--CHECK MESSAGES
CREATE OR REPLACE PROCEDURE VIEWing(MESSAGES OUT VARCHAR2, RECEIVERID IN NUMBER, POSITION IN NUMBER)
IS
BEGIN
SELECT * FROM (SELECT TEXT FROM MESSAGE WHERE USERID=RECEIVER ORDER BY SENDTIME DESC) WHERE ROWNUM=POSITION;
UPDATE MESSAGE SET READ='Y' WHERE RECEIVER=RECEIVERID;
END;
/
--CHECK NOTIFICATION
CREATE OR REPLACE PROCEDURE NOTIFY
(RECEIVERID IN NUMBER, NOTIFICATION OUT NUMBER)
IS
BEGIN
SELECT COUNT(*) INTO NOTIFICATION FROM MESSAGE WHERE USERID=RECEIVER AND READ='N';
END;
/
--10.Try to write three more queries that really show off your database
--select the best three movies Woody Allen has directed after 1999-07-24
SELECT *FROM
(SELECT NAME FROM MOVIES
WHERE RELEASEDATE > TO_DATE('1999-07-24', 'YYYY-MM-DD') and DIRECTOR IN (
SELECT ACTORID
FROM ACTORS
WHERE NAME = 'Woody Allen')
ORDER BY AVGRATING desc)
WHERE rownum < 4;
--select all movies produced by the studio 'X' but without 'Mark Ruffalo' being in
--the casting list
SELECT MOVIES.NAME
FROM CASTING
INNER JOIN MOVIES ON MOVIES.MOVIEID = CASTING.MOVIEID
INNER JOIN ACTORS ON CASTING.ACTORID = ACTORS.ACTORID
where studio = 'X'
MINUS
SELECT MOVIES.NAME
FROM CASTING
INNER JOIN MOVIES ON MOVIES.MOVIEID = CASTING.MOVIEID
INNER JOIN ACTORS ON CASTING.ACTORID = ACTORS.ACTORID
where ACTORS.name = 'Mark Ruffalo';
--select all actors who has co-operated with director 'Paul Thomas Anderson'
select actors.name
FROM CASTING
INNER JOIN MOVIES ON MOVIES.MOVIEID = CASTING.MOVIEID
INNER JOIN ACTORS ON CASTING.ACTORID = ACTORS.ACTORID
where movies.movieid in(
select movies.movieid
FROM CASTING
INNER JOIN MOVIES ON MOVIES.MOVIEID = CASTING.MOVIEID
INNER JOIN ACTORS ON CASTING.ACTORID = ACTORS.ACTORID
where movies.director in (
select actors.actorid
from actors
where actors.name = 'Paul Thomas Anderson'
)
)
--11. ADD TITLE TO THE PROFILE
ALTER TABLE USERS ADD TITLE VARCHAR(20) DEFAULT 'NEW USER';
CREATE OR REPLACE TRIGGER TITLECHANGE
BEFORE INSERT ON POST
FOR EACH ROW
DECLARE
POSTCOUNT NUMBER;
BEGIN
SELECT COUNT(*)+1 INTO POSTCOUNT FROM POST WHERE POST.USERNAME=:NEW.USERNAME;
IF (POSTCOUNT<=10) THEN
UPDATE USERS SET TITLE='NEW USER' WHERE USERS.USERNAME=:NEW.USERNAME;
ELSIF (POSTCOUNT>10 AND POSTCOUNT<=30) THEN
UPDATE USERS SET TITLE='CADET' WHERE USERS.USERNAME=:NEW.USERNAME;
ELSIF (POSTCOUNT>30 AND POSTCOUNT<=70) THEN
UPDATE USERS SET TITLE='PRO' WHERE USERS.USERNAME=:NEW.USERNAME;
ELSIF (POSTCOUNT>70 AND POSTCOUNT<=130) THEN
UPDATE USERS SET TITLE='ELITE' WHERE USERS.USERNAME=:NEW.USERNAME;
ELSE
UPDATE USERS SET TITLE='MASTER' WHERE USERS.USERNAME=:NEW.USERNAME;
END IF;
END TITLECHANGE;
/
--15 The client wants users to be able to submit new items to the database
--SO FAR ONLY WORKS WHEN THERE IS ONE ENTRY IN VALIDATE TABLE...
--Triggered by moderator changing value of VALID field
CREATE TRIGGER confirm
AFTER UPDATE OF VALID ON VALIDATEMOVIE
BEGIN
INSERT INTO MOVIES(MOVIEID, NAME, DESCRIPTION, RELEASEDATE, STUDIO, DIRECTOR, AGERATING, IMAGE, WEBSITE, RUNNINGTIME)
VALUES(
(SELECT COUNT(*) FROM(MOVIES)) +1, -- Automatically assignes new MOVIEID number
(SELECT MIN (NAME) FROM (SELECT NAME FROM VALIDATEMOVIE WHERE VALID = 'Y')),
(SELECT MIN (DESCRIPTION) FROM (SELECT DESCRIPTION FROM VALIDATEMOVIE WHERE VALID = 'Y')),
(SELECT MIN (RELEASEDATE) FROM (SELECT RELEASEDATE FROM VALIDATEMOVIE WHERE VALID = 'Y')),
(SELECT MIN (STUDIO) FROM (SELECT STUDIO FROM VALIDATEMOVIE WHERE VALID = 'Y')),
(SELECT MIN (DIRECTOR) FROM (SELECT DIRECTOR FROM VALIDATEMOVIE WHERE VALID = 'Y')),
(SELECT MIN (AGERATING) FROM (SELECT AGERATING FROM VALIDATEMOVIE WHERE VALID = 'Y')),
(SELECT MIN (IMAGE) FROM (SELECT IMAGE FROM VALIDATEMOVIE WHERE VALID = 'Y')),
(SELECT MIN (WEBSITE) FROM (SELECT WEBSITE FROM VALIDATEMOVIE WHERE VALID = 'Y')),
(SELECT MIN (RUNNINGTIME) FROM (SELECT RUNNINGTIME FROM VALIDATEMOVIE WHERE VALID = 'Y')));
DELETE FROM VALIDATEMOVIE WHERE NAME = (SELECT NAME FROM VALIDATEMOVIE);
END confirm;
/
--16 Suggest another user as a friend if their ratings are similar to the user’s
--Note that USERID is hard-coded at the moment
SELECT USERS.USERID
FROM USERS, MOVIES, COLLECTION
WHERE USERS.USERID <> 1 AND COLLECTION.RATING > 6
AND COLLECTION.USERID = USERS.USERID
AND COLLECTION.MOVIEID = MOVIES.MOVIEID
AND MOVIES.MOVIEID IN -- Other users who also rate these fave films highly
(SELECT MOVIES.MOVIEID
FROM USERS, MOVIES, COLLECTION
WHERE USERS.USERID = 1 AND COLLECTION.RATING > 6
AND COLLECTION.USERID = USERS.USERID
AND COLLECTION.MOVIEID = MOVIES.MOVIEID) -- Films that this user rates highly
GROUP BY USERS.USERID
HAVING COUNT(USERS.USERID) > (SELECT COUNT(*) -- The user's highly-rated films
FROM (SELECT MOVIES.MOVIEID
FROM USERS, MOVIES, COLLECTION
WHERE USERS.USERID = 1 AND COLLECTION.RATING > 6
AND COLLECTION.USERID = USERS.USERID
AND COLLECTION.MOVIEID = MOVIES.MOVIEID))
/ -- Calculates how many movies is 70% of the user's h--Note that USERID is hard-coded at the momentighly-rated films
(SELECT COUNT(*) -- TOTAL number of movies rated
FROM (SELECT MOVIES.MOVIEID
FROM USERS, MOVIES, COLLECTION
WHERE USERS.USERID = 1
AND COLLECTION.USERID = USERS.USERID
AND COLLECTION.MOVIEID = MOVIES.MOVIEID));
--17 Automatically suggest an item which a user might be interested in.
SELECT MOVIES.NAME
FROM USERS, MOVIES, COLLECTION
WHERE USERS.USERID = 3 AND COLLECTION.USERID = USERS.USERID AND COLLECTION.MOVIEID = MOVIES.MOVIEID AND COLLECTION.OWN = 'N' AND COLLECTION.WISHLIST = 'N' AND COLLECTION.WATCHED = 'N' AND MOVIES.MOVIEID IN -- Films that have not been watched/owned/already in wish list
(SELECT DISTINCT MOVIES.MOVIEID
FROM USERS, MOVIES, COLLECTION
WHERE COLLECTION.RATING > 6 AND COLLECTION.USERID = USERS.USERID AND COLLECTION.MOVIEID = MOVIES.MOVIEID AND USERS.USERID IN -- other films that these users like
(SELECT DISTINCT USERS.USERID
FROM USERS, MOVIES, COLLECTION
WHERE COLLECTION.RATING > 6 AND COLLECTION.USERID = USERS.USERID AND COLLECTION.MOVIEID = MOVIES.MOVIEID AND MOVIES.MOVIEID IN -- Other users who also rate these fave films highly
(SELECT MOVIES.MOVIEID
FROM USERS, MOVIES, COLLECTION
WHERE USERS.USERID = 3 AND COLLECTION.RATING > 6 AND COLLECTION.USERID = USERS.USERID AND COLLECTION.MOVIEID = MOVIES.MOVIEID)));