DROP DATABASE IF EXISTS videoteca; 
CREATE DATABASE videoteca;


use videoteca;

DROP TABLE IF EXISTS pelicula;
CREATE TABLE pelicula(
  id_pelicula INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  titulo VARCHAR(64) NOT NULL,
  director VARCHAR(128) NOT NULL,
  actor VARCHAR(128) NOT NULL
);

describe pelicula;


DROP TABLE IF EXISTS clientes;
CREATE TABLE clientes(
  id_cliente INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  cliente VARCHAR(64) NOT NULL
);


describe clientes;

DROP TABLE IF EXISTS rentas;
CREATE TABLE rentas(
  fecha_inicio date NOT NULL,
  fecha_fin date NOT NULL,
  id_cliente INT NOT NULL, 
  id_pelicula INT NOT NULL,
FOREIGN KEY(id_cliente) REFERENCES clientes(id_cliente) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(id_pelicula) REFERENCES pelicula(id_pelicula) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY(id_cliente,id_pelicula, fecha_inicio)

);

describe rentas;

DROP table rentas;

DROP TABLE IF EXISTS rentas;
CREATE TABLE rentas(
  fecha_inicio date NOT NULL,
  fecha_fin date NOT NULL,
  id_cliente INT NOT NULL, 
  id_pelicula INT NOT NULL,
FOREIGN KEY(id_cliente) REFERENCES clientes(id_cliente) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY(id_pelicula) REFERENCES pelicula(id_pelicula) ON DELETE CASCADE ON UPDATE CASCADE, 
PRIMARY KEY(id_cliente,id_pelicula,fecha_inicio)
);

describe clientes;

ALTER TABLE clientes ADD COLUMN year  INT(4) ;

describe pelicula;

ALTER TABLE pelicula ADD COLUMN categoria  Varchar(20) ;

describe pelicula;
ALTER TABLE pelicula DROP COLUMN categoria;
describe pelicula;

INSERT INTO pelicula (titulo, director, actor) VALUES( 'Blade Runner', 'Ridley Scott', 'Harrison Ford' ); 

INSERT INTO pelicula (titulo, director, actor) VALUES( 'Alien', 'Ridley Scott', 'Sigourney Weaver' ); 

INSERT INTO pelicula (titulo, director, actor) VALUES( 'Doce monos', 'Terry Gilliam', 'Bruce Willis' ); 

INSERT INTO pelicula (titulo, director, actor) VALUES( 'Contact', 'Robert Zemeckis', 'Jodie Foster' ); 

INSERT INTO pelicula (titulo, director, actor) VALUES( 'Tron', 'Steven Lisberger', 'Jeff Bridges' ); 

INSERT INTO pelicula (titulo, director, actor) VALUES( 'La guerra de las galaxias', 'George Lucas', 'Harrison Ford' ); 

INSERT INTO pelicula (titulo, director, actor) 
VALUES( 'Los vengadores', 'Lucas', 'Ford' );

 SELECT * FROM pelicula; 


INSERT INTO clientes (cliente, year) VALUES( 'Jorge Perez', 1980); 
INSERT INTO clientes (cliente, year) VALUES( 'Juan Dominguez', 1950);
INSERT INTO clientes (cliente, year) VALUES( 'Jose Luis Lopez', 1967); 

 
SELECT * FROM clientes; 

SELECT * FROM clientes WHERE year<1980;


INSERT INTO rentas (id_cliente, id_pelicula, fecha_inicio,fecha_fin) select clientes.id_cliente,pelicula.id_pelicula,CURDATE(),CURDATE()+2 from clientes,pelicula where clientes.cliente='Jorge Perez' and pelicula.titulo='Tron'; 
INSERT INTO rentas (id_cliente, id_pelicula, fecha_inicio,fecha_fin) select clientes.id_cliente,pelicula.id_pelicula,CURDATE(),CURDATE()+2 from clientes,pelicula where clientes.cliente='Jorge Perez' and pelicula.titulo='Doce monos';
INSERT INTO rentas (id_cliente, id_pelicula, fecha_inicio,fecha_fin) select clientes.id_cliente,pelicula.id_pelicula,CURDATE(),CURDATE()+2 from clientes,pelicula where clientes.cliente='Jorge Perez' and pelicula.titulo='Contact';
INSERT INTO rentas (id_cliente, id_pelicula, fecha_inicio,fecha_fin) select clientes.id_cliente,pelicula.id_pelicula,CURDATE(),CURDATE()+2 from clientes,pelicula where clientes.cliente='Juan Dominguez' and pelicula.titulo='Contact';

 
select * from rentas;

SELECT * FROM pelicula WHERE director='Ridley Scott'; 


SELECT titulo FROM pelicula WHERE director='Ridley Scott� 
ORDER BY titulo; 

select titulo from pelicula;

UPDATE pelicula 
SET titulo='Star Wars' 
WHERE titulo='La guerra de las galaxias'; 

select titulo from pelicula;

select * from pelicula;

//DELETE  FROM pelicula where director=�Ridley Scott�;

//select * from pelicula;

//ALTER TABLE pelicula ADD COLUMN ranking  int(4);
//update pelicula set ranking=30 where id_pelicula=2;