Lab Manual

IT1402 DBMS LAB S.A.
ENGINEERING COLLEGE
EXNO :1A
DATE :
CREATION OF A DATA DEFINITION COMMANDS
AIM:
To create a database and to write SQL queries to retrieve information from the database in
RDBMS.
CREATE TABLE
It is used to create a table .
Rules:
1. Oracle reserved words cannot be used.
2. Underscore, numerals, letters are allowed but not blank space.
3. Maximum length for the table name is 30 characters.
4. Different tables should not have same name.
5. We should specify a unique column name.
6. We should specify proper data type along with width.
Syntax:
SQL>Create table tablename (column_name1 data_ type constraints, column_name2 data_ type
constraints …)
Example:
SQL>Create table emp( empno number(5), ename VarChar(15), job VarChar(10), deptno
number(3) ,sal number(5));
SQL>Create table stud(sname varchar(20) , rollno number(10) not null,dob date);
DESC
This is used to view the structure of the table.
Example: SQL>desc emp;
Name Null? Type

--------------------------------- -------- ----------------------------
empno number(5)
ename VarChar(15)
job Var Char(10)
deptno number(3)
sal number (5)
1
IT1402 DBMS LAB S.A.ENGINEERING COLLEGE
INSERTING VALES INTO TABLE
Insert a single record into dept table.
SQL> insert into emp values (10,'xxxx','Lect',2,10000);

1 row created.
SQL> insert into stud values (‘zzzzzz’,100,to_date(‘19951225’,’yyyymmdd’));

1 row created.
Insert more than a record into emp table using a single insert command.
SQL> insert into emp values(&empno,'&ename','&job',&deptno,&sal);

Enter value for empno: 1
Enter value for ename: Mathi
Enter value for job: AP
Enter value for deptno: 1
Enter value for sal: 10000
old 1: insert into emp values(&empno,'&ename','&job',&deptno,&sal)
new 1: insert into emp values(1,'Mathi','AP',1,10000)
1 row created.
SQL> /
Enter value for ename: Arjun
Enter value for job: ASP
new 1: insert into emp values(2,'Arjun','ASP',2,12000)
1 row created.
SQL> /
Enter value for ename: Gugan
Enter value for job: ASP
new 1: insert into emp values(3,'Gugan','ASP',1,12000)
1 row created.
VIEW THE DATABASE CREATED
SQL> select * from emp;
2
EMPNO ENAME JOB DEPTNO SAL

---------- -------------------- ------------- ---------- ----------
1 Mathi AP 1 10000
2 Arjun ASP 2 12000
3 Gugan ASP 1 12000
10 xxx Lect 2 10000
Select particular column to view from database
SQL> select ename, job from emp;
ENAME JOB
----------- -------------
Mathi AP
Arjun ASP
Gugan ASP
Karthik Prof
Akalya AP
5 rows selected.
Select a particular row to view from database using some condition
SQL> select * from emp where empno=3;

---------- -------------------- ------------- ---------- ----------
3 Gugan ASP 1 12000
Select rows from database using some condition
SQL> select * from emp where job=’ASP’ AND sal>10000;

---------- -------------------- ------------- ---------- ----------
2 Arjun ASP 2 12000
3 Gugan ASP 1 12000
RESULT
Thus the database is created and the information is retrieved using SQL queries in RDBMS.
3
EXNO :1B
DATE:
PERFORMING INSERTION, DELETION, MODIFYING, ALTERING, UPDATING
AND VIEWING RECORDS BASED ON CONDITIONS.
AIM:
To study DML commands in RDBMS.
Create table:
SQL>Create table persons( pid number(5), firstname VarChar(15),lastname VarChar(15),

address VarChar(25),city varchar(10));
INSERT COMMAND
Insert command is used to insert values into table
Inserting a single row into a table:
Syntax:SQL>insert into <table name> values (value list)
Inserting more than one record using a single insert commands:
Syntax: SQL>insert into <table name> values (&col1, &col2, ….)
Skipping the fields while inserting:
Syntax:SQL>Insert into <tablename(col names to which data’s to be inserted)> values (list of

values);
INSERTING VALES INTO TABLE
Insert a single record into dept table.
SQL>insert into persons values (001,'nelson','raj','no25,annai street','chennai');

1 row created.
Insert more than a record into emp table using a single insert command.
SQL> insert into persons values(&pid,'&firstname','&lastname','&address','&city');

Enter value for pid: 100
Enter value for firstname: niranjan
Enter value for lastname: kumar
4
Enter value for address: 10/25 krishna street

Enter value for city: mumbai
old 1: insert into persons values(&pid,'&firstname ','&lastname','&address','&city ')
new 1: insert into persons values(100,'niranjan','kumar','10/25 krishna street','mumbai')
1 row created.
SQL> /
Enter value for pid: 102
Enter value for firstname: arjun
Enter value for lastname: kumar
Enter value for address: 30 sundaram street
Enter value for city: coimbatore
new 1: insert into persons values(102,'arjun','kumar','30 sundaram street','coimbatore')
1 row created.
SQL> /
Enter value for pid:300
Enter value for firstname: gugan
Enter value for lastname: chand
Enter value for address: 5/10 mettu street
Enter value for city: coimbatore
new 1: insert into persons values(300,'gugan','chand','5/10 mettu street','coimbatore')
1 row created.
Skipping the fields while inserting:
SQL> insert into persons(pid,firstname) values(500,'prabhu');
ALTER COMMAND
Alter command is used to:

1. Add a new column.
2. Modify the existing column definition.
3. To include or drop integrity constraint.
ADD COMMAND
Add the new column to the existing table.
Syntax:
SQL>alter table tablename add/modify (attribute datatype(size));
5
Example:
SQL>Alter table persons add (phoneno char (20));

Table altered.
SQL>alter table persons add primary key(pid);

Table altered.
MODIFY COMMAND
Modify the existing column definition
Syntax :SQL>alter table <tablename> modify(columnname constraint);

SQL>alter table <tablename>modify(columnname datatype);
Example:
SQL> alter table persons modify(firstname not null);

Table altered.
SQL> alter table persons modify(firstname varchar(20));
Table altered.
UPDATE COMMAND
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.
Syntax:
SQL>update tablename set field=values where condition;
Example:
SQL>update persons set phoneno = 999999999 where empno=100;
Select Commands
It is used to retrieve information from the table.it is generally referred to as querying the
table.We can either display all columns in a table or only specify column from the table.
Syntax:
SQL> Select * from tablename; // This query selects all rows from the table.
Example:
SQL>Select * from persons;
6
PID FIRSTNAME LASTNAMEADDRESS CITY PHONENO

---------- -------------------- ----------------------- -------------------- ----------- ------------------
001 nelson raj no25,annai street Chennai
100 niranjan kumar 10/25 krishna street Mumbai 999999999
102 arjun kumar 30 sundaram street coimbatore
300 gugan chand 5/10 mettu street Coimbatore
500 prabhu
The retrieval of specific columns from a table:
It retrieves the specified columns from the table
Syntax: SQL>Select column_name1, …..,column_namen from table name;
Example: SQL>Select pid, firstname from persons;
PID FIRSTNAME
---------- --------------------
001 nelson
100 niranjan
102 arjun
300 gugan
500 prabhu
Elimination of duplicates from the select clause:
It prevents retrieving the duplicated values .Distinct keyword is to be used.
Syntax:SQL>Select DISTINCT col1, col2 from table name;
Example:
SQL>Select DISTINCT lastname from persons;
LASTNAME
---------------------
raj
kumar
chand
Select command with where clause:
To select specific rows from a table we include ‘where’ clause in the select command. It
can appear only after the ‘from’ clause.
Syntax: SQL>Select column_name1, …..,column_name n from table name where condition;
7
Example: SQL>Select firstname, lastname from persons where pid>200;
FIRSTNAME LASTNAME
----------------------------------------------
nelson raj
niranjan kumar
arjun kumar
gugan chand
prabhu
Select command with order by clause:
Syntax: SQL>Select column_name1, …..,column_namen from table name where condition

orderby colmnname;
Example:
SQL>Select firstname, lastname from persons order by pid;
FIRSTNAME LASTNAME
-------------------- -----------------------
nelson raj
niranjan kumar
arjun kumar
gugan chand
prabhu
Select command to create a table:
Syntax:
SQL>create table tablename as select * from existing_tablename;
Example:
SQL>create table persons1 as select * from persons;
Table created
Select command to insert records:
Syntax: SQL>insert into tablename ( select columns from existing_tablename);
Example: SQL>insert into persons1 ( select * from persons);
8
---------- -------------------- ----------------------- -------------------- ----------- ------------------

001 nelson raj no25,annai street Chennai
102 arjun kumar 30 sundaram street coimbatore
300 gugan chand 5/10 mettu street Coimbatore
500 prabhu
Select command using IN keyword:
Syntax: SQL>Select column_name1, …..,column_namen from table name where colmnname IN

