-- 10/30/2019

(4) List names, the number of classes enrolled, and the average n_alerts of CSCI or
ARTS major students in the following manner.

+--------+------------+---------+--------------+
| stuId | student | n_class | avg_n_alerts |
+--------+------------+---------+--------------+
| 100008 | Bill Ching | 1 | 0.0000 |
| 100009 | Linda King | 0 | NULL |
| 100000 | Tony Hawk | 6 | 1.3333 |
| 100001 | Mary Hawk | 2 | 0.0000 |
| 100002 | David Hawk | 3 | 3.0000 |
+--------+------------+---------+--------------+
5 rows in set (0.00 sec)

4a. Raw data for the summary:

stuId, ames, classId in enroll table, and the n_alerts of CSCI or ARTS major students
output: fname, lanme (student); classId and n_alerts (enroll)
source: student INNER JOIN ENROLL
condition: CSCI or ARTS major

SELECT DISTINCT s.stuId,
    CONCAT(s.fname, ' ', s.lname) AS student,
    e.classId, e.n_alerts
FROM student s INNER JOIN enroll e ON (s.stuId = e.stuId)
WHERE s.major = 'CSCI' OR s.major = 'ARTS';

+--------+------------+---------+----------+
| stuId  | student    | classId | n_alerts |
+--------+------------+---------+----------+
| 100008 | Bill Ching |   10007 |        0 |
| 100000 | Tony Hawk  |   10000 |        0 |
| 100000 | Tony Hawk  |   10001 |        2 |
| 100000 | Tony Hawk  |   10002 |        1 |
| 100000 | Tony Hawk  |   10003 |        0 |
| 100000 | Tony Hawk  |   10004 |        1 |
| 100000 | Tony Hawk  |   11001 |        4 |
| 100001 | Mary Hawk  |   10000 |     NULL |
| 100001 | Mary Hawk  |   10001 |        0 |
| 100002 | David Hawk |   10000 |        3 |
| 100002 | David Hawk |   10002 |        2 |
| 100002 | David Hawk |   10003 |        4 |
+--------+------------+---------+----------+
12 rows in set (0.04 sec)

SELECT DISTINCT s.stuId,
    CONCAT(s.fname, ' ', s.lname) AS student,
    e.classId, e.n_alerts
FROM student s LEFT JOIN enroll e ON (s.stuId = e.stuId)
WHERE s.major = 'CSCI' OR s.major = 'ARTS';

4b. summary.

SELECT DISTINCT s.stuId,
    CONCAT(s.fname, ' ', s.lname) AS student,
    COUNT(e.classId) AS n_class, 
    AVG(e.n_alerts) AS avg_n_alerts
FROM student s INNER JOIN enroll e ON (s.stuId = e.stuId)
WHERE s.major = 'CSCI' OR s.major = 'ARTS'
GROUP BY s.stuId, student;

-- to include NULL in n_alerts:
SELECT DISTINCT s.stuId,
    CONCAT(s.fname, ' ', s.lname) AS student,
    COUNT(e.classId) AS n_class, 
    AVG(e.n_alerts) AS avg_n_alerts
FROM student s LEFT JOIN enroll e ON (s.stuId = e.stuId)
WHERE s.major = 'CSCI' OR s.major = 'ARTS'
GROUP BY s.stuId, student;


A LEFT JOIN B = B RIGHT JOIN A


mysql storage engines:

CREATE TABLE IF NOT EXISTS Grade (
    grade VARCHAR(2),
    gradePoint DECIMAL(5,4),
    CONSTRAINT Grade_grade_pk PRIMARY KEY (grade)
);


same as

CREATE TABLE IF NOT EXISTS Grade (
    grade VARCHAR(2),
    gradePoint DECIMAL(5,4),
    CONSTRAINT Grade_grade_pk PRIMARY KEY (grade)
) ENGINE = INNODB;

CREATE TABLE IF NOT EXISTS Grade (
    grade VARCHAR(2),
    gradePoint DECIMAL(5,4),
    CONSTRAINT Grade_grade_pk PRIMARY KEY (grade)
) ENGINE = MyISAM;  -- default

desc student;
create index lname_index on student(lname);
desc student;