Database activity for the day 2019/10/14

Example 01

Create following table in Database name “ABC School”

Student (Table Name)

AdminNo	Name	Term Fees	Fees Paid Date	Paid or not	Math	Science	English
1456	Kavidu Silva	$250.00	2016.11.15	Yes	78	48	67
1457	Amila Jayani	$150.00	2016.10.12	Yes	56	56	72
1458	Subashini Perera	$250.00		No	96	95	79
1459	Vinosh Kumara	$200.00	2016.11.26	yes	62	45	86
1460	Asitha Manoj	$100.00		No	45	53	47

01)  Design above table by giving suitable data type

Field Name	Data Type

02)  Create table by inserting above data

03)  Design Query to find answer for following question

a.      Obtain data for  AdminNo, Name, Math, Science and English marks and name the query as “QueryMark”

Field:
Table:
Sort:
Show:
Criteria:

b.       Obtain AdminNo, Name, Fee and Paid Or not and the student who do not paid term fees (query     name is “QueryNOTPaid” )

Field:
Table:
Sort:
Show:
Criteria:

c.       Obtain AdminNo and Name of the student with the fees  who paid fees 200/- or more than 200/-

(query name is “Query200” )

Field:
Table:
Sort:
Show:
Criteria:

a.      Obtain AdminNo, Name and the Math marks who had more than 75 (query name is “QueryMath75” )

Field:
Table:
Sort:
Show:
Criteria:

b.      Obtain AdminNo, Name and the subject marks who had more than 75 for all subject (query name is “QueryAllMark” )  

Field:
Table:
Sort:
Show:
Criteria:

c.       Calculate total marks for all subject by using relevant equation and create query to indicate AdminNo, Name, All Subject marks and Total  (query name is “QueryTotal” )

Field:
Table:
Sort:
Show:
Criteria:

d.      Create Forms and insert one record to the table using that form

e.      Design Report to present Table and the queries and save those by giving relevant name