lOMoARcPSD|38492759
DBMS Lab Manual Diploma
Computer Science & Engineering (Swami Sarvanand Group of Institutes)
Scan to open on Studocu
Studocu is not sponsored or endorsed by any college or university
Downloaded by Dinesh Kumari (dineshkumari@[Link])
lOMoARcPSD|38492759
SWAMI SARVANAND INSTITUTE OF ENGINEERING & TECHNOLOGY,
DINANAGAR
LAB MANUAL
DATABASE MANAGEMENT SYSTEM
DIPLOMA-CSE 4th Semester
DEPARTMENT OF
COMPUTER SCIENCE & ENGINEERING
SSIET, DINANAGAR
Downloaded by Dinesh Kumari (dineshkumari@[Link])
lOMoARcPSD|38492759
INDEX
EX .NO LIST OF EXPERIMENTS PAGE NO
1 Exercises on creation and modification of structures 1-2
of tables.
2 Exercises on inserting and deleting values from 3-7
tables.
Exercises on using various types of Joins.
3 8-12
Exercises on commands like Grant, Revoke, Commit
4 13-14
and Rollback etc.
5 Exercises on using functions provided by database
package.
6 Exercises on Quering the table (using select
commands)
7 Design of Database for any Application.
Downloaded by Dinesh Kumari (dineshkumari@[Link])
lOMoARcPSD|38492759
EXPERIMENT NO: 1
Exercises on creation and modification of structures of tables
The commands used are:
● CREATE - It is used to create a table.
● ALTER – The structure of a table can be modified by using the ALTER TABLE
command. This command is used to add a new column, modify the existing column
definition and to include or drop integrity constraint.
● DROP - It will delete the table structure provided the table should be empty.
● TRUNCATE - If there is no further use of records stored in a table and the structure has
to be retained, and then the records alone can be deleted.
● DESC - This is used to view the structure of the table
CREATION OF TABLE:
SYNTAX:
create table<table name>(column1 datatype,column2 datatype...);
EXAMPLE:
SQL>CREATE TABLE Employee ( EmpNo number(5), EName VarChar(15), Job Char(10) ,
DeptNo number(3));
ALTER TABLE:
(a) To Add column to existing Table
Syntax:
alter table table-name add(column-name datatype );
EXAMPLE:
ALTER TABLE Employee ADD (phone_no char (20));
(b)To Add Multiple columns to existing Table
Downloaded by Dinesh Kumari (dineshkumari@[Link])
lOMoARcPSD|38492759
Syntax:
alter table table-name add(column-name1 datatype1, column-name2
datatype2, column-name3 datatype3);
EXAMPLE:
alter table Employee add(salary number(7), age(5));
(c) Dropping a Column from a Table
Syntax:
ALTER TABLE <Table Name>DROP COLUMN <CoumnName>;
EXAMPLE:
ALTER TABLE Employee DROP COLUMN phone_no ;
(d) Modifying Existing Columns
Syntax:
ALTER TABLE <Table Name>MODIFY (<CoumnName><Newdata type>(<size>));
EXAMPLE:
ALTER TABLE Employee MODIFY (EName VarChar(25));
Downloaded by Dinesh Kumari (dineshkumari@[Link])
lOMoARcPSD|38492759
EXPERIMENT NO 2
Exercises on inserting and deleting values from tables
DML commands are the most frequently used SQL commands and is used to
query and manipulate the existing database objects. Some of the commands are
1. INSERT
This is used to add one or more rows to a table. The values are separated
by commas and the data types char and date are enclosed in apostrophes.
The values must be entered in the same order as they are defined.
2. SELECT
It is used to retrieve information from the [Link] is generally referred to
as querying the table. We can either display all columns in a table or only
specify column from the table.
3. UPDATE
It is used to alter the column values in a table. A single column may be
updated or more than one column could be updated.
4. DELETE
After inserting row in a table we can also delete them if required. The
delete command consists of a from clause followed by an optional where
clause
INSERT COMMAND
(a) Inserting a single row into a
table: Syntax:
insert into <table name> values (<expression1>,<expression2>)
Example:
SQL>INSERT INTO EMPLOYEE VALUES(101,'MANU','LECTURER',15000);
(b) Inserting more than one record using a single insert commands:
Syntax:
insert into <table name> values (&col1, &col2, ….)
Example:
SQL> INSERT INTO EMPLOYEE
VALUES(&EMPNO,'&ENAME','&DESIGNATIN','&SALARY');
( c) Skipping the fields while inserting:
Insert into <tablename>(<column name1>,<column name3>)>values
(<expression1>,<expression3>);
Other way is to give null while passing the values.
Downloaded by Dinesh Kumari (dineshkumari@[Link])
lOMoARcPSD|38492759
SELECT COMMAND
(a) view all rows and all columns
Syntax:
Select *
from
tablename;
Example:
Select * from Employee;
(b) Selected Columns And All Rows
Syntax:
Select <column1>,<column2> from
tablename; Example:
Select empno, empname from
Employee; (c)Selected Columns And
selected Rows Syntax:
SELECt <column1>, <column2> FROM <tablename> WHERE <condition> ;
Example:
Select empno, empname from Employee where
designation=’lecturer’; (c)Eliminating duplicate rows
Syntax:
SELECT DISTINCT <column1>, <column2> FROM
<tablename>
Example:
Select distinct empname from Employee;
UPDATE COMMAND
(b)updating all rows
Downloaded by Dinesh Kumari (dineshkumari@[Link])
lOMoARcPSD|38492759
Syntax:
update tablename set
columnname1>=<exprssion1>,<columnname2>=<exprssion2>;
Example:
Update Employee set Designation = ‘lecturer’;
(b)updating records
conditionally Syntax:
update tablename set field=values where condition;
Example:
Update Employeeemp set sal = 10000 where empno=135;
DELETE COMMAND
(b)Removal of all
rows Syntax:
Delete from <table
name> ; Example:
Delete from emp;
(b)removal of specific
rows Syntax:
Delete from <table name> where <condition>; Example:
delete from emp where empno=135;
Downloaded by Dinesh Kumari (dineshkumari@[Link])
lOMoARcPSD|38492759
EXPERIMENT NO:3
Exercises on using various types of Joins.
a) JOINS:
Join is a query in which data is returned from two or more tables.
Natural join:
It returns the matching rows from the table that are being joined
.
Syntax:
>select <attribute> from TN where [Link]=[Link].
Inner join:
It returns the matching rows from the table that are being joined.
Syntax:
>select <attribute> from TN1 innerjoin TN2 on [Link]=[Link].
Left outer join:
It returns all the rows from the table1 even when they are unmatched.
Syntax:
5. select <attribute> from TN1 left outer join TN2 on
[Link]=[Link].
2. select <attribute> from TN where [Link](+)=[Link].
Right outer join:
It returns all the rows from the table2 even when they are unmatched.
Syntax:
4. select <attribute> from TN1 right outer join TN2 on
[Link]=[Link].
2. select <attribute> from TN where [Link]=(+)[Link].
Full join:
It is the combination of both left outer and right outer join. Syntax:
>select <attribute> from TN1 full join TN2 on [Link]=[Link].
JOINS
SQL> create table emp(name varchar2(20),salary number(10)); Table created.
SQL> select * from emp;
NAME SALARY
Downloaded by Dinesh Kumari (dineshkumari@[Link])
lOMoARcPSD|38492759
ashu 10000
asma 1200
asif 2000
arif 1000
niyas 3000
SQL> create table emp1(name varchar2(20),empid number(10)); Table
created.
SQL> select * from emp1; NAME
EMPID
fathi 12
sumi 32
priya 11
wahab 10
sweety 9
asma 1200
6 rows selected.
NATURAL JOIN
************
SQL>select [Link],salary from emp,emp1 where [Link]=[Link] NAME SALARY
asma 1200
LEFT OUTER JOIN
SQL>select [Link],salary from emp left outer join emp1 on [Link]=[Link]
NAME SALARY
asma 1200
asif 2000
arif 1000
niyas 3000
ashu 10000
RIGHT OUTER JOIN
SQL>select [Link],empid from emp right outer join emp1 on [Link]=[Link]
NAME EMPID
asma 1200
sweety 9
sumi 32
wahab 10
fathi 12
priya 11
6 rows selected.
Downloaded by Dinesh Kumari (dineshkumari@[Link])
lOMoARcPSD|38492759
FULL JOIN
SQL>select [Link],[Link],[Link],salary from emp full join emp1 on
[Link]=[Link]
NAME NAME EMPID SALARY
asma asma 1200 1200
asif 2000
arif 1000
niyas 3000
ashu 10000
sweety 9
sumi 32
wahab 10
fathi 12
priya 11
10 rows selected.
Downloaded by Dinesh Kumari (dineshkumari@[Link])
lOMoARcPSD|38492759
EXPERIMENT NO:4
Exercises on commands like Grant, Revoke, Commit and Rollback etc.
Data Control Language (DCL) consists of various commands which are related to data sharing
and security of data in database.
They are
GRANT
REVOKE
Granting Privileges:
Objects that are created by a user are owned and controlled by that user. If user wishes to
access any of the objects belonging to another user, the owner of the object will have to give
permissions for such access. This is called Granting of Privileges.
Granting privileges using the GRANT statements:
The GRANT statements provide various types of access to database objects such as tables,
views.
Syntax:
GRANT {object privileges} ON
object name
TO username;
Object Privileges:
each object privilege that is granted authorizes the grantee to perform some operation on the
object. The user can grant all the privileges or grant only specific object privileges.
The list of object privileges is as follows:
• ALTER: allows the grantee to change the table definitions with the ALTER table
command.
• DELETE: allows the grantee to remove the records from the table with the DELETE
command.
• INDEX: allows the grantee to create an index on the table with the CREATE INDEX
command.
• INSERT: allows the grantee to add records to the table with the INSERT command.
• SELECT: allows the grantee to query the table with SELECT command.
• UPDATE: allows the grantee to modify the records in the table with the UPDATE
Downloaded by Dinesh Kumari (dineshkumari@[Link])
lOMoARcPSD|38492759
command.
Revoking privileges given:
Privileges once given can be denied to a user using the REVOKE command. The object
owner can revoke privileges granted to another user. A user of an object who is not owner,
but has been granted the GRANT privilege, has the power to REVOKE the privileges from
the grantee.
Revoking permission using the REVOKE statement:
The REVOKE statement is used to deny the grant given on an object.
Syntax:
REVOKE {object privileges} ON
object name
FROM username;
The REVOKE command is used to revoke object privileges that the user previously granted to
the Revoke.
The REVOKE command cannot be used to revoke the privileges granted through operating
system.
Downloaded by Dinesh Kumari (dineshkumari@[Link])
lOMoARcPSD|38492759
Downloaded by Dinesh Kumari (dineshkumari@[Link])
lOMoARcPSD|38492759
Downloaded by Dinesh Kumari (dineshkumari@[Link])
lOMoARcPSD|38492759
Downloaded by Dinesh Kumari (dineshkumari@[Link])
lOMoARcPSD|38492759
Downloaded by Dinesh Kumari (dineshkumari@[Link])
