ataRui24

1DROP DATABASE IF EXISTS songs_website;
2CREATE DATABASE songs_website;
3USE songs_website;
4
5
6
7CREATE TABLE performer(
8id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
9name VARCHAR(100) NOT NULL,
10age INT NOT NULL,
11adress VARCHAR(50) NOT NULL
12);
13
14CREATE TABLE producer(
15id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
16name VARCHAR(100) NOT NULL,
17adress VARCHAR(50) NOT NULL
18);
19
20
21CREATE TABLE songs(
22id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
23title VARCHAR(50) NOT NULL,
24lenght VARCHAR(10) NOT NULL,
25year_s year NOT NULL,
26genre VARCHAR(10),
27style VARCHAR(10)
28);
29
30create table song_performer_producer(
31song_id int not null,
32performer_id int not null,
33constraint foreign key (song_id) references songs(id),
34constraint foreign key (performer_id) references performer(id),
35producer_id int not null,
36constraint foreign key (producer_id)references producer(id),
37primary key(song_id,performer_id,producer_id)
38);
39
40
41
42INSERT INTO performer(name,age,adress) VALUES ("Ed Sheeran",27," Framlingham, Suffolk, England");
43INSERT INTO performer(name,age,adress) VALUES ("Pink",38,"Doylestown, Pennsylvania, U.S.");
44INSERT INTO performer(name,age,adress) VALUES ("Eminem",45,"St. Joseph, Missouri, U.S.");
45INSERT INTO performer(name,age,adress) VALUES ("Rihanna",30,"Saint Michael, Barbados");
46INSERT INTO performer(name,age,adress) VALUES ("Alex Turner",32,"High Green, Sheffield, England");
47INSERT INTO performer(name,age,adress) VALUES ("Dua Lipa",22,"London, England");
48
49insert into songs(title,lenght,year_s,genre,style) values ("Shape of you","3:53",2017,"pop","dancehall");
50insert into songs(title,lenght,year_s,genre,style) values ("Just Like Fire","3:35",2016,"pop","rock");
51insert into songs(title,lenght,year_s,genre,style) values ("River","3:41",2018,"folk","hip-hop");
52insert into songs(title,lenght,year_s,genre,style) values ("Monster","4:10",2013,"hip-hop","pop");
53
54insert into producer(name,adress) values ("Bryan Fryzel","Rockville Centre, New York");
55insert into producer(name,adress) values ("Emile Haynie","Buffalo, New York");
56insert into producer(name,adress) values ("Max Martin","Stockholm, Sweden");
57insert into producer(name,adress) values ("Johan Schuster","Karlshamn, Sweden");
58insert into producer(name,adress) values ("Steve McCutcheon","Surrey, United Kingdom");
59
60insert into song_performer_producer(song_id,performer_id,producer_id) values (1,1,5);
61insert into song_performer_producer(song_id,performer_id,producer_id) values (2,2,4);
62insert into song_performer_producer(song_id,performer_id,producer_id) values (3,1,2);
63insert into song_performer_producer(song_id,performer_id,producer_id) values (3,3,4);
64insert into song_performer_producer(song_id,performer_id,producer_id) values (4,3,1);
65insert into song_performer_producer(song_id,performer_id,producer_id) values (4,4,2);
66
67
68/*zad2*/
69select performer.name, song_performer_producer.song_id
70from performer
71join song_performer_producer
72on song_performer_producer.performer_id = performer.id
73where performer.age = 27;
74/*zad3*/
75select song_performer_producer.performer_id,count(song_performer_producer.song_id) 
76from song_performer_producer
77group by song_performer_producer.performer_id;
78/*zad4*/
79select songs.id, songs.title, performer.name
80from songs 
81join song_performer_producer
82on songs.id = song_performer_producer.song_id
83right join performer on performer.id = song_performer_producer.performer_id;
84/*зад5*/
85select producer.name,count(song_performer_producer.song_id)
86from producer
87join song_performer_producer
88on producer.id = song_performer_producer.producer_id
89group by song_performer_producer.producer_id;