User:Meera Mehta/Q-BANK

From WikiEducator
Jump to: navigation, search

Set No: 1 (A) A clinic issues certificate of having charged and received the fee with date and diagnoses to its patients at the end of a month as per following format and specification It is desired that this certificate is issued for the month of January 2006: _________________ 

      ________________

Sub: Certificate of Diagnosis 

     Dear  <here give Sir or madam>
                     This is to certify that we have charged and received Rs. <Amount of Fee> for a consultation on <Date of consultation>. The diagnosis reveal that you are suffering from  < as recorded in consultation field>. You may keep in touch with the attending physician in future.

Thanking you,

(Executive Officer) Note: Database tables given in Q. No: 4 need be used for getting necessary data/information for this letter. (D) A health center provides health services to outdoor patients visiting the clinic. The database of this health centre consists of two tables that have been described below: (7) Patients Field Data Type Description Code Text (Width:4) Identification of Patient (Primary Key) FName Text (Width:10) First Name of Patient MName Text (Width:10) Middle Name of Patient LName Text (Width:10) Last Name of Patient Street Text (Width:6) Address Street Block Text (Width:6) Address Block Colony Text (Width:10) Address Colony City Text (Width:10) Address City Sex Text (Width: 1) M=Male, F=Female BirthDate Date/Time Date of Birth Consultation Field Data Type Description Code Text (Width:4) Identity of Patient (Foreign key) Dated Date/Time Date of Consultation Amount Number/Integer Fee Received Diagnosis Text (Width: 50) Diagnosis (Identity of Patient and Date of consultation together constitute the composite key for consultation) Required: • Create database named as Clinic for the health center. • Establish and implement referential integrity between fields of tables • Incorporate a constraint to specify that the consultation fee is not below Rs.200 per patient per day. • Write and Execute SQL that retrieves date of consultation and the consultation fee paid by a particular patient • Prepare a Report that shows the patient details and the fees paid by them as per format given below:


Statement of Total Fees paid by patient during the year. Code Name of Patient Date of Birth Fee Paid


(E) A health center provides health services to outdoor patients visiting the clinic. The database consists of two tables that have been described below: (7) Patients Description Identification of Patient (Primary Key) First Name of Patient Middle Name of Patient Last Name of Patient Date of Birth

Consultation Description Identity of Patient (Foreign key) Date of Consultation Fee Received Diagnosis (Identity of Patient and Date of consultation together constitute the composite key for consultation)

Required: • Create database named as Clinic for the health center. • Establish and implement referential integrity between fields of tables • Incorporate a constraint to specify that the consultation fee is not below Rs.200 per patient per day. • Write and Execute SQL that retrieves date of consultation and the consultation fee paid by a particular patient


Paper Set No. 2 1. Ms/ Gamma study center is engaged in conducting coaching classes for various courses. There are a number of students pursuing different courses at this coaching center. A database named as Studies is maintained to store various data items using the following three data tables: (7) Students Fields Data Type Description RollNo Text (Width:4) Identity of Student (Primary Key) Name Text (Width 30) Name of Students CCode Number/byte Identity of Course (Foreign Key) Courses Fields Data types Description Code Number/Byte Identity of Course (Primary Key) Name Text(Width:20) Name of Course Amount Number/Long Course fees


Required: • Create a database that contains above tables with referential integrity constraints established. • Enter records of three courses and five students, each of whom belongs to one of these courses • While entering the data in course table, it must be ensured that the course fee does not exceed Rs.25000.

• Prepare a Form that is used for entering the data pertaining to students in appropriate table in the following format:


Student Records 

 Roll No:                                                                                                      
 Name of Student                                                                       
Course of Study: <use list control>



• Create and Execute SQL statement to retrieve and display the student details: Identity of student, Name of student, Name of course being pursued, Amount of course fee


Paper Set No. 9