(value1,value2);
Example: SQL>Select * from persons where pid in (100,500);

(OR)
SQL>Select * from persons where (pid=100 OR pid=500);

---------- -------------------- ----------------------- -------------------- ----------- ------------------
500 prabhu
Select command using BETWEEN keyword:
Syntax: SQL>Select column_name1, …..,column_namen from table name where colmnname

BETWEEN value1 AND value2;
Example: SQL>Select * from persons where pid between 100 and 500;

---------- -------------------- ----------------------- -------------------- ----------- ------------------
500 prabhu
Select command using pattern:
Syntax: SQL>Select column_name1, …..,column_namen from table name where colmnname

LIKE ‘% or _‘;
Example: SQL>Select * from persons where firstname like ‘nir_n%’;

---------- -------------------- ----------------------- -------------------- ----------- ------------------
Renaming the fieldname at the time of display using select statement:
9
Syntax: SQL>Select old_column_name new_column_name from table name where condition;

Example: SQL>Select pid personid from persons;
PERSONID
-----------------
001
100
Select command to retrieve null values:
Syntax: SQL>Select column_namefrom table name where column_name is NULL ;
Example: SQL>Select * from persons where lastname is null;

---------- -------------------- ----------------------- -------------------- ----------- ------------------
500 prabhu
DROP TABLE
It will delete the table .
Syntax: SQL>DROP TABLE <TABLE NAME>;

Example: SQL>drop table persons;
TRUNCATE TABLE
If there is no further use of records stored in a table and the structure has to be retained then the
records alone can be deleted.
Syntax: TRUNCATE TABLE <TABLE NAME>;
Example: SQL>Truncate table persons;
DELETE COMMAND
Syntax: SQL>Delete from table where conditions;
Example: SQL>delete from persons where pid=500;
10
TCL COMMANDS:
Syntax:
SAVEPOINT:SQL>SAVEPOINT<SAVE POINT NAME>;

ROLLBACK: SQL>ROLL BACK <SAVE POINT NAME>;
COMMIT: SQL>Commit;
Example:
SQL> SAVEPOINT S1;

Savepoint created.

---------- ------- -------- ---------- ----------
1 Mathi AP 1 10000
2 Arjun ASP 2 15000
3 Gugan ASP 1 15000
4 Karthik Prof 2 30000
SQL> INSERT INTO EMP VALUES(5,'Akalya','AP',1,10000);
1 row created.

---------- ------- -------- ---------- ----------
1 Mathi AP 1 10000
2 Arjun ASP 2 15000
3 Gugan ASP 1 15000
5 Akalya AP 1 10000
SQL> rollback to s1;

(OR)
SQL>roll back s1;

---------- ------- -------- ---------- ----------
1 Mathi AP 1 10000
2 Arjun ASP 2 15000
11
3 Gugan ASP 1 15000

SQL> COMMIT;
Commit complete.
Data Control Language (DCL Commands)
Grant :
Syntax:
GRANT privilege_name ON object_name TO {user_name |PUBLIC |role_name} [WITH
GRANT OPTION];
Example :
SQL>grant select on emp to public;

SQL>grant select on emp to secit01;
Revoke :
Syntax :
SQL> revoke privilege_name on object_name from {user_name|public|role_name}
Example:
SQL> revoke select on emp from public ;

SQL> revoke select on emp from secit01;
RESULT:
Thus the database has been created and the data has been inserted, deleted, modified, altered,
updated and records are viewed based on conditions.
12
EXNO :2
DATE:
SIMPLE QUERIES, NESTED QUERIES, SUB QUERIES AND JOINS
AIM:
To create relationship between the databases using Join Queries in RDBMS.
Nested Queries:
Nesting of queries one within another is known as a nested queries.
Example:

---------- --------- ---------- ---------- ----------
100 mathi lect 1 12000
101 arjun asp 1 12000
102 gugan AP 1 10000
103 yuva lect 2 20000
108 ganesh Ap 2 30000
SQL> select empno,ename,job from emp where sal>(select sal from emp where ename=
'arjun');
EMPNO ENAME JOB

---------- --------------- ----------
103 yuva lect
108 ganesh Ap
Subqueries
The query within another is known as a subquery. A statement containing subquery is called
parent statement. The rows returned by subquery are used by the parent statement.
SQL> select ename,sal from emp where sal>(select min(sal) from emp where job like 'A%');
ENAME SAL
--------------- ----------
mathi 12000
arjun 12000
yuva 20000
ganesh 30000
13
Example:
SQL> select ename, eno, from employee where salary <any (select salary from employee where
deptno =1);
Correlated subquery
A subquery is evaluated once for the entire parent statement whereas a correlated subquery is
evaluated once per row processed by the parent statement.
Example:
select * from emp x where x.salary > (select avg(salary) from emp where deptno =x.deptno);
Relating Data through Join Concept
The purpose of a join concept is to combine data spread across tables. A join is actually
performed by the ‘where’ clause which combines specified rows of tables.
Syntax:
select columns from table1, table2 where logical expression;
Types of Joins
1. Simple Join
2. Self Join
3. Outer Join
1.Simple Join
It is the most common type of join. It retrieves the rows from 2 tables having a common
column and is further classified into

---------- -------------------- ------------- ---------- ----------
1 Mathi AP 1 10000
2 Arjun ASP 2 12000
3 Gugan ASP 1 12000
10 xxx Lect 2 10000
SQL> select * from dept;
14
DEPTNO DNAME LOC

---------- -------------- -------------
1 ACCOUNTING NEW YORK
2 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
a) Equi-join
A join, which is based on equalities, is called equi-join.
Example:
select * from item, cust where item.id=cust.id;
In the above statement, item-id = cust-id performs the join statement. It retrieves rows from both
the tables provided they both have the same id as specified by the where clause. Since the where
clause uses the comparison operator (=) to perform a join, it is said to be equijoin. It combines
the matched rows of tables.
SQL> select * from emp,dept where emp.deptno=dept.deptno;
EMPNO ENAME JOB DEPTNO SAL DEPTNO DNAME LOC

---------- ------------ ---------- --------------- ---------- ---------- -------------- - ------------
1 Mathi AP 1 10000 1 ACCOUNTING NEW YORK
2 Arjun ASP 2 12000 2 RESEARCH DALLAS
3 Gugan ASP 2 20000 2 RESEARCH DALLAS
4 Karthik AP 1 15000 1 ACCOUNTING NEW YORK
b) Non Equi-join
It specifies the relationship between columns belonging to different tables by making use of
relational operators other than’=’.
Example:
select * from item, cust where item.id<cust.id;
SQL> select * from emp,dept where emp.deptno!=dept.deptno;
Table Aliases
Table aliases are used to make multiple table queries shorted and more readable. We give an
alias name to the table in the ‘from’ clause and use it instead of the name throughout the query.
15
2.Self join
Joining of a table to itself is known as self-join. It joins one row in a table to another. It can
compare each row of the table to itself and also with other rows of the same table.
Example:
select * from emp x ,emp y where x.salary >= (select avg(salary) from x.emp where x. deptno
=y.deptno);
3.Outer Join
It extends the result of a simple join. An outer join returns all the rows returned by simple join as
well as those rows from one table that do not match any row from the table. The symbol(+)
represents outer join.
Example:
select ename, job, dname from emp, dept where emp.deptno (+) =dept.deptno;
Different SQL JOINs
Before we continue with examples, we will list the types the different SQL JOINs you can use:
 INNER JOIN: Returns all rows when there is at least one match in BOTH tables
 LEFT JOIN: Return all rows from the left table, and the matched rows from the right
table
 RIGHT JOIN: Return all rows from the right table, and the matched rows from the left
table
 FULL JOIN: Return all rows when there is a match in ONE of the tables
SQL INNER JOIN
The INNER JOIN keyword selects all rows from both tables as long as there is a match between
the columns in both tables.
Syntax:
SQL> select * from product;
Product_id Product_name Supplier_name Unit_price

------------- ------------------ ------------------ ---------------
100 camera Nikon 300
16
101 television onida 100

102 refrigerator LG 150
103 ipod apple 75
104 mobile nokia 50
SQL>Select * from order_items;
Order_id Product_id Total_units Customer

------------- ----------------- ----------------- -------------
5100 104 30 infosys
5101 102 5 satyam
5102 103 25 wipro
5103 101 10 tcs
SQL> select Order_id,Product_name,Unit_price,Supplier_name,Total_units from

product,order_items where order_items.Product_id=product. Product_id;
Order_id Product_name Unit_price Supplier_name Total_units

