jueves, 15 de julio de 2010

EXAMEN

EXAMEN DE ORACLE


<>
CREATE TABLESPACE "TBLEXAMEN147" DATAFILE 'D:\DATA\TBLEXAMEN147.ora' SIZE 10M autoextend on next 1024 konline;
<>
CREATE USER MAALMEIDA PROFILE DEFAULT IDENTIFIED BY "123ma" DEFAULT TABLESPACE "TBLEXAMEN147" ACCOUNT UNLOCK;GRANT CONNECT, resource TO MAALMEIDA;


<>

GRANT "RESOURCE" TO "MAALMEIDA";ALTER USER "MAALMEIDA" DEFAULT ROLE ALL

<>
CREATE TABLE SOCIOS (COD_SOCIO NUMBER(2) NOT NULL, NOM_SOCIO VARCHAR2(50) NOT NULL, DIR_SOCIO VARCHAR2(25) NOT NULL, FECHA_NACIMIENTO_SOCIO DATE NOT NULL, SEXO_SOCIO VARCHAR(15) NOT NULL, CONSTRAINT PK_SOCIOS PRIMARY KEY(COD_SOCIOS)) TABLESPACE TBLEXAMEN147;

CREATE TABLE PRESTAMOS (COD_PRESTAMO NUMBER(2) NOT NULL, FECHA_APROBADA DATE NOT NULL, FECHA_ENTREGA DATE NOT NULL, PLAZO VARCHAR2(25) NOT NULL, MONTO_ENTREGADO NUMBER (6) NOT NULL,SALDO NUMBER (6) NOT NULL, NOM_SOCIO VARCHAR2(25) NOT NULL, CONSTRAINT PK_PRESTAMOS PRIMARY KEY(COD_PRESTAMOS)); TABLESPACE TBLEXAMEN147;

CREATE TABLE PAGO (COD_PAGO NUMBER(2) NOT NULL, FECHA_PAGO DATE NOT NULL, MONTO_PAGO NUMBER(6) NOT NULL, COD_PRESTAMO NUMBER(2) NOT NULL, CONSTRAINT PK_PAGO PRIMARY KEY(COD_PAGO)) TABLESPACE TBLEXAMEN147;

CREATE TABLE CUENTA (COD_CUENTA NUMBER(2) NOT NULL, FECHA_CREADA DATE NOT NULL, SALDO NUMBER (6) NOT NULL, COD_SOCIO NUMBER(2) NOT NULL, CONSTRAINT PK_CUENTA PRIMARY KEY(COD_CUENTA)); TABLESPACE TBLEXAMEN147;

CREATE TABLE TRANSACCIONES (COD_TRANS NUMBER(2) NOT NULL, TIPO_TRANS VARCHAR2(25) NOT NULL, FECHA_TRANS DATE NOT NULL, MONTO_TRANS NUMBER(6) NOT NULL,COD_CUENTA NUMBER(2) not null, CONSTRAINT PK_CUENTA PRIMARY KEY(COD_CUENTA)); TABLESPACE TBLEXAMEN147;

<<>>create sequence sq_alumnosstart with 1increment by 1;
create sequence sq_materiasstart with 1increment by 1;
create sequence sq_notasstart with 1increment by 1;

CREATE TABLE NOTAS (COD_NOTAS NUMBER(5) NOT NULL, NOTA1 NUMBER(2) NOT NULL, NOTA2 NUMBER(2) NOT NULL, NOTA3 NUMBER(2) NOT NULL, COD_ALUMNO NUMBER(5) NOT NULL constraint Fk_Alumnos references alumnos, COD_MATERIA NUMBER(5) NOT NULL constraint FK_materias references materias, CONSTRAINT PK_NOTAS PRIMARY KEY(COD_NOTAS)) TABLESPACE TBLSEPTIMO147;

<<>>create sequence sq_alumnosstart with 1increment by 1;
create sequence sq_materiasstart with 1increment by 1;
create sequence sq_notasstart with 1increment by 1;

ver filas creadasselect * from dba_data_files
<>
CREATE USER MAALMEIDA PROFILE DEFAULT IDENTIFIED BY "123ma" DEFAULT TABLESPACE "TBLSEPTIMO147" ACCOUNT UNLOCK;GRANT CONNECT, resource TO MAALMEIDA;

CREATE USER JURAMIREZ PROFILE DEFAULT IDENTIFIED BY "123ju" DEFAULT TABLESPACE "TBLSEPTIMO147" ACCOUNT UNLOCK;GRANT CONNECT, resource TO JURAMIREZ;
CREATE USER VIGUANOCHANGA PROFILE DEFAULT IDENTIFIED BY "123vi" DEFAULT TABLESPACE "TBLSEPTIMO147" ACCOUNT UNLOCK;GRANT CONNECT, resource TO VIGUANOCHANGA ;

<<>>create sequence sq_alumnosstart with 1increment by 1;
create sequence sq_materiasstart with 1increment by 1;
create sequence sq_notasstart with 1increment by 1;

<>
GRANT "RESOURCE" TO "MAALMEIDA";ALTER USER "MAALMEIDA" DEFAULT ROLE ALL

