SQL(3)- order by 복습, 단일 함수 中 문자 함수

---

문제 59. 직업이 SALESMAN이 아닌 사원들의 이름과 월급과 직업과 입사일을 출력하는데,

              먼저 입사한 사원순으로 출력하시오  

SQL> 3 SELECT ename, sal, JOB, hiredate
           1 FROM EMP
            2 WHERE JOB != 'SALESMAN'

            4 ORDER BY hiredate ASC;

*keyword 앞에 번호들은 실행 순서 

---

문제 60. 20번 부서번호에서 근무하는 사원들의 이름과 월급과 연봉(sal*12)을 출력하는데

              연봉이 높은 사원부터 출력하시오 

SQL> SELECT ename, sal, sal*12 as "연봉"
           FROM EMP
           WHERE deptno = 20

           ORDER BY "연봉" DESC;

---

문제 61. 우리반 테이블에서 이름과 나이를 출력하는데 나이가 높은 사람부터 출력하시오!

SQL >SELECT ename, age

            FROM emp2

            ORDER BY age DESC;

---

문제 62. 우리반 테이블에서 여학생의 이름과 주소와 나이를 출력하는데

                 나이가 높은 순서대로 출력하시오! 

SQL>SELECT ename, address, age

          FROM emp2
          WHERE gender = '여자'

          ORDER BY age DESC;

---

문제 63. 우리반 테이블에서 성씨가 김씨인 학생의 이름과 나이를 출력하시오! 

SQL>SELECT ename, age

          FROM emp2

          WHERE ename LIKE '김%';

---

문제 64. 나이가 30대인 학생들의 이름과 성별과 나이를 출력하시오!

SQL>SELECT ename, gender, age

           FROM emp2

           WHERE age BETWEEN 30 AND 39;

---

문제 65. 서울에 사는 학생들에 이름과 주소를 출력하시오!   

SQL>SELECT ename, address

           FROM emp2

           WHERE address LIKE '%서울%';

---

문제 66. 전공이null인 학생들의 이름과 전공을 출력하시오!

SQL> SELECT ename, major

            FROM EMP2

            WHERE major is NULL;

주의! null 같은 '='가 아닌 is 를 사용

---

문제 67. 전공을 출력하는데 중복제거해서 출력하시오!

SQL> SELECT DISTINCT major

            FROM EMP2;

---

문제 68. 전공이 컴퓨터 공학이 아닌 학생들의 이름과 전공을 출력하시오!

SQL > SELECT ename, major

            FROM EMP2

            WHERE major <> '컴퓨터공학'; 

* null 이 아닌 이상  'is'를 쓰지 않는다. 

---

문제 69. 주소가 서울이 아닌 학생들의 이름과 주소를 출력하시오! 

SQL > SELECT ename, address

            FROM EMP2

            WHERE address NOT LIKE '%서울%';

* 'not like' 도 사용가능

---

문제 70. 학원까지 오는 시간이 가장 적게 걸리는 학생부터 이름과 주소와 시간을 출력하시오! 

SQL > SELECT ename, address, distance

            FROM EMP2

            ORDER BY distance ASC;

---

문제 71.  전공이 컴퓨터 공학이고 성별이 여학생인 학생들의 이름과 전공과 주소를 출력하는데,

               집과의 걸리가 먼 학생순으로 출력하시오 

SELECT ename, major, address
  FROM EMP2
  WHERE major ='컴퓨터공학' AND gender ='여자'

  ORDER BY distance DESC;

---

문제 72. 같은 라인에 있는 학생들의 결과가 아래와 같이 출력되게하시오! 

            곽민영 학생의 나이는 34이고 전공이 컴퓨터 공학이며 집에서 학원으로 오는 시간은 3분 입니다. 

SELECT ename||' 학생의 나이는'||age||'이고 전공이 '||major||'이며 집에서 학원으로 오는 시간은 '||distance||'분 입니다.'
  FROM EMP2

  WHERE empno BETWEEN 16 AND 19;

* MS_SQL 구현시 

  SELECT concat( ename,' 학생의 나이는',age,'이고 전공이 ',major,'이며 집에서 학원으로 오는 시간은 ',distance,'분 입니다.')

  FROM EMP2

  WHERE empno BETWEEN 16 AND 19;

---

3장 함수 

---

- 함수를 왜 배워야하는가? 

  아래와 같은 정보을 알기 위하여 배워야 한다. 

ex)

우리반 테이블에서 전공, 전공별 인원수를 출력하시오!

우리반 테이블에세 가장 나이가 어린 학생의 이름을 출력하시오!

메일 도메인, 메일 도메인별 건수를 출력하시오! 

- 함수의 종류 2가지? 

1. 단일행 함수

   - 문자

   - 숫자

   - 날짜

   - 변환

   - 일반 

1) 문자 함수 

1. 대소문자 변환 함수 

     - lower : 소문자로 출력하는 함수

     - upper : 대문자로 출력하는 함수

     - initcap: 첫번째 철자는 대문자 나머지는 소문자로 출력하는 함수 

