| Basic Select Statements |
| Select All Columns and All Records in a Single Table or View |
SELECT *
FROM ; |
SELECT *
FROM all_tables; |
| Select Named Columns |
SELECT
FROM ; |
SELECT table_name, tablespace_name, num_rows
FROM all_tables; |
Create Table As (CTAS)
Note: Redo only created when in ARCHIVE LOG mode |
CREATE TABLE AS
SELECT
FROM ; |
CREATE TABLE t AS
SELECT *
FROM all_tables; |
| SELECTs can go anywhere |
SELECT DECODE((SELECT 'x' FROM DUAL), (SELECT 'x' FROM DUAL), (SELECT 'y' FROM dual)) AS RESULT
FROM (SELECT 'm' FROM dual)
WHERE (SELECT 1 FROM dual) = (SELECT 1 FROM dual)
AND (SELECT 2 FROM dual) BETWEEN (SELECT 1 FROM dual) AND (SELECT 3 FROM dual)
AND NVL((SELECT NULL FROM dual ), (SELECT 'z' FROM dual)) = (SELECT 'z' FROM dual)
ORDER BY (SELECT 1 FROM dual); |
| |
| Select Statement With SAMPLE Clause |
Sample Clause Returning 1% Of Records |
SELECT *
FROM
SAMPLE (1); |
CREATE TABLE t AS
SELECT object_name
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W';
SELECT COUNT(*)
FROM t;
SELECT COUNT(*) * 0.1
FROM t;
SELECT *
FROM t
SAMPLE(1);
SELECT *
FROM t
SAMPLE(1);
SELECT *
FROM t
SAMPLE(1); |
| |
| Select Statement With WHERE Clause |
Sample Clause Returning 35% Of Records After Filtering With A WHERE Clause |
SELECT *
FROM
SAMPLE (3.5)
WHERE .... |
SELECT COUNT(*)
FROM t
WHERE object_name LIKE '%J%';
SELECT COUNT(*) * 0.35
FROM t
WHERE object_name LIKE '%J%';
SELECT *
FROM t
SAMPLE(35)
WHERE object_name LIKE '%J%';
SELECT *
FROM t
SAMPLE(35)
WHERE object_name LIKE '%J%';
SELECT *
FROM t
SAMPLE(35)
WHERE object_name LIKE '%J%'; |
| |
| Select Statement With GROUP BY Clause |
Select with Group By Clause |
SELECT ,
FROM
GROUP BY ; |
SELECT object_type, COUNT(*)
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W'
GROUP BY object_type; |
| |
| Select Statement With HAVING Clause |
Select With Having Clause |
SELECT ,
FROM
GROUP BY
HAVING ; |
SELECT object_type, COUNT(*)
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W'
GROUP BY object_type
HAVING COUNT(*) < 6;
SELECT object_type, COUNT(*)
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W'
GROUP BY object_type
HAVING COUNT(*) > 5; |
| |
| Scalar Select |
| Select In Select Clause |
SELECT (
SELECT FROM
FROM ; |
| SELECT (SELECT 1 FROM dual) FROM dual; |
| |
| Select Unique Values |
| Distinct |
SELECT DISTINCT
FROM ; |
SELECT DISTINCT object_type
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W'; |
| Unique |
SELECT UNIQUE
FROM ; |
SELECT UNIQUE object_type
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W'; |
| |
| Select Statement Using Functions |
Date Function Example |
SELECT ))
FROM ; |
desc all_objects
SELECT object_name, TO_DATE(timestamp, 'YYYY-MM-DD:HH24:MI:SS')
FROM all_objects
WHERE rownum < 11; |
Numeric Function Example |
SELECT ))
FROM ; |
desc user_extents
SELECT SUM(bytes)/1024/1024 USED_MB
FROM user_extents;
SELECT segment_type, SUM(bytes)/1024/1024 USED_MB
FROM user_extents
GROUP BY segment_type; |
String Function Example |
SELECT ))
FROM ; |
desc all_objects
SELECT object_name, LOWER(object_name) LOWER_ONAME
FROM all_objects
WHERE rownum < 11; |
| |
| Select For Update |
Lock Record(s) |
SELECT FROM
FOR UPDATE; |
CREATE TABLE parents (
pid NUMBER(10),
cash NUMBER(10,2));
CREATE TABLE children
(cid NUMBER(10),
fid NUMBER(10),
fin_level VARCHAR2(35));
DECLARE
CURSOR x_cur IS
SELECT pid
FROM parents;
x_rec x_cur%ROWTYPE;
x NUMBER(10,2) := 18000.64;
y NUMBER(10,2) := 100;
BEGIN
DELETE FROM parents;
DELETE FROM children;
FOR i IN 1..25
LOOP
INSERT INTO parents
VALUES (y, x);
x := x+1235.31;
y := y-1;
END LOOP;
y := 0;
OPEN x_cur;
LOOP
FETCH x_cur INTO x_rec;
EXIT WHEN x_cur%NOTFOUND;
y := y+1;
INSERT INTO children (cid, fid)
VALUES (y, x_rec.pid);
y := y+1;
INSERT INTO children (cid, fid)
VALUES (y, x_rec.pid);
END LOOP;
CLOSE x_cur;
COMMIT;
END;
/
CREATE OR REPLACE PROCEDURE cursor_loop3 IS
CURSOR x_cur IS
SELECT pid, cash
FROM parents
WHERE cash < 35000
FOR UPDATE;
BEGIN
FOR x_rec IN x_cur
LOOP
UPDATE parents
SET cash = FLOOR(cash)
WHERE CURRENT OF x_cur;
END LOOP;
COMMIT;
END cursor_loop3;
/ |
| FOR UPDATE with NOWAIT |
See Deadlocks Demo page |
| FOR UPDATE with WAIT |
See Deadlocks Demo page |
| FOR UPDATE with SKIP LOCKED |
See Deadlocks Demo page |
| |
| Partition Select |
Select From Named Partition |
SELECT DISTINCT
FROM PARTITION (); |
CREATE TABLE pt (
deptno NUMBER(10),
state VARCHAR2(2))
PARTITION BY LIST (state) (
PARTITION nw VALUES ('OR', 'WA'),
PARTITION sw VALUES ('AZ', 'CA', 'NM'));
INSERT INTO pt VALUES (1, 'WA');
INSERT INTO pt VALUES (1, 'OR');
INSERT INTO pt VALUES (1, 'CA');
SELECT COUNT(*) FROM pt;
SELECT COUNT(*) FROM pt PARTITION(nw);
SELECT COUNT(*) FROM pt PARTITION(sw); |
| |
| PL/SQL Select Into |
Selecting In PL/SQL Objects |
SELECT
INTO
FROM
WHERE |
CREATE TABLE t (
testcol NUMBER(3));
CREATE SEQUENCE seq;
SELECT seq.NEXTVAL FROM dual;
/
/
INSERT INTO t
(testcol)
VALUES
(seq.NEXTVAL);
/
/
SELECT * FROM t;
BEGIN
SELECT seq.NEXTVAL FROM dual;
END;
/
set serveroutput on
DECLARE
x INTEGER;
BEGIN
SELECT seq.NEXTVAL
INTO x
FROM dual;
dbms_output.put_line(x);
END;
/
/
/ |
