UNIVERSITY INSTITUTE OF

ENGINEERING & TECHNOLOGY, ROHTAK

PRACTICAL FILE
SUBJECT: DBMS-LAB(CSE-212-F)

Submitted To: Submitted By:

DR. RAHUL RISHI RAVI
DIRECTOR UIET 13521 CSE 4th Sem.
 INDEX

S.No. Practical Name Signature

1. To study basics of Structured Query Language

(SQL)
2. To create table and insert values in it

3. To study the use of alter, describe and drop

statements
4. To study the use of delete and update statements.

5. To study the various query processing statements

6. To study the various character functions

7. To study the various number functions

8. To study the various group functions

9. To study the join operations

10. To study the set operations

 PRACTICAL: 1

Aim: To study basics of Structured Query Language (SQL)

Theory: The ‘sequel’ is the abbreviation for structured query language. The SQL
consists of a set of facilities for defining, accessing and managing relational databases.
All tasks related to relational data management- creating tables, querying the database,
deleting, granting access to users etc., can be done using SQL. Is has been accepted as
an American standard by American National Standards Institute (ANSI) and is a
Federal Information Processing Standard (FIPS). The first commercial DBMS that
supported SQL was Oracle in 1979. SQL statements can be invoked either
interactively in a terminal session or by embedding them in application programs.

Characteristics of SQL: The following are the important characteristics of SQL

1. SQL is extremely flexible.

2. SQL uses a free form syntax that gives the user the ability to structure SQL
statements in a way best suited.
3. It is a free formatted language, i.e., there is no need to start SQL statements ina
particular column or to be finished in a single line.
4. It is a non-procedural language.

Components of SQL language:

The SQL language is mainly divided into four major parts. These four parts are further
divided into subparts. The major parts are as follows:

1. Data-Definition Language (DDL) :

The SQL DDL provide commands for defining the relations, deleting the
relations and modifying the existing relation schemas. The subparts of DDL
are:
• View Definition Language (VDL): The SQL DDL provide commands
for deleting and dropping the views.
• Integrity: The SQL DDL provide commands for specifying integrity
constants that must be satisfied by the data stored in database.
 • Authorization: The SQL DDL provide commands for specifying
access rights to the relations and views.

2. Data-Manipulation Language (DML):

The SQL DML provides a query language. This query language is based on
relational algebra and tuple relational calculus. This contain commands to insert
tuples into the database, to delete tuples from the database and to modify/update
tuples in the database.

3. Data Control Language or Transaction Control Language (DCL or TCL):

The SQL DCL provide commands that help the DBA to control the database
such as commands to grant or revoke privileges to access the database and to
store or remove transactions that would affect the database.

4. Embedded SQL and Dynamic SQL:

• Embedded SQL defines the way the SQL statements can be embedded
within general purpose programming languages like C,C++,Cobol,
Pascal etc. the language in which SQL queries are embedded is referred
to as a host language. The SQL queries embedded in the host language
constitute embedded SQL.
• Dynamic SQL allows programs to construct and submit SQL queries at
run time.
 PRACTICAL: 2

Aim: To create table and insert values in it.

Theory: Create Table statement is used to create new tables in database.

Syntax: CREATE TABLE <table name>

(<column name> <data type (size)>,

<column name> <data type (size)>,

----------------------------------------------);

Example: CREATE TABLE data

( rollno int,

name char(15),

place char(20),

deptid int);

The above statement will create a table ‘data’ with ‘rollno’,’name’,’place’,’deptid’ as

its columns along with their respective data types and size.

Example: CREATE TABLE debt

( id int,

deptname char(15),

loc char(20));
Insert statement

Insert statement is used to insert or add new rows in table.

Syntax: INSERT INTO <table-name> (column 1, column 2, column 3 …. column n)

VALUES (value 1, value 2, value 3 …… value n);

Note: Only a single row is inserted at a time.

Name of columns can be given in any order.

Example: INSERT INTO data

VALUES(‘13521’, 'Ravi','Rohtak','1');

INSERT INTO data

VALUES(‘13519’, 'Sachin','Bhiwani','1');

INSERT INTO data(name, place, deptid)

VALUES( 'Vijay','Sonipat','2');

INSERT INTO data(rollno, name, deptid)

VALUES(‘13443’,’Rajat’,'3');

INSERT INTO data

VALUES(‘13511’, 'Viren','Rohtak','2');

INSERT INTO data(rollno, name, place)

VALUES(‘13493’, 'Dev','Bahadurgarh');

INSERT INTO data

VALUES(‘13469’, 'Parshant','Rohtak','4');

The above statements will insert the respective values in the table ‘data’. The table
will look like this
Example: INSERT INTO debt

VALUES(‘1’,’CSE’,’ComputerBlock’);

INSERT INTO debt

VALUES(‘2’,’Civil’,’CivilBlock’);

INSERT INTO debt

VALUES(‘3’,’ECE’,’ComputerBlock’);

