DISTANCE EDUCATION
M.Sc. (Software Engineering) Years Integrated) DEGREE
EXAMINATION, MAY 2017.
RELATIONAL DATABASE MANAGEMENT SYSTEM LAB
Time Three hours Maximum 100 marks
Examiner should select and give ONE questions to each
candidate from lot system.
1. Create oracle table with the table name as
STUDENT and do the following
• INSERT the values in the table
• Display the students name who's Rollno. is 5
• Display the students percentage between 70
and 85
• UPDATE the PERCENTAGE=90 WHERE
Rollno=3;
• Delete the table
Create table sales_order_details with the following
fields s_order_no product_no, description,
qty_ordered, qty_disp, product_rate, profit_percent,
sell_price, supplier_name.
• Select each row and compute sell_price*.50
and sell_price*1.50 for each row selected.
• Select product_no, profit_percent, Sell_price
where profit_per is not between 10 and 20
both inclusive.
Sub. Code
27
DE-9561
Ws3
2
• Select product_no, description, profit_percent,
sell_price where profit_percent is not between
20 and 30.
• Select the suppliername and product_no
where suppliername has or as second
character
2. Create the Table CUSTOMER with the following
fields cust_no, Fname, Lname, birth_date, city,
mob_no,dept_name, salary. Salary should not be
less than 5000.
Solve the following query.
• List of customer by salary wise.
• Display details of customer whose birth date is
in year of
• Display details of customer whose mobile no
start with series.
• Display name and depart name of customer
whose city staffing from
• Display count of customer having salary
greater than 3000.
• List name of customer having as 2nd letter
in first name.
Create the following tables: PROPERTY with the
fields of pno, description, area etc., OWNER with
the fields of onername, address, phone etc.,
An owner can have one or more properties, but a
property belongs to exactly one owner.
• Insert two records into owner table.
• Insert 2 property records for each owner.
Cut here
DE-9561
Ws3
3
• Update phone no of "Mr. Ragu" to 9123456789.
• Delete all properties from "Karaikudi" owned
by "Mr. Kumar".
• List names of owners having property in
Erode.
3. Create the following tables:
EMP with the fields like eno, name, dno, salary etc.,
PROJECT with the fields like pno, pname, controldno,
budget etc.,
• List the names of emp whose salary is greater
than 5000 and less than 20000.
• List the names of projects, controlled by
department no 503
• List the details of the projects with minimum
budget.
• List the details of the projects with maximum
budget.
• List the names of employees, working on
computerscience department.
Create the following tables: PERSON with the
fields like pnumber, pname, birthdate, income etc.,
AREA with the fields like aname, area_type etc.,
• List the names of all people living in moshi'
area.
• List details of all people whose names start
with the alphabet and contains maximum
7 alphabets in it.
Cut here
DE-9561
Ws3
4
• Give the count of people who are born on
'18th July 1982'
• Give the count of people whose income is
below 10000.
4. Create a Table for a Company with the following
fields and inserts the values:
Fieldname Fieldtype Fieldsize
Company_name Character 15
Proprietor character 15
Address character 25
Suppliername character 15
No of employees Number 4
GPpercent number 6with2decimal places
Queries:
• Display all the records of the company which
are in the ascending order of GP percent
• Display the name of the company whose
supplier name is 'AAA".
• Display the details of the company whose GP
percentage is greater than 20 and orderby GP
percentage.
• Display the detail of the company having the
employee ranging from 300 to 1000
• Display the name of the company whose
supplier is same as like Tata's
Cut here
DE-9561
Ws3
5
Create a table named Student with the following
fields and insert the values:
Field name field type field size
Student Name character 15
Gender character 6
Roll No. character 10
Department Name character 15
Address character 25
Percentage number 4 with 2 decimal places
Queries:
• Calculate the average percentage of the
students.
• Display the names bf the students whose
percentage is greater than 80
• Display the details of the student who got the
highest percentage.
• Display the details of the students whose
percentage is between 50 and 70.
• Display the details of the students whose
percentage is greater than the percentage of
Roll No 12CA01
5. Create the table PRODUCT with the following
fields and insert the values:
Field name Field type Field size
Product no number 6
Product name character 15
Unit of measure character 15
Quantity number 6 with 2 decimal places
Total amount number 8 with 2 decimal places
Queries:
• Using update statements calculate the total
amount and then select the record.
Cut here
DE-9561
Ws3
6
• Select the records whose unit of measure is
"Kg"
• Select the records whose quantity is greater
than 10 and less than or equal to 20
• Calculate the entire total amount by using
sum operation
• Calculate the number of records whose unit
price is greater than 50 with count operation.
Write a PL/SQL program for income tax bill
preparation.