######################################################################
# SQL EXAMPLES                                                       #
# Introduction to Databases, IT University of Copenhagen, Fall 2003  #
# Taught by Rasmus Pagh                                              #
# September 2, 2003                                                  #
######################################################################

# 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;

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%';

SELECT a,b, a AND b, a OR b, NOT a
FROM example;

SELECT a,b, a AND b, a OR b, NOT a
FROM example 
WHERE a AND b;


# 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 
# Note: When several attributes in the SELECT part have the same name,
#       MySQL needs explicit names for them (using AS after each attribute)

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 CHAR(30),
  food CHAR(30),
  seats INT,
  occupiedSeats INT
);

DROP TABLE DinnerTable;


# Examples for NULL

SELECT a, a = 10
FROM NullExample;

SELECT a, a = 10
FROM NullExample
WHERE a = 10;

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

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

SELECT a,b, a AND b, a OR b, NOT a
FROM BooleanNullExample;
# Note: MySQL does not seem to evaluate (0 AND NULL) according to the SQL99 standard.