Basic Select Statements
|
Select All Columns and All Records in a Single Table or View
|
SELECT *
FROM <table_name>;
|
SELECT *
FROM all_tables; |
Select Named Columns
|
SELECT <column_name, column_name, ..., <column_name>
FROM <table_name>;
|
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 <table_name> AS
SELECT <column_name, column_name, ..., <column_name>
FROM <table_name>;
|
CREATE TABLE t AS
SELECT *
FROM all_tables;
SELECT * FROM t; |
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 <table_name>
SAMPLE (percentage_of_rows);
|
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 <table_name>
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 <column_name>, <aggregating_operation>
FROM <table_name>
GROUP BY <column_name>; |
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 <column_name>, <aggregating_operation>
FROM <table_name>
GROUP BY <column_name>
HAVING <aggregating_op_result> <condition> <value>; |
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 <single_value
FROM <table_name>
FROM <table_name>; |
SELECT (SELECT 1 FROM DUAL) FROM DUAL; |
|
Select Unique Values |
Distinct |
SELECT DISTINCT <column_name_list>
FROM <table_name>; |
SELECT DISTINCT object_type
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W'; |
Unique |
SELECT UNIQUE <column_name_list>
FROM <table_name>; |
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
<date_function(<column_name>))
FROM <table_name>; |
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 <numeric_function(<column_name>))
FROM <table_name>; |
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 <string_function(<column_name>))
FROM <table_name>; |
desc all_objects
SELECT object_name, LOWER(object_name) LOWER_ONAME
FROM all_objects
WHERE ROWNUM < 11; |
|
Select For Update |
Lock Record(s) |
SELECT <column_name_list)
FROM <table_name_list>
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
<column_name_list>
FROM <table_name> PARTITION (<partition_name>); |
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); |
|
CASE Insensitive Select |
Select From Named Partition |
SELECT DISTINCT
<column_name_list>
FROM <table_name> PARTITION (<partition_name>); |
conn / as sysdba
GRANT select ON v_$nls_parameters TO uwclass;
SELECT * FROM sys.v_$nls_parameters WHERE parameter LIKE '%SORT%';
conn uwclass/uwclass
CREATE TABLE cis_test (
col1 VARCHAR2(10));
INSERT INTO cis_test VALUES ('one');
INSERT INTO cis_test VALUES ('TWO');
INSERT INTO cis_test VALUES ('thRee');
INSERT INTO cis_test VALUES ('FouR');
INSERT INTO cis_test VALUES ('fiVE');
SELECT * FROM cis_test;
SELECT col1 FROM cis_test ORDER BY 1;
ALTER SESSION SET nls_sort=binary_ci;
SELECT * FROM sys.v_$nls_parameters WHERE parameter LIKE '%SORT%';
SELECT col1 FROM cis_test ORDER BY 1; |
|
PL/SQL Select Into |
Selecting In PL/SQL Objects |
SELECT <clause>
INTO <clause>
FROM <clause>
WHERE <clause> |
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;
/
/
/ |