2) 문자 조작 함수 

      - concat : 두개의 컬럼의 값을 연결하는 함수

      - substr : 특정 철자를 잘라내는 함수

      - length : 철자의 길이를 출력하는 함수 

      - instr : 특정 철자의 위치를 출력하는 함수

      - lpad : 왼쪽에 특정값을 채워넣는 함수 

      - rpad : 오른쪽에 특정값을 채워넣는 함수 

      - trim : 특정 철자나 값을 잘라내는 함수 

      - replace : 특정 철자로 변경하는 함수 

2. 복수행 함수

   - max

   - min

   - count

   - avg

   - sum

---

문제 73.  아래와 같이 결과를 출력하시오! 

SQL> select upper(ename), lower(ename), initcap(ename)

           from emp;

* MS_SQL 로 작성시 

  - MS_SQL은 inticap 함수가 없어서 Oracle의  inticap 함수 처럼 나타내기 위해서는 아래와 같이 사용해야 한다. 

    SQL> select upper(ename), lower(ename), concat(upper(left(ename,1)),lower(substring(ename,2,10)))

           from emp;

---

문제 74. 이름이 smith인 사원의 이름과 월급을 출력하시오! 

              아래 ? 부분을 채워 넣으시오!

     where ? = 'smith'

SQL> SELECT ename, sal
           FROM EMP

           WHERE LOWER(ename) = 'smith';

* 자료가 대문자 인지 소문자 인지 확실히 모를 때 유용하게 사용할 수 있다.

---

문제 75. 이름과 월급을 붙여서 출력하시오! 

 

 SQL> select concat(ename, sal)

            from emp;

 

* concat은 2개의 컬럼만 붙일 수 있다. 

* MS_SQL 에서 concat 함수 

  - MS_SQL에서는 concat 안에 여러개의 컬럼은 붙일 수 있다. 

---

문제 76. 우리반 테이블에서 이름과 나이를 붙여서 출력하시오!

 SQL> SELECT CONCAT(ename, age)

            FROM EMP2;

---

문제 77. 이름을 출력하는데 이름의 첫번째 철자만 출력하시오! 

SQL> select ename, substr(ename,1,1)

           from emp;

* substr(컬럼명, 숫자1, 숫자2) 

- 숫자1 : 시작점을 의미한다. 

- 숫자 2: 시작점 부터 몇개를 출력할 것인지 입력한다. 

* MS_SQL 에서 해당 철자만 사용하기 

  - Oracle substr 함수에 해당 하는 MS_SQL 함수는 substring 이다. 

  SQL> select ename, substring(ename,1,1)

           from emp;

---

문제 78. 이름의 세번째 철자만 출력하는데 소문자로 출력하시오! 

SQL>SELECT LOWER(substr(ename,3,1))

           FROM EMP;

* MS_SQL 에서 출력 방법 

SQL> SELECT LOWER(substring(ename,3,1))

          FROM EMP;

---

문제 79. 아래의 쿼리의 결과를 initcap 사용하지 말고,

              upper, lower, substr || 를 사용해서 출력하시오!

예시) select initcap(ename)

           from emp;

SQL> SELECT UPPER(substr(ename,1,1))||LOWER(substr(ename,2,8))

            FROM EMP;

---

문제 80. 우리반 테이블에서 성씨가 김씨, 이씨, 박씨인 학생들의 이름과 주소를 출력하시오!

              (like 사용하지 말고)

SQL> SELECT ename, address
           FROM EMP2

           WHERE substr(ename,1,1) IN ('김','이','박');

* MS_SQL에서 출력 방법 

SQL> SELECT ename, address
           FROM EMP2

           WHERE substring(ename,1,1) IN ('김','이','박');

---

문제 81. 이름과 이름의 철자의 갯수를 출력하시오 !

 SQL> select ename, length(ename)

            from emp;

* MS_SQL 에서 철자의 갯수 출력 방법 

- Oracle length에 해당하는 MS_SQL 의 함수는 len 이다 

SQL> select ename, len(ename)

         from emp;

---

문제 82. 이름, 이메일, 이메일의 철자의 갯수를 출력하는데, 

             이메일의 철자의 갯수가 가장 많은 학생부터 출력하시오! 

SQL>SELECT ename,email, length(email)
          FROM EMP2

          ORDER BY LENGTH(email) DESC;

Tip.

  select 절에서 length에 alias 를 주고 order by에 alias를 활용하여도 됨

* MS_SQL에서 위와 같은 사용 법

SQL>SELECT ename,email, len(email)
          FROM EMP2

          ORDER BY LEN(email) DESC;

---

문제 83. 이름 이메일의 도메인만 출력하시오!

SQL> SELECT substr(email,instr(email,'@')+1,(instr(email,'.')-instr(email,'@')-1))

           FROM EMP2;

* 다른 방법들도 있다. length를 사용해서 뒤에서 부터 빼주는 방법도 있다. 

   

   SQL> SELECT substr(email,instr(email,'@')+1, length(email)-instr(email,'@')-4 )

              FROM EMP2;

