DBMS LAB MANUAL FOR IV SEM B



CODE: MIT/CSE/LM/05

CSE-212 RDBMS LAB MANUAL

IV Sem, BE ( CS&E)

( 2009 scheme)

Approved By:

HOD

Dept. of CS&E

DEPT OF COMPUTER SCIENCE & ENGG.

M. I. T., MANIPAL

GENERAL INSTRUCTIONS TO STUDENTS

1. Students should be regular and come prepared for the lab practice.

2. In case a student misses a class, it is his/her responsibility to complete that missed experiment(s).

3. Students should bring the observation book, lab journal and lab manual.

Prescribed textbook and class notes can be kept ready for reference if required.

4. They should implement the given experiment individually.

5. While conducting the experiments students should see that their programs would meet the following criteria:

• Programs should be interactive with appropriate prompt messages, error messages if any, and descriptive messages for outputs.

• Programs should perform input validation (Data type, range error, etc.) and give appropriate error messages and suggest corrective actions.

• Comments should be used to give the statement of the problem and every function should indicate the purpose of the function, inputs and outputs

• Statements within the program should be properly indented

• Use meaningful names for variables and functions.

• Make use of Constants and type definitions wherever needed.

6. Once the experiment(s) get executed, they should show the program and results to the instructors and copy the same in their observation book.

7. Questions for lab tests and exam need not necessarily be limited to the questions in the manual, but could involve some variations and / or combinations of the questions.

Note: Above mentioned instructions can be modified based on the context of the lab.

CONTENTS

SL NO. TITLE OF EXPERIMENT WEEKS

1. MSACCESS 1 week

2. SQL 4 weeks

3. PL/SQL BASICS 1 week

4. Cursors & exception handling 2 weeks

5. Triggers, Procedures, Functions, Packages 2 weeks

6. Interfacing DB with VB 2 weeks

12 weeks

PROCEDURE OF EVALUATION

Student will be evaluated based on the following criteria:

Implementation of experiments,

Observation and /or 60%(60 Marks)

JournalAndViva

Test 40% (40 Marks)

1. MSACCESS

1. Creating Tables

2. Forms

3. Relationships

4. Filters

5. Queries

6. Reports

7. SQL

Exercises:

1. Create four tables for the VideoParlour database using Design view. The tables are Member to hold members details, Video to hold details of videos, VideoForRent to hold the details of copies of videos for rent, and RentalAgreement to hold the details of video rentals by members.

The Member table has the following fields (with the data type of each in brackets):

memberNo (AutoNumber), fName (Text), lName (Text), gender (Text), DOB (Date/Time), address (Text), dateJoined (Date/Time), comments (Memo)

The primary key is memberNo.

(Also for this table, set the format property of the gender field to a field size of 1 with an Input Mask >L. Also, set this field with a Validation Rule =”M” or “F” and Validation Text Please enter M or F. If you do not understand the purpose of the properties associated with each field, Use the help facility using the F1 key).

The Video table has the following fields (with the data type of each in brackets):

catalogNo (Text), title (Text), category (Text), dailyRental (Currency), price (Currency), directorNo (Text).

The primary key is catalogNo

The VideoForRent table has the following fields (with the data type of each in brackets): videoNo (Text), available (Yes/No), catalogNo (Text)

The primary key is videoNo

The RentalAgreement table has the following fields (with the data type of each in brackets): rentalNo (AutoNumber), dateOut (Date/Time), dateReturn (Date/Time), memberNo (Number), videoNo(Text)

The primary key is rentalNo

(Also for this table, set the format property for the dateOut and dateReturn fields to Medium Date format e.g. 10-Oct-00.)

2. Open your VideoParlour database. Create a form for your Video table using the Form Wizard facility and name this form VideoForm1. Use the form to view records in your Video table. Practise, changing between viewing your Video table using Form view and Datasheet view.

3. Create relationships between your Member, RentalAgreement, VideoForRent, Video tables using the Relationship window.

4. Apply filters to the members and video records. For example, create the following filters to view:

• Only male members of the video shop.

• Only male members of the shop who joined the shop this year in order of last name and then first name.

• All members born in the 1960s.

• Only videos in the Children category with a daily rental rate of less than £4.00 and sorted according to video title.

• Only videos currently available for rent with a certification of “PG” or “U”.

• Only videos by a certain director.

