SQL language
SQL is a standard non-procedural language for relational database management systems (RDBMS)
Examples of SQL queries:
SELECT ename FROM emp;
SELECT last_name, department_id FROM employees;
DELETE FROM department WHERE department_id IN (10, 30, 70);
CREATE TABLE articles (article_id VARCHAR2 (15), designation VARCHAR2 (40));
INSERT INTO articles VALUES (‘imp1’, laser printer ’);
DROP TABLE articles;
GRANT SELECT ON department TO scott;
REVOKE DELETE ON department FROM scott;
The terms SELECT, FROM, DELETE, WHERE, IN, CREATE, TABLE, VARCHAR2, INSERT, INTO, VALUES, DROP, GRANT, TO, REVOKE, ON are words reserved for SQL
Oracle SQL statements are classified into categories:
DML commands for Data Manipulation Language (SELECT, INSERT, UPDATE, DELETE)
DDL commands for Data Definition Language (CREATE, ALTER, DROP, RENAME, TRUNCATE, GRANT, REVOKE…).
DDL commands are automatically validated: they generate an implicit commit.
Control Commands of:
Transactions (COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION)
Sessions (ALTER SESSION)
and System (ALTER SYSTEM)
SQL Recursive commands:
When an DDL command is issued, Oracle automatically issues an LMD command to modify the corresponding information in the data dictionary. This last command is called a recursive SQL command.
An DDL "CREATE TABLE" command, for example, issued by a user to create a table in their own schema, automatically generates another recursive LMD command (INSERT) in the SYS schema: data dictionary tables. In fact, the information on the structure of the newly created table must be inserted and available in the data dictionary.
Stay connected with ORAGEEK for advance Oracle DBA Tips with Dbametrix.
