Basic SQL Select Statements

SELECT * FROM all_tables;

Create Table As  (CTAS)
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;

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 <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 HERE 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;

**************************************************************