WdjAY4p0

1/*
2Autores: Álvaro Ruizfernández Palacios,Rubén Marcos González, Antonio de los Mozos Alonso, Beatriz Mayo Gil.
3Ejercicio: Practica05
4Fecha de entrega: 30-10-2015
5*/
6drop table if exists clienteformas cascade;
7drop table if exists clienteytipo cascade;
8drop table if exists lineasDeFacturas cascade;
9drop table if exists facturas cascade;
10drop table if exists productos cascade;
11drop table if exists color cascade;
12drop table if exists formasPago cascade;
13drop table if exists clientes cascade;
14drop table if exists tiposCliente cascade;
15drop table if exists ciudades cascade;
16drop table if exists provincia cascade;
17drop table if exists comerciales cascade;
18drop table if exists contactos cascade;
19
20
21
22Create Table formasPago(
23	FPago			VarChar(15) Constraint unq_FormasPago unique not null,
24	AcronimoP 		VarChar(3) Constraint chk_FormasPago Check (AcronimoP=upper(AcronimoP)) Constraint pk_FormasPago Primary key
25);
26Create table comerciales(
27	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,
28	nombre	VarChar(20) not null,
29	ape1	VarChar (20) not null,
30	ape2	VarChar (20) not null,
31	tfn 	Numeric (9) not null,
32	email 	VarChar (25)
33	
34);
35Create Table tiposCliente(
36	TCliente		VarChar(15) Constraint unq_tiposCliente unique not null,
37	AcronimoC		VarChar(3) Constraint chk_tiposCliente Check (AcronimoC=upper(AcronimoC)) Constraint pk_tiposCliente Primary key,
38	comercial		VarChar(9) Constraint fk_Comerciales references comerciales constraint unq_comercial unique not null
39);
40
41Create Table provincia(
42	AcronimoPr 	VarChar(3) Constraint chk_provincia Check (AcronimoPr =upper(AcronimoPr)) Constraint pk_provincia Primary key,
43	NProvncia	VarChar(20) Constraint unq_provincia unique not null
44);
45Create Table ciudades(
46	NCiudad		VarChar(20),
47	AcronimoPr	VarChar(3) Constraint chk_AcronimoPr Check (AcronimoPr =upper(AcronimoPr))Constraint fk_AcronimoPr References provincia on Update Cascade,
48	Constraint Pk_nomAcro Primary key(NCiudad, AcronimoPr)
49);
50Create Table clientes(
51	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]'),
52	nombre 			VarChar (20) Not null,
53	apellidos		VarChar (50) Not null,
54	direccion 		VarChar (50) Not null,
55	telefono 		numeric (9) Not null,
56	CuentaBank 		numeric (20) Not null,
57	fax 			numeric (9),
58	Email 			VarChar (25),
59	pagoDefecto 		VarChar (3) references formasPago,
60	Ciudad 			VarChar (25),
61	AcronimoPr 		VarChar (20),
62	Constraint fk_Ciudad_AcronimoPr foreign key (Ciudad,AcronimoPr) References ciudades on Update Cascade,
63	Constraint unq_nom_dir_telf_banc UNIQUE(nombre,apellidos,direccion,telefono,CuentaBank),
64	Constraint unq_pagoDefecto Unique(CIF,pagoDefecto)
65);
66
67Create Table facturas(
68	IDFactura 		Numeric (5) Constraint pk_IDFactura Primary key,
69	Fecha 			Date,
70	CIF 			VarChar (9) Constraint fk_CIF References clientes,
71	AcronimoP		VarChar (15) Constraint fk_facturaPago References formasPago on Update Cascade
72);
73
74Create Table Color(
75	Color				VarChar (20) Constraint pk_Color Primary key,
76	r 				Smallint DEFAULT 0 Not null,
77	g 				Smallint DEFAULT 0 Not null,
78	b 				Smallint DEFAULT 0 Not null,
79	Constraint unq_rgb UNIQUE(r,g,b)
80);
81
82Create Table Productos(
83	Articulo		VarChar (20),
84	RefFamilia 		Smallint,
85	Color 			VarChar (20) Constraint fk_Color References Color on Update Cascade,
86	Existencias 		Smallint DEFAULT 0 Not null,
87	Descripcion		VarChar (50),
88	Precio			numeric(5,2) not null,
89	Constraint pk_Productos Primary key (Articulo, RefFamilia,Color)
90);
91Create Table lineasDeFacturas(
92	IDFactura 		Smallint Constraint fk_Numfactura References facturas on Delete Cascade,
93	Articulo 		VarChar (20),
94	RefFamilia 		Smallint,
95	Color 			VarChar (20),
96	Cantidad 		Numeric (2) default 1 not null,
97	Precio 			Numeric(5,2) not null,
98	Constraint fk_ArticuloRColor foreign key (Articulo, RefFamilia, Color) References Productos on Update Cascade,
99	Constraint pk_compra Primary key (IDfactura, Articulo, RefFamilia, Color)
100);
101Create table clienteytipo(
102	CIF 		VarChar(9) References clientes on Update Cascade,
103	AcronimoC 	VarChar(3) References tiposCliente on Update Cascade,
104	Constraint pk_clienteytipo Primary key (CIF, AcronimoC)
105);
106Create table clienteformas(
107	CIF 		VarChar(9) References clientes on Update Cascade,
108	AcronimoP 	VarChar(3) References formasPago on Update Cascade,
109	Constraint pk_clienteformas Primary key (CIF, AcronimoP)
110);
111Create table contactos(
112	DNIcom VarChar(9) constraint FK_com references comerciales,
113	CIFcli Varchar(9) constraint FK_cli references clientes,
114	fechaVis date default current_date,
115	constraint PK_contactos primary key(DNIcom,CIFcli,fechaVis)
116	
117);
118
119
120
121insert into formasPago values ('TarjetaCredito','TC');
122insert into formasPago values ('AlContado','ACT');
123insert into formasPago values ('Metalico','MET');
124insert into formasPago values ('PayPal','PP');
125insert into comerciales values ('R95175364','Pepe','Garcia','Majo',987654321,'pepeelgrande@gmail.com');
126insert into comerciales values ('R95174364','Luis','Garcia','Majo',987654321,'pepeelgrande@gmail.com');
127insert into comerciales values ('R95173364','Adolfo','Garcia','Majo',987654321,'pepeelgrande@gmail.com');
128insert into tiposCliente values ('Pequeño','P','R95175364');
129insert into tiposCliente values ('Grande','G','R95174364');
130insert into tiposCliente values ('Medio','M','R95173364');
131insert into provincia values ('BU','Burgos');
132insert into provincia values ('ZA','Zamora');
133insert into provincia values ('SO','Soria');
134insert into provincia values ('LE','Leon');
135insert into ciudades values ('Benavente','ZA');
136insert into ciudades values ('Toro','ZA');
137insert into ciudades values ('Aranda','BU');
138insert into ciudades values ('Miranda','BU');
139insert into clientes values ('A09652345','Mangel','Rogel','CalleFalsa 123',987654321,56419876544569845,958746312,'mangelrogel@gmail.com','TC','Benavente','ZA');
140insert into clientes values ('A09627845','Perico','Palotes','CalleCierta 456',987654121,56413164944569845,957516312,'pericopalotes@gmail.com','ACT','Aranda','BU');
141insert into clientes values ('A09675345','Manolo','eldelBombo','CalleIncierta 789',987651231,56413167895569845,957741312,'manolobombon@gmail.com','MET','Miranda','BU');
142insert into facturas values (5,'09-05-2008','A09652345','TC');
143insert into facturas values (94456,'15-05-2008','A09627845','ACT');
144insert into facturas values (65412,'22-05-2008','A09675345','MET');
145insert into color values ('Rojo',234,023,045);
146insert into color values ('Azul',034,023,255);
147insert into color values ('Verde',065,245,012);
148insert into productos values ('Cajonera',1,'Rojo',12,'Cajonera para colocar en un salon',50);
149insert into productos values ('Maceta',2,'Verde',52,'Maceta de jardin de color verde camuflaje',90);
150insert into productos values ('Baldosa',3,'Azul',2,'Baldosa de piscina, color azul',10);
151insert into lineasdefacturas values (5,'Cajonera',1,'Rojo',5,654.95);
152insert into lineasdefacturas values (5,'Maceta',2,'Verde',9,126.90);
153insert into lineasdefacturas values (5,'Baldosa',3,'Azul',7,54.99);
154insert into clienteytipo values ('A09652345','M');
155insert into clienteytipo values ('A09627845','P');
156insert into clienteytipo values ('A09675345','G');
157insert into clienteytipo values ('A09652345','G');
158insert into clienteytipo values ('A09627845','M');
159insert into clienteytipo values ('A09675345','P');
160insert into clienteformas values ('A09652345','TC');
161insert into clienteformas values ('A09627845','ACT');
162insert into clienteformas values ('A09675345','MET');
163insert into clienteformas values ('A09652345','MET');
164insert into clienteformas values ('A09627845','TC');
165insert into clienteformas values ('A09675345','ACT');
166
167
168--select AVG(lineasdefacturas.precio) from (lineasdefacturas join facturas using(IDFactura)) natural join clientes where clientes.ciudad='Burgos'
169
170--select SUM(lineasdefacturas.precio) 
171--from ((lineasdefacturas join facturas using(IDFactura)) natural join clientes) natural join clienteytipo 
172--where AcronimoC='M' and (facturas.fecha >= '01-01-2012' and facturas.fecha <= '31-12-2012')
173
174
175select count(*)
176from (tiposCliente inner join comerciales on comercial=DNI) where comercial='M'