Page 1 of 6

1. Create a Table as workers and the details are { S.No, Name, Designation, Branch }
Perform the following commands:
➢ Alter the table by adding a column Salary
➢ Alter the table by modifying the column Name
➢ Describe the table employee
➢ Copy the table employee as emp
➢ Truncate the table
➢ Delete the Second row from the table
➢ Drop the table

Aim /Commands Queries/Program Output & Result Viva-Voce Record Total

10 50 20 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

2. Create the following tables

student_details {register_no, student_name, DOB, address, city}
mark_details {register_no, mark1, mark2, mark3, total }
➢ Display only those rows whose total ranges between 250 and 300.
➢ Drop the table mark_details and Delete the row whose register_no=161.
➢ Display all details whose names begins with 'a'.

Aim /Commands Queries/Program Output & Result Viva-Voce Record Total

10 50 20 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

3. Consider the following relational schema for a Loan database application:

Customer {Customer_id, Customer_name, Age, phone_no}
Loan { Loan_id, Amount, Customer_id)}
➢ Include the constraint on Loan_id that it starts with letter ‘L’.
➢ Display the list of the customer_ids and total Loan amount taken.
➢ Display the Customer_id and Customer_name who have taken less than two loans.

Aim /Commands Queries/Program Output & Result Viva-Voce Record Total

10 50 20 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

 Page 2 of 6

4. Consider the following database for a Banking Enterprise.

Branch{branch_name, branch_city, assets) ACCOUNT(accno, branch_name, balance}
Depositor {customer_name, accno) CUSTOMER(customer_name, customer_street,
customer_city}
Loan {loan_number, branch_name, amount}
Borrower { customer_name, loan_number)}
➢ Create the above tables by properly specifying the primary keys and foreign keys and
enter at least five tuples for each relation.
➢ Find all the customers who have at least two accounts at the main branch.
➢ Find all the customers who have an account at all the branches located in a specific
city.
➢ Demonstrate how you delete all account tuples at every branch located in a specific
city.
Aim /Commands Queries/Program Output & Result Viva-Voce Record Total
10 50 20 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

5. Write a procedure called proc_udate is created and stored in the database. This procedure when
called in PL/SQL block updates the qty_on_hand in the product_master table it also updates the
order_status in the sales_order table.
➢ Prodct_master { product_no, description, profit_percentage, unit_measure,
qty_on_hand, recorder_level, sell_price, cost_price }
➢ Sales_order {order_no, order_date, client_no, dely_address, salesman_no, dely_type,
billed_yn, delay_date, order_status }

Aim /Commands Queries/Program Output & Result Viva-Voce Record Total

10 50 20 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

6. 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)
➢ Display the total number of girls and boys hostel in the college.
➢ Display the menu in the hostel ‘x’ on Tuesday.
➢ Display the number of wardens for each hostel.II.
Aim /Commands Queries/Program Output & Result Viva-Voce Record Total
10 50 20 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

 Page 3 of 6

7. Consider a view branch_cust defined as follows:

Create view branch_cust as
select branch_name, customer_name
from depositor, account
where depositor.account_number = account.account_number.
suppose that a view is materialized; that is the view is computed and stored. Write a trigger to
maintain the view, that is, to keep it up-to-date on insertions to and deletions from depositor or
account. Do not bother about updates.
Aim /Commands Queries/Program Output & Result Viva-Voce Record Total
10 50 20 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

