Untitled
user_8194681
sql
a year ago
3.1 kB
5
Indexable
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;
Editor is loading...