from library import sqlQuery
#This file assumes the views have already been created
print sqlQuery("""
UPDATE CurrentReserves SET title = '????' WHERE 200 < iid AND iid < 400
""")
from library import sqlQuery
from library import *
try: # Drop procedure if it exists
sqlQuery('DROP PROCEDURE addFines')
except: # It didn't exist
pass
# Now define it
sqlQuery("""
CREATE PROCEDURE addFines(IN fine INTEGER) BEGIN
DECLARE myPnid INT;
DECLARE myDate DATE;
DECLARE done INT DEFAULT FALSE;
DECLARE pnids CURSOR FOR SELECT pnid, `to` FROM members NATURAL JOIN reserved_by WHERE type = "borrowed" AND `to` < CURRENT_DATE;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN pnids;
read_loop: LOOP
FETCH pnids INTO myPnid, myDate;
IF done THEN
LEAVE read_loop;
END IF;
IF EXISTS(SELECT 1 FROM employees WHERE pnid = myPnid) THEN
UPDATE employees SET salary = salary - fine * (CURRENT_DATE - myDate) WHERE pnid = myPnid LIMIT 1;
ELSE
UPDATE members SET balance = balance + fine * (CURRENT_DATE - myDate) WHERE pnid = myPnid LIMIT 1;
END IF;
END LOOP;
CLOSE pnids;
END
""")
from library import sqlQuery
print sqlQuery("""
DELETE FROM has_languages where iid = (SELECT iid FROM items WHERE title = "An Apache's Gratitude")
""")
print sqlQuery("""
INSERT INTO has_languages (`iid`,`type`,`iso`) VALUES (
(SELECT iid FROM video NATURAL JOIN items WHERE items.title = "An Apache's Gratitude"),
'subtitled',
(SELECT iso FROM languages WHERE english='French')
)
""")
print sqlQuery("""
UPDATE members SET balance = '0' WHERE standing = 'good';
""")
print sqlQuery("""
DELETE FROM reserved_by WHERE reserved_by.to < CURRENT_TIMESTAMP
""")
from library import sqlQuery
# This file assumes the views have already been created
print sqlQuery("""
INSERT INTO CurrentReserves(iid, instid, title, pnid) values(900, 200, 'Wuthering Heights', 999)
""")
print sqlQuery("""
INSERT INTO OnlyOneInstance(iid, title) values(669, ‘Sunset Boulevard’)
""")
from library import sqlQuery
print sqlQuery("""
-- All actors that have acted in 10 movies or more
-- Useful to know which actors are popular
SELECT actors.name, COUNT(actors.name) AS numberOfMovies FROM actors
NATURAL JOIN has_actors
GROUP BY actors.name
HAVING COUNT(actors.name) >= 10
""")
print sqlQuery("""
-- Movies available in both english (spoken) and french (spoken) released in 1913
-- Useful if we're looking for content in certain languages to share with a household where some people speak french and some english,
-- while looking for a particular period
SELECT iid, title FROM items NATURAL JOIN video
WHERE `date` >= '1913-01-01' AND `date` <= '1913-12-31'
AND iid IN (
SELECT iid FROM has_languages WHERE type = 'spoken' AND iso = 'en'
) AND iid IN (
SELECT iid FROM has_languages WHERE type = 'spoken' AND iso = 'fr'
)
""")
print sqlQuery("""
-- List members who have not given back their items before the end of their reservation date, and are already in bad standing.
-- Useful when wanting to see the list of people whose membership should probably be ended.
SELECT members.pnid, name, email, balance FROM members NATURAL JOIN people WHERE standing = 'bad' AND EXISTS (
SELECT 1 FROM reserved_by
WHERE reserved_by.pnid = members.pnid
AND `to` < CURRENT_DATE
