SQL Exercise (Due 2/22/2016 at the end of class) Using Microsoft Access and the Northwind sample dat

SQL Exercise (Due 2/22/2016 at the end of class)

 

Using Microsoft Access and the Northwind sample database, produce an SQL statement for each of the 4 exercises below. For each exercise you should submit both the SQL statement you have written along with a screen shot of the results. 

*Important – Do Not Use the Query Wizard as these are exercises in learning to write SQL.  It will be apparent in the syntax if you completed the assignment with the Query Wizard.  Please practice honesty and integrity as you complete the assignment.

 


 

#1)  Number of Orders with Credit Card Payment

Write a SQL statement that will count the number of orders in the [Order Details] table that were paid by credit card. 

Example:

SELECT [field], count(1)

FROM [table]

WHERE [field] = “value”

GROUP BY [field]

 


 

#2)  Top Selling Product based on Order Amount (One Join)

Write a SQL statement that will join the [Order Details ] table and [Products] table to determine the top selling product based on [Quantity]*[Unit Price].

Example:

SELECT B.[field], sum(A.[field]*A.[field]) as Orders

FROM ( [table] A

INNER JOIN [table] B

on A.[field] = B.[field] )

GROUP BY B.[field]

ORDER BY sum(A.[field] *A.[field] ) desc

 

 


 

#3)  Products with Less Than $2500 in Orders

Write a SQL statement that will join the [Order Details] table and [Products] table to determine only those products with less than $2500 in Orders based on [Quantity]*[Unit Price]. 

Example:

SELECT B.[field], sum(A.[field] * A.[field]) as Orders

FROM ([table] A

INNER JOIN [table] B

on A.[field] = B.[field])

GROUP BY B.[field]

HAVING sum(A.[field]*A.[field]) value

ORDER BY sum(A.[field] *A.[field]) desc

 


 

#4) Top Producing Employee base on Order Amount (Two Joins)

Write a SQL statement that will join the [Orders] table, [Order Details] table and [Employees] table to determine the highest producing employee based on the employees first name and [Quantity]*[Unit Price].

 

Example:

SELECT C.[field], sum(A.[field] *A.[field] ) as Orders

FROM (([table] A

INNER JOIN [table] B

on A.[field] = B.[field])

INNER JOIN [table] C

on B.[field] = C.[field])

GROUP BY C.[field]

ORDER BY sum(A.[field] *A.[field] ) desc

 

 

 

 

 

 

"Looking for a Similar Assignment? Get Expert Help at an Amazing Discount!"

Assume that the sample is a representative of the population of CMSU. Based on the data, answer…

The Student News Service at Clear Mountain State University (CMSU) has decided to gather data about the undergraduate students that attend CMSU. CMSU creates and distributes a survey of 14 questions and receives responses from 62 undergraduates (stored in theSurvey.csv file).

Part I

· 2.1. For this data, construct the following contingency tables (Keep Gender as row variable)
2.1.1. Gender and Major
2.1.2. Gender and Grad Intention
2.1.3. Gender and Employment
2.1.4. Gender and Computer

· 2.2. Assume that the sample is a representative of the population of CMSU. Based on the data, answer the following questions:
2.2.1. What is the probability that a randomly selected CMSU student will be male?
What is the probability that a randomly selected CMSU student will be female?
2.2.2. Find the conditional probability of different majors among the male students in CMSU.
Find the conditional probability of different majors among the female students of CMSU.
2.2.3. Find the conditional probability of intent to graduate, given that the student is a male.
Find the conditional probability of intent to graduate, given that the student is a female.
2.2.4. Find the conditional probability of employment status for the male students as well as for the female students.
2.2.5. Find the conditional probability of laptop preference among the male students as well as among the female students.

· 2.3. Based on the above probabilities, do you think that the column variable in each case is independent of Gender?
Justify your comment in each case.

Part II

