TRABAJANDO EN ORACLE

---CREACION DE TABLE SPACE

CREATE TABLESPACE Tblseptimo155
DATAFILE 'D:\data\Tblseptimo155.ora 'size 10M
AUTOEXTEND ON NEXT 1024K
PERMANENT
ONLINE;

---CONSULTA
SELECT * FROM DBA_DATA_FILES;

--MODIFICAR UN TABLE SPACE

ALTER TABLESPACE Tblseptimo155
ADD DATAFILE
'D:\data\Tblseptimo1552.ora 'size 10M
AUTOEXTEND ON NEXT 1024K;

SELECT * FROM DBA_TABLESPACES;


CREATE USER CCNA
IDENTIFIED BY CCNA DEFAULT TABLESPACE TBLSEPTIMO155
ACCOUNT UNLOCK;


GRANT CONNECT,RESOURCE TO CCNA;


CREATE USER CCNA
IDENTIFIED BY CCNA DEFAULT TABLESPACE TBLSEPTIMO155
ACCOUNT UNLOCK;

GRANT CONNECT,RESOURCE TO CCNA;

---CRACION DE SECUENCIAS AUTONUMERICAS

CREATE SEQUENCE SQ_NOTAS
START WITH 1
INCREMENT BY 1
--OPCIONAL LIMITACION
MAXVALUE 5000

--VERIFICO EL USUARIO

SHOW USER;

---CAMBIAR DE USUARIO

sql>connect nombre_usuario/contraceña@cadena_conexion


--CREACION DE TABLAS

CREATE TABLE ALUMNOS
(COD_ALUMNO NUMBER(5) NOT NULL,
NOM_ALUMNO VARCHAR2(10) NOT NULL,
DIR_ALUMNO VARCHAR2(10),
TEL_ALUMNO VARCHAR2(10),
DEP_ALUMNO VARCHAR2(10),
PRIMARY KEY(COD_ALUMNO))
TABLESPACE TBLSEPTIMO155;

CREATE TABLE MATERIAS
(COD_MATERIAS NUMBER(2) NOT NULL,
NOM_MATERIA VARCHAR2(10) NOT NULL,
PENSUM VARCHAR2(25) NOT NULL,
NUMCREDITOS NUMBER(15) NOT NULL,
CONSTRAINT PK_MATERIA PRIMARY KEY(COD_MATERIAS))
TABLESPACE TBLSEPTIMO155;


CREATE TABLE NOTAS
(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_MATERIAS NUMBER(2) NOT NULL CONSTRAINT FK_MATERIAS REFERENCES MATERIAS,
COD_NOTAS NUMBER(2) NOT NULL,
PRIMARY KEY(COD_NOTAS))
TABLESPACE TBLSEPTIMO155;


--CREAR LA RELACIONES DE LAS TABLAS

ALTER TABLE NOTAS
ADD (CONSTRAINT FK_NOTAS_MATERIA FOREIGN KEY(COD_MATERIA)
REFERENCES MATERIAS(COD_MATERIAS));

---VER TABLAS
SELECT * FROM TAB;

---ASOCIAR LAS SECUENCIAS A LAS TABLAS

CREATE OR REPLACE TRIGGER CLAVE_PRIMARIA_ALUMNO
BEFORE INSERT ON ALUMNOS
FOR EACH ROW
DECLARE
VALOR NUMBER;
BEGIN
SELECT SQ_ALUMNOS.NEXTVAL INTO VALOR FROM DUAL;
:NEW.COD_ALUMNO := VALOR;
END;
/


---- VER ERRORES
SHOW ERRORS;

CREATE OR REPLACE TRIGGER CLAVE_PRIMARIA_MATERIAS
BEFORE INSERT ON MATERIAS
FOR EACH ROW
DECLARE
VALOR NUMBER;
BEGIN
SELECT SQ_MATERIAS.NEXTVAL INTO VALOR FROM DUAL;
:NEW.COD_MATERIAS := VALOR;
END;


CREATE OR REPLACE TRIGGER CLAVE_PRIMARIA_NOTAS
BEFORE INSERT ON NOTAS
FOR EACH ROW
DECLARE
VALOR NUMBER;
BEGIN
SELECT SQ_NOTAS.NEXTVAL INTO VALOR FROM DUAL;
:NEW.COD_NOTAS := VALOR;
END;


---------------
SELECT * FROM alumnos
INSERT INTO ALUMNOS VALUES(NULL,'MANUEL','AMBATO','0295645','LORENA');
INSERT INTO MATERIAS VALUES(NULL,'CLIENTE','DIURNO','3');
INSERT INTO NOTAS VALUES('10','10','10','1','1',NULL);

----VISTAS

CREATE VIEW NOTAS_MENSAJE
AS
SELECT NOM_ALUMNO,NOM_MATERIA,NOTA1,NOTA2,NOTA3,
NOTA1+NOTA2+NOTA3 AS TOTAL,(NOTA1+NOTA2+NOTA3)/3 AS PROMEDIO,
SELECT CASE promedio
When 6 Then 'reprobado'
When 10 Then 'aprobado' end
FROM ALUMNOS, MATERIAS ,NOTAS
WHERE ALUMNOS.COD_ALUMNO=NOTAS.COD_ALUMNO
AND MATERIAS.COD_MATERIAS = NOTAS.COD_MATERIAS;


SELECT CASE promedio
When 6 Then 'reprobado'
When 10 Then 'aprobado' end
FROM NOTAS_POR_MATERIA_ALUMNO;