DBMS LAB Question set

DBMS LAB Question set

1. Consider the following database consisting of the following tables:

            Employee (ssn, first name, last name, gender, designation, doj, address)

            Employee-salary (ssn, basic pay, DA, TA, pay)

            Department (did, dname,mgrssn)

            Employee-department (ssn, deptid)

            Employee-dependency (ssn, depname, depgender, deprelationship)

I.                   1. Retrieve the doj, address of employees who work for ‘Research’ department

2. For each employee, retrieve the employee’s first name and last name

3. Retrieve the names of each employee who has a dependency with same first name and gender of that employee.

II.                Write a PL/SQL function to calculate the income tax for the given employee:

Conditions:

                  If pay <=10000, no tax is charged.

                  If pay is >10000 and <=20000, 20% of pay is charged as tax.

                  If pay is >20000 and <=50000, 35% of pay is charged as tax.

                  If pay is >50000 , 50% of pay is charged as tax.

III.             Design & implement employee Personal form using Visual Basic and generate report.

2. Consider the following database consisting of the following tables:

            Employee (ssn, first name, last name, gender, designation, doj, address)

            Employee-salary (ssn, basic pay, DA, TA, pay)

            Department (did, dname,mgrssn)

            Employee-department (ssn, deptid)

            Employee-dependency (ssn, depname, depgender, deprelationship)

       I.      Queries:

1. Retrieve the names of employees who have no dependents.

2. Retrieve all the information about employees working in ‘Research’ department including the department information.

3. Display the department having employee count > 5.

    II.      Write a PL/SQL cursor to do payroll processing with Employee-salary table.

 III.      Design Menu Driven application for employee database to display personal details using Visual Basic.

3. Consider the following database consisting of the following tables:

            Employee (employee name, street, city)

            Works (employee name, company name, salary, doj)

            Company (company name, city)

            Manager (employee name, manager name)

I.                   1. Find the names, streets and cities of residence of all employees who work for ‘First bank corporation’ and earn more than 10,000.

2. Find the employees in the database who live in the same cities as the companies for which they work.

3. Find those companies whose employees earn a higher salary on average than the average salary at ‘First Bank Corporation’.

II.                Write a PL/SQL function to return the name of the employee in a given company name.

    III Design an application to display Employee details using JAVA

4. Consider the following database consisting of the following tables:

            Employee (employee name, street, city)

            Works (employee name, company name, salary, doj)

            Company (company name, city)

            Manager (employee name, manager name)

I.                   1. Find all employees in database who live in the city ‘chennai’ and under the manager ‘John’.

2. Find all employees who earn more than the average salary of all employees of that company.

3. Find the no. of employees in each company..

II.                Write a PL/SQL procedure to find employee name whose company name as “small bank corporation”.

     III .    Design & implement an application to display Employees working details using Visual basic and generate report.

5. Consider the following database consisting of the following tables:

            Department (dept id, dept name)

            Student (rollno, name, gender, mark1, mark2, mar3, total, average, dept id)

            Staff (staff id, name, designation, qualification, dept id)

            Tutor (rollno, staff id)

I.                   1. Display the student details who come under the tutor ship of the given staff name ‘X’.

2. Display the student details who got greater than overall average marks of their department.

3. How many students are there in CSE department?

II.                Write a trigger to display the message “Invalid marks” when marks are < 0 and > 100 and to calculate and update total and average when a record is inserted

   III.         Design Menu Driven application display staff details using Visual Basic

6. Consider the following database consisting of the following tables:

            Department (dept id, dept name)

            Student (rollno, name, gender, mark1, mark2, mar3, total, average, dept id)

            Staff (staff id, name, designation, qualification, dept id)

            Tutor (rollno, staff id)

I.                   1. Display the staff details who work in CSE department.

2. How many different designations and departments are there?

3. Display the student details whose name start with ‘R’.

II.                Write a PL/SQL procedure to display the staff name whose deptname is ‘CSE’.

III. Design & implement an application to display student details using JAVA..

7. Consider the following database consisting of the following tables:

            Branch (bname, bcity, assets)

            Account (ano, starting date, balance)

            Customer (cusid, name, address)

            Deposit (ano, cusid, bname)

            Transaction (ano, amount, mode, date of trans)

I.                   1. Find the average account balance at each branch and display only if it is greater than 10000.

2. Display the branch details located in a city starting with the letter ‘S’..

3. Find the number of depositors in each branch.

II.                Write a view with the account table for the balance greater than 25000 and delete the record with balance 28000 from the view..

    III.      Design Menu Driven application for displaying Customer details..

8. Consider the following database consisting of the following tables:

            Party (pid, pname, leader)

            Constituency (cid, cname)

            Contestant (ctid, ctname, ctaddr)

            Election (ctid, number of votes, pname, cname)

I.                   1. Display the contestant details if they secured greater than 10,000 votes.

2. Find the number of contestants, constituency wise.

3. Display the winner details in each constituency.

II.                Write a PL/SQL function to return the number of votes secured for a given contestant name.

III.             Design & implement an application to display Election details using JAVA.

9. Consider the following database consisting of the following tables:

            Hostel (hno, hname, type [boys/girls])

            Menu (hno, day, breakfast, lunch, dinner)

            Warden (wname, qual, hno)

            Student (sid, sname, gender, year, hno)

I.                   1. Display the total number of girls and boys hostel in the college.

2. Display the menu in the hostel ‘x’ on Tuesday.

3. Display the number of wardens for each hostel.

II.                Write a View to display the girl students residing in a given hostel name and try to insert a following record in view.

                 

Sid	Sname	gender	year	Hno
120	siva	Male	second	3

 .

    III.       Design an application to display menu details of a Hostel using visual basic..

10. Consider the following database consisting of the following tables:

            Inventory (item, level, cost)

            Minlevel (item, level)

            Reorder (item, quantity)

            Purchase (item, quantity, cost, customer name, date of purchase)

I.                   1. Display the number of customers for the shop on a particular day.

2. Write a query to display the item purchased by a given customer name.

3. Display the overall income for the shop on a given date.

II.                Write a view to display item and their reorder level and minimum level of that item. Update the reorder level of item X to 10 in the view.

III.             Design & implement an Inventory system and generate report.

11. Consider the following database consisting of the following tables:

            Hostel (hno, hname, type [boys/girls])

            Menu (hno, day, breakfast, lunch, dinner)

            Warden (wname, qual, hno)

            Student (sid, sname, gender, year, hno)

I.                   1. Display the total number of studnets in the particular hostel.

2. Change the breakfast of the hostel 5 on Thursday to ‘Noodles’.

3. Display the Wardens for each hostel with the qualification ‘B.Com’.

II   Write a PL/SQL function to display the students residing in a given hostel no

    III.      Design an application to display student details of a Hostel using JAVA.

12. Consider the following database consisting of the following tables:

            Department (dept id, dept name)

            Student (rollno, name, gender, mark1, mark2, mar3, total, average, dept id)

            Staff (staff id, name, designation, qualification, dept id)

            Tutor (rollno, staff id)

III.             1. Display the no.of. student under the department ‘cse’.

2. Display the student details who got average >85.

3. How many students are under the tutor ‘x’?

IV.             Write a PL/SQL cursor  to calculate and update total and average marks of a student.

   III.         Design an application to display student details using Visual Basic and generate report.