(B) Ms/ Gamma study center is engaged in conducting coaching classes for various courses. There are a number of students pursuing different courses at this coaching center. A database named as Studies is maintained to store various data items using the following three data tables: (5) Students Description Identity of Student (Primary Key) Name of Students Identity of Course (Foreign Key) Courses Description Identity of Course (Primary Key) Name of Course Course fees

Required: • Create a database that contains above tables with referential integrity constraints established. • Enter records of two courses and three students, each of whom belongs to one of these courses • While entering the data in course table, it must be ensured that the course fee does not exceed Rs.10000. • Prepare a Form that is used for entering the data pertaining to students in appropriate table.


Set No: 3 (C) M/s Alpha Ltd. wants to issue a certificate of pay to its employees as per the following format: (5) 

     ________________
     ________________

Sub: Certificate of Pay 

     Dear  <here give Sir or madam>
              
               This is to certify that you have been granted basic pay of Rs.  <Basic pay> for the month of January 2005. The total salary on this basic pay works out to Rs. <computed value>

Thanking you,


(Executive Officer)

Note: Database tables given in Q. No: 4 need be used for getting necessary data/information for this letter. The total salary consists of basic pay and D.A @ 40% of basic pay.

(D) M/s Alpha Ltd. maintains database of its employees the details of three tables of which is given below: (7) Employees Fields Data Types Description Code Text (Width:4) Identity of Employee (Primary Key) Name Text (Width:30) Name of Employee Sex Text (Width: 1) M=Male, F=Female Street Text (Width:15) Address Street Block Text (Width:10) Address Block Colony Text (Width:20) Address Colony City Text (Width:15) Address City BDate Date/Time Date of Birth PayDetails Fields Data Types Description Code Text (Width:4) Identity of Employee (Foreign Key) Basic Number/Long Basic Pay for Month Month Number/Byte Numeric value of month (Foreign Key) Identity of Employee and Numeric value of month together constitute the composite key for PayDetails table Months Fields Data Types Description Month Number/Byte Numeric value of Month (Primary Key) Name Text (Width:15) Name of Month

Required: • Create a database named as Payroll that contains above tables with referential integrity constraints. Choose appropriate field names and data types. • Ensure that the date of birth of employee is not after 1-Apr-1986. Give a suitable message if this condition/constraint is violated. • Enter six records for months, three records of employees with their pay details for two different months. • Set lookup property to ensure that the records entered in paydetails table are entered using list box. • Create and execute SQL statement that retrieves Identification of Employee, their names, the date of birth and the basic pay for a particular month. • Design and Create a Report that shows employee details in format given below: Pay Details of Employees for the month of April Code Name of Employee Date of Birth Basic Pay



Paper Set No: 4

(E) You are given the following data tables that implements an accounting design: (7) Accounts Field Data Type Comments Code Text(Width: 6) Identity of account Primary Key) Name Text Name of Account Vouchers Field Data Type Comments vNo AutoNumber/Long Identity of Voucher vDate Date/Time Date of Voucher Debit Text Account Debited in transaction (F/K) Credit Text Account Credited in transaction (F/K) Amount Number/Double Amount of transaction Narration Text(Width:60) Narration of Transaction There exists referential integrity between [code and debit] as well as [code and credit] fields. Required: • Create these tables in a database named Fasys; Enter five accounts and five transactions in voucher table using MS Access • Establish and implement referential integrity between relevant fields of tables • Incorporate a constraint to specify that the transactions pertain to the financial year 2005-06 • Design and execute a query to retrieve Names of Accounts and the amounts by which they have been debited. • Generate a Form that is capable of entering the transaction data in Vouchers table.


2. M/s Alpha Ltd. maintains database of its employees the details of three tables of which is given below: (7) Employees Description Identity of Employee (Primary Key) Name of Employee Date of Birth PayDetails Description Identity of Employee (Foreign Key) Basic Pay for Month Numeric value of month (Foreign Key) Identity of Employee and Numeric value of month together constitute the composite key for PayDetails table Months Description Numeric value of Month (Primary Key) Name of Month

