Examen DAC - 2do Trimestre - 08/02/12

Lobo14

Bueno pues voy a poner el primer examen de este segundo trimestre.
El examen es de la parte de PL/SQL, y he sacado un 6. No es una gran nota, pero considerando que solo hemos aprobado dos de toda la clase, no está mal del todo...
Examen DAC - 2do Trimestre - PL/SQL
Aquí tenéis el script por si queréis importarlo para hacer el examen:
ALTER SESSION SET NLS_DATE_FORMAT=&#39;YYYY/MM/DD HH24:MI:SS&#39;;

DROP TABLE usuario CASCADE CONSTRAINTS;
DROP TABLE articulo CASCADE CONSTRAINTS;
DROP TABLE transaccion CASCADE CONSTRAINTS;
DROP TABLE voto CASCADE CONSTRAINTS;
DROP TABLE puja CASCADE CONSTRAINTS;
DROP TABLE tienda CASCADE CONSTRAINTS;
DROP TABLE tienda_articulo CASCADE CONSTRAINTS;

CREATE TABLE usuario
(
nickname		VARCHAR2(25)	PRIMARY KEY,
nombre			VARCHAR2(25)	NOT NULL,
apellido1		VARCHAR2(25)	NOT NULL,
apellido2		VARCHAR2(25)	NOT NULL,
direc_calle		VARCHAR2(50)	NOT NULL,
direc_numero	VARCHAR2(6)		NOT NULL,
direc_CP		NUMBER(6,0)		NOT NULL,
direc_pais		VARCHAR2(25)	NOT NULL,
fecha_alta		DATE			NOT NULL
);


CREATE TABLE articulo
(
ref_arti		VARCHAR2(9)		PRIMARY KEY,
nick_vendedor	VARCHAR2(25)	NOT NULL,
nombre			VARCHAR2(50)	NOT NULL,
descripcion		VARCHAR2(200),
unidades		NUMBER(3,0)		NOT NULL,
precio_salida	NUMBER(7,2)		NOT NULL,
ubicacion_CP	NUMBER(6,0)		NOT NULL,
ubic_pais		VARCHAR2(25)	NOT NULL,
fecha_inicio	DATE			NOT NULL,
fecha_fin		DATE			NOT NULL,
FOREIGN KEY (nick_vendedor) REFERENCES usuario(nickname) ON DELETE CASCADE,
UNIQUE (nick_vendedor, nombre)
);

CREATE TABLE transaccion
(
codigo			VARCHAR2(9)		PRIMARY KEY,
nick_comprador	VARCHAR2(25)	NOT NULL,
ref_articulo	VARCHAR2(9)		NOT NULL,
fecha			DATE			NOT NULL,
precio_final	NUMBER(7,2)		NOT NULL,
unidades		NUMBER(3,0)		NOT NULL,
FOREIGN KEY (nick_comprador) REFERENCES usuario(nickname) ON DELETE CASCADE,
FOREIGN KEY (ref_articulo) REFERENCES articulo(ref_arti) ON DELETE CASCADE
);