------------ -------------------- --------------- ------------------------ -----------------
5103 television 100 onida 10
5101 refrigerator 150 LG 5
5102 ipod 75 apple 25
5100 mobile 50 nokia 30
Outer Join
It extends the result of a simple join. An outer join returns all the rows returned by simple join as
well as those rows from one table that do not match any row from the table. The symbol(+)
represents outer join.
LEFT OUTER JOIN
The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no
matches in the right table (table_name2)
SQL> SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON

table_name1.column_name=table_name2.column_name
SQL> select p.Product_id,p.Product_name,o.Order_id,o.Total_units from order_items o LEFT

JOIN product p ON o.Product_id=p. Product_id;
(OR)
SQL> select p.Product_id,p.Product_name,o.Order_id,o.Total_units from order_items o,product

p where o.Product_id(+)=p. Product_id;
17
Product_id Product_name Order_id Total_units

--------------- ------------------ ------------- -----------------
100 camera
101 television 5103 10
102 refrigerator 5101 5
103 ipod 5102 25
104 mobile 5100 30
RIGHT OUTER JOIN
The RIGHT JOIN keyword Return all rows from the right table (table_name2), even if there are
no matches in the left table (table_name1).
SQL> SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2 ON

SQL> select p.Product_id,p.Product_name,o.Order_id,o.Total_units from order_items o RIGHT

JOIN product p ON o.Product_id=p. Product_id;
(OR)
SQL> select p.Product_id,p.Product_name,o.Order_id,o.Total_units from order_items o,product

p where o.Product_id=p. Product_id(+);

--------------- ------------------ ------------- -----------------
103 ipod 5102 25
104 mobile 5100 30
FULL JOIN
The FULL JOIN keyword return rows when there is a match in one of the tables.
SQL>SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON

SQL> select p.Product_id,p.Product_name,o.Order_id,o.Total_units from order_items o FULL

OUTER JOIN product p ON o.Product_id=p. Product_id;

--------------- ------------------ ------------- -----------------
100 camera
18

103 ipod 5102 25
104 mobile 5100 30
UNION OPERATOR
The union operator is used to combine the result set of two or more select statements.
1) Union
2) Union all
SYNTAX:
SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2.
SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2.
EXAMPLE
SQL>select * from name1;

Name
--------
Sara
Meera
Meena
Sabari
SQL>select * from name2;
Name
--------
Sara
Meena
Raji
Roja
SQL>select * from name1 unoin select * from name2;
Name
--------
Sara
Meena
Meera
Raji
19
Roja
Sabari
SQL>select * from name1 unoin all select * from name2;
Name
--------
Sara
Meera
Meena
Sabari
Sara
Meena
Raji
Roja
MINUS
SQL>select * from name1 MINUS select * from name2;
Name
--------
Meera
Sabari
INTERSECT
SQL>select * from name1 INTERSECT select * from name2;
Name
--------
Meena
Sara
RESULT:
Thus the creating relationship between the databases have be done successfully.
20
EXNO :3
DATE:
VIEWS, SYNONYMS, SEQUENCE
AIM:
To create Views ,Synonyms ,sequences commands in database using SQL queries in RDBMS.
CREATING VIEWS:
A view is a virtual table based on the result-set of an SQL statement. A view contains rows and
columns, just like a real table. The fields in a view are fields from one or more real tables in the
database.
Syntax
create view view_name as select column_name(s) from table_name where condition;
Example:
SQL> create view emp1 as select * from emp where salary>10000;

View created
SQL> select * form emp1;

---------- -------------------- ------------- ---------- ----------
2 Arjun ASP 2 12000
3 Gugan ASP 1 12000
SQL> desc emp1;

Name Null? Type
--------------------------------- -------- ----------------------------
empno NOT NULL number(5)
ename NOT NULL VarChar(15)
job Var Char(10)
deptno NOT NULL number(3)
sal number (5)
Updating a View
View can be updated. If view is already created with same name it will be replaced by create or
replace statement (or) if view already doesn’t exist, it will be created.
21
Syntax
SQL>create or replace view view_name as select column_name(s) from table_name where

condition;
Example:
SQL> create or replace view emp1 as select * from emp where salary>10000;
View created
Dropping a View
Deleting a view can be done with the DROP VIEW command.
Syntax :SQL>drop view view_name;
Example:SQL>drop view emp1;
CREATING SYNONYMS
A synonym is an alternative name for objects such as tables, views, sequences,

storedprocedures, and other database objects.
Syntax :
SQL>create or replace synonym synonym_name for table_name;

example:
SQL>create public employee synonym for emp;

SQL>create or replace public synonym employee FOR emp;
synonym created
Drop synonym
Used to drop the synonym that has been created .
Syntax :
SQL>Drop public synonym synonym_name;
example:
SQL>drop public synonym employee;
22
SEQUENCES
In Oracle, you can create an auto number field by using sequences. A sequence is an
object in Oracle that is used to generate a number sequence. This can be useful when you need to
create a unique number to act as a primary key.
CREATING SEQUENCES
Syntax
CREATE SEQUENCE sequence_name

MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value;
Example:
CREATE SEQUENCE emp_seq
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;
This would create a sequence object called supplier_seq. The first sequence number that it would
use is 1 and each subsequent number would increment by 1 (ie: 2,3,4,...}. It will cache up to 20
values for performance.
SQL>INSERT INTO emp (empno, ename) VALUES (emp_seq.NEXTVAL, 'asha');
Drop Sequence
Once you have created your sequence in Oracle, you might find that you need to remove it from
the database.
23
Syntax
The syntax to a drop a sequence in Oracle is:
SQL>DROP SEQUENCE sequence_name;
sequence_name is the name of the sequence that you wish to drop.
Example
SQL>DROP SEQUENCE emp_seq;
CREATING INDEX
An index can be created in a table to find data more quickly and efficiently.The users cannot see
the indexes, they are just used to speed up searches/queries.Creates an index on a table.
Duplicate values are allowed
Syntax :SQL>CREATE INDEX index_name ON table_name (column_name);
Example
The SQL statement below creates an index named "PIndex" on the "LastName" column in the
"Persons" table:
SQL>create index PIndex ON Persons (LastName);
If you want to create an index on a combination of columns, you can list the column names
within the parentheses, separated by commas:
SQL>create index PIndex ON Persons (LastName, FirstName);
RESULT:
Thus Views, Synonyms, Sequence have been created and updated in RDBMS.
24
EXNO :4
DATE:
IMPLICIT AND EXPLICIT CURSORS
AIM:
To study and execute the Implicit and Explicit Cursors using PL/SQL.
PL/SQL Cursor
When an SQL statement is processed, Oracle creates a memory area known as context area. A
cursor is a pointer to this context area. It contains all information needed for processing the
statement. In PL/SQL, the context area is controlled by Cursor. A cursor contains information on
a select statement and the rows of data accessed by it.
A cursor is used to referred to a program to fetch and process the rows returned by the SQL
statement, one at a time. There are two types of cursors:
 Implicit Cursors
 Explicit Cursors
1) PL/SQL Implicit Cursors
The implicit cursors are automatically generated by Oracle while an SQL statement is executed,
if you don't use an explicit cursor for the statement.
These are created by default to process the statements when DML statements like INSERT,
UPDATE, DELETE etc. are executed.
Orcale provides some attributes known as Implicit cursor's attributes to check the status of DML
operations. Some of them are: %FOUND, %NOTFOUND, %ROWCOUNT and %ISOPEN.
For example: When you execute the SQL statements like INSERT, UPDATE, DELETE then
the cursor attributes tell whether any rows are affected and how many have been affected. If you
run a SELECT INTO statement in PL/SQL block, the implicit cursor attribute can be used to find
out whether any row has been returned by the SELECT statement. It will return an error if there
no data is selected.
The following table soecifies the status of the cursor with each of its attribute.
Attribute Description
Its return value is TRUE if DML statements like INSERT, DELETE and
%FOUND UPDATE affect at least one row or more rows or a SELECT INTO statement
returned one or more rows. Otherwise it returns FALSE.
Its return value is TRUE if DML statements like INSERT, DELETE and
%NOTFOUND
UPDATE affect no row, or a SELECT INTO statement return no rows.
25
Otherwise it returns FALSE. It is a just opposite of %FOUND.

It always returns FALSE for implicit cursors, because the SQL cursor is
%ISOPEN
automatically closed after executing its associated SQL statements.
It returns the number of rows affected by DML statements like INSERT,
%ROWCOUNT
DELETE, and UPDATE or returned by a SELECT INTO statement.
Example
ID NAME AGE ADDRESS SALARY

