ITSE 2309 Distant Learning LAB #1 Sample Distant Learning Database and SQL Queri

Question

ITSE 2309

Distant Learning LAB #1

Sample Distant Learning Database and SQL Queries

Submit: Query Statements

Query Output

Do the following queries:

1. List all columns and rows in the stock table.

2. List the last name, first name, and company of all customers (List the columns in that

order). Place the list in alphabetical order by company name.

3. List the company names for all customers from Sunnyvale, Redwood City, or San

Francisco.

4. List all orders that were placed between the dates 12/31/1999 and 01/03/2000. List

order number, order date, customer number, ship date, and paid date. (Hint: Specify

year in single quotes DD-MMM-YYYY‘)

5. List the order number, order date, and shipping charges for all orders that are not on

backlog and for which the shipping charge is over $15.00.

6. List all stock items which are baseball items which have a unit price greater than

$200.00 and a manufacturer code which starts with H‘. (Hint: use LIKE)

7. List the company name for all customers who have orders. Don not list a company

more than once.

8. List the customer number and the description (from the stock table) of all items

ordered by customers with customer numbers 104 – 108. Order the output by

customer number and description. (There should be no duplicate rows in your output).

9. List the number of (distinct) customers having an order. Label the column

Total_Customers_with_Orders.

10. For each customer having an order, list the customer number, the number of orders

that customer has, the total quantity of items on those orders, and the total price for

the items. Order the output by customer number. (Hint: You must use a GROUP BY

clause in this query).

Explanation / Answer

[1]
SELECT * FROM stock;

[2]
SELECT first_name,last_name,company_name
FROM stock
ORDER BY company_name DESC;

[3]
SELECT company_name
FROM stcok
WHERE city_name IN ('Sunnyvale','Redwood City','San Francisco');

[4]
SELECT order number, order date, customer number, ship date, paid date
FROM stock
WHERE order date BETWEEN '12-MON-1999 AND -01-MAR-2000';

[5]

SELECT order number, order date, shipping charges
FROM stock
WHERE shipping charges < 15

[6]

SELECT *
FROM stock
WHERE price > 200 AND manufacturer code like 'H%';

[7]

SELECT Distinct company name , count(order id)
FROM stock
group by company name
having count(order id ) > 1;

[8]
SELECT DISTINCT customer number , description
FROM stocks
WHERE customer number BETWEEN 104 AND 108
ORDER BY customer number , description

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.