Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- Autores: Álvaro Ruizfernández Palacios,Rubén Marcos González, Antonio de los Mozos Alonso, Beatriz Mayo Gil.
- Ejercicio: Practica05
- Fecha de entrega: 30-10-2015
- */
- DROP TABLE IF EXISTS clienteformas cascade;
- DROP TABLE IF EXISTS clienteytipo cascade;
- DROP TABLE IF EXISTS lineasDeFacturas cascade;
- DROP TABLE IF EXISTS facturas cascade;
- DROP TABLE IF EXISTS productos cascade;
- DROP TABLE IF EXISTS color cascade;
- DROP TABLE IF EXISTS formasPago cascade;
- DROP TABLE IF EXISTS clientes cascade;
- DROP TABLE IF EXISTS tiposCliente cascade;
- DROP TABLE IF EXISTS ciudades cascade;
- DROP TABLE IF EXISTS provincia cascade;
- DROP TABLE IF EXISTS comerciales cascade;
- DROP TABLE IF EXISTS contactos cascade;
- CREATE TABLE formasPago(
- FPago VARCHAR(15) CONSTRAINT unq_FormasPago UNIQUE NOT NULL,
- AcronimoP VARCHAR(3) CONSTRAINT chk_FormasPago CHECK (AcronimoP=UPPER(AcronimoP)) CONSTRAINT pk_FormasPago PRIMARY KEY
- );
- CREATE TABLE comerciales(
- DNI VARCHAR(9) CONSTRAINT chk_DNI CHECK (DNI SIMILAR TO '[A-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]') CONSTRAINT pk_com PRIMARY KEY,
- nombre VARCHAR(20) NOT NULL,
- ape1 VARCHAR (20) NOT NULL,
- ape2 VARCHAR (20) NOT NULL,
- tfn NUMERIC (9) NOT NULL,
- email VARCHAR (25)
- );
- CREATE TABLE tiposCliente(
- TCliente VARCHAR(15) CONSTRAINT unq_tiposCliente UNIQUE NOT NULL,
- AcronimoC VARCHAR(3) CONSTRAINT chk_tiposCliente CHECK (AcronimoC=UPPER(AcronimoC)) CONSTRAINT pk_tiposCliente PRIMARY KEY,
- comercial VARCHAR(9) CONSTRAINT fk_Comerciales REFERENCES comerciales CONSTRAINT unq_comercial UNIQUE NOT NULL
- );
- CREATE TABLE provincia(
- AcronimoPr VARCHAR(3) CONSTRAINT chk_provincia CHECK (AcronimoPr =UPPER(AcronimoPr)) CONSTRAINT pk_provincia PRIMARY KEY,
- NProvncia VARCHAR(20) CONSTRAINT unq_provincia UNIQUE NOT NULL
- );
- CREATE TABLE ciudades(
- NCiudad VARCHAR(20),
- AcronimoPr VARCHAR(3) CONSTRAINT chk_AcronimoPr CHECK (AcronimoPr =UPPER(AcronimoPr))CONSTRAINT fk_AcronimoPr REFERENCES provincia ON UPDATE Cascade,
- CONSTRAINT Pk_nomAcro PRIMARY KEY(NCiudad, AcronimoPr)
- );
- CREATE TABLE clientes(
- CIF VARCHAR (9) CONSTRAINT pk_CIF PRIMARY KEY CONSTRAINT chk_CIF CHECK (CIF SIMILAR TO '[A-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
- nombre VARCHAR (20) NOT NULL,
- apellidos VARCHAR (50) NOT NULL,
- direccion VARCHAR (50) NOT NULL,
- telefono NUMERIC (9) NOT NULL,
- CuentaBank NUMERIC (20) NOT NULL,
- fax NUMERIC (9),
- Email VARCHAR (25),
- pagoDefecto VARCHAR (3) REFERENCES formasPago,
- Ciudad VARCHAR (25),
- AcronimoPr VARCHAR (20),
- CONSTRAINT fk_Ciudad_AcronimoPr FOREIGN KEY (Ciudad,AcronimoPr) REFERENCES ciudades ON UPDATE Cascade,
- CONSTRAINT unq_nom_dir_telf_banc UNIQUE(nombre,apellidos,direccion,telefono,CuentaBank),
- CONSTRAINT unq_pagoDefecto UNIQUE(CIF,pagoDefecto)
- );
- CREATE TABLE facturas(
- IDFactura NUMERIC (5) CONSTRAINT pk_IDFactura PRIMARY KEY,
- Fecha DATE,
- CIF VARCHAR (9) CONSTRAINT fk_CIF REFERENCES clientes,
- AcronimoP VARCHAR (15) CONSTRAINT fk_facturaPago REFERENCES formasPago ON UPDATE Cascade
- );
- CREATE TABLE Color(
- Color VARCHAR (20) CONSTRAINT pk_Color PRIMARY KEY,
- r SMALLINT DEFAULT 0 NOT NULL,
- g SMALLINT DEFAULT 0 NOT NULL,
- b SMALLINT DEFAULT 0 NOT NULL,
- CONSTRAINT unq_rgb UNIQUE(r,g,b)
- );
- CREATE TABLE Productos(
- Articulo VARCHAR (20),
- RefFamilia SMALLINT,
- Color VARCHAR (20) CONSTRAINT fk_Color REFERENCES Color ON UPDATE Cascade,
- Existencias SMALLINT DEFAULT 0 NOT NULL,
- Descripcion VARCHAR (50),
- Precio NUMERIC(5,2) NOT NULL,
- CONSTRAINT pk_Productos PRIMARY KEY (Articulo, RefFamilia,Color)
- );
- CREATE TABLE lineasDeFacturas(
- IDFactura SMALLINT CONSTRAINT fk_Numfactura REFERENCES facturas ON DELETE Cascade,
- Articulo VARCHAR (20),
- RefFamilia SMALLINT,
- Color VARCHAR (20),
- Cantidad NUMERIC (2) DEFAULT 1 NOT NULL,
- Precio NUMERIC(5,2) NOT NULL,
- CONSTRAINT fk_ArticuloRColor FOREIGN KEY (Articulo, RefFamilia, Color) REFERENCES Productos ON UPDATE Cascade,
- CONSTRAINT pk_compra PRIMARY KEY (IDfactura, Articulo, RefFamilia, Color)
- );
- CREATE TABLE clienteytipo(
- CIF VARCHAR(9) REFERENCES clientes ON UPDATE Cascade,
- AcronimoC VARCHAR(3) REFERENCES tiposCliente ON UPDATE Cascade,
- CONSTRAINT pk_clienteytipo PRIMARY KEY (CIF, AcronimoC)
- );
- CREATE TABLE clienteformas(
- CIF VARCHAR(9) REFERENCES clientes ON UPDATE Cascade,
- AcronimoP VARCHAR(3) REFERENCES formasPago ON UPDATE Cascade,
- CONSTRAINT pk_clienteformas PRIMARY KEY (CIF, AcronimoP)
- );
- CREATE TABLE contactos(
- DNIcom VARCHAR(9) CONSTRAINT FK_com REFERENCES comerciales,
- CIFcli VARCHAR(9) CONSTRAINT FK_cli REFERENCES clientes,
- fechaVis DATE DEFAULT CURRENT_DATE,
- CONSTRAINT PK_contactos PRIMARY KEY(DNIcom,CIFcli,fechaVis)
- );
- INSERT INTO formasPago VALUES ('TarjetaCredito','TC');
- INSERT INTO formasPago VALUES ('AlContado','ACT');
- INSERT INTO formasPago VALUES ('Metalico','MET');
- INSERT INTO formasPago VALUES ('PayPal','PP');
- INSERT INTO comerciales VALUES ('R95175364','Pepe','Garcia','Majo',987654321,'pepeelgrande@gmail.com');
- INSERT INTO comerciales VALUES ('R95174364','Luis','Garcia','Majo',987654321,'pepeelgrande@gmail.com');
- INSERT INTO comerciales VALUES ('R95173364','Adolfo','Garcia','Majo',987654321,'pepeelgrande@gmail.com');
- INSERT INTO tiposCliente VALUES ('Pequeño','P','R95175364');
- INSERT INTO tiposCliente VALUES ('Grande','G','R95174364');
- INSERT INTO tiposCliente VALUES ('Medio','M','R95173364');
- INSERT INTO provincia VALUES ('BU','Burgos');
- INSERT INTO provincia VALUES ('ZA','Zamora');
- INSERT INTO provincia VALUES ('SO','Soria');
- INSERT INTO provincia VALUES ('LE','Leon');
- INSERT INTO ciudades VALUES ('Benavente','ZA');
- INSERT INTO ciudades VALUES ('Toro','ZA');
- INSERT INTO ciudades VALUES ('Aranda','BU');
- INSERT INTO ciudades VALUES ('Miranda','BU');
- INSERT INTO clientes VALUES ('A09652345','Mangel','Rogel','CalleFalsa 123',987654321,56419876544569845,958746312,'mangelrogel@gmail.com','TC','Benavente','ZA');
- INSERT INTO clientes VALUES ('A09627845','Perico','Palotes','CalleCierta 456',987654121,56413164944569845,957516312,'pericopalotes@gmail.com','ACT','Aranda','BU');
- INSERT INTO clientes VALUES ('A09675345','Manolo','eldelBombo','CalleIncierta 789',987651231,56413167895569845,957741312,'manolobombon@gmail.com','MET','Miranda','BU');
- INSERT INTO facturas VALUES (5,'09-05-2008','A09652345','TC');
- INSERT INTO facturas VALUES (94456,'15-05-2008','A09627845','ACT');
- INSERT INTO facturas VALUES (65412,'22-05-2008','A09675345','MET');
- INSERT INTO color VALUES ('Rojo',234,023,045);
- INSERT INTO color VALUES ('Azul',034,023,255);
- INSERT INTO color VALUES ('Verde',065,245,012);
- INSERT INTO productos VALUES ('Cajonera',1,'Rojo',12,'Cajonera para colocar en un salon',50);
- INSERT INTO productos VALUES ('Maceta',2,'Verde',52,'Maceta de jardin de color verde camuflaje',90);
- INSERT INTO productos VALUES ('Baldosa',3,'Azul',2,'Baldosa de piscina, color azul',10);
- INSERT INTO lineasdefacturas VALUES (5,'Cajonera',1,'Rojo',5,654.95);
- INSERT INTO lineasdefacturas VALUES (5,'Maceta',2,'Verde',9,126.90);
- INSERT INTO lineasdefacturas VALUES (5,'Baldosa',3,'Azul',7,54.99);
- INSERT INTO clienteytipo VALUES ('A09652345','M');
- INSERT INTO clienteytipo VALUES ('A09627845','P');
- INSERT INTO clienteytipo VALUES ('A09675345','G');
- INSERT INTO clienteytipo VALUES ('A09652345','G');
- INSERT INTO clienteytipo VALUES ('A09627845','M');
- INSERT INTO clienteytipo VALUES ('A09675345','P');
- INSERT INTO clienteformas VALUES ('A09652345','TC');
- INSERT INTO clienteformas VALUES ('A09627845','ACT');
- INSERT INTO clienteformas VALUES ('A09675345','MET');
- INSERT INTO clienteformas VALUES ('A09652345','MET');
- INSERT INTO clienteformas VALUES ('A09627845','TC');
- INSERT INTO clienteformas VALUES ('A09675345','ACT');
- --select AVG(lineasdefacturas.precio) from (lineasdefacturas join facturas using(IDFactura)) natural join clientes where clientes.ciudad='Burgos'
- --select SUM(lineasdefacturas.precio)
- --from ((lineasdefacturas join facturas using(IDFactura)) natural join clientes) natural join clienteytipo
- --where AcronimoC='M' and (facturas.fecha >= '01-01-2012' and facturas.fecha <= '31-12-2012')
- SELECT COUNT(*)
- FROM (tiposCliente INNER JOIN comerciales ON comercial=DNI) WHERE comercial='M'
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement