Western University Ontario

Department of Computer Science

📊 The Pop-Up Shop: SQL Queries 📊

CS 1032B – Winter 2026

Assignment Weight: 8% of Final Grade

📋 Overview
Great work completing your pop-up shop database in Assignment 3! Now it is time to interact with that
database using SQL – the universal language of databases. In this assignment you will practice the
four core SQL operations: SELECT (read data), INSERT (add data), UPDATE (change data), and
DELETE (remove data). You will also practice combining tables using JOIN.

In this assignment, you will:

• Use SELECT to retrieve and filter data from your tables
• Use INSERT to add new records to your database
• Use UPDATE to modify existing records
• Use DELETE to remove records
• Use INNER JOIN to combine data from two related tables
• Write a short reflection on what your queries revealed

✨ Building on Assignment 3:
You will use the SAME Access database file from Assignment 3!
Open your yourusername_PopUpShopDB.accdb before you start.
Your sample data from Assignment 3 will be used to verify query results.

⚠ Important Reminders
This is INDIVIDUAL work – no collaboration or AI assistance.
Save ALL queries inside your Access database file with the exact names shown.
macOS users: Save to H: drive in MyVLab, NOT the Z: drive!
See OWL for due date and late policy.

🛠 How to Write SQL Queries in Access

Follow these steps for every query in this assignment:
1. Open your database in Microsoft Access
2. Click the Create tab → Query Design
3. Close the 'Show Table' dialog (click Close without adding any tables)
 4. In the Query Design ribbon, click View → SQL View
5. Type your SQL statement in the blank area
6. Click the Run button ( ! ) to execute and check the results
7. Click Save and name the query EXACTLY as shown in each task

💡 Tip
After running a SELECT query, switch to Datasheet View to see the results.
For INSERT / UPDATE / DELETE, Access will ask you to confirm before making changes –
click Yes.
If Access shows an error, check spelling of table and field names carefully.

📚 Quick Reference: SQL Statements

Study these four patterns before starting. Every task in this assignment uses one of them.

Statement What it does & Example

Read / retrieve data from a table.

SELECT SELECT FirstName, LastName FROM Customer WHERE LastName =
'Smith';

Add a new row to a table.

INSERT INSERT INTO Customer (Email, FirstName, LastName, Address)
VALUES ('jane@[Link]', 'Jane', 'Doe', '123 Main St');

Change existing data in a table.

UPDATE
UPDATE Product SET SalePrice = 12.00 WHERE ProductID = 1;

Remove a row from a table.

DELETE
DELETE FROM Ticket WHERE Status = 'Resolved';

Combine rows from two tables where the join field matches.
SELECT [Link], [Link]
INNER JOIN
FROM Customer INNER JOIN Invoice ON [Link] =
[Link];

Useful Extras
WHERE – filter rows: WHERE TotalPrice > 20
ORDER BY – sort results: ORDER BY LastName ASC
AND / OR – combine conditions: WHERE Status = 'Open' AND Severity = 'High'
IS NULL – find missing values: WHERE EmployeeUserName IS NULL
LIKE – pattern match (Access): WHERE Email LIKE '*@[Link]'

🔵 Part 1: SELECT Queries (2%)

SELECT queries let you READ data from your database. You will write four SELECT statements, each
targeting different information in your tables.

Query 1: AllCustomers
Business Question: Show the first name, last name, and email of every customer in your
database, sorted alphabetically by last name.
SQL Keyword(s) to Use: SELECT ... FROM ... ORDER BY
Columns to Show: FirstName, LastName, Email
Requirements:
• Use the Customer table
• Show only these three columns: FirstName, LastName, Email
• Sort results by LastName in ascending order (A to Z)
• Save the query as: AllCustomers

Query 2: AllProducts
Business Question: Show all product names and their sale prices. Display the most
expensive product first.
SQL Keyword(s) to Use: SELECT ... FROM ... ORDER BY ... DESC
Columns to Show: Title, SalePrice
Requirements:
• Use the Product table
• Show Title and SalePrice columns
• Sort by SalePrice from highest to lowest (DESC)
• Save the query as: AllProducts

Query 3: OpenTickets
Business Question: Show all support tickets that currently have a status of 'Open'. Display
the ticket ID, subject, and severity.
SQL Keyword(s) to Use: SELECT ... FROM ... WHERE
Columns to Show: TicketID, Subject, Severity
Requirements:
• Use the Ticket table
• Filter: only rows where Status = 'Open'
• Show TicketID, Subject, Severity
• Save the query as: OpenTickets

