### Programming with C lab COMPUTER SCIENCE 3RD SEMESTER MODEL QUESTION PAPER - Diploma Engineering

MODEL QUESTION BANK

1. a) Create table Employee and Dependent as per the following schema: Identify the Primary and referential constraints on the table. Specify suitable constraint on the salary column such that 12000 < Salary < 120000.
i) Employee Table

 Fname Lname ssn Bdate Address Sex Salary Super ssn Dno

i)     Dependent Table

 Essn Dependent_name Sex Bdate

b) Insert records into the above tables. c). Queries to be executed
Add another column Relationship to dependent Table
Retrieve all female dependents of the employees who have joined service after 31-01- 2010
Retrieve the no. Of employees and total salary drawn by the employees belonging to department no. 10.
d) Create a GUI to show any one of the table contents(include data grid view and perform operations such as add, delete, modify etc)

2. a) Create table BOOK ,BOOK_AUTHORS and PUBLISHER with following Schema. Identify the Primary and referential constraints on the tables. Specify default constraint on gender column of BOOK _AUTHOR table.

i)               BOOK

 Book_id Title Publisher_name

ii)             BOOK_AUTHOR

 Book_id Author_name Gender

iii)           PUBLISHER

b) Insert records into the above tables.
c) Queries to be executed
Retrieve the book details of all the books authored by “Sharma”.
Delete the record for the publisher by name “sapna”.
For each publisher retrieve the total number of book titles published.
d) Create a GUI to show any one of the table contents(include data grid view and perform operations such as add, delete, modify etc).

3. a) Create table EMPLOYEE,PROJECT and WORKS_ON as per the following schema: Identify the Primary and referential constraints on the table.
i) Employee Table

Fname Lname Ssn Bdate Address Sex Salary Super
ssn Dno

ii) Project table
P_name Pno P_location
iii) Works_On
Essn Pno Hours

b) Insert records into the above tables.
c) Queries to be executed
Retrieve employee name, address and salary with all employee names first letter in capital.
For each employee retrieve the total projects and total no. of hours he/she works on.
Illustrate the use of Save point, Rollback and Commit transaction commands.
d) Create a GUI to show any one of the table contents (include data grid view and perform operations such as add, delete, modify etc).

4. a). Create table BOOK,BOOK_BORROWED and BOOK_BORROWER as per the following schema: Identify the Primary and referential constraints on the table.
i) Book

Book_id Title Publisher_name

ii) Book_Borrowed
Book_id Card_No Date_out Due_Date

iii) Book_Borrower

b) Insert records into the above tables.
c) Queries to be executed
Retrieve the names of people who have borrowed “Computer Network” book.
Retrieve the number of days permitted to borrow “Computer Organization” by “Cherry”.
Illustrate the use of any four numeric functions.
d) Create a GUI to show any one of the table contents (include data grid view and perform operations such as add, delete, modify etc).

5. a) Create table CUSTOMER , ACCOUNT, LOAN as per the following schema: Identify the Primary and referential constraints on the table.
i) Customer

ii) Account

Account_no. Transaction_type Amount Balance
iv) Loan

Loan _no Account_no Amount Loan_type Interest_rate

b) Insert records into the above tables.
c) Queries to be executed
Retrieve all customers who have availed the loan amount of more than 5 lakhs.
Create a view with the following attributes: Customer name, bank balance and loan amount.
Illustrate the use of Grant and Revoke commands.
d) Create a GUI to show any one of the table contents (include data grid view and perform operations such as add, delete, modify etc).

6. a) Create table ART_OBJECT, PAINTING and STATUE as per the following schema: Identify the Primary and referential constraints on the table.
i) Art_object
Id_no Artist_ Name Year_ Created Title Art_ type Description
Art type can either Painting or Statue

ii) PAINTING
Paint_ type Base_ Material Style Price
Paint type can be oil or water colour or organic colour Base material can be paper, canvas or wood
Style can be Modern or Abstract
iv) Statue
Material_type Height Weight Style Price
Material tyoe can be stone, wood, glass Style can be Modern or Abstract
b) Insert records into the above tables.
c) Queries to be executed
Retrieve the details of the costliest painting using nested queries.
Retrieve the names of the artist who have worked on Abstract style
Delete all the records pertaining to the artist by name “Sahithya”.

d) Create a GUI to show any one of the table contents (include data grid view and perform operations such as add, delete, modify etc).