------------------------------------------------------------------------
-- SQL EXAMPLES                                                       --
-- Introduction to Databases, IT University of Copenhagen, Fall 2004  --
-- Taught by Rasmus Pagh                                              --
-- September 10, 2004                                                 --
------------------------------------------------------------------------

-- Projection examples

SELECT *
FROM Movie
WHERE studioName = 'Disney' AND year = 1990;

SELECT title, length
FROM Movie
WHERE studioName = 'Disney' AND year = 1990;

SELECT title AS name, length AS duration
FROM Movie
WHERE studioName = 'Disney' AND year = 1990;

SELECT title AS name, length/60 AS lengthInHours
FROM Movie
WHERE studioName = 'Disney' AND year = 1990;

SELECT title AS name, length/60 AS length, 'hours' AS inHours
FROM Movie
WHERE studioName = 'Disney' AND year = 1990;

SELECT title, length/60 length, 'hours'
FROM Movie
WHERE studioName = 'Disney' AND year = 1990;


-- Selection examples

SELECT title
FROM Movie
WHERE year > 1970 AND NOT (inColor=1);

SELECT title
FROM Movie
WHERE (year > 1970 OR length < 90) AND studioName = 'MGM';

SELECT title
FROM Movie
WHERE year > 1970 OR length < 90 AND studioName = 'MGM';

SELECT title
FROM Movie
WHERE title LIKE 'Star ____';

SELECT title
FROM Movie
WHERE title LIKE 'Star %';

SELECT title
FROM Movie
WHERE title LIKE '%''s%';


-- Join examples

SELECT name
FROM Movie, MovieExec
WHERE title = 'Star Wars' AND producerC = cert;

SELECT name
FROM Movie, MovieExec
WHERE title = 'Star Wars' AND producerC > cert;

SELECT MovieStar.name AS MovieStar, MovieExec.name AS MovieExec
FROM MovieStar, MovieExec
WHERE MovieStar.address = MovieExec.address;

SELECT Ships.name, displacement, numGuns
FROM Ships, Classes, Outcomes
WHERE (Outcomes.ship = Ships.name) AND (Classes.class = Ships.class);

SELECT Star1.name AS Star1, Star2.name AS Star2
FROM MovieStar Star1, MovieStar Star2
WHERE Star1.address = Star2.address
      AND Star1.name < Star2.name;


-- Examples for database modification

INSERT INTO StarsIn(movieTitle, starName, movieYear)
VALUES ('The Maltese Falcon', 'Sydney Greenstreet', 1942);

DELETE FROM StarsIn
WHERE starName = 'Sydney Greenstreet';

INSERT INTO StarsIn
VALUES ('The Maltese Falcon', 1942, 'Sydney Greenstreet');

UPDATE MovieExec
SET netWorth = netWorth * 1000000
WHERE netWorth < 1000000;

CREATE TABLE DinnerTable(
  host VARCHAR(30),
  food VARCHAR(30),
  seats INT,
  occupiedSeats INT
);

DROP TABLE DinnerTable;


-- Examples for NULL

SELECT a
FROM NullExample
WHERE a = 10;

SELECT a
FROM NullExample
WHERE NOT (a = 10);

SELECT *
FROM Movie
WHERE length <= 120 OR length >120;