SQL 문법( Join - 2 )

 

1. 학번이 10101인 학생의 학번, 이름, 학과이름 과 학과 위치를 출력

1 2 3 4 5 6 7

SQL> select s.studno, s.name, d.dname, d.loc 2 from student s, department d 3 where s.studno=10101 and s.deptno=d.deptno; STUDNO NAME DNAME LOC ---------- ---------- ---------------- ---------- 10101 전인하 컴퓨터공학과 1호관

2. 몸무게가 80kg 이상인 학생의 학번, 이름,체중,학과이름,학과위치를 출력

1 2 3 4 5 6 7 8 9

SQL> select s.studno, s.name,s.weight, d.dname, d.loc 2 from student s, department d 3 where s.weight >= 80 and s.deptno=d.deptno; STUDNO NAME WEIGHT DNAME LOC ---------- ---------- ---------- ---------------- ---------- 10107 이광훈 92 컴퓨터공학과 1호관 10103 김영균 88 컴퓨터공학과 1호관 10202 오유석 92 멀티미디어학과 2호관

3. Temp테이블을 사용해 사번, 성명, 생일 출력. 단, 자신보다 생일이 빠른 사람수를 오름차순 정렬하여 출력

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

SQL> select a.emp_id, a.emp_name, a.birth_date, count(b.birth_date) 2 from temp a, temp b 3 where a.birth_date > b.birth_date(+) 4 group by a.emp_id, a.emp_name, a.birth_date 5 order by 4; EMP_ID EMP_NAME BIRTH_DATE COUNT(B.BIRTH_DATE) ---------- ---------- ------------ ------------------- 19960303 설까치 25-SEP-71 0 19966102 지문덕 05-JUL-72 1 19930402 강감찬 15-AUG-72 2 19960212 배뱅이 15-DEC-72 3 19960101 홍길동 22-MAR-73 4 19950303 이순신 15-JUN-73 5 19970101 김길동 25-JAN-74 6 19970201 박문수 15-APR-75 7 19930331 정도령 25-MAY-76 8 19970112 연흥부 05-NOV-76 9 20000101 이태백 25-JAN-80 10 20000102 김설악 22-MAR-80 11 20000203 최오대 15-APR-80 12 20000334 박지리 25-MAY-80 13 20000305 정북악 15-JUN-80 14 20006106 유도봉 05-JUL-80 15 20000407 윤주왕 15-AUG-80 16 20000308 강월악 25-SEP-80 17 20000119 장금강 05-NOV-80 18 20000210 나한라 15-DEC-80 19 20 rows selected.

4. EQUI JOIN을 이용하여 학생 이름과 소속 학과 이름을 학과 이름, 학생 이름순으로 정렬하여 출력

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

SQL> select s.name, d.dname 2 from student s, department d 3 where s.deptno=d.deptno 4 order by 2, 1; NAME DNAME ---------- ---------------- 김진영 멀티미디어학과 오유석 멀티미디어학과 윤진욱 멀티미디어학과 하나리 멀티미디어학과 김진경 전자공학과 박동진 전자공학과 이동훈 전자공학과 조명훈 전자공학과 김영균 컴퓨터공학과 류민정 컴퓨터공학과 박미경 컴퓨터공학과 서재진 컴퓨터공학과 이광훈 컴퓨터공학과 임유진 컴퓨터공학과 전인하 컴퓨터공학과 지은경 컴퓨터공학과 16 rows selected.

5. OUTER JOIN을 이용하여 101번 학과에 소속된 학생들의 이름과 지도 교수이름을 출력
이때 지도교수가 배정되지 않은 학생도 함께 출력

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

SQL> select s.name, p.name 2 from student s, professor p 3 where s.profno=p.profno(+) and s.deptno=101; NAME NAME ---------- ---------- 이광훈 성연희 전인하 성연희 김영균 이만식 류민정 전은지 임유진 전은지 지은경 전은지 서재진 박미경 8 rows selected.

6. 공과대학에 소속된 학부와 학과 이름을 출력

1 2 3 4 5 6 7 8 9 10

SQL> select b.dname, a.dname 2 from department a, department b 3 where a.college = b.deptno and b.dname <> '공과대학'; DNAME DNAME ---------------- ---------------- 정보미디어학부 멀티미디어학과 정보미디어학부 컴퓨터공학과 메카트로닉스학부 기계공학과 메카트로닉스학부 전자공학과

