1z5i5a68

1drop database if exists movies;
2create database movies;
3use movies;
4
5create table actors(
6id int primary key auto_increment not null,
7name varchar(200),
8birthDate date not null,
9adress varchar(256) not null,
10gender enum('male','female')
11);
12
13insert into actors(name,birthDate,adress,gender)
14values("John Stevenson",'1956-06-12',"St. Anna",'male');
15insert into actors(name,birthDate,adress,gender)
16values("Ana Nikol", '1968-01-21',"St. John ",'female');
17insert into actors(name,birthDate,adress,gender)
18values("Emma Dickens", '1971-03-18',"Nikolas Kopernik ",'female');
19
20create table studio(
21id int primary key auto_increment not null,
22adress varchar(256) not null,
23bulstat int not null
24);
25insert into studio(adress,bulstat) 
26values("x-studio",10020056);
27insert into studio(adress,bulstat) 
28values("y-studio",10120157);
29
30create table producers(
31id int primary key auto_increment not null,
32adress varchar(256) not null,
33bulstat int not null
34);
35
36insert into producers(adress,bulstat) 
37values("HHH avenue",12151645);
38insert into producers(adress,bulstat) 
39values("5 avenue",16161633);
40
41create table movies(
42id int primary key auto_increment not null,
43title varchar(100) not null,
44m_year year not null,
45lenght int not null,
46budget double not null,
47studio_id int,
48producer_id int,
49
50constraint foreign key (studio_id) references studio(id),
51constraint foreign key (producer_id) references producers(id)
52);
53
54insert into movies(title,m_year,lenght, budget, studio_id, producer_id) 
55values("One", 1997 , 120 , 9.5,1,2);
56insert into movies(title,m_year,lenght, budget,studio_id, producer_id) 
57values("Two", 2000 , 180 , 12.5,2,1);
58insert into movies(title,m_year,lenght, budget,studio_id, producer_id) 
59values("Three", 2016 , 90 , 10,1,1);
60
61create table movies_actors(
62actor_id int not null references actors(id),
63movie_id int not null references movies(id),
64
65primary key(actor_id, movie_id)
66);
67
68insert into movies_actors(actor_id,movie_id) 
69values (1,1),(2,1),(3,2);
70
71select actors.name,actors.gender,actors.adress
72from actors
73where actors.gender = 'male' or actors.adress = 'St. John';
74
75select m_year, budget 
76from movies
77where m_year between 1990 and 2000
78order by budget desc
79limit 2;