1 Ramesh 23 Allahabad 20000
2 Suresh 22 Kanpur 22000
3 Mahesh 24 Ghaziabad 24000
4 Chandan 25 Noida 26000
5 Alex 21 Paris 28000
6 Sunita 20 Delhi 30000
Let's execute the following program to update the table and increase salary of each customer by
5000. Here, SQL%ROWCOUNT attribute is used to determine the number of rows affected:
Create procedure:
DECLARE
total_rows number(2);
BEGIN
UPDATE customers
SET salary = salary + 5000;
IF sql%notfound THEN
dbms_output.put_line('no customers updated');
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' customers updated ');
26
END IF;
END;
/
Output:
6 customers updated
PL/SQL procedure successfully completed.
Sql>select * from customers;

Explicit Cursors
The Explicit cursors are defined by the programmers to gain more control over the context area.
These cursors should be defined in the declaration section of the PL/SQL block. It is created on a
SELECT statement which returns more than one row.
Following is the syntax to create an explicit cursor:
Syntax of explicit cursor
Following is the syntax to create an explicit cursor:
CURSOR cursor_name IS select_statement;;
Steps:
You must follow these steps while working with an explicit cursor.
Declare the cursor to initialize in the memory.

Open the cursor to allocate memory.
Fetch the cursor to retrieve data.
27
Close the cursor to release allocated memory.
1) Declare the cursor:
It defines the cursor with a name and the associated SELECT statement.
Syntax for explicit cursor decleration
CURSOR name IS
SELECT statement;
2) Open the cursor:
It is used to allocate memory for the cursor and make it easy to fetch the rows returned by the
SQL statements into it.
Syntax for cursor open:
OPEN cursor_name;
3) Fetch the cursor:
It is used to access one row at a time. You can fetch rows from the above-opened cursor as
follows:
Syntax for cursor fetch:
FETCH cursor_name INTO variable_list;
4) Close the cursor:
It is used to release the allocated memory. The following syntax is used to close the above-
opened cursors.
Syntax for cursor close:
Close cursor_name;
PL/SQL Explicit Cursor Example
Explicit cursors are defined by programmers to gain more control over the context area. It is
defined in the declaration section of the PL/SQL block. It is created on a SELECT statement
which returns more than one row.
28
Let's take an example to demonstrate the use of explicit cursor. In this example, we are using the
already created CUSTOMERS table.
Create customers table and have records:

Create procedure:
Execute the following program to retrieve the customer name and address.
DECLARE
c_id customers.id%type;
c_name customers.name%type;
c_addr customers.address%type;
CURSOR c_customers is
SELECT id, name, address FROM customers;
BEGIN
OPEN c_customers;
LOOP
FETCH c_customers into c_id, c_name, c_addr;
EXIT WHEN c_customers%notfound;
dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
END LOOP;
CLOSE c_customers;
END;
/
Output:
1 Ramesh Allahabad
2 Suresh Kanpur
3 Mahesh Ghaziabad
4 Chandan Noida
5 Alex Paris
6 Sunita Delhi
29
EXNO :5
DATE:
CREATION OF PROCEDURES AND FUNCTIONS
AIM:
To study and execute the procedures and functions using control structures in PL/SQL.
PL/SQL :
A procedure is a block that can take parameters (sometimes referred to as arguments) and be
invoked. Procedures promote reusability and maintainability. Once validated, they can be used in
number of applications. If the definition changes, only the procedure are affected, this greatly
simplifies maintenance. Modularized program development: · Group logically related statements
within blocks. · Nest sub-blocks inside larger blocks to build powerful programs. · Break down a
complex problem into a set of manageable well defined logical modules and implement the
modules with blocks.
KEYWORDS AND THEIR PURPOSES
 REPLACE: It recreates the procedure if it already exists.

 PROCEDURE: It is the name of the procedure to be created.
 ARGUMENT: It is the name of the argument to the procedure. Parenthesis can be
omitted if no arguments are present.
 IN: Specifies that a value for the argument must be specified when calling the procedure
ie., used to pass values to a sub-program. This is the default parameter.
 OUT: Specifies that the procedure passes a value for this argument back to its calling
environment after execution ie. used to return values to a caller of the sub-program.
 INOUT: Specifies that a value for the argument must be specified when calling the
procedure and that procedure passes a value for this argument back to its calling
environment after execution.
 RETURN: It is the data type of the function’s return value because every function must
return a value, this clause is required.
SYNTAX:
PROCEDURES :
create or replace procedure <procedure name> (argument {in,out,inout} datatype ) {is,as}
variable declaration;
constant declaration;
begin PL/SQL subprogram body;
30
exception exception PL/SQL block;

end;
Tables used:
SQL> select * from ititems;
ITEMID ACTUALPRICE ORDID PRODID

--------- ----------------------- ----------- -------------
101 2000 500 201
102 3000 1600 202
103 4000 600 202
PROGRAM FOR GENERAL PROCEDURE – SELECTED RECORD‘S PRICE IS INCREMENTED BY

500 , EXECUTING THE PROCEDURE CREATED AND DISPLAYING THE UPDATED TABLE
SQL> create procedure itsum(identity number, total number) is price number;

2 null_price exception;
3 begin
4 select actualprice into price from ititems where itemid=identity;
5 if price is null then
6 raise null_price;
7 else
8 update ititems set actualprice=actualprice+total where itemid=identity;
9 end if;
10 exception
11 when null_price then
12 dbms_output.put_line('price is null');
13 end;
14 /
Procedure created.
SQL> exec itsum(101, 500);
SQL> select * from ititems;
ITEMID ACTUALPRICE ORDID PRODID

--------- ---------------------- --------- ---------
101 2500 500 201
102 3000 1600 202
103 4000 600 202
31
PROCEDURE FOR IN PARAMETER – CREATION, EXECUTION
SQL> set serveroutput on;

SQL> create procedure yyy (a IN number) is price number;
2 begin
3 select actualprice into price from ititems where itemid=a;
4 dbms_output.put_line('Actual price is ' || price);
5 if price is null then
6 dbms_output.put_line('price is null');
7 end if;
8 end;
9/
Procedure created.
SQL> exec yyy(103); Actual price is 4000
PROCEDURE FOR OUT PARAMETER – CREATION, EXECUTION
SQL> set serveroutput on;

SQL> create procedure zzz (a in number, b out number) is identity number;
2 begin
3 select ordid into identity from ititems where itemid=a;
4 if identity<1000 then
5 b:=100;
6 end if;
7 end;
8/
Procedure created.
SQL> declare
2 a number;
3 b number;
4 begin
5 zzz(101,b);
6 dbms_output.put_line('The value of b is '|| b);
7 end;
8/
The value of b is 100 PL/SQL procedure successfully completed.
32
PROCEDURE FOR INOUT‘ PARAMETER – CREATION, EXECUTION
SQL> create procedure itit ( a in out number) is

2 begin
3 a:=a+1;
4 end;
5/
Procedure created.
SQL> declare
2 a number:=7;
3 begin
4 itit(a);
5 dbms_output.put_line(„The updated value is „||a);
6 end;
7/
The updated value is 8
FUNCTIONS:
SYNTAX:
create or replace function <function name> (argument in datatype,……) return datatype {is,as}
variable declaration;
constant declaration;
begin PL/SQL subprogram body;
exception exception PL/SQL block;
end;
PROGRAM FOR FUNCTION AND IT‘S EXECUTION

SQL> create function trainfn (trainnumber number) return number is
2 trainfunction ittrain.tfare % type;
3 begin
4 select tfare into trainfunction from ittrain where tno=trainnumber;
5 return(trainfunction);
6 end;
7/
Function created.
SQL> declare
33
2 total number;
3 begin
4 total:=trainfn (1001);
5 dbms_output.put_line('Train fare is Rs. '||total);
6 end;
7/
Train fare is Rs.550

FACTORIAL OF A NUMBER USING FUNCTION — PROGRAM AND EXECUTION

SQL> create function itfact (a number) return number is
2 fact number:=1;
3 b number;
4 begin
5 b:=a;
6 while b>0
7 loop
8 fact:=fact*b;
9 b:=b-1;
10 end loop;
11 return(fact);
12 end;
13 /
Function created.
SQL> declare
2 a number:=7;
3 f number(10);
4 begin
5 f:=itfact(a);
6 dbms_output.put_line(„The factorial of the given number is‟||f);
7 end;
8/
The factorial of the given number is 5040
RESULT:
Thus the procedures and functions using control structures were studied and executed in
PL/SQL.
34
EXNO : 6
DATE:
CREATION OF DATABASE TRIGGERS
AIM:
To study and execute Triggers in RDBMS
TRIGGER
A Trigger is a stored procedure that defines an action that the database automatically take when
some database-related event such as Insert, Update or Delete occur.
TRIGGER VS. PROCEDURE VS CURSOR

