SQLite 데이터베이스, 쿼리문 관련

 

=================================

=================================

=================================

 

 

출처: http://blog.simplism.kr/?p=2329

 

 

사용환경

• 운영체제 : Windows 7 Ultimate K SP1
• SQLite3 버전 : SQLite 3.7.11

01. 시작하면서…

기존에 Java로 전산시스템을 유지보수하는 일만 하다가 신규로 윈도우즈 애플리케이션을 개발 및 유지보수를 할 일이 생겨서 이것저것 공부를 하고 있습니다. 사실 리눅스 시스템과의 호환성이나 요즘 추세인 멀티플랫폼을 생각해서 Qt4와 같은 멀티플랫폼을 지원하는 프레임워크를 사용하고 싶었으나 나 혼자만 사용할 프로젝트라면 그렇게 하겠지만 회사차원에서 개발 및 유지보수를 할 것이므로 회사에서 선택한 언어인 C#을 공부하고 있습니다.

개발언어나 프레임워크를 빠르게 익히는 방법은 역시 학습용으로 작은 프로젝트를 진행해보는 것입니다. 저도 C#과 WPF를 스터디하기 위해서 간단한 스터디용 프로젝트를 진행해보려고 하는데요… 최근에 관심을 갖게된 SQLite3를 애플리케이션의 설정파일이나 데이터파일을 대체하는 용도로 사용해보려고 합니다. 기존에 Oracle이나 MySQL과 같은 RDBMS를 사용해보신 분들이라면 쉽게 사용할 수 있을 것 같더군요.

02. SQLite3 주요특징

SQLite is a in-process library that implements a self-contained, serverless, zero-configuration,transactional SQL database engine

SQLite3 공식홈페이지에 About 메뉴를 클릭하면 간단한 특징이 적혀있습니다. 위의 문장을 해석하면…

SQLite는 독립적이고 서버가 불필요하며,설정이 필요없고 트랜잭션을 지원하는 SQL데이터베스 엔진으로 구현된 개발 중인 라이브러리입니다. (아.. 발번역)

위에서 독립적이라는 의미는 아마도 플랫폼 독립적이라는 것 같습니다. 요즘에 안드로이드와 같은 모바일 앱의 데이터베이스로 많이 활용되고 있는 것으로 알고 있습니다.

애플리케이션의 설정과 데이터들을 보관할 용도로는 딱인 것 같더군요. 단순한 것을 좋아하는 저에겐 다 맘에 드는 특징이었습니다. 가장 마음에 든 것은 serverless였지요. 기존에 사용하던 RDBMS와 같은 쿼리를 사용할 수 있지만 로컬서버던 원격서버던 RDBMS 서버를 구동하고 관리해야한다는 부담이 줄 수 있다는 장점이 있습니다.

일단, C#에서 설정파일 및 데이터파일로 사용하기 위해서 SQLite를 미리 알아보는 시간을 가져보려고 합니다.

03. SQLite3 설치

SQLite의 설치는 너무 간단해서 살짝 당황했습니다. 단순히 압축풀어서 원하는 위치에 풀어주면 끝입니다.(윈도우즈 시스템에서 setup.exe를 실행하는 것이 너무 익숙해서…)

공식홈페이지를 접속합니다.(아래의 스크린샷은 전부 2012.03.25일자 기준이므로 시간이 지나서 달라질 수 있음을 참고하시길 바라겠습니다.) 접속주소는 http://sqlite.org/입니다.

 

 메인화면을 접속하면 상단에 Dowload 메뉴를 보실 수 있습니다.

 

다운로드 화면을 들어가면 윈도우즈 시스템을 위해서 컴파일된 바이너리를 다운로드할 수 있습니다.

 

용량도 정말 작습니다. 254KB… 압축을 풀면 더 가관입니다.(실행파일 딸랑 하나…)

 

압축을 푼 디렉토리의 이름을 접근하기 편하게 이름을 바꿉니다.

 

디렉토리의 이름을 sqlite3로 변경합니다.

 

여기에서 뭔가 잘 못된줄 알았습니다. 너무 간단해서… 이게 설치의 마지막입니다. 사용할 준비가 완료된 것이지요. 다운받은 exe파일은 SQLite를 쉘에서 사용할 수 있도록 해주는 커맨드라인 쉘 인터페이스입니다.

