For the Database Course. Write statements using NotePad a. For each order, list
ID: 3811172 • Letter: F
Question
For the Database Course.
Write statements using NotePad
a. For each order, list the order number, orderdate, part number, part description, and item class for each part that makes up the order. The output shuold be sorted by class in descending order.
b. For each order, list the order number and order date along with the number and name of customer who placed the order.
c. LIst the order number along with the number and name of the customers who did not place an order on "October 23, 2007"
d. Create a view that consists of the number, name, address, balance, and credit limit of all customers with credit limits that are greater than or equal to $10,000.
e. Create a view that consists of the rep number, rep last name, rep first name, customer number, and customer name.
Explanation / Answer
[1]
SELECT order number, orderdate, part number, part description, and item class
FROM order
ORDER BY item class
[2]
SLECT a.ordernumber,a.orderdate,b.name.b.number
FROM order a JOIN customer b
WHERE a.customerid = b.customerid
[3]
SLECT a.ordernumber,b.name.b.number
FROM order a JOIN customer b
WHERE a.customerid = b.customerid
AND a.orderdate != '23-oct-2007';
[4]
CREATE VIEW details AS
SELECT a.number.a.name,a.address,a.balance,a.limit
FROM customer a
WHERE a.limit >= 10000;