Mr Long Grade:

Subject:
12
Information Technology
Version:
Topic:
Beta
SQL
Video Education

Mr L ong ON SQL

EXERCISE 1 – SELECT STATEMENTS

Open the SQL Editer file. In the block provided, type your SQL statements and then click on the Execute SQL
button. The resulting query should be displayed in the space above with the number of records in the box
provided. Compare your number of records with answer provided with the question.

Click on the View Tables tab to view what the data looks like in the tables you are using.

The exercise makes use of TWO tables

tblAnimals (stores details of animals in a reserve) with the following fields
• EnclosureNo (primary key) : text
• FamilyName : text
• ScientificName : text
• GeneralName : text
• NumAdults : number
• NumYoung : number
• EnclosureSize : number
• Endangered : text

tblVetVisits (stores details of when animals where visited by the medical staff) with the following fields
• VisitID (primary key) : number
• VisitDate : date
• EnclosureNo : text (foreign key linked to tblAnimals)
• ReasonForVisit : text
• FollowUp : boolean
• Animal_ID : text

PART 1
1.1 Write an SQL statement that displays all the animals’ details. (46 records)

1.2 Write an SQL statement the displays all the animals’ details that have the Felidae family name.
(9 records)

1.3 Write an SQL statement that displays the scientific name, general name and enclosure size of all
the animals that have more than 100 units in enclosure size. (4 records)

1
Mr Long Grade:
Subject:
12
Information Technology
Version:
Topic:
Beta
SQL
Video Education

1.4 Write an SQL statement that displays the general name and number of adults of all the animals
with exactly 5 adults. (2 records)

1.5 Write an SQL statement that displays the scientific name, general name and endangered status of
all the animals with a Vulnerable endangered status. (4 records)

1.6 Write an SQL statement that displays all the animals’ details of all animals EXCEPT those with a
Vulnerable endangered status. (42 records)

1.7 Write an SQL statement that displays the general name of all the animals that have no young
animals in the reserve. (6 records)

PART 2
2.1 Write an SQL statement that displays all the animals’ details of all animals with 5 or more number
of adults and 5 or more number of young. (3 records)

2.2 Write an SQL statement that displays the scientific name, general name, number of young and
enclosure size of all the animals with an enclosure size of 400 units or greater and at least 2 young
animals. (3 records)

2.3 Write an SQL statement the displays all the animals’ details that have the Canidae family name and
at least 6 adults. (2 records)

2.4 Write an SQL statement that displays the scientific name, general name and enclosure size of all
the animals that have no young animals and that DO NOT have Not evaluated as an endangered
status. (5 records)

2.5 Write an SQL statement that displays the general name, number of adults and number of young of
all the animals with have no adults or no young. (7 records)

2.6 Write an SQL statement that displays the scientific name, general name and endangered status of
all the animals with a Vulnerable and Endangered endangered status. (6 records)

PART 3
3.1 Write an SQL statement that displays all the animals’ details of all animals without an enclosure
size. (2 records)

3.2 Write an SQL statement that displays the general name and enclosure size of all the animals with
an enclosure size. (44 records)

3.3 Write an SQL statement the displays all the animals’ details that have the Canidae family name and
an enclosure size of 50 to 60 (inclusive). (3 records)

3.4 Write an SQL statement that displays the family name, scientific name and general name of all the
animals of the Mustelidae, Hyaenidae, Viverridae and Bradypodidae families. (13 records)

3.5 Write an SQL statement that displays the general name of all the animals with a general name from
C to any animal starting with the letter E. (11 records)

2
Mr Long Grade:
Subject:
12
Information Technology
Version:
Topic:
Beta
SQL
Video Education

PART 4
NOTE: If using wild card symbols, use % instead of * and _ instead of ?.
4.1 Write an SQL statement that displays all the animals’ details of all animals in the ZD enclosures.
(10 records)

4.2 Write an SQL statement that displays visit date and reason of visit of all the vet visits that deal with
some sort of injury. (5 records)

4.3 Write an SQL statement the displays all the animals’ details of all the seals (general name).
(7 records)

4.4 Write an SQL statement that displays the family name, scientific name and general name of all the
animals with a general name of only 7 letters. (5 records)

