쿼리문 연습

CODEDRAGON Development/Database

반응형

   

쿼리문 연습

   

1

SELECT AGE, SALARY FROM EMPLOYEE

WHERE AGE=30;

SELECT AGE, SALARY FROM EMPLOYEE

WHERE NAME='KIM';

SELECT AGE, SALARY FROM EMPLOYEE

WHERE SALARY=3000000;

   

   

   

2

사원테이블(emp)에서

부서(DEPTNO)별 평균 급여(SAL)가 400만원 이상인 부서 중에서 부서의 부서번호(deptno)와, 평균급여(AVG), 최소급여액(MIN), 최대급여액(MAX)을 구하라.

   

사원테이블 구조는 아래와 같다.

EMP TABLE

PK

COLUMN

DATA_TYPE

PK

EMPNO

NUMBER(4)

   

ENAME

VARCHAR2(10)

   

SAL

NUMBER(9)

   

DEPTNO

NUMBER(2)

   

SELECT DEPTNO, AVG(SAL), MIN(SAL), MAX(SAL) FROM EMP

GROUP BY DEPTNO HAVING SUM(SAL) >= 4000000;

②.

SELECT DEPTNO, AVG(SAL), MIN(SAL), MAX(SAL) FROM EMP

GROUP BY DEPTNO HAVING AVG(SAL) >= 4000000;

SELECT DEPTNO, AVG(SAL), MIN(SAL), MAX(SAL) FROM EMP

GROUP BY DEPTNO HAVING EVEN(SAL) >= 4000000;

SELECT DEPTNO, AVG(SAL), MIN(SAL), MAX(SAL) FROM EMP

WHERE AVG(SAL) >= 4000000 GROUP BY DEPTNO;

   

   

생성 조건

쿼리

사원테이블에서

FROM EMP

평균 급여(SAL)가 400만원 이상

AVG(SAL) >= 4000000

평균 급여(SAL)가 400만원 이상인 부서

GROUP BY DEPTNO HAVING AVG(SAL) >= 4000000;

부서의 부서번호(deptno)와, 평균급여, 최소급여액, 최대급여액

SELECT DEPTNO, AVG(SAL), MIN(SAL), MAX(SAL)

   

   

3

아래의 조건에 맞는 테이블을 설계하고 데이터를 조작하여 테스트하는 쿼리문을 작성하시오

   

인사관리에 필요한 테이블(employees)을 생성하기

컬럼

조건

사원번호

empno

PK

사원명

empname

Not Null

성별

gender

m 또는 f의 값만 허용

부서코드

deptno

부서테이블(departments)과 관계 설정

메일주소

email

중복불허

휴대폰번호

phone

중복불허

학력

school

코드(학력테이블)

주소

address

-

   

   

인사기본테이블(employees) 구성된 테이블 생성

CREATE TABLE edu_level(

empno NUMBER(4) PRIMARYKEY,

empname VARCHAR2(10) NOT NULL,

gender VARCHAR2(1) CONSTRAINT emp_gender_CK CHECK( gender IN('M', 'F') ),

gender VARCHAR2(10) constraint check_gender check( gender IN('남성','여성') ),

deptno VARCHAR2(10) NOT NULL FOREIGN KEY REFERENCES departments(deptno),

email VARCHAR2(10) UNIQUE,

phone NUMBER(4) UNIQUE,

school VARCHAR2(10),

address VARCHAR2(10)

);

   

   

부서테이블(departments) 생성하기

컬럼

조건

부서코드

(deptno)

PK

부서명

(deptname)

not Null

지역

(region)

-

   

CREATE TABLE departments(

deptno VARCHAR2(10) PRIMARYKEY,

deptname VARCHAR2(10) NOT NULL,

region VARCHAR2(10)

);

   

   

   

학력테이블(edulevel) 생성하기

컬럼

조건

학력코드

PK

학력명

not null

학력가중치

기본 1

   

CREATE TABLE edulevel(

eduno VARCHAR2(10) PRIMARYKEY,

eduname VARCHAR2(10) NOT NULL,

weight NUMBER(1) DEFAULT 1

);

   

   

   

4

작업용 데이터(임시) 생성 및 테스트

생성된 테이블에 작업용 임시 데이터 생성(최소 3 이상의 자료 생성)

   

   

자료 생성시 반드시 테스트

테스트 절차

쿼리 예

자료 생성전

select count(*) from edu_level;

자료 생성

insert into edu_level(컬럼1, 컬럼2, 컬럼3) values(데이터1, 데이터2, 데이터3);

자료 생성 후의 테스트가 이루어질 수 있도록 과정별 쿼리 작성및 확인후 커밋

select count(*) from edu_level;

select 컬럼1, 컬럼2, 컬럼3 from edu_level where 컬럼1=데이터1 and 컬럼2=데이터2 and 컬럼3 = 데이터3;

검증 테스트

자료 생성전과 자료생성후의 테스트후의 결과에 대한 비교를 통한 검증 테스트

결과 테스트

참고사항 : 위 작업은 쿼리문을 통한 가장 기초적인 생성과 테스트 작업이며 이를 프로그래밍의 단위테스트를 통하여 자동화할 수 있으며 종합적인 프로젝트 진행시에 이를 적용하여 세부적인 테스트가 이루어질 수 있도록 한다.

   

   

생성작업에 대한 테스트

  • "자료 생성전" 과 "자료생성후의 테스트" 의 결과에 대한 비교를 통한 검증 테스트
  • '검증 테스트"에 대한3) 기초적인 성능 분석
  • index의 이해

인덱스 생성 전 조회 결과에 대한 응답시간 : xxxxxx(단위:nano초)

인덱스 생성 후 조회 결과에 대한 응답시간 : xxxxxx(단위:nano초)

①과 ②의 응답시간에 대한 결과 비교

   

   

   

5

테이블 EMPLOYEE의 복합 컬럼(AGE, SALARY, NAME)에 인덱스가 정의되어 있다.

SELECT문 으로 이 인덱스를 이용하여 효율적으로 수행하는 쿼리문 작성하시오.

SELECT AGE, SALARY FROM EMPLOYEE WHERE AGE=30;