SQL Language Elements and Data type
What is SQL?
SQL is a structure Query language which is used to manage relational database and perform various operations .It is a standardized programming language working on a various queries such as INSERT,SEARCH, UPDATE, DELETE database record. It is pronounced as S-Q-L as See-Quel and it is a standard language for dealing with relational database.
SQL Syntax
SELECT* FROM students where age>20
Language Elements in SQL
The SQL language is divided into several elements that is Clauses, Expression, Predicates, Queries and Statements.
Clauses :- It is a fundamental factor of statement and queries. Clauses are optional for the statements some of the clauses are WHERE clause, HAVING clause.
Expression :- It is the combination of a value which can produce either scalar value.
Predicates :- It is used to specify the condition in the SQL that can calculate to SQL . It is used to determine whether something is TRUE or FALSE in expression and change the flow of the program.
Queries :- Queries is used to extract the data from the database and present in a readable format. Each query has different function and it is based on a specific criteria.
Statement :- A statement is a string of characters which have a fixed effect. SQL statement includes semicolon (",") statement terminator. It controls the flow of program connections, sessions or problems.
Data type in SQL
Internal Data Type | Description |
VARCHAR2 (size) | Variable length character string having maximum length size bytes. Maximum size is 2000 and the minimum is 1. It specify size for a VARCHAR2. |
NUMBER (p,s) | Number having accuracy a and scale s. The accuracy a range is about 1 to 38 and the scale s range is about 84 to 127. |
LONG | Variable length and character data length is uoto 2 gigabytes or 231-1 bytes. |
DATA | Authentic data range from January 1,4712 BC to December 31,4712 AD. |
RAW (size) | Rough binary data of length size bytes. Maximum size is 255 bytes. Specify size of RAW value. |
LONG RAW | Rough binary data of variable length up to 2 gigabytes. |
ROWID | This data type is basically used for values return by the ROWID presdocolumn |
CHAR (size) | Secure length character data of length size byte. Maximum size is 255. Minimum size is 1 byte. |
SQL Data Definition Language(DDL)
The Data Defination language (DDL) is a part of SQL that allow the database table to create or delete .We can also define keys (index), by establishing a link between tables, and set the force between the database tables.
There are some most important DDL statements in SQL are:-
CREATE TABLE
It is used to create a new database table
/*Example*/
CREATE TABLE Student
(Reg_no varchar2(10),
Name char(30),
DOB date,
Address varchar2(50));
ALTER TABLE
It is used to alters(changes) a database table
/*Syntax*/
ALTER TABLE <table_name>
ADD (<NewColumnName> <Data_Type>(<size>),......n)
DROP TABLE
It is used to delete a database table
/*Syntax*/
DROP TABLE <table_name>
CREATE INDEX
It is used to create an index (search key)
/*Syntax*/
CREATE INDEX index_name
ON table_name (column1, column2, ...);
DROP INDEX
It is used to delete an index
/*Syntax*/
DROP INDEX <index name>;
SQL Data Manipulation Language (DML)
The SQL language is used for executing queries and it also includes a syntax to insert, update, and delete records. These queries and update command from a DML (Data Manipulation Language) which is a part of the SQL .
SELECT
It is used to extract the data from the database table
/*Syntax*/
SELECT * FROM Table_name;
UPDATE
It is used to update the data in a database table
/*Syntax*/
UPDATE tablename
SET col1 = value1, col2 = value2, ...
WHERE criteria
DELETE
It is used to delete the data from the database table
/*Syntax*/
DELETE FROM Table_name;