SQL inner join with ansi 복습용 필기

DB/SQL

SQL inner join with ansi 복습용 필기

ej503 2022. 6. 25. 13:36

create table professor (
    prfs_id int(10) comment '교수ID',
    bl_major_id int(10) comment '소속학과ID',
    name varchar(20) comment '교수이름',
    tel varchar(15) comment '교수연락처'
);

create table major (
    major_id int(10) comment '학과ID',
    major_title varchar(30) comment '학과명',
    major_prfs_cnt int(5) comment '학과소속교수수',
    major_student_cnt int(5) comment '학과소속학생수',
    tel varchar(15) comment '학과사무실연락처'
);

create table student (
    student_id int(10) comment '학생번호',
    major_id int(10) comment '학과ID',
    bl_prfs_id int(10) comment '담당교수ID',
    name varchar(20) comment '학생이름',
    tel varchar(15) comment '학생연락처'
);

INSERT INTO professor (prfs_id, bl_major_id, name, tel) VALUES (7019901, 9901, '김보경', '023445678');
INSERT INTO professor (prfs_id, bl_major_id, name, tel) VALUES (7029902, 9902, '조숙', '023446789');
INSERT INTO professor (prfs_id, bl_major_id, name, tel) VALUES (7039903, 9903, '이호', '023449584');
INSERT INTO professor (prfs_id, bl_major_id, name, tel) VALUES (7049904, 9904, '박철남', '023449588');
INSERT INTO professor (prfs_id, bl_major_id, name, tel) VALUES (7059905, 9905, '이만기', '023443443');
INSERT INTO professor (prfs_id, bl_major_id, name, tel) VALUES (7069901, 9901, '강조교', '023449994');
INSERT INTO professor (prfs_id, bl_major_id, name, tel) VALUES (7079902, 9902, '이희숙', '023443321');
INSERT INTO professor (prfs_id, bl_major_id, name, tel) VALUES (7089903, 9903, '소머리', '023440123');
INSERT INTO professor (prfs_id, bl_major_id, name, tel) VALUES (7099904, 9904, '두수위', '023443327');
INSERT INTO professor (prfs_id, bl_major_id, name, tel) VALUES (7109905, 9905, '지만래', '023449995');

INSERT INTO major (major_id, major_title, major_prfs_cnt, major_student_cnt, tel) VALUES (9901, '컴퓨터공학과', 7, 123, '023454321');
INSERT INTO major (major_id, major_title, major_prfs_cnt, major_student_cnt, tel) VALUES (9902, '아동보육학과', 8, 345, '023456676');
INSERT INTO major (major_id, major_title, major_prfs_cnt, major_student_cnt, tel) VALUES (9903, '국문학과', 6, 213, '023456567');
INSERT INTO major (major_id, major_title, major_prfs_cnt, major_student_cnt, tel) VALUES (9904, '경제학과', 5, 432, '023456987');
INSERT INTO major (major_id, major_title, major_prfs_cnt, major_student_cnt, tel) VALUES (9905, '사회복지학과', 9, 312, '023454534');

