Oracle SQL - Introduction SQL Introduction

  1. HOME
  2. Oracle SQL
  3. Introduction
  4. SQL Introduction

Introduction

SQL (Structured Query Language) is a language to work with database.

The SQL language is divided into the following four command categories:

  • Data definition Language or DDL
  • Data manipulation Language or DML
  • Retrieval
  • Security and authorization, Data Control Language, or DCL

Data Definition

The SQL data definition commands allow you to create, modify, and remove components of a database structure.

Typical database structure components are tables, views, indexes, constraints, synonyms, sequences, and so on.

Almost all SQL data definition commands start with one of the following three keywords:

  • CREATE, to create a new database object
  • ALTER, to change an aspect of the structure of an existing database object
  • DROP, to drop (remove) a database object

For example, with the CREATE VIEW command, you can create views.

With the ALTER TABLE command, you can change the structure of a table, for example by adding, renaming, or dropping a column.

With the DROP INDEX command, you can drop an index.

Data Manipulation and Transactions

SQL data manipulation commands allow you to change the contents of your database.

SQL offers three basic data manipulation commands:

  • INSERT, to add rows to a table
  • UPDATE, to change column values of existing rows
  • DELETE, to remove rows from a table

You can add rows to a table with the INSERT command in two ways.

  • add rows one by one by specifying a list of column values in the VALUES clause of the INSERT statement.
  • add one or more rows to a table based on a selection and manipulation of existing data in the database called a subquery.

DELETE empties a table; DROP removes a table.

TRUNCATE allows you to delete all the rows in a table in an efficient (but irrevocable) way.

UPDATE changes the contents of a table; ALTER changes its structure.

Data manipulation commands are always treated as being part of a transaction.

All database changes caused by SQL data manipulation commands get a pending status until you confirm (commit) or cancel (roll back) the transaction.

No one, except the transaction itself, can see the pending changes of a transaction before it is committed.

SQL offers two commands to control your transactions explicitly:

  • COMMIT, to confirm all pending changes of the current transaction
  • ROLLBACK, to cancel all pending changes and restore the original situation

Sometimes, transactions are committed implicitly without any explicit request from a user.

For example, every data definition command (like CREATE, DROP, TRUNCATE etc.) implicitly commits your current transaction.