* MS_SQL 로 위의 쿼리 변환 시 

 - 함수 변화가 필요하다.  

 SQL> SELECT substring(email,patindex('%@%',email)+1,(patindex('%.%',email)-patindex('%@%',email)-1))

          FROM EMP2 ;

---

문제 84. 이름을 출력하고 그 옆에 이름에 철자 A가 몇번째 있는지 출력하시오! 

 SQL> select ename, instr(ename, 'A')

             from emp;

*instr/ 해당 문자가 몇 번째에 위치하는지 알수 있게 해주는 keyword

* MS_SQL에서 instr 대체 사용법 

   - MS_SQL에서 instr을 대체하는 함수는 patindex 이다 

 SQL> select ename, patindex('%A%',ename)

             from emp;

---

문제 85. 우리반 테이블에서 이메일을 출력하고

              그 옆에 이메일의 @ 몇번째 자리에 있는지 출력하시오!

SQR> SELECT email, instr(email,'@')

            FROM EMP2; 

---

문제 86. 이메일을 출력하는데 @ 다음의 철자부터 출력하시오! 

SQR> SELECT substr(email,instr(email,'@')+1)

            FROM EMP2;

*substr에서 두번째 자리에 아무것도 쓰지 않으면 끝까지 출력 된다.

* MS_SQL에서 substring 사용법 

   - MS_SQL에서 substring은 무조건 3개의 인자가 있어야지 쿼리가 실행된다. 

   - 아래의 쿼리는 필요 인자 부족으로 error가 발생한다. 

SQL> SELECT substring(email,patindex('%@%',email)+1)

         FROM EMP;

---

문제 87. 이름과 월급을 출력하는데 월급을 10자리로 출력하고, 

             월급이 출력되는 나머지 자리에*를 출력하시오! 

  select ename, lpad(sal, 10, '*')

     from emp;

*lpad/rpad

  - 자리 개수를 선정하고 나머지는 다른 것으로 채우겠다. 

  - ex) lpad(컬럼명, 자리개수, 채울 내용)

* MS_SQL 에서 lpad 대체법 

 - MS_SQL은 lpad가 사용되지 않는다. 그래서 replicate를 사용해서 쿼리를 작성해야 한다. 

   또한, 숫자와 같은 경우 형변환이 일어나서 기존에 숫자 값도 형변환을 해줘야 한다. 

SQL> select replicate('*',10-len(sal))+cast(sal as varchar) 

         from emp ;

---

문제 88. 아래의 data를 입력하고 이름이 JACK인 사원의 이름과 월급을 출력하시오! 

SQL> insert into emp(empno, ename, sal)

           values(1235,'  JACK  ', 3600);

         select ename, sal

           from emp

           where trim(ename) = 'JACK';

*trim 

 - 앞, 뒤의 공백을 지워준다. (단, 중간에 공백은 지워주지 못한다.)

* MS_SQL 의 Trim 함수 대체 방법 

 - MS_SQL은 trim 함수를 사용할 수 없어서 ltrim 과 rtrim을 중첩 사용하여 Oracle의 trim 함수와 같은 효과를 낼 수 있다. 

SQL> select ename, sal

         from emp

       where rtrim(ltrim(ename)) = 'JACK';

---

문제 89. 이름과 전공을 출력하는데 전공을 출력할때에 과 가 안나오게 하시오! 

SQL> SELECT ename, TRIM('과' FROM major)

           FROM EMP2;

* MS_SQL 에서 Trim 함수 대체 방법 2 

  - MS_SQL 에서는 Trim 함수를 사용할 수 없어서 replace를 활용하여서 쿼리를 작성해야 한다. 

SQL> SELECT ename, replace(substring(major,1,1),'과','') + substring(major,2,(len(major)-2)) + replace(substring(mjor,len(ename),1),'과','')

          FROM EMP2 ;

---

문제 90. 이름과 월급을 출력하는데 월급을 출력할때에 0을 *로 출력하시오! 

SQL> select ename, replace(sal, 0, '*')

          from emp;

* replace

   - 대체하는 keyword

   - replace(컬럼명, 대체하고 싶은 문자 or 숫자, 대체할 숫자 or 문자)

   - regexp_replace(sal, '[0-2]', '*')를 사용해서 좀 더 확장할 수 있다. 

---

문제 91. 우리반 테이블에서 20대인 학생들 중에 naver 메일을 사용하는 학생들의 이름과 주소와 나이와 메일주소를 출력하는데, 나이가 높은순서대로 출력을 하고 그리고 컬럼명이 아래와 같이 출력되게 하시오! 

      학생이름, 사는 곳, 메일주소, 나이 

SQL> SELECT ename AS "학생이름", address AS "사는곳", email AS "메일주소", age AS "나이"

            FROM EMP2
            WHERE age BETWEEN 20 AND 29
               AND email LIKE '%naver%'

           ORDER BY age DESC;