Required: • Create a database named as Payroll that contains above tables with referential integrity constraints. • Ensure that the date of birth of employee is not after 1-Apr-1986. Give a suitable message if this condition/constraint is violated. • Enter six records for months, three records of employees with their pay details for two different months. • Set lookup property to ensure that the records entered in paydetails table are entered using list box. • Create and execute SQL statement that retries Identification of Employee, their names, the date of birth and the basic pay for a particular month

Paper Set No: 14 (F) A clinic operates a laboratory that conducts various types of pathological tests on patients visiting the clinic. The database consists of three tables that have been described below: (7) Patients Description Identification of Patient (Primary Key) First Name of Patient Middle Name of Patient Last Name of Patient Date of Birth

TestTypes Description Identity of Test (Primary Key) Name of Test Test Charges TestResults Description Identity of Patient (Foreign key) Identity of Test (Foreign key) Date of Test Result of Test as comments (Identity of Patient, Identity of Test and Date of Test together constitute the composite key for Test results)

Required: • Create database named as PathLab for a clinic that has a pathological laboratory, • Establish and implement referential integrity between fields of tables • Incorporate a constraint to specify that laboratory does not charge more than Rs.100 for any test that conducted in the laboratory • Generate a Form that is capable of entering data of patients test results in Test Results table


Paper Set No: 5 (F) A banking company wishes to dispatch a letter to many of its customers whose addresses and other details are given in a database given in Q.No:4. The format of the letter is as specified below: (5)

________________ ________________ ________________ 


Dear  <here give Sir or madam>
              
               We are to inform you that you have a confirmed balance of Rs. ____________ in you savings Bank Account No: _____________, with this bank. Further a sum of  Rs. _______ has been credited in your account as interest for the financial year 2005-06 (up to Dec 2005).

Thanking you,


(Branch Manager)

You are required to use appropriate technique in MS word to achieve this task

(G) A banking company maintains database of its clients and the amount due from the Database: Banking that consists of two tables: Customers and Transactions (7) 

      Name of Table: Customers

Field Data Type Comments AccNo Number/Long Savings Bank Account Number of customer Sex Text (width: 1) M=Male, F=Female fName Text (width: 10) First name MName Text (width: 10) Middle Name LName Text (width: 10) Last Name HouseNo Text (width: 5) House Number of Client Block Text (width: 4) Block Number of Client Locality Text (width: 30) Locality of client State Text (width: 30) The state in which house is situated Interest Number/double Amount of interest credited. Name of Table: Transactions Field Data Type Comments AccNo Number/Long Savings Bank Account Number of customer Dated Date/time Date of Transaction Amount Number/double Amount to the credit or debit of account AccNo and Dated together constitute the primary key of transition table. Deposits into the account are indicated by positive values of amount and withdrawals by negative values. Required: • Create database named as Banking for the banking company that has a number of customers. • Establish and implement referential integrity between fields of tables and Enter three records of customers and 12 records of Transactions • Incorporate a constraint to specify that the constraint that the transactions pertain to the financial year 2005-06. • Generate a Form that is capable of entering data in Customers table. The form must include appropriate operation as well as navigation command buttons. • Create and Execute a Query to find the total amount by which each customers account has been debited.


Paper Set No. 7 (H) National Institute of Business studies wants to inform its students the attendance status in respect of PG Diploma course as per the following format: (5) 

     ________________
     ________________

Sub: Attendance Status 

     Dear  <here give Sir or madam>
              
               This is to inform you that you have attended __________Lectures out ________.

Your Total Attendance works out to be _______percent which is ________ % Short.. You are advised to be regular in studies so that you are allowed to appear in the ensuing final Exams.

Yours Sincerely,


(Adm. Officer)

