lOMoARcPSD|49921704

Unit 2: Relational Model Concepts and SQL Overview

Database Design and Management (Anna University)

Scan to open on Studocu

Studocu is not sponsored or endorsed by any college or university

Downloaded by kaleeswaran (kaleeswaranmmcas@gmail.com)
 lOMoARcPSD|49921704

UNIT II
RELATIONAL MODEL AND SQL
Relational model concepts --Integrity constraints --SQL Data manipulation –SQL Data
definition –Views --SQL programming.
2.1 Relational Model concept
Relational model can represent as a table with columns and rows. Each row is known as a
tuple. Each table of the column has a name or attribute.
Domain: It contains a set of atomic values that an attribute can take.
Attribute: It contains the name of a column in a particular table. Each attribute Ai must have a
domain, dom(Ai)
Relational instance: In the relational database system, the relational instance is represented by a
finite set of tuples. Relation instances do not have duplicate tuples.
Relational schema: A relational schema contains the name of the relation and name of all
columns or attributes.
Relational key: In the relational key, each row has one or more attributes. It can identify the row
in the relation uniquely.
Example: STUDENT Relation

NAME ROLL_NO PHONE_NO ADDRESS AGE

Ram 14795 7305758992 Noida 24
Shyam 12839 9026288936 Delhi 35
Laxman 33289 8583287182 Gurugram 20
Mahesh 27857 7086819134 Ghaziabad 27
Ganesh 17282 9028 9i3988 Delhi 40
 In the given table, NAME, ROLL_NO, PHONE_NO, ADDRESS, and AGE are the
attributes.
 The instance of schema STUDENT has 5 tuples.
 t3 = <Laxman, 33289, 8583287182, Gurugram, 20>
Properties of Relations
 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

Downloaded by kaleeswaran (kaleeswaranmmcas@gmail.com)

 lOMoARcPSD|49921704

 Order of tuple can have a different sequence

Relational Algebra
Relational algebra is a procedural query language. It gives a step by step process to obtain the
result of the query. It uses operators to perform queries.
Types of Relational operation

1. Select Operation:
 The select operation selects tuples that satisfy a given predicate.
 It is denoted by sigma (σ).
Notation: σ p(r)
Where: σ is used for selection prediction
r is used for relation
p is used as a propositional logic formula which may use connectors like: AND OR and NOT.
These relational can use as relational operators like =, ≠, ≥, <, >, ≤.

BRANCH_NAME LOAN_NO AMOUNT

Downtown L-17 1000
Redwood L-23 2000
Perryride L-15 1500
Downtown L-14 1500
Mianus L-13 500
Roundhill L-11 900
Perryride L-16 1300

Input:

Downloaded by kaleeswaran (kaleeswaranmmcas@gmail.com)

 lOMoARcPSD|49921704

σ BRANCH_NAME="perryride" (LOAN)

Output:

BRANCH_NAME LOAN_NO AMOUNT

Perryride L-15 1500
Perryride L-16 1300
2. Project Operation:
This operation shows the list of those attributes that we wish to appear in the result. Rest of the
attributes are eliminated from the table.
It is denoted by ∏.
Notation: ∏ A1, A2, An (r)
Where A1, A2, A3 is used as an attribute name of relation r.
Example: CUSTOMER RELATION

NAME STREET CITY

Jones Main Harrison
Input:
Smith North Rye
Hays Main Harrison ∏ NAME, CITY (CUSTOMER)
Curry North Rye Output:
Johnson Alma Brooklyn
Brooks Senator Brooklyn
NAME CITY
Jones Harrison
Smith Rye
Hays Harrison
Curry Rye
Johnson Brooklyn
Brooks Brooklyn
3. Union Operation:
 Suppose there are two tuples R and S. The union operation contains all the tuples that are
either in R or S or both in R & S.
 It eliminates the duplicate tuples. It is denoted by ∪.
Notation: R ∪ S
A union operation must hold the following condition:

Downloaded by kaleeswaran (kaleeswaranmmcas@gmail.com)

 lOMoARcPSD|49921704

 R and S must have the attribute of the same number.

 Duplicate tuples are eliminated automatically.
Example:
DEPOSITOR RELATION

CUSTOMER_NAME ACCOUNT_NO
Johnson A-101
Smith A-121
Mayes A-321
Turner A-176
Johnson A-273
Jones A-472
Lindsay A-284

BORROW RELATION

CUSTOMER_NAME LOAN_NO
Jones L-17
Smith L-23
Hayes L-15
Jackson L-14
Curry L-93
Smith L-11
Williams L-17
Input:
∏ CUSTOMER_NAME (BORROW) ∪ ∏ CUSTOMER_NAME (DEPOSITOR)
Output:

CUSTOMER_NAME
Johnson
Smith
Hayes
Turner
Jones
Lindsay
Jackson

