GOPALAN INTERNATIONAL SCHOOL
Worksheet – Practical – Database (MS Access)
Question 1:
DATABASE: Student Course Registration
RegID
StdName
Class
Section
Course
Instructor
Mode
CourseFee
FeePaid
DateOfReg
REG201 Aarav 8 A Music Ravi Offline 2000 Yes 12-06-2020
REG202 Diya 9 B Dance Uma Online 1500 No 20-08-2019
REG203 Kabir 10 A Music David Offline 2500 Yes 15-07-2018
REG204 Meera 11 C Art Anita Online 1800 Yes 10-09-2021
REG205 Rohan 12 B Music Ravi Offline 3000 No 05-05-2022
REG206 Anaya 9 A Dance Uma Online 2000 Yes 18-11-2020
Questions
1. Create a table in MS Access using the fields mentioned in the above table.
2. Set the field RegID as the Primary Key.
3. Alter the field Mode to create a Lookup List with the values:
o Online
o Offline
4. Fix the size of the following fields:
o StudentName → 30 characters
o Instructor → 25 characters
5. Create an Input Mask for the field StudentName so that the first letter is in uppercase
and the remaining letters are in lowercase.
6. Apply the following Validation Checks:
Field Name Validation Check Note
RegID Length check Equal to 6
StudentName Presence check Cannot be blank
Class Range check Between 8 and 12
Mode Limit to specific choices Online or Offline
DateOfReg Range check Must not be a future date
7. Create a Form to enter records into the table.
8. Create the following Query with the fields:
StudentName, Course, CourseFee, FeePaid, DateOfRegistration
Select only those records where:
o Course contains Music
o FeePaid is Yes
9. Generate a Report based on the above query.
Question 2:
DATABASE: City Library Membership
PaymentStatus
DateEnrolled
IssuingStaff
PhoneNum
AgeGroup
BookType
FullName
Member
SubAmt
LIB301 Arjun Adult Fiction Neha 9876543210 1200 Paid 15-03-2021
LIB302 Sneha Teen Science Rahul 9123456789 900 Not 10-07-2020
Paid
LIB303 Karan Adult History Neha 9012345678 1500 Paid 25-08-2019
LIB304 Meena Senior Fiction Anita 8899001122 1000 Paid 05-09-2022
LIB305 Ritu Teen Science Rahul 9012345678 900 Not 18-01-2021
Paid
LIB306 Aman Adult Fiction Neha 9345678901 1200 Paid 11-11-2020
Questions:
1. Create a table in MS Access using the fields given in the above table.
2. Set the field MemCode as the Primary Key.
7. Create an Input Mask for the field PhoneNum so that the format is (+91)99999-99999
8. Create an Input Mask for the field FullName so that the first letter is uppercase and the
remaining letters are lowercase.
9. Fix the size of the fields: FullName (30 characters) and IssuingStaff (25 characters).
10. Alter the field AgeGroup to create a Lookup List with the values:
• Child
• Teen
• Adult
• Senior
11. Apply appropriate validation checks for MemberCode, FullName, AgeGroup,
MembershipMode and DateEnrolled.
Field Name Validation Check Note
MemCode Length check Must be equal to 6
FullName Presence check Cannot be blank
PaymentMode Limit values Paid or Not Paid
DateEnrolled Range check Must not be a future date
11. Create a Form to enter membership details.
12. Create a Query to display FullName, BookType, SubAmount, PaymentStatus and
DateEnrolled where:
• BookType contains Fiction and
• PaymentStatus is Paid.
13. Generate a Report based on the above query.