Easy Way To Understand SQL Statements and Its Uses

1. Objective

Earlier we have discussed SQL Data Types. Today, we will see the SQL Statements Tutorial. So, in this tutorial of statements in SQL, we will discuss types of SQL Statements and their roles.

Easy Way To Understand SQL Statements and Its Uses

Easy Way To Understand SQL Statements and Its Uses

2. What are the Different SQL Statements?

The following is a list of statements which we can use in SQL the categories are listed below

  • Data definition language (DDL) statements
  • Data manipulation language statements (DML)
  • Transaction control statements
  • Session control statements
  • System control statement
  • Embedded SQL statement
SQL Statements

Types of SQL Statements

3. Types of SQL statements

i. Data Definition Language (DDL) statements

A data definition language statement helps you perform the following task –

  • It helps you create drop and alter the objects
  • It also helps you revoke and grant privileges and roles
  • A DDL statement also helps you analyses information available in the form of table index or a plaster
  • It helps you establish auditing options and add comments to data dictionary.

There are certain commands that require an exclusive access for a specific object namely CREATE, ALTER and DROP command an example to this would be the ALTER TABLE command that is it won’t work if another user has an access or open transaction on a specified table.

On the other hand, there are a certain command which does not require an exclusive access namely grant, revoke, analyze, audit and comment you can Analyse a table with an open transaction period

The Oracle database implicitly commenced the current transaction after every DDL statement

The DDL statements are as following

  • ALTER … (All statements beginning with ALTER)
ALTER TABLE table_name
ADD column_name datatype;
  • ANALYZE
ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
   TABLE tbl_name [, tbl_name] ...
ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name
    UPDATE HISTOGRAM ON col_name [, col_name] ...
        [WITH N BUCKETS]
ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
   TABLE tbl_name
   DROP HISTOGRAM ON col_name [, col_name]
  • ASSOCIATE STATISTICS
CREATE STATISTICS statistics
        ON {table | view} (column [,...n] )
           [WITH option  [NORECOMPUTE]
  • AUDIT
CREATE SERVER AUDIT audit_name  
{  
   TO { [ FILE (<file_options> [ , ...n ] ) ] | APPLICATION_LOG | SECURITY_LOG | URL}  
   [ WITH ( <audit_options> [ , ...n ] ) ]   
   [ WHERE <predicate_expression> ]  
}  
[ ; ]  
<file_options>::=  
{  
       FILEPATH = 'os_file_path'  
   [ , MAXSIZE = { max_size { MB | GB | TB } | UNLIMITED } ]  
   [ , { MAX_ROLLOVER_FILES = { integer | UNLIMITED } } | { MAX_FILES = integer } ]  
   [ , RESERVE_DISK_SPACE = { ON | OFF } ]   
}  
<audit_options>::=  
{  
   [ QUEUE_DELAY = integer ]  
   [ , ON_FAILURE = { CONTINUE | SHUTDOWN | FAIL_OPERATION } ]  
   [ , AUDIT_GUID = uniqueidentifier ]  
}  
<predicate_expression>::=  
{  
   [NOT ] <predicate_factor>   
   [ { AND | OR } [NOT ] { <predicate_factor> } ]   
   [,...n ]  
}  
<predicate_factor>::=   
   event_field_name { = | < > | ! = | > | > = | < | < = } { number | ' string ' }
  • COMMENT
/*  text */
  • CREATE … (All statements beginning with CREATE)
Syntax:[code lanuage=”sql”] CREATE TABLE <table_name> (
<column_name1> <datatype1> <constraint1>
<column_name2> <datatype2> <constraint2>
<constraint-list>
) ;
  • DISASSOCIATE STATISTICS
  • DROP … (All statements beginning with DROP)
[code lanuage=”sql”] Drop <table name>;
  • FLASHBACK … (All statements beginning with FLASHBACK)
FLASHBACK TABLE table_name
[TO BEFORE DROP] |
[TO TIMESTAMP time_stamp] |
[TO SCN scn_number] |
[ENABLE TRIGGERS | DISABLE TRIGGERS];
  • GRANT
GRANT [privilege]ON [object]TO [user][WITH GRANT OPTION]
  • NOAUDIT
NOAUDIT {ALL|ALL PRIVILEGES|sql_statement|system_priv} [options]
     [WHENEVER [NOT] SUCCESSFUL]
     options:
           BY user
           BY proxy [ON BEHALF OF ANY|user]
  • PURGE
SELECT * FROM RECYCLEBIN;
SELECT * FROM USER_RECYCLEBIN;
  • RENAME
[code lanuage=”sql”]
ALTER TABLE table_name RENAME TO new_table_name;
  • REVOKE
[code language=”sql”] REVOKE [GRANT OPTION FOR] [permission]ON [object]FROM [user]
  • TRUNCATE
[code language=”sql”] TRUNCATE table <table name>;

ii. Data Manipulation Language Statements (DML)

This kind of statements access and manipulate data in an existing schema object. These statements do not implicitly commit the current transaction following are the manipulation language statements

  • CALL
CALL sp_name([parameter[,...]])
CALL sp_name[()]
  • DELETE
[code language=”sql”] DELETE from table WHERE condition;
  • EXPLAIN PLAN
  • INSERT
[code language=”sql”] INSERT INTO table
(column-1,column-2,…column-n)
(value-1, value-2, … value-n);
  • LOCK TABLE
LOCK TABLES table_name [READ | WRITE]
  • MERGE
MERGE [AS TARGET]  
USING [AS SOURCE]  
ON  
[WHEN MATCHED  
THEN ]  
[WHEN NOT MATCHED [BY TARGET]  
THEN ]  
[WHEN NOT MATCHED BY SOURCE  
THEN ];
  • SELECT
SELECT column1, column2, ...
FROM table_name;
  • UPDATE
[code language=”sql”] UPDATE table SET column1=value1,column2=value2… WHERE condition;

Note

A select statement is a limited form of a DML statement as it cannot manipulate data in the database that can access the data before returning the query it can only access the database

iii. Transaction Control Statements

The role of transaction control statements to manage changes made by DML statement following are the Transaction Control Statement.

  • COMMIT
[code language=”sql”] COMMIT;
  • ROLLBACK
[code language=”sql”] ROLLBACK SavePoint-Name;
  • SAVEPOINT
[code language=”sql”] SAVEPOINT SavePoint-Name;
  • SET TRANSACTION
SET TRANSACTION [ READ WRITE | READ ONLY ];

Note

All the transactions control statements are supported by PL/SQL except commit and rollback

iv. Session Control Statements

The statement also does not commit implicitly to the current transaction the dynamically – the property of a user’s session these statements are not supported by PL / SQL following are the control statements

v. System Control Statement

This kind of statements dynamically manages the property of an Oracle database instance. The ALTER system command which is a single system control statement is used mostly in this task it also doesn’t implicitly commit to the current transaction

Note

This type of statements are also not supported in PL / SQL

vi. Embedded SQL Statement

This kind of statements are supported by the Oracle pre compilers the embedded SQL statements place DDL, DML and transaction control statements in a procedural language program.

4. Conclusion

In this SQL Statements tutorial, we learned about the various Statements in SQL. We have discussed DDL, DML, TCS, and embedded statements in SQL. Also, we saw the role and their types. 

Share your feedback on with us.

Gear up with the Most Asked SQL Interview Questions

Reference for SQL

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.