SCA of Computer Application
DBMS Lab
IFTM,University,Moradabad
1 Database Schema for a customer-sale scenario
Customer(Cust id : integer, cust_name: string)
Item(item_id: integer, item_name: string, price: integer)
Sale(bill_no: integer, bill_data: date, cust_id: integer,
item_id: integer, qty_sold: integer)
For the above schema, perform the following—
a) Create the tables with the appropriate integrity constraints
b) Insert around 10 records in each of the tables
c) List all the bills for the current date with the customer names
and item numbers
d) List the total Bill details with the quantity sold, price of the
item and the final amount
e) List the details of the customer who have bought a
product which has a price>200
f) Give a count of how many products have been bought by
each customer
g) Give a list of products bought by a customer having
cust_id as 5
h) List the item details which are sold as of today
i) Create a view which lists out the bill_no, bill_date,
cust_id, item_id, price, qty_sold, amount
Create a view which lists the daily sales date wise for the last
one week
2 Database Schema for a Student Library scenario
Student(Stud_no : integer, Stud_name: string)
Membership(Mem_no: integer, Stud_no: integer)
Book(book_no: integer, book_name:string, author: string)
Iss_rec(iss_no:integer, iss_date: date, Mem_no: integer,
book_no: integer)
For the above schema, perform the following—
a) Create the tables with the appropriate integrity constraints
b) Insert around 10 records in each of the tables
c) List all the student names with their membership numbers
d) List all the issues for the current date with a student .
e) List the details of students who borrowed book whose author is ABC.
f) Give a count of how many books have been bought by each student
g) Give a list of books taken by student with stud_no as 5
h) List the book details which are issued as of today
i) Create a view which lists out the iss_no, iss _date,stud_name, book name
j) Create a view which lists the daily issues-date wise for the last one week
3 Database Schema for an Employee-pay scenario
employee(emp_id : integer, emp_name: string)
department(dept_id: integer, dept_name:string)
paydetails(emp_id : integer, dept_id: integer, basic: integer,
deductions: integer, additions: integer, DOJ: date)
payroll(emp_id : integer, pay_date: date)
For the above schema, perform the following—
a) Create the tables with the appropriate integrity
constraints
b) Insert around 10 records in each of the tables
c) List the employee details department wise
d) List all the employee names who joined after particular date
e) List the details of employees whose basic salary is between 10,000 and 20,000
f) Give a count of how many employees are working in each department
g) Give a name of the employees whose netsalary>10,000
h) List the details for an employee_id=5
i) Create a view which lists out the emp_name, department, basic, deductions, net salary
j) Create a view which lists the emp_name and his net salary
4 Database Schema for a Video Library scenario
Customer(cust_no: integer,cust_name: string)
Membership(Mem_no: integer, cust_no: integer)
Cassette(cass_no:integer, cass_name:string, Language:
String) Iss_rec(iss_no: integer, iss_date: date, mem_no: integer,
cass_no: integer)
For the above schema, perform the following—
a) Create the tables with the appropriate integrity constraints
b) Insert around 10 records in each of the tables
c) List all the customer names with their membership
numbers
d) List all the issues for the current date with the customer
names and cassette names
e) List the details of the customer who has borrowed the
cassette whose title is “ The Legend”
f) Give a count of how many cassettes have been borrowed
by each customer
g) Give a list of book which has been taken by the student
with mem_no as 5
h) List the cassettes issues for today
i) Create a view which lists outs the iss_no, iss_date,
cust_name, cass_name
j) Create a view which lists issues-date wise for the last one
week
5 Database Schema for a student-Lab scenario
Student(stud_no: integer, stud_name: string, class: string)
Class(class: string, descrip: string)
Lab(mach_no: integer, Lab_no: integer, description: String)
Allotment(Stud_no: Integer, mach_no: integer, day of week: string)
For the above schema, perform the following—
a) Create the tables with the appropriate integrity constraints
b) Insert around 10 records in each of the tables
c) List all the machine allotments with the student names,
lab and machine numbers
d) List the total number of lab allotments day wise
e) Give a count of how many machines have been allocated
to the ‘CSIT’ class
f) Give a machine allotment etails of the stud_no 5 with his
personal and class details
g) Count for how many machines have been allocated in
Lab_no 1 for the day of the week as “Monday”
h) How many students class wise have allocated machines in
the labs
i) Create a view which lists out the stud_no, stud_name,
mach_no, lab_no, day of week
j) Create a view which lists the machine allotment details for
“Thursday”.
6 Write a program to find the largest number from the given three numbers.
7 Simple programs using a loop, while and for iterative control statement.
8 Write a program to check whether the given number is Armstrong or not
9 Write a program to generate all prime numbers below 100.
10 Write a program to demonstrate the GOTO statement.
11 Write a program to demonstrate %type and %rowtype attributes
12 Write a program to demonstrate predefined exceptions
13 Write a program to demonstrate user defined exceptions
14 Create a cursor, which displays all employee numbers and names from the EMP table.
15 Create a cursor, which updates the salaries of all employees as per the given data.
16 Create a cursor, which displays names of employees having salary > 50000.
17 Create a procedure to find reverse of a given number
18 Create a procedure to update the salaries of all employees as per the given data
19 Create a procedure to demonstrate IN, OUT and INOUT parameters
20 Create a function to check whether given string is palindrome or not.
21 Create a function to find the sum of salaries of all employees working in depart number 10.
22 Create a trigger before/after update on employee table for each row/statement.
23 Create a trigger before/after delete on employee table for each row/statement.
24 Create a trigger before/after insert on employee table for each row/statement.
25 Create a Form to display employee details using SQL
26 Create a Report to generate all employee annual salaries….
Additional Programs
S. No Name of the Program
1 Create a form using Forms 6i to display Employee table data.
2 Create a Master/details relationship form which performs Add New, Search, Delete, Save and Update on the records
3 Generate a report to calculate employee’s salaries department wise from employee table.
4 Create a Report to generate the details of employee table including sum and average salaries department wise.
No comments:
Post a Comment