쿼리문 임시저장소

4/12

create table director
	(directorID		int not null,
	directorName	varchar(20) not null,
	dateOfBirth		varchar(10) not null,
	dateOfDeath		varchar(10),
	primary key(directorName));
	
select *
from director;

create table actor
	(actorID		int not null,
	actorName		varchar(20) not null,
	dateOfBirth		varchar(10) not null,
	dateOfDeath		varchar(10),
	gender			varchar(6) not null,
	primary key(actorName));
	
select *
from actor;

create table customer
	(customerID		int not null,
	customerName	varchar(20) not null,
	dateOfBirth		varchar(20) not null,
	gender			varchar(10) not null,
	primary key(customerName));
	
select *
from customer;

 

4/13

-- drop table director;
create table director
	(directorID		int not null,
	directorName	varchar(20) not null,
	dateOfBirth		varchar(10) not null,
	dateOfDeath		varchar(10),
	primary key(directorID));

insert into director values (1, 'Tim Burton', '1958.8.25', null);
insert into director values (2, 'David Fincher', '1962.8.28', null);
insert into director values (3, 'Christopher Nolan', '1970.7.30', null);

select *
from director;





-- drop table actor;
create table actor
	(actorID		int not null,
	actorName		varchar(20) not null,
	dateOfBirth		varchar(10) not null,
	dateOfDeath		varchar(10),
	gender			varchar(6) not null,
	primary key(actorID));
	
insert into actor values (1, 'Johnny Depp', '1963.6.9', null, 'Male');
insert into actor values (2, 'Winona Ryder', '1971.10.29', null, 'Female');
insert into actor values (3, 'Mia Wasikowska', '1989.10.14', null, 'Female');
insert into actor values (4, 'Christian Bale', '1974.1.30', null, 'Male');
insert into actor values (5, 'Heath Ledger', '1979.4.4', '2008.1.22', 'Male');
insert into actor values (6, 'Jesse Eisenberg', '1983.10.5', null, 'Male');
insert into actor values (7, 'Justin Timberlake', '1981.1.31', null, 'Male');
insert into actor values (8, 'Fionn Whitehead', '1997.7.18', null, 'Male');
insert into actor values (9, 'Tom Hardy', '1977.9.15', null, 'Male');

select *
from actor;





-- drop table customer;
create table customer
	(customerID		int not null,
	customerName	varchar(20) not null,
	dateOfBirth		varchar(20) not null,
	gender			varchar(10) not null,
	primary key(customerID));

insert into customer values (1, 'Ethan', '1997.11.14', 'Male');
insert into customer values (2, 'John', '1978.01.23', 'Male');
insert into customer values (3, 'Hayden', '1980.05.04', 'Female');
insert into customer values (4, 'Jill', '1981.04.17', 'Female');
insert into customer values (5, 'Bell', '1990.05.14', 'Female');

select *
from customer;





-- drop table movie;
create table movie
	(movieID		int not null,
	movieName		varchar(30) not null,
	releaseYear		varchar(4) not null,
	releaseMonth	varchar(2) not null,
	releaseDate		varchar(2) not null,
	publisherName	varchar(30) not null,
	avgRate			float default 0,
	primary key(movieID));
	
select *
from movie;
	
-- drop table award;
create table award
	(awardID		int not null,
	awardName		varchar(20) not null,
	primary key(awardID));
	
select *
from award;
	
-- drop table genre;
create table genre
	(genreName		varchar(20) not null,
	primary key(genreName));

select *
from genre;

-- drop table movieGenre;
create table movieGenre
	(movieID		int not null,
	genreName		varchar(20) not null,
	primary key(movieID, genreName),
	foreign key(movieID) references movie (movieID) on delete cascade
	foreign key(genreName) references genre (genreName) on delete cascade);

select *
from movieGenre;

 

4/14

create table director
	(directorID		int not null,
	directorName	varchar(20) not null,
	dateOfBirth		varchar(10) not null,
	dateOfDeath		varchar(10),
	primary key(directorID));

insert into director values (1, 'Tim Burton', '1958.8.25', null);
insert into director values (2, 'David Fincher', '1962.8.28', null);
insert into director values (3, 'Christopher Nolan', '1970.7.30', null);


create table actor
	(actorID		int not null,
	actorName		varchar(20) not null,
	dateOfBirth		varchar(10) not null,
	dateOfDeath		varchar(10),
	gender			varchar(6) not null,
	primary key(actorID));
	
