/********************************CREATE*****************************/


DROP TABLE NOTAS;
DROP TABLE ASIGNACIONES;
DROP TABLE HIST_NOTAS;
DROP INDEX ESTUDIANTE_I;
DROP TABLE ESTUDIANTE;
DROP TABLE CURSOS;
DROP SEQUENCE CARNET_ESEQ;


CREATE TABLE ESTUDIANTE
(CARNET NUMBER PRIMARY KEY,
NOMBRE VARCHAR2 (15) NOT NULL,
ESPECIALIDAD VARCHAR2 (15) NOT NULL,
CICLO NUMBER (2) CHECK (CICLO BETWEEN 1 AND 10),
PROMEDIO NUMBER (3,2));

CREATE SEQUENCE CARNET_ESEQ
START WITH 100 INCREMENT BY 50;


CREATE INDEX ESTUDIANTE_I ON ESTUDIANTE (ESPECIALIDAD);

CREATE TABLE CURSOS
(CURSO VARCHAR2 (6) PRIMARY KEY,
DIAS VARCHAR2 (7) NOT NULL,
AULA VARCHAR2 (5) NOT NULL);


CREATE TABLE ASIGNACIONES
(CARNET NUMBER REFERENCES ESTUDIANTE ON DELETE CASCADE,
CURSO VARCHAR2 (6) REFERENCES CURSOS,
PUESTO NUMBER,
PRIMARY KEY (CARNET, CURSO));


CREATE TABLE NOTAS
(CARNET NUMBER REFERENCES ESTUDIANTE,
CURSO VARCHAR2 (6) REFERENCES CURSOS,
NOTA NUMBER,
PRIMARY KEY (CARNET, CURSO));


CREATE TABLE HIST_NOTAS
(CARNET NUMBER REFERENCES ESTUDIANTE,
CURSO VARCHAR2 (6) REFERENCES CURSOS,
NOTA NUMBER,
PRIMARY KEY (CARNET,CURSO));


/**********************************INSERT**************************/

INSERT INTO ESTUDIANTE VALUES (CARNET_ESEQ.NEXTVAL, 'JUAREZ',
'HISTORIA', 5
, NULL);
INSERT INTO ESTUDIANTE VALUES (CARNET_ESEQ.NEXTVAL, 'CRUZ',
'CONTABILIDAD',
3 , NULL);
INSERT INTO ESTUDIANTE VALUES (CARNET_ESEQ.NEXTVAL, 'BATRES',
'MATEMATICA',
5, NULL);
INSERT INTO ESTUDIANTE VALUES (CARNET_ESEQ.NEXTVAL, 'GARCIA',
'HISTORIA', 1
, NULL);
INSERT INTO ESTUDIANTE VALUES (CARNET_ESEQ.NEXTVAL, 'BATRES',
'CONTABILIDAD', 7 , NULL);
INSERT INTO ESTUDIANTE VALUES (CARNET_ESEQ.NEXTVAL, 'RODAS',
'MATEMATICA', 1
, NULL);
INSERT INTO ESTUDIANTE VALUES (CARNET_ESEQ.NEXTVAL, 'RIOS',
'CONTABILIDAD',
5 , NULL);
INSERT INTO ESTUDIANTE VALUES (CARNET_ESEQ.NEXTVAL, 'JUAREZ',
'HISTORIA', 3
, NULL);


INSERT INTO CURSOS VALUES ('BA200', 'LJ', 'A101');
INSERT INTO CURSOS VALUES ('BD445', 'LMJ', 'A101');
INSERT INTO CURSOS VALUES ('BF410', 'LMV', 'D303');
INSERT INTO CURSOS VALUES ('CS150', 'LMJ', 'E604');
INSERT INTO CURSOS VALUES ('CS250', 'MJV', 'A403');


INSERT INTO NOTAS VALUES (100, 'BD445', 75);
INSERT INTO NOTAS VALUES (150, 'BA200', 82);
INSERT INTO NOTAS VALUES (200, 'BD445', 58);
INSERT INTO NOTAS VALUES (200, 'CS250', 64);
INSERT INTO NOTAS VALUES (300, 'CS150', 95);
INSERT INTO NOTAS VALUES (400, 'BA200', 80);
INSERT INTO NOTAS VALUES (400, 'BF410', 69);
INSERT INTO NOTAS VALUES (400, 'CS250', 45);
INSERT INTO NOTAS VALUES (450, 'BA200', 50);


INSERT INTO ASIGNACIONES VALUES (100, 'BD445', 1);
INSERT INTO ASIGNACIONES VALUES (150, 'BA200', 1);
INSERT INTO ASIGNACIONES VALUES (200, 'BD445', 2);
INSERT INTO ASIGNACIONES VALUES (200, 'CS250', 1);
INSERT INTO ASIGNACIONES VALUES (300, 'CS150', 1);
INSERT INTO ASIGNACIONES VALUES (400, 'BA200', 2);
INSERT INTO ASIGNACIONES VALUES (400, 'BF410', 1);
INSERT INTO ASIGNACIONES VALUES (400, 'CS250', 2);
INSERT INTO ASIGNACIONES VALUES (450, 'BA200', 3);






/**********************************SELECTS**************************/