Downloaded by kaleeswaran (kaleeswaranmmcas@gmail.com)

 lOMoARcPSD|49921704

Curry
Williams
Mayes
4. 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
Example: Using the above DEPOSITOR table and BORROW table
Input:
∏ CUSTOMER_NAME (BORROW) ∩ ∏ CUSTOMER_NAME (DEPOSITOR)
Output:

CUSTOMER_NAME
Smith
Jones

5. Set Difference:
Suppose there are two tuples R and S. The set intersection operation contains all tuples that are
in R but not in S. It is denoted by intersection minus (-).
Notation: R - S
Example: Using the above DEPOSITOR table and BORROW table
Input:
∏ CUSTOMER_NAME (BORROW) - ∏ CUSTOMER_NAME (DEPOSITOR)
Output:
CUSTOMER_NAME
Jackson
Hayes
Williams
Curry

6. Cartesian product

Downloaded by kaleeswaran (kaleeswaranmmcas@gmail.com)

 lOMoARcPSD|49921704

The Cartesian product is used to combine each row in one table with each row in the other
table. It is also known as a cross product. It is denoted by X.
Notation: E X D

Example:

EMPLOYEE

EMP_ID EMP_NAME EMP_DEPT

1 Smith A
2 Harry C
3 John B

DEPARTMENT

DEPT_NO DEPT_NAME
A Marketing
B Sales
C Legal
Input:
EMPLOYEE X DEPARTMENT
Output:

EMP_ID EMP_NAME EMP_DEPT DEPT_NO DEPT_NAME

1 Smith A A Marketing
1 Smith A B Sales
1 Smith A C Legal
2 Harry C A Marketing
2 Harry C B Sales
2 Harry C C Legal
3 John B A Marketing
3 John B B Sales
3 John B C Legal
7. Rename Operation:
The rename operation is used to rename the output relation. It is denoted by rho (ρ).
Example: We can use the rename operator to rename STUDENT relation to STUDENT1.
ρ (STUDENT1, STUDENT)

Downloaded by kaleeswaran (kaleeswaranmmcas@gmail.com)

 lOMoARcPSD|49921704

8. Join Operations:
A Join operation combines related tuples from different relations, if and only if a given join
condition is satisfied. It is denoted by ⋈.
Example:
EMPLOYEE

EMP_CODE EMP_NAME
101 Stephan
102 Jack
103 Harry

SALARY

EMP_CODE SALARY
101 50000
102 30000
103 25000

Operation: (EMPLOYEE ⋈ SALARY)

EMP_CODE EMP_NAME SALARY

101 Stephan 50000
102 Jack 30000
103 Harry 25000

Types of Join operations:

Downloaded by kaleeswaran (kaleeswaranmmcas@gmail.com)

 lOMoARcPSD|49921704

1. 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 ⋈.

Example: Let's use the above EMPLOYEE table and SALARY table:

Input: ∏EMP_NAME, SALARY (EMPLOYEE ⋈ SALARY)

Output:

EMP_NAME SALARY
Stephan 50000
Jack 30000
Harry 25000
.

2. Outer Join:

The outer join operation is an extension of the join operation. It is used to deal with missing
information

Example:

EMPLOYEE

EMP_NAME STREET CITY

Ram Civil line Mumbai

Downloaded by kaleeswaran (kaleeswaranmmcas@gmail.com)

 lOMoARcPSD|49921704

Shyam Park street Kolkata

Ravi M.G. Street Delhi
Hari Nehru nagar Hyderabad

FACT_WORKERS

EMP_NAME BRANCH SALARY

Ram Infosys 10000
Shyam Wipro 20000
Kuber HCL 30000
Hari TCS 50000

Input: (EMPLOYEE ⋈ FACT_WORKERS)

Output:

EMP_NAME STREET CITY BRANCH SALARY

Ram Civil line Mumbai Infosys 10000
Shyam Park street Kolkata Wipro 20000
Hari Nehru nagar Hyderabad TCS 50000

An outer join is basically of three types:

1. Left outer join

2. Right outer join
3. Full outer join

a. Left outer join:

 Left outer join contains the set of tuples of all combinations in R and S that are equal on
their common attribute names.
 In the left outer join, tuples in R have no matching tuples in S.
 It is denoted by ⟕.

Example: Using the above EMPLOYEE table and FACT_WORKERS table

Input: EMPLOYEE ⟕ FACT_WORKERS

EMP_NAME STREET CITY BRANCH SALARY

Ram Civil line Mumbai Infosys 10000
Shyam Park street Kolkata Wipro 20000

Downloaded by kaleeswaran (kaleeswaranmmcas@gmail.com)

 lOMoARcPSD|49921704

Hari Nehru street Hyderabad TCS 50000

