EJERCICIO

CREAR TABLESPACE

CREATE TABLESPACE EXAMEN152
DATAFILE 'D:\DATA\EXAMEN152.ORA' SIZE 10M
AUTOEXTEND ON NEXT 1024 K
PERMANENT
ONLINE;


VER LOSTABLESPACE CREADOS

SELECT * FROM DBA_TABLESPACES;


ADICIONAR DATAFILE AL TABLESPACE

ALTER TABLESPACE EXAMEN152 ADD DATAFILE 'D:\DATA\EXAMEN152.ORA' SIZE 10M AUTOEXTEND ON NEXT 1024 K;


CREAR USUARIO

CREATE USER MANUEL IDENTIFIED BY MANUEL DEFAULT TABLESPACE EXAMEN152
ACCOUNT UNLOCK;

CONCEDER PRIVILEGIOS

GRANT CONNECT, RESOURCE TO MANUEL;


CREAR TABLAS

CREATE TABLE SOCIOS("NUM_SOCIO" NUMBER (5) NOT NULL,
"NOM_SOCIO" VARCHAR(50) NOT NULL, "FECH_NAC_SOCIO" VARCHAR(20),
SEXO_SOCIO VARCHAR(20), CONSTRAINT "PK_SOCIOS" PRIMARY KEY("NUM_SOCIO"))
TABLESPACE EXAMEN152;

CREATE TABLE CUENTAS("NUM_CTA" NUMBER(5) NOT NULL,
"FECH_CREA" VARCHAR(20) NOT NULL,
"SALDO" NUMBER(10),
"NUM_SOCIO" NUMBER(5) constrainT fk_SOCIOS references SOCIOS,
CONSTRAINT "PK_CUENTAS" PRIMARY KEY("NUM_CTA"))
TABLESPACE EXAMEN152;

CREATE TABLE PRESTAMOS("NUM_PRESTAMO" NUMBER (5) NOT NULL,
"FECH_APROB" VARCHAR(20),
"FECHA_ENTREGA" VARCHAR(20),
"PLAZO" VARCHAR(20),
"MONTO_ESTIMADO" NUMBER(10),
"SALDO" NUMBER(5),
"NUM_SOCIO" NUMBER(5) CONSTRAINT fk_SOCIOS1 REFERENCES SOCIOS, CONSTRAINT "PK_PRESTAMOS" PRIMARY KEY("NUM_PRESTAMO"))
TABLESPACE EXAMEN152;


CREATE TABLE PAGOS("NUM_PAGO" NUMBER(5) NOT NULL,
"FECH_PAGO" VARCHAR(20),
"MONTO_PAGO" NUMBER(10),
"NUM_PRESTAMO" NUMBER(5) CONSTRAINT FK_PRESTAMO REFERENCES PRESTAMOS,
CONSTRAINT "PK_PAGOS" PRIMARY KEY("NUM_PAGO"))
TABLESPACE EXAMEN152;


CREATE TABLE TRANSANCCION(
"NUM_TRANS" NUMBER(5) NOT NULL,
"TIPO_TRANS" VARCHAR(50),
"FECHA_TRANS" VARCHAR(20),
"MONTO_TRANS" NUMBER(10),
"NUM_CUENTA" CONSTRAINT FK_CUENTA1 REFERENCES CUENTAS,
CONSTRAINT "PK_TRANSACCION" PRIMARY KEY("NUM_TRANS")
)


CREAR SECUENCIAS


CREATE SEQUENCE SQ_SOCIOS
START WITH 1
INCREMENT BY 1;

CREATE SEQUENCE SQ_CUENTAS
START WITH 1
INCREMENT BY 1;

CREATE SEQUENCE SQ_PRESTAMOS
START WITH 1
INCREMENT BY 1;

CREATE SEQUENCE SQ_PAGOS
START WITH 1
INCREMENT BY 1;

CREATE SEQUENCE SQ_TRANSACCIONES
START WITH 1
INCREMENT BY 1;



CREAR CLAVES PRIMARIAS


CREATE OR REPLACE TRIGGER CLAVE_PRIMARIA_SOCIOS
BEFORE INSERT ON SOCIOS
FOR EACH ROW
DECLARE
VALOR NUMBER;
BEGIN
SELECT SQ_SOCIOS.NEXTVAL INTO VALOR FROM DUAL;
:NEW.NUM_SOCIO:=VALOR;
END;
/
CREATE OR REPLACE TRIGGER CLAVE_PRIMARIA_CUENTAS
BEFORE INSERT ON CUENTAS
FOR EACH ROW
DECLARE
VALOR NUMBER;
BEGIN
SELECT SQ_CUENTAS.NEXTVAL INTO VALOR FROM DUAL;
:NEW.NUM_CTA:=VALOR;
END;
/

CREATE OR REPLACE TRIGGER CLAVE_PRIMARIA_PRESTAMOS
BEFORE INSERT ON PRESTAMOS
FOR EACH ROW
DECLARE
VALOR NUMBER;
BEGIN
SELECT SQ_PRESTAMOS.NEXTVAL INTO VALOR FROM DUAL;
:NEW.NUM_PRESTAMO:=VALOR;
END;
/
CREATE OR REPLACE TRIGGER CLAVE_PRIMARIA_PRESTAMOS
BEFORE INSERT ON PRESTAMOS
FOR EACH ROW
DECLARE
VALOR NUMBER;
BEGIN
SELECT SQ_PRESTAMOS.NEXTVAL INTO VALOR FROM DUAL;
:NEW.NUM_PRESTAMO:=VALOR;
END;
/

CREATE OR REPLACE TRIGGER CLAVE_PRIMARIA_TRANSACCION
BEFORE INSERT ON TRANSANCCION
FOR EACH ROW
DECLARE
VALOR NUMBER;
BEGIN
SELECT SQ_TRANSACCIONES.NEXTVAL INTO VALOR FROM DUAL;
:NEW.NUM_TRANS:=VALOR;
END;
/



INSERTAR DATOS EN TABLA PRESTAMOS
INSERT INTO PRESTAMOS VALUES(NULL, '11/02/2009', '01/03/2009', '18 MESES', 2000 ,1000, 4)



INSERTAR DATOS EN LA TABLA CUENTAS

INSERT INTO CUENTAS VALUES(NULL,'10/07/2010', 150, 1);
INSERT INTO CUENTAS VALUES(NULL,'09/07/2010', 100, 2);
INSERT INTO CUENTAS VALUES(NULL,'08/07/2010', 50, 3);


INSERTAR DATOS EN LA TABLA SOCIOS

INSERT INTO SOCIOS VALUES(NULL,'MANUEL POZO', '13/08/1983','PASANDO UN DIA'


Autor: Andrés Arredondo