Common SQL

Overview

SQL (Structured Query Language) is designed for the management of data in relational database management systems (RDBMS). In the case of Oracle, SQL commands that manipulate data are called Data Manipulation Language (DML). What follows are the essential SQL DML commands. For detailed SQL information you can go here.

SELECT

SELECT [DISTINCT] <select_list>
FROM <table_list>
[WHERE conditions]
[START WITH condition]
[GROUP BY group_by_list]
[HAVING search_conditions]
[ORDER BY order_list [ASC|DESC]];

SELECT * FROM emp;
SELECT count(*) FROM emp;
SELECT min(date_time) FROM orders;
SELECT max(date_time) FROM orders;

SELECT * FROM emp WHERE state='FL';
SELECT * FROM emp WHERE state='FL' ORDER BY lastname ASC;

SELECT * FROM emp WHERE pay_grade LIKE '%G1%';

SELECT * FROM emp WHERE state IN('FL', 'NY', 'MA');

SELECT count(*) "Deleted" FROM documents WHERE deleted IS NOT NULL;

SELECT date_time FROM orders
WHERE  date_time >= to_date(20070601, 'yyyymmdd');

SELECT date_time FROM orders 
WHERE date_time >= to_date(20060101, 'yyyymmdd') 
AND   date_time <= to_date(20070101, 'yyyymmdd');

SELECT *
FROM scott.documentpages
WHERE documentid 
IN (
    SELECT documentid
    FROM scott.documents
    WHERE creationdate >= to_date('01-01-2001.0000.00','MM-DD-YYYY.HH24MI.SS')
    AND   creationdate <= to_date('12-31-2001.2359.59','MM-DD-YYYY.HH24MI.SS')
    );

SELECT *
FROM scott.documents
LEFT JOIN scott.documentpages ON documents.documentid = documentpages.documentid
WHERE documents.creationdate
BETWEEN to_date('01/01/2001','mm/dd/yyyy') 
AND     to_date('01/01/2010','mm/dd/yyyy');

INSERT

INSERT INTO [schema].<table_name>[@dblink] [table_alias]
(column, column,...) What_to_Insert;

INSERT INTO emp VALUES('Smith','John');

INSERT INTO emp(lastname, firstname) VALUES('Smith','John');

INSERT INTO emp_new(lastname) (SELECT lastname FROM emp_old);

INSERT INTO table2 (SELECT * FROM table1);

INSERT INTO table2 (SELECT * FROM table1
WHERE date_time >= to_date(200512010000, 'YYYYMMDDHH24MISS')
AND   date_time <  to_date(200801010000, 'YYYYMMDDHH24MISS') );

INSERT INTO x15.tracking 
(TRACKING_ID, SCHEMA, TIMESTAMP, TYPE, DETAIL, SESSIONS, SQL)
VALUES ( 
   (SELECT max(tracking_id)+1 FROM x15.tracking),
    'X15', SYSDATE, 'DELETE', 'Test Event','',''
);

Insert (AKA Copy\Duplicate\Restore) Data from Another Database

Modify this simple model for your own needs.

 -- Source Database\Table
 1. QC 
    SELECT count(*) FROM SCOTT.TABLEX;
      1045
 2. Create Spool File for INSERT Commands
    spool C:\app\scripts\sql\spool\SCOTT.TABLEX_INSERT_CMDS.spool
 3. Run SQL to create INSERT commands.
    SELECT 'INSERT INTO SCOTT.TABLEX 
    VALUES(''' || POSTDATE || ''',''' || MEMBERCOUNT || ''');' 
    FROM SCOTT.TABLEX;
    Place above command on one line.
 4. spool off

 -- Spool File Snippet
 INSERT INTO SCOTT.TABLEX VALUES('09-SEP-20','482113');                                                                                        
 INSERT INTO SCOTT.TABLEX VALUES('10-SEP-20','482284');                                                                                        
 INSERT INTO SCOTT.TABLEX VALUES('11-SEP-20','482464');
 ...

 -- Destination Database\Table
 1. Truncate destination table.
    TRUNCATE TABLE SCOTT.TABLEX;
 2. Run INSERT commands from spool file.
 3. COMMIT;
 4. QC
    SELECT count(*) FROM SCOTT.TABLEX;
      1045

UPDATE

UPDATE [schema].<table_name>[@dblink] [alias]
SET col_expr(s)
[WHERE condition];

UPDATE emp
SET
   lastname  = 'Smith',
   firstname = 'John',
   dob       = to_date('10-31-1963',          'MM-DD-YYYY'),
   created   = to_date('09-16-2010 00:00:00', 'MM-DD-YYYY HH24:MI:SS')
WHERE id=1001; 

COMMIT;

DELETE

DELETE [FROM] [schema].<table_name>[@dblink] [alias]
WHERE (condition);

DELETE FROM emp WHERE emp_id=1075;

DELETE FROM emp
WHERE date_time >= to_date(20060101, 'YYYYMMDD')
AND   date_time <= to_date(20070101, 'YYYYMMDD');
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
PROMPT Deleting child records first (DocumentPages)...
DELETE FROM &v_schema..documentpages WHERE documentid IN 
(SELECT documentid FROM &v_schema..documents WHERE deleted IS NOT NULL);
COMMIT;

PROMPT Deleting documents...
DELETE FROM &v_schema..documents ...

<- SpecialTopics