Databases Reference
In-Depth Information
4.
What is a computed field? How can you use one in an SQL query? How do you assign a name to a computed field?
5.
How do you use the LIKE and IN operators in an SQL query?
6.
How do you sort data in SQL? When there is more than one sort key, how do you indicate which one is the major
sort key? How do you sort data in descending order?
113
7.
What are the SQL built-in functions? How do you use them in an SQL query?
8.
What is a subquery? When is a subquery executed?
9.
How do you group data in SQL? When you group data in SQL, are there any restrictions on the items that you
can include in the SELECT clause? Explain.
10.
How do you join tables in SQL?
11.
How do you qualify the name of a field in an SQL query? When is it necessary to do so?
12.
How do you take the union of two tables in SQL? What criteria must the tables meet to make a union possible?
13.
Describe the three update commands in SQL.
14.
How do you save the results of an SQL query as a table?
In the following exercises, you will use the data in the Premiere Products database shown in Figure 2-1 in Chapter 2. (If
you use a computer to complete these exercises, use a copy of the original Premiere Products database so you will still have
the original data when you complete Chapter 4.) In each step, use SQL to obtain the desired results.You can use a DBMS
to complete the exercises using a computer, or you can simply write the SQL command to complete each step. Check
with your instructor if you are uncertain about which approach to take.
1.
List the number and name of all customers.
2.
List the complete Part table.
3.
List the number and name of every customer represented by sales rep 35.
4.
List the number and name of all customers that are represented by sales rep 35 and that have credit limits of
$10,000.
5.
List the number and name of all customers that are represented by sales rep 35 or that have credit limits of
$10,000.
6.
For each order, list the order number, order date, number of the customer that placed the order, and name of
the customer that placed the order.
7.
List the number and name of all customers represented by Juan Perez.
8.
How many orders were placed on 10/20/2010?
9.
Find the total of the balances for all customers represented by sales rep 35.
10.
Give the part number, description, and on-hand value (OnHand * Price) for each part in item class HW.
11.
List all columns and all rows in the Part table. Sort the results by part description.
12.
List all columns and all rows in the Part table. Sort the results by part number within item class.
13.
List the item class and the sum of the number of units on hand. Group the results by item class.
14.
Create a new table named SportingGoods to contain the columns PartNum, Description, OnHand, Ware-
house, and Price for all rows in which the item class is SG.
15.
In the SportingGoods table, change the description of part BV06 to “Fitness Gym.”
16.
In the SportingGoods table, delete every row in which the price is greater than $1,000.
Ray Henry is very aware of the importance of the SQL language in database management. He realizes that he can
use SQL to perform the same functions that you performed with queries in Chapter 2. In each of the following steps, use
SQL to obtain the desired results using the data shown in Figures 1-17 through 1-20 in Chapter 1. (If you use a com-
puter to complete these exercises, use a copy of the original Henry Books database so you will still have the origi-
nal data when you complete Chapter 4.) You can use a DBMS to complete the exercises using a computer, or you can
