Data Types and Literals in SQL

Every field or column in a table is given a data type when a table is defined. These data types describe the kind of information which can be stored in a column.

Since relational database systems are based of the relationship between pieces of information, various types of data must be clearly distinguished from one another, so that the appropriate processes and comparisons can be applied. In SQL, this is done by assigning each field a data type that indicates the kind of value the field will contain. All the values in a given field must be of the same type.

Some of the common data types that we shall be using in SQL are

1. CHAR. 

2.VARCHAR2.

3. NUMBER.

4. DATE.

5. LONG.

CHAR(size): 

A column defined with a CHAR data type is allowed to store all types of characters which include letters (both upper and lower case) Special characters like (@ # $ & etc) and Numerals that are perceived by the systems as letters. However you cannot perform any mathematical operations on these numbers.

VARCHAR2(size): 

It is similar to CHAR but can store variable sized sting having a maximum length determined by (size). Maximum value the (size) can have is 2000 characters.

NUMBERS(p, s):

It is used to store variable length data. The value of p determines the total number of digits possible to the left of decimal point. The second syllable s, determines the total number of digits possible to the right of decimal point. 

DATE:

This type of data is used to store date and time information. Default format is DD-MM-YY.

LONG:

LONG data type stores variable length character strings containing up to 2 gigabytes size. 

But use of LONG data type has got the following limitations :

a. A table cannot have more than one LONG type of data field.

b. It cannot be indexed.

c. It cannot be used with SQL functions.

d. It cannot appear in WHERE. GROUP BY, ORDER BY, clauses. 

Types of SQL Commands 

SQL commands can be classified into the following three types:

A. Data  definition language commands (DDL)

B. Data manipulation language commands (DML)

C. Data control language commands ( DCL).

Data Definition language:

Data Definition language is a part of SQL which consists of those commands that create the objects (table, indexes, views, etc) in the database. Create, Drop, Alter are a few commands of DDL language.

Data Manipulation Language:

Data manipulation language is that part of SQL which consists of a set of commands that determine which values are present in the table at any given time. 

Data Manipulation language is divided into three categories:

a. Retrieving data.

b. Manipulating data 

c. Updating data.

Data manipulation language has commands like (update, insert, delete and select)

Data Control Language:

Data Control language is another portion of SQL which allows definition of a security mechanism or scheme for protecting data from unauthorized access. DCL consists of features that determine whether a user is permitted to perform a particular action. it contains commands like Grant, Revoke etc. These are also known as Transaction Control Commands.

SQL Operators

We need operators to do Arithmetic operations such as addition, subtraction, etc. on column values. Similarly, to perform relational comparison, we need relational operators like <, >, <=, >=, etc. 

SQL operators may be classified as: 

 Arithmetic operators.        Relational operators

 SQL have the following four arithmetic operators: 

+ (Addition)  – (Subtraction)  * ( Multiplication)  / (Division).

In addition to the =,<, >, <=, >=, relational operators, 

SQL has the following comparison operators:

a. LIKE.     b. IN.     c. BETWEEN.