4.5 Write an SQL statement that displays the general name of all the animals with a general name of
only 5 letters that ends in the letter R. (1 – Tiger)

4.6 Write an SQL statement that displays the general name and number of young of all the mongooses
with 1 or less young. (3 records)

PART 5
5.1 Write an SQL statement that displays all the animals’ details of in alphabetically order according to
their general name. (Aardwolf to Yellow mongoose)

5.2 Write an SQL statement that displays the general name, scientific name and enclosure size of all
the animals in the Canidae family sorted from biggest enclosure to the smallest.
(African wild dog to Cape fox)

5.3 Write an SQL statement the displays a list of all the family names without any duplicates of all the
animals. (9 records)

5.4 Write an SQL statement the displays a list of all the reasons to visit without any duplicates of all the
vet visits. (12 records)

5.5 Write an SQL statement that displays the enclosure number, general name, visit date and reason
for visit of all those that have a skin problem as the reason for the visit. Sort by visit date.
(5 records)

5.6 Write an SQL statement that displays the enclosure number, general name, endangered status,
reason for visit, and follow up status of all the Vulnerable animals that need a have had a follow up
visit (true). (1 = Cheetah)

PART 6
6.1 Write an SQL statement that displays all the general name, number of adults, number of young and
a calculated TotalAnimals field that shows the total number of adults and young for each animal.
(Cheetah is 3 TotalAnimals)

6.2 Write an SQL statement that displays the general name, scientific name, enclosure size and a
calculated AnimalRatio field which is the enclosure size divided by the sum of the number of adults
and young). This field must be displayed to 2 decimal places. (Cheetah is 16.67 AnimalRatio)

3
Mr Long Grade:
Subject:
12
Information Technology
Version:
Topic:
Beta
SQL
Video Education

6.3 All Hyaenidae cost R3000 per adult to feed and R1200 per young to feed every month. Write an
SQL statement the displays a list of all the family names, general names, number of adults, number
of young and a calculated MonthlyTotal field that displays how much to feed all that family name
every month. Display the MonthlyTotal as a currency. (Spotted hyena is R34 200.00 MonthlyTotal)

6.4 Write an SQL statement the displays the general name and a calculated MoreAdults field that
shows the difference between the number of adults and the number of young but only for those
animals where there are more adults then young. (26 records, Cheetah is 1 MoreAdults)

6.5 Write an SQL statement that displays the family name, general name and number of adults of all
the animals that have an EVEN number of number of adults. (Hint: Use the INT function).
(27 records)

PART 7
7.1 Write an SQL statement that displays the visit ID, visit date and reason for visit of all those visits the
occurred in the first week of September 2019. (8 records)

7.2 Write an SQL statement that displays the visit ID, visit date and reason for visit of all those visits the
occurred in September 2019 and that need a follow up (false). (22 records)

7.3 Write an SQL statement that displays the visit ID, visit date and a calculated DaysTillVisit that
displays the number of days from the current date since the visit date.

7.4 Write an SQL statement that displays the visit ID, visit date, reason to visit and a calculated
NextCheckup that displays the date of the next check-up which is 60 days after the visit date. This is
only for the vet visits that are routine check-ups. (21 records)

PART 8
8.1 Write an SQL statement that displays the MOST number of adults of all animals. (Answer = 9)

8.2 Write an SQL statement that displays the AVERAGE enclosure size of only the Felidae family. Round
your answer to TWO decimal places. (Answer = 1193.11)

8.3 Write an SQL statement that displays the MOST number of young for EACH family. (Canidae = 4)

8.4 Write an SQL statement that displays the average number of adults for EACH family where the
average is 5 or more. (Records = 2, Viverridae = 5)

8.5 Write an SQL statement that displays the average number of animals (adults and young combined)
for EACH family where they are sorted in descending order of the average number of animals.
(9 records, Hyaenidae = 8 at top)

PART 9
9.1 Write an SQL statement that displays the general name and scientific name of all the animals that
have the word cat at the end of the general name (You may NOT use the LIKE operator). (3 records)

9.2 Write an SQL statement that displays the general name and the calculated length of the general
name (LengthOfName) for all animals.

9.3 Write an SQL statement that displays the number of characters in the scientific name with the
MOST characters. (Answer = 24)

