[Udemy] One To Many

The Ultimate MySQL Bootcamp 12강을 공부하며 정리한 내용입니다.

Types of Data Relationships

One to One 일대일

One customer from Customer table has one row in the Customer detail table and one customer detail row is associated with one customer.

Many to Many 다대다

Books have multiple authors and authors can have multiple books.

One to Many 일대다

A customer can have many orders but an order belongs to one customer.

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_date date,
    amount decimal(8,2),
    customer_id INT, //FK naming convention 
    FOREIGN KEY(customer_id) 
      REFERENCES customers(id)
      ON DELETE CASCADE
);

INSERT INTO customers (first_name, last_name, email) 
VALUES ('Boy', 'George', 'george@gmail.com'),
       ('George', 'Michael', 'gm@gmail.com'),
       ('David', 'Bowie', 'david@gmail.com'),
       ('Blue', 'Steele', 'blue@gmail.com'),
       ('Bette', 'Davis', 'bette@aol.com');

INSERT INTO orders (order_date, amount, customer_id)
VALUES ('2016/02/10', 99.99, 1),
       ('2017/11/11', 35.50, 1),
       ('2014/12/12', 800.67, 2),
       ('2015/01/03', 12.50, 2),
       ('1999/04/11', 450.25, 5);

//select data from orders where last_name is George
// 쿼리문 두 번 작성하기
SELECT id FROM books
WHERE last_name = 'George';
SELECT * FROM books
WHERE customer_id = 1; 

// 서브쿼리로 작성하기
SELECT * FROM books
WHERE customer_id = (
  SELECT id FROM customers
  WHERE last_name = 'George'
  );

//cross join
SELECT first_name, last_name, order_date, amount  
FROM customers, orders;

//(implicit) inner join
SELECT first_name, last_name, order_date, amount
FROM customers, orders 
WHERE customers.id = orders.customer_id;

//(explicit) inner join
SELECT first_name, last_name, order_date, amount
FROM customers 
JOIN orders 
  ON customers.id = orders.customer_id;

//left join
SELECT * FROM customers
LEFT JOIN orders
  ON customers.id = orders.customer_id;

SELECT 
  first_name,
  last_name, 
  IFNULL(SUM(amount), 0) AS total_spent
FROM customers
LEFT JOIN orders
  ON customers.id = orders.customer_id
GROUP BY customers.id
ORDER BY total_spent DESC;

---

QUICK CHECK

SELECT 
  first_name,
  title,
  grade
FROM students
JOIN papers
ON students.id = papers.student_id;

SELECT 
  first_name,
  title, 
  grade 
FROM students
LEFT JOIN papers 
ON students.id = papers.student_id

SELECT 
  first_name,
  IFNULL(title, 'MISSING'),
  IFNULL(grade, 0) 
FROM students
LEFT JOIN papers 
ON students.id = papers.student_id

SELECT 
  first_name, IFNULL(AVG(grade), 0)
FROM students
LEFT JOIN papers
ON students.id = papers.student_id
GROUP BY students.id 
ORDER BY average desc;

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