04. SQLite3 실행하기

윈도우즈 상에서 SQLite3를 사용해보기 위해서는 cmd.exe를 사용합니다. Windows키 + R을 눌러서 cmd.exe를 실행합니다.

 

이제 cmd.exe를 실행해서 C:sqlite3로 이동합니다.

 

이제는 아래의 명령어를 실행해서 SQLite3를 실행합니다.

1. c:sqlite3> sqlite3 test.db

test.db라는 SQLite3 데이터베이스 파일명으로 지정하여 실행합니다.(해당 디렉토리를 열어보면 아직 test.db 파일은 생성되진 않았습니다.)

이제부터 입력되는 명령어나 쿼리는 SQLite용 명령어나 쿼리뿐입니다.

 

SQLite의 경우에는 여러 개의 database 파일을 한꺼번에 사용할 수 있습니다. .database 명령어는 그렇게 연결된 데이터베이스파일의 목록을 보여주는 명령어입니다.

테이블 생성

간단하게 테이블을 하나 생성해보겠습니다.(대소문자는 가리지 않으므로 편하게 입력하셔도 됩니다. 저는 읽기 편하도록 테이블명과 컬럼명은 소문자로 처리하였습니다.)

 

기존에 오라클이나 MySQL과 같은 RDBMS를 사용해보신 분이라면 쉽게 이해할 수 있을 것입니다. 테이블이 추가되었으니 .table 명령어로 확인해봅니다.

1. sqlite> .table

SQLite에서 쿼리를 제외한 나머지 명령어는 .(dot)로 시작합니다. 명령어의 목록은 .help를 입력하면 명령어 목록과 설명을 확인할 수 있습니다.

 

t1 이라는 테이블명이 출력되는 것을 확인할 수 있습니다.

데이터 추가(Insert)

이제 테이블에 데이터를 넣어보도록 하겠습니다.

 

테이블 생성과 마찬가지로 RDBMS를 기존에 사용해보신 분이라면 쉽게 이해하실 수 있습니다.  위에서 DATETIME 명령어는 오라클의 sysdate와 유사한 것입니다. 이제는 SELECT문을 이용해서 추가한 데이터를 조회해보도록 하겠습니다.

 

테이블 생성(create table), 데이터 추가(insert)와 마찬가지로 쿼리는 기존의 RDBMS와 거의 동일합니다. SELECT 문의 결과가 조회됩니다만… ‘|’ 문자열로 분리된 데이터는 조금 보기가 어렵습니다. 아래의 명령어를 이용해서 보이 좋게 바꿔봅니다.

 

1. sqlite> .header on
2. sqlite> .mode column

위의 명령어를 이용하면 기존의 오라클 클라이언트의 SQL*Plus와 유사하게 조회되는 것을 확인할 수 있습니다.

참고) INTEGER형의 Primary Key 컬럼

정확한 내용은 모르겠지만… 아마 Integer형으로 Primary Key로 지정하면 Auto-Increment 특징을 가지는 것 같습니다. 위와 유사한 방식으로 t1 테이블에 데이터를 2 row 추가해봤는데… 아래와 같습니다.

 

 05. 마치면서…

간단하게 사용해보니… 딱 제 취향입니다. 공식문서 중에 대용량 사이트의 DB로도 사용이 가능하다고 하는데… 그건 약간 회의적입니다만 애플리케이션의 설정파일을 대체하거나 데이터파일을 대체하는 것은 가능할 것 같기도 합니다.

앞으로는 C#에서 SQLite를 사용하는 법을 찾아서 직접 사용해보려고 합니다.

06. 참고문서

1. http://www.sqlite.org/about.html
2. http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html

 

=================================

=================================

=================================

 

 

출처: http://www.tutorialspoint.com/sqlite/sqlite_create_table.htm

 

he SQLite CREATE TABLE statement is used to create a new table in any of the given database. Creating a basic table involves naming the table and defining its columns and each column's data type.

Syntax:

Basic syntax of CREATE TABLE statement is as follows:

CREATE TABLE database_name.table_name(    column1 datatype  PRIMARY KEY(one or more columns),    column2 datatype,    column3 datatype,    .....    columnN datatype, );

CREATE TABLE is the keyword telling the database system to create a new table. The unique name or identifier for the table follows the CREATE TABLE statement. Optionally you can specify database_name along with table_name.