Note: Database tables given in Q. No: 4 need be used for getting necessary data/information for this letter. The total attendance requirement is 67%. (I) National Institute of Business Studies is engaged in conducting classes for PG Diploma in Business Studies.. There are a number of students, who have opted for various papers pursuing different courses at this coaching center. A database named as Studies is maintained to store various data items using the following three data tables: (7) Students Fields Data Type Description RollNo Text (Width:4) Identity of Student (Primary Key) SName Text (Width 30) Name of Students Sex Text (Width 1) M=Male and F=Female Papers Fields Data types Description Code Number/Byte Identity of Course (Primary Key) Name Text(Width:20) Name of Paper Attendance Fields Data types Description Code Number/Byte Identity of Course (Primary Key) RollNo Text (Width 4) Identity of Student (Primary Key) LectAtt Number/Integer Lectures Attended LectDel Number/Integer Lectures Delivered Required: • Create a database that contains above tables with referential integrity constraints established. Enter records of Four Papers, Five Students and five Attendance • While entering the data in course table, it must be ensured that the Lectures delivered do not exceed the limit of 75 and when this constraint is violated, an appropriate message should be displayed. • Design and create a report pertaining to students in the following format:

Code Name Papers Lectures Attended Delivered


• Create and Execute SQL statement to retrieve and display the student details: Identity of student, Name of student, Name of paper, Lectures Attended, Lectures Delivered

Paper Set No: 8 (J) You are given the following data tables that implement trading transactions of a whole-seller who buys goods from Manufacturers and supplies goods its retailers: (7) Items Field Data Type Comments Code Text (Width: 4) Identity of Item(Primary Key) Name Text Name of Account OpnStk Number/Integer Opening Stock Party Field Data Type Comments Code Text (Width: 4) Identity Party( Primary Key) Name Text Name of Party: Customer or Supplier Transactions Field Data Type Comments InvNo AutoNumber/Long Invoice Number Dated Date/Time Date of Invoice ICode Text(Width: 4) Item code (F/K) Units Number/Integer Units of party item of goods Rate Number/Integer Rate at which goods are purchased or sold PCode Text (Width:4) Party Code (F/K) Type Text(Width:1) P=Purchase, S=Sales Create relationships among these tables to implement referential integrity with a stipulation to change primary key values of any table and yet ensure data integrity. Required: • Create these tables in a database named Stores; Enter five Items, Three Parties and five trading transactions in Transactions table using MS Access. • Establish and implement referential integrity between relevant fields of tables. No Item is transacted at a rate below Rs.750 • Incorporate a constraint to specify that the transactions pertain to the financial year 2005-06 • Design and execute a query to retrieve Names of Suppliers and the total amount of goods supplied by them • Design a report to show the Amount of goods purchased against each. Paper Set No: 9 (K) A banking company wishes to dispatch a letter to many of its customers whose addresses and other details are given in a database given in Q.No:4. The format of the letter is as specified below. It begins with Name and Address. (5)

________________ ________________ 


Dear  <here give Sir or madam>
              
               We are to inform you that you have a confirmed balance of Rs. ____________ in you savings Bank Account No: _____________, with this bank. Further a sum of  Rs. _______ has been credited in your account as interest for the financial year 2005-06 (up to Dec 2005).

Thanking you,


(Branch Manager)

You are required to use appropriate technique in MS word to achieve this task • (L) A banking company maintains database of its clients and the amount due from the Database: Banking that consists of two tables: Customers and Transactions (7) 

      Name of Table: Customers

Field Data Type Comments AccNo Number/Long Savings Bank Account Number of customer Sex Text (width: 1) M=Male, F=Female fName Text (width: 10) First name MName Text (width: 10) Middle Name LName Text (width: 10) Last Name HouseNo Text (width: 5) House Number of Client Block Text (width: 4) Block Number of Client Locality Text (width: 30) Locality of client State Text (width: 30) The state in which house is situated Interest Number/double Amount of interest credited. Name of Table: Transactions Field Data Type Comments AccNo Number/Long Savings Bank Account Number of customer Dated Date/time Date of Transaction Amount Number/double Amount to the credit or debit of account AccNo and Dated together constitute the primary key of transition table. Deposits into the account are indicated by positive values of amount and withdrawals by negative values. Required: • Create database named as Banking for the banking company that has a number of customers. • Establish and implement referential integrity between fields of tables and Enter three records of customers and 12 records of Transactions • Incorporate a constraint to specify that the constraint that the transactions pertain to the financial year 2005-06. • Generate a Form that is capable of entering data in Customers table. The form must include appropriate operation as well as navigation command buttons. • Create and Execute a Query to find the total amount by which each customers account has been debited.

