SQL Categories

Structured Query Language(SQL) as we all know is the database language by the use of which we can perform certain operations on the existing database and also we can use this language to create a database.

SQL commands are mainly categorized into four categories as:

·         DDL - Data Definition Language.

·         DML - Data Modeling/Manipulation Language.

·         DCL - Data Control Language.

·         TCL - Transaction Control Language.

ü  DDL (Data Definition Language): It actually consists of the SQL commands that can be used to define the database schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in the database.

Examples of DDL:

·       CREATE – is used to create the database or its objects (like table, index, function, views, store procedure and triggers).

·       DROP – is used to delete objects from the database.

·       ALTER - is used to alter the structure of the database.

·       TRUNCATE – is used to remove all records from a table, including all spaces allocated for the records are removed.

·       RENAME – is used to rename an object existing in the database.

ü  DML (Data Manipulation Language): The SQL commands that deals with the manipulation of data present in the database belong to DML or Data Manipulation Language.

Examples of DML:

·         INSERT – is used to insert data into a table.

·         UPDATE – is used to update existing data within a table.

·         DELETE – is used to delete records from a database table.

Note: The SELECT statement is a limited form of DML statement in that it can only access data in the database. It cannot manipulate data in the database, although it can operate on the accessed data before returning the results of the query.

ü  DCL (Data Control Language): DCL includes commands such as GRANT and REVOKE which mainly deals with the rights, permissions and other controls of the database system.

Examples of DCL:

·         GRANT - gives user’s access privileges to database.

·         REVOKE - withdraw user’s access privileges given by using the GRANT command.

ü  TCL (transaction Control Language) : TCL commands deals with the transaction within the database.

Examples of TCL:

·         COMMIT – commits a Transaction.

·         ROLLBACK – rollbacks a transaction in case of any error occurs.

·         SAVEPOINT – sets a savepoint within a transaction.

Subqueries

What is a subquery?

A subquery is a query within a query. In Oracle, you can create subqueries within your SQL statements. These subqueries can reside in the WHERE clause, the FROM clause, or the SELECT clause.

• WHERE clause

Most often, the subquery will be found in the WHERE clause. These subqueries are also called nested subqueries.

For example:

select * from all tables tabs
where tabs.table_name in	(select cols.table_name
	from all_tab_columns cols
	where cols.column_name = 'SUPPLIER_ID');

Limitations: Oracle allows up to 255 levels of sub queries in the WHERE clause.

• FROM clause

A subquery can also be found in the FROM clause. These are called inline views.

For example:

select suppliers.name, subquery1.total_amt
from suppliers,
   (select supplier_id, Sum(orders.amount) as total_amt
   from orders
   group by supplier_id) subquery1,
where subquery1.supplier_id = suppliers.supplier_id;

In this example, we've created a sub-query in the FROM clause as follows:

(select supplier_id, Sum(orders.amount) as total_amt

 from orders
 group by supplier_id) subquery1

This subquery has been aliased with the name subquery1. This will be the name used to reference this subquery or any of its fields.

Limitations: Oracle allows an unlimited number of subqueries in the FROM clause.

• SELECT clause

A subquery can also be found in the SELECT clause.

For example:

select tbls.owner, tbls.table_name,
  (select count(column_name) as total_columns
   from all_tab_columns cols
   where cols.owner = tbls.owner
   and cols.table_name = tbls.table_name) subquery2
from all_tables tbls;

In this example, we've created a subquery in the SELECT clause as follows:

(select count(column_name) as total_columns
 from all_tab_columns cols
 where cols.owner = tbls.owner
 and cols.table_name = tbls.table_name) subquery2

The subquery has been aliased with the name subquery2. This will be the name used to reference this subquery or any of its fields.

Note: The trick to placing a subquery in the select clause is that the subquery must return a single value. This is why an aggregate function such as SUM, COUNT, MIN, or MAX is commonly used in the subquery.

Insert Statement

Insert Statement

INSERT statement is used to insert a single record or multiple records into a table in Oracle.

1.    To enter records into a table we use INSERT command

2.    Using "&" we can read the values from key board in SQL.

•          INSERT statement when inserting a single record using the VALUES keyword is: 

Syntax:

INSERT INTO Table

(column1, column2, column3…. columnx)

VALUES

(Values1, values2, values3…...valuesx);

Example:

•  INSERT statement when inserting multiple records using a SELECT statement is:

Syntax:

INSERT INTO Table

(column1, column2, column3…. columnx)

                        SELECT field1, field2, field3….. fieldx 

FROM source table

[WHERE condition] --- (where condition is optional)

Example:

INSERT INTO Employee

(emp_id, emp_name)

                        SELECT trainee_id, trainee_name 

FROM Trainee

WHERE status = 'A';

Tables and Constraints

Table: Tables are basic units of data storage. A table is defined as intersection of rows and columns. Data is stored in rows and columns.

CREATING TABLES: A table name can not exceed 30 characters and necessarily should start with a character. Blank spaces and other special characters except an “under score” ( _ ) are not allowed in table names.

 Each column in the table will be given a unique name. Every column is assigned a data type corresponding to the data to be entered into the column. Column names are also restricted to 30 characters and follow the same rules as table names.

Syntax for creating a Table:CREATE TABLE <Table Name>