Ravi M.G. Street Delhi NULL NULL
b. Right outer join:
 Right outer join contains the set of tuples of all combinations in R and S that are equal on
their common attribute names.
 In right outer join, tuples in S have no matching tuples in R.
 It is denoted by ⟖.
Example: Using the above EMPLOYEE table and FACT_WORKERS Relation
Input: EMPLOYEE ⟖ FACT_WORKERS
Output:

EMP_NAME BRANCH SALARY STREET CITY

Ram Infosys 10000 Civil line Mumbai
Shyam Wipro 20000 Park street Kolkata
Hari TCS 50000 Nehru street Hyderabad
Kuber HCL 30000 NULL NULL
c. Full outer join:
 Full outruner join is like a left or right join except that it contains all rows from both
tables.
 In full outer join, tuples in R that have no matching tuples in S and tuples in S that have
no matching tuples in R in their common attribute name.
 It is denoted by ⟗.
Example:
Using the above EMPLOYEE table and FACT_WORKERS table
Input: EMPLOYEE ⟗ FACT_WORKERS

Output:

EMP_NAME STREET CITY BRANCH SALARY

Ram Civil line Mumbai Infosys 10000
Shyam Park street Kolkata Wipro 20000
Hari Nehru street Hyderabad TCS 50000
Ravi M.G. Street Delhi NULL NULL
Kuber NULL NULL HCL 30000
3. Equi join:

10

Downloaded by kaleeswaran (kaleeswaranmmcas@gmail.com)

 lOMoARcPSD|49921704

It is also known as an inner join. It is the most common join. It is based on matched data as per
the equality condition. The equi join uses the comparison operator (=).
Example:
CUSTOMER RELATION

CLASS_ID NAME
1 John
2 Harry
3 Jackson

PRODUCT

PRODUCT_ID CITY
1 Delhi
2 Mumbai
3 Noida
Input: CUSTOMER ⋈ PRODUCT
Output:

CLASS_ID NAME PRODUCT_ID CITY

1 John 1 Delhi
2 Harry 2 Mumbai
3 Harry 3 Noida

Relational Calculus

In contrast to Relational Algebra, Relational Calculus is a non-procedural query language, that is,
it tells what to do but never explains how to do it.

Types of Relational calculus:

Relational calculus exists in two forms −

11

Downloaded by kaleeswaran (kaleeswaranmmcas@gmail.com)

 lOMoARcPSD|49921704

1. Tuple Relational Calculus (TRC)

It is a non-procedural query language which is based on finding a number of tuple variables also
known as range variable for which predicate holds true.
It describes the desired information without giving a specific procedure for obtaining that
information. The tuple relational calculus is specified to select the tuples in a relation.
In TRC, filtering variable uses the tuples of a relation. The result of the relation can have one or
more tuples.
Notation:
A Query in the tuple relational calculus is expressed as following notation
{T | P (T)} or {T | Condition (T)}
Where
T is the resulting tuples
P(T) is the condition used to fetch T.
For example:
{T.name | Author(T) AND T.article = 'database' }
Output: This query selects the tuples from the AUTHOR relation. It returns a tuple with 'name'
from Author who has written an article on 'database'.
TRC (tuple relation calculus) can be quantified. In TRC, we can use Existential (∃) and
Universal Quantifiers (∀).
Universal Quantifiers: The universal quantifier denoted by ∀ is read as for all which means that
in a given set of tuples exactly all tuples satisfy a given condition.

12

Downloaded by kaleeswaran (kaleeswaranmmcas@gmail.com)

 lOMoARcPSD|49921704

Existential Quantifiers: The existential quantifier denoted by ∃ is read as for all which means
that in a given set of tuples there is at least one occurrences whose value satisfy a given
condition.
For example:
{ R| ∃T ∈ Authors(T.article='database' AND R.name=T.name)}
Output: This query will yield the same result as the previous one.
2. Domain Relational Calculus (DRC)
The second form of relation is known as Domain relational calculus. In domain relational
calculus, filtering variable uses the domain of attributes.
Domain relational calculus uses the same operators as tuple calculus. It uses logical connectives
∧ (and), ∨ (or) and ┓ (not).

It uses Existential (∃) and Universal Quantifiers (∀) to bind the variable. The QBE or Query by
example is a query language related to domain relational calculus.
Notation: { a1, a2, a3, ..., an | P (a1, a2, a3, ... ,an)}
Where
a1, a2 are attributes
P stands for formula built by inner attributes
For example: {< article, page, subject > | ∈ javatpoint ∧ subject = 'database'}
Output: This query will yield the article, page, and subject from the relational javatpoint, where
the subject is a database.

