INDEX

S.No	Date	Name of the Experiment	Page No	Remarks
		Introduction to SQL	3
1.		Creation, altering and dropping of tables and inserting rows into a table	14
2.		Queries (along with sub Queries)	18
3.		Queries using Aggregate functions GROUP BY, HAVING and Creation and dropping of Views.	23
4.		Queries using Conversion functions, string functions, date functions	26
		PL/SQL Introduction	29
5.		Write a PL/SQL program to find the total and average of 4 subjects and display the grade	31
6.		Write a PL/SQL program to find the largest of three numbers	33
7.		Write a PL/ SQL program to generate Fibonacci series	35
8.		Write a PL/SQL Program to display the number in Reverse Order	36
		Exceptions,cursors,Sub-Programs,Triggers	38
9		Write a PL/SQL Program to demonstrate the Predefined Exception	42
10		Write a PL/SQL Program to demonstrate User defined Exception	43
11		Write a PL/SQL Program to demonstrate explicit cursor	44
12		Write a PL/SQL Program to demonstrate implicit cursor Name of the Experiment	45
13		Write a PL/SQL Program to demonstrate ref cursor	46
14		Write a PL/SQL Program to demonstrate procedure	47
15		PL/SQL Program to demonstrate function	48
16		PL/SQL Program to demonstrate package	49
17		Write a PL/SQL Program to demonstrate statement level trigger	50
18		Write a PL/SQL Program to demonstrate row level trigger	50
		Queries	51

1. INTRODUCTION

Oracle has many tools such as SQL * PLUS, Oracle Forms, Oracle Report Writer, Oracle Graphics etc.

v SQL * PLUS: The SQL * PLUS tool is made up of two distinct parts. These are

· Interactive SQL: Interactive SQL is designed for create, access and manipulate data structures like tables and indexes.

· PL/SQL: PL/SQL can be used to developed programs for different applications.

v Oracle Forms: This tool allows you to create a data entry screen along with the suitable menu objects. Thus it is the oracle forms tool that handles data gathering and data validation in a commercial application.

v Report Writer: Report writer allows programmers to prepare innovative reports using data from the oracle structures like tables, views etc. It is the report writer tool that handles the reporting section of commercial application.

v Oracle Graphics: Some of the data can be better represented in the form of pictures. The oracle graphics tool allows programmers to prepare graphs using data from oracle structures like tables, views etc.

SQL (Structured Query Language):

Structured Query Language is a database computer language designed for managing data in relational database management systems(RDBMS), and originally based upon Relational Algebra. Its scope includes data query and update, schema creation and modification, and data access control. SQL was one of the first languages for Edgar F. Codd's relational model in his influential 1970 paper, "A Relational Model of Data for Large Shared Data Banks"[3] and became the most widely used language for relational databases.

· IBM developed SQL in mid of 1970’s.

· Oracle incorporated in the year 1979.

· SQL used by IBM/DB2 and DS Database Systems.

· SQL adopted as standard language for RDBS by ASNI in 1989.

DATA TYPES:

1. CHAR (Size): This data type is used to store character strings values of fixed length. The size in brackets determines the number of characters the cell can hold. The maximum number of character is 255 characters.

2. VARCHAR (Size) / VERCHAR2 (Size): This data type is used to store variable length alphanumeric data. The maximum character can hold is 2000 character.

3. NUMBER (P, S): The NUMBER data type is used to store number (fixed or floating point). Number of virtually any magnitude may be stored up to 38 digits of precision. Number as large as 9.99 * 10 124. The precision (p) determines the number of places to the right of the decimal. If scale is omitted then the default is zero. If precision is omitted, values are stored with their original precision up to the maximum of 38 digits.

4. DATE: This data type is used to represent date and time. The standard format is dd-mm-yy as in 17-SEP-2009. To enter dates other than the standard format, use the appropriate functions. Date time stores date in the 24-Hours format. By default the time in a date field is 12:00:00 am, if no time portion is specified. The default date for a date field is the first day the current month.

5. LONG: This data type is used to store variable length character strings containing up to 2GB. Long data can be used to store arrays of binary data in ASCII format. LONG values cannot be indexed, and the normal character functions such as SUBSTR cannot be applied.

6. RAW: The RAW data type is used to store binary data, such as digitized picture or image. Data loaded into columns of these data types are stored without any further conversion. RAW data type can have a maximum length of 255 bytes. LONG RAW data type can contain up to 2GB.

interactive SQL:

syntax : verb(Parameter_1,Parameter_2,Parameter_3,........Parameter_n);

SQL language is sub-divided into several language elements, including:

§ Clauses, which are in some cases optional, constituent components of statements and queries.

§ Expressions, which can produce either scalar values or tables consisting of columns and rows of data.

§ Predicates which specify conditions that can be evaluated to SQL three-valued logic (3VL) Boolean truth values and which are used to limit the effects of statements and queries, or to change program flow.

§ Queries which retrieve data based on specific criteria.

§ Statements which may have a persistent effect on schemas and data, or which may control transactions, program flow, connections, sessions, or diagnostics.

§ SQL statements also include the semicolon (";") statement terminator. Though not required on every platform, it is defined as a standard part of the SQL grammar.

§ Insignificant white space is generally ignored in SQL statements and queries, making it easier to format SQL code for readability.

There are five types of SQL statements. They are:

1. data definition LANGUAGE (ddl)

2. data manipulation language (dml)

3. DATA RETRIEVAL LANGUAGE (DRL)

4. TRANSATIONAL CONTROL LANGUAGE (TCL)

5. DATA CONTROL LANGUAGE (DCL)

1. data definition LANGUAGE (ddl): The Data Definition Language (DDL) is used to create and destroy databases and database objects. These commands will primarily be used by database administrators during the setup and removal phases of a database project. Let's take a look at the structure and usage of four basic DDL commands:

1. CREATE 2. ALTER 3. DROP 4. RENAME

1. CREATE:

(a)create table: This is used to create a new relation and the corresponding

Syntax: create table relation_name

(field_1 data_type(Size),field_2 data_type(Size), .. . );

Example:

SQL>create table Student (sno NUMBER(3),sname char(10),class char(5));

(b)create TABLE..as select....: This is used to create the structure of a new relation from the structure of an existing relation.

Syntax: create table (relation_name_1, field_1,field_2,.....field_n) AS SELECT field_1,field_2,...........field_n from relation_name_2;

Example: SQL>create table std(rno,sname) as select sno,sname from student;

2. ALTER:

(a)ALTER TABLE ...ADD...: This is used to add some extra fields into existing relation.

Syntax: ALTER TABLE relation_name ADD(new field_1 data_type(size), new field_2 data_type(size),..);

Example : SQL>ALTER TABLE std ADD(Address CHAR(10));

(b)ALTER table...modify...: This is used to change the width as well as data type of fields of existing relations.

Syntax: alter table relation_name modify (field_1 newdata_type(Size), field_2 newdata_type(Size),....field_newdata_type(Size));

Example:SQL>alter table student modify(sname varchar(10),class varchar(5));

3. drop table: This is used to delete the structure of a relation. It permanently deletes the records in the table.

Syntax: drop table relation_name;

Example: SQL>drop table std;

4. Rename: It is used to modify the name of the existing database object.

Syntax: RENAME table old_relation_name TO new_relation_name;

Example: SQL>rename table std to std1;

5. TRUNCATE: This command will remove the data permanently. But structure will not be removed.

Syntax: TRUNCATE TABLE <Table name>

Example TRUNCATE TABLE student;

Difference between Truncate & Delete:-

ü By using truncate command data will be removed permanently & will not get back where as by using delete command data will be removed temporally & get back by using roll back command.

ü By using delete command data will be removed based on the condition where as by using truncate command there is no condition.

ü Truncate is a DDL command & delete is a DML command.

2. data manipulation language (dml): The Data Manipulation Language (DML) is used to retrieve, insert and modify database information. These commands will be used by all database users during the routine operation of the database. Let's take a brief look at the basic DML commands:

