-- Database: VideoClub

 

-- -----------------------------------------------------

-- Table customer

-- -----------------------------------------------------

DROP TABLE IF EXISTS movie cascade ;

DROP TABLE IF EXISTS actor cascade ;

DROP TABLE IF EXISTS plays cascade ;

 

-- -----------------------------------------------------

-- Table movie

-- -----------------------------------------------------

CREATE TABLE movie(

title TEXT NOT NULL,

production_year INT NOT NULL,

duration INT,

colour_type TEXT,

PRIMARY KEY (title));

 

-- -----------------------------------------------------

-- Insert Data Into Table movie

-- -----------------------------------------------------

 

INSERT INTO movie VALUES

('District 9',2009, 112, 'Colour'),

('Moon',2009, 97,'Black and White'),

('Surrogates',2009, 88, 'Black and White'),

('Star Trek',2009, 127, 'Colour'),

('V for Vendetta',2005, 132, 'Colour'),

('The Truman Show',1998, 103, 'Colour'),

('The Star Wars',1977, 121, 'Colour'),

('Blade Runner',1982, 117, 'Colour'),

('Terminator 2: Judgment Day',1991, 137, 'Colour');

 

-- -----------------------------------------------------

-- Table actor

-- -----------------------------------------------------

 

CREATE TABLE actor(

ActorName TEXT NOT NULL,

Address TEXT NOT NULL,

BirthYear INT NOT NULL,

CoupleName TEXT,

PRIMARY KEY (ActorName));

 

-- -----------------------------------------------------

-- Insert Data Into Table actor

-- -----------------------------------------------------

 

INSERT INTO actor VALUES

('Sharlto Copley', 'USA', 1966, NULL),

('Jason Cope', 'UK', 1955, 'Sharlto Copley'),

('Sam Rockwell','UK', 1975, NULL),

('Kevin Spacey', 'USA', 1945, NULL),

('Bruce Willis', 'USA', 1945, NULL),

('Chris Pine', 'USA', 1945, NULL),

('Zachary Quinto', 'USA', 1945, NULL),

('Leonard Nimoy', 'USA', 1945, NULL),

('Peter Cushing', 'USA', 1945, NULL);

 

 

-- -----------------------------------------------------

-- Table plays

-- -----------------------------------------------------

 

CREATE TABLE plays(

Actor TEXT NOT NULL,

Movie TEXT NOT NULL,

Etos INT NOT NULL,

PRIMARY KEY (Actor, Movie),

FOREIGN KEY (Actor) REFERENCES actor(ActorName),

FOREIGN KEY (Movie) REFERENCES movie(title));

 

 

-- -----------------------------------------------------

-- Insert Data Into Table plays

-- -----------------------------------------------------

 

INSERT INTO plays VALUES

('Sharlto Copley', 'District 9', 2009),

('Sharlto Copley','Moon', 2009),

('Bruce Willis','V for Vendetta', 2009),

('Bruce Willis', 'District 9', 2010),

('Jason Cope','V for Vendetta', 2005),

('Jason Cope','Surrogates', 2009);

 

/*

Τα παραδείγματα απο τα slides

*/ 

 

-- Προβολή γνωρίσματος πίνακα 

select  Actor

from    plays;

 

-- Προβολή γνωρίσματος πίνακα υπό συνθήκη 

select Actor

from   plays

where  Movie = 'V for Vendetta';

 

-- Διαγραφή διπλότιμων

select distinct Actor

from plays;

 

-- Πράξεις στο select

select title, production_year, duration / 60, colour_type

from  movie;

 

-- Επιλογή με συνθήκη 

select title 

from movie

where production_year > 1000 AND 

 

-- Χρήση του BETWEEN

select title  

from movie

where production_year between 2000 and 2020;

 

-- Ανισότητα στη συνθήκη επιλογής  

select title 

from movie

where production_year >= 2000  and 

 

-- Σύγκριση με συμβολοσειρά

select  distinct  title

from movie 

where  title like  '%The%';

 

-- Χρήση του order by

select distinct  Movie, Etos 

from plays

where Actor = 'Bruce Willis'

order by Etos;

 

-- Διάταξη πλειάδων 

select *

from movie

order by production_year desc, title asc;

 

-- Joins

-- Καρτεσιανό γίνομενο με συνθήκη σύνδεσης πινάκων και συνθήκη επιλογής 

select Actor

from   plays, movie

where  plays.Movie = movie.title AND

       movie.production_year= plays.Etos AND

 

-- Καρτεσιανό γίνομενο με συνθήκη σύνδεσης πινάκων, συνθήκη επιλογής

-- και διαγραφή διπλοτιμων 

select  distinct  Actor

from  plays, movie

where plays.movie = movie.title and 

 

-- Καρτεσιανό γίνομενο με renaming

select  distinct  Actor

from  plays as P, movie as M

where P.movie = M.title and 

 

-- Nested query

select  distinct  ActorName

from  plays as P, actor as A

where P.Actor = A.ActorName AND 

 

-- Join τριών πινάκων

select  distinct  ActorName

from  plays as P, actor as A, movie as M

where P.Actor = A.ActorName AND 

 

-- Find the actor and the year of birth who have played in B&W movies 

-- and their address is either in the UK or the USA. In your answer you should include

-- the movie name

 

select  distinct  ActorName 

from  plays as P, actor as A, movie as M

where P.Actor = A.ActorName AND 

UNION

select  distinct  ActorName 

from  plays as P, actor as A, movie as M

where P.Actor = A.ActorName AND 

select  distinct  ActorName 

from  plays as P, actor as A, movie as M

where P.Actor = A.ActorName AND