SQL문 모음

1. INSERT 문
   
   INSERT INTO DEPARTMENT VALUES(5, '연구', ''); INSERT INTO tmember (no, dept, name, sex) VALUES (201812345, '스마트미디어학과', '홍길동', '남');"

   1 2 3 4 5 6

   INSERT INTO DEPARTMENT VALUES(5, '연구', '');   INSERT INTO tmember (no, dept, name, sex) VALUES (201812345, '스마트미디어학과', '홍길동', '남');"

   
    
2. DELETE 문
   
   DELETE FROM DEPARTMENT WHERE DEPTNO = 4; DELETE FROM tmember WHERE dept='사회복지학과';

   1 2 3 4 5

   DELETE FROM DEPARTMENT WHERE DEPTNO = 4;   DELETE FROM tmember WHERE dept='사회복지학과';

   
    
3. UPDATE 문
   
   UPDATE EMPLOYEE SET DNO = 3, SALARY = SALARY * 1.05 WHERE EMPNO = 2106; UPDATE tmember SET dept='스마트미디어학과' WHERE no=201892016;

   1 2 3 4 5 6 7 8 9 10 11 12 13

   UPDATE EMPLOYEE SET DNO = 3, SALARY = SALARY * 1.05 WHERE EMPNO = 2106;     UPDATE tmember SET dept='스마트미디어학과' WHERE no=201892016;

   
    
4. SELECT 문
   
   SELECT * FROM DEPARTMENT; SELECT DEPTNO, DEPTNAME FROM DEPARTMENT; SELECT TITLE FROM EMPLOYEE; SELECT DISTINCT TITLE FROM EMPLOYEE; SELECT * FROM EMPLOYEE WHERE DNO = 2; SELECT * FROM EMPLOYEE WHERE DNO <> 2; SELECT * FROM EMPLOYEE WHERE EMPNAME LIKE '이%'; SELECT FLOOR FROM DEPARTMENT WHERE DEPTNAME='영업' OR DEPTNAME='개발'; SELECT * FROM EMPLOYEE WHERE SALARY BETWEEN 3000000 AND 4500000; SELECT * FROM EMPLOYEE WHERE SALARY >= 3000000 AND SALARY <= 4500000; SELECT * FROM EMPLOYEE WHERE DNO IN (1, 3); SELECT * FROM EMPLOYEE WHERE DNO NOT IN (1, 3); SELECT EMPNAME, SALARY, SALARY * 1.1 AS NEWSALARY FROM EMPLOYEE; SELECT * FROM EMPLOYEE WHERE MANAGER IS NULL; SELECT * FROM EMPLOYEE WHERE MANAGER IS NOT NULL; SELECT * FROM EMPLOYEE WHERE DNO = 2 ORDER BY SALARY; SELECT * FROM EMPLOYEE WHERE DNO = 2 ORDER BY SALARY DESC; SELECT * FROM EMPLOYEE WHERE DNO = 2 ORDER BY SALARY, NAME; SELECT AVG(SALARY) AS AVGSAL, MAX(SALARY) AS MAXSAL FROM EMPLOYEE; SELECT DNO, AVG(SALARY) AS AVGSAL, MAX(SALARY) AS MAXSAL FROM EMPLOYEE GROUP BY DNO; SELECT DNO, AVG(SALARY) AS AVGSAL, MAX(SALARY) AS MAXSAL FROM EMPLOYEE GROUP BY DNO HAVING AVG(SALARY) >= 2500000;

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

   SELECT * FROM DEPARTMENT; SELECT DEPTNO, DEPTNAME FROM DEPARTMENT;   SELECT TITLE FROM EMPLOYEE; SELECT DISTINCT TITLE FROM EMPLOYEE;   SELECT * FROM EMPLOYEE WHERE DNO = 2; SELECT * FROM EMPLOYEE WHERE DNO <> 2; SELECT * FROM EMPLOYEE WHERE EMPNAME LIKE '이%'; SELECT FLOOR FROM DEPARTMENT WHERE DEPTNAME='영업' OR DEPTNAME='개발'; SELECT * FROM EMPLOYEE WHERE SALARY BETWEEN 3000000 AND 4500000; SELECT * FROM EMPLOYEE WHERE SALARY >= 3000000 AND SALARY <= 4500000;   SELECT * FROM EMPLOYEE WHERE DNO IN (1, 3); SELECT * FROM EMPLOYEE WHERE DNO NOT IN (1, 3);   SELECT EMPNAME, SALARY, SALARY * 1.1 AS NEWSALARY FROM EMPLOYEE; SELECT * FROM EMPLOYEE WHERE MANAGER IS NULL; SELECT * FROM EMPLOYEE WHERE MANAGER IS NOT NULL;   SELECT * FROM EMPLOYEE WHERE DNO = 2 ORDER BY SALARY; SELECT * FROM EMPLOYEE WHERE DNO = 2 ORDER BY SALARY DESC; SELECT * FROM EMPLOYEE WHERE DNO = 2 ORDER BY SALARY, NAME;   SELECT AVG(SALARY) AS AVGSAL, MAX(SALARY) AS MAXSAL FROM EMPLOYEE;   SELECT DNO, AVG(SALARY) AS AVGSAL, MAX(SALARY) AS MAXSAL FROM EMPLOYEE GROUP BY DNO;   SELECT DNO, AVG(SALARY) AS AVGSAL, MAX(SALARY) AS MAXSAL FROM EMPLOYEE GROUP BY DNO HAVING AVG(SALARY) >= 2500000;

   
    
