SELECT DATA FROM A TABLE
SELECT col1, col2 FROM table; |
Selecting data in columns1, columns1 from a specified table |
SELECT * FROM table; |
Selecting ALL data from a specified table |
SELECT col1, col2 FROM table WHERE condition; |
Selecting data in columns1, columns1 from a specified table, with a condition(like a filter) |
SELECT DISTINCT col1 FROM table WHERE condition; |
Selecting distinct rows from a specified table |
SELECT col1, col2 FROM table ORDER BY col1 ASC [DESC]; |
Display the data in ascending or descending order |
SELECT col1, aggregate(col2) FROM table GROUP BY col1; |
Group rows using an aggregate* function |
SELECT col1, aggregate(col2) FROM table GROUP BY col1 HAVING condition; |
Filter groups using HAVING with an aggregate function |
|
|
SELECTING DATA FROM MULTIPLE TABLES
SELECT col1, col2 FROM table1 INNER JOIN table2 ON condition; |
Returning data from different tables, with a specific condition |
SELECT col1, col2 FROM table1 LEFT JOIN table2 ON condition; |
Returning data from left table matching data in table2, with a specific condition |
SELECT col1, col2 FROM table1 RIGHT JOIN table2 ON condition; |
Returning data from right table matching data in table2, with a specific condition |
SELECT col1, col2 FROM table1 FULL OUTER JOIN table2 ON condition; |
Returning data from left & right table when both sides match, with a specific condition |
CHECK FOR NULL VALUES
SELECT col1, col2 FROM table WHERE col1 IS NOT NULL; |
Check if the value is not empty |
SELECT col1, col2 FROM table WHERE col1 IS NULL; |
Check if the value is empty |
|
|
AGGREGATE FUNCTIONS
AVG() |
Returns the Average |
COUNT() |
Returns the Number of rows |
FIRST() |
Returns the First alue in a column |
LAST() |
Returns the Last alue in a column |
MAX() |
Returns the Highest value |
MIN() |
Returns the Lowest value |
SUM() |
Returns the Sum of all values |
|
|
CREATING & EDITING TABLES
CREATE TABLE PERSON (ID VARCHAR2(15), Name VARCHAR2(20), Phone NUMBER PRIMARY KEY (ID) ); |
Create a new table with three columns |
INSERT INTO Table1(COL LIST) VALUES(VALUES LIST); |
Insert a row into a table |
INSERT INTO Table1(COL LIST) VALUES(VALUE LIST),(VALUE LIST),....; |
Insert multiple rows into a table |
UPDATE Table1 SET Col1 =newValue; |
Updates a new value in col1 for all rows |
DELETE FROM Table1; |
Delete all data in a table |
DELETE FROM Table1 WHERE specified condition; |
Delete a subset of rows in a table |
|