8. Create an Employee table with an attributes { empid, empname, department designation,
Gross_salary . Using cursor, select the five highest paid employees from the Employee table.

Aim /Commands Queries/Program Output & Result Viva-Voce Record Total

10 50 20 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

9. The accounts table is modified according to instructions stored in the action table. Each row in
the action table contains an account number, an action to be taken (I, U, or D for insert, update,
or delete), an amount by which to update the account, and a time tag used to sequence the
transactions.
On an insert, if the account already exists, an update is done instead. On an update, if the account
does not exist, it is created by an insert. On a delete, if the row does not exist, no action is taken.

Aim /Commands Queries/Program Output & Result Viva-Voce Record Total

10 50 20 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

10. Suppose you have a table named data_table that holds data collected from laboratory
experiments, and you want to analyze the data from experiment 1.Compute the results and store
them in a database table named temp.
Aim /Commands Queries/Program Output & Result Viva-Voce Record Total
10 50 20 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

 Page 4 of 6

11. Write a program in PL/SQL to create a cursor displays the name and salary of each employee in
the Employees table whose salary is less than that specified by a passed-in parameter value.

Aim /Commands Queries/Program Output & Result Viva-Voce Record Total

10 50 20 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

12. Given Student_Report Database, in which student marks assessment is recorded. In such
schema, create a trigger so that the total and average of specified marks is automatically inserted
whenever a record is insert.

Aim /Commands Queries/Program Output & Result Viva-Voce Record Total

10 50 20 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

13. Write a PL/SQL function to calculate the income tax for the given employee:
Conditions:
If pay  2,50,000, no tax is charged.
If pay is  2,50,001 and  5,00,000, 5% of pay is charged as tax.
If pay is  5,00,001 and  10,00,000, 20% of pay is charged as tax.
If pay is  10,00,001, 30% of pay is charged as tax. .

Aim /Commands Queries/Program Output & Result Viva-Voce Record Total

10 50 20 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

14. Design a database for an Airline. The database must keep track of customers and their
reservations, flights and their status, seat assignments on individual flights, and the schedule and
routing of future flights.
Your design should include an E-R diagram, a set of relational schemas, and a list of constraints,
including primary-key and foreign-key constraints.

Aim /Commands Queries/Program Output & Result Viva-Voce Record Total

10 50 20 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

 Page 5 of 6

15. Consider a database used to record the marks that students get in different exams of different
courses offerings.
➢ Construct an ER diagram that models exams as entites, and uses a ternary relationship,
for the database.
➢ Construct and alternative ER diagram that uses only a binary relationship between
students and course_offerings. Make sure that only one relationship exists between a
particular student and course offering pair, yet you can represent the marks that a student
gets in different exams of a course offering.
Aim /Commands Queries/Program Output & Result Viva-Voce Record Total
10 50 20 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

16. Consider the employee relational database, Where the primary keys are underlined.
Employee ( employee_name, street, city)
Works ( employee_name, company_name, salary)
Company ( comapny_name, city)
Manages ( employee_name, manager_name)
➢ 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.
➢ Give all managers of first Bank Corporation a 10 percent raise unless the salary becomes
greater than $100,000; in such cases, give only a 3 percent raise.
Aim /Commands Queries/Program Output & Result Viva-Voce Record Total
10 50 20 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

17. Consider the following relational schema:
Employee( empno, name, office, age )
Books ( isbn, title, authors, publisher )
Loan ( empno, isbn, date )
Write the following queries in SQL:
➢ Print the names of employees who have borrowed any books published by McGraw-Hill.
➢ Print the names of employees who have borrowed all books published by McGraw-Hill.
➢ For each publishers, print the names of employees who have borrowed more than five books
of that publisher.

Aim /Commands Queries/Program Output & Result Viva-Voce Record Total

10 50 20 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

 Page 6 of 6

18. (a). Create a table as book (sl.no, book_name, author_name,price, edition,publisher_name ).

Perform the following operations:
➢ Insert minimum ten tuples in book table
➢ Commit the table book
➢ Create a save point for the table book as B
➢ Rollback the table book after inserting 4 & 5 row
➢ Define Grant & Revoke
(b). Design an application for Library Management System and Show the database
connectivity details using suitable Front End Tool.

Aim /Commands Queries/Program Output & Result Viva-Voce Record Total

10 50 20 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

19. An Electricity Board charges the following rates to domestic users to discourage large
consumption of energy:
For the first 1 - 200 units→ { 1 - 100 units = Rs. 1.00, 101 - 200 units = Rs.1.50 }
From 201 - 500 units→{ 1 - 200 units = Rs. 2.00 and 201 - 500 units = Rs. 3.00 }
Above 500 units→{ 1 - 200 = Rs. 3.50, 201- 500 unit = Rs. 4.60 &above 500 unit Rs.6.60}
Write a PL/SQL program to read the connection_id of users and number of units consumed,
display the charges with names.

Aim /Commands Queries/Program Output & Result Viva-Voce Record Total

10 50 20 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER

20. Create a table salesman{ salesman_id, name, city, commission } and customer{customer_id,
name, city, grade, slaesman_id}. Write the following query:
➢ Create a view for those salesmen belongs to the city New York
➢ Create a view for all salesmen with columns salesman_id, name, and city
➢ Find the salesmen of the city New York who achieved the commission more than 13%.
➢ Create a view to getting a count of how many customers we have at each level of a grade.
Aim /Commands Queries/Program Output & Result Viva-Voce Record Total
10 50 20 10 10 100

INTERNAL EXAMINER EXTERNAL EXAMINER