SEHH2240 Database Systems Tutorial 6
Tutorial 6
SQL II
Learning Outcomes
➢ Getting familiar with the Microsoft Access environment.
➢ Practicing simple SQL-DML statements.
How to Edit and Execute a SQL Statement
A query can be set up to question about data stored in a database, e.g., to return the records for
salary over 30000. You may do this in Excel by sorting on the salary column, but if you need to
return information from multiple tables, you need the database querying function involving
multiple tables. In Access, you have to specify the conditions and restrictions imposed on data to
limit the information presented. These are called criteria.
• In the database window, choose the Create tab, Query Design.
Query Designs
Create
• If you select the Design View, you will see the Show Table window. You can add the
appropriate tables you want as you have done in the Relationships window. The links between
the tables are shown automatically as well (information when you define the relationships).
• You will see the Query Design window at the bottom and you can add the fields and table
names in the window, or else you can drag the fields from the tables directly to the cells in the
bottom window.
• Specify any criteria and sorting order you want.
• When you are done, select File tab, Save to save the query under a certain name.
• Double-click the query to return the result of the query.
• Or in Design View click File tab, Run.
Run
Note: You can enter one statement at a time. Delete the old statement before entering a new one.
Writing queries to database tables using SQL-DML on multiple tables
JustLee Books is a book shop. The boss of this book shop needs your help to retrieve information
from MS Access database called [Link].
Open the database called [Link], that have been given to you by your instructor. Complete the
following queries using SQL-DML statements. To determine the exact name of the fields used in
the tables for these exercises, refer to the design view of the table.
For each of the following tasks, determine (a) the SQL statement needed to perform the stated task
using the traditional approach and (b) the SQL statement needed to perform the stated task using
the JOIN keyword.
Page 1 of 2
SEHH2240 Database Systems Tutorial 6
Also create the necessary RELATIONSHIPS with appropriate integrity rules on the database tables
before doing the queries.
1. Create a list that displays the title of each book and the name and phone number of the
person at the publisher’s office whom you would need to contact to reorder each book.
2. Determine which order numbers have not yet shipped and the name (first name and last
name) of the customer that placed each order. Sort the results by the date on which the order
was placed.
3. List the customer number and names of all individuals who have purchased books in the
Fitness Category.
4. Determine which book titles Jake Lucas has purchased.
5. Determine each book title sold to Jake Lucas and its profit. Sort the results by the date of
the order. If more than one book was ordered, have the results sorted by the profit amount in
descending order.
6. Determine how many orders have been placed by each customer number in the
CUSTOMERS table. Sort the results by the number of orders and then by the customer
numbers. Do not include any customer in the results who has not recently placed an order
with the book shop.
7. List the customer names living in State Georgia (GA) or Florida (FL) who have placed
orders totally more than $80.
8. Determine which book titles were published by the publisher of The Wok Way to Cook.
9. Case:
The book shop has a problem: Their book storage space is becoming very limited. As a
solution, management is considering limiting the inventory to only those books that return
at least a 55 % profit. Any book that returns less than a 55 % would be dropped from
inventory and not reordered.
This plan could, however, have a negative impact on overall sales. Management fears that if
the bookstore stops carrying the less-profitable books, the company might lose repeat
business from its customers. As part of management’s decision-making process, they want
to know whether less-profitable books are frequently purchased by current customers.
Therefore, management would like to know how many times these less-profitable books
have been purchased recently.
Determine which book titles generate less than a 55% profit and how many copies of those
books have been sold recently. Summarise your findings for management, and include a
copy of the query necessary to retrieve the data from the data from the database tables.
10. Determine which order numbers with the ship state is same as order 1001.
Page 2 of 2