5. CREATE 문
   
   CREATE TABLE tmember ( no INTEGER NOT NULL, dept TEXT DEFAULT '스마트미디어학과', name TEXT NOT NULL, sex TEXT DEFAULT '여' CHECK(SEX in ('남','여')), hp TEXT UNIQUE, post INTEGER, PRIMARY KEY(no) ); CREATE TABLE tbook ( no INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, title TEXT, author TEXT, publ TEXT, yea INTEGER, isbn TEXT, symb TEXT ); CREATE TABLE tlend ( bno INTEGER, mno INTEGER, cdate TEXT DEFAULT CURRENT_TIMESTAMP, days INTEGER, PRIMARY KEY(cdate), FOREIGN KEY(bno) REFERENCES tbook(no) on update CASCADE on delete CASCADE, FOREIGN KEY(mno) REFERENCES tmember(no) on update CASCADE on delete set NULL );

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

   CREATE TABLE tmember ( no INTEGER NOT NULL, dept TEXT DEFAULT '스마트미디어학과', name TEXT NOT NULL, sex TEXT DEFAULT '여' CHECK(SEX in ('남','여')), hp TEXT UNIQUE, post INTEGER, PRIMARY KEY(no) );   CREATE TABLE tbook ( no INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, title TEXT, author TEXT, publ TEXT, yea INTEGER, isbn TEXT, symb TEXT );     CREATE TABLE tlend ( bno INTEGER, mno INTEGER, cdate TEXT DEFAULT CURRENT_TIMESTAMP, days INTEGER, PRIMARY KEY(cdate), FOREIGN KEY(bno) REFERENCES tbook(no) on update CASCADE on delete CASCADE, FOREIGN KEY(mno) REFERENCES tmember(no) on update CASCADE on delete set NULL );

   
    
6. DROP 문
   
   DROP TABLE employee;

   1	DROP TABLE employee;

   
    
7. JOIN 문
   
   SELECT tlend.mno as hakbun, tmember.name as name, tbook.title as title, tlend.cdate, tlend.days FROM tlend, tbook, tmember WHERE tlend.bno = tbook.no AND tlend.mno = tmember.no;

   1 2 3 4 5 6 7 8 9 10

   SELECT  tlend.mno as hakbun, tmember.name as name, tbook.title as title, tlend.cdate, tlend.days   FROM tlend, tbook, tmember WHERE tlend.bno = tbook.no AND tlend.mno = tmember.no;

   
    
8. 오라클 명령
   
   SELECT * FROM dba_users;  <-- DBA계정만 사용 가능 SELECT * FROM all_users;  <-- 모든 계정에서 사용 가능 SELECT * FROM tab; <-- 테이블 목록 보기 DESC DEPARTMENT; CREATE USER KIM IDENTIFIED BY bluesky DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp; grant connect, resource, create session, create view to KIM;

   1 2 3 4 5 6 7 8 9 10 11 12

   SELECT * FROM dba_users;  <-- DBA계정만 사용 가능 SELECT * FROM all_users;  <-- 모든 계정에서 사용 가능 SELECT * FROM tab; <-- 테이블 목록 보기   DESC DEPARTMENT;     CREATE USER KIM IDENTIFIED BY bluesky   DEFAULT TABLESPACE users   TEMPORARY TABLESPACE temp;   grant connect, resource, create session, create view to KIM;