INSERT INTO student (student_id, major_id, bl_prfs_id, name, tel) VALUES (1001, 9901, 7029901, '한지호', '01098447362');
INSERT INTO student (student_id, major_id, bl_prfs_id, name, tel) VALUES (1002, 9902, 7029902, '김은숙', '01023456787');
INSERT INTO student (student_id, major_id, bl_prfs_id, name, tel) VALUES (1003, 9903, 7039903, '강경호', '01092938476');
INSERT INTO student (student_id, major_id, bl_prfs_id, name, tel) VALUES (1004, 9904, 7049904, '민현민', '01088786623');
INSERT INTO student (student_id, major_id, bl_prfs_id, name, tel) VALUES (1005, 9905, 7059905, '조승우', '01092877795');
INSERT INTO student (student_id, major_id, bl_prfs_id, name, tel) VALUES (1006, 9901, 7069901, '이남철', '01045671234');
INSERT INTO student (student_id, major_id, bl_prfs_id, name, tel) VALUES (1007, 9902, 7079902, '이강철', '01021213434');
INSERT INTO student (student_id, major_id, bl_prfs_id, name, tel) VALUES (1008, 9903, 7089903, '조민수', '01098937262');
INSERT INTO student (student_id, major_id, bl_prfs_id, name, tel) VALUES (1009, 9904, 7099904, '박찬경', '01029884432');
INSERT INTO student (student_id, major_id, bl_prfs_id, name, tel) VALUES (1010, 9905, 7109905, '이도경', '01029385647');
INSERT INTO student (student_id, major_id, bl_prfs_id, name, tel) VALUES (1011, 9901, 7019901, '이만호', '01099996453');
INSERT INTO student (student_id, major_id, bl_prfs_id, name, tel) VALUES (1012, 9902, 7029902, '김효민', '01092887666');
INSERT INTO student (student_id, major_id, bl_prfs_id, name, tel) VALUES (1013, 9903, 7039903, '최효성', '01098999933');
INSERT INTO student (student_id, major_id, bl_prfs_id, name, tel) VALUES (1014, 9904, 7049904, '우민국', '01087651112');
INSERT INTO student (student_id, major_id, bl_prfs_id, name, tel) VALUES (1015, 9905, 7059905, '지대한', '01093934848');
INSERT INTO student (student_id, major_id, bl_prfs_id, name, tel) VALUES (1016, 9901, 7069901, '한나름', '01023329882');
INSERT INTO student (student_id, major_id, bl_prfs_id, name, tel) VALUES (1017, 9902, 7079902, '유육경', '01099881111');
INSERT INTO student (student_id, major_id, bl_prfs_id, name, tel) VALUES (1018, 9903, 7089903, '조민경', '01023311120');
INSERT INTO student (student_id, major_id, bl_prfs_id, name, tel) VALUES (1019, 9904, 7099904, '경지수', '01029100293');
INSERT INTO student (student_id, major_id, bl_prfs_id, name, tel) VALUES (1020, 9905, 7109905, '오종환', '01098882226');
INSERT INTO student (student_id, major_id, bl_prfs_id, name, tel) VALUES (1021, 9901, 7019901, '조형민', '01098909876');
INSERT INTO student (student_id, major_id, bl_prfs_id, name, tel) VALUES (1022, 9902, 7029902, '이수강', '01099992222');
INSERT INTO student (student_id, major_id, bl_prfs_id, name, tel) VALUES (1023, 9903, 7039903, '서민호', '01092997654');
INSERT INTO student (student_id, major_id, bl_prfs_id, name, tel) VALUES (1024, 9904, 7049904, '박효숙', '01022293332');
INSERT INTO student (student_id, major_id, bl_prfs_id, name, tel) VALUES (1025, 9905, 7059905, '남궁옥경', '01099938475');
INSERT INTO student (student_id, major_id, bl_prfs_id, name, tel) VALUES (1026, 9901, 7069901, '피경남', '01029222233');
INSERT INTO student (student_id, major_id, bl_prfs_id, name, tel) VALUES (1027, 9902, 7079902, '고주경', '01099226655');
INSERT INTO student (student_id, major_id, bl_prfs_id, name, tel) VALUES (1028, 9903, 7089903, '하지만', '01022228965');
INSERT INTO student (student_id, major_id, bl_prfs_id, name, tel) VALUES (1029, 9904, 7099904, '기지효', '01012090912');
INSERT INTO student (student_id, major_id, bl_prfs_id, name, tel) VALUES (1030, 9905, 7109905, '박민호', '01074746363');

select p.name as 교수이름, m.major_title as 학과명 from professor p, major m 
where p.bl_major_id = m.major_id;

//기본적인 inner join, where절 사용

select p.name as 교수이름, m.major_title as 학과명
from professor p inner join  major m 
on  p.bl_major_id = m.major_id;

// from ~ join ~ on ~

select p.name as 교수이름, m.major_title as 학과명, s.name as 학생이름
from professor p, major m, student s 
where p.bl_major_id = m.major_id
and m.major_id = s.major_id;

//from ~ where ~ and ~ (3중)

select p.name as 교수이름, m.major_title as 학과명, s.name as 학생이름
from professor p
join major m
join student s
on  p.bl_major_id = m.major_id
and m.major_id = s.major_id;

//from ~ join ~ join ~ on ~ and ~ (3중)