DBMS Lab Manual

Company database Along with queries
bank data base Along with queries
sailors_reserves_boats database Along with queries

pl/sql programs

1 write a pl/sql program to display the numbers from 30 to 40.
2 write a pl/sql program to find the area of circle
3 Fibonacci series
4 Factorial:
5 odd numbers
6 prime number

Function:

Procedure:

FUNCTION USING CASE STATEMENTS:

EXCEPTION HANDLING:

TRIGGERS

Download:

http://www.ziddu.com/download/8005381/DBMSPrograms.doc.html

DML/DDL COMMANDS & More (DBMS)

DML/DDL COMMANDS

Done in SQL


CREATION OF TABLES:

INSERTION OF VALUES INTO TABLES:

SELECT COMMAND:

AGGREGATES

NUMERIC FUNCTIONS

CHARACTER FUNCTIONS:

DATE FUNCTIONS:

VIEWS

JOIN COMMAND

QUERIES

1.FIND THE NAMES OF THE SAILORS WHO HAVE RESERVED BOAT 103?
2.FIND THE NAMES OF THE SAILORS WHO HAVE RESERVED THE GREEN BOAT?
3.FIND THE SIDs OF SAILORS WHO HAVE RESERVED A RED BOAT?
4.FIND THE COLOURS OF BOATS RESERVED BY LUBBER?
5.FIND THE NAMES OF THE SAILORS WHO HAVE RESERVED ON MONDAY?
6.FIND THE NAMES OF THE SAILORS WHOSE AGE IS GREATER THAN 16?
7.FIND THE COLOUR OF THE BOATS RESERVED BY THE SAILORS WHOSE AGE IS GREATER THAN 25?

NESTED QUERIES

1.FIND THE NAMES OF THE SAILORS WHO HAVE RESERVED BOAT 103?
2.FIND THE SAILORS WHO HAVE NOT RESERVED BOAT 103?
3.FIND THE NAMES OF THE SAILORS WHO HAVE RESERVED A RED BOAT?

GROUP BY CLAUSE

1.FIND THE AGE OF THE YOUNGEST SAILOR FOR EACH RATING LEVEL?

2.FIND THE AVERAGE AGE OF SAILORS FOR EACH RATING LEVEL THAT HAS ATLEAST TWO SAILORS?

CORELATED NESTED QUERIES

1.FIND THE NAMES OF THE SAILORS WHO HAVE RESERVED BOAT 103?
2.FIND THE NAMES OF THE SAILOR WITH BID=103?
3.FIND THE NAMES OF SAILORS WHO HAVE NOT ACQUIRED BID =103

PL/SQL PROGRAMS

1.FIND THE SUM OF TWO INTERGERS?
2.FIND THE AVERAGE OF 3 INTEGERS?
3.FIND THE RESULT OF THE STUDENT?
4.FIND WHETHER THE GIVEN NUMBER IS EVEN OR ODD?
5.PRINTIN THE FIRST 20 INTEGERS?
6.PRINTING SUM OF GIVEN NUMBERS?
7.FINDING FACTORIAL OF GIVEN NUMBER?

EXCEPTIONS

ZERO_DIVIDE Exception
Catch 'divide by zero' exception
OTHERS Exception

CASES

OTHERS

TRIGGERS

What is a Trigger?
Syntax of Triggers
1) Create the 'product' table and 'product_price_history' table
2) Create the price_history_trigger and execute it.
3) Lets update the price of a product.
4)If you ROLLBACK the transaction before committing to the database, the data inserted to the table is also rolled back.

Download Following Link For Above Programs:

http://www.ziddu.com/download/8088346/SQL.doc.html

DBMS Programs

Data Base Management Programs (DBMS)

Done in oracle 10G

1) To find sum of 2 given numbers
2) To find average of 3 given numbers
3) To check whether a student is pass or fail for a given mark
4) To find whether a given number is odd or even
5) To find factorial of a given number
6) To calculate sum of numbers from m to n
7) To check whether given number is prime or not
8) To display numbers from m to n
9) Exception zero_divide1
10) Exception zero_divide2
11) Exception zero_divide3
12) Invalid number exception
13) Others exception
14) Others exception should be at the end
15) Creation of table
16) Insertion of values
17) Selection of a table
18) Views
19) Aggregate functions
20) Joins
21) Conversion functions
22) Number functions
23) Case function
24) Date functions
25) Calculating area of circle for given radius

For Above Programs Download Following Link:

http://www.ziddu.com/download/8088156/DBMS.doc.html

More Examples:

Bank Database Schema:

http://www.ziddu.com/download/8088154/bankdatabase.doc.html

Sailor Database Schema:

http://www.ziddu.com/download/8088157/salorsdatabase.doc.html

Company Database Schema:

http://www.ziddu.com/download/8088155/companydatabase.doc.html