TRIGGER PROCEDURES CURSORS
These are named PL/SQL These are named PL/SQL These are named PL/SQL blocks.
blocks. blocks.
These are invoked User as per need invokes These can be created both
automatically. these. explicitly and implicitly.
These can’t take These can take These can take parameters.
parameters. parameters.
These are stored in These are stored in These are not stored in database.
database. database.
TYPES OF TRIGGERS The various types of triggers are as follows,

 Before: It fires the trigger before executing the trigger statement.
 After: It fires the trigger after executing the trigger statement.
 For each row: It specifies that the trigger fires once per row.
 For each statement: This is the default trigger that is invoked. It specifies that the trigger
fires once per statement.
VARIABLES USED IN TRIGGERS

 new
 old
These two variables retain the new and old values of the column updated in the database. The
values in these variables can be used in the database triggers for data manipulation
Row Level Trigger vs. Statement Level Trigger:
Row Level Trigger Statement Level Trigger

These are fired for each row affected by the These are fired once for the statement instead
DML statement. of the no of rows modified by it.
35
These are used for generating/checking the These are used for generated the summary
values begin inserted or updated. information.
Before trigger vs. after trigger
Before Triggers After Triggers

Before triggers are fired before the DML After triggers are fired after the DML
statement is actually executed. statement has finished execution.
Sytax:
Create or replace trigger <trg_name>Before /After Insert/Update/Delete
[of column_name, column_name….]
on<table_name>
[for each row]
[when condition]
begin
---statement
End
Create a trigger that insert current user into a username column of an existing table
SQL> create table itstudent4(name varchar2(15),username varchar2(15));
Table created.
SQL> create or replace trigger itstudent4 before insert on itstudent4 for each row
2 declare
3 name varchar2(20);
4 begin
5 select user into name from dual;
6 :new.username:=name;
7 end;
8/
Trigger created.
Output:
SQL> insert into itstudent4 values('&name','&username');
Enter value for name: akbar
Enter value for username: ranjani
old 1: insert into itstudent4 values('&name','&username')
new 1: insert into itstudent4 values('akbar','ranjani')
1 row created.
SQL> /
Enter value for name: suji
Enter value for username: priya
old 1: insert into itstudent4 values('&name','&username')
36
new 1: insert into itstudent4 values('suji','priya')

1 row created.
SQL> select * from itstudent4;
NAME USERNAME
--------------- ---------------
akbar SCOTT
suji SCOTT
Create a Simple Trigger that does not allow Insert Update and Delete Operations on the
Table
Table used:
SQL> select * from itempls;
ENAME EID SALARY

---------- --------- ---------
xxx 11 10000
yyy 12 10500
zzz 13 15500
Trigger:
SQL> create trigger ittrigg before insert or update or delete on itempls for each row
2 begin
3 raise_application_error(-20010,'You cannot do manipulation');
4 end;
5/
Trigger created.
Output:
SQL> insert into itempls values('aaa',14,34000);
insert into itempls values('aaa',14,34000)
*
ERROR at line 1:
ORA-20010: You cannot do manipulation ORA-06512:at"STUDENT.ITTRIGG", line 2
ORA-04088: error during execution of trigger 'STUDENT.ITTRIGG'
SQL> delete from itempls where ename='xxx';
delete from itempls where ename='xxx'
*
ERROR at line 1:
ORA-20010: You cannot do manipulation
37
ORA-06512: at "STUDENT.ITTRIGG", line 2

ORA-04088: error during execution of trigger 'STUDENT.ITTRIGG'
SQL> update itempls set eid=15 where ename='yyy';
update itempls set eid=15 where ename='yyy'
develop a query to Drop the Created Trigger

SQL> drop trigger ittrigg;
Trigger dropped.
RESULT:
Thus the creation of triggers for various events such as insertion, updation, etc., was created,
performed and executed successfully.
38
EXNO :7
DATE:
PL/SQL BLOCK HANDLES ALL TYPES OF EXCEPTIONS
AIM:
To write a PL/SQL block to handle all types of exceptions.
PL/SQL - EXCEPTIONS
An error condition during a program execution is called an exception in PL/SQL. PL/SQL

supports programmers to catch such conditions using EXCEPTION block in the program and an
appropriate action is taken against the error condition. There are two types of exceptions:
 System-defined exceptions
 User-defined exceptions
Syntax for Exception Handling
The General Syntax for exception handling is as follows. Here you can list down as many as
exceptions you want to handle. The default exception will be handled using WHEN others
THEN:
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling goes here >
WHEN exception1 THEN
exception1-handling-statements
........
WHEN others THEN
END;
39
Example
SQL> create table customer(custid number(5),name varchar(20),address varchar(30));
Insert values into table.
DECLARE
c_id customer.custid%type := 10;
c_name customer.name%type;
c_addr customer.address%type;
BEGIN
SELECT name, address INTO c_name, c_addr
FROM customer
WHERE custid = c_id;
DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name);

DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No such customer!');
WHEN others THEN
dbms_output.put_line('Error!');
END;
/
OUTPUT
No such customer!
Raising Exceptions
Exceptions are raised by the database server automatically whenever there is any internal
database error, but exceptions can be raised explicitly by the programmer by using the command
RAISE. Following is the simple syntax of raising an exception:
DECLARE
exception_name EXCEPTION;
BEGIN
IF condition THEN
RAISE exception_name;
END IF;
EXCEPTION
WHEN exception_name THEN
statement;
END;
40
User-defined Exceptions
PL/SQL allows you to define your own exceptions according to the need of your program. A
user-defined exception must be declared and then raised explicitly, using either a RAISE
statement or the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR.
The syntax for declaring an exception is:
DECLARE
my-exception EXCEPTION;
Example:
This program asks for a customer ID, when the user enters an invalid ID, the exception
invalid_id is raised.
DECLARE
c_id customer.custid%type :=&cc_id;
c_name customer.name%type;
c_addr customer.address%type;
-- user defined exception

ex_invalid_id EXCEPTION;
BEGIN
IF c_id <= 0 THEN
RAISE ex_invalid_id;
ELSE
SELECT name, address INTO c_name, c_addr
FROM customer
WHERE custid = c_id;
DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name);
DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);
END IF;
EXCEPTION
WHEN ex_invalid_id THEN
dbms_output.put_line('ID must be greater than zero!');
WHEN no_data_found THEN
dbms_output.put_line('No such customer!');
WHEN others THEN
dbms_output.put_line('Error!');
END;
/
41
OUTPUT:
Enter value for cc_id: -6 (let's enter a value -6)

old 2: c_id customers.id%type := &cc_id;
new 2: c_id customers.id%type := -6;
ID must be greater than zero!
RESULT:
Thus the PL/SQL block is created to handle all types of exceptions and are well executed.
42
EXNO : 8
DATE:
DATABASE DESIGN USING NORMALIZATION

Aim:
To design database using normalization.
Definition and Syntax:
Normalization
Normalization is the process of minimizing redundancy in a database.A relation schema is

taken and subjected to a series of tests to verify whether it is in normal form or not.This process proceeds
in top-down fashion evaluating each relation against criteria and decomposing relations.It is proposed by
Codd.
Normalization of data is a process of analyzing the given relation schemas based on

their functional dependencies and primary keys to achieve the desirable properties of
 minimizing redundancy
 minimizing the insertion,deletion and update anomalies.
A normal form is a state of a table that results from applying simple rules regarding functional
dependencies to that table.The various types of normal form are:
 First Normal Form(1NF): The multi-valued should be removed i.e elimination of redundant
groups.
 Second Normal Form(2NF): The partial functional dependencies have to be removed i.e
elimination of redundant data.
 Third Normal Form(3NF): The transitive dependencies have to be removed i.e elimination of
columns not dependent on the key.
 Boyce-Codd Normal Form(BCNF): The remaining anomalies that result from functional
dependencies are removed.
 Fourth Normal Form(4NF): Multi-valued dependencies are removed i.e isolation of

independent multiple relationships.
43
 Fifth Normal Form(5NF): Any remaining anomalies are removed.Here we isolate semantically
related multiple relationships.
First Normal Form(1NF)
1NF states that the domain of an attribute must include only atomic
(simple,indivisible) values and that value of any attribute in a tuple must be a single value from the
domain of that attribute.Hence 1NF disallows multi-valued attributes,composite attributes.It disallows
“relations
within relations”.
Second Normal Form(2NF)
A relation is said to be in 2NF if it is already in 1NF and it has no partial dependency. 2NF is
based on the concept of full functional dependency.
A functional dependency(FD) XY is full functional dependency if (X-(A))Y does not hold
dependency any more if AX.
A functional dependency XY is partial dependency if A can be removed which does not affect the
dependency i.e. (X-(A))Y holds.
A relation is in 2NF if it is in 1NF and every non-primary key attribute is fully and functionally
dependent on primary key.
A relation in the 1NF will be in the 2NF if one of the following conditions is satisfied:
The primary key consist of only one attribute.
No non-key attribute exist in relation i.e. all the attributes in the relation are components of the primary
key.
Every non-key attribute is FD on full set of primary key attributes.
Third Normal Form(3NF)
A relation is said to be in 3NF if it is already in 2NF and it has no transitive dependency.
A FD XY in a relation schema R is a transitive dependency if there is a set of attributes Z

that is neither a candidate key nor a subset of any key of the relation and both XZ and ZY hold.
44
Boyce-Codd Normal Form(BCNF)
A relation is said to be in BCNF if it is already in 3NF and every determinant is a

candidate key.It is stronger version of 3NF.Every relation in BCNF is also in 3NF but the vice-
versa may not hold.
The formal definition of BCNF differs from the definition of 3NF.A relatio schema
R is in BCNF if whenever a non-trivial functional dependency XA holds in R,then X is a
superkey of R.
Procedure:
1.Create a type ‘Address’ to represent composite attribute.

2. Create the table
3. Insert the values in the table.
4. Draw the E-R diagram for the table.
5. Convert the given table to the normalized form.
a) Converting a table to 1NF:
To convert a table to 1NF removes all the multi valued & composite attributes
from the table.
b) Converting a Table to 2NF:
i) Find and remove attributes that are functionally dependent on only a part
of the key and not on the whole key. Place them in a different
table.Group the remaining attributes.
FIRST NORMAL FORM:
SQL>create type address as object(sno number(5),

2 sname varchar2(20),
3 city varchar2(20),
4 state varchar2(20));
/
Type created.
SQL>create table Employees(eno number(3) primary key,

2 enmae varchar2(20),
3 eadd address,
4 sal number(7,2))
45
SQL> /
Table created.
SQL> desc employees

