Introduction to Structured Query Language - Database Processing: Fundamentals, Design, and Implementation - page 108

ITEM

Column Name Type Key Required Remarks

ItemID AutoNumber Primary Key Yes Surrogate Key

ItemDescription Text (255) No Yes

CompanyName Text (100) No Yes

Yes

PurchaseDate Date No

Currency, 2
decimal places
Currency, 2
decimal places

Figure 2-45
Column Characteristics
for the QACS
Database ITEM Table

ItemCost Number No Yes

Yes

ItemPrice Number No

We recommend that you create a Microsoft Access 2013 database named QACS-CH02.accdb
using the database schema, column characteristics, and data shown above and then use this da-
tabase to test your solutions to the questions in this section. Alternatively, SQL scripts for creat-
ing the QACS-CH02 database in Microsoft SQL Server, Oracle Database, and MySQL are available
on our Web site at www.pearsonhighered.com/kroenke .

Write SQL statements and show the results based on the QaCS data for each of the
following:

a. Show all data in each of the tables.

B. List the LastName, FirstName, and Phone of all customers.

C. List the LastName, FirstName, and Phone for all customers with a FirstName of 'John'.

D. List the LastName, FirstName, and Phone of all customers with a last name of
'Anderson'.

e. List the LastName, FirstName, and Phone of all customers whose first name starts
with 'D'.

F. List the LastName, FirstName, and Phone of all customers whose last name includes
the characters 'ne'.

G. List the LastName, FirstName, and Phone for all customers whose second and third
numbers (from the right) of their phone number are 56.

h. Determine the maximum and minimum sales Total.

I. Determine the average sales Total.

J. Count the number of customers.

K. Group customers by LastName and then by FirstName.

L. Count the number of customers having each combination of LastName and FirstName.

M. Show the LastName, FirstName, and Phone of all customers who have had an or-
der with Total greater than $100.00. Use a subquery. Present the results sorted by
LastName in ascending order and then FirstName in descending order.