MC9218 DBMS LAB

Ex.No1. Single Line and Group Line Functions AIM: To create a table and then perform Insertion, Deletion and Updating and then execute single line and group line functions. DESCRIPTION: Name strno stname stbranch stdob stnative Algorithm: 1. Create a student table with the required columns. 2. Alter the table by adding an additional column. 3. Insert the values into the table. 4. Update the row where stname is AAA and set the stnative to Chennai. 5. Using the select command view the records. 6. Apply the group line functions. Queries: * Class work Practice.. Null? Type Number(5) Varchar2(10) Varchar2(3) Date Varchar2(8)

MC9218 DBMS LAB

Ex.No.2. Implementing TCL and DCL commands AIM: To create a table and execute the TCL and DCL commands. DESCRIPTION: Name strno stname dept year m1 m2 m3 tot avg Algorithm:
1.

Null?

Type Number(5) Varchar2(10) Varchar2(3) Number(3) Number(3) Number(3) Number(3) Number(3) Number(3)

Create a table called mark with the required fields. Insert values into the table. Update the marks of the student where strno = 8001. Create a save point, s and perform. . Again rollback to the save point using rollbacks.

2. 3. 4.
5.

Queries: * Class work Practice.. .

MC9218 DBMS LAB

Ex.No.3. Database Objects AIM: To create a table and to manipulate various database objects. DESCRIPTION: Name rollno name branch m1 m2 m3 Algorithm: 1. 2. 3. 4. Create a table with the required fields. Insert the values into the table. Create a synonym stu1 for the table. Create a sequence b2 and specify the starting value, max value and Increment value. 5. After the max value of the sequence B2. Null? Type Number(5) Varchar2(10) Varchar2(3) Number(3) Number(3) Number(3)

Queries: * Class work Practice..

MC9218 DBMS LAB

Ex.No.4. Table Lock and Partitions AIM: To create a table and create locks, partitions for the particular database. DESCRIPTION: Name rollno name branch m1 m2 m3 Name e_no e_name e_dept Algorithm: 1. 2. 3. 4. 5. 6. Create a table with the required fields. Update the record where rollno=8011, and set the name to the desired name. Lock the table using different lock modes. Share update mode, exclusive mode, with no wait condition. Create an employee table with the required fields. Create partition and view partitions. Null? Null? Type Number(5) Varchar2(10) Varchar2(3) Number(3) Number(3) Number(3) Type Number(5) Varchar2(10) Varchar2(10)

Queries: * Class work Practice.

MC9218 DBMS LAB

Ex.No.5. Exception Handling AIM: To write a PL/SQL procedure for an application using user-defined and predefined exceptions. DESCRIPTION: Name emp_no emp_name emp_sal Name sno sname m1 m2 m3 Algorithm: Null? Null? Type number(5) Varchar2(10) number(7,2) Type number(5) varchar2(10) number(3) number(3) number(3)

USER DEFINED EXCEPTION: 1. Create a table with the required fields. 2. Declare the necessary variables with appropriate datatypes. 3. If employee salary > 10,000, then user-defined exception is raised. . 4. Print the exception e. PRE DEFINED EXCEPTION: 1. 2. Create a table with the required fields. Using select command, get the required data..

3. Check whether the Query returns more than one row or not and also print them. . Queries: * Class work Practice.
5

MC9218 DBMS LAB

Ex.No.6. Cursors AIM: To write a PL/SQL program for an application using cursor. Algorithm: 1. Declare the necessary variables with appropriate data types. 2. 3. Define the cursor a, open it and select the value of m3 from the table where m3=51. In the loop section, fetch the value of the cursor a into sm3 and update (the stud view) the sname to aaa where m3=sm3.

4. Close the loop and print that table which has been updated. 5. Close the cursor and stop the execution. Strong Cursor: Algorithm: 1. Declare the cursor and necessary variables with appropriate (values) data types. 2. Begin the execution. 3. Assign the reference variable, enter no. 4. Check if n is equal to 10, and if so, open the cursor and select sno from t the table where sname=aaa. 5. If n is not equal to 10, open the cursor, select sname from the table, here sno=105. 6. In the loop, fetch (var1) cursor into no and exit when the cursor is not found and display a message. 7. End of the loop, cursor is closed. 8. Stop the program execution.

MC9218 DBMS LAB

Implicit Cursor: 1.
2.

Start the execution of the program. Delete from table stud 01, the records where sno=105. If it is not found, then print that the value is not found. Else, if found, print element founded and delete. Stop the programs execution.

3. 4. 5.

Queries: * Class work Practice.

MC9218 DBMS LAB

Ex.No.7. Functions AIM: To create a program to prepare reports for an application using functions. Algorithm: 1. 2.
3.

Define a function along with the arguments with its appropriate data types. Select command, re level, max level from the table and copy the same values to the new variables which have been declared. Add qty hand and re level is less than max level and if again max value to arguments. If it is not less, add the values of qty hand and re level and assign into arguments. Close the cursor and stop the execution. Close the if condition. Stop the execution.

4. 5.
6.

7.

Queries: * Class work Practice.

MC9218 DBMS LAB

Ex.No.8. Triggers AIM: To create a PL/SQL block for transaction applications of a typical application using triggers. Algorithm: 1.
2.

Create the triggers and the time / situation as to when it should be found or triggered. Select the item code from order mastertable where qtyhand < re level and put or insert values into code. Check if itemcode is in the ob and if so raise an application error. Stop the process.

3.

4. Queries:

* Class work Practice.

MC9218 DBMS LAB

Ex.No.9. Packages AIM: To create a PL/SQL block for transaction applications of a typical application using Packages. Algorithm: 1. 2. 3. 4. 5. Queries: * Class work Practice. Create a package along with the arguments of appropriate data types. Select columns, status value where orderno = ono from the inventory note. If the status is P, display the order is completed. Close the condition and the procedure. Stop the process.

10

MC9218 DBMS LAB

Ex.No.10.Application creation with Front end and Back end AIM: To create an employee application using VB as front end and Oracle as backend. DATA FLOW DIAGRAM:
[

Em ployee
E-R- Diagram:
No. Name BP allo ww Ded NP

Access View Store

Emp det

Em ployee
Algorithm:
1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12.

Create a table namely empdet with no., name, allow, ded, np as attributes. Create a form in Visual Basic having 6 labels, 6 text boxes, 6 command buttons and a data control. In getfocus() event of the textbox, calculate netpay as follows.

Netpay = basicpay + allowance - deduction. Connect the form with oracle. Connect the front end and backend. For each command button, key in the codings. For adding records, enter the data in textbox and click add. For clearing records, use clear. For modifying data values, enter appropriate values and click modify. For deleting, click delete command button For viewing of the records, click view command button. Finally, click close to close the VB form.

Queries: * Class work Practice.

MC9218 DBMS LAB

12