• Author / Uploaded
• Ezequiel Hernán Villanueva

Citation preview

Introduction to Oracle: SQL and PL/SQL, Instructor Guide, Volumen 1

Práctica 15: Taller de SQL --------------------------------------------------------------------

Ejercicio 1: Cree las tablas basadas en los diagramas de instancia de tablas siguient es. Elija los tipos de datos apropiados y asegúrese de agregar las restricciones de integridad. Ejercicio 1.a

Nombre de la tabla:

MEMBER

Diagrama de Instancia de la Tabla MEMBER ---------------------------------------ME

ADDRESS

NOMBRE_COLUMNA: CITY TIPO_DE_CLAVE: NULL/UNIQUE:

MEMBER_ID PHONE PK NN, U

LAST_NAME JOIN_DATE

FIRST_NA

NN

NN VALOR_PREDEFINIDO: FECHA DEL SISTEMA TIPO_DE_DATO: NUMBER VARCHAR2 VARCHAR2 VARCHAR2 DATE TAMAÑO: 10 100 30 15 SQL>

VARCHAR2

VARCHAR2

25

25

CREATE TABLE MEMBER ( MEMBER_ID NUMBER( 10 ), LAST_NAME VARCHAR2( 25 )

NOT NULL

, FIRST_NAME

VARCHAR2( 25 )

NOT NULL

ADDRESS CITY PHONE JOIN_DATE

VARCHAR2( 100 ), VARCHAR2( 30 ), VARCHAR2( 15 ), DATE DEFAULT SYSDATE

NOT NULL

CONSTRAINT

MEMBER_MEMBER_ID_PK

PRIMARY

,

, KEY( MEMBER_ID ) ); SQL>

save p15_1_a.sql

Nota: Al definir la Restricción de Clave Primaria sobre la columna MEMBER_ID, no hace falta volver a definir la otra restricción de clave única. Ejercicio 1.b: Nombre de la tabla:

TITLE

Diagrama de Instancia de la Tabla TITLE ---------------------------------------ION

RATING

NOMBRE_COLUMNA: CATEGORY TIPO_DE_CLAVE: NULL/UNIQUE:

TITLE_ID RELEASE_DATE PK NN, U

TITLE

DESCRIPT

NN

NN

G, PG, R,

CHECK: DRAMA,

NC17, NR

COMEDY,

ACTION, CHILD, SCIFI, DOCUMENTARY VARCHAR2 4

TIPO_DE_DATO: VARCHAR2 TAMAÑO: 20 SQL>

NUMBER

VARCHAR2

VARCHAR2

10

60

400

DATE

CREATE TABLE TITLE TITLE_ID

( NUMBER( 10 )

NOT NULL

TITLE

VARCHAR2( 60 )

NOT NULL

DESCRIPTION

VARCHAR2( 400 )

NOT NULL

RATING CATEGORY RELEASE_DATE CONSTRAINT

VARCHAR2( 4 ), VARCHAR2( 20 ), DATE, TITLE_TITLE_ID_PK

PRIMARY

, , ,

KEY( TITLE_ID ), CONSTRAINT TITLE_RATING_CK CHECK( R IN ('G', 'PG', 'R', 'NC17', 'NR' ) ), CONSTRAINT TITLE_CATEGORY_CK CHECK( C ATEGORY IN ('DRAMA', 'COMEDY', 'ACTION', 'CHILD', 'SCIFI', 'DOCUMENTARY') ) ); ATING

SQL>

save p15_1_b.sql

Ejerci cio 1.c: Nombre de la tabla:

TITLE_COPY

Diagrama de Instancia de la Tabla TITLE_COPY -------------------------------------------NOMBRE_COLUMNA:

COPY_ID

TITLE_ID

TIPO_DE_CLAVE: NULL/UNIQUE:

PK NN, U

PK, FK NN, U

NUMBER

TITLE TITLE_ID NUMBER

STATUS NN CHECK: AVAILABLE, DESTROYED, RENTED, RESERVED FK_TABLA_REFERENCIADA: FK_COLUMNA_REFERENCIADA: TIPO_DE_DATO: VARCHAR2

TAMAÑO:

10

10

15 SQL>