(<Column Name1> <Data Type>,<Column Name2> <Data Type>, <Column Name1> <Data Type>);

Example:

DROPPING A TABLE: To drop a table it must already exist in the database. The syntax for DROP TABLE statement is DROP TABLE <Table Name>;

Constraints: An integrity constraint defines a business rule for a table column. When enabled, the rule will be enforced by oracle. The two basic 

Integrity Constraints Supported by Oracle

1) NOT NULL: A column with this constraint will not allow NULL values.
2) PRIMARY KEY: There can be only one primary key column in a table. This will only UNIQUE values. Does not allow NULL values.
3) UNIQUE KEY: We can have any number of primary keys in a table. UNIQUE Constraint also would accept only UNIQUE values.
4) CHECK: This constraint defines a condition which need to be satisfied by the value entering into the table.
5) FOREIGN KEY: A foreign key is a combination of columns with values based primary. It is also known as referential integrity constraint. Values that a foreign key can take are the values that are present in primary key. 

Creating Table Using Constraints:

Note: In the above screen shot lines after “----“ are comments. Different ways of defining constraints are given above.

ALTER TABLE command is used for modifying structure of the table.
1) 1) Adding and deleting or renaming columns.
2) 2) Increasing and decreasing column data size.
3) 3) Changing column data type.
4) 4) Enable or Disable constraints.

Syntax:
ALTER TABLE
ADD | MODIFY | DROP | ENABLE | DISABLE |

The following screen shots illustrate how to add a column and how to remove a column.

ALTER TABLE can be used to add and remove constraints as shown below

Data Types

Oracle Data Types:

CHAR: Char data type stores fixed-length character strings.

Max Size: Oracle 7 255 bytes Default and minimum size is 1 byte.
Max Size: Oracle 8 2000 bytes Default and minimum size is 1 byte.
Max Size: Oracle 9 2000 bytes Default and minimum size is 1 byte.

VARCHAR or VARCHAR2: This data type stores variable-length character data. Varchar is a deprecated data type and is a synonym for varchar2.

Max Size: Oracle 7 2000 bytes minimum is 1
Max Size: Oracle 8 4000 bytes minimum is 1
Max Size: Oracle 9 4000 bytes minimum is 1

NUMBER: The NUMBER data type stores fixed and floating point numbers. We can specify Precision

(Total number of digits) and scale (number of digits to the right of decimal point).

Max Size: Oracle 7 The precision p can range from 1 to 38.The scales can range from -84 to 127.
Max Size: Oracle 8 The precision p can range from 1 to 38.The scales can range from -84 to 127.
Max Size: Oracle 9 The precision p can range from 1 to 38.The scales can range from -84 to 127.

DATE: The DATE data type stores dates and time in the table.

Max Size:Oracle 7 from January 1, 4712 BC to December 31, 4712 AD.
Max Size:Oracle 8 from January 1, 4712 BC to December 31, 9999 AD.
Max Size:Oracle 9 from January 1, 4712 BC to December 31, 9999 AD.

LONG: The LONG data type stores variable-length character data (bigger than VARCHAR2) up to two gigabytes length. You can use LONG columns to store long text strings.

Max Size:Oracle 7 2 Gigabytes
Max Size:Oracle 8 2 Gigabytes
Max Size:Oracle 9 2 Gigabytes

RAW: This data type stores binary data. We must specify the size for RAW value.

Max Size:Oracle 7 Maximum size is 255 bytes.
Max Size:Oracle 8 Maximum size is 2000 bytes
Max Size:Oracle 9 Maximum size is 2000 bytes

LONG RAW: Raw binary data of variable length.

Max Size:Oracle 7 2 Gigabytes.
Max Size:Oracle 8 2 Gigabytes.
Max Size:Oracle 9 2 Gigabytes.

LARGE OBJECTS (LOBs): The above data types are called traditional oracle data types. LOBs are added from Oracle 8.0 version. From there Oracle 8.0 is considered as ORDBMS (Object relational database management system.). The traditional oracle database is extended to include object-oriented concepts and structures such as abstract data types, nested tables, varying arrays, object views and references.

LOB datatypes are capable of storing large volumes of data. The LOB datatypes available are BLOB, CLOB, NCLOB, and BFILE.

CLOB ( Character Large Object): This data type is used to store character data.

Max Size:Oracle 7
Max Size:Oracle 8 4Gigabytes
Max Size:Oracle 9 4Gigabytes

BLOB ( Binary Large Object): This data type is used for binary data.

Max Size:Oracle 7
Max Size:Oracle 8 4Gigabytes
Max Size:Oracle 9 4Gigabytes

NLOB (National Character Large Object): This datatype is used to store character data containing Unicode characters.( ASCII character is of 1 byte and UNICODE character is of 2 bytes )

Max Size:Oracle 7
Max Size:Oracle 8 4Gigabytes
Max Size:Oracle 9 4Gigabytes

BFILE (Binary File): It is a pointer to external file. The files referenced by BFILE exist in the file system. The database only maintains a pointer to the file. The size of the external file is limited only by the operating system. ORACLE does not maintain concurrency and integrity of the data. 

Note: A table can use more than one LOB. Earlier to LOBs it was possible to have only one LONG datatype column in a table.