5. Using the Select Query window, select your Member, RentalAgreement, VideoForRent, Video tables. Practise, joining and deleting the join lines between your tables. Examine the join properties of the join lines relating your tables.

6. Create a report for your Video table containing the catalogNo, title, category and certificate fields. Group your records according to the values in the category field and then sort on the values in the title field.

Create a report for your Video table containing the category, dailyRental and price fields. Group your records according to the values in the category field and then sum the values in the dailyRental and price fields.

Create a report based on a query that contains the following fields

memberNo, fName, lName, videoNo, title, dateOut and dateReturn. Group your records according to memberNo and then order by videoNo.

7. Using Access SQL, create simple select queries on the tables of your StayHome database. For example, create and save the following queries on the Video table.

• List the catalogNo, title and category of the Video table, ordered by video title

• List title, certificate, category and dailyRental of the Video table for videos in the “Childrens” category with a rental rate less than £4.00.

• List all videos with a certification of “PG” or “18”in the Video table.

2. SQL

2.1 Try the following SQL plus commands:

Creating a Table

CREATE TABLE ();

Creating a Table with a Primary Key

CREATE TABLE ( PRIMARY KEY,);

CREATE TABLE (, PRIMARY KEY (, ));

Inserting Tuples

INSERT INTO

VALUES ();

Deleting Tuples

DELETE FROM [where ];

Updating Column values

UPDATE SET ;

Getting the Value of a Relation

SELECT * FROM ;

SELECT Column1, column2, … from ;

Modifying the structure of Tables

Adding New Columns:

ALTER TABLE ADD( , …);

Modifying Existing Columns:

ALTER TABLE MODIFY( , …);

Restrictions:

• Changing the name of table

• Changing the name of the column

• Decreasing the size of a column

Getting Rid of Your Tables and attributes

DROP TABLE ;

ALTER TABLE DROP ()

Getting Information about Your Database

SELECT TABLE_NAME FROM USER_TABLES;

To recall the attributes of a table

DESCRIBE ;

Quitting sqlplus

quit;

Executing SQL from a File

Instead of executing SQL commands typed at a terminal, it is often more convenient to type the SQL command(s) into a file and cause the file to be executed.

To run the file foo.sql, type:

@foo or start foo or run foo

You can also run a file at connection by using a special form on the command line. The form of the command is:

sqlplus / @

Editing Commands in the Buffer

If you end a command without a semicolon, but with an empty new line, the command goes into a buffer. You may execute the command in the buffer by either the command RUN or a single slash (/).

You may also edit the command in the buffer before you execute it. Here are some useful editing commands. They are shown in upper case but may be either upper or lower.

|LIST |lists the command buffer, and makes the last line in the buffer the "current" line |

|LIST n |prints line n of the command buffer, and makes line n the current line |

|LIST m n |prints lines m through n, and makes line n the current line |

|INPUT |enters a mode that allows you to input text following the current line; you must terminate the sequence of|

| |new lines with a pair of "returns" |

|CHANGE /old/new |replaces the text "old" by "new" in the current line |

|APPEND text |appends "text" to the end of the current line |

|DEL |deletes the current line |

All these commands may be executed by entering the first letter or any other prefix of the command except for the DEL command.

Working with an ASCII Editor

An alternative is to edit the file where your SQL is kept directly from sqlplus. If you say

edit foo.sql

the file foo.sql will be passed to an editor of your choice. The default is Noted Pad.

Recording Your Session

There are several methods for creating a typescript to turn in for your programming assignments. The most primitive way is to cut and paste your terminal output and save it in a file (if you have windowing capabilities). sqlplus provides the command spool to save query results to a file. At the SQL> prompt, you say:

spool foo;

and a file called foo.lst will appear in your current directory and will record all user input and system output, until you exit sqlplus or type:

spool off;

Oracle Data Types

:

|KEY DATA TYPES | |

|CHAR(size) |Fixed-length character data, size characters long. Maximum size=255; |

| |default=1 byte. Padded on right with blanks to full length of size. |

|DATE |Valid dates range from Jan 1, 4712 B.C. to Dec 31, 4712 A.D. |

|NUMBER |For NUMBER column with space for 40 digits, plus space for a decimal point |

| |and sign. Numbers may be expressed in two ways: first, with numbers 0 to 9,|

| |the signs + and -, and a decimal point(.); second, in scientific notation, |

| |e.g. 1.85E3 for 1850. Valid values are 0 and positive and negative numbers |

