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. 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 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 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
**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
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 SELECT part_num, description
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 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 *
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. 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