Example:

Following is an example which creates a COMPANY table with ID as primary key and NOT NULL are the constraints showing that these fields can not be NULL while creating records in this table:

sqlite> CREATE TABLE COMPANY(    ID INT PRIMARY KEY     NOT NULL,    NAME           TEXT    NOT NULL,    AGE            INT     NOT NULL,    ADDRESS        CHAR(50),    SALARY         REAL );

Let us create one more table, which we will use in our exercises in subsequent chapters:

sqlite> CREATE TABLE DEPARTMENT(    ID INT PRIMARY KEY      NOT NULL,    DEPT           CHAR(50) NOT NULL,    EMP_ID         INT      NOT NULL );

You can verify if your table has been created successfully using SQLIte command .tables command, which will be used to list down all the tables in an attached database.

sqlite>.tables COMPANY     DEPARTMENT

Here, you can see COMPANY table twice because its showing COMPANY table for main database and test.COMPANY table for 'test' alias created for your testDB.db. You can get complete information about a table using SQLite.schema command as follows:

sqlite>.schema COMPANY CREATE TABLE COMPANY(    ID INT PRIMARY KEY     NOT NULL,    NAME           TEXT    NOT NULL,    AGE            INT     NOT NULL,    ADDRESS        CHAR(50),    SALARY         REAL );

 

 

 

=================================

=================================

=================================

 

 

출처: http://www.tutorialspoint.com/sqlite/sqlite_insert_query.htm

 

The SQLite INSERT INTO Statement is used to add new rows of data into a table in the database.

Syntax:

There are two basic syntaxes of INSERT INTO statement as follows:

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)]   VALUES (value1, value2, value3,...valueN);

Here, column1, column2,...columnN are the names of the columns in the table into which you want to insert data.

You may not need to specify the column(s) name in the SQLite query if you are adding values for all the columns of the table. But make sure the order of the values is in the same order as the columns in the table. The SQLite INSERT INTO syntax would be as follows:

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

Example:

Consider you already have created COMPANY table in your testDB.db as follows:

sqlite> CREATE TABLE COMPANY(    ID INT PRIMARY KEY     NOT NULL,    NAME           TEXT    NOT NULL,    AGE            INT     NOT NULL,    ADDRESS        CHAR(50),    SALARY         REAL );

Now, following statements would create six records in COMPANY table:

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 );  INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Allen', 25, 'Texas', 15000.00 );  INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );  INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );  INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, 'David', 27, 'Texas', 85000.00 );  INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );

You can create a record in COMPANY table using second syntax as follows:

INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );

All the above statements would create following records in COMPANY table. Next chapter will teach you how to display all these records from a table.

ID          NAME        AGE         ADDRESS     SALARY ----------  ----------  ----------  ----------  ---------- 1           Paul        32          California  20000.0 2           Allen       25          Texas       15000.0 3           Teddy       23          Norway      20000.0 4           Mark        25          Rich-Mond   65000.0 5           David       27          Texas       85000.0 6           Kim         22          South-Hall  45000.0 7           James       24          Houston     10000.0

Populate one table using another table:

You can populate data into a table through select statement over another table provided another table has a set of fields, which are required to populate first table. Here is the syntax:

INSERT INTO first_table_name [(column1, column2, ... columnN)]     SELECT column1, column2, ...columnN     FROM second_table_name    [WHERE condition];

For now, you can skip above statement, first let's learn SELECT and WHERE clauses which will be covered in subsequent chapters.

 

 

=================================

=================================

=================================

 

 

 

반응형

 

728x90

 

 

 

출처: http://www.java-school.net/jdbc/SQL-SELECT-Statement

 

 

SQL 연습 - SELECT 문

 

SQL도 역시 표준이 존재한다.
여기서는 오라클을 사용하지만 표준 SQL 위주로 실습한다.
SELECT 문장은 순서에 유의해서 작성해야 한다.
"SELECT column1,column2,.. FROM table_name" 까지는 필수적으로 작성해야 한다.

SELECT 컬럼,컬럼... FROM 테이블명  WHERE 조건  GROUP BY 구문  UNION/UNION ALL/INTERSECT/MINUS 구문  ORDER BY 구문

SQL*PLUS로 scott계정에 접속한 후 다음에 나오는 모든 SQL문을 실행한다.

사원 테이블의 모든 레코드를 조회하시오.

