When you are executing an SQL command for any RDBMS, the
system determines the best way to carry out your request and
SQL engine figures out how to interpret the task.
There are various components included in this process. These
components are −
Query Dispatcher
Optimization Engines
Classic Query Engine
SQL Query Engine, etc.
A classic query engine handles all the non-SQL queries, but a SQL
query engine won't handle logical files. Following is a simple
diagram showing the SQL Architecture –
SQL Data type
An SQL data type refers to the type of data which can be stored in
a column of a database table.
o SQL Data type is used to define the values that a
column can contain.
o Every column is required to have a name and data
type in the database table.
Binary Datatypes
There are Three types of binary Datatypes which are
given below:
Data Description
Type
binary It has a maximum length of 8000
bytes. It contains fixed-length binary
data.
varbinar It has a maximum length of 8000
y bytes. It contains variable-length
binary data.
image It has a maximum length of
2,147,483,647 bytes. It contains
variable-length binary data.
2. Approximate Numeric Datatype :
The subtypes are given below:
Data From To Description
type
float -1.79E 1.79E It is used to specify a
+ 308 + 308 floating-point value
e.g. 6.2, 2.9 etc.
real -3.40e 3.40E It specifies a single
+ 38 + 38 precision floating
point number
3. Exact Numeric Data type
The subtypes are given below:
Data Description
type
int It is used to specify an integer value.
smallint It is used to specify small integer
value.
bit It has the number of bits to store.
decimal It specifies a numeric value that can
have a decimal number.
numeric It is used to specify a numeric value.
4. Character String Datatype
The subtypes are given below:
Data Description
type
char It has a maximum length of 8000
characters. It contains Fixed-length non-
unicode characters.
varchar It has a maximum length of 8000
characters. It contains variable-length
non-unicode characters.
5. Date and time Datatypes
The subtypes are given below:
Datatype Description
date It is used to store the year, month,
and days value.
time It is used to store the hour, minute,
and second values.
timesta It stores the year, month, day, hour,
mp minute, and the second value.
SQL Commands
o SQL commands are instructions. It is used to communicate
with the database. It is also used to perform specific tasks,
functions, and queries of data.
o SQL can perform various tasks like create a table, add data to
tables, drop the table, modify the table, set permission for
users.
Types of SQL Commands
There are five types of SQL commands: DDL, DML, DCL, TCL, and
DQL.
Data Definition Language (DDL)
o DDL changes the structure of the table like creating a table,
deleting a table, altering a table, etc.
o All the command of DDL are auto-committed that means it
permanently save all the changes in the database.
Here are some commands that come under DDL:
o CREATE
o ALTER
o DROP
o TRUNCATE
o CREATE It is used to create a new table in the database. The
command define each column of the table uniquely. Each
column has minimum of three attributes a name, data type
and size. Each table column definition is separated from the
other by a comma. Finally, the sql statement is terminated
with semicolon.
Syntax:
CREATE TABLE TABLE_NAME
(COLUMN SPECIFICATION[,....]);
IT INCLUDES COL- NAME
,DATA TYPE ,LENGTH,
COSTRAINTS.
EXAMPLE-
CREATE TABLE EMPLOYEE
(E_CODE CHAR (4),
NAME CHAR(20),
AGE NUMBER(4);
DROP: It is used to delete both the structure and record stored in
the table.
1. Syntax- DROP TABLE table_name;
Example
DROP TABLE EMPLOYEE;
ALTER: It is used to alter the structure of the database. This change
could be either to modify the characteristics of an existing attribute
or probably to add a new attribute. You can add ,modify, delete
columns. The structure of the table can be modified by Alter table.
Syntax:
To add a new column in the table..
ALTER TABLE table_name
ADD column_name COLUMN-definition;
Example
ALTER TABLE EMPLOYEE
ADD( ADDRESS VARCHAR(30),
PHONE_NO VARCHAR(10));
To modify existing column in the table:
Syntax:
ALTER TABLE table_name
MODIFY(column_definitions....);
Example
ALTER TABLE EMPLOYEE
MODIFY( NAME VARCHAR(20));
DROP: It is used to delete both the structure and record stored in
the table.
Syntax
DROP TABLE table_name;
Example
DROP TABLE EMPLOYEE; OR
ALTER TABLE EMPLOYEE
DROP NAME;
TRUNCATE: It is used to delete all the rows from the table and free
the space containing the table.
Syntax:
1. TRUNCATE TABLE table_name;
Example:
1. TRUNCATE TABLE EMPLOYEE;
2. Data Manipulation Language
o DML commands are used to modify the database. It is
responsible for all form of changes in the database.
o The command of DML is not auto-committed that means it
can't permanently save all the changes in the database. They
can be rollback.
Here are some commands that come under DML:
o INSERT
o UPDATE
o DELETE
o SELECT
INSERT: The INSERT statement is a SQL query. It is used to insert
data into the row of a table.
Syntax:
INSERT INTO TABLE_NAME
(col1, col2, col3,.... col N)
VALUES (value1, value2, value3, .... valueN);
Example
INSERT INTO EMPLOYEE
VALUES (‘XYZ’,’JAY’,34);
INSERT DATA IN SPECIFIED COLUMNS
IF DATA IS NOT AVAILABLE FOR ALL THE COLUMNS THEN THE LIST
MUST BE INCLUDED.
EXAMPLE-
INSERT INTO EMPLOYEE(NAME,AGE)
VALUES(‘AMAN’,23);
INSERTING THROUGH PARAMETER SUBSTITUTION
INSERT INTO EMPLOYEE
VALUES (‘&1’,’&2’,&3);
UPDATE: This command is used to update or modify the value of a
column in the table.
Syntax:
UPDATE table_name SET [column_name1= value1,...column_nameN
= valueN] [WHERE CONDITION]
For example:
UPDATE EMPLOYEE
SALARY=SALARY+1000;
OR
UPDATE EMPLOYEE
SALARY=SALARY+1000;
WHERE E_CODE=’222’;
DELETE: It is used to remove one or more row from a
table.
Syntax:
1. DELETE FROM table_name [WHERE condition];
For example:
1. DELETE FROM EMPLOYEE
2. WHERE EMP_ID=’222’;
OR
DELETE EMPLOYEE;//THIS JUST DELETE ALL THE ROW OF THE TABLE
RATHER THAN DELETING THE TABLE.
Data Control Language
DCL commands are used to grant and take back authority from any
database user.
Here are some commands that come under DCL:
o Grant
o Revoke
