DROP database IF EXISTS harrybooks;
CREATE DATABASE IF NOT EXISTS harrybooks;


USE harrybooks;

CREATE TABLE filiali
(
  numero_filiale INT PRIMARY KEY,
  nome char(20) NOT NULL,
  sede char(20) NOT NULL,
  numero_dipendenti INT NOT NULL
);
describe filiali;
CREATE TABLE editori
(
  codice CHAR (2) PRIMARY KEY,
  nome CHAR(20) NOT NULL,
  citta CHAR(20) NOT NULL,
  stato CHAR (2) NOT NULL
);
describe editori;
CREATE TABLE autori
(
  numero_autore INT PRIMARY KEY,
  cognome CHAR (25) NOT NULL,
  nome CHAR(25)
);
describe autori;
CREATE TABLE libri
(
  codice CHAR(4) PRIMARY KEY,
  titolo CHAR(30) NOT NULL,
  codice_editore CHAR(2),
  tipo CHAR(3) NOT NULL,
  prezzo DECIMAL(4,2) DEFAULT 0,
  paperback  ENUM ('Y','N'),
  FOREIGN KEY(codice_editore) REFERENCES editori(codice)
);
describe libri;
CREATE TABLE hascritto
(
  codice_libro CHAR(4) ,
  numero_autore INT ,
  numero_sequenziale INT NOT NULL,
  PRIMARY KEY(numero_autore,codice_libro),
  FOREIGN KEY(codice_libro) REFERENCES libri(codice),
  FOREIGN KEY(numero_autore) REFERENCES autori(numero_autore)
);	
describe hascritto;
CREATE TABLE scorte
(
  codice_libro CHAR(4),
  numero_filiale INT,
  num_copie_disponibili INT,
  PRIMARY KEY(codice_libro,numero_filiale),
  FOREIGN KEY(codice_libro) REFERENCES libri(codice),
  FOREIGN KEY(numero_filiale) REFERENCES filiali(numero_filiale)
);
describe scorte;
  

Insert into filiali values (1,'Harry Downtown','16 Riverview',10);
Insert into filiali values (2,'Harry On the Hill','1289 Bedford',06);
Insert into filiali values (3,'Harry Brentwood','Brentwood Mall',15);
Insert into filiali values (4,'Harry Eastshore','Eastshore Mall',09);
select * from filiali;


insert into autori values (01,'Archer','Jeffrey');
insert into autori values (002,'Christie','Agatha');
insert into autori values (03,'Clarke','Arthur C.');
insert into autori values (04,'Francis','Dick');
insert into autori values (05,'Cussler','Clive');
insert into autori values (06,'King','Stephen');
insert into autori values (07,'Pratt','Philip');
insert into autori values (08,'Adamski','Joseph');
insert into autori values (10,'Harmon','Willis');
insert into autori values (11,'Rheingold','Howard');
insert into autori values (12,'Owen','Barbara');
insert into autori values (13,'Williams','Peter');
insert into autori values (14,'Kafka','Franz');
insert into autori values (15,'Novalis',NULL);
insert into autori values (16,'Lovecraft','H.P.');
insert into autori values (17,'Paz','Octavio');
insert into autori values (18,'Camus','Albert');
insert into autori values (19,'Castleman','Riva');
insert into autori values (20,'Zimbardo','Philip');
insert into autori values (21,'Gimferrer','Pere');
insert into autori values (22,'Southworth','Rod');
insert into autori values (23,'Wray','Robert');
select * from autori;


insert into editori values ('AH','Arkham House Publ.','Sauk City','WI');
insert into editori values ('AP','Arcade Publishing','New York','NY');
insert into editori values ('AW','Addison Wesley','Reading','MA');
insert into editori values ('BB','Bantam Books','New York','NY');
insert into editori values ('BF','Best and Furrow','Boston','MA');
insert into editori values ('JT','Jeremy P. Tarcher','Los Angeles','CA');
insert into editori values ('MP','McPherson and Co.','Kinston','NY');
insert into editori values ('PB','Pocket Books','New York','NY');
insert into editori values ('RH','Random House','New York','NY');
insert into editori values ('RZ','Rizzoli','New York','NY');
insert into editori values ('SB','Schoken Books','New York','NY');
insert into editori values ('SI','Signet','New York','NY');
insert into editori values ('TH','Thames and Hudson','New York','NY');
insert into editori values ('WN','W.W. Norton and Co.','New York','NY');
select * from editori;


