<jupyter_start><jupyter_text>NOTE:-----Please run the below cells first before proceeding- you'll need them soon!<jupyter_code>%load_ext sql
%sql sqlite://
%%sql
DROP TABLE IF EXISTS Movies;
CREATE TABLE Movies(title VARCHAR(50), year INT, director VARCHAR(50), length INT);
INSERT INTO Movies VALUES('Database Wars', 1967, '<NAME>', 123);
INSERT INTO Movies VALUES('The Databaser', 1992, '<NAME>', 190);
INSERT INTO Movies VALUES('Database Wars', 1998, '<NAME>', 176);
%sql DROP TABLE IF EXISTS A; DROP TABLE IF EXISTS B;
%sql CREATE TABLE A (x int, y int); CREATE TABLE B (x int, y int);
for i in range(1,6):
    %sql INSERT INTO A VALUES (:i, :i+1)
for i in range(1,11,3):
    %sql INSERT INTO B VALUES (:i, :i+2)<jupyter_output>Done.
Done.
Done.
Done.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.<jupyter_text>Activity 3-1:------------ORDER BY semantics, set operators & nested queries<jupyter_code>%sql SELECT * FROM movies<jupyter_output>Done.<jupyter_text>Exercise 1-----------**Can you write the movie query from lecture as a single SFW query?**Recall that we are trying to find **all movie titles that were used for more than one movie.** You may assume that no two movies in the same year have the same title. Our schema for the `movies` table is:> * title STRING> * year INT> * director STRING> * length INTLet's try to write the nested query that solves this from lecture:<jupyter_code>%%sql
SELECT m.title 
FROM Movies m
WHERE m.year <> ANY(SELECT year FROM Movie WHERE title = m.title);<jupyter_output>(sqlite3.OperationalError) near "SELECT": syntax error [SQL: u'SELECT m.title \nFROM Movies m\nWHERE m.year <> ANY(SELECT year FROM Movie WHERE title = m.title);']<jupyter_text>What?  This doesn't work?  Why?**ANY doesn't exist in SQLite!**  Can we do this query without nesting?  Write your query here:<jupyter_code>%%sql
SELECT DISTINCT a.title
FROM Movies a, Movies b 
WHERE a.title = b.title AND a.year <> b.year;<jupyter_output>Done.<jupyter_text>Exercise 2--------------------Consider the two relations $A$ and $B$ below:<jupyter_code>%sql SELECT * FROM A;
%sql SELECT * FROM B;<jupyter_output>Done.<jupyter_text>Assuming no duplicates, can you write an `INTERSECT` query, **just over the $x$ attribute**, without using `INTERSECT` OR nested queries?  Write your query here:<jupyter_code>%%sql
SELECT A.x FROM A, B Where A.x = B.x<jupyter_output>Done.<jupyter_text>What is this operation called?Next, using set operators again as well, can you return all the _full_ tuples in $A$ and $B$ that overlap in $x$ attributes?  Write your query here:<jupyter_code>%%sql
SELECT x, y FROM (
    SELECT A.x, A.y FROM A, B WHERE A.x = B.x
    UNION
    SELECT B.x, B.y FROM A, B WHERE A.x = B.x
);<jupyter_output>Done.