Set No: 10

(M) You are given the following data tables that implements an accounting design. In each voucher an account is debited and another account is credited for a particular amount of transaction: (7) Accounts Field Data Type Comments Code Text(Width: 6) Identity of account Primary Key) Name Text Name of Account Vouchers Field Data Type Comments vNo AutoNumber/Long Identity of Voucher vDate Date/Time Date of Voucher Debit Text Account Debited in transaction (F/K) Credit Text Account Credited in transaction (F/K) Amount Number/Double Amount of transaction Narration Text(Width:60) Narration of Transaction There exists referential integrity between [code and debit] as well as [code and credit] fields. Required: • Create these tables in a database named Accounting; Enter five accounts and five transactions in voucher table using MS Access • Establish and implement referential integrity between relevant fields of tables, ensuring that a delete of Account Record leads to deletion of all Corresponding transaction records • Incorporate a constraint to specify that the transactions pertain to the financial year 2005-06 • Design and execute a query to retrieve Names of Accounts and the amounts by which they have been debited. • Prepare a statement of Accounts with Debit Balances as given below: Code Name of Account Amount



Set No: 11 (N) A clinic issues certificate of having charged and received the fee with date and diagnoses to its patients at the end of a month as per following format and specification It is desired that this certificate is issued for the month of January 2006: _________________ ________________ 

     ________________

Sub: Certificate of Diagnosis 

     Dear  <here give Sir or madam>
                     This is to certify that we have charged and received Rs. <Amount of Fee> for a consultation on <Date of consultation>. The diagnosis reveal that you are suffering from  < as recorded in consultation field>. You may keep in touch with the attending physician in future.

Thanking you,


(Executive Officer) Note: Database tables given in Q. No: 4 need be used for getting necessary data/information for this letter. (D) A health center provides health services to outdoor patients visiting the clinic. The database of this health centre consists of two tables that have been described below: (7) Patients Field Data Type Description Code Text (Width:4) Identification of Patient (Primary Key) FName Text (Width:10) First Name of Patient MName Text (Width:10) Middle Name of Patient LName Text (Width:10) Last Name of Patient Street Text (Width:6) Address Street Block Text (Width:6) Address Block Colony Text (Width:10) Address Colony City Text (Width:10) Address City Sex Text (Width: 1) M=Male, F=Female BirthDate Date/Time Date of Birth Consultation Field Data Type Description Code Text (Width:4) Identity of Patient (Foreign key) Dated Date/Time Date of Consultation Amount Number/Integer Fee Received Diagnosis Text (Width: 50) Diagnosis (Identity of Patient and Date of consultation together constitute the composite key for consultation) Required: • Create database named as Clinic for the health center. • Establish and implement referential integrity between fields of tables • Incorporate a constraint to specify that the consultation fee is not below Rs.200 per patient per day. • Write and Execute SQL that retrieves date of consultation and the consultation fee paid by a particular patient • Prepare a Report that shows the patient details and the fees paid by them as per format given below: Statement of Total Fees paid by patient during the year. Code Name of Patient Date of Birth Fee Paid


Paper Set No. 12 2. Ms/ Gamma study center is engaged in conducting coaching classes for various courses. There are a number of students pursuing different courses at this coaching center. A database named as Studies is maintained to store various data items using the following three data tables: (7) Students Fields Data Type Description RollNo Text (Width:4) Identity of Student (Primary Key) Name Text (Width 30) Name of Students CCode Number/byte Identity of Course (Foreign Key) Courses Fields Data types Description Code Number/Byte Identity of Course (Primary Key) Name Text(Width:20) Name of Course Amount Number/Long Course fees

