Your Problems Essay

Submitted By Nick-sem
Words: 2305
Pages: 10

Respond here if you have gotten the invite. This is where you will do put your problems or if
you have questions or problems for the coding.



Plan going forward from today, Wed. 11/19:

● Work on remaining SQLs: #24 … checked against
NONAPPLIANCE
Table at #22.
Queries for numbers 23­28 are created but still need to be incorporated into the
NONAPPLIANCE
Table at #22, then checked for accuracy by running the VIEW
command at #28..
● Work on Part I ­ Look below Part II, for postings for Part I. See if any commonalities in
two posted models. We need to agree on a 1NF mapping. This will probably be most
difficult part since we are unable to discuss in person.

Happy Thanksgiving!

IMPORTANT DATES:
Friday 6pm, 11/28
­ Online meeting, HERE, to discuss remainder of project; what’s left to do,
issues… USE CHAT (or SKYPE?) TO COMMUNICATE.

Sunday 6pm, 11 /30
­ Target Date for completion Part I and Part II.

Wednesday, Dec. 3rd by 11:00pm
Deliverables due in Dropbox,

Part I ­ ERD diagram and script to create our MS­Patient­Data­Worksheet Database. (Note:
we do not need to submit our (PPT) presentation to dropbox.)

Part II ­ All SQL queries.
Group Project Part I and II Submitted Wednesday 12/3

Chat meeting here 6pm, Sunday 12/7

Sunday 12/7 ­ Chat meeting here 6pm


Wednesday, Dec. 10th
­ In class Presentation

Final Exam

End of semester ­ WaaHoo!!!

Bill


Friday Nov. 28th

As of 3:30pm, Friday Nov. 28th, all queries/statements in Part II have been checked and
verified at least once.

Bill


POST A QUESTION or ANSWER ONE, BELOW HERE




Thanks Jake!


**Prob 1 – List all rows and columns for the complete ORDERS table

SELECT *
FROM Orders

**Prob 2 – List the order number for each order placed by customer number 148 on
10/20/2007

SELECT order_num
FROM Orders
WHERE orders.order_date = (select DATE('2007­10­20'))

and customer_num = 148

**Prob 3 – List the part number and part description of each part that is not in item class SG

SELECT part_num,
description
FROM part p
WHERE p.class not in('SG')

**Prob 4 – List the part number, part description, and on­hand value (units on hand * unit
price) of each part in item class AP. Assign the Name on_hand_value to the computation

SELECT part_num,
description,
(p.on_hand * price) as on_hand_value

FROM Part p
WHERE p.class in ('AP')

**Prob 5 – Use the IN operator to list the part number and part description of each part in item
class AP or SG.

SELECT part_num,
description
FROM Part p
WHERE p.class in ('AP', 'SG')

**Prob 6 – List all details about all parts. Order the output by part description.

SELECT *
FROM Part
ORDER BY description

**Prob 7 – How many customers balances that are more than their credit limits?

SELECT COUNT( customer_num) as Number_Customers
FROM Customer
WHERE balance > credit_limit

**Prob 8 – List the part number, part description, and on­hand quantity of each part whose
number of units on hand is more than average number of units on hand for all parts.

select
part_num, description, sum(on_hand) on_hand
from part
where on_hand > (select avg(on_hand) from part)
group by part_num;

**Prob 9 What is the part number, description, and price of the most expensive part in the
database?

select
part_num, description, max(price) price
from
part

10. List the sum of the balances of all customers for each sales rep, but restrict the output
to those sales reps for which the sum is more than $10,000.


select
rep_num, sum(balance) total_customer_balance
from
customer c
group by rep_num
having Total_Customer_Balance > 10000




11. List the part number of any part with an unknown description.

select part_num from part
where description is null

12. For each order placed on October 21, 2007, list the order number along with the number
and name of the customer that placed the…