1. INSERT 2. UPDATE 3. DELETE

1. insert into: This is used to add records into a relation. These are three type of insert into queries which are as

a) Inserting a single record

Syntax: insert into relationname(field_1,field_2,.field_n)values

(data_1,data_2,........data_n);

Example: SQL>insert into student(sno,sname,class,address)VALUES

(1,’Ravi’,’M.Tech’,’Palakol’);

b) Inserting all records from another relation

Syntax: insert into relation_name_1 select field_1,field_2,field_n

FROM relation_name_2 WHERE field_x=data;

Example: SQL>insert into std select sno,sname from student

where name = ‘Ramu‘;

c) Inserting multiple records

Syntax: insert into relation_name field_1,field_2,.....field_n) values

(&data_1,&data_2,........&data_n);

Example: SQL>insert into student(sno,sname,class,address)

VALUES(&sno,’&sname’,’&class’,’&address’);

Enter value for sno: 101

Enter value for name: Ravi

Enter value for class: M.Tech

Enter value for name: Palakol

2. update-set-WHERE: This is used to update the content of a record in a relation.

Syntax: SQL>update relation name set field_name1=data,field_name2=data,

where field_name=data;

Example: SQL>update student set sname = ‘kumar’ WHERE sno=1;

3. delete-from: This is used to delete all the records of a relation but it will retain the structure of that relation.

a) delete-from: This is used to delete all the records of relation.

Syntax: SQL>delete from relation_name;

example: SQL>delete from std;

b) delete -from-WHERE: This is used to delete a selected record from a relation.

Syntax: SQL>delete from relation_name WHERE condition;

Example: SQL>delete from student WHERE sno = 2;

3. DRL(DATA RETRIEVAL LANGUAGE): Retrieves data from one or more tables.

1. select from: To display all fields for all records.

Syntax : select * from relation_name;

Example : SQL> select * from dept;

DEPTNO DNAME LOC

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

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

2. Select from: To display a set of fields for all records of relation.

Syntax: select a set of fields FROM relation_name;

Example: SQL> select deptno, dname from dept;

DEPTNO DNAME

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

10 ACCOUNTING

20 RESEARCH

30 SALES

3. select - from -WHERE: This query is used to display a selected set of fields for a selected set of records of a relation.

Syntax: select a set of fields from relation_name where condition;

Example: SQL> select * FROM dept WHERE deptno<=20;

DEPTNO DNAME LOC

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

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

4. select - from -group BY: This query is used to group to all the records in a relation together for each and every value of a specific key(s) and then display them for a selected set of fields the relation.

Syntax: select a set of fields FROM relation_name GROUP BY field_name;

Example: SQL> SELECT EMPNO, SUM (SALARY) FROM EMP GROUP BY EMPNO;

EMPNO SUM (SALARY)

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

1 3000

2 4000

3 5000

4 6000

4 rows selected.

5. select - from -order by: This query is used to display a selected set of fields from a relation in an ordered manner base on some field.

Syntax: select a set of fields FROM relation_name

order by field_name;

Example: SQL> SELECT empno,ename,job FROM emp ORDER BY job;

EMPNO ENAME JOB

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

4 RAVI MANAGER

2 aravind Manager

1 sagar clerk

3 Laki clerk

4rows selected.

6. join using select - from - order by: This query is used to display a set of fields from two relations by matching a common field in them in an ordered manner based on some fields.

Syntax: select a set of fields from both relations from relation_1, relation_2 WHERE relation_1.field_x = relation_2.field_y order by field_z;

Example: SQL>SELECT empno,ename,job,dname FROM emp,dept

WHERE emp.deptno = 20 ORDER BY job;

EMPNO ENAME JOB DNAME

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

7788 SCOTT ANALYST ACCOUNTING

7902 FORD ANALYST ACCOUNTING

------

7566 JONES MANAGER OPERATIONS

7566 JONES MANAGER SALES

20 rows selected.

7. join using select - from - group by: This query is used to display a set of fields from two relations by matching a common field in them and also group the corresponding records for each and every value of a specified key(s) while displaying.

Syntax: select a set of fields from both relations FROM relation_1,relation_2 WHERE relation_1.field-x=relation_2.field-y group by field-z;

Example: SQL> SELECT empno,SUM(SALARY) FROM emp,dept

WHERE emp.deptno =20 GROUP BY empno;

EMPNO SUM (SALARY)

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

7369 3200

7566 11900

7788 12000

7876 4400

8. union: This query is used to display the combined rows of two different queries, which are having the same structure, without duplicate rows.

Syntax: SELECT field_1,field_2,....... FROM relation_1 WHERE (Condition) UNION SELECT field_1,field_2,....... FROM relation_2 WHERE (Condition);

Example:

SQL> SELECT * FROM STUDENT;

SNO SNAME

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

1 kumar

2 ravi

3 ramu

SQL> SELECT * FROM STD;

SNO SNAME

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

3 ramu

5 lalitha

9 devi

1 kumar

SQL> SELECT * FROM student UNION SELECT * FROM std;

SNO SNAME

---- ------

1 kumar

2 ravi

3 ramu

5 lalitha

9 devi

9. interset: This query is used to display the common rows of two different queries, which are having the same structure, and to display a selected set of fields out of them.

Syntax: select field_1,field_2,.. FROM relation_1 WHERE

(Condition) INTERSECT SELECT field_1,field_2,.. FROM relation_2 WHERE(Condition);

Example : SQL> SELECT * FROM student INTERSECT SELECT * FROM std;

SNO SNAME

---- -------

1 Kumar

10. minus: This query is used to display all the rows in relation_1,which are not having in the relation_2.

Syntax: select field_1,field_2,......FROM relation_1

WHERE(Condition) MINUS SELECT field_1,field_2,.....

FROM relation_2 WHERE(Conditon);

SQL> SELECT * FROM student MINUS SELECT * FROM std;

SNO SNAME

---- -------

2 RAVI

3 RAMU

3. TRANSATIONAL CONTROL LANGUAGE (T.C.L):

A transaction is a logical unit of work. All changes made to the database can be referred to as a transaction. Transaction changes can be mode permanent to the database only if they are committed a transaction begins with an executable SQL statement & ends explicitly with either role back or commit statement.

1. COMMIT: This command is used to end a transaction only with the help of the commit command transaction changes can be made permanent to the database.

Syntax: SQL>COMMIT;

Example: SQL>COMMIT;

2. SAVE POINT: Save points are like marks to divide a very lengthy transaction to smaller once. They are used to identify a point in a transaction to which we can latter role back. Thus, save point is used in conjunction with role back.

Syntax: SQL>SAVE POINT ID;

Example: SQL>SAVE POINT xyz;

3. ROLE BACK: A role back command is used to undo the current transactions. We can role back the entire transaction so that all changes made by SQL statements are undo (or) role back a transaction to a save point so that the SQL statements after the save point are role back.

Syntax: ROLE BACK( current transaction can be role back)

ROLE BACK to save point ID;

Example: SQL>ROLE BACK;

SQL>ROLE BACK TO SAVE POINT xyz;

4. DATA CONTROL LANGUAGE (D.C.L):

DCL provides uses with privilege commands the owner of database objects (tables), has the soul authority ollas them. The owner (data base administrators) can allow other data base uses to access the objects as per their requirement

1. GRANT: The GRANT command allows granting various privileges to other users and allowing them to perform operations with in their privileges

For Example, if a uses is granted as ‘SELECT’ privilege then he/she can only view data but cannot perform any other DML operations on the data base object GRANTED privileges can also be withdrawn by the DBA at any time

Syntax: SQL>GRANT PRIVILEGES on object_name To user_name;

Example: SQL>GRANT SELECT, UPDATE on emp To hemanth;

2. REVOKE: To with draw the privileges that has been GRANTED to a uses, we use the REVOKE command

Syntax: SQL>REVOKE PRIVILEGES ON object-name FROM user_name;

Example: SQL>REVOKE SELECT, UPDATE ON emp FROM ravi;

1. Creation, altering and dropping of tables and inserting rows into a table (use constraints while creating tables) examples using SELECT command.

1. CREATE:

(a)create table: This is used to create a new relation

Syntax: create table relation_name

(field_1 data_type(Size),field_2 data_type(Size), .. . );

Example:

SQL>create table Student (sno NUMBER(3) PRIMARY KEY ,sname

char(10),class char(5));

2. ALTER:

(a)ALTER TABLE ...ADD...: This is used to add some extra fields into existing relation.

Syntax: ALTER TABLE relation_name ADD(new field_1 data_type(size), new field_2 data_type(size),..);

Example : SQL>ALTER TABLE std ADD(Address CHAR(10));

(b)ALTER table...modify...: This is used to change the width as well as data type of fields of existing relations.

Syntax: alter table relation_name modify (field_1 newdata_type(Size), field_2 newdata_type(Size),....field_newdata_type(Size));

Example: SQL>alter table student modify(sname varchar(10),class

varchar(5));

3. drop table: This is used to delete the structure of a relation. It permanently deletes the records in the table.

Syntax: drop table relation_name;

Example: SQL>drop table std;

4. INSERT:

Syntax: insert into relation_name field_1,field_2,.....field_n) values

(&data_1,&data_2,........&data_n);

Example: SQL>insert into student(sno,sname,class,address)

VALUES(&sno,’&sname’,’&class’,’&address’);

Enter value for sno: 101

Enter value for name: SIRISHA

Enter value for class: CSE

Enter value for address: Palakol

5. select from: To display all fields for all records.

Syntax : select * from relation_name;

Example : SQL> select * from student;

SNO SNAME CLASS ADDRESS

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

101 SIRISHA CSE PALAKOL

102 DEVAKI CSE NARSAPUR

103 KUMAR CAD BHIMAVARAM

104 RAVI VLSI PALAKOL

2. Select from: To display a set of fields for all records of relation.

Syntax: select a set of fields FROM relation_name;

Example: SQL> select sno, sname from student;

SNO SNAME

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

101 SIRISHA

102 DEVAKI

103 KUMAR

104 RAVI

3. select - from -WHERE: This query is used to display a selected set of fields for a selected set of records of a relation.

Syntax: select a set of fields from relation_name where condition;

Example: SQL> select * FROM student WHERE class=’CSE’;

SNO SNAME CLASS ADDRESS

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

101 SIRISHA CSE PALAKOL

102 DEVAKI CSE NARSAPUR

There are 5 constraints available in ORACLE:

1. NOT NULL: When a column is defined as NOTNULL, then that column becomes a mandatory column. It implies that a value must be entered into the column if the record is to be accepted for storage in the table.

Syntax:

CREATE TABLE Table_Name(column_name data_type(size) NOT NULL, );

Example:

CREATE TABLE student (sno NUMBER(3)NOT NULL, name CHAR(10));

2. UNIQUE: The purpose of a unique key is to ensure that information in the column(s) is unique i.e. a value entered in column(s) defined in the unique constraint must not be repeated across the column(s). A table may have many unique keys.

Syntax:

CREATE TABLE Table_Name(column_name data_type(size) UNIQUE, ….);

Example:

CREATE TABLE student (sno NUMBER(3) UNIQUE, name CHAR(10));

3. CHECK: Specifies a condition that each row in the table must satisfy. To satisfy the constraint, each row in the table must make the condition either TRUE or unknown (due to a null).

Syntax:

CREATE TABLE Table_Name(column_name data_type(size) CHECK(logical expression), ….);

Example: CREATE TABLE student (sno NUMBER (3), name CHAR(10),class CHAR(5),CHECK(class IN(‘CSE’,’CAD’,’VLSI’));

4. PRIMARY KEY: A field which is used to identify a record uniquely. A column or combination of columns can be created as primary key, which can be used as a reference from other tables. A table contains primary key is known as Master Table.

ü It must uniquely identify each record in a table.

ü It must contain unique values.

ü It cannot be a null field.

ü It cannot be multi port field.

ü It should contain a minimum no. of fields necessary to be called unique.

Syntax:

CREATE TABLE Table_Name(column_name data_type(size) PRIMARY KEY, ….);

Example:

CREATE TABLE faculty (fcode NUMBER(3) PRIMARY KEY, fname CHAR(10));

5. FOREIGN KEY: It is a table level constraint. We cannot add this at column level. To reference any primary key column from other table this constraint can be used. The table in which the foreign key is defined is called a detail table. The table that defines the primary key and is referenced by the foreign key is called the master table.

Syntax: CREATE TABLE Table_Name(column_name data_type(size)

FOREIGN KEY(column_name) REFERENCES table_name);

Example:

CREATE TABLE subject (scode NUMBER (3) PRIMARY KEY,

subname CHAR(10),fcode NUMBER(3),

FOREIGN KEY(fcode) REFERENCE faculty );

Defining integrity constraints in the alter table command:

Syntax: ALTER TABLE Table_Name ADD PRIMARY KEY (column_name);

Example: ALTER TABLE student ADD PRIMARY KEY (sno);

(Or)

Syntax: ALTER TABLE table_name ADD CONSTRAINT constraint_name

PRIMARY KEY(colname)

Example: ALTER TABLE student ADD CONSTRAINT SN PRIMARY KEY(SNO)

Dropping integrity constraints in the alter table command:

Syntax: ALTER TABLE Table_Name DROP constraint_name;

Example: ALTER TABLE student DROP PRIMARY KEY;

(or)

Syntax: ALTER TABLE student DROP CONSTRAINT constraint_name;

Example: ALTER TABLE student DROP CONSTRAINT SN;

2. Queries (along with sub Queries)

Selecting data from sailors table

SQL> select * from sailors;

SID SNAME AGE RATING

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

22 dustin 7 45

29 brutus 1 33

31 lubber 8 55

32 andy 8 25.5

58 rusty 10 35

64 horatio 7 35

71 zorba 10 40

74 horatio 9 40

85 art 3 25.5

95 bob 3 63.5

10 rows selected.

Selecting data from reserves table

SQL> select * from reserves;

SID BID DAY

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

22 101 10-OCT-98

22 102 10-OCT-98

22 103 10-AUG-98

22 104 10-JUL-98

31 102 11-OCT-98

31 103 11-JUN-98

31 104 11-DEC-98

64 101 09-MAY-98

64 102 09-AUG-98

74 104 09-AUG-98

10 rows selected.

Selecting data from boat table

SQL> select * from boats;

BID BNAME COLOR

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

101 interlake blue

102 interlake red

103 clipper green

104 marine red

Q: find the names of sailors who have reserved boat 103.

SQL> select s.sname from sailors s where s.sid in (select r.sid

from reserves r where r.bid=103);

SNAME

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

dustin

lubber

2 rows selected.

Q: find the names of sailors who have reserved a red boat.

SQL> select s.sname from sailors s where s.sid in

(select r.sid from reserves r where r.bid in (select b.bid

from boats b where b.color='red'));

SNAME

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

dustin

lubber

horatio

4 rows selected.

Q: Find the name and age of the oldest sailors.

SQL> select MAX(s.age)from sailors s;

MAX(S.AGE)

----------

Q: Count the number of sailors.

SQL> select COUNT(s.age)from sailors s

COUNT(S.AGE)

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

Q: Count the number of different sailors names.

SQL> select COUNT(distinct s.sname)* from sailors s

COUNT(DISTINCTS.SNAME)

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

Q: find the names of sailors who have not reserved a red boat.

SQL> select s.sname

2 from sailors s

3 where s.sid not in (select r.sid

4 from reserves r

5 where r.bid in (select b.bid

6 from boats b

7 where b.color='red'))

SNAME

--------

brutus

andy

rusty

zorba

art

bob

6 rows selected.

Q: find the names of sailors who have not reserved boat 103.

SQL> select s.sname from sailors s where exists(select * from

reserves r where r.bid=103 and r.sid=s.sid);

SNAME

-------

dustin

lubber

2 rows selected.

Q: find the names of sailors who have reserved at least one boat.

SQL> select s.sname from sailors s, reserves r where

s.sid=r.sid;

SNAME

----------

dustin

lubber

horatio

10 rows selected.

Q: Compute increments for the ratings of persons who have sailed two different boats on the same day.

SQL> select s.sname,s.rating+1 As rating from sailors s,

reserves r1, reserves r2 where s.sid=r1.sid AND s.sid=r2.sid

AND r1.day=r2.day AND r1.bid<>r2.bid

SNAME RATING

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

dustin 46

Q: Find the names of sailors who have reserved a red or a green boat.

SQL> select s.sname from sailors s, reserves r,boats b where s.sid=r.sid AND r.bid=b.bid AND (b.color='red' OR

b.color='green')

SNAME

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

dustin

lubber

horatio

8 rows selected.

Q: find the all sids of sailors who have rating 10 or have reserved boat 104..

SQL> select s.sid from sailors s where s.rating=10 union

select r.sid from reserves r where r.bid=104;

SID

------

Q: Find the number of reservations for each red boat.

SQL> select b.bid,count(*)As sailorcount from boats b,

reserves r where r.bid=b.bid AND b.color='red' group by b.bid;

BID SAILORCOUNT

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

102 3

104 3

Q: Find the minimum age of the sailor.

SQL> select min(s.age) from sailors s;

MIN(S.AGE)

----------

Q: Find the sum of the rating of sailors.

SQL> select sum(s.rating)from sailors s;

SUM(S.RATING)

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

397.5

Q: find the id and names of sailors who have reserved id=22 or age<25.

SQL> select sid,sname from sailors where sid=22 or age<25

SID SNAME

-- --------

22 dustin

3) Queries using Aggregate functions (COUNT, SUM, AVG, MAX and MIN), GROUP BY, HAVING and Creation and dropping of Views.