Name Null? Type
----------------------------------------- -------- --------------------------
ENO NOT NULL NUMBER(3)
ENMAE VARCHAR2(20)
EADD ADDR
SAL NUMBER(7,2)
SQL> insert into employees values(&eno,'&enmae',address(&sno,'&sname','&city','&state'),&sal);

Enter value for eno: 001
Enter value for enmae: anbu
Enter value for sno: 12
Enter value for sname: Ist street
Enter value for city: chennai
Enter value for state: tamilnadu
old 1: insert into employees values(&eno,'&enmae',address(&sno,'&sname','&city','&state'),&sal)
new 1: insert into employees values(001,'anbu',address(12,'Ist street','chennai','tamilnadu'),10000)
1 row created.
SQL> /
Enter value for enmae: balu
Enter value for sname: car street
Enter value for city: madurai
Enter value for state: tamilnadu
new 1: insert into employees values(002,'balu',address(13,'car street','madurai','tamilnadu'),10000)
1 row created.
SQL> /
Enter value for enmae: chiru
Enter value for sname: 9th street
46
Enter value for city: hyderabad

Enter value for state: andhra pradesh
new 1: insert into employees values(003,'chiru',address(10,'9th street','hyderabad','andhra pradesh')
1 row created.
SQL> select * from employees;
ENO ENMAE EADD(SNO, SNAME, CITY, STATE) SAL

---------- ---------- ------------------------------------------------------------
1 anbu ADDR(12, 'Ist street', 'chennai', 'tamilnadu') 10000
2 balu ADDR(13, 'car street', 'madurai', 'tamilnadu') 10000
3 chiru ADDR(10, '9th street', 'hyderabad', 'andhra pradesh') 15000
Normalizing the table to 1NF:
SQL> create table en1 as select eno, ename ,sal from employees;
Table created.
SQL> alter table en1 add primary key(eno);
Table altered.
SQL> desc en1

Name Null? Type
----------------------------------------- -------- ----------------------------
ENAME VARCHAR2(15)
SAL NUMBER(7,2)
SQL> create table en2 as select eno,eadd from employees;
Table created.
SQL> alter table en2 add foreign key(eno) references en1(eno);
Table altered.
47
SQL> desc en2

Name Null? Type
----------------------------------------- -------- ----------------------------
ENO NUMBER(3)
EADD ADDR
Normalizing to 1NF:
employee
Eno Ename Eadd Sal
1NF
Emp1 emp2
Eno eadd
Eno Ename Sal
SECOND NORMAL FORM:
SQL> create table empproject(eno number(3) primary key,

2 ename varchar2(20),
3 pno number(3) unique,
4 pname varchar2(20),
5 hours number(3));
Table created.
SQL> desc empproject

Name Null? Type
----------------------------------------- -------- ----------------------------
ENAME VARCHAR2(20)
PNO NUMBER(3)
PNAME VARCHAR2(20)
HOURS NUMBER(3)
48
SQL> insert into empproject values(&eno,'&ename',&pno,'&pname',&hours);

Enter value for ename: raja
Enter value for pno: 12
Enter value for pname: compilers
Enter value for hours: 12
old 1: insert into empproject values(&eno,'&ename',&pno,'&pname',&hours)
new 1: insert into empproject values(101,'raja',12,'compilers',12)
1 row created.
SQL> /
Enter value for ename: ragu
Enter value for pname: atm
new 1: insert into empproject values(102,'ragu',13,'atm',24)
1 row created.
SQL> /
Enter value for ename: sunil
Enter value for pname: robotics
new 1: insert into empproject values(103,'sunil',14,'robotics',15)
1 row created.
SQL> select * from empproject;
ENO ENAME PNO PNAME HOURS

---------- -------------------- ---------- -------------------- ----------
101 raja 12 compilers 12
102 ragu 13 atm 24
103 sunil 14 robotics 15
49
SQL>create table ep1 as select eno,ename from empproject;
Table created.
SQL>alter table ep1 add primary key(eno);
Table altered.
SQL> desc ep1
Name Null? Type

----------------------------------------- -------- ----------------------------
ENAME VARCHAR2(20)
SQL>create table ep2 as select pno,pname from empproject;
Table created.
SQL>alter table ep3 add primary key(pno);
Table altered.
SQL> desc ep2

Name Null? Type
----------------------------------------- -------- ----------------------------
PNO NOT NULL NUMBER(3)
PNAME VARCHAR2(20)
SQL>create table ep3 as select eno,pno,hours from empproj;
Table created.
SQL>alter table ep3 add primary key(eno);
50
Table altered.
SQL>alter table ep3 add unique(pno);
Table altered.
SQL> desc ep3

Name Null? Type
----------------------------------------- -------- ----------------------------
PNO NUMBER(3)
HOURS NUMBER(3)
Normalizing to 2NF:
Eno Ename pno pname hours
 2NF
Ep1
eno Ename
Ep2
Pname
pno
Ep3
pno Hours
eno
THIRD NORMAL FORM:
SQL>create table empdept (eno number(3) primary key,

2 ename varchar2(20),
3 sal number(7),
4 dno number(3),
5 dname varchar2(20));
Table created.
51
SQL> desc empdept

Name Null? Type
----------------------------------------- -------- ----------------------------
ENAME VARCHAR2(20)
SAL NUMBER(7)
DNO NUMBER(3)
DNAME VARCHAR2(20)
SQL> insert into empdept values(&eno,'&ename',&sal,&dno,'&dname');

Enter value for ename: ravi
Enter value for dno: 1
Enter value for dname: cse
old 1: insert into empdept values(&eno,'&ename',&sal,&dno,'&dname')
new 1: insert into empdept values(101,'ravi',10000,1,'cse')
1 row created.
SQL> /
Enter value for ename: ragu
Enter value for dno: 2
Enter value for dname: eee
old 1: insert into empdept values(&eno,'&ename',&sal,&dno,'&dname')
new 1: insert into empdept values(102,'ragu',20000,2,'eee')
1 row created.
SQL> select * FROM EMPDEPT;
ENO ENAME SAL DNO DNAME

---------- -------------------- ---------- ---------- --------------------
101 ravi 10000 1 cse
102 ragu 20000 2 eee
SQL>create table ed1 as select eno,ename,sal,dno from empdept;
52
Table created.
SQL>alter table ed1 add primary key(eno);
Table altered.
SQL> desc ed1

Name Null? Type
----------------------------------------- -------- ----------------------------
ENAME VARCHAR2(20)
SAL NUMBER(7,2)
DNO NUMBER(3)
SQL>create table ed2 as select dno,dname from empdept;

Table created.
SQL>alter table ed2 add primary key(dno);

SQL> desc ed2
Name Null? Type

----------------------------------------- -------- ----------------------------
DNO NOT NULL NUMBER(3)
DNAME VARCHAR2(20)
SQL>alter table ed1 add foreign key(dno) references ed2(dno);
Table altered.
53
Normalizing to 3NF:
Empdept
ename Sal dno

Eno dname

 3NF
Ed1
eno sal dno
ename
Ed2
Dname
Dno
Result:
Thus the database was designed using Normalization.
54
EXNO : 9
DATE:
NOSQL DATABASE TOOLS
AIM:
To Create Document, column and graph based data using NOSQL database tools.
{
"_id": 1,
"first_name": "Tom",
"email": "tom@example.com",
"cell": "765-555-5555",
"likes": [
"fashion",
"spas",
"shopping"
],
"businesses": [
{
"name": "Entertainment 1080",
"partner": "Jean",
"status": "Bankrupt",
"date_founded": {
"$date": "2012-05-19T04:00:00Z"
}
},
{
"name": "Swag for Tweens",
"date_founded": {
"$date": "2012-11-01T04:00:00Z"
}
}
]
}
"_id": 2,
"first_name": "Donna",
"email": "donna@example.com",
"spouse": "Joe",
"likes": [
55
"spas",
"shopping",
"live tweeting"
],
"businesses": [
{
"name": "Castle Realty",
"status": "Thriving",
"date_founded": {
"$date": "2013-11-21T04:00:00Z"
}
}
]
}
Users
ID first_name email cell