SELECT *  FROM EMP

SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO  FROM EMP

위 두 SQL문은 같은 레코드를 가져온다.
select 다음에 * 사용하면 모든 컬럼을 순서대로 를 의미한다.

사원명과 입사일을 조회하시오.

SELECT ENAME,HIREDATE FROM EMP;

사원번호와 이름을 조회하시오.

SELECT DEPTNO,ENAME  FROM EMP

컬럼을 선택하여 조회할 수 있고 컬럼의 순서를 바꾸어 조회할 수 있다.

사원테이블에 있는 직책의 목록을 조회하시오.

SELECT DISTINCT JOB FROM EMP

DISTINCT는 검색 결과에서 중복된 레코드는 한 번만 보여주어야 할 때 사용한다.

총 사원수를 구하시오.

SELECT COUNT(EMPNO) FROM EMP

COUNT는 그룹함수의 하나로 검색된 레코드의 수를 반환한다.
COUNT(컬럼명)은 NULL이 아닌 레코드의 수를,COUNT(*)은 NULL을 포함한 레코드의 수를 반환한다.

WHERE 조건

부서번호가 10인 사원을 조회하시오.

SELECT *  FROM EMP WHERE DEPTNO = 10;

월급여가 2500이상 되는 사원을 조회하시오.

SELECT * FROM EMP WHERE SAL >= 2500;

where 조건이 추가되었다.
이제 where 다음에 나오는 조건에 맞는 레코드만 선별해서 가져오게 된다.
위 SQL문은 where 조건에 =,>,>=,<=,< 비교 연산자를 사용한 예이다.

이름이 'KING'인 사원을 조회하시오.

SELECT * FROM EMP WHERE ENAME = 'KING';

SQL문장은 대소문자를 가리지 않는다고 했다.
하지만 컬럼에 들어가는 데이터는 당연히 대소문자를 가린다.
KING라고 저장되어 있는데 king로는 검색되지 않는다.

사원들 중 이름이 S로 시작하는 사원의 사원번호와 이름을 조회하시오.

SELECT EMPNO,ENAME FROM EMP WHERE ENAME LIKE 'S%';

사원 이름에 T가 포함된 사원의 사원번호와 이름을 조회하시오.

SELECT EMPNO,ENAME FROM EMP WHERE ENAME LIKE '%T%';

LIKE 는 % 와 _문자와 함께 검색시 사용된다.

SQL*PLUS 사용법

명령 버퍼에 있는 SQL문을 편집하는 방법을 소개한다.
SQL문을 잘못 입력했을 때 유용하다.

ed

SQL*PLUS 에서 ed 명령어를 실행하면 버퍼에 있는 내용을 시스템의 디폴트 편집기가 실행되면서 보여준다.
윈도우의 경우 메모장이 실행되면서 버퍼에 저장된 SQL문를 보여주게 된다.
메모장에 있는 내용을 수정하고 닫은 다음에 / 명령으로 버퍼의 SQL문을 실행할 수 있다.

/

커미션이 300, 500, 1400 인 사원의 사번,이름,커미션을 조회하시오.

SELECT EMPNO,ENAME,COMM FROM EMP WHERE COMM = 300 OR COMM = 500 OR COMM = 1400;

SELECT EMPNO,ENAME,COMM FROM EMP WHERE COMM IN (300,500,1400);

둘 다 같은 결과를 보여주는 SQL문이다.
첫번째 SQL문은 논리 연산자 OR 를 사용했고 두번째는 IN 을 사용했다.

월급여가 1200 에서 3500 사이의 사원의 사번,이름,월급여를 조회하시오.

SELECT EMPNO,ENAME,SAL FROM EMP WHERE SAL BETWEEN 1200 AND 3500;

BETWEEN ~ AND ~ 사용법이다.
SAL BETWEEN 1200 AND 3500 은 수학적으로 1200 <= SAL <= 3500 와 같다.

직급이 매니저이고 부서번호가 30번인 사원의 이름,사번,직급,부서번호를 조회하시오.

SELECT ENAME,EMPNO,JOB,DEPTNO FROM EMP WHERE DEPTNO = 30 AND JOB = 'MANAGER';

부서번호가 30인 아닌 사원의 사번,이름,부서번호를 조회하라.

