QUESTION $4[30$ marks$]$

NOTE: All SQL syntax must be correct, missing characters will be penalized!

BTEE Adventure Tours is a small business that organizes daylong, guided trips to South Africa. To support the

company$$s growing business, management uses a database to ensure that the company$$s data is current,

accurate, and easily accessible.

The owner of Colonial Adventure Tours knows that being able to run queries is one of the most important

benefits of using a DBMS$.$ Use SQL commands to answer the following exercises, you will use the data in the

Colonial Adventure Tours database shown in the below figures:

Figure $4\mathrm{.}1$ presents sample trip data for BTEE Adventure Tours. Each trip is identified by a unique number called

TripID. Furthermore, management tracks the trip name, the trip$$s starting location, and the province in which the

trip originates. In addition, management records the trip$$s total distance $($in KMs$),$ the trip$$s maximum group size,

the trip$$s type, and the season of the year.

Figure $4\mathrm{.}1$: Trip

TripID TripName StartLocation Province Distance MaxGrpSize Type Season

$1$ State Park Ride Maloti$-$Drakensberg Park KZN $158$ Hiking Summer

$2$ Mount Mandela Boschkop Gauteng $38$ Biking Early spring

$3$ Thaba Tshwaane Grootfontein Bike Park Gauteng $1916$ Biking Spring

$4$ Mount Zweli iSimangaliso Wetland Park KZN $754$ Hiking Spring

$5$ God$$s Window Hazeldean Valley Trails Gauteng $818$ Biking Summer

$6$ Sharak Hills Underberg KZN $5012$ Hiking Late Spring

$7$ Waterfalls Paarl Western Cape $2715$ Paddling Early Fall

$84$

Njomane Mount Canoeing Mpumalanga $156$ Paddling Summer

$9$ River Loop Rosemary Hill Farm Gauteng $10418$ Biking Spring

$10$ Swaart Kop Tierpoort Adventure Farm Gauteng $1414$ Biking Early Spring

$11$ Long Pond Velddrif Western cape $457$ Paddling Late Spring

$12$ Mpuma Falls Kayaking Mpumalanga $3814$ Paddling Spring

The table named Reservation shown in Figure $4\mathrm{.}2$ shows the sample of reservation data for BTEE Adventure

Tours. Each reservation is identified by a unique reservation number that uses the last $2$ digits of the current year

followed by a three$-$digit number that increments sequentially. The table shows the TripID, date, number of

persons, price per person, and other related information.

Figure $4\mathrm{.}2$: Reservation

ReseravtionID TripID TripDate NumPersons TripPrice OtherFees CustomerNum

$24001314/02/202412$ R$2250\mathrm{.}00$ R$0\mathrm{.}005501$

$24002618/03/202410$ R$18000\mathrm{.}00$ R$0\mathrm{.}005501$

$240031127/02/20245$ R$9000\mathrm{.}00$ R$10\mathrm{.}005526$

$24004129/03/20244$ R$450\mathrm{.}00$ R$15\mathrm{.}005521$

$24005410/04/20246$ R$19500\mathrm{.}00$ R$0\mathrm{.}005513$

$24006919/04/20248$ R$45000\mathrm{.}00$ R$25\mathrm{.}005508$

$24007803/03/202413$ R$1350\mathrm{.}00$ R$12\mathrm{.}005509$

$24008721/02/20245$ R$1650\mathrm{.}00$ R$35\mathrm{.}005521$

$240091025/04/20249$ R$1150\mathrm{.}00$ R$35\mathrm{.}005502$

a$.$Write the SQL code to list the name of each trip that does not start in KZN$.(3)$

b$.$Write the SQL code to list the name and start location for each trip that has the type

Biking. $(2)$

c$.$Write the SQL code to list the name of each trip that has the type Hiking and that has a

distance greater than twenty kilometers. $(4)$

d$.$Write the SQL code to list the name of each trip that has the type Paddling or that is

located in Mpumalanga. $(4)$

e$.$Write the SQL command that calculates how many trips have a type of Paddling or Biking.

$(6)$

f$.$ Write the SQL code to list the trip name and province for each trip that occurs during the

summer season. Sort the results by trip name within the same province. $(4)$

g$.$How many reservations include a trip with a price that is greater than R$2000\mathrm{.}00$ but less

than R$35000\mathrm{.}00?(5)$

h$.$Write the SQL code to delete table RESERVATION from the database. $(2)$