쿼리문 연습

   

쿼리문 연습

   

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;