Aggregative operators: In addition to simply retrieving data, we often want to perform some computation or summarization. SQL allows the use of arithmetic expressions. We now consider a powerful class of constructs for computing aggregate values such as MIN and SUM.

1. Count: COUNT following by a column name returns the count of tuple in that column. If DISTINCT keyword is used then it will return only the count of unique tuple in the column. Otherwise, it will return count of all the tuples (including duplicates) count (*) indicates all the tuples of the column.

Syntax: COUNT (Column name)

Example: SELECT COUNT (Sal) FROM emp;

2. SUM: SUM followed by a column name returns the sum of all the values in that column.

Syntax: SUM (Column name)

Example: SELECT SUM (Sal) From emp;

3. AVG: AVG followed by a column name returns the average value of that column values.

Syntax: AVG (n1,n2..)

Example: Select AVG(10, 15, 30) FROM DUAL;

4. MAX: MAX followed by a column name returns the maximum value of that column.

Syntax: MAX (Column name)

Example: SELECT MAX (Sal) FROM emp;

SQL> select deptno,max(sal) from emp group by deptno;

DEPTNO MAX(SAL)

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

10 5000

20 3000

30 2850

SQL> select deptno,max(sal) from emp group by deptno having max(sal)<3000;

DEPTNO MAX(SAL)

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

30 2850

5. MIN: MIN followed by column name returns the minimum value of that column.

Syntax: MIN (Column name)

Example: SELECT MIN (Sal) FROM emp;

SQL>select deptno,min(sal) from emp group by deptno having min(sal)>1000;

DEPTNO MIN(SAL)

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

10 1300

VIEW: In SQL, 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.

You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.

A view is a virtual table, which consists of a set of columns from one or more tables. It is similar to a table but it doest not store in the database. View is a query stored as an object.

Syntax: create view view_name AS SELECT set of fields FROM relation_name WHERE (Condition)

1. Example:

SQL>create view employee as select empno,ename,job from emp

where job = ‘clerk’;

view created.

sql> select * from employee;

empno ename job

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

7369 smith clerk

7876 adams clerk

7900 james clerk

7934 miller clerk

2.Example:

CREATE VIEW [Current Product List] AS

SELECT ProductID,ProductName

FROM Products

WHERE Discontinued=No

drop view: This query is used to delete a view , which has been already created.

Syntax: drop VIEW view_name;

Example : SQL> DROP VIEW EMPLOYEE;

View dropped

4. Queries using Conversion functions (to_char, to_number and to_date), string functions (Concatenation, lpad, rpad, ltrim, rtrim, lower, upper, initcap, length, substr and instr), date functions (Sysdate, next_day, add_months, last_day, months_between, least, greatest, trunc, round, to_char, to_date)

1. Conversion functions:

To_char: TO_CHAR (number) converts n to a value of VARCHAR2 data type, using the optional number format fmt. The value n can be of type NUMBER, BINARY_FLOAT, or BINARY_DOUBLE.

SQL>select to_char(65,'RN')from dual;

LXV

To_number : TO_NUMBER converts expr to a value of NUMBER data type.

SQL> Select to_number('1234.64') from Dual;

1234.64

To_date: TO_DATE converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type to a value of DATE data type.

SQL>SELECT TO_DATE('January 15, 1989, 11:00 A.M.')FROM DUAL;

TO_DATE('

---------

15-JAN-89

2. String functions:

Concat: CONCAT returns char1 concatenated with char2. Both char1 and char2 can be any of the datatypes

SQL>SELECT CONCAT(‘ORACLE’,’CORPORATION’)FROM DUAL;

ORACLECORPORATION

Lpad: LPAD returns expr1, left-padded to length n characters with the sequence of characters in expr2.

SQL>SELECT LPAD(‘ORACLE’,15,’*’)FROM DUAL;

*********ORACLE

Rpad: RPAD returns expr1, right-padded to length n characters with expr2, replicated as many times as necessary.

SQL>SELECT RPAD (‘ORACLE’,15,’*’)FROM DUAL;

ORACLE*********

Ltrim: Returns a character expression after removing leading blanks.

SQL>SELECT LTRIM(‘SSMITHSS’,’S’)FROM DUAL;

MITHSS

Rtrim: Returns a character string after truncating all trailing blanks

SQL>SELECT RTRIM(‘SSMITHSS’,’S’)FROM DUAL;

SSMITH

Lower: Returns a character expression after converting uppercase character data to lowercase.

SQL>SELECT LOWER(‘DBMS’)FROM DUAL;

dbms

Upper: Returns a character expression with lowercase character data converted to uppercase

SQL>SELECT UPPER(‘dbms’)FROM DUAL;

DBMS

Length: Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.

SQL>SELECT LENGTH(‘DATABASE’)FROM DUAL;

Substr: Returns part of a character, binary, text, or image expression.

SQL>SELECT SUBSTR(‘ABCDEFGHIJ’3,4)FROM DUAL;

CDEF

Instr: The INSTR functions search string for substring. The function returns an integer indicating the position of the character in string that is the first character of this occurrence.

SQL>SELECT INSTR('CORPORATE FLOOR','OR',3,2)FROM DUAL;

3. Date functions:

Sysdate:

SQL>SELECT SYSDATE FROM DUAL;

29-DEC-08

next_day:

SQL>SELECT NEXT_DAY(SYSDATE,’WED’)FROM DUAL;

05-JAN-09

add_months:

SQL>SELECT ADD_MONTHS(SYSDATE,2)FROM DUAL;

28-FEB-09

last_day:

SQL>SELECT LAST_DAY(SYSDATE)FROM DUAL;

31-DEC-08

months_between:

SQL>SELECT MONTHS_BETWEEN(SYSDATE,HIREDATE)FROM EMP;

Least:

SQL>SELECT LEAST('10-JAN-07','12-OCT-07')FROM DUAL;

10-JAN-07

Greatest:

SQL>SELECT GREATEST('10-JAN-07','12-OCT-07')FROM DUAL;

10-JAN-07

Trunc:

SQL>SELECT TRUNC(SYSDATE,'DAY')FROM DUAL;

28-DEC-08

Round:

SQL>SELECT ROUND(SYSDATE,'DAY')FROM DUAL;

28-DEC-08

to_char:

SQL> select to_char(sysdate, "dd\mm\yy") from dual;

24-mar-05.

to_date:

SQL> select to_date(sysdate, "dd\mm\yy") from dual;

24-mar-o5.

PL/SQL Introduction

PL/SQL stands for Procedural Language extension of SQL. PL/SQL is a combination of SQL along with the procedural features of programming languages. It was developed by Oracle Corporation in the early 90’s to enhance the capabilities of SQL.

Oracle uses a PL/SQL engine to processes the PL/SQL statements. A PL/SQL code can be stored in the client system (client-side) or in the database (server-side).

Advantages of PL/SQL:

Block Structures: PL SQL consists of blocks of code, which can be nested within each other. Each block forms a unit of a task or a logical module. PL/SQL Blocks can be stored in the database and reused.
Procedural Language Capability: PL SQL consists of procedural language constructs such as conditional statements (if else statements) and loops like (FOR loops).
Better Performance: PL SQL engine processes multiple SQL statements simultaneously as a single block, thereby reducing network traffic.
Error Handling: PL/SQL handles errors or exceptions effectively during the execution of a PL/SQL program. Once an exception is caught, specific actions can be taken depending upon the type of the exception or it can be displayed to the user with a message.

Syntax of PL/SQL program:

Declare

Variable declaration;

Begin

Executable statements;

end;

Conditional Statements in PL/SQL

As the name implies, PL/SQL supports programming language features like conditional statements, iterative statements.

The programming constructs are similar to how you use in programming languages like Java and C++. In this section I will provide you syntax of how to use conditional statements in PL/SQL programming.

IF THEN ELSE STATEMENT:

1) IF condition THEN