4
Mr Long Grade:
Subject:
12
Information Technology
Version:
Topic:
Beta
SQL
Video Education

9.4 Write an SQL statement that displays all the details as well as a calculated AnimalCode field that is
made of first THREE letters of the general name, last two characters of the enclosure number,
characters 2 to 4 of the scientific name and lastly the number of adults (use the STR function to
convert NumAdults to text). The whole AnimalCode must be in capital letters.
(Cheetah = CHEA1CIN 2)

PART 10
NOTE: Read the Mr Long Summary on subqueries then complete part 10.
10.1 Write an SQL statement that displays the general name and the calculated TotalAnimals (number
of adults and young combined) of all the animals where the total animals is ABOVE the average
total (adults and young) of all animals. (23 records)

10.2 Write an SQL statement that displays the AVERAGE total animals (adults and young combined) for
EACH family where the average total of that family is above the average total (adults and young) of
all animals, sorted in descending order according to the average total animals.
(6 records, Hyaenidae = 8 at top)

END OF EXERCISE 1

5
Mr Long Grade:
Subject:
12
Information Technology
Version:
Topic:
Beta
SQL
Video Education

EXERCISE 2
Open the Project1 file and complete the program by answering the following questions:

Click on the View Table tab to view what the data looks like in the table you are using.

The exercise makes use of the following table:

AdvertsTb (stores details about adverts that are published in a newspaper) with the following fields:
• AdvertID (primary key) : text
• ClientID : text
• DatePlaced : date
• WeeksRunning : number
• NumWords : number
• Paid : boolean
• Category : text

1.1 Complete the SQL code for the btnQ11 button that will display all the advert details for all adverts by a
given client, based on an Client ID entered by the user via an Inputbox function. The results must be
displayed from latest advert paced to the oldest advert placed.

Example of output if the user gives “HI004” as the input for the Client ID:

1.2 Complete the SQL code for the btnQ12 button that will display all the advert details for all adverts
placed on a given date, based on a date entered by the user via an Inputbox function and that have
MORE words than a value entered by the user via another Inputbox.

Example of output if the user gives the date 2018/12/10 and number of words as 15:

6
Mr Long Grade:
Subject:
12
Information Technology
Version:
Topic:
Beta
SQL
Video Education

1.3 Complete the SQL code for the btnQ13 button that will display the most number of words, least
number of words and average number of words (rounded to 2 decimal places) for all adverts of a given
category, based on a category entered by the user via an Inputbox function. Use the field names as
shown in the diagram below.
HINT: you can separate calculations by a comma.

Example of output if the user gives “Cars” as the input for the category:

1.4 Complete the SQL code for the btnQ14 button that will display all the advert details for all adverts that
were paid or unpaid, based on a Yes or No entered by the user via an Inputbox function and that have
an Advert ID that starts with a letter entered by the user via another Inputbox.

Example of output if the user wants adverts that were paid (True) and where the Advert ID starts with
the letter C:

END OF EXERCISE 2

7
Mr Long Grade:
Subject:
12
Information Technology
Version:
Topic:
Beta
SQL
Video Education

EXERCISE 3 – OTHER STATEMENTS

Open the SQL Editer file. In the block provided, type your SQL statements and then click on the Execute SQL
button. The SQL statements will be executed and then the table that you were modifying will be displayed in
the space above with the number of records in the box provided. Review the table to see if your SQL
statement was executed correctly
HINT: Use the Restore Database button to undo any changes you have made to the database.

Click on the View Tables tab to view what the data looks like in the tables you are using.

The exercise makes use of TWO tables

tblAnimals (stores details of animals in a reserve) with the following fields
• EnclosureNo (primary key) : text
• FamilyName : text
• ScientificName : text
• GeneralName : text
• NumAdults : number
• NumYoung : number
• EnclosureSize : number
• Endangered : text

tblVetVisits (stores details of when animals where visited by the medical staff) with the following fields
• VisitID (primary key) : number
• VisitDate : date
• EnclosureNo : text (foreign key linked to tblAnimals)
• ReasonForVisit : text
• FollowUp : boolean
• Animal_ID : text

PART 1
1.1 Write an SQL statement inserts the following details into the animals table:
Enclosure number: ZX1
Family name: Rhinocerotidae
Scientific name: Ceratotherium simum
General name: White rhino
Number of adults: 1
Number of young: 1
Enclosure size: 1000
Endangered status: Vulnerable. (1 record affected)