Query 4: CheapProducts
Business Question: Show all products that cost less than $5.00 to manufacture. Display
the product name and cost to manufacture.
SQL Keyword(s) to Use: SELECT ... FROM ... WHERE
 Columns to Show: Title, CostToManufacture
Requirements:
• Use the Product table
• Filter: only rows where CostToManufacture < 5
• Show Title and CostToManufacture
• Save the query as: CheapProducts

🔵 Part 2: INSERT Queries (2%)

INSERT queries let you ADD new rows to a table. You will add new records to three different tables.

⚠ INSERT Reminder
List the column names in brackets after the table name.
List the matching values in the same order inside VALUES ( ... ).
Text values go in single quotes. Numbers do NOT use quotes.
Example: INSERT INTO Customer (Email, FirstName, LastName, Address) VALUES
('john@[Link]', 'John', 'Smith', '99 Oak St');

Query 5: AddNewCustomer
Business Question: A new customer just made their first purchase at the market. Add
them to the Customer table.
SQL Keyword(s) to Use: INSERT INTO ... VALUES (...)
Requirements:
• Use the Customer table
• Add one new customer with a realistic name, email, and address
• Use an email address that does NOT already exist in your data
• PhoneNumber and Birthday are optional – you may leave them out
• Save the query as: AddNewCustomer

Query 6: AddNewProduct
Business Question: You have created a brand new handmade item to add to your shop.
Add it to the Product table.
SQL Keyword(s) to Use: INSERT INTO ... VALUES (...)
Requirements:
• Use the Product table
• Add one new product (different from your 3 existing products)
• Fill in Title, Description, SalePrice, CostToManufacture, and QuantityInStock
• PhotoURL is optional – you may leave it out
• Save the query as: AddNewProduct
 Query 7: AddNewInvoice
Business Question: A customer just completed a purchase at your market booth. Add a
new invoice record.
SQL Keyword(s) to Use: INSERT INTO ... VALUES (...)
Requirements:
• Use the Invoice table
• The CustomerEmail MUST match an email already in your Customer table
(referential integrity!)
• Choose a realistic PaymentMethod (VISA, Debit, or PayPal)
• Fill in TotalPrice with a realistic amount
• CouponCode is optional
• Save the query as: AddNewInvoice

🔵 Part 3: UPDATE Queries (2%)

UPDATE queries let you CHANGE existing data. Always use a WHERE clause to target only the row(s)
you want to change. Without WHERE, every row in the table gets updated!

⚠ UPDATE Reminder
Always include WHERE so you only change the intended row(s).
You can update more than one column at once by separating them with commas.
Example: UPDATE Product SET SalePrice = 8.00, QuantityInStock = 50 WHERE
ProductID = 1;

Query 8: UpdateProductPrice
Business Question: The cost of materials has gone up, so you need to raise the sale price
of one of your products.
SQL Keyword(s) to Use: UPDATE ... SET ... WHERE
Requirements:
• Use the Product table
• Change the SalePrice of ONE specific product (use its ProductID in the WHERE
clause)
• Choose a new price that is higher than the original
• Save the query as: UpdateProductPrice

Query 9: UpdateTicketStatus
Business Question: A support ticket has been resolved. Update its status so it no longer
appears as open.
SQL Keyword(s) to Use: UPDATE ... SET ... WHERE
Requirements:
 • Use the Ticket table
• Change the Status of ONE specific ticket to 'Resolved' (use its TicketID in the
WHERE clause)
• Save the query as: UpdateTicketStatus

Query 10: UpdateInventory

Business Question: You restocked your bestselling product after the market. Update the
quantity in stock.
SQL Keyword(s) to Use: UPDATE ... SET ... WHERE
Requirements:
• Use the Product table
• Increase QuantityInStock for ONE product (use its ProductID in the WHERE clause)
• Choose a realistic restocked amount (e.g., add 20 or 30 units)
• Save the query as: UpdateInventory

🔵 Part 4: DELETE Queries (1%)

DELETE queries REMOVE rows from a table. Like UPDATE, always use WHERE to target specific
rows. Deleted data cannot be recovered easily!

⚠ DELETE Reminder
Always use WHERE to specify which row(s) to delete.
You cannot delete a row that other tables depend on (referential integrity).
Example: DELETE FROM Ticket WHERE TicketID = 3;

Query 11: DeleteResolvedTicket

Business Question: Old resolved tickets are cluttering your database. Delete one resolved
ticket to clean up.
SQL Keyword(s) to Use: DELETE FROM ... WHERE
Requirements:
• Use the Ticket table
• Delete ONE ticket that has Status = 'Resolved'
• Use the TicketID in the WHERE clause to be specific
• Make sure this ticket has no RMA linked to it (otherwise Access will block the delete)
• Save the query as: DeleteResolvedTicket

