lab manual edited

(Autonomous)

Department

Of

Computer Science & Engineering

In-charge HOD Principal

S.VIGNESHWARAN V.G.RAVINDHREN S.SANGAPILLAI

The Seshasayee Institute of Technology

(Autonomous)

Trichy-10

DEPARTMENT OF COMPUTER SCIENCE & ENGINEERING

Lab Manual for the Academic Year 2013-14

SUBJECT : DBMS LAB

STREAM : CSE

Prepared by:

Approved & Reviewed by:

Issued by:

w.e.f Date:

Head Of the Department

V.G.RAVINDHREN, M.E., MISTE.

INDEX

S.No Contents Page.no

1 Creating & Executing DDL in SQL.

2 Creating & Executing Integrity constraints in SQL.

3 Creating & Executing DML in SQL.

4 Executing relational, logical and mathematical set operators using

SQL.

5 Executing group functions

6 Executing string operators & string functions.

7 Executing Date & Time functions.

8 Executing Data Conversion functions.

9 Test all the SQL Editor Commands and explain the functions of

each Command.

10 To work with the following Conditions: Column Formatting, Alias

and Column Ordering.

11 To work with Four Types of Join.

12 To work with the following: Query – Sorting, Indexing, Correlated

Sub queries, Group by having clauses.

13 Executing Sequences and synonyms in SQL.

14 Program for declaring and using variables and constant using

PL/SQL.

15 Program using if then else in PL/SQL

16 Program using for loop & while loop in PL/SQL to display the

Numbers from 1 to 100.

17 Program using nested loop in Pl/SQL.

18 Write a PL/SQL code to display the Employee No for an

Employee.

19 Write a PL/SQL code to check the item code using procedure.

20 Write a PL/SQL code to Combine Procedure and Functions and get

the Results

LAB OBJECTIVE

Upon successful completion of this Lab the student will be able to:

Creating database objects

Modifying database objects

Manipulating the data

Retrieving the data from the database server

Performing database operations in a procedural manner using pl/sql

Performing database operations (create, update, modify, retrieve, etc.,) using front-

end tools like D2K.

Design and Develop applications like banking, reservation system, etc.,

INTRODUCTION ABOUT LAB

There are 60 systems installed in this Lab. Their configurations are as follows:

Processor : Dual core

RAM : 512 MB

Hard Disk : 160 GB

Mouse : Optical Mouse

Network Interface card : Present

Software:

All systems are configured in DUAL BOOT mode i.e., Students can boot

from Windows XP or Linux as per their lab requirement. This is very useful

for students because they are familiar with different Operating Systems so

that they can execute their programs in different programming environments.

Oracle 10G version is installed in all systems. This is very useful because

students can save their work (scenarios‟, pl / sql programs, data related

projects, etc) in their own accounts.

Each student work is safe and secure from other students.

Systems are provided for students in the 1:1 ratio.

Systems are assigned numbers and same system is allotted for students when

they do the lab.

Understand the Basic of SQL :

The Structured Query Language is often abbreviated with the letters SQL. Some people

pronounce it by spelling out the letters, as in "ESS-CUE-ELL". Others pronounce it as

"sequel". Both pronunciations are fine, and both are used by respected professional in the

industry. Whatever you do, just don't call it "squeal".

SQL is a language to :

Create database and the object within them.

Store data in those databases.

Change and analyze that data.

Get that data back out in reports, web pages, or virtually any other use imaginable.

SQL is divided into the following

Data Definition Language (DDL)

Data Manipulation Language (DML)

Data Retrieval Language (DRL)

Transaction Control Language (TCL)

Data Control Language (DCL)

DDL -- create, alter, drop, truncate, rename

DML -- insert, update, delete

DRL -- select

TCL -- commit, rollback, savepoint

DCL -- grant, revoke

Ex.no:1

Data Definition Language (DDL)

Aim:

To create a database with the help of Data Definition Language queries.

Data Definition Language:

The DDL is the part of the sql which permits the database to be created we can also

provide link between the tables and impose constraints between database tables.

DDL Commands are:

Create

Alter

drop

truncate

rename

Create:

Create Table Syntax:

Create table <table_name> (col1 datatype1, col2 datatype2 …coln datatypen);

Example:

create table student (no number (2), name varchar (10), marks number (3));

OUTPUT:

No Name Marks

- - -

- - -

Table created.

0.46 seconds

Alter:

This can be used to add or remove columns and to modify the precision of the datatype.

ADDING COLUMN

Syntax:

alter table <table_name> add <col datatype>;

Example:

alter table student add Regno number;

OUTPUT:

No Name Marks Regno

- - - -

- - - -

Table altered.

0.16 seconds

Truncate:

This can be used to delete the entire table data permanently.

Syntax:

truncate table <table_name>;

Example:

truncate table student;

Output:

Table truncated.

0.01 seconds

Drop:

This will be used to drop the database object;

Syntax:

Drop table <table_name>;

Example:

drop table student;

Output:

Table dropped.

1.27 seconds

Rename:

This will be used to rename the database object;

Syntax:

rename <old_table_name> to <new_table_name>;

Example:

rename student to stud;

Output:

Statement processed.

0.05 seconds

Result:

Thus the DDL commands was executed.

Ex.no:2

Data Manipulation Language(DML)

Aim:

To perform manipulation in database with the help of Data Manipulation Language

queries.

Data Manipulation Language(DML):

It allows manipulations on the database.

It allows the updation content in the table.

DML Commands are:

1. INSERT

2. UPDATE

3. DELETE

INSERT:

This will be used to insert the records into table.

By value method

USING VALUE METHOD

Syntax:

insert into <table_name) values (value1, value2, value3 …. Valuen);

Example:

insert into student values (1, ‟sudha‟, 100);

select * from student;(for display the table)

Output:

NO Name Marks

1 Sudha 100

1 rows returned in 0.07 seconds

Update:

This can be used to modify the table data.

Syntax:

Update <table_name> set <col1> = value1, <col2> = value2 where <condition>;

Example:

update student set name=‟sud‟ where name=‟Sudha‟;

select * from student;

Output:

NO Name Marks

1 Sud 100

1 rows returned in

0.01 seconds

Delete:

This can be used to delete the table data temporarily.

Syntax:

