1

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
  1. SQL 4 weeks
  1. PL/SQL BASICS 1 week
  1. Cursors & exception handling 2 weeks
  1. Triggers, Procedures, Functions, Packages 2 weeks
  1. 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.)

  1. 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.
  1. Create relationships between your Member, RentalAgreement, VideoForRent, Video tables using the Relationship window.
  1. 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.
  1. 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.
  1. 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.

  1. 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 <tableName> (<list of attributes and their types>);

Creating a Table with a Primary Key

CREATE TABLE <tableName> (<attribute_name> <type> PRIMARY KEY,);

CREATE TABLE <tableName>(<attrs and their types>, PRIMARY KEY (<attribute1>,<attribute2>));

Inserting Tuples

INSERT INTO <tableName>

VALUES (<list of values for attributes, in order>);

Deleting Tuples

DELETE FROM <tableName> [where <condition>];

Updating Column values

UPDATE <tableName> SET <columnName = value>;

Getting the Value of a Relation

SELECT * FROM <tableName>;

SELECT Column1, column2, … from <tableName>;

Modifying the structure of Tables

Adding New Columns:

ALTER TABLE <tableName> ADD(<newColumnName> <dataType(size)>, …);

Modifying Existing Columns:

ALTER TABLE <tableName> MODIFY(<ColumnName> <newDataType(newSize)>, …);

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 <tableName>;

ALTER TABLE <table name> DROP (<attribute>)

Getting Information about Your Database

SELECT TABLE_NAMEFROM USER_TABLES;

To recall the attributes of a table

DESCRIBE <tableName>;

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 <yourName>/<yourPassword> @<fileName>

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 <filename>

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 insteadof 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 locatedin 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 AggregateFunctions (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 balanceis 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 thetotal 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.