7. 각 학과별로 학과번호 , 학과 이름, 소속 학생 이름, 학생의 지도교수 이름,
지도교수 직급, 지도교수 소속 학과번호 , 지도교수소속 학과명을 출력
(단, 지도교수가 없는 학생과 지도 학생이 없는 교수 모두 출력)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64

SQL> select d.deptno, d.dname, s.name, p.name, p.position, dept.deptno, dept.dname 2 from student s 3 full outer join professor p on p.profno=s.profno 4 left outer join department d on d.deptno = s.deptno 5 left outer join department dept on dept.deptno = p.deptno 6 order by 1,3; DEPTNO DNAME NAME NAME POSITION DEPTNO DNAME ---------- ---------------- ---------- ---------- -------------------- ---------- ---------------- 101 컴퓨터공학과 김영균 이만식 부교수 101 컴퓨터공학과 101 컴퓨터공학과 류민정 전은지 전임강사 101 컴퓨터공학과 101 컴퓨터공학과 박미경 101 컴퓨터공학과 서재진 101 컴퓨터공학과 이광훈 성연희 조교수 101 컴퓨터공학과 101 컴퓨터공학과 임유진 전은지 전임강사 101 컴퓨터공학과 101 컴퓨터공학과 전인하 성연희 조교수 101 컴퓨터공학과 101 컴퓨터공학과 지은경 전은지 전임강사 101 컴퓨터공학과 102 멀티미디어학과 김진영 권혁일 교수 102 멀티미디어학과 102 멀티미디어학과 오유석 권혁일 교수 102 멀티미디어학과 102 멀티미디어학과 윤진욱 권혁일 교수 102 멀티미디어학과 102 멀티미디어학과 하나리 201 전자공학과 김진경 이재우 조교수 201 전자공학과 201 전자공학과 박동진 201 전자공학과 이동훈 201 전자공학과 조명훈 염일웅 전임강사 102 멀티미디어학과 김도훈 교수 101 컴퓨터공학과 남은혁 부교수 202 기계공학과 19 rows selected. select d.deptno, d.dname, s.name, p.name, p.position, p.deptno, d.dname from department d, student s, professor p where d.deptno=s.deptno and s.profno=p.profno(+) union select d.deptno, d.dname, s.name, p.name, p.position, p.deptno, d.dname from department d, student s, professor p where s.profno(+)=p.profno and d.deptno=p.deptno; DEPTNO DNAME NAME NAME POSITION DEPTNO DNAME ---------- ---------------- ---------- ---------- -------------------- ---------- ---------------- 101 컴퓨터공학과 김영균 이만식 부교수 101 컴퓨터공학과 101 컴퓨터공학과 류민정 전은지 전임강사 101 컴퓨터공학과 101 컴퓨터공학과 박미경 컴퓨터공학과 101 컴퓨터공학과 서재진 컴퓨터공학과 101 컴퓨터공학과 이광훈 성연희 조교수 101 컴퓨터공학과 101 컴퓨터공학과 임유진 전은지 전임강사 101 컴퓨터공학과 101 컴퓨터공학과 전인하 성연희 조교수 101 컴퓨터공학과 101 컴퓨터공학과 지은경 전은지 전임강사 101 컴퓨터공학과 101 컴퓨터공학과 김도훈 교수 101 컴퓨터공학과 102 멀티미디어학과 김진영 권혁일 교수 102 멀티미디어학과 102 멀티미디어학과 오유석 권혁일 교수 102 멀티미디어학과 102 멀티미디어학과 윤진욱 권혁일 교수 102 멀티미디어학과 102 멀티미디어학과 하나리 멀티미디어학과 102 멀티미디어학과 염일웅 전임강사 102 멀티미디어학과 201 전자공학과 김진경 이재우 조교수 201 전자공학과 201 전자공학과 박동진 전자공학과 201 전자공학과 이동훈 전자공학과 201 전자공학과 조명훈 전자공학과 202 기계공학과 남은혁 부교수 202 기계공학과 19 rows selected.

8. 교수 테이블에서 학과번호 ,교수 이름, 교수 별 지도 학생 수 , 학과별 소계학생수, 총 학생수를 출력

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

SQL> select p.deptno, p.name, count(s.name) s_count 2 from professor p, student s 3 where s.profno=p.profno 4 group by rollup(p.deptno, p.name); DEPTNO NAME S_COUNT ---------- ---------- ---------- 101 성연희 2 101 이만식 1 101 전은지 3 101 6 102 권혁일 3 102 3 201 이재우 1 201 1 10 9 rows selected.