SELECT EMPNO,ENAME,DEPTNO FROM EMP WHERE NOT DEPTNO = 30;

커미션이 300, 500, 1400 이 모두 아닌 사원의 사번,이름,커미션을 조회하라.

SELECT EMPNO,ENAME,COMM FROM EMP WHERE COMM NOT IN (300,500,1400);

이름에 S가 포함되지 않는 사원의 사번,이름을 조회하라.

SELECT EMPNO,ENAME FROM EMP WHERE ENAME NOT LIKE '%S%';

급여가 1200보다 미만이거나 3700 초과하는 사원의 사번,이름,월급여를 조회하라.

SELECT EMPNO,ENAME,SAL FROM EMP WHERE SAL NOT BETWEEN 1200 AND 3700;

AND, OR, NOT 사용법이다.

직속상사가 NULL 인 사원의 이름과 직급을 조회하라.

SELECT ENAME,JOB FROM EMP WHERE MGR IS NULL;

컬럼이 NULL 인지 아닌지를 알기 위한 IS NULL, IS NOT NULL 의 사용법이다.

GROUP BY 구문

SELECT DEPTNO,AVG(SAL) FROM EMP

위 SQL문의 의도는 DEPTNO별로 월급여 평균값을 구하는 것일 것이다.
이때 그룹화의 기준이 되는 컬럼은 DEPTNO 이다.
그룹함수와 그룹화의 기준이 되는 컬럼이 함께 쓰일 때는 이 컬럼을 GROUP BY 로 명시해 주어야 에러가 나지 않는다.

부서별 평균월급여를 구하는 쿼리

SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO;

부서별 전체 사원수와 커미션을 받는 사원들의 수를 구하는 쿼리

SELECT DEPTNO,COUNT(*),COUNT(COMM) FROM EMP GROUP BY DEPTNO;

부서별 최대 급여와 최소 급여를 구하는 쿼리

SELECT DEPTNO,MAX(SAL),MIN(SAL) FROM EMP GROUP BY DEPTNO;

HAVING 은 GROUP BY 절에서 생성된 결과 값 중 원하는 조건에 부합하는 자료 추출할 때 사용한다.

부서별로 월급여 평균을 구하되 나온 결과값이 2000 이상인 것만 구하는 쿼리

SELECT DEPTNO,AVG(SAL) FROM EMP WHERE AVG(SAL) >= 2000; GROUP BY DEPTNO;

GROUP BY 구문을 사용하면서 이 결과에 조건을 줄 때 WHERE 조건문을 사용할 수 없다.
따라서 위 쿼리는 에러를 발생한다.
GROUP BY 구문을 사용하면서 조건을 주기 위해서는 대신 HAVING 구문을 사용한다.
HAVING 구문 에서는 그룹화의 기준이 되는 컬럼과 그룹함수만이 사용 할 수 있다는 점을 주의해야 한다.
위의 쿼리문에서는 그룹화의 기준이 되는 컬럼이 DEPTNO 이므로 DEPTNO 는 HAVING 구문에 쓸 수 있다.

SELECT DEPTNO,AVG(SAL) FROM EMP GROUP BY DEPTNO HAVING AVG(SAL) >= 2000;

월급여가 1000 이상인 사원만을 대상으로 부서별로 월급여 평균을 구하라. 단, 평균값이 2000 이상인 레코드만 구하라.

SELECT DEPTNO,AVG(SAL) FROM EMP WHERE SAL >= 1000 GROUP BY DEPTNO HAVING AVG(SAL) >= 2000;

WHERE 절은 테이블에서 데이터를 가져올 때 그 테이블에서 특정 조건에 부합하는 레코드만을 가져올 때 사용하는 절이고, HAVING 절은 GROUP BY 구문 사용시 나온 레코드 중에서 원하는 조건에 맞는 레코드만을 가져올 때 사용한다.

ORDER BY 구문

SELECT 문장을 사용하여 레코드를 검색할 때 임의의 컬럼을 기준으로 정렬을 해야 할 필요가 발생한다.
이런 경우 사용하는 구문이 ORDER BY 이다.

사용형식은 아래와 같다.

ORDER BY 정렬의 기준이 되는 컬럼 ASC 또는 DESC;

ASC : 오름차순을 의미(생략가능)
DESC : 내림차순을 의미

급여가 높은 순으로 조회하되 급여가 같을 경우 이름의 철자가 빠른 사원순으로 사번,이름,월급여를 조회하시오.

SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL DESC,ENAME ASC;

조인

조인은 2개 이상의 테이블에서 데이터를 조회할 때 사용한다.
조인조건은 테이블 N개를 조인할 때 N-1 개의 조인 조건을 주어야 한다.

사용형식
SELECT 테이블1.컬럼,테이블2.컬럼,....
FROM 테이블1,테이블2,...

카테시안 곱

SELECT EMP.ENAME,DEPT.DNAME FROM EMP,DEPT

SQL> select ename,dname   2  from emp,dept;  ENAME                DNAME -------------------- ---------------------------- SMITH                ACCOUNTING ALLEN                ACCOUNTING WARD                 ACCOUNTING JONES                ACCOUNTING MARTIN               ACCOUNTING BLAKE                ACCOUNTING CLARK                ACCOUNTING KING                 ACCOUNTING TURNER               ACCOUNTING JAMES                ACCOUNTING FORD                 ACCOUNTING MILLER               ACCOUNTING SMITH                RESEARCH ALLEN                RESEARCH WARD                 RESEARCH JONES                RESEARCH MARTIN               RESEARCH BLAKE                RESEARCH CLARK                RESEARCH KING                 RESEARCH TURNER               RESEARCH JAMES                RESEARCH FORD                 RESEARCH MILLER               RESEARCH SMITH                SALES ALLEN                SALES WARD                 SALES JONES                SALES MARTIN               SALES BLAKE                SALES CLARK                SALES KING                 SALES TURNER               SALES JAMES                SALES FORD                 SALES MILLER               SALES SMITH                OPERATIONS ALLEN                OPERATIONS WARD                 OPERATIONS JONES                OPERATIONS MARTIN               OPERATIONS BLAKE                OPERATIONS CLARK                OPERATIONS KING                 OPERATIONS TURNER               OPERATIONS JAMES                OPERATIONS FORD                 OPERATIONS MILLER               OPERATIONS  48 개의 행이 선택되었습니다.  SQL> 

조인의 기본이 되는 위의 WHERE 조건이 없는 단순조인 결과를 보고 스스로 파악해야 한다.
앞으로 나오는 조인예제는 위와 같은 단순 조인을 머리속에 그리면서 실습해야 한다.
총 56개의 행으로 EMP 테이블에 존재하는 14개의 레코드와 DEPT 테이블에 존재하는 4개의 레코드의 곱으로 생성된 것이다.
조회 대상이 되는 각 테이블의 컬럼이 명백히 어느 테이블의 컬럼인지가 확실하다면 EMP.ENAME 을 ENAME 처럼 테이블명을 생략 할 수 있다.

사원명과 부서명을 구하는 쿼리

SELECT ENAME,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;

SELECT E.ENAME,D.DNAME FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO;

테이블에 별칭을 사용할 수 있다.
이로써 복잡한 조인문이 간단해 질 수 있다.

이름,월급여,월급여등급을 조회

SELECT E.ENAME,E.SAL,S.GRADE FROM EMP E,SALGRADE S WHERE E.SAL >= S.LOSAL AND E.SAL <= S.HISAL;

WHERE 조건에 조인조건을 = 이외의 비교 연산자를 사용한 조인문이다.
이 SQL문을 BETWEEN ~ AND 문으로 변경하면 아래와 같다.

SELECT E.ENAME,E.SAL,S.GRADE FROM EMP E,SALGRADE S WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;

이름,부서명,월급여등급을 조회

SELECT E.ENAME,D.DNAME,S.GRADE FROM EMP E,DEPT D,SALGRADE S WHERE E.DEPTNO = D.DEPTNO AND E.SAL BETWEEN S.LOSAL AND S.HISAL;

이름,직속상사이름을 조회

SELECT E.ENAME,M.ENAME FROM EMP E,EMP M WHERE E.MGR = M.EMPNO;

위 결과에 회장에 대한 레코드가 빠졌다.
회장은 직속상사가 없으므로 MGR 컬럼이 NULL 이고 사원번호과 NULL인 사원은 없으므로 조인조건에 만족하지 않아서 빠진것이다.
그럼에도 불구하고 결과에 회장 레코드를 보여야 한다면 아래처럼 질의해야 한다.