CONSTRAINTS
Constraints enforce limits to the data or type of data that can be inserted/updated/deleted from a
table.
The whole purpose of constraints is to maintain the data integrity during an update/delete/insert
into a table.
Types of constraints
 NOT NULL
 UNIQUE
 DEFAULT
 CHECK

13

Downloaded by kaleeswaran (kaleeswaranmmcas@gmail.com)

 lOMoARcPSD|49921704

 Key Constraints – PRIMARY KEY, FOREIGN KEY

 Domain constraints
 Mapping constraints
NOT NULL:
NOT NULL constraint makes sure that a column does not hold NULL value. When we don’t
provide value for a particular column while inserting a record into a table, it takes NULL value
by default.
By specifying NULL constraint, we can be sure that a particular column(s) cannot have NULL
values.
Example:
CREATE TABLE STUDENT (ROLL_NO INT NOT NULL, STU_NAME VARCHAR (35) NOT
NULL, STU_AGE INT NOT NULL, STU_ADDRESS VARCHAR (235), PRIMARY KEY
(ROLL_NO));

Specify the NULL constraint for already existing table

In the above section we learnt how to specify the NULL constraint while creating a table.
However, we can specify this constraint on already present table also. For this we need to use
ALTER TABLE statement.
ALTER TABLE STUDENTS

MODIFY STU_ADDRESS VARCHAR (235) NOT NULL;

After this STU_ADDRESS column will not accept any null values.
UNIQUE:
UNIQUE Constraint enforces a column or set of columns to have unique values. If a column has
a unique constraint, it means that particular column cannot have duplicate values in a table.
CREATE TABLE STUDENT (ROLL_NO INT NOT NULL, STU_NAME VARCHAR (35) NOT
NULL UNIQUE, STU_AGE INT NOT NULL, STU_ADDRESS VARCHAR (35) UNIQUE,

PRIMARY KEY (ROLL_NO));

MySQL:
CREATE TABLE STUDENTS (ROLL_NO INT NOT NULL, STU_NAME VARCHAR (35)
NOT NULL, STU_AGE INT NOT NULL, STU_ADDRESS VARCHAR (35),
UNIQUE(STU_NAME), PRIMARY KEY (ROLL_NO));

Naming of UNIQUE Constraint:

MySQL / SQL Server / MS Access / Oracle:

14

Downloaded by kaleeswaran (kaleeswaranmmcas@gmail.com)

 lOMoARcPSD|49921704

CREATE TABLE STUDENTS (ROLL_NO INT NOT NULL, STU_NAME VARCHAR (35) NOT
NULL, STU_AGE INT NOT NULL, STU_ADDRESS VARCHAR (35), CONSTRAINT stu_Info
UNIQUE (STU_NAME, STU_ADDRESS), PRIMARY KEY (ROLL_NO));

Set UNIQUE Constraint on an already created table

For MySQL / Oracle / SQL Server / MS Access:

For single column and without constraint naming:

Syntax:

ALTER TABLE <table_name>

ADD UNIQUE (<column_name>);

Example:

ALTER TABLE STUDENTS

ADD UNIQUE (STU_NAME);

For multiple columns and with constraint naming

Syntax:

ALTER TABLE <table_name>

ADD CONSTRAINT <constraint_name> UNIQUE (<column_name1>, <column_name2>...);

Example:

ALTER TABLE STUDENTS

ADD CONSTRAINT stu_Info UNIQUE (STU_NAME, STU_ADDRESS);

How to drop a UNIQUE Constraint?

IN MySQL:

syntax:

ALTER TABLE <table_name>

DROP INDEX <constraint_name>;

Example:

ALTER TABLE STUDENTS

DROP INDEX stu_Info

IN ORACLE / SQL Server / MS Access:

Syntax:

15

Downloaded by kaleeswaran (kaleeswaranmmcas@gmail.com)

 lOMoARcPSD|49921704

ALTER TABLE <table_name>

DROP CONSTRAINT <constraint_name>;

Example:

ALTER TABLE STUDENTS

DROP CONSTRAINT stu_Info;

DEFAULT:

The DEFAULT constraint provides a default value to a column when there is no value provided while
inserting a record into a table.

CREATE TABLE STUDENT (ROLL_NO INT NOT NULL, STU_NAME VARCHAR (35) NOT
NULL, STU_AGE INT NOT NULL, EXAM_FEE INT DEFAULT 10000, STU_ADDRESS
VARCHAR (35), PRIMARY KEY (ROLL_NO));

Specify DEFAULT constraint while creating a table

What if we want to set this constraint on an already existing table? For this we can ALTER Table
statement like this:

Syntax:

ALTER TABLE <table_name>

MODIFY <column_name> <column_data_type> DEFAULT <default_value>;

Example:

ALTER TABLE STUDENTS

MODIFY EXAM_FEE INT DEFAULT 10000;

This way we can set constraint on an already created table.

