The DML is basically of two types :

(i) Procedural DML Requires a user to specify what data is needed and how to get it.

(ii) Non-Procedural DML - Requires a user to specify what data is needed without specifying how to get it.

(i) SQL -Structured Query Language

(ii) DBMS-Database Management System
(iii) RDBMS- Relational Database Management System.

Query : SELECT * FROM student WHERE Age < 18 ORDERBY Name;

(i) char (Character)

(ii) varchar

(iii) dec (Decimal)

(iv) numeric

(v) int (Integer)

(vi) smallint

(vii) float

(viii) real

(ix) double

(i) INSERT:

INSERT INTO <table-name> [column-list] VALUES (values);

(ii) DELETE :

DELETE FROM table-name WHERE condition;

(iii) UPDATE :

UPDATE SET column-name = value, column-name = value,.. WHERE condition;

(i) The DISTINCT keyword is used along with the SELECT command to eliminate duplicate rows in the table. This helps to eliminate redundant data.

(ii) For Example: SELECT DISTINCT PIace FROM Student;

Will display the following data as follows:

Place

Chennai

Bangalore

Delhi

The SAVEPOINT Command is used to temporarily save a transaction so that you can rollback to the point whenever required.
Example: SAVEPOINT savepoint_name;

UPDATE Student SET Name = Mini WHERE Admno=105;

SAVEPOINT A;

Data Definition Language :

(i) Create Command : To create tables in the database.

(ii) Alter Command : The ALTER command is used to alter the table.

(iii) Drop Command : The DROP TABLE command is used to remove a table from the database.

ALTER TABLE <table-name> ADD <column-name><data type><size>;

Example :ALTER TABLE Student ADD Address
CHAR (25)

(i) Constraint is a condition applicable on a field or set of fields.

Primary Key Constraint :

(i) This constraint declares a field as a Primary key which helps to uniquely identify a record.

(ii) It is similar to unique constraint except that only one field of a table can be set as primary key.

(iii) The primary key does not allow NULL value.

ALTER Command:

(i) The ALTER Command is used to alter the table structure like adding a column, renaming the existing column, change the data type of
any column or size of the column or delete the column from the table. It is used in the following way :

ALTER TABLE <Table-name> ADD

<column-name><data type><size>;

(ii) To add a new column "Address" of type char to the Student table, the command is used as

ALTER TABLE Student ADD Address char;

(ii) To modify existing column of table, the ALTER TABLE command can be used with MODIFY clause like wise:

ALTER <table-name> MODIFY

<column-name><data type><size>;

ALTER TABLE Student MODIFY Address char (25);

(iv) The ALTER command can be used to rename an existing column in the following way:

ALTER TABLE <table-name> CHANGE old column-name new-column name new column definition;

(v) The ALTER command can also be used to remove a column or all columns,
ALTER TABLE DROP COLUMN <column-names>;

The various processing skills of SQL are :

(i) Data Definition Language (DDL) : The SQL DDL provides commands for defining relation schemas (structure), deleting relations, creating indexes and modifying relation schemas.

(ii) Data Manipulation Language (DML): The SQL DML includes commands to
insert, delete, and modify tuples in the database.

(iii) Embedded Data Manipulation Language : The embedded form of SQL is used in high level programming languages.

(iv) View Definition : The SQL also includes commands for defining views of tables.

(v) Authorization : The SQL includes commands for access rights to relations and views of tables.

(vi) Integrity : The SQL provides forms for integrity checking using condition.

(vii) Transaction control: The SQL includes commands for file transactions and control over transaction processing.

CREATE TABLE employee

(

eempcode INTEGER NOT NULL,

name CHAR(20),

desig CHAR(20),

pay INTEGER,

allowance INTEGER,

PRIMARY KEY (empcode)

);

(i) GROUP BY clause :

• The GROUP BY clause is used with the SELECT statement to group the students on rows or columns having identical values or divide the table in to groups.

• For example to know the number of male students or female students of a class, the GROUP BY clause may be used.

• The syntax for the GROUP BY clause is

• SELECT <column-names> FROM <table-name> GROUP BY <column-name>

HAVING condition];

• To apply the above command on the student table:

• SELECT Gender FROM Student GROUP BY Gender;

• The following command will give the below given result:

• SELECT Gender, count(*) FROM Student GROUP BY Gender;

(ii) ORDER BY clause :

• The ORDER BY clause in SQL is used to sort the data in either ascending
or descending based on one or more columns.

(a) By default ORDER BY sorts the data in ascending order.

(b) We can use the keyword DESC to sort the data in descending order and the keyword ASC to sort in ascending order.

• The ORDER BY clause is used as :

• SELECT <column-name> [,<column-name>....] FROM <table-name>ORDER BY<column1>,<column2>,....ASC| DESC;

• Example:

To display the students in alphabetical order of their names, the command is used as

• SELECT * FROM Student ORDER BY Name;

The above student table is arranged as follows :

Components of SQL: SQL commands are divided into five categories:

Data Definition Language :

(i) The Data Definition Language (DDL) consist of SQL statements used to define the database structure or schema.

(ii) It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in databases.

(iii) SQL commands which comes under Data Definition Language are :

Data Manipulation Language :

(i) A Data Manipulation Language (DML) is a query language used for adding (inserting), removing (deleting). and modifying (updating) data in a database.

(ii) SQL commands which comes under Data Manipulation Language are :

Data Control Language :

(i) A Data Control Language (DCL) is a programming language used to control the access of data stored in a database. It is used for controlling privileges in the database (Authorization).

(ii) SQL commands which come under Data Control Language are :

Transactional Control Language :

(i) Transactional control language (TCL) commands are used to manage transactions in the database. These are used to manage the changes made to the data in a table by DML statements.

(ii) SQL command which come under Transfer Control Language are :

Data Query Language :

(i) The Data Query Language consist of commands used to query or retrieve data from a database.

(ii) One such SQL command in Data Query Language is