SELECT E.ENAME,M.ENAME FROM EMP E,EMP M WHERE E.MGR = M.EMPNO(+);

위와 같은 조인을 외부조인이라 한다.
외부조인은 조인 조건에 만족하지 못하였더라도 해당 행을 나타내고 싶을 때 사용한다.
외부조인은 (+) 연산자를 사용하여 NULL 값이기에 배제된 행을 결과에 포함시킬 수 있다.
KING 의 MGR 필드 값은 NULL 인데 사원 번호가 NULL 인 사원이 존재하지 않아서 KING 이 결과에서 배제되었지만 이번 예제에서 WHERE 조건 오른쪽에 (+) 연산자를 붙이면 KING 에 대한 정보를 포함된다.

이름,부서명을 조회하라.단, 사원테이블에 부서번호가 40인 사원이 없지만 부서명 OPERATIONS 도 출력되도록 할 것.

SELECT E.ENAME,D.DNAME FROM EMP E,DEPT D WHERE E.DEPTNO(+) = D.DEPTNO;

이름,부서번호,부서이름을 출력

SELECT ENAME,E.DEPTNO,DNAME FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO;

부서번호가 30번인 사원들의 이름, 직급, 부서번호, 부서위치

SELECT ENAME,JOB,E.DEPTNO,LOC FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO AND D.DEPTNO = 30;

커미션을 받는 사원의 이름, 커미션, 부서이름,부서위치

SELECT ENAME,COMM,DNAME,LOC FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO  AND EMP.COMM IS NOT NULL AND EMP.COMM <> 0;

SELECT ENAME,COMM,DNAME,LOC FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO  AND EMP.COMM IS NOT NULL AND EMP.COMM != 0;

SELECT ENAME,COMM,DNAME,LOC FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO  AND EMP.COMM IS NOT NULL AND EMP.COMM NOT IN(0);

DALLAS 에서 근무하는 사원의 이름,직급,부서번호,부서명

SELECT E.ENAME,E.JOB,D.DEPTNO,D.DNAME FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO AND D.LOC = 'DALLAS';

이름에 A 가 들어가는 사원들의 이름,부서명

SELECT E.ENAME,D.DNAME FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO AND E.ENAME LIKE '%A%';

이름, 직급, 월급여, 월급여등급

SELECT E.ENAME,E.JOB,E.SAL,S.GRADE FROM EMP E,SALGRADE S WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;

ALLEN 과 같은 부서에 근무하는 사원의 이름, 부서번호

SELECT C.ENAME,C.DEPTNO FROM EMP E,EMP C WHERE E.EMPNO <> C.EMPNO AND E.DEPTNO = C.DEPTNO AND E.ENAME = 'ALLEN' ORDER BY C.ENAME;

서브쿼리

서브 쿼리는 SELECT문안에서 ()로 둘러싸인 SELECT 문을 말하며 쿼리문의 결과를 메인 쿼리로 전달하기 위해 사용된다.

사원명 'JONES'가 속한 부서명

SELECT DNAME  FROM DEPT WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = 'JONES');

부서번호를 알아내기 위한 쿼리가 서브 쿼리로 사용되고, 이 서브쿼리는 단 하나의 결과값을 얻기 때문에 단일 행 서브 쿼리라 한다.

10번 부서에서 근무하는 사원의 이름과 10번 부서의 부서명

SELECT E.ENAME,D.DNAME FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO AND D.DEPTNO = 10;

SELECT E.ENAME,D.DNAME FROM EMP E, ( 	SELECT DEPTNO,DNAME 	FROM DEPT 	WHERE DEPTNO = 10 ) D WHERE E.DEPTNO = D.DEPTNO;

평균 월급여보다 더 많은 월급여를 받은 사원의 사원번호,이름,월급여 조회

SELECT EMPNO,ENAME,SAL FROM EMP WHERE SAL > (SELECT AVG(SAL) 	      FROM EMP) ORDER BY SAL DESC;

부서번호가 10인 사원중에서 최대급여를 받는 사원과 동일한 급여를 받는 사원의 사원번호, 이름 조회

SELECT EMPNO,ENAME FROM EMP WHERE SAL = (SELECT MAX(SAL)  	     FROM EMP  	     WHERE DEPTNO = 10);

• 다음 : SQL 연습-DML
• 이전 : SQL 연습-SCOTT 계정

 

=================================

=================================

=================================