-- ============================================================================
-- Esercitazione su SQL (musicisti)
-- Creazione e popolamento del database da utilizzare per l'esercizio 3
-- ============================================================================

 DROP TABLE Abilita;
 DROP TABLE Strumenti;
 DROP TABLE Musicisti;

create table strumenti (
   nome      varchar(20) primary key,
   categoria varchar(20) default 'Non specificato'
   );
   
create table musicisti (
   codice       char(7)  primary key,
   nome         varchar(20) not null,
   cognome      varchar(20) not null,
   datanascita  date not null,
   sesso        char check (sesso = 'M' OR sesso = 'F'),
   unique (nome, cognome, datanascita)
   );

create table abilita (
   musicista   char(7),
   strumento   varchar(20),
   primary key (musicista, strumento),
   foreign key (musicista) references  musicisti (codice),
   foreign key (strumento) references strumenti (nome)
   );


INSERT INTO MUSICISTI (Codice,Nome,Cognome,datanascita,Sesso) VALUES ('CM12340','Angela','Hewitt','1955-03-30','F');
INSERT INTO MUSICISTI (Codice,Nome,Cognome,datanascita,Sesso) VALUES ('CM12341','Cecilia','Chaily','1971-12-15','F');
INSERT INTO MUSICISTI (Codice,Nome,Cognome,datanascita,Sesso) VALUES ('CM12342','Billie','Holiday','1915-04-07','F');
INSERT INTO MUSICISTI (Codice,Nome,Cognome,datanascita,Sesso) VALUES ('CM12343','Chet','Baker','1929-12-23','M');
INSERT INTO MUSICISTI (Codice,Nome,Cognome,datanascita,Sesso) VALUES ('CM12344','Pat','Metheny','1954-08-12','M');
INSERT INTO MUSICISTI (Codice,Nome,Cognome,datanascita,Sesso) VALUES ('CM12345','Keith','Richards','1943-12-18','M');
INSERT INTO MUSICISTI (Codice,Nome,Cognome,datanascita,Sesso) VALUES ('CM12346','Mike','Oldfield','1949-11-20','M');
INSERT INTO MUSICISTI (Codice,Nome,Cognome,datanascita,Sesso) VALUES ('CM12347','Janis','Joplin','1943-01-19','F');
INSERT INTO MUSICISTI (Codice,Nome,Cognome,datanascita,Sesso) VALUES ('CM12348','John','Coltrane','1926-08-20','M');
INSERT INTO MUSICISTI (Codice,Nome,Cognome,datanascita,Sesso) VALUES ('CM12349','Miles','Davis','1926-05-26','M');

-- Si noti che il formato date prevede che le date siano inserite nella forma yyyy-mm-dd, cioe' anno-mese-giorno.

INSERT INTO STRUMENTI (Nome,Categoria) VALUES ('Chitarra','A Corde');
INSERT INTO STRUMENTI (Nome,Categoria) VALUES ('Pianoforte','A Corde');
INSERT INTO STRUMENTI (Nome,Categoria) VALUES ('Arpa','A Corde');
INSERT INTO STRUMENTI (Nome,Categoria) VALUES ('Sax Tenore','Ottoni');
INSERT INTO STRUMENTI (Nome,Categoria) VALUES ('Tromba','Ottoni');
INSERT INTO STRUMENTI (Nome,Categoria) VALUES ('Batteria','Ritmica');
INSERT INTO STRUMENTI (Nome,Categoria) VALUES ('Basso','A Corde');
INSERT INTO STRUMENTI (Nome,Categoria) VALUES ('Percussioni','Ritmica');
INSERT INTO STRUMENTI (Nome,Categoria) VALUES ('Violino','A Corde');

INSERT INTO ABILITA (Musicista,Strumento) VALUES ('CM12340','Pianoforte');
INSERT INTO ABILITA (Musicista,Strumento) VALUES ('CM12341','Arpa');
INSERT INTO ABILITA (Musicista,Strumento) VALUES ('CM12343','Tromba');
INSERT INTO ABILITA (Musicista,Strumento) VALUES ('CM12344','Chitarra');
INSERT INTO ABILITA (Musicista,Strumento) VALUES ('CM12345','Chitarra');
INSERT INTO ABILITA (Musicista,Strumento) VALUES ('CM12346','Pianoforte');
INSERT INTO ABILITA (Musicista,Strumento) VALUES ('CM12346','Chitarra');
INSERT INTO ABILITA (Musicista,Strumento) VALUES ('CM12346','Batteria');
INSERT INTO ABILITA (Musicista,Strumento) VALUES ('CM12346','Basso');
INSERT INTO ABILITA (Musicista,Strumento) VALUES ('CM12346','Percussioni');
INSERT INTO ABILITA (Musicista,Strumento) VALUES ('CM12346','Tromba');
INSERT INTO ABILITA (Musicista,Strumento) VALUES ('CM12347','Chitarra');
INSERT INTO ABILITA (Musicista,Strumento) VALUES ('CM12348','Sax Tenore');
INSERT INTO ABILITA (Musicista,Strumento) VALUES ('CM12349','Tromba');