CREATE TABLE TITLE_COPY ( COPY_ID NUMBER( 10 ) NOT NULL, TITLE_ID NUMBER( 10 ) NOT NULL CONSTRAINT TITLE_COPY_TITLE_ID_FK REFERENCES TITLE( TITLE_ID ), STATUS VARCHAR2( 15 ) NOT NULL, CONSTRAINT TITLE_COPY_COPY_ID_TITLE_ID_PK PRIMARY KEY ( COPY_ID, TITLE_ID ), CONSTRAINT TITLE_COPY_STATUS_CK CHECK( STATUS IN ('AVAILABLE', 'DESTROYED', 'RENTED', 'RESERVED') ) ); SQL>

save p15_1_c.sql

Ejercicio 1.d: Nombre de la tabla:

RENTAL

Diagrama de Instancia de la Tabla RENTAL -------------------------------------------D

COPY_ID

NOMBRE_COLUMNA: ACT_RET_DATE TIPO_DE_CLAVE:

BOOK_DATE EXP_RET_DATE PK

PK, FK2

MEMBER_I TITLE_ID PK, FK1 PK, FK2

VALOR_PREDEFINIDO: Fecha de Sistema + 2 días FK_TABLA_REFERENCIADA: TITLE_COPY FK_COLUMNA_REFERENCIADA: D COPY_ID TIPO_DE_DATO: NUMBER DATE DATE TAMAÑO: 10 SQL>

CREATE TABLE RENTAL ( BOOK_DATE MEMBER_ID COPY_ID ACT_RET_DATE EXP_RET_DATE

Fecha de Sistema

DATE

DATE NUMBER( 10 ), NUMBER( 10 ), DATE, DATE

MEMBER TITLE_COPY MEMBER_I TITLE_ID NUMBER NUMBER 10 10

DEFAULT SYSDATE,

DEFAULT (SYSDATE

+ 2), TITLE_ID NUMBER( 10 ), CONSTRAINT RENTAL_BD_MI_CI_TI_PK PRIMARY KEY( BOOK_DATE, MEMBER_ID, COPY_ID, TITLE_ID ), CONSTRAINT RENTAL_MEMBER_ID_FK_1 FOREIGN KEY( MEMBER_ID ) REFERENCES MEMBER ( MEMBER_ID ), CONSTRAINT RENTAL_COPY_ID_TITLE_ID_FK_2 FOREIGN KEY( COPY_ID, TITLE_ID ) REFERENCES TITLE_COPY( COPY_ID, TITLE_ID ) ); SQL> save p15_1_d.sql ________________________________

---------------------------------Ejercicio 4:

...Falta texto...

Ejercicio 4.a: Inserte datos en la Tabla TITLE, de acuerdo a la siguiente tabla . Use una Secuencia para la columna TITLE_ID. DATOS DE LA TABLA TITLE: -----------------------CATEGORY -------CHILD

SCIFI

TITLE RELEASE_DATE ---------------Willie and 05-OCT-1995 Christmas Too

DESCRIPTION

RATING

-----------

------

Todos los Amigos de Willie

G

Alien Again 19-MAY-1995

Otra instalación de historia

hacen una lista de navidad para Papa Noel, pero Willie todavía no ha añadido su deseo en la lista. R

de Ciencia Ficción. La heroína puede salvar al planeta de la amenaza de la forma de vida extraterrestre. SCIFI

The Glob 12-AUG-1995

Un meteoro cae cerca de un

NR

pequeño pueblo Americano y libera basura cósmica carnívora en este clásico. COMEDY

My Day Off 12-JUL-1995

Con un poco de suerte y mucha

PG

ingenuidad, un adolescente falta a clases por un día en Nueva York. DRAMA

Miracles 12-SEP-1995 On Ices

Una niña de 6 años tiene dudas PG

Soda Gang

Después de descubrir una

acerca de la existencia de Papa Noel, pero descubre que el milagro realmente existe. NR

01-JUN-1995 cantidad de drogas, un joven se encuentra enganchado y adicto y se vuelve un ganster mafioso. SQL>

CREATE SEQUENCE INCREMENT BY START WITH

SQL>

INSERT INTO VALUES (

TITLE

TITLE_TITLE_ID_SEQ 1 1;

ACTION

TITLE_TITLE_ID_SEQ.NEXTVAL, 'Willie and Christmas Too', 'Todos los Amigos de Willie hacen una li sta de navidad para Papa Noel, pero Willie todavía no ha añadido su deseo en la list a.', 'G', 'CHILD', '05-OCT-1995' ); SQL>

save p15_4_a_1.sql

SQL>

INSERT INTO VALUES (

TITLE

TITLE_TITLE_ID_SEQ.NEXTVAL, 'Alien Again', 'Otra instalación de historia de Ciencia F icción. La heroína puede salvar al planeta de la amenaza de la forma de vida extrate rrestre.', 'R', 'SCIFI', '19-MAY-1995' ); SQL>