· 2.4. Note that there are three numerical (continuous) variables in the data set, Salary, Spending and Text Messages. For each of them comment whether they follow a normal distribution.
Write a note summarizing your conclusions.
[Recall that symmetric histogram does not necessarily mean that the underlying distribution is symmetric]

 

"Looking for a Similar Assignment? Get Expert Help at an Amazing Discount!"

New ventures are subject to periodic introspection as to whether they should continue or liquidate..

New ventures are subject to periodic introspection as to whether they should continue or liquidate. Explain the types of information you would expect to gather and how they would be used in each stage to aid an entrepreneur’s approach to the venture’s future.

New ventures are subject to periodic introspection as to whether

 

"Looking for a Similar Assignment? Get Expert Help at an Amazing Discount!"

PSY 340 Week 5 Write a 200- to 300-word reflection describing brain liberalization and its role… 1 answer below »

PSY 340 Week 5 Writea 200- to 300-word reflection describing brain lateralization and its role in language.

Providean example of language disruption as a result of brain trauma.

Includeone to two peer-reviewed sources.

Formatyour paper consistent with APA guidelines.

Attachments:

 

"Looking for a Similar Assignment? Get Expert Help at an Amazing Discount!"

Explain the advantages of a trade agreement and how it might impact a company like red fish-blue fis

Explain the advantages of a trade agreement and how it might impact a company like red fish-blue fish? What makes the demographics of the Asian countries attractive for future trade development? What challenges would red fish-blue fish likely experience?

 

"Looking for a Similar Assignment? Get Expert Help at an Amazing Discount!"

Project requirements brief can be derived from Objectives and Scope of Work section of the RFP…. 1 answer below »

Task -a) Project requirements brief can be derived from Objectives and Scope of Work

section of the RFP. A requirement brief is usually an itemized list of the client’s

requirements derived from the RFP.

Task -b) Wireless LAN design for a typical floor, which includes the following information:-

1. Wireless computer desktop layout plan with network topology for a typical

floor level.

2. Wireless Access Point location on a typical floor, vertical cable paths and

lengths between the floors. Switch/Access Point functional specifications.

Assume the floor to ceiling height as 3 meters approximately including the

false ceiling.

Task -c) Recommendation and justification of the structured backbone cabling, Access

Point (AP), Switches i.e. distribution layer, building backbone cabling and media

type to be used for a typical floor level and for the connection across the road to

the existing LAN.

Task -d) List of equipment and the wireless specifications, technical and cabling

specifications. Note: Specifications/Technical details should be included in the

Appendix.

Task -e) You need to outline design rules for a computer WLAN & WAN network and

describe it in detail. Your description should include:

Attachments:

 

"Looking for a Similar Assignment? Get Expert Help at an Amazing Discount!"

Universal Product attributes Discuss four of the common “universal product attributes” that most cus

Universal Product attributes

Discuss four of the common “universal product
attributes” that most customers want in most industries

 

"Looking for a Similar Assignment? Get Expert Help at an Amazing Discount!"

What are Romeo’s and Juliet’s reactions when they discover they have fallen in love with their famil

What are Romeo’s and Juliet’s reactions when they discover they have fallen in love with their family’s enemy?

 

"Looking for a Similar Assignment? Get Expert Help at an Amazing Discount!"

What is the probability that a 5-card poker hand has the following? (a) A Royal Flush, i.e. an…

What is the probability that a 5-card poker hand has the following?

(a) A Royal Flush, i.e. an Ace, King, Queen, Jack and Ten of the same suit.

(b) A Flush, i.e. any 5 cards of the same suit.

(c) 4 aces.

(d) 4 of a kind, i.e. 4 cards of the same value, e.g. 4 Eights or 4 Queens.

(e) A Straight, i.e. a set of 5 consecutive values.

(f) A Straight that is not a Flush.

(g) The same number of hearts and diamonds.

(h) More spades than hearts.

 

 

"Looking for a Similar Assignment? Get Expert Help at an Amazing Discount!"