8
Mr Long Grade:
Subject:
12
Information Technology
Version:
Topic:
Beta
SQL
Video Education

1.2 Write an SQL statement inserts the following details into the vet visits table:
Visit ID: 50
Visit Date: 2019/10/15
Enclosure number: ZH3
Reason for visit: Arm injury
Follow up: False
Animal ID: ZH3_2 (1 record affected)

1.3 Write an SQL statement inserts the following details into the animals table:
Enclosure number: ZX2
Family name: Hippopotamidae
Scientific name: Hippopotamus amphibius
General name: Hippopotamus
(1 record affected)

PART 2
2.1 Write an SQL statement that changes the meerkat’s (general name) enclosure size to 50.
(1 record affected)

2.2 Write an SQL statement that changes the tiger’s (general name) general name to Siberian tiger.
(1 record affected)

2.3 Write an SQL statement that increases the number of adults by 4 for all the Mustelidae family.
(5 records affected)

2.4 Write an SQL statement that changes all the animals with no enclosure size to 100.
(2 records affected)

2.5 Write an SQL statement that changes all the follow up values to true for all the routine check-up
vet visits in September that needed a follow up (are false). (19 records affected)

PART 3
NOTE: Press the Restore Database button before executed each of these questions.
3.1 Write an SQL statement that deletes visit ID 30 in the vet visits table. (1 record affected)

3.2 Write an SQL statement that deletes all the vet visits that involved ear infections for the reason for
the visit. (5 records affected)

3.3 Write an SQL statement that deletes all the vet visits that took place on the 5th day of any month.
(4 records affected)

3.4 Write an SQL statement that deletes all the skin problems and ear infections that had a follow up
(true) from the vet visits table. (6 records affected)

END OF EXERCISE 3

9
Mr Long Grade:
Subject:
12
Information Technology
Version:
Topic:
Beta
SQL
Video Education

EXERCISE 4
Open the Project1 file and complete the program by answering the following questions:

Click on the View Table tab to view what the data looks like in the table you are using.

The exercise makes use of the following table:

AdvertsTb (stores details about adverts that are published in a newspaper) with the following fields:
• AdvertID (primary key) : text
• ClientID : text
• DatePlaced : date
• WeeksRunning : number
• NumWords : number
• Paid : boolean
• Category : text

1.1 Complete the SQL code for the btnQ11 button that takes in SEVEN values entered by the user via
Inputbox functions (one for each field in the AdvertTb table) and adds those details as a new record to
the AdvertsTb table.

1.2 Complete the SQL code for the btnQ12 button that takes in FOUR values entered by the user via
Inputbox functions (advert ID, Client ID, date advert was placed and number of words in the advert)
and adds those details as a new record to the AdvertsTb table.

2.1 Complete the SQL code for the btnQ21 button that takes in a category and number entered by the
user via Inputbox functions and increases the weeks running by the given number of all the adverts in
the category given as input.

2.2 Complete the SQL code for the btnQ22 button that takes in a string value entered by the user via
Inputbox function and changes Paid field to false of all the adverts with advert IDs that START with the
string value given as input. (Example if J is given as input then all the advert ID’s that start with a J).

3.1 Compete the SQL code for the btnQ31 button that takes in a value for the advert ID entered by the
user via Inputbox function and deletes the record with that advert ID.

3.2 Compete the SQL code for the btnQ32 button that takes in a date entered by the user via Inputbox
function and deletes all adverts that were placed on the given date.

END OF EXERCISE 4

10
Mr Long Grade:
Subject:
12
Information Technology
Version:
Topic:
Beta
SQL
Video Education

 FINAL CHALLENGE 
Open the Project1 file and complete the program by answering the following questions:

The GrabMuch.mdb database contains two tables called tblItems and tblOrders.

The records contained in the tblItems table consist of the following fields:
• ItemID – a unique identification code (text data type) which is also the primary key (PK)
• ItemName – name of the item (text data type)
• Category – category (‘DVD’, ‘Game’ or ‘TABLET’) of the item (text data type)
• Price – cost of one unit of the item (real data type)
• Stock – number of items currently in stock (integer data type)
• LastOrdered – date when the item was last ordered from the supplier (date data type)

