Data types of SQL
Just like any other programming language, the facility of defining data of various types is available in
SQL also. Following are the most common data types of SQL.
3) VARCHAR / VARCHAR2
6) RAW/LONG RAW
1. NUMBER Used to store a numeric value in a field/column. It may be decimal, integer or a real value. General syntax is Number(n,d) Where n specifies the number of digits and d specifies the number of digits to the right of the decimal point. e.g marks number(3) declares marks to be of type number with maximum value 999. pct number(5,2) declares pct to be of type number of 5 digits with two digits to the right of decimal point.
2. CHAR Used to store character type data in a column. General syntax is Char (size) where size represents the maximum number of characters in a column. The CHAR type data can hold at most 255 characters. e.g name char(25) declares a data item name of type character of upto 25 size long.
3. VARCHAR/VARCHAR2 This data type is used to store variable length alphanumeric data. General syntax is varchar(size) / varchar2(size) where size represents the maximum number of characters in a column. The maximum allowed size in this data type is 2000 characters. e.g address varchar(50); address is of type varchar of upto 50 characters long.
4. DATE Date data type is used to store dates in columns. SQL supports the various date formats other that the standard DD-MON-YY. e.g dob date; declares dob to be of type date.
5. LONG This data type is used to store variable length strings of upto 2 GB size. e.g description long;
6. RAW/LONG RAW To store binary data (images/pictures/animation/clips etc.) RAW or LONG RAW data type is used. A column LONG RAW type can hold upto 2 GB of binary data. e.g image raw(2000);
Constraints are the conditions that can be enforced on the attributes of a relation. The constraints come in
play when ever we try to insert, delete or update a record in a relation.They are used to ensure integrity of
a relation, hence named as integrity constraints.
1. NOT NULL
3. PRIMARY KEY
4. FOREIGN KEY
i. Not Null constraint : It ensures that the column cannot contain a NULL value.
ii. Unique constraint : A candidate key is a combination of one or more columns, the value of which uniquely identifies each row of a table.
iii. Primary Key : It ensures two things :
(i) Unique identification of each row in the table.
(ii) No column that is part of the Primary Key constraint can contain a NULL value.
iv. Foreign Key : The foreign key designates a column or combination of columns as a foreign key and establishes its relationship with a primary key in different table.
Create table Fee (RollNo number(2) Foreign key (Rollno) references Student (Rollno), Name varchar2(20) Not null, Amount number(4), Fee_Date date);
v. Check Constraint : Sometimes we may require that values in some of the columns of our table are to be within a certain range or they must satisfy certain conditions.
Example: Create table Employee (EmpNo number(4) Primary Key, Name varchar2(20) Not Null, Salary number(6,2) check (salary > 0), DeptNo number(3) );
Operators in SQL:
The following are the commonly used operators in SQL
1. Arithmetic Operators +, -, *, /
2. Relational Operators =, <, >, <=, >=, <>
3. Logical Operators OR, AND, NOT
Arithmetic operators are used to perform simple arithmetic operations.
Relational Operators are used when two values are to be compared and
Logical operators are used to connect search conditions in the WHERE Clause in SQL.
Other operators :
4. Range check – between low and high
5. List check – in
6. Pattern check – like , not like ( % and _ ‘under score’ is used)
SQL Functions :
SQL supports functions which can be used to compute and select numeric, character and date
columns of a relations. These functions can be applied on a group of rows. The rows are grouped on a
common value of a column in the table. These functions return only one value for a group and
therefore, they are called aggregate or group functions.
1. SUM() :
It returns the sum of values of numeric type of a column.
Eg. Select sum(salary) from employee;
2. AVG() :
It returns the average of values of numeric type of a column.
Eg. Select avg(salary) from employee;
3. MIN() :
It returns the minimum of the values of a column of a given relation.
Eg. Select min(salary) from employee;
4. MAX() :
It returns the maximum of the values of a column of a given relation.
Eg. Select max(salary) from employee;
It returns the number of rows in a relation.
Eg. Select count(*) from employee;