Learn Sql Server   Learn Sql Theory   Learn Sql

 
Basic SQL

Sql Commands

Sql Like Operator

Sql In Operator

Sql Select statement

Sql update statement

Sql Delete Statement

Sql where clause

Sql alias

Sql Between And Operator

Sql Concatenation Operator

Sql Order By Clause

Sql Is Null And Is Not Null

Sql Select Into

Sql Rollback Statement

Sql Commit Statement

Sql Distinct

Sql Truncate Table

Sql Drop table

Sql Alter

Sql Group by

What is Sql

SQL Functions
Advance SQL
SQL Question

Sql Commands

Sponsored links

SQL commands are basicaly used to communicate with the database to perform specific tasks. SQL commands can be used not only for searching the database but also to perform various other functions like, for example, you can create tables, add data to tables, or modify data, drop the table, set permissions for users. SQL commands are grouped into four major categories depending on their functionality:

DATA DEFINITIONAL LANGUAGE COMMANDS (DDL)

DDL commands are used to define a data base, including creating, altering, dropping (deleting) and modification of definitions for database structures such as tables, views and indexes.

DDL-type commands can be undone using a ROLLBACK command, but not in Oracle Database. DDL commands cannot be committed or rolled back because they are automatically and forcibly committed (permanently changed). Oracle implicitly commits the current transaction before and after every DDL fails. Thus it concludes that, any uncommitted changes made prior to the DDL statement submission cannot be rolled back. These statements are commonly used by a database designer and database administrator for establishing database structures used by an application. Some of the common DDL statement in Oracle include CREATE TABLE, ALTER TABLE, CREATE VIEW, DROP, CREATE INDEX, RENAME etc. integrity constraints can be defined on tables, either when the table is created or later. The DDL also provides commends for specifying access rights or privileges to tables and views.

DATA MANIPULATION LANGUAGE COMMANDS (DML)

ML commands are used to maintain and query a database, including updating, inserting, modifying, and querying data. ALL DML commands are subject to transactional control. Transactional control includes the COMMIT and ROLLBACK commands, which allows changes to be permanently stored or undone, respectively. These commands are used most often by application developers.
some of the common DML commands in Oracle include:

INSERT : Adds data into the rows to a database (table/view).
UPDATE : Modifies data of existing rows in a database (table/view).
DELETE : Removes data of existing rows in database (table/view).
SELECT : Retrieves data of rows from a database (table/view).

Unlike DDL that creates, modifies or destroys database structures and does not deal with data, the DML is the part of SQT that operates on data although most of DML statements are very easy to understand and resembles with ordinary English language sentences but some of them can be very complex when they include multiple expression, clauses or subqueries.

DATA CONTROL LANGUAGE COMMANDS (DCL)

DCL commands are used to control the database including administering privileges and saving of data. DCL commands are used to determine whether a user is allowed to carry out a particular option or not. The ANSI standards groups these commands are being part of the DDL.

It define activities that are not in the categories of those for the DDL and DML such as granting privileges to the user etc. It protects your database from a verity of threats including hardware breakdown, software operator errors and malicious hackers. Some of the common DCL statements in Oracle include GRANT, REVOKE etc.

TRANSACTION CONTROL LANGUAGE (TCL) COMMANDS

A database transaction is a group of SQL statements that are a logical unit of work. you can think of a transaction as a set of SQL statements that should be made permanent in the database (or undone) as a whole. An example of this would be a transfer of money from one bank account to another. One UPDATE statement would subtract from the total amount of money from one account, and another UPDATE would add money to the other account. Both the subtraction and the addition must either be permanently recorded in the database, or they both must be undone-otherwise money will be lost. This simple example uses only two UPDATE statements, but a more realistic transaction may consist of many INSERT, UPDATE, and DELETE statements.

Also you database is more vulnerable to damage while it is in the process of being changed. A software or hardware failure that occurs while the change is in progress could leave the database in an inconsistent state. SQL protects your database by restricting operations that can change it so that they occur only within transactions. The TCL statements manage the change made by DML statements and group statements into transactions. The various TCL statements are COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION etc.

Sponsored Links