create database student_papers
go -- If using MSSQL Server. Remove for MySQL. 
use student_papers;
-- Create tables
CREATE TABLE students (
    id INT PRIMARY KEY,
    [name] NVARCHAR(100)
);  
CREATE TABLE papers (
    id INT PRIMARY KEY,
    title NVARCHAR(100),
    grade INT,
    student_id INT,
    FOREIGN KEY (student_id) REFERENCES students(id)
);
-- Populate Tables
INSERT INTO students (id,[name]) VALUES
(1,'Caleb'), 
(2,'Samantha'), 
(3,'Raj'), 
(4,'Carlos'), 
(5,'Lisa');
INSERT INTO papers (id,student_id, title, grade ) VALUES
(1,1, 'My First Book Report', 60),
(2,1, 'My Second Book Report', 75),
(3,2, 'Russian Lit Through The Ages', 94),
(4,2, 'De Montaigne and The Art of The Essay', 98),
(5,4, 'Borges and Magical Realism', 89);

-- SELECT all Records (will show duplicate names)
SELECT [name] FROM Students INNER JOIN Papers ON Students.id = Papers.student_id
-- SELECT with DISTINCT (Remove duplicates)
SELECT DISTINCT [name] FROM Students INNER JOIN Papers ON Students.id = Papers.student_id
-- SELECT with GROUP BY (Remove duplicates)
SELECT [name] FROM Students INNER JOIN Papers ON Students.id = Papers.student_id
GROUP BY [name]

-- Note: The count function can actually be used without a GROUP BY and will return a total count of all records in a single table
SELECT count(name) AS [Number of Students] FROM Students

-- Note: By Joining the Papers table, we are seeing each paper that a student turned in. Then, GROUPING BY name will 'clump' all the duplicates, allowing us to count the number of records in the group. We are also using a column alias so that the output column gets a name. 
SELECT [name], count(name) AS [Number of Submissions] FROM Students 
INNER JOIN Papers ON Students.id = Papers.student_id
GROUP BY [name]

-- Note: We should group by as many columns as we select. This should only be done when we are sure that we have values that can be grouped. The difference in grades will defeat the purpose of grouping and so we only group by the names (which will repeat and thus get grouped) and aggregate the grade column for a single value in the amount of a grade average. 
SELECT [name], avg(grade) AS [Average Grade] FROM Students 
INNER JOIN Papers ON Students.id = Papers.student_id
GROUP BY [name]

-- The following query depicts how the differing grades will break the grouping and not lead to an average grade
SELECT [name], avg(grade) AS [Bad - Average Grade] FROM Students 
INNER JOIN Papers ON Students.id = Papers.student_id
GROUP BY [name], grade

SELECT [name], max(grade) AS [Highest Grade] FROM Students 
INNER JOIN Papers ON Students.id = Papers.student_id
GROUP BY [name]

SELECT [name], min(grade) AS [Lowest Grade] FROM Students 
INNER JOIN Papers ON Students.id = Papers.student_id
GROUP BY [name]

SELECT [name], sum(grade) AS [Total Grade] FROM Students 
INNER JOIN Papers ON Students.id = Papers.student_id
GROUP BY [name]

-- The following query depicts using multiple aggregate functions in a single select query. Notice, it almost doesn't matter which column is placed inside the aggregate function, as long as the column(s) being grouped are correct. 
SELECT [name], 
    avg(grade) AS [Average Grade],
    max(grade) AS [Highest Grade],
    min(grade) AS [Lowest Grade],
    count(name) AS [Number of Submissions]
FROM Students 
INNER JOIN Papers ON Students.id = Papers.student_id
GROUP BY [name]

-- Building on the previous query, we do a LEFT JOIN instead, to bring back all students and then throw in an order by to sort the by the average grade. 
SELECT [name], 
    avg(grade) AS [Average Grade],
    max(grade) AS [Highest Grade],
    min(grade) AS [Lowest Grade],
    count(name) AS [Number of Submissions]
FROM Students 
LEFT JOIN Papers ON Students.id = Papers.student_id
GROUP BY [name]
ORDER BY [Average Grade] desc