Delete <table_name> where <condition>;

Example:

delete student;


Output:

NO Name Marks

- - -

1 row(s) deleted.

0.02 seconds

Result:

Thus the DML commands was executed.

Ex.no:3

Integrity Constraints

Aim:

To create and execute the Integrity constraints in SQL.

Constraints are categorized as follows.

Domain integrity constraints

Not null

Check

Entity integrity constraints

Unique

Primary key

Referential integrity constraints

Foreign key

Not Null:

This is used to avoid null values.

We can add this constraint in column level only.

Syntax:

create table <table_name>(col1 datatype(size) not null,col2 datatype(size)..);

Example:

create table student(no number(2) not null, name varchar(10), marks

number(3));

insert into student values(1,‟sr‟,100);

insert into student values(2,‟ar‟,100);


Output:

No Name Marks

1 Sr 100

2 Ar 100

2 rows returned in

0.07 seconds

Check:

This is used to insert the values based on specified condition.

We can add this constraint in all three levels.

Syntax:

create table <table_name>(col1 datatype(size) not null,col2 datatype(size)..);

check(column_name)<condition>;

Example:

COLUMN LEVEL

create table student(no number(2) , name varchar(10), marks number(3) check (marks

> 300));




Output:

No Name Marks

1 Sr 100

2 Ar 100

2 rows returned in

0.07 seconds

Unique

This is used to avoid duplicates but it allow nulls.


Syntax:

create table <table_name>(col1 datatype(size) unique,col2 datatype(size)..);

Example:

COLUMN LEVEL

create table student(no number(2) unique, name varchar(10), marks number(3));




Output:

No Name Marks

1 Sr 100

2 Ar 100

2rows returned in

0.07 seconds

Primary Key

This is used to avoid duplicates and nulls. This will work as combination of

unique and not null.

Primary key always attached to the parent table.


Syntax:

alter table <table_name>add primary key(column_name);

Example:

COLUMN LEVEL

create table student(no number(2) primary key, name varchar(10), marks

number(3));




Output:

No Name Marks

1 Sr 100

2 Ar 100

2 rows returned in

0.07 seconds

FOREIGN KEY

This is used to reference the parent table primary key column which allows

duplicates.

Foreign key always attached to the child table.

We can add this constraint in table and alter levels only.

Syntax:

create table <table_name>(col1 datatype(size) foreign key references <old table_name>

primary key(column_name) col2 datatype(value)…);

Example:

COLUMN LEVEL

create table student(no number(2) primary key, name varchar(10), marks

number(3));




Output:

No Name Marks

1 Sr 100

2 Ar 100

2 rows returned in

0.07 seconds

create table student1(no number(2),regno number, marks

number(3) primary key(regno),foreign key(no) references student(no));

insert into student values(1,121530,100);

insert into student values(2,121432,100);


Output:

No Regno Marks

1 121530 100

2 121432 100

2 rows

returned

in 0.07

seconds

Result:

Thus the integrity constraints were executed.

Ex.no:4

Executing relational, logical and mathematical set operators

Aim:

To execute relational, logical and mathematical operators in database.

Commands:

Relational operators:

= (equal to)

!= or <> (not equal to)

> (greater than)

< (lesser than)

>= (greater than equal to)

<= (less than equal to)

Between

Not between

In

Not in

Logical operators:

And

Or

Not

Set operators:

Union

Union all

Intersect

Minus

Relational operators:

= (equal to):

Purpose:

The condition equals the values in LHS and RHS.

Syntax:

select <column_name> from <table_name>

where <column_name> = values;

Example:

select * from student where marks = 500;

Output:

!= (not equal to):

Purpose:

The condition not equal to the values in LHS and RHS.

Syntax:


where <column_name> ! = values;

Example:

select * from student where marks != 500;

Output:

>(greater than):

Purpose:

It Checks the value is greater than.

Syntax:


MARKS

500

MARKS

400

2100

where <column_name> < values;

Example:

select * from student where marks < 500;

Output:

< (less than):

Purpose:

It checks the value is less than.

Syntax:


where <column_name> > values;

Example:

select * from student where marks > 500;

Output:

>= (greater than equal to):

Purpose:

It checks the value is greater than and equal to.

Syntax:


where <column_name> >= values;

MARKS

400

MARKS

2100

Example:

select * from student where marks >= 500;

Output:

<= (less than equal to):

Purpose:

It checks the value is less than and equal to.

Syntax:


where <column_name> <= values;

Example:

select * from student where marks <= 500;

Output:

between:

Purpose:

It retrieves the data between the specified condition.

Syntax:

MARKS

500

2100

MARKS

400

500


where <column_name> between <values1> <values2>;

Example:

select * from student where marks between 200 and 400;

Output:

not between:

Purpose:

It retrieves data which is not between the specified condition.

Syntax:


where <column_name> not between<values1> <values2>

Example:

select * from student where marks not between 200 and 400;

Output:

in:

Purpose:

It retrives the set of columns which is specified in conditions using in.

MARKS

400

300

MARKS

500

2100

Syntax:


where <column_name> in (value1,value2,…value n);

Example:

select * from student where no in(1,2,3,4);

Output:

not in:

Purpose:

It does not retrives the set of columns which is specified in conditions using not in.

Syntax:


where <column_name> not in (value1,…….value n);

Example:

select * from student where no not in(2,3,4);

Output:

NO MARKS NAME

1 400 jireh

3 700 jehova

NO MARKS NAME

2 100 shalom

4 500 jesibah

NO MARKS NAME

1 400 jireh

Logical operator:

And:

Purpose:

It retrieves the data when all conditions are true.

Syntax:

select *from <table_name> where <condition1> and <condition2>

Example:

select *from stud where no=2 and mark >=100;

Output:

or:

Purpose:

It retrieves the data when either one condition is true.

Syntax:

select *from <table_name> where <condition1> or <condition2>;

Example:

select *from stud where no=2 or mark >=100;

Output:

3 700 jehova

NO MARKS NAME

2 100 shalom

NO MARKS NAME

Set operators:

Union:

Purpose:

All distinct rows selected either by query

Syntax:

select *from <table_name1> union

select * from <table_name2> where <condition>;

Example:

select *from stud union

select * from result where no=1;

Output:

UNION ALL:

Purpose:

All distinct rows selected by queries including duplicates.

Syntax:

1 400 Jireh

2 100 Shalom

3 700 Jehova

4 500 Jesibah

NO

1

2

500

select *from <table_name1> union all


Example:

select *from stud union all

select * from result;

Output:

Intersect:

Purpose:

All distinct rows selected by both queries.

Syntax:

select *from <table_name1> intersect


Example:

select *from stud intersect


Output:

NO

1

2

500

500

NO

1

2

3

minus:

Purpose:

All distinct rows selected either by query

Syntax:

select *from <table_name1> minus


Example:

select *from stud minus


Output:

Result:

Thus the relational, logical and set operators are executed successfully in database.

NO

1

-

Ex.no: 5

Group Functions

Aim:

To retrieve the data using Group Functions.

Commands:

sum (Sum)

avg (Average)

max (Maximum)

min (Minimum)

count (Count)

sum:

Purpose:

It retrieves the sum of the specified column in database.

Syntax:

select sum(<column_name>) from <table_name>;

Example:

select sum(marks) from student;

Output:

avg:

Purpose:

It retrieves the average of the specified column in database.

Syntax:

select avg(<column_name>) from <table_name>;

Example:

SUM(MARKS)

3000

select avg(marks) from student;

Output:

max:

Purpose:

It retrieves the maximum number in the specified column in database

Syntax:

select sum(<column_name>) from <table_name>;

Example:

select max(marks) from student;

Output:

min:

Purpose:

It retrieves the minimum number in the specified column in database.

Syntax:

select min(<column_name>) from <table_name>;

Example:

select min(marks) from student;

AVG(MARKS)

1000

MAX(MARKS)

2100

MIN(MARKS)

Output:

count:

Purpose:

It retrieves the number of records in the specified column in the database.

Syntax:

select count(<column_name>) from <table_name>;

Example:

select count(marks) from student;

Output:

Result:

Thus the data are retrieved using group functions successfully.

500

COUNT(MARKS)

3

Ex.no:6

String functions

Aim:

To perform string operations in the database.

Commands:

Initcap

Upper

Lower

Length

Trim

Concat

Ascii

Initcap:

Purpose:

It capitalize the initial letter of the string.

Syntax:

select initcap(„string‟) from dual;

Example:

select initcap(„computer‟) from dual;

Output:

upper:

Purpose:

It converts the string into upper case.

INITCAP('COMPUTER')

Computer

Syntax:

select upper(„string‟) from dual;

Example:

select upper(„computer‟) from dual;

Output:

lower:

Purpose:

It converts the string into lower case

Syntax:

select lower(„string‟) from dual;

Example:

select lower(„computer‟) from dual;

Output:

length:

Purpose:

It returns the length of the string.

Syntax:

select length(„string‟) from dual;

Example:

UPPER('COMPUTER')

COMPUTER

LOWER('COMPUTER')

computer

select length(„computer‟) from dual;

Output:

trim:

Purpose:

It trims the unwanted string in front and end of the string.

Syntax:

select trim(„unwanted_string from „string‟) from dual;

Example:

select trim(„i‟ from „indiani‟) from dual;

Output:

concat:

Purpose:

It is used to combine two strings.

Syntax:

select concat(„string1‟,‟string2‟) from dual;

Example:

select concat(„computer‟,‟ technology‟) from dual;

Output:

LENGTH('COMPUTER')

8

TRIM('I'FROM'INDIANI')

Ndian

ascii:

Purpose:

It returns decimal representation in database character set of the first character of the

string.

Syntax:

select ascii(„string‟) from dual;

Example:

select ascii(„computer‟) from dual;

Output:

Result:

Thus the string operator is executed successfully.

CONCAT('COMPUTER','TECHNOLOGY')

computer technology

ASCII('COMPUTER')

99

EX.NO:7

Date & Time Functions

AIM:

To work the date and time functions with the help of system database of dual.

DATE FUNCTIONS:

1)Add_Months(DATE,N):

Purpose: The add_months data function returns a date after adding a specified data with the

specified number of months. The format is add_months(d,n), where d is the date and n

represents the number of months.

Syntax:

Add_Months(Date,no.of Months)

Example:

SQL> select Add_Months(sysdate,2) from dual;

This will add two months in system date.

Output:

ADD_MONTHS(SYSDATE,2)

24-SEP-14

2)Months_Between(X,X2):

Purpose: Where Date1, Date2 are dates. The output will be a number. If Date1 is later than Date2,

result is positive; if earlier, negative. If Date1 and Date2 are either the same days of the

month or both last days of months, the result is always an integer; otherwise Oracle

calculates the fractional portion of the result based on a 31-day month and considers the

difference in time components of Date1 and Date2.

Syntax:

Months_Between(Date1,Date2)

Example:

select MONTHS_BETWEEN('22-aug-2014','25-dec-2014') from dual;

Output:

MONTHS_BETWEEN('22-AUG-2014','25-DEC-2014')

4.

3) Next_Day(x,week_day):

Purpose: Returns the date of the first weekday named by „char‟ that is after the date named

by„Date‟. „Day‟ must be the day of the week.

Syntax:

Next_Day(Date,Day)

Example:

SQL> select NEXT_DAY('22-AUG-2014','wednesday') from dual;

This will return date of next wedensday.

Output:

27-aug-2014

4) last_day(x):

Purpose: This function returns the date, which is rounded to the unit specified by the format.

Syntax:

Last_day (DD.MM.YYYY);

Example:

SQL> Select LAST_DAY(‟22-aug-2014‟) from dual;

Output:

LAST_DAY('22-AUG-2014')

31-AUG-14

5) new_time (x,zone1,zone2):

Purpose: Return the date and time in zone2 if date „x‟ represents the time in zone1.

Syntax:

Select new_time(‟date‟,‟zone1‟,‟zone2‟) from dual;

Example:

SQL>select NEW_TIME(‟22-aug-2014‟,‟PST‟,‟EST‟) from dual;

Output:

22-aug-2014 20:45

Result:

The Aim to work the functions with the help of dual is verified successfully.

EX.NO:8

Conversion Functions

AIM:

To work the conversion function with the help of system database of dual.

Definition:

Conversion functions converts a value from one data type to another. The

