SophiaPretty

(5)

$14/per page/Negotiable

About SophiaPretty

Levels Tought:
Elementary,Middle School,High School,College,University,PHD

Expertise:
Accounting,Algebra See all
Accounting,Algebra,Applied Sciences,Architecture and Design,Art & Design,Biology,Business & Finance,Calculus,Chemistry,Communications,Computer Science,Economics,Engineering,English,Environmental science,Essay writing Hide all
Teaching Since: Jul 2017
Last Sign in: 304 Weeks Ago, 6 Days Ago
Questions Answered: 15833
Tutorials Posted: 15827

Education

  • MBA,PHD, Juris Doctor
    Strayer,Devery,Harvard University
    Mar-1995 - Mar-2002

Experience

  • Manager Planning
    WalMart
    Mar-2001 - Feb-2009

Category > Computer Science Posted 20 Nov 2017 My Price 10.00

data scientist for Acme Music, a large music store.

Question:Case: SQL for Acme Music

You are the data scientist for Acme Music, a large music store. Acme Music specializes in selling musical instruments to jazz musicians. Over time, Acme Music has created a robust customer database and saved it in a SQL database.

 

See the associated dataset for the case, "DataScience_6_Case_AcmeMusic.xls". The screenshot below shows a portion of the data.

 

Untitled.png

 

In order to learn more about your customers and their purchases, you plan to query your SQL database.

 

For the questions below, specify the SQL statements necessary to obtain the information required. Use ANSI SQL. Refer to the name of the database as "AcmeMusic". You do not need to write out the results, or show a screenshot of them. Simply indicate the SQL command relevant to the data desired.

 

1. SELECT: To prepare for the deep dives into the customer database, you will need some practice. Practice using the SELECT command by executing the following statements.Using SQL commands, answer the below:

 

a. SQL statement to display the last name and the State for each customer in the database.

 

b. SQL statement to display all the customers who purchased "Trumpet" as a product.

 

c. SQL statement to display the customers born in the State of New York.

 

2. SQL TABLE: Acme Music has asked you to show a new table. The table is to record purchases by customer. The table is to contain the columns indicated below. Specify the SQL statement to create the table.

·        ID (capable of holding 8 digits)

·        Date (capable of holding date data)

·        Product (capable of holding a variable-length character string of up to 20 characters)

·        Price (capable of holding a number with a maximum number of digits of 10, and 2 digits to the right of the decimal)

SQL Statement:

 

3. UPDATE: Acme Music has asked you to make some updates to the customer database. Specify the SQL commands required to perform the following updates.

a.    Change the name of "Armstrong", "Louis" to "Armstrong", "Satchmo".

b.    Change the type of product used by Jimmy Smith from "Organ" to "Organ, Hammond".

c.    It turns out that one of the clerks did not enter in the service sales amounts correctly. Add $10 to all of the amounts in "Sales, Service".

SQL Statements:

Commentary: What is a problem with the way the sales amounts are specified in the dataset? How would we benefit from having separate "Customers" and "Purchases" tables?

 

Include research: How do small to mid-sized companies keep track of their customers? What type of database system do they use?

Attachments:

Answers

SophiaPretty
(5)
Status NEW Posted 20 Nov 2017 01:11 PM My Price 10.00

-----------  ----------- 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

Not Rated(0)