How to drop DEFAULT Constraint?

In the above sections, we have learnt the ways to set Constraint. Here we will see how to drop (delete) a
Constraint:

Syntax:

ALTER TABLE <table_name>

ALTER COLUMN <column_name> DROP DEFAULT;

Example:

Let’s say we want to drop the constraint from STUDENTS table, which we have created in the above
sections. We can do it like this.

ALTER TABLE CUSTOMERS

16

Downloaded by kaleeswaran (kaleeswaranmmcas@gmail.com)

 lOMoARcPSD|49921704

ALTER COLUMN EXAM_FEE DROP DEFAULT;

CHECK:

This constraint is used for specifying range of values for a particular column of a table. When this
constraint is being set on a column, it ensures that the specified column must have the value falling in the
specified range.

CREATE TABLE STUDENT (ROLL_NO INT NOT NULL CHECK (ROLL_NO >1000),

STU_NAME VARCHAR (35) NOT NULL, STU_AGE INT NOT NULL, EXAM_FEE INT
DEFAULT 10000, STU_ADDRESS VARCHAR (35), PRIMARY KEY (ROLL_NO));

In the above example we have set the check constraint on ROLL_NO column of STUDENT table. Now,
the ROLL_NO field must have the value greater than 1000.

KEY CONSTRAINTS:

PRIMARY KEY:

Primary key uniquely identifies each record in a table. It must have unique values and cannot contain
nulls. In the below example the ROLL_NO field is marked as primary key, that means the ROLL_NO
field cannot have duplicate and null values.

CREATE TABLE STUDENT (ROLL_NO INT NOT NULL, STU_NAME VARCHAR (35) NOT
NULL UNIQUE, STU_AGE INT NOT NULL, STU_ADDRESS VARCHAR (35) UNIQUE,
PRIMARY KEY (ROLL_NO));

FOREIGN KEY:

Foreign keys are the columns of a table that points to the primary key of another table. They act as a
cross-reference between tables.

2.2 Integrity Constraints

Integrity constraints are a set of rules. It is used to maintain the quality of information.
Integrity constraints ensure that the data insertion, updating, and other processes have to be
performed in such a way that data integrity is not affected.
Thus, integrity constraint is used to guard against accidental damage to the database.
Types of Integrity Constraint

17

Downloaded by kaleeswaran (kaleeswaranmmcas@gmail.com)

 lOMoARcPSD|49921704

1. Domain constraints
Domain constraints can be defined as the definition of a valid set of values for an attribute.
The data type of domain includes string, character, integer, time, date, currency, etc. The value
of the attribute must be available in the corresponding domain.
Example:

2. Entity integrity constraints

The entity integrity constraint states that primary key value can't be null.
This is because the primary key value is used to identify individual rows in relation and if the
primary key has a null value, then we can't identify those rows.
A table can contain a null value other than the primary key field.
Example:

18

Downloaded by kaleeswaran (kaleeswaranmmcas@gmail.com)

 lOMoARcPSD|49921704

3. Referential Integrity Constraints

A referential integrity constraint is specified between two tables.
In the Referential integrity constraints, if a foreign key in Table 1 refers to the Primary Key of
Table 2, then every value of the Foreign Key in Table 1 must be null or be available in Table 2.
Example:

4. Key constraints
Keys are the entity set that is used to identify an entity within its entity set uniquely.
An entity set can have multiple keys, but out of which one key will be the primary key. A
primary key can contain a unique and null value in the relational table.
Example:

19

Downloaded by kaleeswaran (kaleeswaranmmcas@gmail.com)

 lOMoARcPSD|49921704

SQL
SQL is the Structured Query Language used to store, manipulate, and retrieve data present in a
database server.
MySQL is a relational database management system. It supports large databases and is
customizable. This article will discuss the important commands in SQL.
SQL Commands
SQL commands are instructions that are used to communicate with the database. It is used to
perform specific tasks, work, and functions with data in the database.
Types of SQL Commands
There are five types of SQL commands: DDL, DML, DCL, TCL, and DQL.

2.3 SQL Data Manipulate Language

Data Manipulation Language (DML) is the language that gives users the ability to access, or
manipulate the condition that the appropriate data model has inherited.

20

Downloaded by kaleeswaran (kaleeswaranmmcas@gmail.com)

 lOMoARcPSD|49921704

Here are some SQL commands that come under DML:

 INSERT
 UPDATE
 DELETE