conversion functions are classified into the following:

o To_Number()

o To_Char()

o To_Date()

o Bin_To_Num()

o Ascii()

FUNCTIONS:

1)To_Number:

Purpose: The to_number function allows the conversion of string containing numbers into the

number data type on which arithmetic operations can be performed.

Syntax:

Select to_number („n‟)

Example:

SQL>Select to_number („50‟) from dual;

Output:

50

2) To_Char:

Purpose: To_char function converts a value of number data type to a value of char data type,

using the optional format string. It accepts a number (no) and a numeric format (fmt) in

which the number has to appear. If „fmt‟ is omitted, „no‟ is converted to a char exactly

long enough to hold significant digits.

Syntax:

To_char(no,[fmt])

Example:

SQL> select TO_CHAR('sysdate','DD,MM,YYYY') from dual;

Output:

22-aug-2014

To_char converts a value of date data type to character value. It accepts a date, as

well as the format(fmt) in which the date has to appear. „fmt‟ must be the date format. If

„fmt‟ is omitted, „date‟ is converted to a character value in the default date format dd-

mon-yy_.

3)To_Date:

Purpose: The format is to_date(char [,fmt]). This converts char or varchar data type to date data

type. Format model, fmt specifies the form of character. Consider the following example

which returns date for the string „January 27 2000‟.

Syntax:

To_date(char,[fmt])

Example:

SQL>select to_date(‟27 January 2000‟,‟dd/mon/yy‟) 泥ate_ from dual;

Output:

27-JAN-2014

4)BIN_TO_NUM:

Purpose: This function will convert the binary value into number.(1,2,3 etc).

Syntax:

Select binary_to_num(1,0,0,1)

Example:

SQL>select bin_to_num( 1,0,01) from dual;

Output:

9

5)ASCIISTR:

Purpose: This function will convert the ascii string equivalent of the text string “ABCDE”

Syntax:

Select AsciiSTR(„string‟)

Example:

Select AsciiStr(„A‟) from dual;

Output:

A

Result:

Thus the output is verified successfully

Ex.No.9

Execute and test all the SQL Editor commands

Date:

Aim:

To execute and test all the SQL Editor commands.

First type a query at the sql prompt.

Procedure:

1. To save this query.

SQL>SAVE ex;

The query will be stored both in sql buffer and notepad as ex.sql.

2. To display the contents,

SQL>get ex;

The query which is stored in ex.sql will be displayed

3. To edit the file

SQL/edit ex.sql;

A notepad application will open. This contains the query which we have already stored.

We can change the query.

Current buffer operations

4. To list all lines in a current buffer.

SQL> /;

5. To add a specified line to the end of current buffer data.

SQL > a add;

6. To change text on current buffer content

SQL > C/add/sub;

7. To delete the current buffer line

SQL > del.

8. To clear the entire buffer.

SQL > cl;

9. To place the line number on the editor

SQL > #

10. To add one or more lines after the current buffer line,

SQL > i;

Solution:

SQL> create table stud(regno number(6),name char(10));

Table created.

SQL>SAVE ex;

Created file ex

SQL>get ex;

1* create table stud(regno number(6),name char(10))

SQL/edit ex.sql;

Notepad will be open and edit and save the file and exit.

create table stud(regno number(6),name char(10))

SQL> /;

create table stud(regno number(6),name char(10))

SQL > a add;

1* create table stud(regno number(6),name char(10)) add

SQL > C/add/sub;

1* create table stud(regno number(6),name char(10)) sub

SQL > 1


SQL > #


SQL > i;

one or more lines are added to current buffer line.

SQL > cl;

cleared

SQL > del.

No lines in buffer

Result:

The above SQL editor commands are executed and verified successfully.

Ex.No.10

Work with the following conditions

Date:

Aim:

To work with following conditions:

a) Column formatting

b)Alias

c)Column ordering

Procedure:

a) Column formatting:

Syntax:

Create table <tablename>(column name1 <datatype>,column name2 <datatype>,

column name3 <datatype>, … , column name n <datatype n>);

Purpose:

Create command is used to create a new table.Let us insert some records.

Syntax:

Insert into <table name> values(value 1,value 2,…,value n);

Purpose:

Insert command is used to insert values in the columns of specified table.

Then type ed sales.SQL at the SQL prompt.

SQL > ed sales.Sql;

A notepad will open.

Type the following codes:

Column ord_no format a 7 jusify centre

Column ord_no heading „ORDERNO‟

Column name format a 15 jusify centre

Column name heading „NAME‟

Column pname format a 25 justify centre

Column pname heading „ Items‟

Column rate format 999.99 justify centre

Column rate heading „PRICE‟

Select ord_no,name,pname,rate from sales;

Save the file.

Type start at the prompt.

SQL > start sales;

b) Alias:

We can give alias name for any column in the table.

Syntax:

Select <column name> “<alias name>” from <table name>;

c) Column Ordering:

We can arrange the rows in a table with any one of the columns.

Syntax:

Select * from <table name> order by <column name>;

Solution:

SQL > Create table Sales(ord_no varchar2(6),name char(25),P_no varchar2(6),

pname varchar2(30), rate number(8,2));

Table Created.

SQL > insert into sales values(„A001‟, „V.Raj‟ , „COL100‟, „COLGATE‟,40.00);

1 Row Created.

SQL > insert into sales values(„B002‟, „V.Raj‟ , „HAM125‟, „HAMAM‟,25.40);

1 Row Created.

SQL > insert into sales values(„C001‟, „A.ANU‟ , „LUX125‟, „LUX‟,20.40);

1 Row Created.

SQL > ed sales.Sql;

A notepad will open. Type the following codes:

Column ord_no format a 7 jusify centre

Column ord_no heading „ORDERNO‟

Column name format a 15 jusify centre

Column name heading „NAME‟

Column pname format a 25 justify centre

Column pname heading „ Items‟

Column rate format 999.99 justify centre

Column rate heading „PRICE‟

Select ord_no,name,pname,rate from sales;

Save the file.

SQL > start sales;

ORDER NAME Items PRICE

------ ------------------------- ------------------------------ -------

A001 V.Raj COLGATE 40.00

B002 V.Raj HAMAM 25.40

C001 A.ANU LUX 20.40

