Hello, I need help doing an ISMG3500 course. I need help creating this database in Microsoft Access. 

 

 

The database against which you’ll construct queries contains the following tables: 1 Clients (ClientID, CName, Address, City, State,
Zip, Phone)
ClientI
Addres
Stat
Phon
CName
City
Zip
D
s
e
e
1 John
111 5th Denver CO 8011 (720)
Smith St
1
111111
2 Mary
222 4th Aurora CO 8002 (720)
Johnso St
2
33n
4321
3 Alex
333 3rd Denver CO 8022
Kupres St
2
4 Max
444 2nd Glendal CO 8033 (303)
Smirnof St
e
3
33f
3333
5 Dana 555 1st Denver CO 8003 (720)
Renes St
3
111234 Items (ItemID, IName, Cost, AvailableQty,
Manufacturer) OrderItem
OrderID ItemID Quantity UnitPrice ItemTotal
1
1
1 $1,400.00 $1,400.00
2
1
1 $1,600.00 $1,600.00
2
3
2 $400.00 $800.00
3
2
1 $1,300.00 $1,300.00
4
1
1 $1,500.00 $1,500.00
4
3
1 $440.00 $440.00
4
4
3
$30.00
$90.00
4
5
3
$10.00
$30.00
5
1
2 $1,600.00 $3,200.00
6
4
7
$20.00 $140.00
6
5
5
$9.00
$45.00
7
2
1 $1,320.00 $1,320.00
7
3
1 $400.00 $400.00 Item
Available
IName
Cost
Manufacturer
ID
Qty
1 Laptop
$1,500.00
25 Sony
2 Desktop
$800.38
17 ViewSonic
3 Monitor
$150.50
19 ViewSonic
4 Keyboard
$10.17
43 Sony
5 Mouse
$6.33
37 Microsoft Order: (OrderID, ClientID, Order_Date,
PaymentType, PaidAmount, OrderTotal)
Order
Order Clien
ID
tId
1
2
3 1
3
2 4
5
6
7 4
1
1
5 Pay
ment
Type
1/1/2014 Visa
2/13/2013 Check
3/28/2013 Maste
rCard
4/12/2013 Visa
7/30/2013 Check
9/18/2013 Visa
11/3/2013 Visa
Order
_date Paid
Amount Order
Total $1,400.00 $1,400.00
$2,000.00 $2,400.00
$1,300.00 $1,300.00
$2,000.00 $2,060.00
$800.00 $3,200.00
$185.00 $185.00
$0.00 $1,720.00 OrderItem (OrderID, ItemID, Quantity,
UnitPrice, ItemTotal)
2 Shipments
ShipmentI Order PickUp
D
ID
Date
1
1 1/3/201
4
2
6 9/19/20
13
3
6 9/21/20
13
4
3 3/30/20
13
5
2 2/15/20
13
6
5 8/2/201
3
7
4 4/15/20
13
8
7 11/5/20
13 Ship
Price
$25.00 DaysTo
Deliver
3 $17.50 5 $27.40 3 $38.25 3 $45.07 5 $33.03 3 $27.48 4 $45.90 5 3 Use Query Design View to complete the following queries. Make sure your query would produce the
result as given in the hints. However, you cannot use the information given in the hints in the
construction of your query. Instead, use only information stated in the problem itself and use the hints
to verify that your query was constructed correctly.
Observe the following rules when constructing your queries: Use only necessary tables (points deducted otherwise). Use only information given in the questions (points deducted otherwise). For example, in
Query06, customer id is not given. So in constructing your query, do not use the criterion that
customerid is 1; instead, only use the criterion that customer name is John Smith. Display only those fields mentioned in the question (points deducted otherwise).
You must name your queries query01, query02 … query06…query14.—Points are evenly
distributed.
(01). For all orders placed after 11/1/2013, show orderId, order_date, clientId, client name, and all
batches of shipping information (shipprice and pick up date). Hint: correct query should return two rows
of data.
(02). Find all Orders that were paid with a payment type other than Check. Show OrderId, order date,
ClientId, and Payment Type for each such order. Hint: Correct query should return 5 rows of data.
(03). Find all orders that ordered a ViewSonic product and were paid with a Check. Show OrderId and
Orderdate for all such orders. (Hint: correct query should return 1 such order).
(04). Find the names of all customers who have ordered a laptop. Show clientid and client name for all
such customers. Hint: correct query should return 4 names, with two of them being the same.
(05). Find all orders that ordered a desktop. Show OrderId, OrderDate, and Quantity Ordered. Please
note that you can’t use ItemId =2 in the query because this information is not given to you in the
question itself. You’re given the information that IName is desktop, not ItemId is 2. Hint: correct query
should return 2 such orders.
(06). Show all shipments where the ship price is greater than $35. Show all shipment information.
(Hint: correct query should return three rows of data). (07). For all orders occurred during the first quarter of 2013 (i.e., from January 1 to March 31), show
orderId, clientId, Month of order, and Year of order. Hint: This will require Year function and Month
function. Correct query should return the following information:
Query07
OrderID ClientId MonthOfOrder YearOfOrder
2
3
2
2013
3
2
3
2013 (08). Show all orders occurred on the 28th of any month in 2013. Show order id. Hint: correct query
should return 3 such orders. (09). For all customers who have ordered a laptop, identify those orders that have an OrderTotal greater
than $3,000. Show customer name, order total and product name. Hint: Correct query obtain the
following output:
Query06
CName OrderTotal IName
JOHN SMITH $3,200.00 Laptop (10). Find the most recent Order_date, earliest Order_Date, highest order total, and lowest order total in
one query. Hint, correct query should return the following: (11). UsingOrderItem table, find the total revenue from each Item. (Hint: correct query should return the
following result. It’s Ok if your output columns are named differently from the output shown.)
Query11
ItemID total
1 $7,700.00
2 $2,620.00
3 $1,640.00
4 $230.00
5
$75.00 (12). How many laptops have been sold? Show this quantity. (Hint: correct result should return 5.)
(13) For products with a cost higher than 800, how many of them have been sold and how much revenue
have they brought in? (Hint: correct result should return 7 for quantity and $10,320.00 for revenue.) (14). Using Order table, find the total amount paid with a credit card (Visa, Mastercard). Show this total
only. (Hint: Correct result should return 4,885.00).
(Note, #13 and #14 may be removed depending on coverage on 2/6.)

• 1493858034-Homework 2.docx

ComputerScienceExpert

(11)

Status NEW Posted 04 May 2017 12:05 AM My Price 8.00

-----------

Attachments

file 1493858037-Solutions file 2.docx preview (51 words )

H-----------ell-----------o S-----------ir/-----------Mad-----------am ----------- Th-----------ank----------- yo-----------u f-----------or -----------you-----------r i-----------nte-----------res-----------t a-----------nd -----------buy-----------ing----------- my----------- po-----------ste-----------d s-----------olu-----------tio-----------n. -----------Ple-----------ase----------- pi-----------ng -----------me -----------on -----------cha-----------t I----------- am----------- on-----------lin-----------e o-----------r i-----------nbo-----------x m-----------e a----------- me-----------ssa-----------ge -----------I w-----------ill----------- be----------- qu-----------ick-----------ly -----------onl-----------ine----------- an-----------d g-----------ive----------- yo-----------u e-----------xac-----------t f-----------ile----------- an-----------d t-----------he -----------sam-----------e f-----------ile----------- is----------- al-----------so -----------sen-----------t t-----------o y-----------our----------- em-----------ail----------- th-----------at -----------is -----------reg-----------ist-----------ere-----------d o-----------n -----------THI-----------S W-----------EBS-----------ITE-----------. ----------- Th-----------ank----------- yo-----------u -----------

Not Rated(0)

• Buy Answer