Basic SQL

기초적인 SQL에 대한 예제 정리이다.

JOIN 관련된 포스팅은 다음에 할 예정이다.

 

이 예제들과 JOIN에 대해서 제대로 이해하고 있다면 웬만한 쿼리는 다 짤 수 있는 것 같다.

물론 시간은 오래 걸리지만..

 

공부도 하는 겸, 자꾸 까먹는 미래의 나(시험 보기 직전의 나)를 위해 정리해놓음..

 

1. CREATE : 테이블 및 스키마 생성

ex) CREATE TABLE EMPLOYEE (

           Fname VARCHAR(15) NOT NULL,

           Minit CHAR,

           Lname VARCHAR(15) NOT NULL,

           Ssn CHAR(9) NOT NULL,

           Bdate DATE,

           Address VARCHAR(30),

           Sex CHAR, Salary DECIMAL(10, 2),

           Super_ssn CHAR(9),

           Dno INT NOT NULL,

           PRIMARY KEY (Ssn),

           FOREIGN KEY (Super_ssn) REFERENCES EMPLOYEE(Ssn),

           FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber)

) ;

 

# 여기서 VARCHAR와 CHAR의 차이점은 고정 길이인지 가변 길이인지의 차이이다.

 

ex) CREATE TABLE DEPARTMENT (

           Dname VARCHAR(15) NOT NULL,

           Dnumber INT NOT NULL,

           Mgr_ssn CHAR(9) NOT NULL,

           Mgr_start_date DATE,

           PRIMARY KEY (Dnumber),

           UNIQUE (Dname),     <----   대체키

           FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE (Ssn)

) ;

 

 

2. DROP : 테이블 및 스키마 삭제

 

ex) DROP TABLE DEPENDENT CASCADE;  <--- 삭제 시 관련된 (참조 무결성 제약 조건) 정보도 같이 삭제한다

(참조 무결성 제약 조건과 상관없이 DEPENDENT 테이블만 drop 하고 싶다면 RESTRICT 사용하면 됨) 

 

 

3. ALTER : 정보 변경 및 수정

 

ex) ALTER TABLE EMPLOYEE ADD Job VARCHAR(12);

    ALTER TABLE EMPLOYEE DROP Address CASCADE;

    ALTER TABLE DEPARTMENT ALTER Mgr_ssn DROP DEFAULT;    <--- default 값 없애기

    ALTER TABLE DEPARTMENT ALTER Mgr_ssn SET DEFAULT “333445555”;    <--- default 값 주기

    ALTER TABLE EMPLOYEE DROP CONSTRAINT EMPSUPERFK CASCADE;      <--- 제약조건 삭제

 

 

4. SELECT-FROM-WHERE : 일반적인 SQL문 Structure

SELECT : 어떠한 attribute를 출력할 것인지

FROM : 어떠한 TABLE에서 WHERE절의 조건들을 실행할 것인지

WHERE : 조건 수행

 

 

5. Renaming (Aliasing) : 별칭 제공

 

ex) SELECT E.Fname

     FROM EMPLOYEE AS E

 

ORACLE DB에서는 AS를 사용하면 안 된다!

 

 

6. ALL : 모든 값 제공

 

ex) SELECT ALL Salary

     FROM EMPLOYEE;

--> 즉 Salary column을 반환함

 

7. DISTINCT : 제공되는 값 중 중복된 값 제거

 

ex) SELECT DISTINCT Salary

     FROM EMPLOYEE;

 

 

8. UNION : Query의 결과값(테이블)을 합침

 

# Union 사용 시 선택되는 attr가 동일해야 한다. 

 

ex) (SELECT Pname

     FROM PROJECT

     WHERE pnumber = 1)

     UNION

     (SELECT Pname

     FROM PROJECT

     WHERE pnumber = 1)

---> pnumber=1인 프로젝트의 이름과 pnumber=2인 프로젝트 이름 한 컬럼으로 출력

 

9. IN & NOT IN : Set에 포함되어 있는/있지 않은 tuple만 제공

ex) SELECT Pname

     FROM PROJECT

     WHERE Pnumber IN (1, 2);

---> Pnumber = 1 이거나 2인 프로젝트의 이름만 출력

 

 

10. LIKE : Substring(부분 문자열) Pattern Mathcing, 조건에 맞는 문자열 찾기

 

# 이때 '%' 기호는 어떠한 문자열이 와도 된다는 의미이고, '_' 기호는 어떠한 문자가 와도 된다는 것이다.

 

ex) SELECT Fname, Lname

     FROM EMPLOYEE

     WHERE Address LIKE '% HOUSTON, TX%;'

--> HOUSTON, TX 앞, 뒤로 어떤 문장이 와도 상관없다는 뜻임

 

ex) SELECT Fname, Lname

     FROM EMPLOYEE

     WHERE Bdate LIKE '_ _ 5 _ _ _ _ _ _ _' ;

--> 세 번째 문자가 5인 모든 문자열 가능

 

11. BETWEEN AND : 사이의 값이라면 TRUE

 

ex) SELECT *

     FROM EMPLOYEE

     WHERE (Salary BETWEEN 30000 AND 40000) AND Dno =5;

 

 

12. ORDER BY : 정렬

 

# DESC = 내림차순, ASC = 오름차순, default값은 ASC, 여러 개 조건 지정 가능

 

ex) SELECT Dname, Lname, Fname, Pname

     FROM DEPARTMENT, EMPLOYEE, WORKS_ON, PROJECT

     WHERE Dnumber=Dno AND Ssn=Essn AND Pno=Pnumber

     ORDER BY Dname, Lname, Fname;

--> Dname이 작은 순, Lname이 작은 순, Fname이 작은 순으로 정렬

 

ex) ORDER BY Dname DESC, Lname ASC, Fname ASC

--> Dname은 큰 순, Lname은 작은 순, Fname도 작은 순으로 정렬

 

 

13. INSERT : Tuple 삽입

 

# 값을 정의해주지 않는다면 Default 값 또는 NULL 값으로 채워진다

# 쿼리문으로도 INSERT가 가능하다

 

ex) INSERT INTO EMPLOYEE VALUES

    (‘Richard’, ‘K’, ‘Marini’, ‘653298653’, ‘1962-12-30’,’98 Oak Forest, Katy,TX’, ’M’, 37000, ‘987654321’, 4);

ex) INSERT INTO EMPLOYEE (Fname, Lname, Dno, Ssn)

     VALUES (‘Richard’, ‘Marini’, 4, ‘653298653’);

 

ex) INSERT INTO WORKS_ON_INFO (Emp_name, Proj_name, Hours_per_week)

             SELECT E.Lname, P.Pname, W.Hours 

             FROM PROJECT P, WORKS_ON W, EMPLOYEE E

             WHERE P.Pnumber=W.Pno AND W.Essn=E.Ssn;

 

14. DELETE : Tuple 삭제

 

ex) DELETE FROM EMPLOYEE WHERE Ssn=’123456789’;

 

15. UPDATE : Attribute 값 update

 

ex) UPDATE PROJECT

     SET Plocation = ‘Bellaire’, Dnum = 5

     WHERE Pnumber = 10;

 

16. IS NULL : NULL 값인지 확인

 

ex) SELECT Fname, Lname

     FROM EMPLOYEE

     WHERE Super_ssn IS NULL;