Exam Details
Subject | lab ii — relational database management systems | |
Paper | ||
Exam / Course | p.g.d.c.a.(s) | |
Department | ||
Organization | Alagappa University Distance Education | |
Position | ||
Exam Date | December, 2017 | |
City, State | tamil nadu, karaikudi |
Question Paper
DISTANCE EDUCATION
P.G.D.C.A. EXAMINATION, DECEMBER 2017.
First Semester
LAB II — RELATIONAL DATABASE MANAGEMENT
SYSTEMS
(2011 Calendar Year onwards)
Time Three hours Maximum 100 marks
Record Note Book 5
Algorithm/Flow chart 15
Program 35
Debugging and Execution 35
Result 10
Total 100
ONE question should be given to each candidate by lot system.
1. Create following tables and put proper constraints
wherever required:
Employee name, addr, qualification, course_id,
dept, desig, doj)
Accounts basic_sal, DA, HRA, PF, gross_sal)
Training (course_id, course_name)
insert data in the above tables.
Solve the following queries using SQL
Select name of all the employees in 'system'
department.
Select employee no. and qualification of all
employees in marketing department and those
whose name start with
Sub. Code
16
DE-9801
WS
2
Select emp no. of all the employees whose basic
range of 3000 to 5000.
Select employee no of all those employees who do
not belong to 'accounts' departments.
Select employee no. of all those employee whose
gross pay is greater then 5000 but basic is less then
4000.
Select that employee whose designation is either
'executive' or 'Sr. executive' and belongs
to 'system' dept.
Find out the names of the existing departments in
this office.
Extract the names of those employees who got
appointed after completion of 25 years of age.
Extract the names, date of birth, date of joining of
all the employees with column as employees: name,
date of birth, date of joining.
2. Create following tables and put proper constraints
wherever required:
Emp ename, sal, man_no, job_no, doj, dob,
commission)
Dept(d_no, dname)
Job(Job_type, j_performed)
Manager(m_name,
Insert data in the above tables.
Solve the following queries using SQL.
List the details of employee having salary between
1000 and 2000.
List deptno. And dept name in dept order.
Cut here
DE-9801
WS
3
List the employees in dept 10 and 20 in alphabetical
order
Display all employees with TH Or LL in their name
List name, job and salary of all employees who have
manager
Display all the employee hired during 1983.
Display name, annual salary and commission of all
employee whose monthly salary is greater then
their commission.
Select data as SMITH HAS WORKED IN THE
POSITION OF CLERK IN DEPT 20.
List employees name and salaries increased by 17%
Find the minimum salary of all employee
Find the average salary and average total
remuneration of each job type.
3. Specify the following queries in SQL on the database
University schema
CLASSROOM (Building, Room Number, capacity)
DEPARTMENT (Dept name, building budget)
COURSE (Course id title, dept name, credits)
INSTRUCTER name, dept name, salary)
SECTION (Course id, Sec id, semester, year, building,
room number, time slot id)
TEACHES course id, see id, semester, year)
STUDENT name, dept name, tot credit)
TAKES Course_id, sec_id, semester, year, grade)
ADVISOR
TIME SLOT (Time_slot_id, day, start time, end time)
PREREQ (Course_id, Prereq_id)
Cut here
DE-9801
WS
4
Supply the datas and use SQL to perform the following;
Find the average salary of instructors in those
departments where the average salary is more than
"42,000."
Find the average salary of all instructors.
Find the total number of student enrolled in each of
department in university
For each course section offered in 2009, find the
average total credits (tot cred) of all students
enrolled in the section, if the section had at least
2 students.
Find the instructors average "salaries of those
departments where the average salary is greater
than "40,000"
Find the total number of students with each grade.
Find the lowest, across all departments of the
per-department maximum salary computed by the
preceding query.
4. Consider the employee database where the primary keys
are bold. Give an expression in SQL for each of the
following queries.
employee (employee_name, street, city,)
works (employee_name, company_name, salary)
company (company_name, city)
manages (employee_name, manager_name)
Find the names and cities of residence of all
employees who work for First Bank Corporation.
Cut here
DE-9801
WS
5
Find the names, street addresses, and cities of
residence of all employees who work for First Bank
Corporation and earn more than 10,000.
Find all employees in the database who do not work
for First Bank Corporation.
Find all employees in the database who earn more
than, each employee of Small Bank Corporation.
Assume that the companies may be located in
several cities. Find all companies located in every
city in which Small Bank Corporation is located.
Find the company that has the most employees.
5. An airline reservation database contains the reservation
table and personal table.
Reservation Table contains the following fields:
Flight No.. class, passenger name, seat number
Personal Table contains the following:
Passenger name, sex, age, marital status,
nationality
Write a program in ORACLE to prepare the following
reports:
List the passengers name with seat number
according to the class and flight numbers.
Total number of married female passengers in a
particular flight,
List out the male passengers in the age between 18
to 25 for all flights.
Cut here
DE-9801
WS
6
6. Write a program in SQL for Hospital Billing system with
the following fields:
Patient no, Patient name, age, attending doctor,
patient_type consulting charge, Blood test
charge, X-ray charges, other test charges and total fee.
Prepare reports with the following conditions:
The patients undergone blood test
The patients who have taken X-rays.
The patients who belong to IN-patient category.
The common fields that is to be included in the above
reports: patient no, patient name. age, charges and total
charges.
7. Create a table "Voters_EPIC" with following fields for
"XYZ" constituency.
Ward, Booth_id, Booth_name, Voter_id, Voter_name,
Voter_age, Sex and address.
Write a PL/SQL statements for the following:
Insert at least 10 records using various insert
syntax
Count the number of senior citizens.
List down all the total number of female voters in
the ward with all the informations.
Count the number of male and female voters
between the age 35 and 50.
————————
P.G.D.C.A. EXAMINATION, DECEMBER 2017.
First Semester
LAB II — RELATIONAL DATABASE MANAGEMENT
SYSTEMS
(2011 Calendar Year onwards)
Time Three hours Maximum 100 marks
Record Note Book 5
Algorithm/Flow chart 15
Program 35
Debugging and Execution 35
Result 10
Total 100
ONE question should be given to each candidate by lot system.
1. Create following tables and put proper constraints
wherever required:
Employee name, addr, qualification, course_id,
dept, desig, doj)
Accounts basic_sal, DA, HRA, PF, gross_sal)
Training (course_id, course_name)
insert data in the above tables.
Solve the following queries using SQL
Select name of all the employees in 'system'
department.
Select employee no. and qualification of all
employees in marketing department and those
whose name start with
Sub. Code
16
DE-9801
WS
2
Select emp no. of all the employees whose basic
range of 3000 to 5000.
Select employee no of all those employees who do
not belong to 'accounts' departments.
Select employee no. of all those employee whose
gross pay is greater then 5000 but basic is less then
4000.
Select that employee whose designation is either
'executive' or 'Sr. executive' and belongs
to 'system' dept.
Find out the names of the existing departments in
this office.
Extract the names of those employees who got
appointed after completion of 25 years of age.
Extract the names, date of birth, date of joining of
all the employees with column as employees: name,
date of birth, date of joining.
2. Create following tables and put proper constraints
wherever required:
Emp ename, sal, man_no, job_no, doj, dob,
commission)
Dept(d_no, dname)
Job(Job_type, j_performed)
Manager(m_name,
Insert data in the above tables.
Solve the following queries using SQL.
List the details of employee having salary between
1000 and 2000.
List deptno. And dept name in dept order.
Cut here
DE-9801
WS
3
List the employees in dept 10 and 20 in alphabetical
order
Display all employees with TH Or LL in their name
List name, job and salary of all employees who have
manager
Display all the employee hired during 1983.
Display name, annual salary and commission of all
employee whose monthly salary is greater then
their commission.
Select data as SMITH HAS WORKED IN THE
POSITION OF CLERK IN DEPT 20.
List employees name and salaries increased by 17%
Find the minimum salary of all employee
Find the average salary and average total
remuneration of each job type.
3. Specify the following queries in SQL on the database
University schema
CLASSROOM (Building, Room Number, capacity)
DEPARTMENT (Dept name, building budget)
COURSE (Course id title, dept name, credits)
INSTRUCTER name, dept name, salary)
SECTION (Course id, Sec id, semester, year, building,
room number, time slot id)
TEACHES course id, see id, semester, year)
STUDENT name, dept name, tot credit)
TAKES Course_id, sec_id, semester, year, grade)
ADVISOR
TIME SLOT (Time_slot_id, day, start time, end time)
PREREQ (Course_id, Prereq_id)
Cut here
DE-9801
WS
4
Supply the datas and use SQL to perform the following;
Find the average salary of instructors in those
departments where the average salary is more than
"42,000."
Find the average salary of all instructors.
Find the total number of student enrolled in each of
department in university
For each course section offered in 2009, find the
average total credits (tot cred) of all students
enrolled in the section, if the section had at least
2 students.
Find the instructors average "salaries of those
departments where the average salary is greater
than "40,000"
Find the total number of students with each grade.
Find the lowest, across all departments of the
per-department maximum salary computed by the
preceding query.
4. Consider the employee database where the primary keys
are bold. Give an expression in SQL for each of the
following queries.
employee (employee_name, street, city,)
works (employee_name, company_name, salary)
company (company_name, city)
manages (employee_name, manager_name)
Find the names and cities of residence of all
employees who work for First Bank Corporation.
Cut here
DE-9801
WS
5
Find the names, street addresses, and cities of
residence of all employees who work for First Bank
Corporation and earn more than 10,000.
Find all employees in the database who do not work
for First Bank Corporation.
Find all employees in the database who earn more
than, each employee of Small Bank Corporation.
Assume that the companies may be located in
several cities. Find all companies located in every
city in which Small Bank Corporation is located.
Find the company that has the most employees.
5. An airline reservation database contains the reservation
table and personal table.
Reservation Table contains the following fields:
Flight No.. class, passenger name, seat number
Personal Table contains the following:
Passenger name, sex, age, marital status,
nationality
Write a program in ORACLE to prepare the following
reports:
List the passengers name with seat number
according to the class and flight numbers.
Total number of married female passengers in a
particular flight,
List out the male passengers in the age between 18
to 25 for all flights.
Cut here
DE-9801
WS
6
6. Write a program in SQL for Hospital Billing system with
the following fields:
Patient no, Patient name, age, attending doctor,
patient_type consulting charge, Blood test
charge, X-ray charges, other test charges and total fee.
Prepare reports with the following conditions:
The patients undergone blood test
The patients who have taken X-rays.
The patients who belong to IN-patient category.
The common fields that is to be included in the above
reports: patient no, patient name. age, charges and total
charges.
7. Create a table "Voters_EPIC" with following fields for
"XYZ" constituency.
Ward, Booth_id, Booth_name, Voter_id, Voter_name,
Voter_age, Sex and address.
Write a PL/SQL statements for the following:
Insert at least 10 records using various insert
syntax
Count the number of senior citizens.
List down all the total number of female voters in
the ward with all the informations.
Count the number of male and female voters
between the age 35 and 50.
————————
Other Question Papers
Subjects
- computer lab iv (unix and shell programming)
- lab i — data structures using c
- lab ii — relational database management systems
- lab iii — graphics using c++