insert into libri values ('0180','Shyness','BB','PSY',7.65,'Y');
insert into libri values ('0189','Kane and Abel','PB','FIC',5.55,'Y');
insert into libri values ('0200','Stranger','BB','FIC',8.75,'Y');
insert into libri values ('0378','Dunwich Horror and Others','PB','HOR',19.75,'N');
insert into libri values ('079X','Smokescreen','PB','MYS',4.55,'Y');
insert into libri values ('0808','Knockdown','PB','MYS',4.75,'Y');
insert into libri values ('1351','Cujo','SI','HOR',6.65,'Y');
insert into libri values ('1382','Marcel Duchamp','PB','ART',11.25,'Y');
insert into libri values ('138X','Death on the Nil','BB','MYS',3.95,'Y');
insert into libri values ('2226','Ghost from the Grand Banks','BB','SFI',19.95,'N');
insert into libri values ('2281','Prints of the 20th Century','PB','ART',13.25,'Y');
insert into libri values ('2766','Prodigal Daughter','PB','FIC',5.45,'Y');
insert into libri values ('2908','Hymns to the Night','BB','POE',6.75,'Y');
insert into libri values ('3350','Higher Creativity','PB','PSY',9.75,'Y');
insert into libri values ('3743','First Among Equals','PB','FIC',3.95,'Y');
insert into libri values ('3906','Vortex','BB','SUS',5.45,'Y');
insert into libri values ('5163','Organ','SI','MUS',16.95,'Y');
insert into libri values ('5790','Database Systems','BF','CS',54.95,'N');
insert into libri values ('6128','Evil Under the Sun','PB','MYS',4.45,'Y');
insert into libri values ('6328','Vixen 07','BB','SUS',5.55,'Y');
insert into libri values ('669X','A Guide to SQL','BF','CS',23.95,'Y');
insert into libri values ('6908','DOS Essentials','BF','CS',20.50,'Y');
insert into libri values ('7405','Night Probe','BB','SUS',5.65,'Y');
insert into libri values ('7443','Carrie','SI','HOR',6.75,'Y');
insert into libri values ('7559','Risk','PB','MYS',3.95,'Y');
insert into libri values ('7947','dBASE Programming','BF','CS',39.90,'Y');
insert into libri values ('8092','Magritte','SI','ART',21.95,'N');
insert into libri values ('8720','Castle','BB','FIC',12.15,'Y');
insert into libri values ('9611','Amerika','BB','FIC',10.95,'Y');
select * from libri;


insert into scorte values ('0180','1',2);
insert into scorte values ('0189','2',2);
insert into scorte values ('0200','1',1);
insert into scorte values ('0200','2',3);
insert into scorte values ('079X','2',1);
insert into scorte values ('079X','3',2);
insert into scorte values ('079X','4',3);
insert into scorte values ('1351','1',1);
insert into scorte values ('1351','2',4);
insert into scorte values ('1351','3',2);
insert into scorte values ('138X','2',3);
insert into scorte values ('2226','1',3);
insert into scorte values ('2226','3',2);
insert into scorte values ('2226','4',1);
insert into scorte values ('2281','4',3);
insert into scorte values ('2766','3',2);
insert into scorte values ('2908','1',3);
insert into scorte values ('2908','4',1);
insert into scorte values ('3350','1',2);
insert into scorte values ('3906','2',1);
insert into scorte values ('3906','3',2);
insert into scorte values ('5163','1',1);
insert into scorte values ('5790','4',2);
insert into scorte values ('6128','2',4);
insert into scorte values ('6128','3',3);
insert into scorte values ('6328','2',2);
insert into scorte values ('669X','1',1);
insert into scorte values ('6908','2',2);
insert into scorte values ('7405','3',2);
insert into scorte values ('7559','2',2);
insert into scorte values ('7947','2',2);
insert into scorte values ('8092','3',1);
insert into scorte values ('8720','1',3);
insert into scorte values ('9611','1',2);
select * from scorte;


insert into hascritto values ('0180',20,1);
insert into hascritto values ('0189',01,1);
insert into hascritto values ('0200',18,1);
insert into hascritto values ('0378',16,1);
insert into hascritto values ('079X',04,1);
insert into hascritto values ('0808',04,1);
insert into hascritto values ('1351',06,1);
insert into hascritto values ('1382',17,1);
insert into hascritto values ('138X',02,1);
insert into hascritto values ('2226',03,1);
insert into hascritto values ('2281',19,1);
insert into hascritto values ('2766',01,1);
insert into hascritto values ('2908',15,1);
insert into hascritto values ('3350',10,1);
insert into hascritto values ('3350',11,2);
insert into hascritto values ('3743',01,1);
insert into hascritto values ('3906',05,1);
insert into hascritto values ('5163',12,2);
insert into hascritto values ('5163',13,1);
insert into hascritto values ('5790',07,1);
insert into hascritto values ('5790',08,2);
insert into hascritto values ('6128',02,1);
insert into hascritto values ('6328',05,1);
insert into hascritto values ('669X',07,1);
insert into hascritto values ('6908',22,1);
insert into hascritto values ('7405',05,1);
insert into hascritto values ('7443',06,1);
insert into hascritto values ('7559',04,1);
insert into hascritto values ('7947',07,1);
insert into hascritto values ('7947',23,2);
insert into hascritto values ('8092',21,1);
insert into hascritto values ('8720',14,1);
insert into hascritto values ('9611',14,1);
select * from hascritto;