Deviad

DROP DATABASE IF EXISTS premiere;

CREATE DATABASE premiere;

USE premiere;
drop table if exists Articoli;
create table if not exists Articoli(
                  NroArt char(4) primary key,
                  descrizione char(20),
                  giacenza int,
                  categoria char (2),
                  PrezzoUnitario decimal(8,2)
) ENGINE=INNODB;
drop table if exists Rappresentanti;

create table if not exists Rappresentanti(
                 CodRappr char(2) primary key,
                 cognome char(10),
                 nome char(8),
                 via char (15),
                 citta char(15),
                 prov char(2),
                 cap char (5),
                 TotProvv decimal(8,2),
                 PerProvv decimal(8,2)
) ENGINE=INNODB;

drop table if exists Clienti;
create table if not exists clienti(
                CodCliente char(3) primary key,
                  cognome char(10),
                  nome char(8),
                  via char (15),
                  citta char(15),
                  prov char(2),
                  cap char (5),
                  saldo decimal(8,2),
                  fido decimal(8,2),
                  CodRappr char(2) references Rappresentanti(CodRappr)
) ENGINE=INNODB;

drop table if exists Ordini;
create table if not exists Ordini(NroOrdine char(6) primary key,
                  data date,
                  CodCliente char(3) references Clienti(CodClienti)
) ENGINE=INNODB;


insert into articoli values
            ('AX12','ferro da stiro',104,'cs',24.95),
            ('AZ52','freccette',20,'sp',12.95),
            ('BA74','pallone',40,'sp',29.95),
            ('BH22','tritatutto',05,'cs',24.95),
            ('BT04','forno',11,'el',149.49),
            ('BZ66','lavatrice',52,'el',399.99),
            ('CA14','setaccio',78,'cs',39.99),
            ('CB03','bicicletta',44,'sp',299.99),
            ('CX11','frullino',142,'cs',22.95),
            ('CZ81','tavola pesi',68,'sp',349.95);


insert into Rappresentanti values
            ('03','Jones','Mary','123 Main','Grant','MI','49219',215,5),
            ('06','Smith','William','102 Raymond','Ada','MI','49441',49412.5,7),
            ('12','Diaz','Miguel','419 Harper','Lansing','MI','49224',2150,5);


insert into clienti values
            ('124','Adams','Sally','481Oak','Lansing','MI','49224',818.75,1000,'03'),
            ('256','Samuel','Ann','215Pete','Grant','MI','49219',21.5,1500,'06'),
            ('311','Charles','Don','48College','Ira','MI','49034',825.75,1000,'12'),
            ('315','Daniels','Tom','914Charry','Kent','MI','48391',770.75,750,'06'),
            ('405','Williams','Al','519Watson','Grant','MI','49219',402.75,1500,'12'),
            ('412','Adams','Sally','16Elm','Lansing','MI','49224',1817.5,2000,'03'),
            ('522','Nelson','Mary','108Pine','Ada','MI','49441',98.75,1500,'12'),
            ('567','Dinh','Tran','808Ridge','Harper','MI','48421',402.4,750,'06'),
            ('587','Galvez','Mara','512Pine','Ada','MI','49441',114.6,1000,'06'),
            ('622','Martin','Dan','419Chip','Grant','MI','49219',1045.75,1000,'03');


insert into  ordini values
            ('12489','2002-09-02','124'),
            ('12491','2002-09-02','311'),
            ('12494','2002-09-04','315'),
            ('12495','2002-09-04','256'),
            ('12498','2002-09-05','522'),
            ('12500','2002-09-05','124'),
            ('12504','2002-09-05','522');