-- student id and major

Analysis:
[1] Output: stuId and major
[2] source: student
[3] condition: no

select distinct stuId, major -- step [3]
from student; -- step [1]
-- where step [2]

select distinct stuId as `student id`  -- variable name using backtick
    , major -- step [3]
from student; -- step [1]
-- where step [2]

select distinct major, -- one row per student
    stuId as `student id`  -- variable name using backtick
-- step [3]
from student; -- step [1]
-- where step [2]

-- Majors and the numbers of students majoring in them.
-- one row per major (a group of students)

select distinct major, -- one group by major value
    count(stuId)as `Number of students` -- group function.  
-- step [4]
from student -- step [1]
-- where step [2]: intermediate: one row one student
group by major -- group of majors: no individual row; only group -- [3]
-- each major value defines a group.
-- major: group by column

-- Majors and the numbers of students majoring in them with 20 or more
-- credits

select distinct major, -- one row per student
    stuId as `student id`,  -- variable name using backtick
    credits
-- step [3]
from student -- step [1]
where credits >= 20; -- step [2]

select distinct major, -- one group by major value
    count(stuId)as `Number of students` -- group function.  
-- step [4]
from student -- step [1]
where credits >= 20 -- step [2]
-- where step [2]: intermediate: one row one student
group by major; -- group of majors: no individual row; only group -- [3]
-- each major value defines a group.
-- major: group by column

select distinct major, -- one group by major value
    count(stuId)as `Number of students`, -- group function.  
    avg(credits) as `Average number of credits`
-- step [4]
from student -- step [1]
where credits >= 20 -- step [2]
-- where step [2]: intermediate: one row one student
group by major; -- group of majors: no individual row; only group -- [3]
-- each major value defines a group.
-- major: group by column

-- Majors and the numbers of students majoring in them with 20 or more
-- credits. Include only majors with average number of credits >= 45.
-- condition: `Average number of credits` >= 45

select distinct major, -- one group by major value
    count(stuId)as `Number of students`, -- group function.  
    avg(credits) as `Average number of credits`
-- step [4]
from student -- step [1]
where credits >= 20 -- step [2]
-- where step [2]: intermediate: one row one student
and `Average number of credits` >= 45
-- No, no, no group yet.
group by major; -- group of majors: no individual row; only group -- [3]
-- each major value defines a group.
-- major: group by column

select distinct major, -- one group by major value
    count(stuId)as `Number of students`, -- group function.  
    avg(credits) as `Average number of credits`
-- step [5]
from student -- step [1]
where credits >= 20 -- step [2]
-- where step [2]: intermediate: one row one student
-- No, no, no group yet.
group by major -- group of majors: no individual row; only group -- [3]
-- each major value defines a group.
-- major: group by column
having `Average number of credits` >= 45; -- [4] filter the group.

-- Majors and the numbers of students majoring in them,
-- only list majors with 2 or more students.


select distinct major,
   count(stuId) as `number of students`
from student
group by major 
having `number of students` >= 2;


-- stuId, student name, and number of courses enrolled.

-- [1] individual rows: stuId, student name, and courses enrolled.

A. output: studId, fname, lname of student; classId of enroll
B. sources: student, enroll
C. conditions: join condition: student.stuId = enroll.stuId

select distinct s.stuId, 
    concat(s.fname, ' ', s.lname) as student, -- regular function
    e.classId
from student s, enroll e
where e.stuId = s.stuId;

-- [2] summary: number of courses enrolled.
select distinct s.stuId, 
    concat(s.fname, ' ', s.lname) as student, -- regular function
    count(e.classId) as `Number of classes`
from student s, enroll e
where e.stuId = s.stuId
group by s.stuId, student;

select distinct s.stuId, 
    concat(s.fname, ' ', s.lname) as student, -- regular function
    count(e.classId) as `Number of classes`  -- group function
from student s, enroll e
where e.stuId = s.stuId
group by s.stuId, student
order by `Number of classes` desc;

-- stuId, student name, and number of classes enrolled.
-- only include students taking three classes or more

select distinct s.stuId, 
    concat(s.fname, ' ', s.lname) as student, -- regular function
    count(e.classId) as `Number of classes`  -- group function
from student s, enroll e
where e.stuId = s.stuId
group by s.stuId, student
having `Number of classes` >= 3
order by `Number of classes` desc;