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