Answered

Task: Database Creation and Analysis

a. Use an appropriate database application to create a database and name it SCORE in the folder created on the desktop.

b. Create the structure of the table below:

[tex]\[
\begin{tabular}{|l|l|}
\hline Student Name & Theory \\
\hline Johnson Duke & 84 \\
\hline Elvis Okora & 73 \\
\hline Helen Obima & 51 \\
\hline Alhassan Armin & 47 \\
\hline Peace Hawa & 66 \\
\hline
\end{tabular}
\][/tex]

[tex]\[
\begin{tabular}{|l|l|}
\hline LabWork & FieldWork \\
\hline 72 & 81 \\
\hline 81 & 65 \\
\hline 65 & 59 \\
\hline 59 & 71 \\
\hline 71 & \\
\hline
\end{tabular}
\][/tex]

c. Save the table as TBLSCORE.

d. Create a query to insert another field after FieldWork and label it Average. Compute the average for each student using the formula:

[tex]\[
\text{Average} = \frac{\text{Theory} + \text{LabWork} + \text{FieldWork}}{3}
\][/tex]

e. Show all records from TBLSCORE except students with an average below 60.

f. Save the query as QRYANALYSIS.



Answer :

GinnyAnswer
Sure! Let's walk through the steps systematically to answer the question:

### Step a: Create the database

You need to use a database application to create a database named `SCORE`.

1. Open the Database Application: Open your preferred database management application (e.g., MySQL, SQLite, Microsoft Access, or any other).
2. Create Database: Create a new database named `SCORE` in the folder on the desktop.

### Step b: Create the structure of the table TBLSCORE

You need to create a table named `TBLSCORE` with the following columns:
- `StudentName` (text)
- `Theory` (integer)
- `LabWork` (integer)
- `FieldWork` (integer)

The table should look like this:

| Student Name | Theory | LabWork | FieldWork |
|---------------|--------|---------|-----------|
| Johnson Duke | 84 | 72 | 81 |
| Elvis Okora | 73 | 81 | 65 |
| Helen Obima | 51 | 65 | 59 |
| Alhassan Armin| 47 | 59 | 71 |
| Peace Hawa | 66 | 71 | 71 |

#### Steps to create the table and insert data:

1. Open/Create Table: In the database application, create a new table named `TBLSCORE`.
2. Define Columns: Define the columns as listed above.
3. Insert Data: Enter the data into the table as shown above.

### Step c: Save the table as TBLSCORE

Ensure that the table is saved correctly with the name `TBLSCORE` in your database.

### Step d: Add a new field "Average" and compute the average for each student

1. Add New Field: Add a new column named `Average` to the `TBLSCORE` table.
2. Compute Average: Populate the `Average` field using the formula:

[tex]\[ \text{Average} = \frac{\text{Theory} + \text{LabWork} + \text{FieldWork}}{3} \][/tex]

You can usually do this by writing an SQL update command or using the GUI of your database application.

For SQL, it would look something like:
```sql
ALTER TABLE TBLSCORE ADD COLUMN Average REAL;
UPDATE TBLSCORE SET Average = (Theory + LabWork + FieldWork) / 3;
```

### Step e: Show all records with an average >= 60

Create a query to filter out students with an average below 60. This can be done using an SQL SELECT statement.

For SQL, it would look like:
```sql
SELECT * FROM TBLSCORE WHERE Average >= 60;
```

### Step f: Save the query as QRYANALYSIS

Ensure that this query is saved with the name `QRYANALYSIS` in your database application.

Summarized step-by-step results in a database application:

1. Create Database: `SCORE`
2. Create Table: `TBLSCORE` with columns `StudentName`, `Theory`, `LabWork`, `FieldWork`
3. Insert Data: Populate the table with the provided student data.
4. Add `Average` Column: Add a new column `Average` and compute the averages.
5. Filter Records: Create a query to show records with `Average >= 60`.
6. Save the Query: Save this query as `QRYANALYSIS`.

And you are done! You should now have a database with the required structure and a saved query for students with averages above the specified threshold.

Other Questions