statement 1;

ELSE

statement 2;

END IF;

2) IF condition 1 THEN

statement 1;

statement 2;

ELSIF condtion2 THEN

statement 3;

ELSE

statement 4;

END IF

5. Write a PL/SQL program to find the total and average of 4 subjects and display the grade

declare

java number(10);

dbms number(10);

co number(10);

mfcs number(10);

total number(10);

avgs number(10);

per number(10);

begin

dbms_output.put_line('ENTER THE MARKS');

java:=&java;

dbms:=&dbms;

co:=&co;

mfcs:=&mfcsl;

total:=(java+dbms+co+mfcs);

per:=(total/600)*100;

if java<40 or dbms<40 or co<40 or mfcs<40 then

dbms_output.put_line('FAIL');

if per>75 then

dbms_output.put_line('GRADE A');

elsif per>65 and per<75 then

dbms_output.put_line('GRADE B');

elsif per>55 and per<65 then

dbms_output.put_line('GRADE C');

else

dbms_output.put_line('INVALID INPUT');

end if;

dbms_output.put_line('PERCENTAGE IS '||per);

end;

OUTPUT:

SQL> @ GRADE.sql

Enter value for java: 80

old 11: java:=&java;

new 11: java:=80;

Enter value for dbms: 70

old 12: dbms:=&dbms;

new 12: dbms:=70;

Enter value for co: 89

old 13: co:=&co;

new 13: co:=89;

Enter value for mfcs: 71

old 14: mfcs:=&mfcs;

new 14: mfcs:=71;

GRADE A

PERCENTAGE IS 77

PL/SQL procedure successfully completed.

6. Write a PL/SQL program to find the largest of three numbers

declare

a number;

b number;

c number;

begin

a:=&a;

b:=&b;

c:=&c;

if a=b and b=c and c=a then

dbms_output.put_line('ALL ARE EQUAL');

elsif a>b and a>c then

dbms_output.put_line('A IS GREATER');

elsif b>c then

dbms_output.put_line('B IS GREATER');

else

dbms_output.put_line('C IS GREATER');

end if;

end;

OUTPUT:

SQL> @ GREATESTOF3.sql

Enter value for a: 8

old 6: a:=&a;

new 6: a:=8;

Enter value for b: 9

old 7: b:=&b;

new 7: b:=9;

Enter value for c: 7

old 8: c:=&c;

new 8: c:=7;

B IS GREATER

PL/SQL procedure successfully completed.

Loops in PL/SQL

There are three types of loops in PL/SQL:

1. Simple Loop

2. While Loop

3. For Loop

1. Simple Loop: A Simple Loop is used when a set of statements is to be executed at least once before the loop terminates. An EXIT condition must be specified in the loop, otherwise the loop will get into an infinite number of iterations. When the EXIT condition is satisfied the process exits from the loop.

Syntax:

LOOP

statements;

EXIT;

{or EXIT WHEN condition ;}

END LOOP;

2. While Loop: A WHILE LOOP is used when a set of statements has to be executed as long as a condition is true. The condition is evaluated at the beginning of each iteration. The iteration continues until the condition becomes false.

Syntax:

WHILE <condition>

LOOP statements;

END LOOP;

3. FOR Loop: A FOR LOOP is used to execute a set of statements for a predetermined number of times. Iteration occurs between the start and end integer values given. The counter is always incremented by 1. The loop exits when the counter reaches the value of the end integer.

Syntax:

FOR counter IN val1..val2

LOOP statements;

END LOOP;

7. Write a PL/ SQL program to generate Fibonacci series

declare

a number;

b number;

c number;

n number;

i number;

begin

n:=&n;

a:=0;

b:=1;

dbms_output.put_line(a);

dbms_output.put_line(b);

for i in 1..n-2

loop

c:=a+b;

dbms_output.put_line(c);

a:=b;

b:=c;

end loop;

end;

OUTPUT:

SQL> @ FIBONACCI.sql

Enter value for n: 3

old 8: n:=&n;

new 8: n:=3;

PL/SQL procedure successfully completed.

8. Write a PL/SQL Program to display the number in Reverse Order

declare

a number;

rev number;

d number;

begin

a:=&a;

rev:=0;

while a>0

loop

d:=mod(a,10);

rev:=(rev*10)+d;

a:=trunc(a/10);

end loop;

dbms_output.put_line('no is'|| rev);

end;

OUTPUT:

SQL> @ REVERSE2.sql

Enter value for a: 536

old 6: a:=&a;

new 6: a:=536;

no is 635

PL/SQL procedure successfully completed.

Exceptions,Cursors,Sub-Programs,Triggers

Exceptions

When a program is executed certain errors are automatically recognized by oracle and certain error situations must recognized by the program itself.

Errors in general are referred to as Exceptions.

Exception : Exceptions are error situations. Error situations can be user-defined or system exceptions which are automatically raised by pl/sql. Certain system exceptions raise the following flags,

CURSOR_ALREADY_OPEN - Displayed when the user tries to open a cursor that is already open

DUP_VAL_ON_INDEX - When user tries to insert a duplicate value into a unique column.

INVALID_CURSOR - When user references an invalid cursor or attempts an illegal cursor operation.

INVALID_NUMBER - When user tries to use something other than a number where one is called for.

LOGIN_DENIED - when connect request for user has been denied.

NO_DATA_FOUND - This flag becomes true when sql select statement failed to retrieve any rows.

NOT_LOGGED_ON - User is not connected to ORACLE.

PROGRAM_ERROR - The user hits an PL/SQL internal error.

STORAGE_ERROR - The user hits a PL/SQL memory error.

TIMEOUT_ON_RESOURCE - When user has reached timeout while waiting for an oracle resource.

TRANSACTION_BACKED_OUT - A remote serer has rolled back your transaction.

TOO_MANY_ROWS - This flag becomes true when sql select statement retrieve more than one row and it was supposed to retrieve only one row.

VALUE_ERROR - User encounters an arithemetic, conversion,Truncation or constraint error.

n ZERO_DIVIDE - This flag becomes true when sql select statement tries to divide a number by zero.

n Others - This flag is used to catch any error situations not coded by the programmer in the exception section. Therefore, it must appear last in the Exception section.