save p15_4_a_2.sql

SQL>

INSERT INTO VALUES (

TITLE

TITLE_TITLE_ID_SEQ.NEXTVAL, 'The Glob', 'Un meteoro cae cerca de un pequeño pueblo Americano y libera basura cósmica carnívora en este clásico.', 'NR', 'SCIFI', '12-AGO-1995' ); SQL>

save

p15_4_a_3.sql

SQL>

INSERT INTO VALUES (

TITLE

TITLE_TITLE_ID_SEQ.NEXTVAL, 'My Day Off', 'Con un poco de suerte y mucha ingenuida d, un adolescente falta a clases por un día en Nueva York.', 'PG', 'COMEDY', '12-JUL-1995' ); SQL>

save p15_4_a_4.sql

SQL>

INSERT INTO VALUES (

TITLE

TITLE_TITLE_ID_SEQ.NEXTVAL, 'Miracles on Ice', 'Una niña de 6 años tiene dudas acerca de la existencia de Papa Noel, pero descubre que el milagro realmente existe.', 'PG', 'DRAMA',

'12-SEP-1995' ); SQL>

save

p15_4_a_5.sql

SQL>

INSERT INTO VALUES (

TITLE

TITLE_TITLE_ID_SEQ.NEXTVAL, 'Soda Gang', 'Después de descubrir una cantidad de drog as, un joven se encuentra enganchado y adicto y se vuelve un ganster mafioso.', 'NR', 'ACTION', '01-JUN-1995' ); SQL>

save

p15_4_a_6.sql

Ejercicio 4.b: Agregue los datos a la tabla MEMBER. Escriba un archivo de coman dos (Script) nombrándolo p15q4b.sql para solicitarle en pantalla, la información al usuario. Ejecute el archivo de comandos. Asegúrese de usar una secuencia para añadir los números de miembros. LAST_NAME FIRST_NAME JOINT_DATE ------------------------------Velasquez Carmen 206-899-6666 08-MAR-1990 Ngao LaDoris va 586-355-8882 08-MAR-1990 Nagayama Midori o 254-852-5764 17-JUN-1991 Quick-to-See Mark 63-559-7777 07-ABR-1990 Ropeburn Audry g 41-559-87 18-ENE-1991 Urguhart Molly 418-542-9988 18-ENE-1991

ADDRESS

CITY

-------

----

283 King Street

Seatle

5 Mondrany

Bratisla

68 Via Centrale

Sao Paol

6921 King Way

Lagos

86 Chu Street

Hong Kon

3035 Laurier

Quebec

PHONE

SQL>

CREATE SEQUENCE INCREMENT BY START WITH

SQL>

edit

MEMBER_MEMBER_ID_SEQ 1 1;

p15q4b.sql

Contenido del Archivo de comandos: p15q4b.sql --------------------------------------------" ACCEPT last_name PROMPT 'Ingrese el Apellido: ' ACCEPT first_name PROMPT 'Ingrese el Nombre: ' ACCEPT address PROMPT 'Ingrese el domicilio: ' ACCEPT city PROMPT 'Ingrese la ciudad: ' ACCEPT phone PROMPT 'Ingrese el número de teléfono: ' ACCEPT joint_date PROMPT 'Ingrese la fecha de ingreso: ' INSERT INTO MEMBER VALUES ( MEMBER_MEMBER_ID_SEQ.NEXTVAL, '&last_name',

'&first_name', '&address', '&city', '&phone', '&joint_date' ); " SQL>

start

p15q4b.sql

SQL>

start

p15q4b.sql

SQL>

start

p15q4b.sql

SQL>

start

p15q4b.sql

SQL>

start

p15q4b.sql

SQL>

start

p15q4b.sql

Ejercicio 4.c: Agregue los datos de las copias de películas en la tabla TITLE_COP Y Nota: Tenga disponible los valores de la columna TITLE_ID de la tabla TITLE para realizar este ejercicio. Datos de la tabla: TITLE_COPY ----------------------------COPY_ID

TITLE

===>

TITLE_ID

-------

-----

--------

1

Willie and Christmas Too

1

1

Alien Again

2

2

Alien Again

2

1

The Glob

4

1

My Day Off

5

2

My Day Off

5

3

My Day Off

5

1

Miracles On Ice

6

1

Soda Gang

7

STATUS -----AVAILABLE AVAILABLE RENTED AVAILABLE AVAILABLE AVAILABLE RENTED AVAILABLE AVAILABLE SQL> TITLE_ID -------1 2 4