| |from 1.0E-130 to 9.99…E125. |

|VARCHAR2(size) |Variable length character string, maximum size up to 2000 bytes. |

|MISCELLANEOUS DATA TYPES AND VARIATIONS | |

|DECIMAL |Same as NUMBER.  |

|FLOAT |Same as NUMBER. |

|INTEGER |Same as NUMBER. |

|INTEGER(size) |Integer of specified size digits wide; same as NUMBER(size) of specific |

| |size digits wide. |

|LONG |Character data of variable size up to 2Gb in length. Only one LONG column |

| |may be defined per table. LONG columns may not be used in subqueries, |

| |functions, expressions, where clauses, or indexes. A table containing LONG |

| |data may not be clustered. |

|LONG RAW |Raw binary data; otherwise the same as LONG (used for images). |

|LONG VARCHAR |Same as LONG |

|NUMBER(size) |For NUMBER column of specified size in digits. |

|NUMBER(size,d) |For NUMBER column of specified size with d digits after the decimal point, |

| |e.g. NUMBER(5,2) could contain nothing larger than 999.99 without an error |

| |being generated. |

|NUMBER(*) |Same as NUMBER. |

|SMALLINT |Same as NUMBER. |

|RAW(size) |Raw binary data, size bytes long, maximum size=255 bytes. |

|ROWID |A value that uniquely identifies a row in an Oracle database - it is |

| |returned by the pseudo-column ROWID. Table columns may not be assigned this|

| |type. |

|VARCHAR(size) |Same as VARCHAR2.  Always use VARCHAR2. |

2.2 Creating, Updating and Querying the Tables.

Create the students table:

students

|RollNo |Name |HostelNoSize |cgpa |

|‘99305017’ |‘Sai Sundar’ |11 |7.23 |

|‘99305018 |‘Shyam Sundar’ |11 |9.23 |

|‘99305019’ |‘Ram Sundar’ |12 |8.32 |

hostel INTEGER, cpi NUMERIC(3,2) );

Run the following queries :

SQL> SELECT * FROM students;

SQL> SELECT rollno FROM students;

SQL> SELECT rollno, name FROM students WHERE cgpa > 9.00;

Updates/deletes:

SQL> UPDATE students SET cgpa = 9.46 WHERE rollno = '99305018';

SQL> DELETE students WHERE rollno = '99305018';

Runnings batch of queries :

- Use Note Pad to create a file having extension as .sql.

- Write queries in the file. Queries should end with a semicolon

- Execute

SQL> start

Multitable queries:

CREATE TABLE course ( courseno CHARACTER(8), coursename VARCHAR(30));

CREATE TABLE registered ( rollno CHARACTER(8), courseno CHARACTER(8));

To add fields interactively

... insert appropriate data into each table

[Use: insert into course values (‘&courseno’, ‘&coursename’);]

Query to find all course numbers with students from Hostel 11

SELECT courseno FROM students, registered

WHERE students.rollno = registered.rollno AND

students.hostel = 11

(Course numbers may appear multiple times: use SELECT DISTINCT instead of SELECT to get them only once.)

Try some more queries:

-- course numbers with some students with cgpa < 6

-- rollnumbers of all students registered for course CS317

2.3 Implement the Bank Database and execute the given queries/updates

Bank Database Schema:

account(account_number, branch_name, balance)

branch (branch_name, branch_city, assets)

customer (customer_name customer_street, customer_city)

loan (loan_number, branch_name, amount)