Required: • Create a database that contains above tables with referential integrity constraints established. • Enter records of three courses and five students, each of whom belongs to one of these courses • While entering the data in course table, it must be ensured that the course fee does not exceed Rs.25000. • Prepare a Form that is used for entering the data pertaining to students in appropriate table in the following format:


Student Records 

 Roll No:                                                                                                      
 Name of Student                                                                       
Course of Study: <use list control>


• Create and Execute SQL statement to retrieve and display the student details: Identity of student, Name of student, Name of course being pursued, Amount of course fee

Set No: 13

(O) M/s Alpha Ltd. wants to issue a certificate of pay to its employees as per the following format: (5) 

     ________________
     ________________

Sub: Certificate of Pay 

     Dear  <here give Sir or madam>
              
               This is to certify that you have been granted basic pay of Rs.  <Basic pay> for the month of January 2005. The total salary on this basic pay works out to Rs. <computed value>

Thanking you,


(Executive Officer)

Note: Database tables given in Q. No: 4 need be used for getting necessary data/information for this letter. The total salary consists of basic pay and D.A @ 40% of basic pay.

(P) M/s Alpha Ltd. maintains database of its employees the details of three tables of which is given below: (7) Employees Fields Data Types Description Code Text (Width:4) Identity of Employee (Primary Key) Name Text (Width:30) Name of Employee Sex Text (Width: 1) M=Male, F=Female Street Text (Width:15) Address Street Block Text (Width:10) Address Block Colony Text (Width:20) Address Colony City Text (Width:15) Address City BDate Date/Time Date of Birth PayDetails Fields Data Types Description Code Text (Width:4) Identity of Employee (Foreign Key) Basic Number/Long Basic Pay for Month Month Number/Byte Numeric value of month (Foreign Key) Identity of Employee and Numeric value of month together constitute the composite key for PayDetails table Months Fields Data Types Description Month Number/Byte Numeric value of Month (Primary Key) Name Text (Width:15) Name of Month

Required: • Create a database named as Payroll that contains above tables with referential integrity constraints. Choose appropriate field names and data types. • Ensure that the date of birth of employee is not after 1-Apr-1986. Give a suitable message if this condition/constraint is violated. • Enter six records for months, three records of employees with their pay details for two different months. • Set lookup property to ensure that the records entered in paydetails table are entered using list box. • Create and execute SQL statement that retrieves Identification of Employee, their names, the date of birth and the basic pay for a particular month. • Design and Create a Report that shows employee details in format given below: Pay Details of Employees for the month of April Code Name of Employee Date of Birth Basic Pay



Paper Set No: 14 (Q) You are given the following data tables that implements an accounting design: (7) Accounts Field Data Type Comments Code Text(Width: 6) Identity of account Primary Key) Name Text Name of Account Vouchers Field Data Type Comments vNo AutoNumber/Long Identity of Voucher vDate Date/Time Date of Voucher Debit Text Account Debited in transaction (F/K) Credit Text Account Credited in transaction (F/K) Amount Number/Double Amount of transaction Narration Text(Width:60) Narration of Transaction There exists referential integrity between [code and debit] as well as [code and credit] fields. Required: • Create these tables in a database named Fasys; Enter five accounts and five transactions in voucher table using MS Access • Establish and implement referential integrity between relevant fields of tables • Incorporate a constraint to specify that the transactions pertain to the financial year 2005-06 • Design and execute a query to retrieve Names of Accounts and the amounts by which they have been debited. • Generate a Form that is capable of entering the transaction data in Vouchers table.

Retrieved from "https://wikieducator.org/index.php?title=User:Meera_Mehta/Q-BANK&oldid=337494"