SELECT FROM

TITLE_ID, TITLE TITLE; TITLE ----Willie and Christmas Too Allien Again The Glob

5 6 7

My Day Off Miracles on Ice Soda Gang

SQL>

INSERT INTO TITLE_COPY VALUES ( 1, 1, 'AVAILABLE' );

SQL>

save

SQL>

INSERT INTO TITLE_COPY VALUES ( 1, 2, 'AVAILABLE' );

SQL>

save

SQL>

INSERT INTO TITLE_COPY VALUES ( 2, 2, 'RENTED' );

SQL>

save

SQL>

INSERT INTO TITLE_COPY VALUES ( 1, 4, 'AVAILABLE' );

SQL>

save

SQL>

INSERT INTO TITLE_COPY VALUES ( 1, 5, 'AVAILABLE' );

SQL>

save

SQL>

INSERT INTO TITLE_COPY VALUES ( 2, 5, 'AVAILABLE' );

SQL>

save

SQL>

INSERT INTO TITLE_COPY VALUES ( 3, 5, 'RENTED' );

SQL>

save

SQL>

INSERT INTO TITLE_COPY VALUES ( 1, 6, 'AVAILABLE');

SQL>

save

SQL>

INSERT INTO TITLE_COPY VALUES ( 1, 7, 'AVAILABLE');

SQL>

save

p15q4c_1.sql

p15q4c_2.sql

p15q4c_3.sql

p15q4c_4.sql

p15q4c_5.sql

p15q4c_6.sql

p15q4c_7.sql

p15q4c_8.sql

p15q4c_9.sql

Ejercicio 4.d: Agregue los datos de alquileres siguientes a la tabla RENTAL. Nota: Los números de los títulos y de los miembros pueden ser dife rentes dependiendo de los números de la secuencia. Datos de la tabla RENTAL -----------------------EXP_RET_DATE ------------

BOOK_DATE TITLE_ID ----------------

MEMBER_ID

COPY_ID

ACT_RET_DATE

---------

-------

------------

18-DIC-2010 20-DIC-2010 19-DIC-2010 17-DIC-2010

16-DIC-2010 1 18-DIC-2010 2 17-DIC-2010 5 15-DIC-2010 7

1

1

1

2

2

3

6

1

17-DIC-2010

17-DIC-2010

SQL>

INSERT INTO RENTAL VALUES ('16-DIC-2010', 1, 1, '17-DIC-2010', '18-DIC-201

SQL>

save p15q4d_1.sql

SQL>

INSERT INTO RENTAL VALUES ('18-DIC-2010', 1, 2,NULL ,'20-DIC-2010', 2);

SQL>

save p15q4d_2.sql

SQL>

INSERT INTO RENTAL VALUES ('17-DIC-2010', 2, 3, NULL, '19-DIC-2010', 5);

SQL>

save p15q4d_3.sql

SQL>

INSERT INTO RENTAL VALUES ('15-DIC-2010', 6, 1, '17-DIC-2010', '17-DIC-201

SQL>

save

0', 1);

0', 7);

Ejercicio 5: Cree una vista películas y la disponibilidad e fue rentada. Consulte todos ultado de la consulta por la columna SQL>

p15q4d_4.sql llamada TITLE_AVAIL para mostrar los títulos de las de cada copia y su día de retorno esperado si es qu los registros/filas desde la vista. Ordene el res TITLE.

CREATE VIEW TITLE_AVAIL AS SELECT t.TITLE, tc.COPY_ID, tc.STATUS, r.EXP_RE

T_DATE FROM WHERE AND

TITLE t, TITLE_COPY tc, RENTAL r t.TITLE_ID = tc.TITLE_ID tc.COPY_ID = r.COPY_ID ;

SQL>

SELECT FROM ORDER BY

* TITLE_AVAIL TITLE_AVAIL.TITLE;

SQL>

CREATE OR REPLACE VIEW TITLE_AVAIL AS SELECT t.TITLE, tc.COPY_ID, tc.STATUS, r.EXP_RE

T_DATE FROM WHERE AND AND SQL>

SELECT FROM

TITLE t, TITLE_COPY tc, RENTAL r t.TITLE_ID = tc.TITLE_ID tc.COPY_ID = r.COPY_ID(+) tc.TITLE_ID = r.TITLE_ID(+) ; * TITLE_AVAIL

ORDER BY TITLE, COPY_ID; ________________________________

__________________________________

------------------------------------