depositor((customer_name, account_number)

borrower(customer_name, loan_number)

Note: Use BankDB.sql to create and populate the Database

Queries/Updations on Bank Database

Retrieving records from a table:

1. List all branch names and their assests

2. List all accounts of Brooklyn branch

3. List all loans with amount > 1000.

4. List all accounts of Perryridge branch with balance < 1000.

5. List Numbers of accounts with balances between 700 and 900

Updating records from a table:

6. Change the assests of Perryridge branch to 340000000.

7. Transfer the accounts and loans of Perryridge branch to Downtown branch.

8. Transfer Rs. 100 from account A-101 to A-215.

Deleting records from a table:

9. Delete the branch Perryridge.

10. Waive off all the loans with amount < 1000.

11. Delete the accounts and loans of Downtown branch.

Modifying the structure of tables:

12. Add a column phoneNo to customer table.

13. Change the size of the branch_city to varchar(20).

14. Drop the column phoneNo from customer table.

Retrieving records from multiple tables

15. For all customers who have a loan from the bank, find their names, loan numbers, and loan amount.

16. Find the customer names, loan numbers, and loan amounts, for all loans at the Perryridge branch.

Rename and Tuple Variables(Use as in select and from)

17. For all customers who have a loan from the bank, find their names and loan numbers with the attribute loan_number replaced by loan_id.

18. Find the names of all branches that have assets greater than atleast one branch located in Brooklyn.

String Operations (Use %, _, LIKE)

19. Find the names of all customers whose street address includes the substring ‘Main’.

Ordering the display of Tuples(Use ORDER BY ASC DESC)

20. List loan data, ordered by decreasing amounts, then increasing loan numbers.

3. SQL (Continued…)

Implement the following Queries on Bank Database:

3.1 Set Operations

UNION (Use union all to retain duplicates):

21. Find all the bank customers having a loan, an account, or both at the bank.

INTERSECT (Use intersect all to retain duplicates):

22. Find all the bank customers having both a loan and an account at the bank

EXCEPT(Minus):

23. Find all customers who have an account but no loan at the bank.

3.2 Aggregate Functions (avg,min,max,sum,count) / Group By

24. Find the average account balance at the Perryridge branch.

25. Find the average account balance at each branch.

26. Find the number of depositors for each branch (Use distinct).

27. Find those branches where the average accounts balance is more than Rs. 1200.

28. Find the number of branches of the bank.

29. Find the average balance for each customer who lives in Harrison and has at least three accounts.

3.3 Null values

30. Find all loan numbers that appear in the loan relation with null values for amount.

3.4 Nested Subqueries

Set Membership (in / not in):

31. Find all the customers who have both a loan and an account at the Perryridge branch

32. Find all customers who do have a loan at the bank, but do not have an account at the bank.

33. Select the names of customers who have a loan at the bank, and whose names are neither Smith nor Jones

Set Comparison (>some/all):

34. Find the names of all branches that have assets greater than those of at least one branch located in Brooklyn.

35. Find the names of all branches that have an asset value greater than that of each branch in Brooklyn.

Test for Empty Relations (exists/ not exists):

36. Find all customers who have both an account and a loan at the bank

37. Find all customers who have an account at all the branches located in Brooklyn.

Test for Absence of Duplicate Tuples

38. Find all customers who have at most one account at the Perryridge branch

39 Find all customers who have at least two accounts at the perryridge branch

3.5 Complex queries

Derived Relations

40. Find the average account balance of those branches where the account balance is greater than Rs. 1200.

41. Find the maximum across all branches of the total balance at each branch

With Clause

42. Select the accounts with maximum balance.

43. Find all branches where the total account deposit is greater than the average of the total account deposits at all branches.

3.6 Views

44. Create a view all_customers consisting branches and their customers.

45. Select all the customers from all_customers view.

46. Create a view Perryridge_customers consisting customers of Perryridge branch using all_customers view.

3.7 Modification of the Database

(Use ROLLBACK (and SAVEPOINT) to undo the effect of any modification on database before COMMIT)

47. Delete all loans with loan amounts between Rs. 1300 and Rs, 1500.

48. Delete all account tuples at every branch located in Brooklyn.

49. Present a new Rs. 200 savings account as a gift to all loan customers of Perryridge branch(Note: It requires insert into account and depositor).

50. Pay 5% interest to all accounts with a balance of Rs. 1000 or more.

51. Pay 5% interest on accounts whose balance is greater than average

52. Pay 6% interest on accounts with balances over Rs. 10000 whereas pay 5% interest on all other accounts (use case).

4. SQL (Continued…)

4.1 Integrity Constraints

• Ensure the uniqueness of the primary key(PRIMARY KEY)

( column_name data_type primary key

( Primary key(column_name(s))

• Ensure the uniqueness of the candidate key which is not the primary key

( column_name data_type unique

• Ensure that child records in related tables have a parent record.

( foreign key(column_name) references table_name(column_name)

• Delete child records when the parent record is deleted.

( foreign key(column_name) references table_name(column_name) on delete cascade

• Ensure that columns always contain a value.

( column_name data_type not null

• Ensure that a column contains a value within a set/specific range.

( check (column_name in (value1, value2,..))

( check (predicate)

• Ensure that a default value is placed in a column.

( column_name data_type default (value)

Consider the following schema:

Employee (EmpNo, EmpName, Sex, Salary, Address, DNo)

Department (DeptNo, DeptName, Location)

1. Create Employee table with following constraints:

• Make EmpNo as Primary key

• Do not allow EmpName, Sex, Salary and Address to have null values

• Allow Sex to have one of the two values: ‘M’, ‘F’

• Set the default salary value to Rs. 1000.

2. Create Department table with following:

• Make DeptNo as Primary key

• Make DeptName as candidate key

3. Make DNo of Employee as foreign key which refers to DeptNo of Department

[ Note: To view the name of integrity constraints

SELECT

CONSTRAINT_NAME, CONSTARINT_TYPE,

FROM USER_CONSTRAINTS

WHERE TABLE_NAME=’Employee’; ]

4. Insert few tuples into Employee and Department which satisfies the above constraints

5. Insert few tuples into Employee and Department which violates some of the above constraints

6. Modify / Delete a tuple which violates a constraint

(e.g. drop a department tuple which has one or more employees)

7. Modify the foreign key constraint of Employee table such that whenever a department tuple is deleted, the employees belonging to that department will also be deleted

4.2 Naming Constraints

• Oracle constraint name is SYS_Cn, where n is a numeric value

• Constraints can have unique user defined name as given below:

CONSTRAINT

e.g. constraint account_pk primary key(account_number)

• Modifying Constraints:

ALTER TABLE

ADD / MODIFY/DROP/DISABLE/ENABLE/VALIDATE/NOVALIDATE

CONSTRAINT

8. Repeat some of the examples (1 to 7) with named constraints.

9. Try some more examples with ADD, DROP, DISABLE, ENABLE, VALIDATE

INVALIDATE and NOVALIDATE

4.3 Built-in Functions

(Use Bank Database for the exercise problems given below)

• LENGTH(string)

10. List the customer names along with the length of the customer names

• LOWER(string)

11. List the customer names in lower case

• SUBSTR(string, start, count)

12. List the customer names and 3 characters from 3rd position of each customer name

• UPPER(string)

12. List the customer names in upper case

• NVL(column_name, substitute value)

13. Replace NULL with value1(say 0) for a column in any of the table

• ROUND(value, precision)

15. List the balance and balance/3 rounded to nearest hundred from account

(Add data of birth column DOB to Employee Table. Insert appropriate DOB values for different employees and try the exercise problems given below)

• TO_CHAR(date1, format)

16. Display the birth date of all the employees in the following format:

• ‘DD-MON-YYYY’

• ‘DD-MON-YY’

• ‘DD-MM-YY’

17. List the employee names and the year(fully spelled out) in which they born

• ‘YEAR’

• ‘Year’

• ‘year’

18. List the employee names and the day(of the week fully spelled out) in which they born

• ‘DAY’

• ‘Day’

19. List the employee names and the month(fully spelled out) in which they born

• ‘MONTH’

• ‘Month’

• LAST_DAY(date1)

20. Find the last day of the month(and its day of the week) in which employee Mr. X is born

• MONTHS_BETWEEN(date1, date2)

21. Find the age of all the employees

[Hint: Use SYSDATE]

• NEXT_DAY(date1, ‘day’)

• ADD_MONTHS(date1, number of months)

22. Find the Saturday following the Employee’s 60th birthday

• TO_DATE(string, ‘format’)

e.g. to_date(‘12021998’, ‘DDMMYYYY’)

23. List the employees whose birth day falls in the given year X

24. List the employees whose birth day fall between the given years X and Y

25. List the employees who will retire on the given year X.

[Hint: use & with the variable name (e.g. &X) in the SQL query to read the value from the user]

5. SQL(Continued…)

Implement the Hospital Database and execute the given queries:

[pic]

Relations (Include all the necessary integrity constraints):

|BILLED | |

|BILL NO |NUMBER(5) - PRI KEY |

|PATIENT_NO |NUMBER(9)  |

|ITEM_CODE  |NUMBER(5) |

|CHARGE |NUMBER(7,2) |

|TREATS | |

|PHY_ID |NUMBER(4) - PRI KEY |

|PATIENT_NO |NUMBER(4) - PRI KEY |

|PROCEDURE_NO |NUMBER(4) - PRI KEY |

|DATE_TREATED |DATE - PRI KEY |

|TREAT_RESULT |VARCHAR2(50) |

|ITEM | |

|ITEM_CODE |NUMBER(4) - PRI KEY |

|DESCRIPTION |VARCHAR2(50) |

|NORMAL_CHARGE |NUMBER(7,2) |

|PHYSICIANS | |

|PHY_ID |NUMBER(4) - PRI KEY |

|PHY_PHONE |CHAR(8) |

|PHY_NAME |VARCHAR2(50) |

|PATIENT | |

|PATIENT_NO |NUMBER(4) - PRI KEY |

|DATE_ADMITTED |DATE |

|DATE_DISCHARAGED |DATE |

|PAT_NAME |VARCHAR2(50) |

|ROOM_LOCATION |CHAR(4) |

|ROOM | |

|ROOM_LOCATION |CHAR(4) - PRI KEY |

|ROOM_ACCOMODATION |CHAR(2) |

|ROOM_EXTENSION |NUMBER(4) |

|PROCEDURES | |

|PROCEDURE_NO |NUMBER(4) - PRI KEY |

|PROC_DESCRIPTION |VARCHAR2(50) |

Implement the following queries (Use Hospital_DB.sql to populate the DB):

1. Get the PATIENT_NO, ITEM_CODE, and CHARGE and from the BILLED table for a specific PATIENT_NO

2. List all of the different charges that are stored to the table

3. Display all columns and all rows from the BILLED table

4. Display all charges greater than Rs. 5.00 for the PATIENT_NO 1116

5. Display all charges for either patient 1116 or patient 1117

6. Count the number of times patient 1116 has been charged for items

7. Display number of DISTINCT procedures performed on a patient

8. Give a meaningful column name for number of DISTINCT procedures in the above query

9. Display a calculated value such as the current charge and the amount that would be charged if the charge were increased by 6% for all rows in the ITEM table

10. List all patients hospitalized more than 6 days

11. List the total charges per patient for expensive medical items (CHARGE greater than Rs100 for an item) where patients owe the hospital a  sum (total charges over Rs500)

12. List the patients who had either Dr. Hawkeye Pierce or Dr. Trapper John or Dr. Jonas Salkman as a physician

13. Show the patient names (PAT_NAME field) and associated physician names (PHY_NAME field) along with the Patient information

14. List the PATIENT_NO and DATE_DISCHARGED from the PATIENT table and the associated CHARGE from the BILLED table.

6. PL/SQL

1. PL/SQL Environment

2. PL/SQL Syntax

3. Conditional & iteration control: IF-THEN – END IF

WHILE LOOP

Exercise

Usage of IF –THEN

Write a PL/SQL code block that will accept an account number from the user and debit an amount of Rs. 2000 from the account if the account has a minimum balance of 500 after the amount is debited. The Process is to fired on the Accounts table.

Usage of While:

Write a PL/SQL code block to calculate the area of the circle for a value of radius varying from 3 to 7. Store the radius and the corresponding values of calculated area in a table Areas.

Areas – radius, area.

Usage of For:

Write a PL/SQL block of code for inverting a number 5639 or 9365.

Usage of for and goto Statement:

Write a PL/SQL block of code to achieve the following: if the price of Product ‘p00001’ is less than 4000, then change the price to 4000. The Price change s to be recorded in the old_price_table along with Product_no and the date on which the price was last changed. Tables involved: product_master- product_no, sell_price.

Old_price_table- product_no,date_change, Old_price

7. Exceptions and Cursors

-Exception handling

-Use of cursors

-Types of cursors –Implicit & Explicit

-Opening a cursor

-Explicit cursor attributes.

-Cursor for loops

Exercise:

Error Handling:

Write a Pl/Sql block which displays area for a given radius. If no data found then display an error message.

Modify the above problem to demonstrate too_many_values and others exception

Write a PL/SQL block of code such that depending upon the user entered salesman_no, the commission_amount is calculated and inserted into the commission_payable table. Salesman_Master (salesman_no, salesman_name, rate_of_commission, tgt_to_get, ytd_sales) table records the sales informationof different salesperson. A salesman is eligible for commission only when he achieves the target sales when omission is paid. The commission amount, the salesman_no and the date_of_payment is recorded in commission_payable table. Raise an exception if the total sales by a salesman is less than the target.

Cursors:

Employee –Emp_code, Emp_name, job, salary, Dept_no.

CursorName %ISOPEN / %FOUND / %NOTFOUND:

The HRD manager has decided to raise the salary for all the employees in department number 20 by 0.05. whenever any such raise is given to the employees, a record for the same is maintained in the temp_raise table. It includes the employee number, the date when the raise was given and the actual raise. Write a PL/SQL block to update the salary of each employee and insert a record in the emp_raise table.

CursorName%ROWCOUNT:

Write a PL/SQL block that will display the name, department and salary of the first 10 employees getting the highest salary.

8. Additional Constructs in Cursors

Exercise:

A HRD manager has decided to raise the salary for all the employees in Department number 20 by 0.05. Whenever any such raise is given to the Employees, an audit trail of the same are maintained in the emp_raise table. The emp_raise table holds the employee number the date when the raise was given and the raise amount. Write a PL/SQL block to update the Salary of each employee of dept_no 20 appropriately and insert a record in the emp_raise table as well.

Employee- Emp_code, Ename, Dept_no, job, salary.

Emp_raise-Emp_code, Raise_date, Raise_amt.

Cursor For Loops:

Repeat the above problem using cursor for loops.

Where Current of:

Repeat the above problem using where current of.

Parameterized Cursors:

Write a Pl/Sql block of code that would update the Bal_stock in the item_master(Itemid, Description, Bal_stock) table each time a transaction takes place in the item_transaction (Itemid, Description, Quantity) table. The change in the item_master table depends on the itemid. If the itemid is already present in the item_master table then an update operation is performed to decrease the Bal_stock by the Quantity specified in the item_transaction table. In case the itemid is not present in the item_master table then the record is inserted into the item_master table.

Transactions: COMMIT / ROLLBACK / SAVEPOINT:

Write a Pl/Sql block first inserts a record in the Emp(Emp_No,Emp_Name,Sal) in ‘Emp’ table. Update the salaries of Anand and Mohan by Rs. 2000 and Rs.1500 respectively. Then check to see that the total salary does not exceed 20000. if the total salary is greater than 20000 then undo the updates made to the salaries of Anand and Mohan.

9. Triggers:

- Row Triggers, statement Triggers, Before Vs After Triggers,

- Execute, Modify, Drop, disable, and Enable Triggers commands.

- SHOW ERRORS.

- RAISE_APPLICATION_ERROR.

Exercise:

Write a row trigger to insert the existing values of the salary table into a new table when the salary table is updated

Write a row trigger to update the ITMStock in ITMMAST when a new transaction is occurred.

Create a transparent audit system for a table Client_master (client_no, name, address,

Bal_due). The system must keep track of the records that are being deleted or updated.

The functionality being when a record is deleted or modified the original record details

and the date of operation are stored in the auditclient(client_no, name, bal_due, operation,

userid, opdate) table, then the delete or update is allowed to go through.

10. Procedures, Functions& Packages:

Parameter modes- IN, OUT, IN OUT

Exercise:

Procedures:

Write a simple procedure to display a message “Good Day to You”

Write a procedure which takes the department_id as input parameter and lists the names of all employees belonging to that department.

Write a Pl/Sql block of code that lists the highest salary drawn by an employee in each of the departments. It should make use of a named procedure dept_highest which finds the highest salary drawn by an employee for the given department.

Code a procedure to calculate the sales made to a particular Customer. (The customer id in the transaction file must be Selected, the quantity sold must be multiplied by the price Which is in the ITEM master and this value must be Accumulated for all records of that customer in the Transaction file).

Use movies database: (a) Create procedure to find out mv_no issued to IVAN (b) Create procedure to find out names and mv_no of all customers who have been issued a movie (c) Create title and type of movies that have been issued to 'Vandana'

Functions:

Code a function to return the Square of a given number.

Code a function to return the net salary given the employee number.

Write a Pl/Sql block of code that lists the highest salary drawn by an employee in each of the departments. It should make use of a function dept_highest which return the highest salary drawn by an employee for the given department.

Packages:

Create a package to include the following:

A named procedure to list the employee_names of given department

A function which returns the max salary for the given department

Write a Pl/Sql block to demonstrate the usage of above package components

11&12. Connecting Database: ODBC/JDBC

Interfacing Java/VB with Oracle Database

Implement a GUI based database application for BANK Database to support few /

all of the following:

• insertion of new customers, accounts, loans

• deletion of customers, accounts, loans

• withdrawal and deposit of amount

• payments for loans

13&14. Test

References:

1. Ivan Bayross, “SQL, PL/SQL” 2nd / 3rd Edition , BPB Publications

******************

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download