This week we are going to continue developing our query skills. Download the Sales textbook

from the database and create queries for to answer the following problems. You can use the

Query Design tool to solve these problems, or try writing the SQL yourself. Some problems may

be easier to solve with the GUI, while other will be easiest to solve using SQL code. If you

choose to stick with the Query Design tool for now, I still recommend you view the SQL code

that Access generates for you.

$1.(2$ Marks$)$ Create a cross$-$tab query that displays the total quantity of each product sold by

each store. Here is a sample output.

Note: We haven$$t covered how to write SQL code for a cross$-$tab yet, so the visual Query

Builder will help here.

Black

tea

Chai corn

flakes

cream

$10\%$

cream

$18\%$

ketchup maple

syrup

milk $1$

litre

pepper rolled

oats

salt

Calgary $111111$

Number $12510141010511110$

Selkirk No$.1$

$($One$)$

$11111$

Woodcroft $1055118320205073$

$2.(2$ Marks$)$ Create a query that creates a new table called Prairies. This new table will contain

all of the records from the Province table for the following provinces: Albert, Saskatchewan,

and Manitoba.

$3.(2$ Marks$)$ Create a query that returns the full name of the Province that has made the most

money $($has the highest total amountSold$).$

$4.(2$ Marks$)$ Create a query that returns the names and cities of each store that has never

made a sale.

$5.(2$ Marks$)$ Create a query that returns the following information in a single column:

a$.$ The names of all products that cost $$4$ or less

b$.$ The names of all categories that have a description containing a comma.

Note: To solve this, you will need to use SQL to perform a UNION