Cursor

n A cursor is a private sql work area also called as System Global area

- static Cursors

- Explicit cursor

- Implicit cursor

- Dynamic Cursor

- Ref Cursor.

Cursor Attributes

- % found

- % notfound

- % rowcount

- % isopen

To manipulate a cursor we need cursor attributes.

%found - it returns true if there are any records in the cursor.

%notfound - It returns true if there are no records in the cursor.

%rowcount - It returns the number of records in the cursor.

%isopen - It return true if the cursor is opened for manipulation.

Syntax:

OPEN cursor_name;

General Syntax to fetch records from a cursor is:

FETCH cursor_name INTO record_name;

FETCH cursor_name INTO variable_list;

General Syntax to close a cursor is:

CLOSE cursor_name;

Sub Program

n A program inside a program is called subprogram

- procedures (They do some work)

Syntax:

CREATE OR REPLACE

PROCEDURE HELLOWORLD IS

BEGIN

DBMS_OUTPUT.PUT_LINE(’Hello World!’);

END;

- functions (Computes a value)

Syntax:

CREATE OR REPLACE

FUNCTION function_name (function_params) RETURN return_type IS

BEGIN

function_body

RETURN something_of_return_type;

END;

- pakages (It is a combination of procedure and function).

Syntax:

CREATE PACKAGE name AS  -- specification (visible part)

   -- public type and object declarations

   -- subprogram specifications

END [name];

CREATE PACKAGE BODY name AS  -- body (hidden part)

   -- private type and object declarations

   -- subprogram bodies

[BEGIN

   -- initialization statements]

END [name];

To pass some value or to get some value we require parameters.

- in

- out // parameter types or parameter modes

- in out

Data Base Triggers

it is a condition which is going to fire during the time of insert or update or delete.

implementation of triggers on two levels

- statement level

- row level.

Syntax:

CREATE [OR REPLACE]

TRIGGER trigger_name

BEFORE (or AFTER)

INSERT OR UPDATE [OF COLUMNS] OR DELETE

ON tablename

[FOR EACH ROW [WHEN (condition)]]

BEGIN

...

END;

Statement level trigger fire once independent of no of records effected.

Row level triggers fire for that many times depending on no of records effected

9. Write a PL/SQL Program to demonstrate the Predefined Exception

Declare

A number(14,2);

Begin

Select sal into a from emp where empno=&empno;

Dbms_out.put_line(‘your sal is ‘ ||A);

Exception

When no_data_found then

Dbms_output.put_line(‘NO RECORDS FOUND’);

When too_many_rows then

Dbms_output.put_line(‘duplicate employee found’);

End;

/

OUTPUT:

10. Write a PL/SQL Program to demonstrate User defined Exception

Declare

A number(4):= &no1

B number(4):=&no2

Exp1 exception;

Begin

If b=0 then

Raise exp1

Else

Dbms_output.put_line(a/b);

End if;

Exception

When exp1 then

Dbms_output.put_line(‘Second number must not be zero’);

End;

/

Output

11. Write a PL/SQL Program to demonstrate explicit cursor

Program:

Declare

Cursor c1 is select *from emp;

Rec emp%rowtype;

Begin

Open c1;

Loop

Fetch c1 into rec;

Exit when c1%notfound;

Dbms_output.put_line(rec.empno|| ‘ ‘ ||rec.ename);

End loop;

Close c1;

End;

/

OUTPUT:

12. Write a PL/SQL Program to demonstrate implicit cursor

Program:

Declare

A number(4);

Begin

Delete from emp where deptno=&no;

If sql%found then

A:=sql%rowcount;

Dbms_output.put_line(‘Total records deleted are ‘ || a);

Elsif sql%notfound then

Dbms_output.put_line(‘no records deleted’);

End if;

End;

/

OUTPUT:

13. Write a PL/SQL Program to demonstrate ref cursor

Program:

Declare

Type refcur is refcursor;

C1 refcur;

Rec1 emp%rowtype;

Rec2 dept%rowtype;

Begin

Open c1 for select * from emp;

Loop

Fetch c1 into rec1;

Exit when c1%notfound;

Dbms_output.put_line(rec1.empno||’ ‘ ||rec1.ename);

Endloop;

Close c1;

Open c1 for select * from dept;

Loop

Fetch c1 into rec2;

Exit when c1%notfound;

Dbms_output.put_line(rec2.deptno|| ‘ ‘ || rec2.dname);

Endloop;

Close c1;

End;

/

OUTPUT:

14.Write a PL/SQL Program to demonstrate procedure .

//accept A and B and display their sum and product.

Program:

CREATE OR REPLACE

PROCEDURE DISP_AB (A INT, B INT) IS

BEGIN

DBMS_OUTPUT.PUT_LINE(’A + B = ’ || (A + B));

DBMS_OUTPUT.PUT_LINE(’A * B = ’ || (A * B));

END;

OUTPUT:

SQL> CALL DISP_AB(17,23);

A + B = 40

A * B = 391

15. Write a PL/SQL Program to demonstrate function

Program:

// a function that computes the sum of two numbers

CREATE OR REPLACE

FUNCTION ADD_TWO (A INT,B INT) RETURN INT IS

BEGIN

RETURN (A + B);

END;

BEGIN

DBMS_OUTPUT.PUT_LINE(’RESULT IS: ’ || ADD_TWO(12,34));

END;

OUTPUT:

RESULT IS: 46

16. Write a PL/SQL Program to demonstrate package

Program:

CREATE OR REPLACE PACKAGE test AS -- package spec

TYPE list IS VARRAY(25) of NUMBER(3);

PROCEDURE hire_employee (emp_id INTEGER, name VARCHAR2);

PROCEDURE fire_employee (emp_id INTEGER);

PROCEDURE raise_salary (emp_id INTEGER, amount REAL);

END test;

//Then we created the package body.

CREATE OR REPLACE PACKAGE BODY test AS -- package body

    PROCEDURE hire_employee (emp_id INTEGER, name VARCHAR2) IS

    BEGIN

    INSERT INTO employee VALUES (emp_id, name, 1000);

    END hire_employee;

PROCEDURE fire_employee (emp_id INTEGER) IS

BEGIN

DELETE FROM employee WHERE empno = emp_id;

END fire_employee;

PROCEDURE raise_salary (emp_id INTEGER, amount REAL) IS

BEGIN

DBMS_OUTPUT.PUT_LINE('Increase Salary :' ||to_char(amount));

UPDATE employee SET sal = sal + amount WHERE empno = emp_id;

END raise_salary;

END test;

SQL> SET SERVEROUTPUT ON

SQL> VARIABLE num NUMBER

SQL> @spec

SQL> @body

SQL> exec test.raise_salary(1,1000);

17. Write a PL/SQL Program to demonstrate statement level trigger

Program:

//cann’t do insertion or deletion on emp table

Create or replace trigger t1 before insert or delete or update on emp

Begin

Dbms_output.put_line(‘statement level trigger fired’);

End;

/

OUTPUT:

18. Write a PL/SQL Program to demonstrate row level trigger

Program:

//On Sunday we cann’t do insertion or deletion on emp table .

Create or replace trigger t2 before insert or delete or update on emp for each row

Begin

If to_char(sysdate,’dy’)=‘sun’ then

Raise_application_error(-20004,’No transaction on Sunday’);

Endif

End;

/

OUTPUT:

CYCLE-1:

1->Create a student table that contains

sid,sname,branch,sem,dob,marks.

2->Insert rows into the table.Using various ways of

insertion(insert)command.

3->alter the student table to contain address of the student,and also modify the size of sname.

4->Display the content of the table.

5->delete some rows from student table based on conditions.

6->drop the student table.

CYCLE-2:

1->Create the payroll data base consider only two tables employee and department.

2->simple queries on the above database.

a)list all information of the above both tables.

b)list the empno,ename,jobtitle,and hiredate of employee from the employee table.

3->list the name,salary of the employees who are clerks.

