you have questions or problems for the coding.
Plan going forward from today, Wed. 11/19:
● Work on remaining SQLs: #24 … checked against
Table at #22.
Queries for numbers 2328 are created but still need to be incorporated into the
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.
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 MSPatientDataWorksheet 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
End of semester WaaHoo!!!
Friday Nov. 28th
As of 3:30pm, Friday Nov. 28th, all queries/statements in Part II have been checked and
verified at least once.
POST A QUESTION or ANSWER ONE, BELOW HERE
**Prob 1 – List all rows and columns for the complete ORDERS table
**Prob 2 – List the order number for each order placed by customer number 148 on
WHERE orders.order_date = (select DATE('20071020'))
and customer_num = 148
**Prob 3 – List the part number and part description of each part that is not in item class SG
FROM part p
WHERE p.class not in('SG')
**Prob 4 – List the part number, part description, and onhand value (units on hand * unit
price) of each part in item class AP. Assign the Name on_hand_value to the computation
(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.
FROM Part p
WHERE p.class in ('AP', 'SG')
**Prob 6 – List all details about all parts. Order the output by part description.
ORDER BY description
**Prob 7 – How many customers balances that are more than their credit limits?
SELECT COUNT( customer_num) as Number_Customers
WHERE balance > credit_limit
**Prob 8 – List the part number, part description, and onhand quantity of each part whose
number of units on hand is more than average number of units on hand for all parts.
part_num, description, sum(on_hand) on_hand
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
part_num, description, max(price) price
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.
rep_num, sum(balance) total_customer_balance
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…