CHAPTER 1: CREATING RELATIONAL DATABASE
❖ Basic Operators of Relational Algebra
1. SELECT (σ) : Select operator is used to select tuples from a
relation based on some condition.
SYNTAX: σ (Condition) <table name>
EXAMPLE: ∏(ename)σ (empno=6)EMPLOYEE
2. PROJECT (∏): Project operator is used to project particular
columns from a relation.
SYNTAX: ∏ (<attributes>)<table_name>
EXAMPLE: ∏ (Rollno,Name) σ (Rollno=5)Student
3. UNION (U): Union operator combines all the tuples of two
desired tables.
SYNTAX: <table1> U <table2>
EXAMPLE: Employee U Department
4. INTERSECTION (∩): Intersection operator only includes the
tuples which are in both the tables.
SYNTAX: <table1> ∩ <table2>
Example Employee ∩ Department
5. SET DIFFERENCE (–): Set difference operator includes the
tuples of one table which are not present in the other table.
SYNTAX: <table1> – <table2>
Example: Employee – Department
6. CARTESIAN PRODUCT (x): Cartesian product operator is
used to join two relations.
SYNTAX: <table1> x <table2>
Example: Employee x Department
7. JOIN (✉): Join operator pair two tuples of different relation.
SYNTAX: <table1> ✉ <table2>
Example: Employee ✉ Department
8. RENAME (ρ()): Rename operator is used to rename the
table.
SYNTAX: ρ(<table_name> , <new_table_name>)
Example: ρ(Employee,Emp)
❖ Introduction To SQL
There are 4 types of SQL Languages:
➢ DDL (Data Definition Language)
➢ DML (Data Manipulation Language)
➢ DCL (Data Control Language)
➢ TCL (Transaction Control Language)
● DDL Commands
1. CREATE: It is used to create table.
SYNTAX:
CREATE TABLE <table_name>
(
<attritube_name_1> <datatype (size)>
<constraint>,
<attritube_name_1> <datatype (size)>
<constraint>,
<attritube_name_n> <datatype (size)>
<constraint>
);
Example:
CREATE TABLE Student
(
Rollno number(5) primary key,
Name varchar2(10) not null,
Phone_no number(10),
Addr varchar2(10),
);
2. ALTER: It has 5 types.
❏ ADD ATTRIBUTE
SYNTAX: ALTER TABLE <Table_name>
ADD (<attribute_name> datatype(size));
Example: ALTER TABLE Student
ADD ( Age number(5));
❏ ADD CONSTRAINT
SYNTAX: ALTER TABLE <Table_name>
ADD (<constraint> <attribute_name>);
Example: ALTER TABLE Student
ADD (UNIQUE (Phone_no));
❏ MODIFY DATATYPE
SYNTAX: ALTER TABLE <Table_name>
MODIFY (<attritube_name> <new
datatype(size)>);
Example: ALTER TABLE Student
MODIFY ( Addr varchar2(15));
❏ RENAME COLUMN
SYNTAX: ALTER TABLE <Table_name>
RENAME COLUMN <column_name> to <new
column_name>;
Example: ALTER TABLE Student
RENAME COLUMN Name to St_name;
❏ DROP COLUMN
SYNTAX: ALTER TABLE <Table_name>
DROP (<attribute_name>);
EXAMPLE: ALTER TABLE Student
DROP (Age);
● DML Commands
1. INSERT: To insert data into the table. There are 2 ways of
Inserting data.
❏ STATIC INSERTION
SYNTAX: INSERT INTO <table_name>
VALUES (val1, val2, val3, valn);
Example: INSERT INTO Student
VALUES (1,’Sam’,9875446872,’Thane’);
❏ DYNAMIC INSERTION
SYNTAX: INSERT INTO <table_name>
VALUES
(&Rollno,’&St_name’,&Phone_no,’&Addr’);
You can insert data dynamically.
2. DELETE: It is used to delete data from the table.
SYNTAX: DELETE FROM <Table_name>
WHERE (Condition);
Example: DELETE FROM Student
WHERE Rollno=1;
3. UPDATE: It is used to update data in the table.
SYNTAX: UPDATE <table_name>
SET <column_name>=<expression>
WHERE <column_name>=<expression>;
Example: UPDATE Student
SET Addr=’Vashi’;
WHERE Rollno=2;
4. SELECT: It is used for selection of data from the table.
SYNTAX: SELECT <attribute1>….<attribute n> FROM
<Table_name>
WHERE (Condition);
Example: SELECT St_name,Rollno FROM Student
Where Rollno=2;
SELECT * FROM Student
WHERE Rollno=2; //For Selection of
all attributes.
● TCL Commands
1. SAVEPOINT: Marks and saves the current point of
transaction.
SYNTAX: SAVEPOINT <savepoint_name>;
Example: SAVEPOINT empsave1;
2. ROLLBACK: Restores the database to last committed
state.
SYNTAX: ROLLBACK To SAVEPOINT
<savepoint_name>;
Example: ROLLBACK To SAVEPOINT empsave1;
3. COMMIT: Ends the current transaction and makes
permanent changes to transaction.
SYNTAX: COMMIT;
