create database aggregation;
use aggregation;

drop table if exists College;
drop table if exists Student;
drop table if exists Apply;

CREATE TABLE College (
    cName VARCHAR(255) PRIMARY KEY,
    state VARCHAR(255),
    enrollment INT
);
CREATE TABLE Student (
    sID INT PRIMARY KEY,
    sName VARCHAR(255),
    GPA DOUBLE,
    sizeHS INT
);
CREATE TABLE Apply (
    sID INT PRIMARY KEY,
    cName VARCHAR(255),
    major VARCHAR(255),
    decision TEXT
);


insert into Student values (123, 'Amy', 3.9, 1000), (234, 'Bob', 3.6, 1500), (345, 'Craig', 3.5, 500),
(456, 'Doris', 3.9, 1000), (567, 'Edward', 2.9, 2000), (678, 'Fay', 3.8, 200), (789, 'Gary', 3.4, 800),
(987, 'Helen', 3.7, 800), (876, 'Irene', 3.9, 400), (765, 'Jay', 2.9, 1500), (654, 'Amy', 3.9, 1000),
(543, 'Craig', 3.4, 2000);

insert into College values ('Stanford', 'CA', 15000), ('Berkeley', 'CA', 36000), ('MIT', 'MA', 10000), ('Cornell', 'NY', 21000);

insert into Apply values (123, 'Stanford', 'CS', 'Y'), (123, 'Stanford', 'EE', 'N'), (123, 'Berkeley', 'CS', 'Y'),
(123, 'Cornell', 'EE', 'Y'), (234, 'Berkeley', 'biology', 'N'), (345, 'MIT', 'bioengineering', 'Y'), (345, 'Cornell', 'bioengineering', 'N'),
(345, 'Cornell', 'CS', 'Y'), (345, 'Cornell', 'EE', 'N'), (678, 'Stanford', 'history', 'Y'), (987, 'Stanford', 'CS', 'Y'), (987, 'Berkeley', 'CS', 'Y'),
(876, 'Stanford', 'CS', 'N'), (876, 'MIT', 'biology', 'Y'), (876, 'MIT', 'marine biology', 'N'), (765, 'Stanford', 'history', 'Y'),
(765, 'Cornell', 'history', 'N'), (765, 'Cornell', 'psychology', 'Y'), (543, 'MIT', 'CS', 'N');

insert into College values ('UGR', 'And', 50000);
insert into Student values (712, 'John', 3.2, 2000), (713, 'Anne', 3.2, 2500);





/**
  La media de GPA de todos los estudiantes
**/

SELECT
    AVG(GPA)
FROM
    Student;

/**
  El GPA mas bajo de todos los estudiantes donde major es CS
**/

SELECT
    AVG(GPA)
FROM
    Student
WHERE
    sID IN (SELECT
            sID
        FROM
            Apply
        WHERE
            major = 'CS');

/**
  Universidades con mas de 15000 alumnos
**/

SELECT
    COUNT(*)
FROM
    College
WHERE
    enrollment > 15000;

/**
 Numero de estudiantes matriculados en Cornell
**/

SELECT
    COUNT(DISTINCT sID)
FROM
    Apply
WHERE
    cName = 'Cornell';

/**
  Estudiantes con el mismo conteo de GPA y sizeHS
**/

SELECT
    *
FROM
    Student S1
WHERE
    (SELECT
            COUNT(*)
        FROM
            Student S2
        WHERE
            S2.sID <> S1.sID AND S2.GPA = S1.GPA) = (SELECT
            COUNT(*)
        FROM
            Student S2
        WHERE
            S2.sID <> S1.sID
                AND S2.sizeHS = S1.sizeHS);

/**
  Cantidad de GPA de alumnos que se matriculan a CS que excede a los
  que no lo hace.
**/

SELECT DISTINCT
    (SELECT
            AVG(GPA) AS avgGPA
        FROM
            Student
        WHERE
            sID IN (SELECT
                    sID
                FROM
                    Apply
                WHERE
                    major = 'CS')) - (SELECT
            AVG(GPA) AS avgGPA
        FROM
            Student
        WHERE
            sID NOT IN (SELECT
                    sID
                FROM
                    Apply
                WHERE
                    major = 'CS')) AS d
FROM
    Student;

/**
  Numero de matriculas de cada universidad
**/

SELECT
    cName, COUNT(*)
FROM
    Apply
GROUP BY cName;

/**
  Matriculaciones a universidades por estado
**/

SELECT
    state, SUM(enrollment)
FROM
    College
GROUP BY state;

/**
  Minimo y maximo de las GPA de las matriculas Major
**/

SELECT
    cName, major, MIN(GPA), MAX(GPA)
FROM
    Student,
    Apply
WHERE
    Student.sID = Apply.sID
GROUP BY cName , major;


/**
  Numero de matriculas de cada estudiante
**/

SELECT
    Student.sID, COUNT(DISTINCT cName)
FROM
    Student,
    Apply
WHERE
    Student.sID = Apply.sID
GROUP BY Student.sID;

/**
  Numero de matriculas de cada estudiante incluyendo 0 a aquellos
  que no han solicitado ninguna.
**/

SELECT
    Student.sID, COUNT(DISTINCT cName)
FROM
    Student,
    Apply
WHERE
    Student.sID = Apply.sID
GROUP BY Student.sID
UNION SELECT
    sID, 0
FROM
    Student
WHERE
    sID NOT IN (SELECT
            sID
        FROM
            Apply);

/**
  Universidades con menos de 5 matriculas
**/

SELECT
    cName
FROM
    Apply
GROUP BY cName
HAVING COUNT(DISTINCT sID) < 5;

/**
  Majors cuyas matriculas estan por debajo de la media
**/

SELECT
    major
FROM
    Student,
    Apply
WHERE
    Student.sID = Apply.sID
GROUP BY major
HAVING MAX(GPA) < (SELECT
        AVG(GPA)
    FROM
        Student);