4->list the name,job,salary of every employee joined on‘december 17,1980’,

5->list name and annual salary of all the employees.

6->list the department name &deptno for departments having

deptno.>=20

7->list the employees who have salary between 1000&2000.

8->list the name,salary,commision of the employees where

commision is greater than their salary.

9->list the name,monthly salary and daily salary and hourly

salary for employee;assume that there are 22 working days in a

month,8 working hours in a day.display the rows with coloumn

names as monthly,daily &hourly sal.

10->list the name and empno of managers who earn more than 20000.display the result in alphabetical order of the name.

11->display all employee names which have ‘TH’or ‘LL’ in them.

12->list the details of the employees in the depts of 10 &20 in the

order of empno.

13->display the information about the managers and clerks from the column ‘Job’ in the table emp,order the result by deptno.

14->list the empnames that don’t end with ‘s’.

15->list the emp names that begin with ‘c’

16->list the name,job&dept of each emp whose names falls in the

range c to p.

17->list the names of the employees strats with T/t anad ends with R/r.

18->list the name of employees who joined during 1983.

19->list the data as shown below”Smith has held the position of

clerk in dept 20 since 19 june,1983”

20->list all rows in employee table by converting commisison column to zero

CYCLE-3:

1->Write a query that accepts a job title and displays all the rows according to that title.

2->list the length of the names appearing on the emptable,eliminate duplicate values.

3->list the names and hire the date of those in the dept of 20.

display the hire date formatted as ‘13/oct/99’(ie. ‘dd/mm/yy’).

4->display the experience of the manager round to the nearest whole number of the months.

5->write a query to find the average sal per job in each departments.

6->display avg sal.minimum sal,max sal of emp having job clerk or manager.

7->list the emp who earned less than 30%of the managers salary.

8->display the details of the employee who joined last in each dept.

9->Write a query to display the no.of employees and their total sum of salaries grouped by their deptno.

10-> Write a que ry to list no.of employees working in “NEW YORK”.

11-> Write a query to list no.of employees along with a message depending on salary,if salary>1500 ,the message will be ‘above target’;salary<1500,the message will be ‘below

target’;salary=1500,then it is ‘not in target’.

12->display those jobs where the min sal is >=3000.

13->find all the depts which have more than 3 employees.

14-> Write a query to list the emp whose job is same as ‘Blake’

15-> Write a query to list no.of employees who earn more than the lowest salary in a department.

16->list the job with highest average salary.

17->display each empname with hire date and review

date.assume review date is one year after the hire date,order the output in the ascending order of the review date.

18->list the emp where salary is that of the “scott”or “ward”.

19-> list the emp whose salary is greater than any clerk.

20-> list the dept with average salary is higher than any other department.

21-> Write a query to list the emp who are earning more than “miller”.

22-> Write a query to list the depts which are having avg sal more than avg sal of all employees.

23->-> Write a query to list the dept with highest avg salary.

24-> Write a query to list the dept which are having the max sal>4000 and total no.of employees>6.

25-> Write a query to list the dept which are having the tot sal more than all clerks total salary but having max sal lesser than all managers.

26-> Write a query to list the emps who are earning more than managers.

27-> Write a query to list the emp with top 3 salaries.

28-> Write a query to list the ename,sal and their salary to avg.sal difference.

29-> Write a query to display the service left for an employee assuming that 58 years of the age limit for the service.

30->display all the employees who haven’t joined in the same year as that of those employees working in the dept with deptno..’20’.

CYCLE-4: (PL/SQL programs)

1->Write a program to accept two numbers and add them.

2-> Write a program to print below given no.

3-> Write a program wish the user depending on the time.

4-> Write a program to check whethere a given number is odd or even.

5-> Write a program to find sum of n numders.

6-> Write a program to reverse a given number.

7-> Write a program ot find the no of vowels in a given string.

8-> Write a program that checks the validity of a givem empno.handling with exception.

9-> Write a program to accept empno and print the details as follows:

Emp name/designation/annual income/grade.

10-> Write a program which accepts an empno and increases his

salary by 1000/-.

CYCLE-5:

1-> Write a program to list all the employee details

2-> Write a program to check for the existance of a given employees in emp table using the cursors.

3-> Write a program to list the details of dept entered if the dept is having employees

CYCLE-6: (PROCEDURES.FUNCTIONS AND TRIGGERS. )

1-> Write a procedure that takes emp no and amount as arguments and update the salary of an employee by the given amount.

2-> Write a procedure that delete student details by accepting studentno.

3-> Write a procedure that accepts rhe empno and return his total

earning per year to the calling environment.

4-> Write a procedure to print the details of emp who is earning maximum salary.

5-> write a function to check for the existance of an employee.

6-> write a function which accepts empno as parameters and return his frade.

7->Write a trigger on employee table to fire after insert or update or delete

8-> Write a trigger on employee table to fire before insert or update or delete.

9-> Write a trigger on employee table to fire before insert for each row.

10-> Write a trigger on employee table to fire before delete for each row

WHERE JOINS GROUPS SUBQ

1. Display the details of those who do not have any person working under them?

2. Display the details of those employees who are in sales department and grade is 3?

3. Display those who are not managers and who are manager any one?

4. Display those employees whose name contains not less than 4 characters?

5. Display those department whose name starts with “S” while the location name ends with

“O”?

6. Display those employees whose manager name is JONES?

7. Display those employees whose salary is more than 3000 after giving 20% increment?

8. Display all employees with their dept names?

9. Display ename who are working in sales dept?

10. Display employee name, dept name, salary and comm. For those sal in between 2000 to

5000 while location is Chicago?

11. Display those employees whose salary is greater than his manager salary?

12. Display those employees who are working in the same dept where his manager is

working?

13. Display those employees who are not working under any manager?

14. Display grade and employees name for the dept no 10 or 30 but grade is not 4 while

joined the company before 31-dec-82?

15. Update the salary of each employee by 10% increment who are not eligible for

commission?

16. Delete those employee who joined the company before 31-dec-82 while their dept

location is New york or Chicago?

17. Display employee name, job, deptname, location for all who are working as manager?

18. Display those employees whose manager name is jones, and also display their manager

name?

19. Display name and salary of ford if his salary is equal to hisal of his grade?

20. Display employee name, his job, his department name, his manager name, his sal, his

grade and make out an under department wise?

21. List out all the employees name, job, salary, grade and department name for every one in

the company except “CLERK”. Sort on salary, display the highest salary?

22. Display employee name, his job and his manager. Display also employees who are

without manager?

23.Find out the top 5 earners of the company?

23. Display name of those employees who are getting the highest salary.

24. Display those employees whose salary is equal to average of maximum and minimum

plus 75 ? (to highlight atleast one row)

25. Select count of employees in each department where count greater than 3?

26. Display dname where atleast 3 are working and display only department name?

27. Display name of those managers salary is more than average salary of employees?

28. Display those managers name whose salary is more than average salary of his employees?

29. Display employee name, sal, comm. And net pay for those employees whose net pay is

greater than Or equal to any other employee salary of the company?

30. Display those employees whose salary is less than his manager but more than salary of any other managers?

31. Display all the employees names with total sal of company with each employee name?

32. Find out the last 5(least) earners of the company?

33. Find out the number of employees whose salary is greater than their manager salary?

34. Display those manager who are not working under president but they are working under any other manager?

35. Delete those department where no employee working?

36. Delete those records from emp table whose deptno not available in dept table.

37. Display those enames whose salary is out of the grade available in salgrade table?

38. Display emplyee name, sal, comm. And net pay whose net pay is greater than any other in the company?

39. Display name of those employees who are going to retire 31-dec-99. If the maximum job period is 30 years?

40. Display those employees whose salary is ODD value?

41. Display those employees whose salary contains atleast 3 digits?

42. Display those employees who joined in the company in the month of Dec?

43. Display those employees whose name contains “A”?

44. Display those employees whose deptno is available in salary?

45. Display those employees whose first 2 characters from hiredate = last 2 characters of salary?