1 Tom tom@example.com 765-555-5555
Likes
ID user_id like
10 1 fashion
11 1 spas
12 1 shopping
Businesses
ID user_id name partner status date_founded

20 1 Entertainment 1080 Jean Bankrupt 2011-05-19
21 1 Swag for Tweens NULL NULL 2012-11-01
RESULT:
Thus the Document, column and graph based data using NOSQL database tools is successfully
completed.
56
EXNO : 10 a)
DATE:
SIMPLE GUI BASED DATABASE APPLICATION

(PERSONAL INFORMATION SYSTEM)
AIM:
To develop a personal information system using Oracle as a back end (data base) and
Microsoft Visual Basic 6.0 as a Front end.
PROCEDURE:
1.Create an employee database with the following fields in Oracle namely

Empno,Empname,address,Basicpay and insert some record into the database.
2.Design the corresponding form with labels ,text boxes and command buttons.
Form1Employee payroll
3.Connect the back end with the front end using DAO method by creating a dsn as follows
a .Select Administrative Tools option from Control Panel .
Then click on Data Sources (ODBC), which displays a dialog box named
ODBC DataSourceAdministrator in which click Add button.
b. In Create New Data Source dialog box, select “Microsoft ODBC for
ORACLE”and click finish button.
c. Give a suitable DataSourceName =payrolldsn,
username=secit1 and
server name=oracle11g.
4. And then click OK and now move to visual basic 6.0
5. Go to ‘Project’ then click ’reference’ in menu bar and select “Microsoft DAO 3.6 Object
Library” and then give OK.
6. A fter completed adding of ‘references’ click ‘debug’ project in menu bar.
7. Execute the project and perform the transaction in banking system.
TABLE DESIGN:
Table Name : Employee
Name Type
----------------------------------- ---------------------------
EMPNO NUMBER(6)
EMPNAME VARCHAR2(30)
ADDRESS VARCHAR2(20)
BASIC NUMBER(8,2)
57
Form :
Dim db As Database
Dim rs As Recordset
Private Sub CALCULATE_Click()

Dim A, B, C As Double
A = Val(Text5.Text) * 0.05
B = Val(Text5.Text) * 0.03
C = Val(Text5.Text) * 0.07
Text5.Text = A
Text6.Text = B
Text7.Text = C
Text8.Text = Val(Text4.Text) + A + B - C
End Sub
Private Sub CLEAR_Click()

Text1.Text = ""
Text2.Text = ""
Text3.Text = ""
Text4.Text = ""
Text5.Text = ""
Text6.Text = ""
Text7.Text = ""
Text8.Text = ""
End Sub
Private Sub DELETE_Click()

rs.DELETE
rs.MoveNext
If rs.EOF Then
MsgBox "NO MORE RECORD"
Else
Text1.Text = rs(0)
Text2.Text = rs(1)
Text3.Text = rs(2)
Text4.Text = rs(3)
End If
End Sub
Private Sub EXIT_Click()

End
End Sub
58
Private Sub FIND_Click()

I = InputBox("ENTER THE EID", FIND)
rs.FindFirst "[EID]=" & I
If rs.NoMatch Then
MsgBox "NO MATCHING RECORD FOUND"
Else
Text1.Text = rs(0)
Text2.Text = rs(1)
Text3.Text = rs(2)
Text4.Text = rs(3)
end If
End Sub
Private Sub FIRST_Click()

rs.MoveFirst
Text1.Text = rs(0)
Text2.Text = rs(1)
Text3.Text = rs(2)
Text4.Text = rs(3)
End Sub
Private Sub Form_Load()
Set db = OpenDatabase("payrolldsn", False, False, "ODBC;uid= secit1; pwd=secit1;")
Set rs = db.OpenRecordset("SELECT * FROM employee")
Text1.Text = rs(0)
Text2.Text = rs(1)
Text3.Text = rs(2)
Text4.Text = rs(3)
End Sub
Private Sub INSERT_Click()
rs.MoveLast
rs.AddNew
rs(0) = Text1.Text
rs(1) = Text2.Text
rs(2) = Text3.Text
rs(3) = Text4.Text
rs(4) = Text5.Text
rs.UPDATE
End Sub
Private Sub LAST_Click()

rs.MoveLast
Text1.Text = rs(0)
Text2.Text = rs(1)
Text3.Text = rs(2)
Text4.Text = rs(3)
59
End Sub
Private Sub NEXT_Click()
rs.MoveNext
If rs.EOF Then
MsgBox "NO MORE RECORDS"
Else
Text1.Text = rs(0)
Text2.Text = rs(1)
Text3.Text = rs(2)
Text4.Text = rs(3)
End If
End Sub
Private Sub PREVIOUS_Click()
rs.MovePrevious
If rs.BOF Then
MsgBox ("No more records")
Else
Text1.Text = rs(0)
Text2.Text = rs(1)
Text3.Text = rs(2)
Text4.Text = rs(3)
End If
End Sub
Private Sub UPDATE_Click()
rs.Edit
rs(0) = Text1.Text
rs(1) = Text2.Text
rs(2) = Text3.Text
rs(3) = Text4.Text
rs(4) = Text5.Text
rs.UPDATE
End Sub
60
Form :
RESULT:
Thus personal information system was developed using Visual Basic as front end and
ORACLE as back end.
61
EXNO : 10 b)
DATE: SIMPLE GUI BASED DATABASE APPLICATION

(WEB BASED USER IDENTIFICATION SYSTEM )
AIM:
To design and implement the application, ‘Banking System’ using MS Access as a back
end (data base) and Microsoft Visual Basic 6.0 as a Front end.
PROCEDURE:
1. Create a bank database with the following fields in MS Access namely acno, name,
address, balance and insert some record into the database.
2. Design the corresponding form with labels ,text boxes and command buttons.
3. Connect the back end with the front end using DAO method by creating a dsn as follows
a) Select control panel ->system and security - > choose Administrative Tools option
b) Then double click on Data Sources (ODBC), which displays a dialog box named
ODBC Data Source Administrator in which click Add button.
c) In Create New Data Source dialog box, select “Microsoft Access Driver
(*.mdb,*.accdb) click finish button.
d) Give a suitable DataSourceName =bankdsn, and click on select option choose
your database location “c:\users\secit10\documents” and choose your table which
appearsOKOK
6. Go to ‘Project’ then click ’components’ in menu bar and select “Microsoft ADO Data
Control 6.0 (OLEDB) ” and also choose Microsoft Data Grid Control 6.0(OLEDB)
ApplyOK.
7. Choose adodc and place on form2 and also choose datagrid control and place it on form2
8. Now click on adodc1 goto property window choose connection stringclick 2nd option use
connection string click buildby default Microsoft OLEDB provider for ODBC Driver will be
choosedclick nextchoose machine datasourceclick newclick user datasourcenext
Microsoft access driver(*.md,*.accdb) nextfinish
9. Click testconnectiontest connection succeedok3.enter the initial catalog to use-choose
your tableokapply
10. Choose 3rd option recordsource in cmd text of SQLtype “select * from bank;” ok
11. Click text1.text properties will be openeddatasourceadodc1 and also in data
fieldchoose the appropriate field.
12. Now give coding and execute the program
62
FORM 1:
Private Sub login_Click()

If Text1.Text = "admin" Then
If Text2.Text = "Admin" Then
MsgBox "welcome to details form"
Form2.Show
Else
MsgBox " incorrect password"
End If
Else
MsgBox "incorrect User name"
End If
Text1.Text = ""
Text2.Text = ""
End Sub
Private Sub Cancel_Click()

Unload Me
End Sub
FORM 2:
Private Sub CANCEL_Click()

Unload Me
End Sub
Private Sub DEPOSIT_Click()

Form4.Show
End Sub
Private Sub WITHDRAW_Click()

