08. 실습(20081029)

E-R 다이어그램을 이용 테이블 만들기

 

 

1. CREATE TABLE 교수(
    교수번호 number(10) primary key
   , 교수이름 varchar2(50) not null
   , 학과 varchar2(20) not null
   , 전공 varchar2(20) not null
   );
   
2.  
3. CREATE TABLE 학생(
      학번 number(10) primary key
   , 이름 varchar2(50) not null
   ,             주소 varchar2(200) 
   ,             학년 number(1)  
   ,  지도교수번호 number(10) references 교수(교수번호) 
   );
   
4.  
5. CREATE TABLE 과목(
    과목번호 varchar2(4) primary key
   , 과목이름 varchar2(50)
   , 학점 number(1)
   , 강의교수번호 number(10) references 교수(교수번호)
   , 강의실 varchar2(3) 
   , 강의시간 varchar2(5)
   );
   
6.  
7. CREATE TABLE 등록(
    학번 number(10) references 학생(학번)
   , 과목번호 varchar2(4) references 과목(과목번호)
   , 성적 varchar2(2)
   ,            constraint enroll_pk primary key(학번,과목번호)
   );

 

 

교재 P4-7 문제 실습

(1) 31번 부서의 이름과 그 부서에 근무하는 사원의 이름을 출력하시오

1. SQL> conn test00/test00
   Connected.
2. 
   SQL> SELECT d.name, e.last_name
     2  FROM s_dept d, s_emp e   // Alias => s_dept는 d로, s_emp는 e로~
     3  WHERE d.id = e.dept_id AND d.id = 31;
3.  
4. NAME                      LAST_NAME
   ------------------------- ----------------
   Sales                     Nagayama
   Sales                     Magee

 

(2) 1, 2번 지역의 이름과 그곳에 있는 부서의 이름을 출력하시오

1. // 잘못쳤음~ 
2. SQL> SELECT d.name, d.name
     2  FROM s_region r, s_dept d
     3  WHERE r.id = d.region_id AND (r.id = 1 or r.id = 2);
3.  
4. NAME                      NAME
   ------------------------- -------------------------
   Administration            Administration
   Finance                   Finance
   Operations                Operations
   Operations                Operations
   Sales                     Sales
   Sales                     Sales
5. 6 rows selected.

 

(3) Bunny Boot라는 제품에 대한 주문번호, 주문날짜, 수량을 출력하시오.

1. SQL> SELECT o.id, o.date_ordered, i.quantity, p.name
     2  FROM s_ord o, s_item i, s_product p
     3  WHERE o.id = i.ord_id AND i.product_id = p.id AND p.name = 'Bunny Boot';
2.  
3.         ID DATE_ORD   QUANTITY
   ---------- -------- ----------
   NAME
   --------------------------------------------------
          100 92/08/31        500
   Bunny Boot
4.        109 92/09/08        150
   Bunny Boot

 

(4) Asia 지역 고객의 이름과 전화번호, 담당 영업사원의 이름을 출력하시오.

1. SQL> SELECT c.name, c.phone, e.last_name
     2  FROM s_customer c, s_region r, s_emp e
     3  WHERE c.region_id = r.id AND c.sales_rep_id = e.id AND r.name = 'Asia';
2.  
3. NAME                                               PHONE
   -------------------------------------------------- -------------------------
   LAST_NAME
   -------------------------
   OJ Atheletics                                      81-20101
   Nguyen
4. Delhi Sports                                       91-10351
   Nguyen
5. Kam's Sporting Goods                               852-3692888
   Dumas

 

 

 Oracle 1 문제

• 1~3번 (test00 계정 사용), 4~5번(scott 계정 사용)

(1) s_emp 테이블에서 title의 두번째 문자가 'a' 인 사원의 이름은?

1. SQL> SELECT last_name
     2  FROM s_emp
     3  WHERE title LIKE '_a%';
2.  
3. LAST_NAME
   -------------------------
   Urguhart
   Menchu
   Biri
   Catchpole
   Havel
   Magee
   Giljum
   Sedeghi
   Nguyen
   Dumas
4. 10 rows selected.

 

(2) 연봉(salary*12) 이 1000 에서 2000 사이인 사원의 이름은?

1. SQL> SELECT last_name
     2  FROM s_emp
     3  WHERE salary*12 BETWEEN 1000 AND 2000;
2. no rows selected

 

(3) title이 'Stock Clerk'인 사원의 봉급을 역순으로 정렬하여 출력.

1. SQL> SELECT last_name, salary
     2  FROM s_emp
     3  WHERE title='Stock Clerk'
     4  ORDER BY salary DESC;
2.  
3. LAST_NAME                     SALARY
   ------------------------- ----------
   Maduro                          1400
   Nozaki                          1200
   Schwartz                        1100
   Smith                            940
   Dancs                            860
   Markarian                        850
   Chang                            800
   Patel                            795
   Patel                            795
   Newman                           750
4. 10 rows selected.

 

(4)사원번호, 사원이름,  부서 이름,  부서위치를 출력하세요

1. SQL> SELECT e.empno 사원번호, e.ename 사원이름, d.dname 부서이름, d.loc 부서위치
     2  FROM emp e, dept d
     3  WHERE e.deptno=d.deptno;
2.  
3.   사원번호 사원이름   부서이름       부서위치
   ---------- ---------- -------------- -------------
         7782 CLARK      ACCOUNTING     NEW YORK
         7839 KING       ACCOUNTING     NEW YORK
         7934 MILLER     ACCOUNTING     NEW YORK
         7369 SMITH      RESEARCH       DALLAS
         7876 ADAMS      RESEARCH       DALLAS
         7902 FORD       RESEARCH       DALLAS
         7788 SCOTT      RESEARCH       DALLAS
         7566 JONES      RESEARCH       DALLAS
         7499 ALLEN      SALES          CHICAGO
         7698 BLAKE      SALES          CHICAGO
         7654 MARTIN     SALES          CHICAGO
         7900 JAMES      SALES          CHICAGO
         7844 TURNER     SALES          CHICAGO
         7521 WARD       SALES          CHICAGO
4. 14 rows selected.

 

(5)사원번호, 사원이름,  부서 이름,  봉급등급을 출력하세요

1. SQL> SELECT e.empno 사원번호, e.ename 사원이름, d.dname 부서이름, s.grade 봉급등급
     2  FROM emp e, dept d, salgrade s
     3  WHERE e.deptno=d.deptno
     4  AND e.sal between s.losal AND s.hisal;
2.  
3.   사원번호 사원이름   부서이름         봉급등급
   ---------- ---------- -------------- ----------
         7369 SMITH      RESEARCH                1
         7900 JAMES      SALES                   1
         7876 ADAMS      RESEARCH                1
         7521 WARD       SALES                   2
         7654 MARTIN     SALES                   2
         7934 MILLER     ACCOUNTING              2
         7844 TURNER     SALES                   3
         7499 ALLEN      SALES                   3
         7782 CLARK      ACCOUNTING              4
         7698 BLAKE      SALES                   4
         7566 JONES      RESEARCH                4
         7788 SCOTT      RESEARCH                4
         7902 FORD       RESEARCH                4
         7839 KING       ACCOUNTING              5
4. 14 rows selected.

 

(6)부서번호가 10번인 사원의 사원번호, 사원이름,  부서 이름,  봉급등급을 출력하세요

1. SQL> SELECT e.empno 사원번호, e.ename 사원이름, d.dname 부서이름, s.grade 봉급등급
     2  FROM emp e, dept d, salgrade s
     3  WHERE e.deptno=d.deptno
     4  AND e.sal between s.losal AND s.hisal
     5  AND e.deptno=10;
2.  
3.   사원번호 사원이름   부서이름         봉급등급
   ---------- ---------- -------------- ----------
         7934 MILLER     ACCOUNTING              2
         7782 CLARK      ACCOUNTING              4
         7839 KING       ACCOUNTING              5

이 글은 스프링노트에서 작성되었습니다.