<> usuarios sys
CREATE TABLE "MAALMEIDA"."ALUMNOS" ("COD_ALUMNO" NUMBER(5) NOT NULL, "NOM_ALUMNO" VARCHAR2(50) NOT NULL, "DIR_ALUMNO" VARCHAR2(25) NOT NULL, "TEL_ALUMNO" NUMBER(9) NOT NULL, "REP_ALUMNO" VARCHAR2(30) NOT NULL, CONSTRAINT "PK_ALUMNOS" PRIMARY KEY("COD_ALUMNO")) TABLESPACE "TBLSEPTIMO"
CREATE TABLE MAALMEIDA.MATERIAS ("COD_MATERIA" NUMBER(5) NOT NULL, "NOM_MATERIA" VARCHAR2(25) NOT NULL, "PENSUM" VARCHAR2(30) NOT NULL, "NUM_CREDITOS" VARCHAR2(10) NOT NULL, CONSTRAINT "PK_MATERIAS" PRIMARY KEY("COD_MATERIA")) TABLESPACE "TBLSEPTIMO"
CREATE TABLE "MAALMEIDA"."NOTAS" ("COD_NOTAS" NUMBER(5) NOT NULL, "NOTA1" NUMBER(2) NOT NULL, "NOTA2" NUMBER(2) NOT NULL, "NOTA3" NUMBER(2) NOT NULL, "COD_ALUMNO" NUMBER(5) NOT NULL, "COD_MATERIA" NUMBER(5) NOT NULL, CONSTRAINT "PK_NOTAS" PRIMARY KEY("COD_NOTAS")) TABLESPACE "TBLSEPTIMO"

<>show user; permite ver en q usuario estamos
connect usuario/contraseña@cadena_de_conección (clicoracle) cambio de usuario


<>
CREATE TABLE alumnos (COD_ALUMNO NUMBER(5) NOT NULL, NOM_ALUMNO VARCHAR2(50) NOT NULL, DIR_ALUMNO VARCHAR2(25) NOT NULL, TEL_ALUMNO NUMBER(9) NOT NULL, REP_ALUMNO VARCHAR2(30) NOT NULL, CONSTRAINT PK_ALUMNOS PRIMARY KEY(COD_ALUMNO)) TABLESPACE TBLSEPTIMO147;
CREATE TABLE Materias (COD_MATERIA NUMBER(5) NOT NULL, NOM_MATERIA VARCHAR2(25) NOT NULL, PENSUM VARCHAR2(30) NOT NULL, NUM_CREDITOS VARCHAR2(10) NOT NULL, CONSTRAINT PK_MATERIAS PRIMARY KEY(COD_MATERIA)) TABLESPACE TBLSEPTIMO147;
CREATE TABLE NOTAS (COD_NOTAS NUMBER(5) NOT NULL, NOTA1 NUMBER(2) NOT NULL, NOTA2 NUMBER(2) NOT NULL, NOTA3 NUMBER(2) NOT NULL, COD_ALUMNO NUMBER(5) NOT NULL constraint Fk_Alumnos references alumnos, COD_MATERIA NUMBER(5) NOT NULL constraint FK_materias references materias, CONSTRAINT PK_NOTAS PRIMARY KEY(COD_NOTAS)) TABLESPACE TBLSEPTIMO147;

<<>>

create or replace trigger clave_primaria_alumnobefore insert on alumnosfor each rowdeclarevalor number;beginselect sq_alumnos.nextval into valor from dual;:new.cod_alumno:=valor;end;
create or replace trigger clave_primaria_materiabefore insert on materiasfor each rowdeclarevalor number;beginselect sq_alumnos.nextval into valor from dual;:new.cod_materia:=valor;end;
create or replace trigger clave_primaria_notabefore insert on notasfor each rowdeclarevalor number;beginselect sq_alumnos.nextval into valor from dual;:new.cod_notas:=valor;end;

<<>>

create view notas_por_materias_alumnosasselect nom_alumno, nom_materia, nota1, nota2, nota3,nota1+nota2+nota3 as total , (nota1+nota2+nota3)/3 as promediofrom alumnos, materias, notaswhere alumnos.cod_alumno= notas.cod_alumnoand materias.cod_materia=notas.cod_materia;

con una condicion aprovado y reprobado/////////
create view notas_por_materias_alumnosasselect nom_alumno, nom_materia, nota1, nota2, nota3,nota1+nota2+nota3 as total , (nota1+nota2+nota3)/3 as promedioif promedio=7 from alumnos, materias, notaswhere alumnos.cod_alumno= notas.cod_alumnoand materias.cod_materia=notas.cod_materia;

<>

ALTER TABLE "MAALMEIDA"."NOTAS" ADD (CONSTRAINT "FK_NOTASMATERIAS" FOREIGN KEY("COD_MATERIA") REFERENCES "MAALMEIDA"."MATERIAS"("COD_MATERIA"))
ALTER TABLE "MAALMEIDA"."NOTAS" ADD (CONSTRAINT "FK_NOTASALUMNOS" FOREIGN KEY() REFERENCES "MAALMEIDA"."ALUMNOS"())
<>
INSERT INTO "MAALMEIDA"."ALUMNOS" ("COD_ALUMNO" ,"NOM_ALUMNO" , "DIR_ALUMNO" ,"TEL_ALUMNO" ,"REP_ALUMNO" ) VALUES (01 ,'Maribel Almeida' ,'San Roque ' ,062 908888 , 'Franco Roldan' )INSERT INTO "MAALMEIDA"."ALUMNOS" ("COD_ALUMNO" ,"NOM_ALUMNO" ,"DIR_ALUMNO" ,"TEL_ALUMNO" ,"REP_ALUMNO" ) VALUES (02 ,'Francisco Ramirez' ,'Otavalo' ,099452447 ,'Estefania Bucheli' )INSERT INTO "MAALMEIDA"."ALUMNOS" ("COD_ALUMNO" ,"NOM_ALUMNO" ,"DIR_ALUMNO" ,"TEL_ALUMNO" ,"REP_ALUMNO" ) VALUES (02 ,'Javier Espinoza' ,'Ibarra' ,099673181 ,'Marco Peralta' )

No hay comentarios:

Publicar un comentario