Untitled

Create table Cantanti
  ( ISWC varchar(16) primary key,
full_name varchar(50) not null,
solista boolean not null,
sanremo boolean not null,
anno_nascita date not null
  );


Create table nipoti
(
ID VARCHAR(16) primary key,
full_name_n varchar(69) not null

);

Create table brani
(
ISMN varchar (12) primary key,
nome varchar (16) not null,
tipo varchar (16) not null,
anno date not null


);
 
 
Create table cd
(
ISRC varchar(12) primary key,
nome_cd varchar (69) not null,
anno_uscita date not null
id_nipoti VARCHAR(16),
foreign key(id_nipoti) references nipoti (ID),
);

Create table fare
(
idf varchar(16) primary key,
ISWC_cantanti varchar(16) not null,
ISRC_cd varchar(12) not null,
foreign key(ISWC_cantanti) references cantanti (ISWC),
foreign key(ISRC_cd) references cd (ISRC),
);

Create table composto
(
id_composto varchar(16) primary key,
ISRC__cd varchar(16) not null,
ISMN_brani varchar(12) not null,
foreign key(ISRC__cd) references cd (ISRC),
foreign key(ISMN_brani) references brani (ISMN),
);

Insert into Cantanti
(ISWC, full_name,solista, sanremo,  anno_nascita)
values('00001', 'Lucio battisti', true, true, '1943-03-05')
 ('00002', 'Duran Duran', false, false, '1989' )
 ('00002', 'francesco guccini', true, false, '1940-06-14')
 ('00003', 'dire straits ', false , true, '1980-02-21')
 
);
Insert into nipoti
(id, full_name_n)
values('DPLMTT05S12H118S','MATTIA DEPLANO')
 ('FGHLKJ45M55F876N','CARLO PERRA')
 ('FLMNBC32M55F876N', 'GIORGIA CAU')
 
);

Insert into brani
   (
(ISMN, nome, tipo , anno)
values('0006', 'Tunnel of love', '2005')
 ('0098', 'Missing', 'La pansè' '1955')
 ('0032', 'Pas Ta Fête', '1955')
 
);


Insert into cd
(
(ISRC, nome_cd, anno_uscita)
VALUES('07165', 'SANTERIA', '2015')
 ('64649', 'SIRIO', '2022')
 ('89324', 'GEMELLI','2020')
);

SELECT cd
FROM cd, fare, cantanti
WHERE cd.ISRC = fare.ISRC_cd
AND fare.ISWC_cantanti = cantanti.ISWC
AND cantanti.full_name = 'Lucio Battisti';


SELECT brani,  cantanti.full_name
FROM brani, composto, cd, fare, cantanti
WHERE brani.ISMN = composto.ISMN_brani
AND composto.ISRC_cd = cd.ISRC
AND cd.ISRC = fare.ISRC_cd
AND fare.ISWC_cantanti = cantanti.ISWC
AND brani.nome = 'Tunnel of Love';

SELECT brani
FROM brani, composto, cd, fare, cantanti
WHERE brani.ISMN = composto.ISMN_brani
AND composto.ISRC_cd = cd.ISRC
AND cd.ISRC = fare.ISRC_cd
AND fare.ISWC_cantanti = cantanti.ISWC
AND EXTRACT(YEAR FROM cantanti.anno_nascita) = 1955;

SELECT brani
FROM brani, composto, cd, fare, cantanti
WHERE brani.ISMN = composto.ISMN_brani
AND composto.ISRC_cd = cd.ISRC
AND cd.ISRC = fare.ISRC_cd
AND fare.ISWC_cantanti = cantanti.ISWC
AND cantanti.solista = false;

SELECT nipoti.full_name_n
FROM nipoti, cd, fare, cantanti
WHERE nipoti.ID = cd.id_nipoti
AND cd.ISRC = fare.ISRC_cd
AND fare.ISWC_cantanti = cantanti.ISWC
AND cantanti.full_name = 'Francesco Guccini'
AND cd.nome_cd = 'Opera buffa';

SELECT brani
FROM brani, composto, cd, fare, cantanti
WHERE brani.ISMN = composto.ISMN_brani
AND composto.ISRC_cd = cd.ISRC
AND cd.ISRC = fare.ISRC_cd
AND fare.ISWC_cantanti = cantanti.ISWC
AND cantanti.sanremo = true;