INSERT, UPDATE and DELETE statements have to comply with constraints such as NOT NULL, UNIQUE or CHECK. Also primary or foreign constraints can not be violated.
INSERT is used to add data to the database. An INSERT statement using a VALUES clause inserts a single row as opposed to an INSERT statement with a subquery which can insert potentially multiple rows selected by the subquery. The INSERT command can also insert data into an updatable view's base table.
The DELETE statement removes rows from tables allowing ROLLBACK or COMMIT. In contrast, TRUNCATE deletes rows without the possibility of ROLLBACK.
SELECT a.FIELD, b.FIELD FROM TABLE1 a, TABLE2 b WHERE a.FIELD = b.FIELD;
The SELECT statement is used to retrieve database values from a table/view or set of tables/views based on given selection criteria. An unrestricted SELECT retrieves all rows. Additionally, the SELECT statement is the basis for all subqueries in all other commands. Results are sorted in ascending order unless specified by DESC. The minimum form of the SELECT statement is as follows.
SELECT * FROM <table_name>;
Note that the DUAL table is required when doing non-directed SELECT statements such as from sequences or non-table related, single-row functions. If two or more tables have common column names in a SELECT statement then the table name or table alias must be used to differentiate between the columns. Column aliases can only be used in the ORDER BY clause. When the DISTINCT operator is used then the total number of bytes selected in all select list expressions is limited to the size of a data block less a small overhead.
SELECT DEPT, EMPLOYEE, AVG(SALARY) FROM EMPLOYEES WHERE DEPT IN (20, 30) GROUP BY DEPT, EMPLOYEE;
SELECT DEPT, EMPLOYEE, AVG(SALARY) FROM EMPLOYEES GROUP BY DEPT, EMPLOYEE HAVING DEPT IN (20, 30);
Column headers can be altered in queries by substituting strings with those column headers. Thus the header is altered from the column name to some other value. This new column name is known as a column alias.
Below is an example. Note that the column alias does not need to be enclosed in single quotes ('') unless that column alias contains spaces.
SELECT table_name "Table Name" FROM ALL_TABLES; Table Name ------------------------------ DUAL SYSTEM_PRIVILEGE_MAP TABLE_PRIVILEGE_MAP STMT_AUDIT_OPTION_MAP AUDIT_ACTIONS PSTUBTBL DEF$_TEMP$LOB DEPT EMP BONUS SALGRADE LOCATION DEPARTMENT JOB EMPLOYEE SALARY_GRADE PRODUCT PRICE CUSTOMER SALES_ORDER ITEM
COLUMN <column_name> FORMAT <format_model>;
Thus the following commands would produce the following result.
column table_name format A10; select table_name from all_tables; TABLE_NAME ---------- DUAL SYSTEM_PRI VILEGE_MAP TABLE_PRIV ILEGE_MAP STMT_AUDIT _OPTION_MA P ...
The SET NUMFORMAT command controls the display of all number values and calculation results in a SELECT statement. The SET FORMAT command can be overwritten by individual column formatting commands.
SET NUMFORMAT <numeric_format_model>
Apostrophes or single quotes are used to delineate the start and end of strings. Simply use two single quotes to display a single quote.
select 'This table''s name is ', table_name from all_tables; 'THISTABLE''SNAMEIS' TABLE_NAME --------------------- -------------------------------- This table's name is DUAL This table's name is SYSTEM_PRIVILEGE_MAP This table's name is TABLE_PRIVILEGE_MAP This table's name is STMT_AUDIT_OPTION_MAP This table's name is AUDIT_ACTIONS This table's name is PSTUBTBL This table's name is DEF$_TEMP$LOB This table's name is DEPT This table's name is EMP This table's name is BONUS This table's name is SALGRADE This table's name is LOCATION This table's name is DEPARTMENT This table's name is JOB This table's name is EMPLOYEE This table's name is SALARY_GRADE This table's name is PRODUCT This table's name is PRICE This table's name is CUSTOMER This table's name is SALES_ORDER This table's name is ITEM
The concatenation operator || can be used to concatenate strings and columns in SELECT query statements.
select owner||' owns '||table_name from all_tables; OWNER||'OWNS'||TABLE_NAME ------------------------------------------------------------- SYS owns DUAL SYS owns SYSTEM_PRIVILEGE_MAP SYS owns TABLE_PRIVILEGE_MAP SYS owns STMT_AUDIT_OPTION_MAP SYS owns AUDIT_ACTIONS SYS owns PSTUBTBL SYSTEM owns DEF$_TEMP$LOB SCOTT owns DEPT SCOTT owns EMP SCOTT owns BONUS SCOTT owns SALGRADE DEMO owns LOCATION DEMO owns DEPARTMENT DEMO owns JOB DEMO owns EMPLOYEE DEMO owns SALARY_GRADE DEMO owns PRODUCT DEMO owns PRICE DEMO owns CUSTOMER DEMO owns SALES_ORDER DEMO owns ITEM
Commit stores database changes.
ROLLBACK removes database changes.
AUTOCOMMIT automatically commits changes to the database on INSERT, UPDATE and DELETE statements. SET AUTOCOMMIT has three options.
INSERT, UPDATE and DELETE commands can fire a trigger when specified as an insert, update or delete trigger respectively. A view change will also fire any underlying base table triggers. Note that Oracle triggers are not rules. Rules can be used to construct a knowledge base. This means that for instance, an INSERT statement which selects multiple rows from a SELECT statement query can only fire a trigger once and not a trigger for each insertion. A trigger fired from a multiple row returning select statement will cause an error.
If a view has a WITH CHECK OPTION clause with its definition then the altered rows must meet the criteria in the defining SELECT statement of the view. The base table of a view can be altered with the INSERT, UPDATE and DELETE statements under the following conditions.
The DUAL table is contained within the data dictionary and is used as a dummy object to pass SQL DML statement results into. For example, SELECT 'test string' FROM DUAL; gives the string test string.