INSERT INTO debt

VALUES(‘4’,’Biotech’,’BioBlock’);

The above statements will insert the respective values in the table ‘debt’. The table
will look like this
 PRACTICAL: 3

Aim: To study the use of alter, describe and drop statements.

Theory: Alter table statement is used to add or drop columns of tables and to
modify name and attributes of an existing column.

• To add new column:

Syntax: ALTER TABLE <table-name>

ADD <column-name> <data type(size)>;

Example: ALTER TABLE data

ADD salary int;

• To drop a column:

Syntax: ALTER TABLE <table-name>

DROP COLUMN <column-name>;

Example: ALTER TABLE data

DROP COLUMN salary;

 • To modify a column:

Syntax: ALTER TABLE <table-name>

MODIFY (<column-name> <new data type(size)>);

Example: ALTER TABLE data

MODIFY(name char(30));

Describe Statement: It describes the structure of table.

Syntax: DESCRIBE <table-name>;

Example: DESCRIBE data;

Drop Statement: Drop table statement is used to remove table from database.
Syntax: DROP TABLE <table-name>;
Example: DROP TABLE data;
 PRACTICAL: 4

Aim: To study the use of delete and update statements.

Theory: Delete Statement is used to remove existing rows from table.

Syntax: DELETE FROM <table-name>

WHERE <condition>;

Example: DELETE FROM data

WHERE name=’parshant’;
Update Statement is used to modify the values of existing rows.

Syntax: UPDATE <table-name>

SET <(column 1= value 1), (column 2= value 2),…..,(column n= value n)>

Where <condition>;

Note: All rows in the table satisfy the conditions are updated.

Example: UPDATE data

SET name=’devender’

WHERE rollno=’13493’;
 PRACTICAL: 5
Aim: To study the various query processing statements.

Theory: Select Statement is used to retrieve information from table.

Syntax: SELECT <column-list>

FROM <table-name>;

Example: To display all names and rollno of the students, the query is

SELECT rollno,name

FROM data;

• Eliminating Duplicate Rows: To eliminate duplicate rows, the keyword

‘DISTINCT’ is used.

Syntax: SELECT DISTINCT <column-name>

FROM <table-name>;

Example: SELECT DISTINCT place

FROM data;
Where Clause: Where clause is used to select particular rows from table.

Syntax: SELECT <column-list>

FROM <table-name>

WHERE <condition>;

Example: List all the names of students having department id=1.

SELECT name

FROM data

WHERE deptid=1;
Operators: The various operators provided by SQL are as follow:

Operator Description
= Equal to
> Greater than
< Less than
>= Greater than equal to
=< Less than equal to
<> Not equal to
IN Checking the value within a set
BETWEEN Checking the vale within a range
LIKE Matching the pattern of characters
IS NULL Checking the null value
AND It returns true if both conditions are
true
OR It returns true if either condition is
true
NOT It returns true if condition is false

• List all names and place of students having roll no. greater than 13501
SELECT name,place
FROM data
WHERE rollno>13501;

• List the names and roll no of students ending with ‘n’

SELECT name,rollno
FROM data
WHERE name LIKE ’%n’;
• List name, place, roll no. of students having deptid equal to null.
SELECT name,place,rollno
FROM data
WHERE deptid IS NULL;
 PRACTICAL: 6

Aim: To study the various character functions.

Theory: Character functions are the sub type of Single Row Functions. The various
character functions provide by SQL are as follow:

Function Description
LENGTH(‘string’) It returns the number of characters in
string
LOWER(‘string’) It converts string to lowercase
UPPER(‘string’) It converts string to uppercase
INITCAP(‘string’) It converts only first character of each
word in string to uppercase
CHR(x) It returns equivalent character value of
integer x
CONCAT(‘string 1’,string 2’) It joins both the strings. It is equivalent to
concatenation operator
REPLACE(‘string’,’search It replaces every occurrence of search str
str’,’replace str’) with replace str within string
SUBSTR(‘string’,m,[n]) It returns a substring starting from mth
position and up to nth position
INSTR(‘string’,’char’) It returns position of first occurrence of
char in string

Dual Table: Dual table is used to explain single row functions. It has one column
name Dummy and one row having value x.

• SELECT LENGTH(‘Ravi’)”Output”
FROM dual;

• SELECT LOWER(‘Virender’)”Output”
FROM dual;

• SELECT UPPER(‘Sachin’)”Output”
FROM dual;
• SELECT INITCAP(‘ravibazad’)”Output”
FROM dual;

• SELECT CHR(103)”Output”
FROM dual;

• SELECT CONCAT(‘Ra’,’vi’)”Output”
FROM dual;

• SELECT REPLACE(‘Virender and Devender’,’ender’,’u’)”Output”

FROM dual;

• SELECT SUBSTR(‘database’,3,7)”Output”
FROM dual;

• SELECT INSTR(‘database’,’b’)”Output”
FROM dual;
 PRACTICAL: 7

