SQL 문법( Join – 2 )

1. 학번이 10101인 학생의 학번, 이름, 학과이름 과 학과 위치를 출력
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 이상인 학생의 학번, 이름,체중,학과이름,학과위치를 출력
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테이블을 사용해 사번, 성명, 생일 출력. 단, 자신보다 생일이 빠른 사람수를 오름차순 정렬하여 출력
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을 이용하여 학생 이름과 소속 학과 이름을 학과 이름, 학생 이름순으로 정렬하여 출력
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번 학과에 소속된 학생들의 이름과 지도 교수이름을 출력 이때 지도교수가 배정되지 않은 학생도 함께 출력
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. 공과대학에 소속된 학부와 학과 이름을 출력
SQL> select b.dname, a.dname
  2  from department a, department b
  3  where a.college = b.deptno and b.dname <> '공과대학';

DNAME            DNAME
---------------- ----------------
정보미디어학부   멀티미디어학과
정보미디어학부   컴퓨터공학과
메카트로닉스학부 기계공학과
메카트로닉스학부 전자공학과
7. 각 학과별로 학과번호 , 학과 이름, 소속 학생 이름, 학생의 지도교수 이름, 지도교수 직급, 지도교수 소속 학과번호 , 지도교수소속 학과명을 출력 (단, 지도교수가 없는 학생과 지도 학생이 없는 교수 모두 출력)
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. 교수 테이블에서 학과번호 ,교수 이름, 교수 별 지도 학생 수 , 학과별 소계학생수, 총 학생수를 출력
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.

You may also like...

답글 남기기

이메일 주소는 공개되지 않습니다. 필수 항목은 *(으)로 표시합니다