Programming with C lab COMPUTER SCIENCE 3RD SEMESTER MODEL QUESTION PAPER

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

Name

Address

Phone

 

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

Card_No Name Address Phone

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

 

Account_no Name Address Phone

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).