Example of records from the tblItems table:

The records contained in the tblOrders table consist of the following fields:
• OrderID – a unique identification code (integer data type) for the number of the order, which is a
part of the primary key (PK)
• ItemID – code of the item that was ordered (text data type) which is also part of the primary key (PK)
• Quantity – amount of the item ordered (integer data type)

Example of records from the tblOrders table:

The ItemID field has been used to link the two tables.
NOTE: Connection code to the database has been provided.

11
Mr Long Grade:
Subject:
12
Information Technology
Version:
Topic:
Beta
SQL
Video Education

Q1 Complete the SQL code for the btnQ1 button that will display all the fields of all the
records in the tblItems table listed from the MOST expensive to the LEAST expensive.

Example of output for the first few records:

Q2 Complete the SQL code for the btnQ2 button that will display the item’s name, price and stock of
ONLY the DVD’s that have stock available (Stock is 1 or more).

Example of output for the first few records:

Q3 Complete the SQL code for the btnQ3 button that will display the item’s ID, name and category of
invalid DVD records. A DVD record is invalid if the item’s ID STARTS with a D but the category field is
not ‘DVD’.

Example of output:

Q4 Complete the SQL code for the btnQ4 button that will display the item’s name, date last ordered,
stock and price of all the items ordered before (not including) this year (2018). Refer to the
LastOrdered field to tell you which year it was ordered.

Example of output for the first few records:

12
Mr Long Grade:
Subject:
12
Information Technology
Version:
Topic:
Beta
SQL
Video Education

Q5 Complete the SQL code for the btnQ5 button that will display the CHEAPEST price of all items in each
of the categories (‘DVD’,’GAME’ and ’TABLET’).

Example of output:

Q6 Complete the SQL code for the btnQ6 button that will display the TOTAL NUMBER of different
TABLETS there are (NOT the number in stock)

Example of output:

Q7 Complete the SQL code for the btnQ7 button that will display the order ID, the item’s name, and
quantity for all the orders for an item name entered by the user via an Inputbox function.
HINT: You need to use both tables in your query.

Example of output if the user gives “Avengers” as the input for the item name:

Q8 Complete the SQL code for the btnQ8 button that will display the item’s name, price and quantity as
well as calculating the sub-total for each item (quantity X price) of all the items bought in an order,
based on an order ID entered by the user via an Inputbox function.
HINT: You need to use both tables in your query.

Example of output if the user gives “1” as the input for the order ID:

Q9 The code prompts the user for two values, one presenting the year and the other representing the
month as numbers. Complete the SQL code for the btnQ9 button that will display the AVERAGE stock
for EACH of the dates (rounded to TWO decimal places) but ONLY for the dates that occur in the same
year and month specified via the user inputs AND that have an average stock of 5 or more.

Example of output if the user inputs 2018 for the year and 7 for the month:

13
Mr Long Grade:
Subject:
12
Information Technology
Version:
Topic:
Beta
SQL
Video Education

Q10 Complete the SQL code for the btnQ10 button that will ADD a new record to the
tblItems table with the following details:

• ItemID: D20
• ItemName: The Hulk
• Category: DVD
• Price: 99
• Stock: 5
• LastOrdered: 2018/08/01

Modify the code above so that the user is prompted for SIX values via inputbox functions for each
detail shown above. Use these inputted values to ADD a new record.

Q11 Complete the SQL code for the btnQ11 button that will update the stock of the
‘Black Panther’ DVD by 5 in tblItems table. In other words, the stock must be increased by 5. For
example, if the stock is 15 then it must be updated to 15 + 5 = 20.

Q12 Complete the SQL code for the btnQ12 button that will prompt the user for an Item ID and remove all
occurrences of that Item ID in the tblOrders table.

END OF FINAL CHALLENGE

Additional Links:
Youtube video playlist:
https://www.youtube.com/watch?v=ZgNdtFp4yME&list=PLxAS51iVMjv9OrVc4wIZM0sFyorFg7ZBI
• Google drive resource activities:
https://tinyurl.com/MLE-G12IT-SQL

For more IT related material find us on:

youtube.com/user/MrLongEducation

facebook.com/MrLongEducation @MrLongEdu

14