Script+Automóviles

code format="sql" DROP TABLE marco; DROP TABLE ventas; DROP TABLE distribucion; DROP TABLE clientes; DROP TABLE concesionarios; DROP TABLE coches; DROP TABLE marca;

CREATE TABLE MARCA ( Cifm int PRIMARY KEY NOT NULL, Nombre VARCHAR(20) NOT NULL, Ciudad VARCHAR(20) NOT NULL );

INSERT INTO MARCA VALUES(0001,'seat','Madrid'); INSERT INTO MARCA VALUES(0002,'renault','Barcelona'); INSERT INTO MARCA VALUES(0003,'citroen','Valencia'); INSERT INTO MARCA VALUES(0004,'audi','Madrid'); INSERT INTO MARCA VALUES(0005,'opel','Bilbao'); INSERT INTO MARCA VALUES(0006,'bmw','Barcelona');

CREATE TABLE COCHES ( Codcoche int PRIMARY KEY NOT NULL, Nombre VARCHAR(20) NOT NULL, Modelo VARCHAR(10) NOT NULL );

INSERT INTO COCHES VALUES (0001,'ibiza','16V'); INSERT INTO COCHES VALUES (0002,'ibiza','tsi'); INSERT INTO COCHES VALUES (0003,'ibiza','tdi'); INSERT INTO COCHES VALUES (0004,'leon','Style Copa'); INSERT INTO COCHES VALUES (0005,'altea','ecomotive'); INSERT INTO COCHES VALUES (0006,'megane','GT Line'); INSERT INTO COCHES VALUES (0007,'megane','gti'); INSERT INTO COCHES VALUES (0008,'laguna','Berlina'); INSERT INTO COCHES VALUES (0009,'laguna','coupé'); INSERT INTO COCHES VALUES (0010,'c4','16V'); INSERT INTO COCHES VALUES (0011,'c4','hdi'); INSERT INTO COCHES VALUES (0012,'c5','hdi'); INSERT INTO COCHES VALUES (0013,'a4','1.8'); INSERT INTO COCHES VALUES (0014,'a4','2.8'); INSERT INTO COCHES VALUES (0015,'astra','caravan'); INSERT INTO COCHES VALUES (0016,'astra','gti'); INSERT INTO COCHES VALUES (0017,'corsa','1.4'); INSERT INTO COCHES VALUES (0018,'300','318d'); INSERT INTO COCHES VALUES (0019,'500','520d'); INSERT INTO COCHES VALUES (0020,'700','730d');

CREATE TABLE CONCESIONARIOS ( Cifc int PRIMARY KEY NOT NULL, Nombre VARCHAR(20) NOT NULL, Ciudad VARCHAR(20) NOT NULL );

INSERT INTO CONCESIONARIOS VALUES(0001,'acar','Madrid'); INSERT INTO CONCESIONARIOS VALUES(0002,'bcar','Madrid'); INSERT INTO CONCESIONARIOS VALUES(0003,'ccar','Barcelona'); INSERT INTO CONCESIONARIOS VALUES(0004,'dcar','Valencia'); INSERT INTO CONCESIONARIOS VALUES(0005,'ecar','Bilbao');

CREATE TABLE CLIENTES ( DNI VARCHAR(10) PRIMARY KEY NOT NULL, Nombre VARCHAR(20) NOT NULL, Apellidos VARCHAR(30) NOT NULL, Ciudad VARCHAR (20) NOT NULL );

INSERT INTO CLIENTES VALUES('0001','Luis','Garcia','Madrid'); INSERT INTO CLIENTES VALUES('0002','Antonio','Lopez','Valencia'); INSERT INTO CLIENTES VALUES('0003','Juan','Martin','Madrid'); INSERT INTO CLIENTES VALUES('0004','Maria','Garcia','Madrid');