C001 A.ANU LUX 20.40

SQL > Select Pno “Pro_Number” from sales;

Pro_No

----------

COL100

HAM125

LUX125

LUX125

SQL > select * from sales order by name;

ORDER NAME P_NO Items PRICE

------ ------------------------- ------ ------------------------------ -------

C001 A.ANU LUX125 LUX 20.40

C001 A.ANU LUX125 LUX 20.40

A001 V.Raj COL100 COLGATE 40.00

B002 V.Raj HAM125 HAMAM 25.40

Result :

The query was executed successfully and output was verified

EX:11

Types of Joins

AIM:

To work some operations with the help of various joins.

PURPOSE:

SQL Joins are used to relate information in different tables. A Join condition is a

part of the sql query that retrieves rows from two or more tables. A SQL Join condition is

used in the SQL WHERE Clause of select, update, delete statements.

The Syntax for joining two tables is:

SELECT col1, col2, col3...FROM table_name1, table_name2 WHERE

table_name1.col2 = table_name2.col1;

EXAMPLE:

Table 1

NO NAME

1 priya

2 monu

3 aadhi

4 nandmubi

5 dhanabar

6 jeni

Table 2

NO CITY

1 madurai

2 coimbatore

3 trichy

4 kashmir

9 delhi

Types of join:

There are three types of Joins in sql. They are

1.Inner join

2.Outer join

3.Cross join

4.Natural join

1. INNER JOIN:

Inner join is the default type of join, it will producesses the result set, which

contains matched rows only.

Syntax:

Select * from table1<innerjoin>table2

Here no need of specifying innerjoin. Simply we can specify Join also.

Syntax:

Select * from table1<join>table2

Example:

NO NAME MARKS NO NAME RESULT

1 janani 500 1 jenifer 500

2 nandi 300 2 jeevi 500

3 mubi 700 3 aadhi 800

4 dhana 200 4 maha 700

2. OUTER JOIN:

Purpose:

Outer join produces the results, which contains matched rows and unmatched rows.

Outer join is further classified as three types.They are

1.Left outer join

2.Right outer join

3.Full outer join.

a)LEFT OUTER JOIN:

Purpose:

Left outer join producesses the results, which contains all the rows from left

table and matched rows from right table.

Syntax:

select * from table1<leftouterjoin>table2

EXAMPLE:

NO NAME NO CITY

1 priya 1 madurai

2 monu 2 coimbatore

3 aadhi 3 trichy

4 nandmubi 4 kashmir

6 jeni - -

5 dhanabar - -

b)RIGHT OUTER JOIN:

Purpose:

Right outer join producesses the resultset, which contains all the rows

from right table and matched rows from left table.

Syntax:

Select * from table1<right outer join>table2

EXAMPLE:

NO NAME NO CITY

1 priya 1 madurai

2 monu 2 coimbatore

3 aadhi 3 trichy


- - 9 delhi

c)FULL OUTER JOIN:

Purpose:

Full outer join producesses the resultset, which contains all the rows from left

table and all the rows from right table.

Syntax:

Select * from table1<fullouterjoin>table2

EXAMPLE:

NO NAME NO CITY

1 priya 1 madurai

2 monu 2 coimbatore

3 aadhi 3 trichy


6 jeni - -

5 dhanabar - -

- - 9 delhi

3.CROSS JOIN:

Purpose:

A join without having any condition is known as cross join, in cross join every

row in first table is joins with every row in second table.

Cross join is nothing but cortizion product.

Syntax:

Select * from table1<cross join>table2

EXAMPLE:

NO NAME NO CITY

1 priya 1 madurai

1 priya 2 coimbatore

1 priya 3 trichy

1 priya 4 kashmir

1 priya 9 delhi

2 monu 1 madurai

2 monu 2 coimbatore

2 monu 3 trichy

2 monu 4 kashmir

More than 10 rows available. Increase rows selector to view more rows.

4.NATURAL JOIN:

Purpose:

It retrieve the two columns from the table,and compare the both columns and

display the matched connect.

SYNTAX:

Select * from table1<natural join>table2

EXAMPLE:

NO NAME CITY

1 priya madurai

2 monu coimbatore

3 aadhi trichy

4 nandmubi kashmir

Result :


EX:12

Query – sorting , indexing , correlated sub queries , group by having clauses.

AIM:

To work with the following: query – sorting , indexing , correlated sub queries , group by

having clauses.

QUERY – SORTING:

USING ORDER BY

This will be used to ordering the columns data (ascending or descending).

Syntax:

Select * from <table_name> order by <col>desc;

By default oracle will use ascending order.

If you want output in descending order you have to use desc keyword after the column.

EXAMPLE:

SQL> select * from student order by no;

NO NAME MARKS

--- ------- ---------

1 Sudha 100

1 Jagan 300

2 Saketh 200

2 Naren 400

3 Ramesh

4 Madhu

5 Visu

6 Rattu

SQL> select * from student order by no desc;

NO NAME MARKS

--- ------- ---------

6 Rattu

5 Visu

4 Madhu

3 Ramesh

2 Saketh 200

2 Naren 400

1 Sudha 100

1 Jagan 300

INDEXING :

PURPOSE:

Indexes allows the data base application to find data fastly, without reading the whole

table.

TYPES OF INDEX:

i) Unique index

ii) Composite index

iii) Single column index

1)UNIQUE INDEX:

Unique indexes guarantee that no two rows of a table have duplicate values in the

columns that define the index.

SYNTAX:

Create unique index <index name> on <table name> (column name);

EXAMPLE:

SQL>create unique index in2 on index1 (regno);

OUTPUT:

INDEX CREATED

2)COMPOSITE INDEX:

A composite key can be defined on a combination of columns.

We can define composite keys on entity integrity and referential integrity constraints.

SYNTAX:

SQL> create table student(no number(2) , name varchar(10), marks number(3),

unique(no,name));

EXAMPLE:

create index in3 on index1 (regno,name);

OUTPUT:

INDEX CREATED

3)SINGLE COLUMN INDEX:

Single column index is used to set in only one column

SYNTAX:

create index <index_name> on <table_name>(col_name)

EXAMPLE:

create index in1 on index1(name);

OUTPUT:

index created.

CORRELATED SUB QUERY:

PURPOSE :

A subquery is evaluated once for the entire parent statement where as a

correlated subquery is evaluated once for every row processed by the parent statement.

SYNTAX:

select<column name> from <table name>where <column name><condition>(select

max(<coliumn name>)from <table name>);

EXAMPLE:

SQL>select regno,name,marks from STUDENT where marks <(select

max(marks)from STUDENT);

OUPTUT:

REG NO NAME MARKS

1 XXX 100

2 YYY 200

3 ZZZ 300

EXAMPLE:

SQL> select distinct deptno from emp e where 5 <= (select count(ename) from emp

where e.deptno = deptno);

DEPTNO

20

30

Group By Clause:

Group by clause is used with group functions only. Normally group functions

returns only one row. But group by clause will group on that column. The group by

clause tells Oracle to group rows based on distinct values for specified columns, i.e. it

creates a data set, containing several sets of records grouped together based on a

condition.

Select group function from table name group by column name

Example:

SQL>select deptno,count(*) from emp_master group by deptno;

DEPTNO COUNT(*)

10 2

20 2

Having Clause

The having clause is used to satisfy certain conditions on rows, retrieved by using group

by clause.Having clause should be preceding by a group by clause. Having clause

further filters the rows return by group by clause.

Example:

SQL> select deptno,count(*) from emp_master group by deptno having Deptno is not

null;

DEPTNO COUNT(*)

10 2

20 2

Order By Clause

Order by clause is used to arrange rows in either ascending or descending order. The

order by clause can also be used to arrange multiple columns. The order by clause

should be the last clause in select statement.It is used as per follows :

Syntax:

select <column(s)> from <TableName> where [condition(s)] [order by <column name>

[asc/]desc];

Example:

If you want to view salary in ascending order the following command can performed:

SQL> select empno,ename,salary from emp_master order by salary;

EMPNO ENAME SALARY

1123 King 3400

1124 Martin 7000

1122 Allen 10000

1125 Tanmay 10000

4 rows selected.

If you have not specify any order by default it will consider ascending order and salary

will be displayed in ascending order. To retrieve data in descending order the desc

keyword is used after order by clause.

SQL> select empno, ename, salary from emp_master order by salary desc;

And the output will opposite from above.

EMPNO ENAME SALARY

1122 Allen 10000

1125 Tanmay 10000

1124 Martin 7000

1123 King 3400

4 rows selected.

Result :


EX.NO:13

SEQUENCE AND SYNONYMS

AIM

Executing sequence and synonyms in sql.

DESCRIPTION

• Sequences are frequently used in databases because many applications require each row

in a table to contain a unique value, and sequences provide an easy way to generate them.

• It is an object in oracle.It generates a series of numbers. It can generate unique

sequential numbers for using a primary key column of a table.

SYNTAX

CREATE SEQUENCE [schema_name . ] sequence_name

[ AS [ built_in_integer_type | user-defined_integer_type ] ]

[ START WITH <constant> ]

[ INCREMENT BY <constant> ]

[ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]

[ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]

[ CYCLE | { NO CYCLE } ]

[ { CACHE [ <constant> ] } | { NO CACHE } ]

[ ; ]

PROGRAM

SQL> create sequence ss

minvalue 1

maxvalue 10

start with 1

increment by 1;

OUTPUT

Sequence created.

PROGRAM

SQL> select ss.nextval from dual;

OUTPUT

NEXTVAL

---------

1


NEXTVAL

---------

2


NEXTVAL

---------

3

SQL> select ss.currval from dual;

CURRVAL

---------

3


NEXTVAL

---------

4

SQL> alter sequence ss

2 maxvalue 5;

Sequence altered.

PROGRAM


OUTPUT

NEXTVAL

---------

5

PROGRAM


OUTPUT

select ss.nextval from dual

*

ERROR at line 1:

ORA-08004: sequence SS.NEXTVAL exceeds MAXVALUE and cannot be instantiated

DROP A SEQUENCE

SYNTAX

DROP SEQUENCE { [ database_name . [ schema_name ] . | schema_name. ]

sequence_name } [ ,...n ] [ ; ]

PROGRAM

SQL> drop sequence ss;

OUTPUT

Sequence dropped.

PROGRAM

SQL> create sequence ss

minvalue 1

maxvalue 5

start with 1

increment by 1;

OUTPUT

Sequence created.

PROGRAM

SQL> create table sports(spid number,spname varchar2(20));

Table created.

SQL> insert into sports values(ss.nextval,'cricket');

1 row created.

SQL> insert into sports values(ss.nextval,'chess');

1 row created.

SQL> insert into sports values(ss.nextval,'tennis');

1 row created.

SQL> insert into sports values(ss.nextval,'football');

1 row created.

SQL> insert into sports values(ss.nextval,'hockey');

1 row created.

PROGRAM

SQL> insert into sports values(ss.nextval,'volleyball');

OUTPUT

insert into sports values(ss.nextval,'volleyball')

*

ERROR at line 1:

ORA-08004: sequence SS.NEXTVAL exceeds MAXVALUE and cannot be instantiated

PROGRAM

SQL> select * from sports;

OUTPUT

SPID SPNAME

--------- --------------------

1 cricket

2 chess

3 tennis

4 football

5 hockey

SQL> drop sequence ss;

OUTPUT

Sequence dropped.

EXERCISE ON SYNONYMS

DESCRIPTION

• A synonym is an alias or alternate name for a table, view, sequence, or other schema

object.

• They are used mainly to make it easy for users to access database objects owned by

other users.

PROGRAM

SQL> create synonym workers for emp;

OUTPUT

Synonym created.

PROGRAM

SQL> create synonym employees for workers;

OUTPUT

Synonym created.

PROGRAM

SQL> select * from workers;

OUTPUT

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH CLERK 7902 17-DEC-80 2800 20

7499 ALLEN SALES 7698 20-FEB-81 3600 300 30

7521 WARD SALES 7698 22-FEB-81 3250 500 30

PROGRAM

SQL> select * from employees;

OUTPUT

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH CLERK 7902 17-DEC-80 2800 20

7499 ALLEN SALES 7698 20-FEB-81 3600 300 30

7521 WARD SALES 7698 22-FEB-81 3250 500 30

DROP SYNONYM

