MySQL - One To Many 중간문제

Udemy - The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert를 수강하며 정리하는 글

문제는 214강에 있다.

CREATE TABLE students(
	id INT AUTO_INCREMENT,
    first_name VARCHAR(100),
    PRIMARY KEY(id)
    );
    
CREATE TABLE papers(
	title VARCHAR(100),
    grade VARCHAR(5),
    student_id INT,
    FOREIGN KEY(student_id)
		REFERENCES students(id)
	);
    
select * from information_schema.table_constraints where table_name = 'papers';

ALTER TABLE papers DROP FOREIGN KEY papers_ibfk_1;

ALTER TABLE papers ADD CONSTRAINT paper_fk FOREIGN KEY(student_id) REFERENCES students(id) ON DELETE CASCADE;


INSERT INTO students (first_name) VALUES 
('Caleb'), ('Samantha'), ('Raj'), ('Carlos'), ('Lisa');

INSERT INTO papers (student_id, title, grade ) VALUES
(1, 'My First Book Report', 60),
(1, 'My Second Book Report', 75),
(2, 'Russian Lit Through The Ages', 94),
(2, 'De Montaigne and The Art of The Essay', 98),
(4, 'Borges and Magical Realism', 89);

SELECT * FROM students;

SELECT * FROM papers;
DESC papers;
ALTER TABLE papers MODIFY COLUMN grade INT;
-- Q1

-- 방법 1. implicit inner join 
SELECT first_name, title, grade
FROM students,papers
WHERE students.id = papers.student_id;

-- 방법 2. INNER JOIN인데 그냥 JOIN으로만 표시하기
SELECT s.first_name, p.title, p.grade
FROM students AS s
JOIN papers AS p
	ON s.id = p.student_id;
    
-- 방법 3. INNER JOIN으로 표시하기
SELECT s.first_name, p.title, p.grade
FROM papers AS p
INNER JOIN students AS s
	ON s.id = p.student_id;
    
-- Q2
SELECT s.first_name, p.title, p.grade
FROM students AS s
LEFT JOIN papers AS p
	ON s.id = p.student_id;
    
-- Q3
SELECT 
	students.first_name,
    IFNULL(papers.title,'MISSING'),
    IFNULL(papers.grade,0)
FROM students
LEFT JOIN papers
	ON students.id = papers.student_id;
    
-- Q4
SELECT students.first_name, IFNULL(AVG(papers.grade),0) AS average
FROM students
LEFT JOIN papers
	ON students.id = papers.student_id
GROUP BY students.id
ORDER BY average DESC; 

SELECT 
	students.first_name,
	CASE
		WHEN ISNULL(AVG(papers.grade)) THEN 0
        ELSE AVG(papers.grade)
	END AS average
FROM students
LEFT JOIN papers
	ON students.id = papers.student_id
GROUP BY students.id
ORDER BY average DESC;

-- Q5
SELECT
	students.first_name,
    IFNULL(AVG(papers.grade),CAST(0 AS UNSIGNED)) AS average,
    CASE
		WHEN AVG(papers.grade) >= 75 THEN 'PASSING'
        ELSE 'FALLING'
	END AS 'passing_status'
FROM students
LEFT JOIN papers
	ON students.id = papers.student_id
GROUP BY students.id
ORDER BY average DESC;