CREATE TABLE voto
(
codigo				VARCHAR2(9)		PRIMARY KEY,
nick_emisor			VARCHAR2(25)	NOT NULL,
nick_receptor		VARCHAR2(25)	NOT NULL,
cod_transaccion		VARCHAR2(9)		NOT NULL,
fecha				DATE			NOT NULL,
valor				VARCHAR2(8)		CHECK(valor IN(&#39;positivo&#39;,&#39;negativo&#39;)),
comentario			VARCHAR2(200),
FOREIGN KEY (nick_emisor) REFERENCES usuario(nickname) ON DELETE CASCADE,
FOREIGN KEY (nick_receptor) REFERENCES usuario(nickname) ON DELETE CASCADE,
FOREIGN KEY (cod_transaccion) REFERENCES transaccion(codigo) ON DELETE CASCADE,
UNIQUE (nick_emisor, nick_receptor, cod_transaccion)
);

CREATE TABLE puja
(
codigo			VARCHAR2(9)		PRIMARY KEY,
nick_pujador	VARCHAR2(25)	NOT NULL,
ref_articulo	VARCHAR2(9)		NOT NULL,
unidades		NUMBER(3,0)		NOT NULL,
puja_unidad		NUMBER(7,2)		NOT NULL,
fecha			DATE			NOT NULL,
FOREIGN KEY (nick_pujador) REFERENCES usuario(nickname) ON DELETE CASCADE,
FOREIGN KEY (ref_articulo) REFERENCES articulo(ref_arti) ON DELETE CASCADE
);

CREATE TABLE tienda
(
codigo				VARCHAR2(9)		PRIMARY KEY,
nick_propietario	VARCHAR2(25)	NOT NULL,
nombre				VARCHAR2(25)	NOT NULL,
descripcion			VARCHAR2(200),
fecha_alta			DATE			NOT NULL,
FOREIGN KEY (nick_propietario) REFERENCES usuario(nickname) ON DELETE CASCADE,
UNIQUE (nick_propietario),
UNIQUE (nombre)
);

CREATE TABLE tienda_articulo
(
codigo_tienda	VARCHAR2(9),
ref_articulo	VARCHAR2(9),
PRIMARY KEY (codigo_tienda,ref_articulo),
FOREIGN KEY (codigo_tienda) REFERENCES tienda(codigo),
FOREIGN KEY (ref_articulo) REFERENCES articulo(ref_arti)
);


INSERT INTO usuario VALUES (&#39;natalia_rm&#39;, &#39;Natalia&#39;, &#39;Ruiz&#39;, &#39;Martinez&#39;, &#39;C/ central&#39;, &#39;14&#39;, 11002, &#39;Spain&#39;, &#39;2007/09/20 15:11:54&#39;);
INSERT INTO usuario VALUES (&#39;jermy99&#39;, &#39;Juan Carlos&#39;, &#39;Garcia&#39;, &#39;Gomez&#39;, &#39;C/ vega &#39;, &#39;43&#39;, 11101, &#39;Spain&#39;, &#39;2007/07/20 15:19:15&#39;);
INSERT INTO usuario VALUES (&#39;klom_54&#39;, &#39;Jose Maria&#39;, &#39;Caballero&#39;, &#39;Sanchez&#39;, &#39;C/ andalus&#39;, &#39;5&#39;, 11101, &#39;Spain&#39;, &#39;2008/02/01 15:21:16&#39;);
INSERT INTO usuario VALUES (&#39;campeon&#39;, &#39;Brush&#39;, &#39;Threepwood&#39;, &#39;Threepwood&#39;, &#39;C/ ron gilbert&#39;, &#39;101&#39;, 333455, &#39;Jamaica&#39;, &#39;2007/07/19 15:22:21&#39;);
INSERT INTO usuario VALUES (&#39;alberto85&#39;, &#39;Alberto&#39;, &#39;Ortega&#39;, &#39;Cala&#39;, &#39;C/ pico del oro&#39;, &#39;8&#39;, 12345, &#39;Spain&#39;, &#39;2007/02/04 15:30:03&#39;);
INSERT INTO usuario VALUES (&#39;anaMar06&#39;, &#39;Ana Maria&#39;, &#39;Forja&#39;, &#39;Carrion&#39;, &#39;C/ manuel de falla&#39;, &#39;54&#39;, 11101, &#39;Spain&#39;, &#39;2007/05/08 15:40:26&#39;);
INSERT INTO usuario VALUES (&#39;holmes2000&#39;, &#39;Arthur&#39;, &#39;Conan&#39;, &#39;Doyle&#39;, &#39;C/ Baker Street&#39;, &#39;221b&#39;, 75, &#39;UK&#39;, &#39;2007/04/14 15:42:33&#39;);
INSERT INTO usuario VALUES (&#39;red2001&#39;, &#39;Rocio&#39;, &#39;Gil&#39;, &#39;Galeano&#39;, &#39;C/ Nueva &#39;, &#39;43 6-C&#39;, 12003, &#39;Spain&#39;, &#39;2007/11/15 15:46:43&#39;);
INSERT INTO usuario VALUES (&#39;manolito_32&#39;, &#39;Manuel&#39;, &#39;Yabe&#39;, &#39;De los Reyes&#39;, &#39;C/ Gran via&#39;, &#39;321&#39;, 12203, &#39;Spain&#39;, &#39;2005/02/13 15:48:02&#39;);
INSERT INTO usuario VALUES (&#39;JJ_Garcia&#39;, &#39;Juan Jose&#39;, &#39;Garcia&#39;, &#39;Jimenez&#39;, &#39;C/ paseo maritimo&#39;, &#39;3&#39;, 11101, &#39;Spain&#39;, &#39;2006/01/21 15:49:47&#39;);


INSERT INTO articulo VALUES (&#39;0000100C&#39;, &#39;campeon&#39;, &#39;La Fuga de Monkey Island&#39;, &#39;Edicion limitada del popular videojuego.&#39;, 2, 25, 333455, &#39;Jamaica&#39;, &#39;2008/01/13 16:00:00&#39;, &#39;2008/01/20 16:00:00&#39;);
INSERT INTO articulo VALUES (&#39;0000100B&#39;, &#39;campeon&#39;, &#39;The Secret of Monkey Island&#39;, &#39;Edicion limitada del popular videojuego.&#39;, 1, 45, 333455, &#39;Jamaica&#39;, &#39;2008/01/13 16:00:00&#39;, &#39;2008/01/20 16:00:00&#39;);
INSERT INTO articulo VALUES (&#39;0000100A&#39;, &#39;campeon&#39;, &#39;The Curse of Monkey Island&#39;, &#39;Edicion limitada del popular videojuego.&#39;, 5, 45, 333455, &#39;Jamaica&#39;, &#39;2008/01/13 16:00:00&#39;, &#39;2008/01/20 16:00:00&#39;);
INSERT INTO articulo VALUES (&#39;0000201A&#39;, &#39;natalia_rm&#39;, &#39;SONY DSC-w50&#39;, &#39;Camara digital de fotos de 6.1 Megapixel. Incluye memoria.&#39;, 5, 150, 12000, &#39;Spain&#39;, &#39;2008/01/27 16:28:53&#39;, &#39;2008/01/31 16:29:02&#39;);
INSERT INTO articulo VALUES (&#39;0000202A&#39;, &#39;natalia_rm&#39;, &#39;CANON EOS 30D&#39;, &#39;Camara de fotos de 10 Megapixel. Nueva.&#39;, 1, 250, 12000, &#39;Spain&#39;, &#39;2008/01/27 16:28:53&#39;, &#39;2008/01/31 16:29:02&#39;);
INSERT INTO articulo VALUES (&#39;0000320F&#39;, &#39;jermy99&#39;, &#39;Postal Cadiz 1955&#39;, &#39;Postal de la playa de la caleta de 1955&#39;, 1, 230, 11101, &#39;Spain&#39;, &#39;2007/08/12 16:41:42&#39;, &#39;2007/08/20 16:41:42&#39;);
INSERT INTO articulo VALUES (&#39;0000455V&#39;, &#39;klom_54&#39;, &#39;Pack sellos 120&#39;, &#39;Coleccion de 120 sellos de 1950 a 1980&#39;, 1, 550, 11101, &#39;Spain&#39;, &#39;2007/05/01 16:44:11&#39;, &#39;2007/05/31 16:44:22&#39;);
INSERT INTO articulo VALUES (&#39;0000501A&#39;, &#39;alberto85&#39;, &#39;Soldados de plomo 30&#39;, &#39;30 figuritas de soldados de plomo&#39;, 1, 1300, 12345, &#39;Spain&#39;, &#39;2008/01/01 16:28:53&#39;, &#39;2008/01/12 16:29:02&#39;);

INSERT INTO transaccion VALUES (&#39;0001A&#39;, &#39;manolito_32&#39;, &#39;0000455V&#39;, &#39;2005/05/13 15:06:00&#39;, 720, 1);
INSERT INTO transaccion VALUES (&#39;0002A&#39;, &#39;JJ_Garcia&#39;, &#39;0000202A&#39;, &#39;2008/01/30 17:20:24&#39;, 275, 1);
INSERT INTO transaccion VALUES (&#39;0003A&#39;, &#39;jermy99&#39;, &#39;0000201A&#39;, &#39;2008/01/29 18:18:13&#39;, 200, 2);
INSERT INTO transaccion VALUES (&#39;0003B&#39;, &#39;holmes2000&#39;, &#39;0000201A&#39;, &#39;2008/01/30 08:19:48&#39;, 225, 1);
INSERT INTO transaccion VALUES (&#39;0004A&#39;, &#39;holmes2000&#39;, &#39;0000100A&#39;, &#39;2008/01/18 08:32:02&#39;, 55, 1);
INSERT INTO transaccion VALUES (&#39;0005A&#39;, &#39;alberto85&#39;, &#39;0000100C&#39;, &#39;2008/01/30 18:02:05&#39;, 50, 2);



INSERT INTO voto VALUES (&#39;001&#39;, &#39;manolito_32&#39;, &#39;klom_54&#39;, &#39;0001A&#39;, &#39;2007/06/13 17:10:47&#39;, &#39;positivo&#39;, &#39;El paquete ha llegado correctamente.&#39;);
INSERT INTO voto VALUES (&#39;002&#39;, &#39;JJ_Garcia&#39;, &#39;natalia_rm&#39;, &#39;0002A&#39;, &#39;2008/01/31 09:20:56&#39;, &#39;negativo&#39;, &#39;El precio no incluia los gastos de envio. &#39;);
INSERT INTO voto VALUES (&#39;003&#39;, &#39;klom_54&#39;, &#39;manolito_32&#39;, &#39;0001A&#39;, &#39;2007/06/14 20:43:21&#39;, &#39;positivo&#39;, &#39;El ingreso se realizo desde el primer dia&#39;);
INSERT INTO voto VALUES (&#39;010&#39;, &#39;holmes2000&#39;, &#39;natalia_rm&#39;, &#39;0003A&#39;, &#39;2008/02/02 18:33:04&#39;, &#39;negativo&#39;, &#39;El paquete no llego en buenas condiciones&#39;);
INSERT INTO voto VALUES (&#39;011&#39;, &#39;jermy99&#39;, &#39;natalia_rm&#39;, &#39;0003B&#39;, &#39;2008/02/04 18:33:08&#39;, &#39;&#39;, &#39;Todo fue a la perfeccion.&#39;);
INSERT INTO voto VALUES (&#39;012&#39;, &#39;campeon&#39;, &#39;holmes2000&#39;, &#39;0004A&#39;, &#39;2008/01/19 18:47:35&#39;, &#39;positivo&#39;, &#39;Todo correctamente&#39;);
INSERT INTO voto VALUES (&#39;013&#39;, &#39;holmes2000&#39;, &#39;campeon&#39;, &#39;0004A&#39;, &#39;2008/01/20 16:08:35&#39;, &#39;positivo&#39;, &#39;Recibida la transaccion.&#39;);
INSERT INTO voto VALUES (&#39;014&#39;, &#39;alberto85&#39;, &#39;campeon&#39;, &#39;0005A&#39;, &#39;2008/01/30 19:18:21&#39;, &#39;positivo&#39;, NULL);


INSERT INTO puja VALUES (&#39;001&#39;, &#39;holmes2000&#39;, &#39;0000455V&#39;, 1, 570, &#39;2005/05/11 17:03:11&#39;);
INSERT INTO puja VALUES (&#39;002&#39;, &#39;red2001&#39;, &#39;0000455V&#39;, 1, 600, &#39;2005/05/11 17:03:11&#39;);
INSERT INTO puja VALUES (&#39;003&#39;, &#39;manolito_32&#39;, &#39;0000455V&#39;, 1, 720, &#39;2005/05/13 15:06:00&#39;);
INSERT INTO puja VALUES (&#39;004&#39;, &#39;anaMar06&#39;, &#39;0000320F&#39;, 1, 250, &#39;2007/08/13 17:17:54&#39;);
INSERT INTO puja VALUES (&#39;005&#39;, &#39;JJ_Garcia&#39;, &#39;0000202A&#39;, 1, 255, &#39;2008/01/30 17:20:24&#39;);
INSERT INTO puja VALUES (&#39;006&#39;, &#39;jermy99&#39;, &#39;0000201A&#39;, 2, 200, &#39;2008/01/29 18:18:13&#39;);
INSERT INTO puja VALUES (&#39;007&#39;, &#39;holmes2000&#39;, &#39;0000201A&#39;, 1, 225, &#39;2008/01/30 08:19:48&#39;);
INSERT INTO puja VALUES (&#39;008&#39;, &#39;holmes2000&#39;, &#39;0000100A&#39;, 1, 50, &#39;2008/01/17 18:41:02&#39;);
INSERT INTO puja VALUES (&#39;009&#39;, &#39;alberto85&#39;, &#39;0000100A&#39;, 1, 47, &#39;2008/01/18 12:22:19&#39;);
INSERT INTO puja VALUES (&#39;010&#39;, &#39;klom_54&#39;, &#39;0000100B&#39;, 1, 47, &#39;2008/01/16 16:17:00&#39;);
INSERT INTO puja VALUES (&#39;011&#39;, &#39;alberto85&#39;, &#39;0000100C&#39;, 2, 50, &#39;2008/01/15 19:02:09&#39;);
INSERT INTO puja VALUES (&#39;012&#39;, &#39;anaMar06&#39;, &#39;0000100C&#39;, 1, 30, &#39;2008/01/16 21:02:13&#39;);
INSERT INTO puja VALUES (&#39;013&#39;, &#39;red2001&#39;, &#39;0000100C&#39;, 2, 32, &#39;2008/01/16 23:24:18&#39;);

INSERT INTO tienda VALUES (&#39;00001&#39;, &#39;campeon&#39;, &#39;LeChuck Shop&#39;, &#39;Videojuegos de segunda mano&#39;, &#39;2006/02/08 16:07:53&#39;);
INSERT INTO tienda VALUES (&#39;00002&#39;, &#39;natalia_rm&#39;, &#39;Digital Shop&#39;, &#39;Todo sobre el mundo de las videocamaras.&#39;, &#39;2008/01/21 16:07:53&#39;);

INSERT INTO tienda_articulo VALUES (&#39;00001&#39;, &#39;0000100A&#39;);
INSERT INTO tienda_articulo VALUES (&#39;00001&#39;, &#39;0000100B&#39;);
INSERT INTO tienda_articulo VALUES (&#39;00001&#39;, &#39;0000100C&#39;);
INSERT INTO tienda_articulo VALUES (&#39;00002&#39;, &#39;0000201A&#39;);
INSERT INTO tienda_articulo VALUES (&#39;00002&#39;, &#39;0000202A&#39;);
Pues ahí está. Espero que a alguien le pueda ser de utilidad.
Un saludo!