1. INSERT
INSERT command is used to insert new rows or records in a table.
Syntax:
INSERT INTO TABLE_NAME (column1, column2, column3...columnN) VALUES (value1,
value2, value3...valueN);
Example:
INSERT INTO Employees (Emp_Id, Emp_Name) VALUES (04, “Sam Tully”);
2. UPDATE
This command is used to update or modify the value of a column in the table.
Syntax:
UPDATE table_name SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
Example:
UPDATE Employees
SET Salary = 1000
WHERE Emp_Id = 04;
The above code will modify the salary of the employee with Emp_ID= 04;
3. DELETE
DELETE is used for removing one or more rows from the table.
Syntax:
DELETE FROM TableName WHERE Condition;
Example:
DELETE FROM Employees WHERE Emp_Id = 04;
This will delete the record of the Employees whose Emp_ID is 4.

21

Downloaded by kaleeswaran (kaleeswaranmmcas@gmail.com)

 lOMoARcPSD|49921704

2.4 Data Definition Language

Data Definition Language helps you to define the database structure or schemas. DDL
commands are capable of creating, deleting, and modifying data.
some commands that come under DDL:
 CREATE
 ALTER
 DROP
 TRUNCATE
1. CREATE
It is used to create a new table in the database.
Syntax:
CREATE TABLE Table Name (Column1 datatype, Column2 datatype, Column3
datatype,ColumnN datatype);
Example:
CREATE TABLE Employees {
Emp_Id int(3), Emp_Name varchar (20) };
This will create a table Employees with Emp_ID and Emp_Name.
2. ALTER
Alter is used to alter the structure of the database.
Syntax:
ALTER TABLE TableName ADD ColumnName Datatype;
ALTER TABLE TableName DROP COLUMN ColumnName;
Example:
ALTER TABLE Employees ADD BloodGroup varchar (255);
This will add a column Blood Group to the existing table Employees.
ALTER TABLE Employees DROP BloodGroup varchar (255);
This will drop the column Blood Group from the existing table.
3. DROP
It is used to delete both the structure and record in the table.

22

Downloaded by kaleeswaran (kaleeswaranmmcas@gmail.com)

 lOMoARcPSD|49921704

Syntax:
DROP TABLE TableName;
Example:
DROP TABLE Employees;
This SQL command will remove the table structure along with its data from the database.
4. TRUNCATE
This truncate command is used to delete all the rows from the table and free the space.
Syntax:
TRUNCATE TABLE TableName;
Example:
TRUNCATE TABLE Employees;

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:
 Grant
 Revoke
a. Grant: It is used to give user access privileges to a database.
Example:
GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;
b. Revoke: It is used to take back permissions from the user.
Example
REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;
4. Transaction Control Language
TCL commands can only use with DML commands like INSERT, DELETE and UPDATE only.
These operations are automatically committed in the database that's why they cannot be used
while creating tables or dropping them.
Some commands that come under TCL:
 COMMIT
 ROLLBACK
 SAVEPOINT

23

Downloaded by kaleeswaran (kaleeswaranmmcas@gmail.com)

 lOMoARcPSD|49921704

a. Commit: Commit command is used to save all the transactions to the database.
Syntax:
COMMIT;
Example:
DELETE FROM CUSTOMERS
WHERE AGE = 25;
COMMIT;
b. Rollback: Rollback command is used to undo transactions that have not already been saved to
the database.
Syntax:
ROLLBACK;
Example:
DELETE FROM CUSTOMERS
WHERE AGE = 25;
ROLLBACK;
c. Save point: It is used to roll the transaction back to a certain point without rolling back the
entire transaction.
Syntax:
SAVEPOINT SAVEPOINT_NAME;
5. Data Query Language
DQL is used to fetch the data from the database.
It uses only one command:
SELECT
a. SELECT: This is the same as the projection operation of relational algebra. It is used to select
the attribute based on the condition described by WHERE clause.
Syntax:
SELECT expressions FROM TABLES WHERE conditions;
For example:

24

Downloaded by kaleeswaran (kaleeswaranmmcas@gmail.com)

 lOMoARcPSD|49921704

SELECT emp_name FROM employee WHERE age > 20;

SQL Operator
There are various types of SQL operator:

SQL Arithmetic Operators

Let's assume 'variable a' and 'variable b'. Here, 'a' contains 20 and 'b' contains 10.

Operator Description Example

+ It adds the value of both operands. a+b will give 30

- It is used to subtract the right-hand operand from the left-hand a-b will give 10
operand.

* It is used to multiply the value of both operands. a*b will give 200

/ It is used to divide the left-hand operand by the right-hand a/b will give 2
operand.

% It is used to divide the left-hand operand by the right-hand a%b will give 0
operand and returns reminder.
SQL Comparison Operators:
Let's assume 'variable a' and 'variable b'. Here, 'a' contains 20 and 'b' contains 10.

Operator Description Example

= It checks if two operands values are equal or not, if the values are queal (a=b) is not
then condition becomes true. true

25

Downloaded by kaleeswaran (kaleeswaranmmcas@gmail.com)

 lOMoARcPSD|49921704

