THE RELATIONAL MODEL 2: SQL - Concepts of Database Management - page 112

EXAMPLE 24

List the number and name of each customer together with the number, last name, and first name of the sales
rep who represents the customer. Order the records by customer number.

112

SELECT CustomerNum, CustomerName, Rep.RepNum, LastName, FirstName
FROM Customer, Rep
WHERE Customer.RepNum=Rep.RepNum
ORDER BY CustomerNum
;

EXAMPLE 25

List the number and name of each customer whose credit limit is $10,000 together with the number, last
name, and first name of the sales rep who represents the customer. Order the records by customer number.

SELECT CustomerNum, CustomerName, Rep.RepNum, LastName, FirstName
FROM Customer, Rep
WHERE Customer.RepNum=Rep.RepNum
AND CreditLimit=10000
ORDER BY CustomerNum
;

EXAMPLE 26

For every order, list the order number, order date, customer number, and customer name. In addition, for
each order line within the order, list the part number, description, number ordered, and quoted price. Order
the records by order number.

SELECT Orders.OrderNum, OrderDate, Customer.CustomerNum,
CustomerName, Part.PartNum, Description, NumOrdered, QuotedPrice
FROM Orders, Customer, OrderLine, Part
WHERE Customer.CustomerNum=Orders.CustomerNum
AND Orders.OrderNum=OrderLine.OrderNum
AND OrderLine.PartNum=Part.PartNum
ORDER BY Orders.OrderNum
;

EXAMPLE 27

List the number and name of all customers that are represented by sales rep 35 or that currently have orders
on file or both.

SELECT CustomerNum, CustomerName
FROM Customer
WHERE RepNum= ' 35 '
UNION
SELECT Customer.CustomerNum, CustomerName
FROM Customer, Orders
WHERE Customer.CustomerNum=Orders.CustomerNum
;