I have an Oracle Problem. Well. I have done 80%, but I am unsure about the two l

Question

I have an Oracle Problem. Well. I have done 80%, but I am unsure about the two last questions. Can you please revise my work and helo me finish it?

Thank you

Here is the task:

******************************************************************************************************************************

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 dates12/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 companymore 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)

******************************************************************************************************************************

Here is my answer:

******************************************************************************************************************************

SELECT * FROM stock;

SELECT lname, fname, company FROM CUSTOMER Order BY company;

SELECT company from CUSTOMER where city IN ('Sunnyvale',
'Redwood City', 'San Francisco');

SELECT order_num, order_date, customer_num, ship_date,
paid_date from ORDERS where order_date
Between '31-DEC-1999' AND '03-JAN-2000';

SELECT order_num, order_date, ship_charge from ORDERS
where BACKLOG = 'n' AND ship_charge > '15';

SELECT * FROM STOCK where description
LIKE '%baseball%' AND unit_price>'200' AND
manu_code LIKE 'H%';

SELECT DISTINCT customer_num, company
FROM CUSTOMER NATURAL JOIN ORDERS;

SELECT DISTINCT customer_num, description from ORDERS
NATURAL JOIN STOCK
where customer_num BETWEEN '104' AND '108'
ORDER BY customer_num, description;

SELECT distinct customer_num "Total_Customers_with_Orders"
from ORDERS;

SELECT DISTINCT customer_num, company
FROM CUSTOMER NATURAL JOIN ORDERS;

Explanation / Answer

For the 9th question, the query goes as follows:-

SELECT COUNT ( DISTINCT customer_num) AS Total_Customers_with_Orders FROM ORDERS WHERE order_num IS NOT NULL.

OR

SELECT COUNT ( DISTINCT customer_num) AS Total_Customers_with_Orders FROM ORDERS WHERE order_num != " ".

The count keyword as name suggests will return the count of total customers, 'DISTINCT' will make sure that no customer is counted twice or no customer is selected again. 'AS' will give the property(number of distinct customers) an Alias and finally, order_num IS NOT NULL will make sure there exists atleast an order corresponding to selected customer_id.

For 10th question.

SELECT customer_num, SUM(order_id), SUM(quantity), SUM(total_price) FROM ORDERS GROUP BY customer_num ORDER BY customer_num.

The GROUP BY will help in grouping all records by customer_num.

The ORDER BY will help in arranging the records according to ascending order of customer_num.

And finally, the SUM will add all the orders , add all the quantity and the add all the total prices corresponding to orders grouped by or belonging to a particular customer_number.

I really appreciate your spirit. You have genuinely asked your problem and unlike others you have not posted the whole assignment for solution. Well tried Champ!!! Always keep this attitude...That will surely help you learn and grow.

Hope the solution will help you...Thanks!!! Do give it a thumbs up!!!!

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

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