PROGRAM

SQL> drop synonym workers;

OUTPUT

Synonym dropped.

PROGRAM

SQL> select * from employee;

OUTPUT

select * from employee

*

ERROR at line 1:

ORA-00942: table or view

does not exist

Result :


PL/SQL

INTRODUCTION

The PL/SQL programming language was developed by Oracle Corporation in the late

1980s as procedural extension language for SQL and the Oracle relational database.

Following are notable facts about PL/SQL:

*)PL/SQL is a completely portable, high-performance transaction-processing language.

*) PL/SQL provides a built-in interpreted and OS independent programming

environment.

*) PL/SQL can also directly be called from the command-line SQL*Plus interface.

*) Direct call can also be made from external programming language calls to database.

*) PL/SQL's general syntax is based on that of ADA and Pascal programming language.

*) Apart from Oracle, PL/SQL is available in TimesTen in-memory database and IBM

DB2.

FEATURES OF PL/SQL

*)PL/SQL is tightly integrated with SQL.

*) It offers extensive error checking.

*) It offers numerous data types.

*) It offers a variety of programming structures.

*) It supports structured programming through functions and procedures.

*) It supports object oriented programming.

*) It supports developing web applications and server pages.

ADVANTAGES OF PL/SQL

• SQL is the standard database language and PL/SQL is strongly integrated with SQL.

• PL/SQL allows sending an entire block of statements to the database at one time. This

reduces network traffic and provides high performance for the applications.

• PL/SQL give high productivity to programmers as it can query, transform, and update

data in a database.

• PL/SQL saves time on design and debugging by strong features, such as exception

handling, encapsulation, data hiding, and object-oriented data types.

• Applications written in PL/SQL are fully portable.

• PL/SQL provides high security level.

• PL/SQL provides access to predefined SQL packages.

• PL/SQL provides support for Object-Oriented Programming.

• PL/SQL provides support for Developing Web Applications and Server Pages

EX:14

calculate total and percentage of marks

Aim:

Write a PL/SQL code to calculate total and percentage of marks of the students in

four subjects.

SOLUTION:

SQL> declare

rno number(10);

s1 number(10);

s2 number(10);

s3 number(10);

s4 number(10);

tot number(10);

per number(4);

begin

rno:=011;

s1:=90;

s2:=91;

s3:=98;

s4:=98;

tot:=s1+s2+s3+s4;

per:=tot/4;

dbms_output.put_line('Regno s1 s2 s3 s4 total per');

dbms_output.put_line(rno||' '||s1||' '||s2||' '||s3||' '||s4||'

'||tot||' '||per);

end;

Output

Regno s1 s2 s3 s4 total per

11 90 91 98 98 377 94

Statement processed.

PL/SQL procedure successfully completed.

Result :


EX:15

IF THEN ELSE

Aim:

Program using if then else in PL/SQL

Syntax:

Syntax for IF-THEN statement is:

IF condition THEN

S;

END IF;

Where condition is a Boolean or relational condition and S is a simple or compound

statement.

Example of an IF-THEN statement is:

IF (a <= 20) THEN

c:= c+1;

END IF;

Let us try a complete example that would illustrate the concept:

DECLARE

a number(2) := 10;

BEGIN

a:= 10;

IF( a < 20 ) THEN

dbms_output.put_line('a is less than 20 ' );

END IF;

dbms_output.put_line('value of a is : ' || a);

END;

When the above code is executed at SQL prompt, it produces the following result:

a is less than 20

value of a is : 10


Result :


EX:16

Simple programs using loop, while and for iterative control statement.

AIM:

To generate first 10 natural numbers using loop, while and for.

Algorithm:

Step 1: Declare the variable I.

Step 2: Store the valid data 1 in I.

Step 3: Use LOOP statement

Step 4: Display the first value.

Step 5: Increment the value of I by 1 value.

Step 6: check the value up to 10 no. and repeat the loop

Step 7: If condition exceeds the given value 10, the loop will be

terminated.

/* using loop statement */

Declare

I number;

Begin

I:=1;

Loop

Dbms_output.put_line (I);

I:=I+1;

Exit when I>100;

End loop;

End;

Algorithm: for WHILE loop


Step 2: Store the valid data 1 in I.

Step 3: Use WHILE statement

Step 4: Check the value of I with value 100.

Step 5: if the value of I reached to 100 the loop will be terminated

Step 6: otherwise display value of I

Step 7: increment the next value of I using I=I+1.

/* using while */

Declare

I number;

Begin

I: =1;

While (I<=100)

Loop


I: =I+1;

End loop;

End;

Algorithm:


Step 2: Store the value 1 in var. I.

Step 3: Use For… LOOP statement

Step 4: Display the first value of I.

Step 5: Increment the value of I by 1 value.

Step 6: check the value up to 100 no. and repeat the loop

Step 7: if the loop exceeds the value 100 then the loop will be terminated.

/* using for loop*/

Begin

For I in 1..100

loop


End loop;

End;

Valid Test Data:

OUTPUT

1

2

3

4

5

6

7

8

9

10

Result :


Ex.No:17

Nested loop

AIM:

Program using nested loop in Pl/SQL.

Program:

DECLARE

i number(3);

j number(3);

BEGIN

i := 2;

LOOP

j:= 2;

LOOP

exit WHEN ((mod(i, j) = 0) or (j = i));

j := j +1;

END LOOP;

IF (j = i ) THEN

dbms_output.put_line(i || ' is prime');

END IF;

i := i + 1;

exit WHEN i = 50;

END LOOP;

END;


2 is prime

3 is prime

5 is prime

7 is prime

11 is prime

13 is prime

17 is prime

19 is prime

23 is prime

29 is prime

31 is prime

37 is prime

41 is prime

43 is prime

47 is prime


Result :


Ex.No:18

Employment

AIM

EXERCISE ON PL/SQL CURSORS

DESCRIPTION

• Oracle creates a memory area, known as context area, for processing an SQL statement,

which contains all information needed for processing the statement, for example, number

of rows processed etc.

• A cursor is a pointer to this context area. PL/SQL controls the context area through a

cursor. A cursor holds the rows (one or more) returned by a SQL statement. The set of

rows the cursor holds is referred to as the active set.

• You can name a cursor so that it could be referred to in 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

IMPLICIT CURSORS

DESCRIPTION

*) Implicit cursors are automatically created by Oracle whenever an SQL statement is

