UNIT-2

INTRODUCTION:

Relational data model:

Relational data model is the primary data model, which is used widely around the
world for data storage and processing. It is a collection of rows and columns .This
model is simple and it has all the properties and capabilities required to process data
with storage efficiency.

Concepts:
Tables: In relational data model, relations are saved in the format of Tables. This format
stores the relation among entities. A table has rows and columns, where rows
represent records and columns represent the attributes.
Tuple: A single row of a table, which contains a single record for that relation is
called a tuple.
Relation: A relation is usually represented as a table, organized into rows
and columns. A relationship consists of multiple records. For example:
student relation which contains tuples and attributes.
Relation instance: A finite set of tuples in the relational database system
represents relation instance. Relation instances do not have duplicate tuples.
Relation schema: A relation schema describes the relation name (table name),
attributes, and their names.
Domain: It contains a set of atomic values that an attribute can take. It could be
accomplish explicitly by listing all possible values or specifying conditions that all
values in that domain must be confirmed. For example: the domain of gender
attributes is a set of data values "M" for male and "F" for female.
Attribute: The smallest unit of data in relational model is an attribute. It contains the
name of a column in a particular table. Each attribute Ai must have a domain,
dom(Ai)
Relation key: Each row has one or more attributes, known as relation key, which can
identify the row in the relation (table) uniquely.
Attribute domain: Every attribute has some predefined value scope, known as attribute
domain.

Properties of Relations

○ Each attribute in a relation has only one data value corresponding to it i.e. they
do not contain two or more values.

○ Name of the relation is distinct from all other relations.

 ○ Each relation cell contains exactly one atomic (single) value

○ Each attribute contains a distinct name

○ Attribute domain has no significance

○ tuple has no duplicate value

○ Order of tuple can have a different sequence

○ It also provides information about metadata.

○ Keys on relation

Topic: Concept of key and its types

Key in relational data model

KEYS:
➢ In relational model the Keys are important because they are used to ensure that each
row in a table is uniquely identifiable. These keys are also used to establish relationships
among tables and to ensure the integrity of the data.
➢ A key consists of one or more attributes that determines other attributes.

There are five types of keys

Types of keys are there in the Relational Database Model?
•Candidate Key
•Primary Key
•Super Key
•Alternate Key
•Foreign Key
•Composite Key
Super key
 Candidate key
An attribute or The minimal set of attributes that can uniquely identify a tuple is known as
a candidate key. For Example, STUD_NO,rollno,Email in STUDENT relation. Are
candidate keys
● It is a minimal super key.
● It is a super key with no repeated data is called a candidate key.
● The minimal set of attributes that can uniquely identify a record.
● It must contain unique values.
● It can contain NULL values.
● Every table must have at least a single candidate key.
● A table can have multiple candidate keys but only one primary key
● ex:

.
 Alternate Key
The candidate key other than the primary key is called an alternate key .
● All the keys which are not primary keys are called alternate keys.
● It is a secondary key.
● It contains two or more fields to identify two or more records.
● These values are repeated.

Composite key

Primary Key
A primary key is used to ensure that data in the specific column is unique.
 A column cannot have NULL values.
An entity can contain multiple keys, as we saw in the PERSON table.
The key which is most suitable from those lists becomes a primary key

SQL syntax:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY(ID,LastName)
);

Foreign Key
A foreign key is a column or group of columns in a relational database table that
provides a link between data in two tables. It is a column (or columns) that references a

column (most often the primary key) of another table .

An attribute or combination of attributes in one table whose values must either match
the primary key in another table.Every database relation is involving just two tables, a
parent table and a child table. Relational databases implement RI using primary keys
and foreign keys. The parent table contains the primary key and the child table
contains the foreign key which references (or "points" to, if you prefer) the parent
table.
EX:
• CREATE TABLE Persons (
personID int,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY(personID)
);
EX:
CREATE TABLE Orders(
OrderID int NOT NULL,
OrderNumber int,
ID int,
PRIMARY KEY(ID),
FOREIGN KEY(ID)REFERENCES Persons(personID)
);
 Topic :Relational Algebra and its operations
It is a procedural query language that takes relations as an input and returns
relations as an output. There are some basic operators which can be applied on
relations to producing the required results where as Relational calculus is a
non-procedural query language, and instead of algebra, it uses mathematical
predicate calculus
Operations
• Unary Relational Operations
• SELECT (symbol: σ)
• PROJECT (symbol: π)
• RENAME (symbol: ρ)
• Relational Algebra Operations From Set Theory
• UNION (υ)
• INTERSECTION ( ),
• DIFFERENCE (-)
• CARTESIAN PRODUCT ( x )
• JOINS

Selection operator (σ):

Selection operator is used to select tuples from a relation based on some
condition.
Syntax: σ (Cond)(Relation Name) σ is used for selection prediction
which may use connectors like: AND OR and NOT. These relational can use as
relational operators like =, ≠, ≥, <, >, ≤.
EX:Extract students whose age is greater than 18 from STUDENT relation
σ (AGE>18)(STUDEN)
EX:Selects tuples from student where stdname is ‘priya' and age is 45

σstdname = “priya" and age = 45(student)

 Projection Operator (∏):
Projection operator is used to project particular columns from a relation.
• Syntax: ∏(Column 1,Column 2….Column n)(Relation Name)
Ex:Extract ROLL_NO and stdNAME from STUDENT relation
∏(rollno,stdname)(STUDENT)
RESULT:
rollno stdname
1 RAM
2 RAMESH
3 SUJIT
4 SURESH

Rename(ρ):
Rename operator is used to give another name to a relation.
• Syntax: ρ(Relation2, Relation1)
To rename STUDENT relation to STUDENT1,
• we can use rename operator like:
• ρ(STUDENT1, STUDENT)

Set operations:
Union Operation:
Union (U): UNION is symbolised by ∪ symbol. It includes all tuples that are in
tables A or in B. It also eliminates duplicate tuples.

• For a union operation to be valid, the following conditions must hold −

• r, and s must have the same number of attributes.
• datatypes of their corresponding attributes must be same.
• Duplicate tuples are automatically eliminated.
• note :in left side table (r1 column names will appear)
• Notation − r U s r and s are the relation(table) names
Ex:
 r1(table1) r2(table2)

column
column1 column2 column1
2
1 1 1 3
1 2 1 1

o/p: r1 U r2

column 1 column 2
1 1
1 2
1 3

Set Intersection:
Suppose there are two tuples R and S. The set intersection operation contains all
tuples that are in both R & S.
It is denoted by intersection ∩.

​ Notation: R ∩ S
Ex:

r1(table1) r2(table2)

column1 column2 column1 s column2

1 1 1 3
1 2 1 1

Table r1– r2

column 1 column 2

1 1
 Set Difference (-)
• – Symbol denotes it. The result of A – B, is a relation which includes all
tuples that are in A but not in B.
• Ex:
•
Table r1 – r2

column 1 column 2

1 2

Cartesian Product(X) in DBMS

Cartesian Product in DBMS is an operation used to merge columns from two
relations. Generally, a cartesian product is never a meaningful operation when it
performs alone
Consider two relations STUDENT(SNO, FNAME, LNAME) and
DETAIL(ROLLNO, AGE)
Student
SNO FNAME LNAME
1 AB EF
2 CD GH

detail
ROLLNO AGE
5 18
9 21

• STUDENT x DETAIL
SNO FNAME LNAME ROLLNO AGE
1 AB EF 5 18
1 AB EF 9 21
2 CD GH 5 18
2 CD GH 9 21
 Joins in relational algebra
Join is an operation inDBMS that combines the row of two or more tables based on
related columns between them. The main purpose of Join is to retrieve the data from
multiple tables . It is denoted by ⨝.
TYPES:
Inner join—--Natural join,Theta join,Equi join
Outer join—--Left Outer Join,Right Outer Join,Full Outer Join

Inner Join
Natural Join:A natural join is the set of tuples of all combinations in R and S that are
equal on their common attribute names.It is denoted by ⋈.
A ⋈ column = column B

same column name num

Theta Join
Theta Join allows you to merge two tables based on the condition represented
by theta. Theta joins work for all comparison operators. It is denoted by symbol
θ. The general case of JOIN operation is called a Theta join.
Syntax:
A ⋈θ B
 EQUI Join
EQUI Join is done when a Theta join uses only the equivalence condition.
EQUI join is the most difficult operation to implement efficiently in an
RDBMS, and one reason why RDBMS have essential performance problems.
A ⋈ A.column 2 = B.column 2 (B)

Outer Join
An Outer Join doesn’t require each record in the two join tables to have a
matching record. In this type of join, the table retains each record even if
no other matching record exists.
Three types of Outer Joins are:

● Left Outer Join

● Right Outer Join
● Full Outer Join
Left Outer Join (A ⟕ B)
Left Outer Join returns all the rows from the table on the left even if no
matching rows have been found in the table on the right. When no matching
record is found in the table on the right, NULL is returned
SY:RELATION1 ⟕ RELATION 2
Right Outer Join ( A ⟖ B )

Right Outer Join returns all the columns from the table on the right even if no matching
rows have been found in the table on the left. Where no matches have been found in the table
on the left, NULL is returned. RIGHT outer JOIN is the opposite of LEFT JOIN
SY: RELATION1 RELATION 2
EX: A B

Full Outer Join ( A ⟗ B)

In a Full Outer Join , all tuples from both relations are included in the result, irrespective of
the matching condition.
SY:SY: RELATION1 RELATION 2

EX:A B

TOPIC:Relational calculus
Relational calculus is a non-procedural query language, and instead of algebra, it uses
mathematical predicate calculus.
• The relational calculus is not the same as that of differential and integral calculus in
mathematics but takes its name from a branch of symbolic logic termed as predicate
calculus.
 • When applied to databases, it is found in two forms. These are
• Tuple relational calculus(TRC)
• Domain relational calculus(DRC)

Tuple relational calculus(TRC)

• In the tuple relational calculus, you will have to find tuples for which a
predicate(condition) is true. The calculus is dependent on the use of tuple variables.
• Syntax: The basic syntax of TRC is as follows:
• { t | P(t) }
here t is a tuple variable and P(t) is a logical formula that describes the conditions
that the tuples in the result must satisfy. The curly braces {} are used to indicate that
the expression is a set of tuples.
P(t) may have various conditions logically combined with OR (∨), AND (∧),
NOT(¬).
It also uses quantifiers:
∃ t ∈ r (Q(t)) = ”there exists” a tuple in t in relation r such that predicate
Q(t) is true.
∀ t ∈ r (Q(t)) = Q(t) is true “for all” tuples in relation r.

• 'ex:Find the staffNo, fName, lName, position, DOB, salary, and branchNo of all staff earning
more than £10,000’,
NOTE :To specify the range of a tuple variable S as the Staff relation
LOGICALLY AND ,OR NOT
{S | Staff(S) ∧ S.salary > 10000

Domain Relational Calculus:

It is similar to Tuple Relational Calculus, where it makes a list of the attributes that are
to be chosen from the relations as per the conditions.
A Domain Relational Calculus (DRC) is a variable that comes in the range of the values of domain
(data types) of some columns (attributes).
{<a1,a2,a3,.....an> | P(a1,a2,a3,.....an)}

where a1,a2,…an are the attributes of the relation and P is the condition.

Find the loan number, branch, and amount of loans greater than or equal
to 10000 amount.
{t.loan number,branch,amount| t ∈ loan ∧ t[amount]>=10000}
 TOPIC:CONSTRAINT(key,domain,integrity,enti
ty constraints)
Constraints in SQL means we are applying certain conditions or restrictions on
the database
Constraints in SQL can be categorized into two :
1. Column Level Constraint:
Column Level Constraint is used to apply a constraint on a single column.
2. Table Level Constraint:
Table Level Constraint is used to apply a constraint on multiple columns.

The constraint is a mechanism used by oracle to prevent invalid data into the table

Domain Constraints
Domain Constraints are user-defined columns that help the user to enter
the value according to the data type.
Types:
1. NOT NULL:
we know that by default all the columns in a table allow null values. When aNOT
NULL constraint is enforced through either on a column or a set of columns in a
table, it will not allow null values.
Adding a constraint at the time of table creation:
sy:CREATE TABLE TableName (ColumnName1 datatype NOT NULL,
ColumnName2 datatype,…., ColumnNameN datatype);

ex:create table stu1(sno number(5) constraint nn1 not null, name char(10) not null,
address varchar2(10) not null);
2. Dropping a Constraint
 ALTER TABLE stu1 DROP CONSTRAINT nn1;

2. CHECK:
The check constraint specifies condition that each row must satisfy. These rules are
formed by logical expressions or Boolean expression.
Sy:CREATE TABLE TableName (ColumnName1 datatype CHECK
(ColumnName1 Condition), ColumnName2 datatype,…., ColumnNameN
datatype);

Ex: create table emp(eno number(5),sal number(8,2) constraint ch1 check(sal>500));

Syntax to apply check constraint on multiple columns:
​ CREATE TABLE TableName (ColumnName1 datatype, ColumnName2
datatype CHECK (ColumnName1 Condition AND ColumnName2
Condition),…., ColumnNameN datatype);

Syntax to apply check constraint on an existing table's column(alter)

​ ALTER TABLE TableName ADD CHECK (ColumnName Condition);

​ ex: ALTER TABLE student ADD CHECK ( Age <=15 );

3. DEFAULT:
When a row is inserted, it is not necessary that value for every column should not be
inserted. SQL offers the provision to specify default values for columns.
sy:CREATE TABLE TableName (ColumnName1 datatype DEFAULT Value,
ColumnName2 datatype,…., ColumnNameN datatype);

Ex: create table emp(eno number(5), sal number(8,2) default 5000);

Key constraints
There must be at least one minimal subset of attributes in the relation, which
can identify a tuple uniquely. This minimal subset of attributes is called key for
that relation. If there are more than one such minimal subsets, these are called
candidate keys.
1. UNIQUE:
Duplicate values are not allowed in the columns to which the UNIQUE
constraint is applied.
○ The column with the unique constraint will always contain a unique
value.
○ This constraint can be applied to one or more than one column of a
 table, which means more than one unique constraint can exist on a
single table.
○ Using the UNIQUE constraint, you can also modify the already
created tables.

Syntax to apply the UNIQUE constraint on a single column:

​ sy:CREATE TABLE TableName (ColumnName1 datatype UNIQUE,

ColumnName2 datatype,…., ColumnNameN datatype);
ex: create table stu2(sno number(5) constraint un1 unique, name char(20) not null,
address varchar2(10));

→Syntax to apply the UNIQUE constraint on more than one column:

​ CREATE TABLE TableName (ColumnName1 datatype, ColumnName2
datatype,…., ColumnNameN datatype, UNIQUE (ColumnName1, ColumnName 2));
→Alter to add unique key
sy:ALTER TABLE TableName ADD UNIQUE (ColumnName);
ex:alter table stu2 add unique(address); -

2. PRIMARY KEY:
Primary Key is a field which uniquely identifies each row in the table.
The primary key constraint avoids duplication of rows and does not allow null
values.
A table can have only one primary key.
If a primary key constraint is assigned to a combination of columns. It is said to be
a composite primary key which contains a maximum of 16 columns.
PRIMARY KEY Constraint is a combination of NOT NULL and Unique
constraints.

sy:CREATE TABLE TableName (ColumnName1 datatype PRIMARY KEY,

ColumnName2 datatype,…., ColumnNameN datatype);

ex:create table stu3(sno number(5) constraint pk1 primary key, name char(10));
To add a constraint:
ALTER TABLE TableName ADD PRIMARY KEY (ColumnName);
ex:
alter table stu3 add primary key(name);

Referential Integrity Constraints

Referential integrity constraints work on the concept of Foreign Keys.
. FOREIGN KEY CONSTRAINT:
To establish a parent child relationship between two tables having a same column.
To implement this we should define the column in the parent table as a primary
key and the same column in the child table as a foreign key referencing to the
corresponding parent entity.
A foreign key is used for referential integrity.
sy:CREATE TABLE tablename(ColumnName1 Datatype(SIZE) PRIMARY KEY,
ColumnNameN Datatype(SIZE), FOREIGN KEY( ColumnName )
REFERENCES PARENT_TABLE_NAME(Primary_Key_ColumnName));

ex:1. CREATE TABLE employee (Emp_ID INT PRIMARY KEY, Emp_Name VARCHAR
(40), Emp_Salary VARCHAR (40));

2. CREATE TABLE department(Dept_ID INT PRIMARY KEY, Dept_Name

VARCHAR(40), Emp_ID INT NOT NULL, FOREIGN KEY(Emp_ID) REFERENCES
employee(Emp_ID));

Topic:Basic sql
Introduction to SQL, Advanced SQL

Overview of the SQL Query Language:

o SQL stands for Structured Query Language. It is used for storing and managing data
in relational database management system (RDMS). o It is a standard language for
Relational Database System. It enables a user to create, read, update and delete
relational databases and tables.
o All the RDBMS like MySQL, Informix, Oracle, MS Access and SQL Server use SQL
as their standard database language.
o SQL allows users to query the database in a number of ways, using English-like
statements.

Rules:

SQL follows the following rules:

o Structure query language is not case sensitive. Generally, keywords of SQL are
written in uppercase. o Statements of SQL are dependent on text lines. We can use a
single SQL statement on one or multiple text line.
 o Using the SQL statements, you can perform most of the actions in a database. o
SQL depends on tuple relational calculus and relational algebra.

SQL process:

o When an SQL command is executing for any RDBMS, then the system figure out the
best way to carry out the request and the SQL engine determines that how to interpret
the task.
o In the process, various components are included. These components can be
optimization Engine, Query engine, Query dispatcher, classic, etc.
o All the non-SQL queries are handled by the classic query engine, but SQL query
engine won't handle logical files.

Characteristics of SQL

SQL is easy to learn.

o SQL is used to access data from relational database management

systems. O

SQL can execute queries against the database. o SQL is used to describe

the data.

o SQL is used to define the data in the database and manipulate it when

needed.

o SQL is used to create and drop the database and table.

 o SQL is used to create a view, stored procedure, function in a database. o

o SQL allows users to set permissions on tables, procedures, and views.

Topic:SQL DATA DEFINATION :

The set of relations in a database must be specified to the system by means of a
data-definition language (DDL). The SQL DDL allows specification of not only a set
of relations, but also information about each relation, including:
• The schema for each relation.
• The types of values associated with each attribute.

• The integrity constraints.

• The set of indices to be maintained for each relation.

Topic:. Basic Types

The SQL standard supports a variety of built-in types, including:
• char (n): fixed-length character string with user-specified length n.
The full form, character, can be used instead.
• varchar(n): A variable-length character string with
user-specified maximum length n. The full form, character varying, is equivalent.
 int: An integer (a finite subset of the integers that is machine dependent). The
full form, integer, is equivalent.
• smallint: A small integer (a machine-dependent subset of the integer type).
• numeric(p, d): A fixed-point number with user-specified precision.
• float(n): A floating-point number, with precision of at
least n digits.
Each type may include a special value called the null value. A null value indicates an
absent value that may exist but be unknown or that may not exist at all.

TOPIC:. Basic Schema Definition

We define an SQL relation by using the
create table command.
The following command creates a relation department in the database.
create table department (dept_name varchar (20), building varchar (15), budget
numeric (12,2), primary key (dept_name));
The create table command also specifies that the dept_name attribute is the primary
key of the
department relation.
The general form of the create table command is:
create table r
(A1 D2,
A2 D2,
...,
An Dn,
{integrity-constraint 1},
...,
{integrity-constraint k });
where r is the name of the relation, each Ai is the name of an attribute in the schema of
relation r, and Di is the domain of attribute Ai; that is, Di specifies the type of attribute
Ai along with optional constraints that restrict the set of allowed values for Ai.
DROP: The DROP command is used to remove or eliminate an existing relation in the
database.
ALTER: The ALTER command is used to modify the existing relation in the database.
TRUNCATE: The TRUNCATE command is used to eliminate all the instances of the table,
thereby it preserves the outer structure of the table/relation.
RENAME: The RENAME command is used with ALTER command either to modify the
name of relation or its attributes.

TOPIC:Basic Structure of SQL Queries:

The fundamental structure of SQL queries includes three clauses that are select, from, and
where clause. What we want in the final result relation is specified in the select clause. Which
relations we need to access to get the result is specified in from clause. How the relation must
be operated to get the result is specified in the where clause.

select A1, A2, . . . , An

from r1, r2, . . . , rm

where P;

▪ In the select clause, you have to specify the

attributes that you want to see in the result
relation  In the from clause, you have to
 specify the list of relations that has to be
accessed for evaluating the query.
▪ In the where clause involves a predicate that
includes attributes of the relations that we have
listed in the from clause.
 Queries on Single Relation

Consider that we have a relation ‘instructor’ with the attributes instr_id, name,
dept_name, and salary. Now we want the names of all the instructors along with their
corresponding department names.

The SQL query we would structure to get a result relation with instructor’s names along
with their department name. select name from instructor;

Queries on Multiple Relation

The SQL queries often need to access multiple relations from the data set in order to get the
required result. Let us take an example we have two relations instructor and department.

Now, if you want to retrieve the names of all the instructors along with their department
names and the corresponding department building. We will get the instructor’s name and
department name in the instructor relation but building name in the department relation. So
the query would be:
 select name, instructor.dept_name, building

from instructor, department

where instructor.dept_name= department.dept_name;

Here department name of each tuple of instructor relation will be matched with the
department name of each tuple of department relation.

Topic:Modification of the Database:

There are lots of situations where we need to alter and need to update existing data. Let’s
discuss one by one.
1. ALTER Command :
ALTER is an SQL command used in Relational DBMS and is a Data Definition
Language (DDL) statement. ALTER can be used to update the table’s structure in the
database (like add, delete, drop indexes, columns, and constraints, modify the attributes
of the tables in the database).
ALTER command is most commonly used to improve SQL SELECT queries by adding
and removing indexes.
SYNTAX :
Adding a column to the existing table –
ALTER TABLE tableName ADD columnName datatype;
Example –
ALTER TABLE Student ADD STNO NUMBER(10);

SYNTAX :
Removing column from existing table –
ALTER TABLE tableName DROP COLUMN columnName;
Example –
ALTER TABLE Student

Changing column name in the existing table –

ALTER TABLE tableName RENAME COLUMN olderName TO newName;
Example –
ALTER TABLE student rename column stdno to stdid
 2)UPDATE: is an SQL command used in Relational DBMS and is a Data Manipulation
Language
(DML) statement. It is used to manipulate the data of any existing column. But can’t
change the table’s definition.
SYNTAX :
UPDATE table_name SET column1= value1, column2 = value2, ... WHERE condition;
Example – UPDATE student SET contact = 91111. WHERE name =ashu;
Before : Student table

ALTER Command UPDATE Command

It is a Data Manipulation
It is a Data Definition Language (DDL).
Language (DML).

It affects the structure of table. It affects the data of the table.

It is used to add, delete, drop indexes,

It is used to UPDATE existing
columns, and constraints, modify the
data in the database
attributes of the tables in the database

It gives the specified values

It gives null value in tuple as default after
in the table as specified in the
initializing.
command.

Example – Table name, functions, table Changes data entry of specified

structure, etc. column or row.

***Topic:SQL COMMANDS
SQL contains different types of commands
DDL (Data Definition Language) Commands
DML(Data Manipulation Language) Commands
TCL(Transaction Control Language) Commands
DCL(Data Control Language) Commands

DDL (Data Definition Language) Commands

The Data Definition Languages (DDL) Commands are used to create a table structure,
modify the table structure and delete the table structure.

DDL changes the structure of the table like creating a table, deleting a table, altering a table,
etc.
 All the command of DDL are auto-committed that means it permanently save all the changes
in the database.

I) DDL Commands:
1. CREATE TABLE
The CREATE TABLE command is used to create a new table
Syntax: CREATE TABLEtablename (column_name1 data_ type (size)
constraint, column_name2 data_ type (size) constraints, …);
Example:
create table student (sname varchar2(20) , rollno number(10) ,dob date );
create table student (sname varchar2(20) not null, rollno number(10) not null,dob date );

2. The ALTER TABLE Statement

The ALTER TABLE statement is used to modify structure of an existing table.
There are three different syntaxes
a. ALTER TABLE ADD
This command is used to add a new column in to an existing table syntax:
SY:ALTER TABLE table_nameADD(column_namedata_type(size) ,
column_namedata_type,…) ;
EX: alter table student add(address varchar2(20),phone_no
number(10));
b. ALTER TABLE MODIFY
This command is used to change the data type and size of an existing column in
to an existing table syntax:
SY:ALTER TABLE table_nameMODIFY
(old_column_namenew_data_type(new_size),
old_column_namenew_data_type(new_size),….);
EX:alter table student modify (address varchar2(40));
c. ALTER TABLE DROP
To delete a column in a table, use the following syntax
SY:ALTER TABLE table_name DROP COLUMN column_name1,… ;
EX:alter table student drop phone_no;

3. DROP:
This command is used to delete the structure of the table as well as
records in the relation.
Syntax :DROP TABLEtable_name;
 Ex: drop table student;

