SQL Queries for Course, Faculty, and Student Database Management

/* #1 */
SELECT * FROM curso;
ALTER TABLE curso ADD horas INT DEFAULT(30);

/* #2 */
UPDATE curso SET horas=60 WHERE cred=6;

/* #3 */
DELETE FROM curso
-- SELECT * FROM curso
WHERE cno NOT IN(SELECT cno FROM clase)
AND cdept IN(SELECT dept FROM departamento AS d1 WHERE ddespacho=
(SELECT MIN(ddespacho) FROM departamento WHERE dedif=d1.dedif));

SELECT * FROM curso;

/* #6 */

SELECT * FROM claustro;

INSERT INTO claustro
SELECT * FROM (SELECT MAX(fno)+1 FROM claustro) AS t1
CROSS JOIN
(SELECT enombre FROM persona1 WHERE enombre REGEXP "^...[aeiou]" AND enombre NOT REGEXP "[aeiou]$") AS t2
CROSS JOIN
(SELECT fdomi, ffcanti, fnumdep, fsueldo FROM claustro AS c1 WHERE fsueldo >
(SELECT AVG(fsueldo) FROM claustro WHERE fdept=c1.fdept)
AND fnumdep > ALL(SELECT AVG(fnundep) FROM claustro GROUP BY fdept)) AS t3
CROSS JOIN
(SELECT enombre FROM persona1 WHERE enombre REGEXP "^...[aeiou]" AND enombre NOT REGEXP "[aeiou]$") AS t4;

/* #4 */
SELECT * FROM estudiante WHERE
siq > (SELECT AVG(siq) FROM (SELECT * FROM estudiante) AS e1)
AND
sesp = ANY(SELECT cdept FROM curso WHERE ctarifa=0 GROUP BY cdept HAVING COUNT(*) = (
SELECT MIN(numcursos) FROM (
SELECT COUNT(*) AS numcursos FROM curso WHERE ctarifa=0 GROUP BY cdept) AS e2)
);

UPDATE estudiante SET siq = siq*1.25 WHERE
siq > (SELECT AVG(siq) FROM (SELECT * FROM estudiante) AS e1)
AND
sesp = ANY(SELECT cdept FROM curso WHERE ctarifa=0 GROUP BY cdept HAVING COUNT(*) = (
SELECT MIN(numcursos) FROM (SELECT COUNT(*) AS numcursos FROM curso WHERE ctarifa=0 GROUP BY cdept) AS e2)
);

/* #5 */

-- THEORY
CREATE TABLE nomtaula SELECT * FROM empleados;

-- Selecting the requested courses
SELECT cno FROM curso c WHERE ctarifa=(
SELECT MAX(ctarifa) FROM curso WHERE cdept=c.cdept GROUP BY cdept);

-- All together
CREATE TABLE IF NOT EXISTS cursoscars
SELECT * FROM curso c
WHERE ctarifa=(
SELECT MAX(ctarifa) FROM curso WHERE cdept=c.cdept GROUP BY cdept);

SHOW TABLES;
SELECT * FROM cursoscars;
EXPLAIN cursoscars;