46. Display those employees whose 10% of salary of equal to the year of joining

47. Display those employee who are working in sales or research?

48. Display the grade of Jones?

49. Display those employees who joined the company before 15th of the month?

50. Delete those records where no .of employees in a particular department is less than 3?

51. Delete those employees who joined the company 10 years back from today?

52. Display the department name the no of characters of which is equal to no of employees in any other department?

53. Display the name of the department where no employee working?

54. Display those employees who are working as manager?

55. Count the no of employees who are working as manager(Using set operation)?

56. Display the ename of the employees who joined the company on the same date?

57. Display those employees whose grade is equal to any number of sal but not equal to first

number of sal?

59.Display the name of employees who joined on the same date?

60.Display the manager who is having maximum number of employees working under him?

61.List out the employees name and salary increased by 15% and expressed as whole number of dollars?

62.Produce the output of emp table “EMPLOYEE_AND_JOB” for ename and job?

63.List all employees with hiredate in the format “june 4, 1988”

64.Print a list of employees displaying “Just Salary” if more than 1500 if exactly 1500

display “On target” if less ‘Belowtarget’?

65.Which query to calculate the length of line any employee has been with the company (User define fo avoid repetitive typing of functions)?

66.Given a string of the format ‘nn/mm’. Verify that the first and last 2 characters are numbers. And that the middle character is ‘/’. Print the expressions ‘Yes’ if valid ‘No’ if not valid. Use the following values to test your solution ‘112/54’,01/1a,’99/88’?

67.Employees hire on 15th of any month are paid on the last Friday of that month. Those

hired after 15th are paid the last Friday of the following month. Print a list of employees,

their hire date and first pay date. Sort those whose salary contains first digits of their deptno.?

68.Display those managers who are getting less than his employees salary?

69.Print the details of all the employees who are Sub-Ordinate to BLAKE?

70.Display those who are working as manager using Co-related sub query/

71.Display those employees whose manager name is jones and also with his manager name?

72.Define a variable representing the expression used to calculate on employees total Annual Remuneration.

73.Use the variable in a statement which finds all employees who can earn $30,000a year or more?

74.Find out how many managers are there without listing them?

75.Find out the average salary and average total remuneration for each job type remember salesman earn commission?

76.Check whether all employees number are indeed unique?

77.List out the lowest paid employees working for each manager, exclude any groups where minimum salary is less than Rs.1000. Sort the output by salary?

78.List ename, job, annual sal, deptno, dname and grade who earn $36,000 a year or who are not Clerks?

79.Find out the job that was filled in the first half of 1983 and the same job that was filled during the same period on 1984?

80.Find out the all employees who joined the company before their managers?

81.List out the all employees by name and number along with their manager’s name and

number, also display KING who has no manager?

82.Find out the employees who earn the highest salary in each job type. Sort in descending salary order?

83.Find out the employees who earn the minimum salary for their job in Ascending order?

84.Find out the most recently hired employees in each department. Order by hiredate?

85.Display ename, salary and deptno for each employee who earn a salary greater than the average for their department Order by deptno?

86.Display the department where there are no employees?

87.Display the department no with highest annual remuneration bill as compensation?

88.In which year did most people join the company. Display the year and number of

employees?

89.Display average salary figure for the department?

90.Write a query of display against the row of the most recently hired employees. Display ename, hiredate and column max date showing?

91.Display employees who can earn more than lowest salary in department no 30?

92.Find employees who can earn more than every employee in deptno 30?

93.Select dept name, deptno and sum of salary?

94.Find out average salary and average total remunerationr for each job type?

95.Find all departments which have more than 3 employees?

96.Check whether employees number are indeed unique?

97.List lowest paid employees working for each manager. Exclude any groups where the minimum salary is less than 1000. Sort the output by salary?

98.If the pay day is next Friday after 15th and 30th of every month. What is the next pay day from their hiredate for employee in emp table?

99.If an employe is taken by you today in your organization. And it is a policy in your company to have a review after 9 months after the joined date (and of 1st of next month after

9 months) how many days from today your employee has to wait for a review?

100.Display employee name and his salary whose salary is greater than highest average of

department number?

101.Display the 10th record of emp table? (with out using rowid)

102.Display the half of the ename’s in upper case & remaining lower case?

103.Display the 10th record of emp table without using group by & rowid?

104.Delete the 10th record of emp table?

105.Create a copy of emp table without any data(records)?

106.Select ename if ename exists more than once?

107.Display all enames in reverse order? (Ex:HTIMS)

108.Display those employees whose joining of month and grade is equal?

109.Display those employees whose date of joining is available in deptno?

110.Display those employees name as follows

i. A ALLEN

ii. B BLAKE

111.List out the employees ename, sal, PF from emp?

112.Display RSPS from emp without using updating, inserting

DBMS Lab Manual

Exceptions

When a program is executed certain errors are automatically recognized by oracle and certain error situations must recognized by the program itself.

Errors in general are referred to as Exceptions.

Exception : Exceptions are error situations. Error situations can be user-defined or system exceptions which are automatically raised by pl/sql. Certain system exceptions raise the following flags,

CURSOR_ALREADY_OPEN - Displayed when the user tries to open a cursor that is already open

DUP_VAL_ON_INDEX - When user tries to insert a duplicate value into a unique column.

INVALID_CURSOR - When user references an invalid cursor or attempts an illegal cursor operation.

INVALID_NUMBER - When user tries to use something other than a number where one is called for.

LOGIN_DENIED - when connect request for user has been denied.

NO_DATA_FOUND - This flag becomes true when sql select statement failed to retrieve any rows.

NOT_LOGGED_ON - User is not connected to ORACLE.

PROGRAM_ERROR - The user hits an PL/SQL internal error.

STORAGE_ERROR - The user hits a PL/SQL memory error.

TIMEOUT_ON_RESOURCE - When user has reached timeout while waiting for an oracle resource.

TRANSACTION_BACKED_OUT - A remote serer has rolled back your transaction.

TOO_MANY_ROWS - This flag becomes true when sql select statement retrieve more than one row and it was supposed to retrieve only one row.

VALUE_ERROR - User encounters an arithemetic, conversion,Truncation or constraint error.

n ZERO_DIVIDE - This flag becomes true when sql select statement tries to divide a number by zero.

n Others - This flag is used to catch any error situations not coded by the programmer in the exception section. Therefore, it must appear last in the Exception section.

Cursor

n A cursor is a private sql work area also called as System Global area

- static Cursors

- Explicit cursor

- Implicit cursor

- Dynamic Cursor

- Ref Cursor.

Cursor Attributes

- % found

- % notfound

- % rowcount

- % isopen

To manipulate a cursor we need cursor attributes.

%found - it returns true if there are any records in the cursor.

%notfound - It returns true if there are no records in the cursor.

%rowcount - It returns the number of records in the cursor.

%isopen - It return true if the cursor is opened for manipulation.

Sub Program

n A program inside a program is called subprogram

- procedures (They do some work)

- functions (Computes a value)

- pakages (It is a combination of procedure and function).

To pass some value or to get some value we require parameters.

- in

- out // parameter types or parameter modes

- in out

Data Base Triggers

it is a condition which is going to fire during the time of insert or update or delete.

implementation of triggers on two levels

- statement level

- row level.

Statement level trigger fire once independent of no of records effected.

Declare

A number(14,2);

Begin

Select sal into a from emp where empno=&empno;

Dbms_out.put_line(‘your sal is ‘ ||A);

Exception

When no_data_found then

Dbms_output.put_line(‘NO RECORDS FOUND’);

When too_many_rows then

Dbms_output.put_line(‘duplicate employee found’);

End;

/

Declare

A number(4):= &no1

B number(4):=&no2

Exp1 exception;

Begin

If b=0 then

Raise exp1

Else

Dbms_output.put_line(a/b);

End if;

Exception

When exp1 then

Dbms_output.put_line(‘Second number must not be zero’);

End;

/

Declare