insert into actor values (1, 'Johnny Depp', '1963.6.9', null, 'Male');
insert into actor values (2, 'Winona Ryder', '1971.10.29', null, 'Female');
insert into actor values (3, 'Mia Wasikowska', '1989.10.14', null, 'Female');
insert into actor values (4, 'Christian Bale', '1974.1.30', null, 'Male');
insert into actor values (5, 'Heath Ledger', '1979.4.4', '2008.1.22', 'Male');
insert into actor values (6, 'Jesse Eisenberg', '1983.10.5', null, 'Male');
insert into actor values (7, 'Justin Timberlake', '1981.1.31', null, 'Male');
insert into actor values (8, 'Fionn Whitehead', '1997.7.18', null, 'Male');
insert into actor values (9, 'Tom Hardy', '1977.9.15', null, 'Male');


create table customer
	(customerID		int not null,
	customerName	varchar(20) not null,
	dateOfBirth		varchar(20) not null,
	gender			varchar(10) not null,
	primary key(customerID));

insert into customer values (1, 'Ethan', '1997.11.14', 'Male');
insert into customer values (2, 'John', '1978.01.23', 'Male');
insert into customer values (3, 'Hayden', '1980.05.04', 'Female');
insert into customer values (4, 'Jill', '1981.04.17', 'Female');
insert into customer values (5, 'Bell', '1990.05.14', 'Female');







create table movie
	(movieID		int not null,
	movieName		varchar(30) not null,
	releaseYear		varchar(4) not null,
	releaseMonth	varchar(2) not null,
	releaseDate		varchar(2) not null,
	publisherName	varchar(30) not null,
	avgRate			numeric(2,1),
	primary key(movieID));


create table award
	(awardID		int not null,
	awardName		varchar(30) not null,
	primary key(awardID));


create table genre
	(genreName		varchar(20) not null,
	primary key(genreName));


create table movieGenre
	(movieID		int,
	genreName		varchar(20),
	primary key(movieID, genreName),
	foreign key(movieID) references movie (movieID) on delete set null,
	foreign key(genreName) references genre (genreName) on delete set null);
    

create table movieObtain
	(movieID		int,
	awardID			int,
	year			varchar(4),
	primary key(movieID, awardID),
	foreign key(movieID) references movie (movieID) on delete set null,
	foreign key(awardID) references award (awardID) on delete set null);
    

create table actorObtain
	(actorID		int,
	awardID			int,
	year			varchar(4),
	primary key(actorID, awardID),
	foreign key(actorID) references actor (actorID) on delete set null,
	foreign key(awardID) references award (awardID) on delete set null);
    

create table directorObtain
	(directorID		int,
	awardID			int,
	year			varchar(4),
	primary key(directorID, awardID),
	foreign key(directorID) references director (directorID) on delete set null,
	foreign key(awardID) references award (awardID) on delete set null);
    

create table cast
	(movieID		int,
	actorID			int,
	role			varchar(20),
	primary key(movieID, actorID),
	foreign key(movieID) references movie (movieID) on delete set null,
	foreign key(actorID) references actor (actorID) on delete set null);
    

create table make
	(movieID		int,
	directorID		int,
	primary key(movieID, directorID),
	foreign key(movieID) references movie (movieID) on delete set null,
	foreign key(directorID) references director (directorID) on delete set null);


create table customerRate
	(customerID		int,
	movieID			int,
	rate			int,
	primary key(customerID, movieID),
	foreign key(customerID) references customer (customerID) on delete set null,
	foreign key(movieID) references movie (movieID) on delete set null);
 





insert into award values((select count(*) from award)+1, 'Best suportting actor');
select * from award;

insert into actorObtain values(
	(select actorID
	from actor
	where actorName = 'Winona Ryder'),
	(select awardID
	from award
	where awardName = 'Best suportting actor'),
	'1994');

select * from actorObtain;

 

4/22

with actorIds(actorID) as
(select actorID from actor where dateOfDeath is not null),
movieIds(movieID) as
(select movieID from casting join actorIds using(actorID))
select * from movie join movieIds using(movieID);




with dicActId(directorID, actorID) as
(select directorID, actorID
from make join casting using(movieID)),
dicId(directorID) as
(select distinct directorID
from dicActId as A
where (select count(*)
	   from dicActId as B
	   where A.actorID = B.actorID) > 1)
	   
select directorName from director join dicId using(directorID);



select movieName, genreName 
from movie join movieGenre using(movieID) 
order by genreName;