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.
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:-
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));
It is used to alters(changes) a database table
/*Syntax*/ ALTER TABLE <table_name> ADD (<NewColumnName> <Data_Type>(<size>),......n)
It is used to delete a database table
/*Syntax*/ DROP TABLE <table_name>
It is used to create an index (search key)
/*Syntax*/ CREATE INDEX index_name ON table_name (column1, column2, ...);
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 .
It is used to extract the data from the database table
/*Syntax*/ SELECT * FROM Table_name;
It is used to update the data in a database table
/*Syntax*/ UPDATE tablename SET col1 = value1, col2 = value2, ... WHERE criteria
It is used to delete the data from the database table
/*Syntax*/ DELETE FROM Table_name;