Aim: To study the various number functions.

Theory: Number functions are also known as arithmetic functions. They accept
numeric data and returns numeric values. The various numeric functions are :

Function Description
CEIL(x) It returns the smallest integer greater than
or equal to x.
FLOOR(x) It returns the largest integer less than or
equal to x.
ABS(x) It returns the absolute value of x.
POWER(x,y) It returns x raised to the power y.
MOD(x,y) It returns the remainder of x divided by y.
SIGN(x) It returns +1 if x is positive or -1 if x is
negative.
ROUND(x,y) It rounds the column. Y specify the
number of digits after decimal. If y is
omitted then there are no decimal places.
If y is negative, numbers to the left of
decimal point are rounded.
EXP(x) It returns e raised to the power x.
SQRT(x) It returns square root of x. If x is negative
NULL is returned.
TRUNC(x,y) It truncates the column. Y specify the
number of digits after decimal. If y is
omitted then there are no decimal places.
If y is negative, numbers to the left of
decimal point are truncated.

• SELECT CEIL(14.8)”Output”
FROM dual;

• SELECT FLOOR(14.6)”Output”
FROM dual;
• SELECT ABS(-15)”Output”
FROM dual;

• SELECT POWER(7,2)”Output”
FROM dual;

• SELECT MOD(5,2)”Output”
FROM dual;

• SELECT SIGN(-9), SIGN(8)”Output”

FROM dual;

• SELECT ROUND(77.123)”Output”
FROM dual;

• SELECT EXP(4)”Output”
FROM dual;

• SELECT SQRT(49)”Output”
FROM dual;

• SELECT TRUNC(79.128,2)”Output”
FROM dual;
 PRACTICAL: 8

Aim: To study the various group functions.

Theory: Group functions are those functions that operate on a group of rows and
return a single result per group. Group may be entire table or a part of table. All the
group functions ignore null values. The various group functions are:

Function Description
MAX(column name) It returns the maximum value of a given
attribute, ignoring NULL values.
MIN(column name) It returns the minimum value of a given
attribute, ignoring NULL values.
AVG(column name) It returns the average value of column
values, ignoring NULL values.
SUM(column name) It returns the sum of column values,
ignoring NULL values.
COUNT(* column name) It returns the total number of rows.

• SELECT MAX(rollno)”Output”
FROM data;

• SELECT MIN(name)”Output”
FROM data;

• SELECT AVG(deptid)”Output”
FROM data;

• SELECT SUM(deptid)”Output”
FROM data;

• SELECT COUNT(* deptid)”Output”

FROM data;
 PRACTICAL: 9

Aim: To study the join operations.

Theory: Join Operation: If we need information from more than one table then we
use joins. To join tables the condition need to be specified. The different join
operations available are:

• Equijoin: When two or more tables are joined by equality of values in one or
more columns then it is called Equijoin.

Note: More than two tables can be joined by using logical operators.

Example: SELECT s.name,d.deptname

FROM data s, debt d
WHERE s.deptid=d.id;

• Non-Equijoin: When tables are joined by any other operator except the quality
operator in condition, then it is known as Non-Equijoin.

Example: SELECT s.name,s.place,d.deptname

FROM data s, debt d
WHERE s.deptid IN (d.id=1, d.id=2);
• Outer join: The outer join operator is represented by symbol (+). During
simple joining some rows are missing due to null value. To display these rows,
outer join operator is used towards deficient side.

Example: SELECT s.name,s.place,d.deptname

FROM data s, debt d
WHERE s.deptid(+)=d.id;

• Left Outer join: To display all the rows of table left of the join condition, use
LEFT OUTER JOIN. This keyword is used instead of outer join operator ‘(+)’.

Example: SELECT s.name,s.place,d.deptname

FROM data s, debt d
LEFT OUTER JOIN debt d ON (s.deptid=d.id);

• Right Outer Join: To display all the rows of table right of the join condition,
use RIGHT OUTER JOIN. This keyword is used instead of outer join operator
‘(+)’.

Example: SELECT s.name,s.place,d.deptname

FROM data s, debt d
RIGHT OUTER JOIN debt d ON (s.deptid=d.id);
• Full Outer Join: To display all the rows of both of the tables, use FULL
OUTER JOIN.

Example: SELECT s.name,s.place,d.deptname

FROM data s, debt d
FULL OUTER JOIN debt d ON (s.deptid=d.id);
 PRACTICAL: 10

Aim: To study various set operations.

Theory: Cartesian product: In Cartesian product there is no join condition. It returns

all possible combinations of rows.

Example: SELECT rollno,name,deptname,loc

FROM data,debt;
Union operator: The UNION operator is used to combine the result-set of two or
more SELECT statements.

Note: Each SELECT statement within the UNION must have the same number of
columns. The columns must also have similar data types. Also, the columns in each
SELECT statement must be in the same order.

Syntax: SELECT <column_name> FROM table1

UNION
SELECT <column_name> FROM table2;