/*1*/
SELECT * FROM ESTUDIANTE;
/*2*/
SELECT NOMBRE, ESPECIALIDAD
FROM ESTUDIANTE;
/*3*/
SELECT DISTINCT ESPECIALIDAD
FROM ESTUDIANTE;
/*4*/
SELECT NOMBRE, CICLO
FROM ESTUDIANTE
WHERE (ESPECIALIDAD='MATEMATICA');
/*5*/
SELECT NOMBRE, CICLO
FROM ESTUDIANTE
WHERE (ESPECIALIDAD='HISTORIA' AND CICLO=5);
/*6*/
SELECT E.NOMBRE,E.CARNET
FROM ESTUDIANTE E,CURSOS C,ASIGNACIONES A
WHERE (E.CARNET=A.CARNET AND A.CURSO=C.CURSO AND E.CICLO=5 AND
C.AULA='A101');
/*7*/
SELECT NOMBRE
FROM ESTUDIANTE
WHERE (ESPECIALIDAD IN ('MATEMATICA','CONTABILIDAD'));
/*8*/
SELECT NOMBRE, PROMEDIO
FROM ESTUDIANTE
WHERE (CICLO NOT IN (5,7));
/*9*/
SELECT NOMBRE,ESPECIALIDAD
FROM ESTUDIANTE
WHERE CICLO=3
ORDER BY NOMBRE ASC;
/*10*/
SELECT NOMBRE,PROMEDIO
FROM ESTUDIANTE
WHERE CICLO IN (3,5,7)
ORDER BY ESPECIALIDAD ASC, PROMEDIO DESC;
/*11*/
SELECT COUNT(*) FROM ESTUDIANTE;
/*12*/
SELECT COUNT (DISTINCT ESPECIALIDAD) FROM ESTUDIANTE;
/*13*/
SELECT ESPECIALIDAD, COUNT(NOMBRE)
FROM ESTUDIANTE
GROUP BY ESPECIALIDAD;
/*14*/
SELECT ESPECIALIDAD, COUNT(NOMBRE)
FROM ESTUDIANTE
GROUP BY ESPECIALIDAD
HAVING COUNT(NOMBRE)>2;
/*15*/
SELECT ESPECIALIDAD, AVG(PROMEDIO)
FROM ESTUDIANTE
WHERE CICLO IN (3,5)
GROUP BY ESPECIALIDAD
HAVING COUNT(NOMBRE)>1;
/*16*/
SELECT E.NOMBRE,N.NOTA
FROM ESTUDIANTE E, NOTAS N
WHERE (E.CARNET=N.CARNET AND N.CURSO='BD445');
/*17*/
SELECT E.NOMBRE
FROM ESTUDIANTE E,ASIGNACIONES A,CURSOS C
WHERE (E.CARNET=A.CARNET AND A.CURSO=C.CURSO AND C.DIAS='LMJ');
/*18*/
SELECT E.NOMBRE,A.CURSO
FROM ESTUDIANTE E,ASIGNACIONES A
WHERE (E.CARNET=A.CARNET);
/*19*/
SELECT E.NOMBRE,A.CURSO
FROM ESTUDIANTE E,ASIGNACIONES A
WHERE (E.CARNET=A.CARNET AND A.PUESTO=1);
/*20*/
SELECT E.CARNET,A.CURSO,C.DIAS
FROM ESTUDIANTE E,ASIGNACIONES A,CURSOS C
WHERE (E.NOMBRE='BATRES' AND E.CARNET=A.CARNET AND A.CURSO=C.CURSO);
/*21*/
SELECT A.CURSO
FROM ESTUDIANTE E,ASIGNACIONES A
WHERE (E.CARNET=A.CARNET AND A.CURSO NOT IN (SELECT A.CURSO FROM
ESTUDIANTE
E,ASIGNACIONES A
WHERE (E.CARNET=A.CARNET AND E.CICLO=5)));
/*22*/
SELECT E.NOMBRE
FROM ESTUDIANTE E,ASIGNACIONES A
WHERE (E.CARNET=A.CARNET AND (SELECT COUNT(DISTINCT A.CURSO) FROM
ASIGNACIONES A,ESTUDIANTE E
WHERE (A.CARNET=E.CARNET))>=1)
GROUP BY E.NOMBRE,E.CARNET;
/*23*/
SELECT E.NOMBRE
FROM ESTUDIANTE E,ASIGNACIONES A
WHERE E.CARNET=A.CARNET
GROUP BY NOMBRE HAVING COUNT(*)=(SELECT COUNT(*) FROM CURSOS);
/*24*/
SELECT E.NOMBRE
FROM ESTUDIANTE E,ASIGNACIONES A
WHERE E.CARNET=A.CARNET
AND E.CICLO=3
AND ((SELECT COUNT (*) FROM ASIGNACIONES
WHERE ASIGNACIONES.CURSO=A.CURSO)=
(SELECT COUNT (*) FROM ESTUDIANTE S, ASIGNACIONES I
WHERE S.CARNET=I.CARNET AND S.CICLO=3));


/*25*/
SELECT DISTINCT(E.ESPECIALIDAD)
FROM ESTUDIANTE E, ASIGNACIONES A
WHERE (E.CARNET=A.CARNET AND A.CURSO='BA200');



/*********************************UPDATES*************************/

/*26*/
update asignaciones
set puesto=5
where (carnet=400 and curso='BF410');

/*27*/
update asignaciones
set puesto=(select max(puesto)+1 from asignaciones
where curso='CS250')
where carnet=200 and curso='CS250';

/*28*/
update estudiante
set ciclo=7
where carnet in(select carnet from asignaciones a
where curso='BA200');

/*29*/
update estudiante
set especialidad=upper(especialidad);

/*30*/
update asignaciones
set puesto=null
where curso='CS250';

/*31*/
delete cursos
where aula='D303';

/*32*/
delete estudiante
where carnet=200;

/*33*/
delete asignaciones
where carnet in (select carnet from  estudiante
where ciclo=5);

/*34*/
delete notas
where curso='CS250';

/*35*/
delete cursos
where dias like '%V%';