executed, when there is no explicit cursor for the statement.

*) Programmers cannot control the implicit cursors and the information in it.

PROGRAM

DECLARE

total_rows number(2);

BEGIN

UPDATE customers

SET salary = salary + 500;

IF sql%notfound THEN

dbms_output.put_line('no customers selected');

ELSIF sql%found THEN

total_rows := sql%rowcount;

dbms_output.put_line( total_rows || ' customers selected ');

END IF;

END;

OUTPUT

6 customers selected


EXPLICIT CURSOR

DESCRIPTION

• Explicit cursors are programmer defined cursors for gaining more control over the

context area.

• An explicit cursor 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.

SYNTAX

CURSOR cursor_name IS select_statement;

PROGRAM

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;

dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);

EXIT WHEN c_customers%notfound;

END LOOP;

CLOSE c_customers;

END;

OUTPUT

1 Ramesh Ahmedabad

2 Khilan Delhi

3 kaushik Kota

4 Chaitali Mumbai

• PL/SQL procedure successfully completed.

Result :


Ex.No:19

Procedure

AIM

EXERCISE ON PL/SQL PROCEDURES

Creating a Procedure:

CREATE [OR REPLACE] PROCEDURE procedure_name

[(parameter_name [IN | OUT | IN OUT] type [, ...])]

{IS | AS}

BEGIN

< procedure_body >

END procedure_name;

Example:

The following example creates a simple procedure that displays the

string 'Hello World!' on the screen when executed.

CREATE OR REPLACE PROCEDURE greetings

AS

BEGIN

dbms_output.put_line('Hello World!');

END;

/When above code is executed using SQL prompt, it will produce the following result:

Procedure created.

Executing a Standalone Procedure

A standalone procedure can be called in two ways:

Using the EXECUTE keyword

Calling the name of the procedure from a PL/SQL block

EXECUTE greetings;

Hello World


The procedure can also be called from another PL/SQL block:

BEGIN

greetings;

END;

The above call would display:

Hello World


Deleting a Standalone Procedure

A standalone procedure is deleted with the DROP PROCEDURE statement. Syntax

for deleting a procedure is:

DROP PROCEDURE procedure-name;

So you can drop greetings procedure by using the following statement:

BEGIN

DROP PROCEDURE greetings;

END;

/

PROGRAM:

DECLARE

a number;

b number;

c number;

PROCEDURE findMin(x IN number, y IN number, z OUT number) IS

BEGIN

IF x < y THEN

z:= x;

ELSE

z:= y;

END IF;

END;

BEGIN

a:= 23;

b:= 45;

findMin(a, b, c);

dbms_output.put_line(' Minimum of (23, 45) : ' || c);

END;


Minimum of (23, 45): 23


Result :


Ex.No:20

FUNCTIONS

AIM

EXERCISE ON FUNCTIONS

DESCRIPTION

• A function is a named PL/SQL Block which is similar to a procedure. The major

difference between a procedure and a function is, a function must always return a value,

but a procedure may or may not return a value.

• A standalone function is created using the CREATE FUNCTION statement.

SYNTAX

CREATE [OR REPLACE] FUNCTION function_name

[(parameter_name [IN | OUT | IN OUT] type [, ...])]

RETURN return_datatype

{IS | AS}

BEGIN

< function_body >

END [function_name];

PROGRAM

DECLARE

a number;

b number;

c number;

FUNCTION findMax(x IN number, y IN number)

RETURN number

IS

z number;

BEGIN

IF x > y THEN

z:= x;

ELSE

Z:= y;

END IF;

RETURN z;

END;

BEGIN

a:= 23;

b:= 45;

c := findMax(a, b);

dbms_output.put_line(' Maximum of (23,45): ' || c);

END;

OUTPUT

Maximum of (23,45): 78


Create a store function that accepts 2 numbers and returns the addition of passed

values. Also write the code to call your function.

SOLUTION:

SQL>

create or replace function addition(a number,b number)

return number

is

begin

dbms_output.put('the sum of '||a||' and '||b||' is :');

return (a+b);

end;

SQL> /

Function created.

SQL> begin

dbms_output.put_line(addition(6,78));

end;

SQL> /

the sum of 6 and 78 is: 84


Result :


Ex.No:21 TRIGGERS

AIM

EXERCISE ON TRIGGERS

DESCRIPTION

Triggers are stored programs, which are automatically executed or fired when some

events occur. Triggers are in fact, written to be executed in response to any of the

following events:

• A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).

• A database definition (DDL) statement (CREATE, ALTER, or DROP).

• A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or

SHUTDOWN).

Triggers could be defined on the table, view, schema, or database with which the event is

associated

SYNTAX

CREATE [OR REPLACE ] TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF }

{INSERT [OR] | UPDATE [OR] | DELETE}

[OF col_name]

ON table_name

[REFERENCING OLD AS o NEW AS n]

[FOR EACH ROW]

WHEN (condition)

DECLARE

Declaration-statements

BEGIN

Executable-statements

EXCEPTION

Exception-handling-statements

END;

PROGRAM

CREATE OR REPLACE TRIGGER display_salary_changes

BEFORE DELETE OR INSERT OR UPDATE ON customers

FOR EACH ROW

WHEN (NEW.ID > 0)

DECLARE

sal_diff number;

BEGIN

sal_diff := :NEW.salary - :OLD.salary;

dbms_output.put_line('Old salary: ' || :OLD.salary);

dbms_output.put_line('New salary: ' || :NEW.salary);

dbms_output.put_line('Salary difference: ' || sal_diff);

END;

OUTPUT

Trigger created.

TRIGGERING A TRIGGER

PROGRAM

INSERT statement which will create a new record in the table:

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (7, 'Kriti', 22, 'HP', 7500.00 );

OUTPUT

Old salary:

New salary: 7500

Salary difference:

UPDATE statement which will update an existing record in the table:

UPDATE customers

SET salary = salary + 500

WHERE id = 2;

OUTPUT

Old salary: 1500

New salary: 2000

Salary difference: 500

Result:

Thus the trigger was created and executed successfully.

lab manual edited

Documents