SQL정리 1-3장

10. SET 명령어 : Sql Plus 환경 설정

가. SET HEADING OFF : 컬럼제목 표시유무

나. SET LINESIZE n : 한 라인에 나올 글자수 지정(가로)

다. SET PAGESIZE n : 한 페이지에 출력될 페이지 크기(세로)

라. COLUMN col_name FORMAT format_type : 컬럼 col_name의 출력 형태 지정

      예) column dname format a10 : dname 을 문자 10자로 지정

          column sal format 0,000,000 : 7자리 숫자로 지정, 천 단위 콤마 사용(,)

마. COLUMN col_name CLEAR : 지정한 col_name의 출력형태 해제

바. SHOW ALL : 환경설정 모두 보기

사. SET AUTOCOMIT ON : 자동 commit 활성화

아. SET PAUSE ON : 한 화면에 모두 나오지 않은 결과값 출력 시 enter 값에 의해 한 페이지씩 이동

 자. SET TIMING ON : 쿼리 실행에 걸린 시간 표시

문제 : 교수테이블에서 교수 이름과 직급 칼럼 사이에, '교수의 직급은' 이라는 문자열을 추가하고 칼럼 이름을 "Title of Professor" 라는 별명을 사용하여 출력하여라.

SQL> select name||'교수의 직급은'||position as "Title of Professor"

     from professor;

문제 : 학생 테이블에서 '홍길동 의 키는 180, 몸무게는 55'와 같은 형식으로 출력되도록 리터럴 문자를 추가하고, 칼럼이름은 "학생의 키와 몸무게"라는 별명으로 출력되도록 출력하시오.

SQL> select name||'의 키는'||height||'몸무게는'||weight as "학생의 키와 몸무게"

     from student;

문제 : 홍길동 (교수) , 홍길동 '교수' 이렇게 나오도록 출력해보세요

     ※ 참고 :　표현식에서 single quotation(') 표현 하는 방법 : single qoutation(')을 연달아 2번 사용

SQL> select name||'('||position||'),'

     ||name||''''||position||'''' as "교수님들"

     from professor;

문제 : 학생 테이블에서 중복되는 행을 제외한 학과번호와 학년을 출력하여라.

※ 참고 :　distinct 는 데이터 건수가 많을 수록 성능에 심각한 영향

SQL> SELECT deptno, grade FROM student;  - 1

SQL> SELECT DISTINCT deptno, grade FROM student; -2

예제 : 교수 테이블에서 교수 이름, 급여 그리고 보너스를 포함한 연봉을 출력하여라.

        단, 보너스를 포함한 연봉은 급여 *12를 한 결과에 보너스 100을 더한 값으로 계산 한다.

SQL> SELECT name, sal, sal*12+100 FROM professor;

or 과 in 사용 법 = 결과 같다.

문제 : 교수테이블(professor)에서 직급이 조교수 또는 전임강사인 교수의 번호, 이름, 직급, 학과번호를 출력하여라.

SQL> select profno, name, position, deptno

     from professor

     where position in ('조교수','전임강사');

SQL> select profno, name, position, deptno

     from professor

     where position='조교수' or position='전임강사';

예제 : IN 연산자를 사용하여 102번 학과와 201번 학과 학생의 이름 , 학년, 학과번호를 출력하여라

SQL> SELECT name, grade, deptno

     FROM student

     WHERE deptno IN (102, 201);

LIKE

※ 참고 : % = *, _ = ? 와 비슷

예제 : 학생 테이블에서 성이 '김'씨인 학생의 이름,학년, 학과번호를 출력하여라.

SQL> SELECT name, grade, deptno

     FROM student

     WHERE name LIKE '김%';

문제： 학생 테이블에서 이름이 3글자, 성은 '김'씨고 마지막 글자가 '영' 으로 끝나는 학생의 이름, 학년, 학과번호를 출력하여라.

SQL>  select name, grade, deptno from student

      where name LIKE '김_영';

문제 : 성부분에 ㅇ 이 들어가는 사람을 출력하라.

SQL> select name

     from student

     where name between '아%' and '자%'

     order by 1;

예제 : 학생 테이블에서 1학년 학생만 검색하여 학번, 이름, 학과 번호를 출력하시오.

SQL> SELECT studno, name, deptno

     FROM student

     WHERE grade = '1';

문제 : 학생테이블에서 부서번호가 101번인 학생들의 학번 , 이름, 학년을  출력하시오.

※ 참고 :　Where deptno=101      - ‘ 표시가 없으면 숫자로 인식

           Where deptno=‘101’      ‘ 표시가 있으면 글자로 인식 (대 소문자 구분)

SQL> select studno, name, grade

     from student

     where deptno = '101';

예제 : 학생 테이블에서 몸무게가 70kg 이하인 학생만 검색하여

       학번, 이름, 학년, 학과번호, 몸무게를 출력하여라.

SQL> SELECT studno, name, grade, deptno, weight

     FROM student

     WHERE weight <= 70;

예제 : 학생 테이블에서 1학년이면서 몸무게가 70kg 이상인 학생만 검색하여

        이름, 학년, 몸무게, 학과 번호를 출력하여라.

SQL> SELECT name, studno, grade, weight, deptno

     FROM student

     WHERE grade = '1'

     AND weight >= 70;

예제 : BETWEEN 연산자를 사용하여 몸무게가 50kg에서 70kg사이인 학생의

       학번, 이름, 몸무게를 출력하여라.

SQL> SELECT studno, name, weight

     FROM student

     WHERE weight BETWEEN 50 AND 70;

문제 : 키가 160cm 이상 180cm 이하인 학생의 학번,이름,키를 출력하시오.

SQL> select studno, name, height

     from student

     where height between 160 and 180;

※ 참고

Null = 무슨 값인지 모른다

Null != 0

Null 에 어떠한 연산작용을 하더라도 값은 null 이다.

예제 : 교수 테이블에서 이름 ,직급, 보직 수당을 출력하여라.

SQL> SELECT name, position, comm

     FROM professor;

예제 : 교수 테이블에서 이름 ,직급, 보직 수당을 출력하여라.

SQL> SELECT name, position, comm

     FROM professor;

IS NULL 연산자 : 칼럼 값중에서 NULL 을 포함하는 행을 검색하기 위해 사용

IS NOT NULL 연산자 : NULL을 포함하지 않는 행을 검색하기 위해 사용

예제 : 교수 테이블(professor)에서 보직수당이 없는 교수의 이름, 직급, 보직수당을 출력하여라.

SQL> SELECT name, position, comm

     FROM professor

     WHERE comm IS NULL;

예제 : 102번 학과의 학생중에서 1학년 또는 4학년 학생의 이름, 학년, 학과 번호를 출력하여라.

SQL> SELECT name, grade, deptno

     FROM student

     WHERE deptno = 102

           AND (grade = '4' OR grade = '1');

SQL> select name, grade, deptno

     from student

     where (deptno = 102 and grade = '4') or grade = '1';

문제 : 102번 학과의 학생 중에서 4학년 학생이거나 소속학과에 상관없이 1학년

       학생들의 이름, 학년, 학과 번호를 출력하여라.

SQL> select name, grade, deptno

     from student

     where grade = 1

     or (deptno = 102 and grade = 4);    

=-=-=-=

※ 집합연산

두개를 더할때 select 바로 뒤에 오는 것  내용은 틀려도 되나 타입은 같아야함 (숫자, 글자)

컬럼 개수도 같게 맞춰줘야 한다. (숫자면 0 을써 줘서 개수를 맞춰주자) 그럼 결과가 나올 것임.

Union           +

Union all       +

Minus           -

Intersect     교집합

예제 : stud_heavy 테이블과 stud_101 테이블에 대해 UNION , UNION ALL 연산을 각각

       수행한 결과를  출력하여라

<UNION사용결과>                 

SQL> SELECT studno, name      FROM stud_heavy      UNION       SELECT studno, name      FROM stud_101;

SQL> SELECT studno, name      FROM stud_heavy      UNION ALL      SELECT studno, name      FROM stud_101;

SQL> SELECT studno, name      FROM stud_heavy      INTERSECT      SELECT studno, name      FROM stud_101;

                           

예제. stud_heavy 테이블과 stud_101 테이블에 대해 MINUS  연산을 수행하여 1학년 중에서 몸무게가 70kg 이상인 학생들의 학번, 이름을 출력하되 101번 학과에 소속된 학생들은 제외하고 출력하여라.

# A MINUS B SQL> SELECT studno 학번, name 이름      FROM stud_heavy      MINUS       SELECT studno, name      FROM stud_101;

# B MINUS A SQL> SELECT studno 학번, name 이름      FROM stud_101      MINUS      SELECT studno, name      FROM stud_heavy

=-=-=-=

ORDER BY 기본적인 정렬방법

* 문자값: 알파벳순 출력, 한글은 가나다 순으로 출력됨

* 숫자값: 가장 작은 값부터 먼저 출력됨

* 날짜값: 과거의 날짜부터 출력됨

* ASC : 오름차순으로 정렬하는 경우에 사용하며 기본값임

* DESC : 내림차순으로 정렬하는 경우에 사용하며 생략 불가능함

※ 참고

오름차순 : 첫번째 데이터보다 다음 데이터가 크면 오름차순

가 A 1 나 B 2 다 C 3

날짜 = 최근날짜가 크다. 10/1 작고 10/2 10/3 크다

예제 : 학생 테이블에서 이름을 가나다 순으로 정렬하여 이름, 학년, 전화번호를 출력하여라.

SQL> SELECT name, grade, tel

     FROM student

     ORDER BY name;

예제 : 학생 테이블에서 모든 학생에 대해 학과번호를 오름차순으로 먼저

       정렬하고 , 같은 학과 학생들은 학년이 높은 순으로 다시 정렬하여 학번,

       이름, 학년, 학과번호, 사용자 아이디를 출력하여라

SQL> SELECT studno, name, grade, deptno, userid

     FROM student

     ORDER BY deptno, grade DESC;

예제 : 학생 테이블에서 SELECT 절의 2번째 칼럼인 userid를 오름차순으로 정렬하여 이름과 사용자 아이디를 출력하여라

SQL> SELECT name, userid

     FROM student

     ORDER　BY 2;