User:Meera Mehta/Q-BANK

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  This is to certify that we have charged and received Rs.  for a consultation on . 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:

•	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  This is to certify that you have been granted basic pay of Rs.  for the month of January 2005. The total salary on this basic pay works out to Rs.

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  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  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  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  This is to certify that we have charged and received Rs.  for a consultation on . 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:

•	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  This is to certify that you have been granted basic pay of Rs.  for the month of January 2005. The total salary on this basic pay works out to Rs.

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.