4.RENAME
RENAME command is used to rename the table.
Rename old table name to new table name
EX: Rename student to student1;
6.TRUNCATING TABLE:
Truncate command will delete all the records permanently in a specified table but
structure of the table will not be deleted.
– Syntax: TRUNCATE TABLE TableName;
Eg:TRUNCATE TABLE student;

II COMMANDS IN DML:
○ DML commands are used to modify the database. It is responsible
for all form of changes in the database.
○ 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.

1. INSERT INTO Statement

The INSERT INTO statement is used to insert new records in a table. There
are different forms of insert commands are used to insert a records into a
table.
Syntax
The first form of insert is used to insert values for all columns in a table.
sy1:INSERT INTO table_name VALUES (value1,value2,value3,...);
The second form of insert is used to insert values for some columns of
a record in a table.
EX: INSERT INTO STU VALUES(‘JHJ’,99);
SY2: INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);
2. SELECT:
The select command is used to retrieve records from the tables.
This type is used to display the set of fields or all the fields of the selected or
all records in a table.
 SY:a. Syntax: SELECT * FROM <table_name> [WHERE condition];
Ex: select * from student;
b. Syntax: SELECT set of fields FROM table_name [WHERE condition];
Ex: select sno ,sname,address from student where address=’BVRM’;
3. UPDATE:
The update command is used to update the content of a table.
Syntax:
Syntax 1
– UPDATE TableName SET ColumnName1=Expression1,
ColumnName2=Expression2,….;
Syntax 2
– UPDATE TableName SET ColumnName1=Expression1,
ColumnName2=Expression2,… WHERE Condition;
Ex: UPDATE student SET total = sub1+sub2+sub3;
UPDATE student SET total = sub1+sub2+sub3 WHEREsno = 10;
4. DELETE:
The delete command is used to delete all the records or selected records in
a table. But the structure of the table remains.
Syntax 1
– DELETE FROM TableName;
EX: DELETE from student;
Syntax 2
– DELETE FROM TableName WHERE Condition;
EX:DELETE from student WHERE address=’BVRM’;

DCL COMMANDS:
DCL commands are used to grant and take back authority from any
database user.
Two types of DCL commands are
1. Grant
2. Revoke

1. GRANT
SQL Grant command is used to grant a privileges on the database
objects to the users. The syntax for the GRANT command is:
GRANT privilege_name ON object_name TO user_name ;
Here, privilege_name: is the access right or privilege granted to the user.
object_name: is the name of the database object like table, view etc.,
user_name: is the name of the user
(a) GRANT SELECT ON employee TO user1 ;
This command grants a SELECT permission on employee table to user1.
(b) GRANTSELECT, INSERT, UPDATE, DELETEONemployee TOSCOTT;
The above statement will GRANT SELECT, INSERT, UPDATE and DELETE privileges to
SCOTT user on employee table.
 2. REVOKE
The revoke command removes user privileges to the database objects.
• The syntax for the REVOKE command is:
REVOKE privilege_name ON object_name FROM User_name ;
For Example:
REVOKE SELECT ON employee FROM user1;
This command will revoke a SELECT privilege on employee table from user1.
Suppose we want to revoke SELECT, INSERT, UPDATE and DELETE privileges from
‘SCOTT’ user on employee table, then we have to execute the following statement;
REVOKE SELECT,INSERT,UPDATE,DELETE ON employee FROM SCOTT;

TCL COMMANDS
Transaction Control (TCL) statements are used to manage the changes made
by DML statements.
•1.COMMIT - To make the changes permanent in the database .The
COMMIT command saves all the transactions to the database since the
last COMMIT or ROLLBACK command.
Syntax:

COMMIT;

2. SAVEPOINT - saving point in a transaction to which you can later roll back .creates
points within the groups of transactions in which to ROLLBACK. A SAVEPOINT is
a point in a transaction in which you can roll the transaction back to a certain point
without rolling back the entire transaction.

Syntax for Savepoint command:

SAVEPOINT SAVEPOINT_NAME;

3 ROLLBACK - Restore database to original since the last COMMIT.If any error occurs
with any of the SQL grouped statements, all changes need to be aborted. The process
of reversing changes is called rollback. This command can only be used to undo
transactions since the last COMMIT or ROLLBACK command was issued
sy:ROLLBACK;