!= It checks if two operands values are equal or not, if values are not equal, (a!=b) is true
then condition becomes true.

<> It checks if two operands values are equal or not, if values are not equal (a<>b) is true
then condition becomes true.

> It checks if the left operand value is greater than right operand value, if yes (a>b) is not
then condition becomes true. true

< It checks if the left operand value is less than right operand value, if yes (a<b) is true
then condition becomes true.

>= It checks if the left operand value is greater than or equal to the right (a>=b) is not
operand value, if yes then condition becomes true. true

<= It checks if the left operand value is less than or equal to the right operand (a<=b) is true
value, if yes then condition becomes true.

!< It checks if the left operand value is not less than the right operand value, if (a!=b) is not
yes then condition becomes true. true

!> It checks if the left operand value is not greater than the right operand (a!>b) is true
value, if yes then condition becomes true.

SQL Logical Operators

There is the list of logical operator used in SQL:

Operator Description

ALL It compares a value to all values in another value set.

AND It allows the existence of multiple conditions in an SQL statement.

ANY It compares the values in the list according to the condition.

BETWEEN It is used to search for values that are within a set of values.

IN It compares a value to that specified list value.

NOT It reverses the meaning of any logical operator.

OR It combines multiple conditions in SQL statements.

EXISTS It is used to search for the presence of a row in a specified table.

26

Downloaded by kaleeswaran (kaleeswaranmmcas@gmail.com)

 lOMoARcPSD|49921704

LIKE It compares a value to similar values using wildcard operator.

2.5 SQL VIEWS

A view is nothing more than a SQL statement that is stored in the database with an associated
name. A view is actually a composition of a table in the form of a predefined SQL query.
A view can contain all rows of a table or select rows from a table. A view can be created from
one or many tables which depends on the written SQL query to create a view.
Views, which are a type of virtual tables allow users to do the following –
 Structure data in a way that users or classes of users find natural or intuitive.
 Restrict access to the data in such a way that a user can see and (sometimes) modify
exactly what they need and no more.
 Summarize data from various tables which can be used to generate reports.
Creating Views
Database views are created using the CREATE VIEW statement. Views can be created from a
single table, multiple tables or another view.
To create a view, a user must have the appropriate system privilege according to the specific
implementation.
The basic CREATE VIEW syntax is as follows –
CREATE VIEW view_name AS SELECT column1, column2....FROM table _name WHERE
[condition];
You can include multiple tables in your SELECT statement in a similar way as you use them in a
normal SQL SELECT query.
Example
Consider the CUSTOMERS table having the following records –

ID NAME AGE ADDRESS SALARY

1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00
7 Muffy 24 Indore 10000.00

27

Downloaded by kaleeswaran (kaleeswaranmmcas@gmail.com)

 lOMoARcPSD|49921704

Following is an example to create a view from the CUSTOMERS table. This view would be
used to have customer name and age from the CUSTOMERS table.
SQL > CREATE VIEW CUSTOMERS_VIEW AS
SELECT name, age FROM CUSTOMERS;
Now, you can query CUSTOMERS_VIEW in a similar way as you query an actual table.
Following is an example for the same.
SQL > SELECT * FROM CUSTOMERS_VIEW;
This would produce the following result.

NAME AGE

Ramesh 32

Khilan 25

kaushik 23

Chaitali 25

Hardik 27

Komal 22

Muffy 24

The WITH CHECK OPTION

The WITH CHECK OPTION is a CREATE VIEW statement option. The purpose of the WITH
CHECK OPTION is to ensure that all UPDATE and INSERTs satisfy the condition(s) in the
view definition.
If they do not satisfy the condition(s), the UPDATE or INSERT returns an error.
The following code block has an example of creating same view CUSTOMERS_VIEW with the
WITH CHECK OPTION.
CREATE VIEW CUSTOMERS_VIEW AS SELECT name, age FROM CUSTOMERS WHERE
age IS NOT NULL WITH CHECK OPTION;
The WITH CHECK OPTION in this case should deny the entry of any NULL values in the
view's AGE column, because the view is defined by data that does not have a NULL value in the
AGE column.
Updating a View
A view can be updated under certain conditions which are given below −

28

Downloaded by kaleeswaran (kaleeswaranmmcas@gmail.com)

 lOMoARcPSD|49921704

 The SELECT clause may not contain the keyword DISTINCT.

 The SELECT clause may not contain summary functions.
 The SELECT clause may not contain set functions.
 The SELECT clause may not contain set operators.
 The SELECT clause may not contain an ORDER BY clause.
 The FROM clause may not contain multiple tables.
 The WHERE clause may not contain subqueries.
 The query may not contain GROUP BY or HAVING.
 Calculated columns may not be updated.
 All NOT NULL columns from the base table must be included in the view in order for