Form3.Show
End Sub
FORM 3:
Dim st, st1, st2 As String

Form2.Show
End Sub

Text1.Text = ""
63
Text2.Text = ""
Text3.Text = ""
Text4.Text = ""
Text5.Text = ""
End Sub

Set db = OpenDatabase("C:\Users\Administrator\Documents\bank.mdb")
Set rs = db.OpenRecordset("bank", dbOpenDynaset)
Set DataGrid1.DataSource = Adodc1
DataGrid1.Refresh
End Sub

Adodc1.Recordset.UPDATE
End Sub
Private Sub WITHDRAW_Click()

st = Val(Text3.Text)
st1 = Val(Text4.Text)
st2 = st - st1
Text3.Text = st2
Text5.Text = st2
End Sub
FORM 4:
Dim st, st1, st2 As String

Form2.Show
End Sub

Set db = OpenDatabase("C:\Users\Administrator\Documents\bank.mdb")
Set rs = db.OpenRecordset("bank", dbOpenDynaset)
Set DataGrid1.DataSource = Adodc1
DataGrid1.Refresh
End Sub

End Sub
64
Private Sub DEPOSIT_Click()

st = Val(Text3.Text)
st1 = Val(Text4.Text)
st2 = st + st1
Text3.Text = st2
Text5.Text = st2
End Sub
FORM 1:
65
FORM 2:
FORM 3:
66
FORM 4:
RESULT:
Thus the application banking system has been designed and implemented in visual basic.
67
EXNO : 10 C
DATE:
SIMPLE GUI BASED DATABASE APPLICATION

(HOSPITAL MANAGEMENT SYSTEM )
AIM:
To design and implement the application, ‘Hospital management system’ using MS Access as a
back end (data base) and Microsoft Visual Basic 6.0 as a Front end.
PROCEDURE:
1. Create a two database (patient,staff)in MS Access with necessary fields.

2. Design the corresponding form with labels ,text boxes and command buttons.
3. Connect the back end with the front end using DAO method by creating a dsn as follows
a) Select control panel ->system and security - > choose Administrative Tools option
b) Then double click on Data Sources (ODBC), which displays a dialog box named
ODBC Data Source Administrator in which click Add button.
c) In Create New Data Source dialog box, select “Microsoft Access Driver
(*.mdb,*.accdb) click finish button.
d) Give a suitable DataSourceName =patientdsn, and click on select option choose
your database location “c:\users\secit10\documents” and choose your table which
appearsOKOK
6. Go to ‘Project’ then click ’components’ in menu bar and select “Microsoft ADO Data
Control 6.0 (OLEDB) ” and also choose Microsoft Data Grid Control 6.0(OLEDB)
ApplyOK.
7. Choose adodc and place on form2 and also choose datagrid control and place it on form2
8. Now click on adodc1 goto property window choose connection stringclick 2nd option use
connection string click buildby default Microsoft OLEDB provider for ODBC Driver will be
choosedclick nextchoose machine datasourceclick newclick user datasourcenext
Microsoft access driver(*.md,*.accdb) nextfinish
9. Click testconnectiontest connection succeedok3.enter the initial catalog to use-choose
your tableokapply
10. Choose 3rd option recordsource in cmd text of SQLtype “select * from patient;” ok
11. Also repeat the same for staff table
12. Click text1.text properties will be openeddatasourceadodc1 and also in data
fieldchoose the appropriate field.
13. Repeat the from step 3-12 for connectivity of staff database.
14. Now give coding and execute the program
68
FORM 1:
Private Sub OK_Click()

MsgBox "welcome to user"
Form2.Show
Else
MsgBox " incorrect password"
End If
Else
MsgBox "incorrect User name"
End If
Text1.Text = ""
Text2.Text = ""
End Sub

Unload me
End Sub
FORM 2:
Private Sub PATIENTDETAIL_Click()

Form3.Show
End Sub
Private Sub STAFFDETAIL_Click()

Form4.Show
End Sub
Private Sub HOME_Click()

Form1.Show
End Sub
FORM 3:
Dim DB As Database
Dim RS As Recordset

Set db = OpenDatabase("C:\Users\Administrator\Documents\patient.mdb")
Set rs = db.OpenRecordset("patient", dbOpenDynaset)
End Sub
69

Text1.Text = ""
Text2.Text = ""
Text3.Text = ""
Text4.Text = ""
Text5.Text = ""
End Sub

Adodc1.Recordset.Delete
End Sub
Private Sub HOME_Click()

Form2.show
End Sub

Adodc1.Recordset.MoveFirst
End Sub

Adodc1.Recordset.Update
End Sub

Adodc1.Recordset.MoveLast
End Sub

Adodc1.Recordset.MoveNext
End Sub

Adodc1.Recordset.MovePrevious
End Sub

Adodc1.Recordset
End Sub
FORM 4 :
Dim DB As Database
Dim RS As Recordset

70
Set db = OpenDatabase("C:\Users\Administrator\Documents\staff.mdb")
Set rs = db.OpenRecordset("staff", dbOpenDynaset)
End Sub

Text1.Text = ""
Text2.Text = ""
Text3.Text = ""
Text4.Text = ""
Text5.Text = ""
Text6.Text = ""
End Sub

Adodc1.Recordset .DELETE
MsgBox "the record is delected"
End Sub
Private Sub EXIT_Click()

Unload me
End Sub

Adodc1.Recordset.MoveFirst
End Sub

Adodc1.Recordset.Update
End Sub

Adodc1.Recordset .MoveLast
End Sub

Adodc1.Recordset.MoveNext
End Sub

Adodc1.Recordset.MovePrevious
End Sub

MsgBox "the record is updated"
End Sub
71
Form 1:
Form 2:
72
Form 3:
73
Form 4 :
RESULT:
Thus the application hospital management system has been designed and implemented in visual
basic.
74

Lab Manual

Uploaded by

Document Informationclick to expand document information

Document Informationclick to expand document information

Copyright:

Available Formats

Lab Manual

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Lab Manual

Uploaded by

Copyright:

Available Formats

IT1402 DBMS LAB S.A.

It is used to create a table .

This is used to view the structure of the table.

Example: SQL>desc emp;

Name Null? Type

INSERTING VALES INTO TABLE

Insert a single record into dept table.

SQL> insert into emp values (10,'xxxx','Lect',2,10000);

SQL> insert into stud values (‘zzzzzz’,100,to_date(‘19951225’,’yyyymmdd’));

SQL> insert into emp values(&empno,'&ename','&job',&deptno,&sal);

VIEW THE DATABASE CREATED

SQL> select * from emp;

EMPNO ENAME JOB DEPTNO SAL

Select particular column to view from database

SQL> select ename, job from emp;

Select a particular row to view from database using some condition

SQL> select * from emp where empno=3;

EMPNO ENAME JOB DEPTNO SAL

Select rows from database using some condition

SQL> select * from emp where job=’ASP’ AND sal>10000;

EMPNO ENAME JOB DEPTNO SAL

To study DML commands in RDBMS.

SQL>Create table persons( pid number(5), firstname VarChar(15),lastname VarChar(15),

Insert command is used to insert values into table

Inserting a single row into a table:

Syntax:SQL>insert into <table name> values (value list)

Inserting more than one record using a single insert commands:

Syntax: SQL>insert into <table name> values (&col1, &col2, ….)

Skipping the fields while inserting:

Syntax:SQL>Insert into <tablename(col names to which data’s to be inserted)> values (list of

INSERTING VALES INTO TABLE

Insert a single record into dept table.

SQL>insert into persons values (001,'nelson','raj','no25,annai street','chennai');

SQL> insert into persons values(&pid,'&firstname','&lastname','&address','&city');

Enter value for address: 10/25 krishna street

Skipping the fields while inserting:

SQL> insert into persons(pid,firstname) values(500,'prabhu');

Alter command is used to:

Add the new column to the existing table.

SQL>alter table tablename add/modify (attribute datatype(size));

SQL>Alter table persons add (phoneno char (20));

SQL>alter table persons add primary key(pid);

Modify the existing column definition

Syntax :SQL>alter table <tablename> modify(columnname constraint);

SQL> alter table persons modify(firstname not null);

SQL>Select * from persons;

PID FIRSTNAME LASTNAMEADDRESS CITY PHONENO

The retrieval of specific columns from a table:

It retrieves the specified columns from the table

Syntax: SQL>Select column_name1, …..,column_namen from table name;

Example: SQL>Select pid, firstname from persons;

Elimination of duplicates from the select clause:

It prevents retrieving the duplicated values .Distinct keyword is to be used.

Syntax:SQL>Select DISTINCT col1, col2 from table name;

Select command with where clause:

Syntax: SQL>Select column_name1, …..,column_name n from table name where condition;

Example: SQL>Select firstname, lastname from persons where pid>200;

Select command with order by clause: