USE zerobase;
SELECT * FROM celeb;
CREATE TABLE snl_show ( ID int NOT NULL AUTO_INCREMENT PRIMARY KEY, SEASON int NOT NULL, EPISODE int NOT NULL, BROADCAST_DATE date, HOST varchar(32) NOT NULL );
DESC snl_show;
INSERT INTO snl_show VALUES (1, 8, 7, '2020-09-05', '강동원'); INSERT INTO snl_show VALUES (2, 8, 8, '2020-09-12', '유재석'); INSERT INTO snl_show VALUES (3, 8, 9, '2020-09-19', '차승원'); INSERT INTO snl_show VALUES (4, 8, 10, '2020-09-26', '이수현'); INSERT INTO snl_show VALUES (5, 9, 1, '2021-09-04', '이병헌'); INSERT INTO snl_show VALUES (6, 9, 2, '2021-09-11', '하지원'); INSERT INTO snl_show VALUES (7, 9, 3, '2021-09-18', '제시'); INSERT INTO snl_show VALUES (8, 9, 4, '2021-09-25', '조정석'); INSERT INTO snl_show VALUES (9, 9, 5, '2021-10-02', '조여정'); INSERT INTO snl_show VALUES (10, 9, 6, '2021-10-09', '옥주현');
SELECT * FROM snl_show;
SELECT column1, column2, ... FROM tableA INNER JOIN talbeB ON tableA.column = tableB.column WHERE condition;
SELECT celeb.id, celeb.name, snl_show.id, snl_show.host FROM celeb INNER JOIN snl_show ON celeb.name = snl_show.host;
SELECT column1, column2, ... FROM tableA LEFT JOIN talbeB ON tableA.column = tableB.column WHERE condition;
SELECT celeb.id, celeb.name, snl_show.id, snl_show.host FROM celeb LEFT JOIN snl_show ON celeb.name = snl_show.host;
SELECT column1, column2, ... FROM tableA RIGHT JOIN talbeB ON tableA.column = tableB.column WHERE condition;
SELECT celeb.id, celeb.name, snl_show.id, snl_show.host FROM celeb RIGHT JOIN snl_show ON celeb.name = snl_show.host;
SELECT column1, column2, ... FROM tableA FULL OUTER JOIN talbeB ON tableA.column = tableB.column WHERE condition;
snl_show 에 호스트로 출연한 celeb 을 기준으로
celeb 테이블과 snl_show 테이블을 FULL OUTER JOIN
에러나네?
SELECT celeb.id, celeb.name, snl_show.id, snl_show.host FROM celeb FULL OUTER JOIN snl_show ON celeb.name = snl_show.host;
SELECT column1, column2, ... FROM tableA LEFT JOIN talbeB ON tableA.column = tableB.column UNION SELECT column1, column2, ... FROM tableA RIGHT JOIN talbeB ON tableA.column = tableB.column WHERE condition;
SELECT celeb.id, celeb.name, snl_show.id, snl_show.host FROM celeb LEFT JOIN snl_show ON celeb.name = snl_show.host UNOIN SELECT celeb.id, celeb.name, snl_show.id, snl_show.host FROM celeb RIGHT JOIN snl_show ON celeb.name = snl_show.host;
SELECT column1, column2, ... FROM tableA, talbeB, ... WHERE condition;
SELECT celeb.id, celeb.name, snl_show.id, snl_show.host FROM celeb, snl_show WHERE celeb.name = snl_show.host;
SELECT name, job_title FROM celeb WHERE agency = '안테나';
SELECT celeb.name, snl_show.host FROM celeb, snl_show WHERE celeb.name = snl_show.host;
SELECT celeb.name, celeb.job_title FROM celeb, snl_show WHERE celeb.name = snl_show.host AND celeb.agency = '안테나';
SELECT * FROM celeb WHERE NOT job_title LIKE '%영화배우%';
SELECT * FROM celeb WHERE NOT job_title LIKE '%영화배우%' AND agency = 'YG엔터테인먼트';
SELECT * FROM celeb WHERE age >= 40;
SELECT * FROM celeb WHERE age >= 40 AND agency != 'YG엔터테인먼트';
SELECT celeb.id, celeb.name, snl_show.host FROM celeb, snl_show WHERE celeb.name = snl_show.host;
SELECT celeb.id, celeb.name, snl_show.host FROM celeb, snl_show WHERE celeb.name = snl_show.host AND NOT job_title LIKE '%영화배우%' AND agency = 'YG엔터테인먼트';
SELECT celeb.name, celeb.age, celeb.job_title, celeb.agency, snl_show.season, snl_show.episode FROM celeb, snl_show WHERE celeb.name = snl_show.host AND NOT job_title LIKE '%영화배우%' AND agency = 'YG엔터테인먼트' OR age >= 40 AND agency != 'YG엔터테인먼트';
SELECT celeb.name, celeb.age, celeb.job_title, celeb.agency, snl_show.season, snl_show.episode FROM celeb, snl_show WHERE celeb.name = snl_show.host AND ((NOT job_title LIKE '%영화배우%' AND agency = 'YG엔터테인먼트') OR (age >= 40 AND agency != 'YG엔터테인먼트'));
SELECT snl_show.id, snl_show.season, snl_show.episode, celeb.name, celeb.job_title FROM celeb, snl_show WHERE celeb.name = snl_show.host;
SELECT id, season, episode, name, job_title FROM celeb, snl_show WHERE name = host;
SELECT snl_show.id, season, episode, name, job_title FROM celeb, snl_show WHERE name = host;
SELECT name, season, episode, broadcast_date, agency FROM celeb, snl_show WHERE name = host;
SELECT name, season, episode, broadcast_date, agency FROM celeb, snl_show WHERE name = host AND episode IN (7, 9, 10);
SELECT name, season, episode, broadcast_date, agency FROM celeb, snl_show WHERE name = host AND agency LIKE 'YG______';
SELECT name, season, episode, broadcast_date, agency FROM celeb, snl_show WHERE name = host AND episode IN (7, 9, 10) OR agency LIKE 'YG______';
SELECT name, season, episode, broadcast_date, agency FROM celeb, snl_show WHERE name = host AND (episode IN (7, 9, 10) OR agency LIKE 'YG______');
SELECT name, season, episode, broadcast_date, agency FROM celeb, snl_show WHERE name = host AND broadcast_date > '2020-09-15';
SELECT name, season, episode, broadcast_date, agency FROM celeb, snl_show WHERE name = host AND (episode IN (7, 9, 10) OR agency LIKE 'YG______') AND broadcast_date > '2020-09-15';
위 글은 제로베이스 데이터 취업 스쿨의 강의자료를 참고하여 작성되었습니다.