the INSERT query to function.
So, if a view satisfies all the above-mentioned rules then you can update that view. The
following code block has an example to update the age of Ramesh.
SQL > UPDATE CUSTOMERS_VIEW SET AGE = 35 WHERE name = 'Ramesh';
This would ultimately update the base table CUSTOMERS and the same would reflect in the
view itself. Now, try to query the base table and the SELECT statement would produce the
following result.

ID NAME AGE ADDRESS SALARY

1 Ramesh 35 Ahmedabad 2000.00

2 Khilan 25 Delhi 1500.00

3 kaushik 23 Kota 2000.00

4 Chaitali 25 Mumbai 6500.00

5 Hardik 27 Bhopal 8500.00

6 Komal 22 MP 4500.00

7 Muffy 24 Indore 10000.00

Inserting Rows into a View

Rows of data can be inserted into a view. The same rules that apply to the UPDATE command
also apply to the INSERT command.
Here, we cannot insert rows in the CUSTOMERS_VIEW because we have not included all the
NOT NULL columns in this view, otherwise you can insert rows in a view in a similar way as
you insert them in a table.

29

Downloaded by kaleeswaran (kaleeswaranmmcas@gmail.com)

 lOMoARcPSD|49921704

Deleting Rows into a View

Rows of data can be deleted from a view. The same rules that apply to the UPDATE and
INSERT commands apply to the DELETE command.
Following is an example to delete a record having AGE = 22
SQL > DELETE FROM CUSTOMERS_VIEW WHERE age = 22;
This would ultimately delete a row from the base table CUSTOMERS and the same would
reflect in the view itself. Now, try to query the base table and the SELECT statement would
produce the following result.

I NAME AGE ADDRESS SALARY

D

1 Ramesh 35 Ahmedabad 2000.00

2 khilan 25 Delhi 1500.00

3 Kaushik 23 Kota 2000.00

4 Chaitali 25 Mumbai 6500.00

5 Hardik 27 Bhopal 8500.00

7 Muffy 24 Indore 10000.00

Dropping Views
Obviously, where you have a view, you need a way to drop the view if it is no longer needed.
The syntax is very simple and is given below −
DROP VIEW view_name;
Following is an example to drop the CUSTOMERS_VIEW from the CUSTOMERS table.
DROP VIEW CUSTOMERS_VIEW;

2.6 SQL Programming

SQL is a standard language for storing, manipulating and retrieving data in databases.
Our SQL tutorial will teach you how to use SQL in: MySQL, SQL Server, MS Access, Oracle,
Sybase, Informix, PostgreSQL, and other database systems.
Example: SELECT * FROM Customers;

30

Downloaded by kaleeswaran (kaleeswaranmmcas@gmail.com)

 lOMoARcPSD|49921704

What is SQL?
 SQL stands for Structured Query Language
 SQL lets you access and manipulate databases
 SQL became a standard of the American National Standards Institute (ANSI) in 1986,
and of the International Organization for Standardization (ISO) in 1987
What Can SQL do?
 SQL can execute queries against a database
 SQL can retrieve data from a database
 SQL can insert records in a database
 SQL can update records in a database
 SQL can delete records from a database
 SQL can create new databases
 SQL can create new tables in a database
 SQL can create stored procedures in a database
 SQL can create views in a database
 SQL can set permissions on tables, procedures, and views
SQL is a Standard - BUT....
Although SQL is an ANSI/ISO standard, there are different versions of the SQL language.
However, to be compliant with the ANSI standard, they all support at least the major commands
(such as SELECT, UPDATE, DELETE, INSERT, WHERE) in a similar manner.
Using SQL in Your Web Site
To build a web site that shows data from a database, you will need:
 An RDBMS database program (i.e. MS Access, SQL Server, MySQL)
 To use a server-side scripting language, like PHP or ASP
 To use SQL to get the data you want
 To use HTML / CSS to style the page
RDBMS
RDBMS stands for Relational Data Base Management System.
RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM
DB2, Oracle, MySQL, and Microsoft Access.
The data in RDBMS is stored in database objects called tables. A table is a collection of related
data entries and it consists of columns and rows.
Example

31

Downloaded by kaleeswaran (kaleeswaranmmcas@gmail.com)

 lOMoARcPSD|49921704

SELECT * FROM Customers;

Every table is broken up into smaller entities called fields. The fields in the Customers table
consist of Customer ID, Customer Name, Contact Name, Address, City, Postal Code and
Country. A field is a column in a table that is designed to maintain specific information about
every record in the table.
A record, also called a row, is each individual entry that exists in a table. For example, there are
91 records in the above Customers table. A record is a horizontal entity in a table.
A column is a vertical entity in a table that contains all information associated with a specific
field in a table.

32

Downloaded by kaleeswaran (kaleeswaranmmcas@gmail.com)