🔵 Part 5: INNER JOIN Queries (1%)

INNER JOIN lets you pull together information from two tables in a single query. The tables are linked
using a matching field (the foreign key relationship you built in Assignment 3).
 📌 INNER JOIN Syntax
SELECT TableA.Field1, TableB.Field2
FROM TableA INNER JOIN TableB ON [Link] = [Link];

Example:
SELECT [Link], [Link], [Link],
[Link]
FROM Customer INNER JOIN Invoice ON [Link] = [Link];

Query 12: CustomerOrders

Business Question: Show each customer's first name, last name, and the total price of
every order they have placed.
SQL Keyword(s) to Use: INNER JOIN
Columns to Show: FirstName, LastName, TotalPrice, PaymentMethod
Requirements:
• Join the Customer table and the Invoice table
• Match on [Link] = [Link]
• Show: FirstName, LastName, TotalPrice, PaymentMethod
• Sort by LastName ASC
• Save the query as: CustomerOrders

Query 13: TicketProducts

Business Question: Show each support ticket along with the name of the product it is
about.
SQL Keyword(s) to Use: INNER JOIN
Columns to Show: TicketID, Subject, Status, Title (product name)
Requirements:
• Join the Ticket table and the Product table
• Match on [Link] = [Link]
• Show: TicketID, Subject, Status, and Title (the product name)
• Save the query as: TicketProducts

📝 Part 6: Reflection (Word Document) (2%)

Create a separate Word document answering the two questions below. Write at least 3–4 sentences
per answer. Total minimum length: 200 words.

Format Requirements:
 Microsoft Word (.docx) format
Include your name, student number, and shop name at the top
At least 3–4 sentences per question
Minimum 200 words total

Question 1: What Did You Discover?

Look at the results of your AllProducts query (Query 2) and your CustomerOrders query (Query 12).
What do the results tell you about your shop? For example: Which product is most expensive? How
many orders did your top customer place? Use specific examples from your results.

Question 2: Why Does SQL Matter?

In Assignment 3 you entered data by hand one row at a time. Explain in your own words why using
SQL (SELECT, INSERT, UPDATE, DELETE) is faster and more reliable than manually editing data in
tables. Give one real example from this assignment where SQL saved you time or reduced the chance
of making a mistake.

📤 Submission Instructions
Submit TWO files via OWL:

File Format Naming Convention

yourusername_PopUpShopDB.accdb (same file as
Parts 1–5: All Queries .accdb
Assignment 3 with queries added)
Part 6: Reflection .docx yourusername_PopUpShop_A4.docx

Example: If your username is ibatool2, submit:

• ibatool2_PopUpShopDB.accdb – with all 13 queries saved inside
• ibatool2_PopUpShop_A4.docx – reflection document

📊 Grading Rubric (8% Total)

Component Weight Criteria
Correct columns shown, WHERE filters work, ORDER BY
Part 1: SELECT (Q1–4) 2%
sorts correctly. 0.5% per query.
New rows added successfully with realistic, valid data.
Part 2: INSERT (Q5–7) 2%
Referential integrity respected. Approx. 0.65% per query.
Correct field updated, WHERE clause targets only the
Part 3: UPDATE (Q8–10) 2%
intended row. 0.65% per query.
Correct row deleted, WHERE clause used, no referential
Part 4: DELETE (Q11) 1%
integrity violation.
 Part 5: INNER JOIN (Q12– Tables joined on the correct matching fields, correct
1%
13) columns shown. 0.5% each.
No
separate
Thoughtful 200+ word answers referencing actual query
Part 6: Reflection mark –
results. 2% if Parts 1–5 complete.
included in
overall

Note: The reflection is worth 2% but is graded as part of the overall submission. If your queries are complete and
correct, the reflection marks are easily earned by writing thoughtfully about your results.

💡 Tips for Success

• Keep your Assignment 3 database open alongside the assignment sheet
• Run every query after writing it and check the results make sense
• Use exact table and field names from your database – spelling matters!
• For INSERT, make sure CustomerEmail values already exist in the Customer table
• For DELETE, check there are no linked records in other tables before deleting
• Save the query with the exact name specified – wrong names lose marks
• For the reflection, reference specific numbers from your query results

What You Will Learn:

• SELECT – how to read and filter data
• INSERT – how to add new records
• UPDATE – how to change existing records safely
• DELETE – how to remove records without breaking your database
• INNER JOIN – how to pull data from two related tables at once

Good luck – make your database talk! 🎉