CREATE TABLE DISTRIBUCION ( Cifc int NOT NULL, Codcoche int NOT NULL, Cantidad int NOT NULL, constraint pk_clavecifc PRIMARY KEY(Cifc,Codcoche), constraint fk_clavecoche FOREIGN KEY (Codcoche) REFERENCES COCHES, constraint fk_clavedis FOREIGN KEY (Cifc) REFERENCES CONCESIONARIOS );

INSERT INTO DISTRIBUCION VALUES (0001,0001,3); INSERT INTO DISTRIBUCION VALUES (0001,0005,7); INSERT INTO DISTRIBUCION VALUES (0001,0006,7); INSERT INTO DISTRIBUCION VALUES (0001,0007,5); INSERT INTO DISTRIBUCION VALUES (0001,0008,10); INSERT INTO DISTRIBUCION VALUES (0002,0009,10); INSERT INTO DISTRIBUCION VALUES (0002,0010,5); INSERT INTO DISTRIBUCION VALUES (0002,0011,3); INSERT INTO DISTRIBUCION VALUES (0002,0012,5); INSERT INTO DISTRIBUCION VALUES (0003,0013,10); INSERT INTO DISTRIBUCION VALUES (0003,0014,5); INSERT INTO DISTRIBUCION VALUES (0003,0015,10); INSERT INTO DISTRIBUCION VALUES (0004,0016,20); INSERT INTO DISTRIBUCION VALUES (0004,0017,8);

CREATE TABLE VENTAS ( Cifc int NOT NULL, DNI VARCHAR(20) NOT NULL, Codcoche int NOT NULL, Color VARCHAR(10) NOT NULL, constraint pk_claveventa1 PRIMARY KEY (Cifc,DNI,Codcoche), constraint fk_claveventa2 FOREIGN KEY (Cifc) REFERENCES Concesionarios, constraint fk_claveventa3 FOREIGN KEY (DNI) REFERENCES Clientes, constraint fk_claveventa4 FOREIGN KEY (Codcoche) REFERENCES Coches );

INSERT INTO VENTAS VALUES(0001,'0001',0001,'blanco'); INSERT INTO VENTAS VALUES(0001,'0002',0005,'rojo'); INSERT INTO VENTAS VALUES(0002,'0003',0008,'blanco'); INSERT INTO VENTAS VALUES(0002,'0001',0006,'rojo'); INSERT INTO VENTAS VALUES(0003,'0004',0011,'rojo'); INSERT INTO VENTAS VALUES(0004,'0004',0014,'verde');

CREATE TABLE MARCO ( Cifm int NOT NULL, Codcoche int NOT NULL, constraint pk_clavemarco PRIMARY KEY (Cifm,Codcoche), constraint fk_clavemarco2 FOREIGN KEY (Cifm) REFERENCES Marca, constraint fk_clavemarco3 FOREIGN KEY (Codcoche) REFERENCES Coches );

INSERT INTO MARCO VALUES (0001,0001); INSERT INTO MARCO VALUES (0001,0002); INSERT INTO MARCO VALUES (0001,0003); INSERT INTO MARCO VALUES (0001,0004); INSERT INTO MARCO VALUES (0001,0005); INSERT INTO MARCO VALUES (0002,0006); INSERT INTO MARCO VALUES (0002,0007); INSERT INTO MARCO VALUES (0002,0008); INSERT INTO MARCO VALUES (0003,0009); INSERT INTO MARCO VALUES (0003,0010); INSERT INTO MARCO VALUES (0003,0011); INSERT INTO MARCO VALUES (0004,0012); INSERT INTO MARCO VALUES (0004,0013); INSERT INTO MARCO VALUES (0005,0014); INSERT INTO MARCO VALUES (0005,0015); INSERT INTO MARCO VALUES (0005,0016); INSERT INTO MARCO VALUES (0005,0017); INSERT INTO MARCO VALUES (0006,0018); INSERT INTO MARCO VALUES (0006,0019); INSERT INTO MARCO VALUES (0006,0020); code