A productivity index of 110% means that
A productivity index of 110% means that a company’s labor costs would have been 10% higher if it had not made production improvements. Assume that Baldwin had a productivity index of 112% and that Chester had a productivity index of 103%. Now refer to the Income Statements in the Annual Report for Baldwin and Chester. Using the labor costs shown in the Income Statements, how much more did Baldwin save in direct labor costs compared to Chester by having a higher productivity index?
Select: 1
$3,340
$3,499
$3,181
$2,863
I am in need of some one to take my COMP-XM examination for me
I am in need of some one to take my COMP-XM examination for me
[SOLVED] example of company performance analysis. select
[SOLVED] example of company performance analysis. select two companies and compare their financial structures including WACC, book value, and market value.
United States steel is a steel production company located in Pittsburgh, Pennsylvania. The company was founded in 1901. The company area of operation includes the USA and central Europe. The company generated revenue of 10 billion in 2016 financial year. Facebook, on the other hand, is a social media platform founded in 2004. The company is based in Menlo Park California. The company generated a revenue of 27 billion 2016 financial year. These companies operate in different industries. Therefore, the underlying conditions that influence the earning of companies may be totally different. Besides, their assets and expenses are totally different. A company like Facebook invest most on research and development while a company like US steel invest heavily on plant and equipment.
United States steel (X) have 176,000,000 outstanding shares while Facebook have 2,930,000,000 outstanding shares. The price per share for United States steel (X) is 35.48 USD and for Facebook (FB) is 161.89 USD. Therefore, the value of United States Steel (X) is 6,244,480,000 USD and for Facebook (FB) is 474,337,700,000 USD (Yahoo, 2019). The book value of the company is calculated by taking the total asset value less total liability. Therefore the book value of each company is as calculated below
[SOLVED] UMGC Data 620 Assignment 7.2Your name:
[SOLVED] UMGC Data 620 Assignment 7.2
Your name:
Date:
Each student runs the Python development environment Jupyter Notebook! Using the browser from the computer, prints “hello world” and writes a Celsius to Fahrenheit converter.
Turn in separate document(s) with the following items. Please label each with the TURN IN # statement so we can easily follow it.
Deliverable
Description
Points
TURN IN #1
Turn in a screen shot of your successful editor Jupyter Notebook.
1
TURN IN #2
Turn in screen shot(s) showing “hello world” program execution with your name as the input string.
1
(You can put TURN IN #1 and TURN IN #2 in the same Word document.)
TURN IN #3
Your Python code to perform a Celsius to Fahrenheit conversion. Name your python file “XXXX-temp.ipynb” where XXXX is your name. Your deliverable here is just the Python notebook; you don’t need to include screen shots. (Note there is a Rubric for Python programs (located also in this week’s materials) for how we will grade this.)
8
TOTAL
10
The following walkthrough exercises from “Python for Everybody – Exploring Data Using Python 3” by Charles R. Severance are included here:
Chapter 1: Exercises 5, 7, 9
Chapter 2: Exercises 2, 3, 4
You are encouraged to walk through these exercises. To do this, you should type the commands into your computer and make sure they give the same results as here.
Browsing the Python Jupyter IDLE (Integrated Development Environment), writing and running a program:
Try Jupyter out without installing anything.
Step 1:
Go to https://jupyter.org/try
Sometimes the screen will look slightly different, like this below. In that case, you need to “Try Classic Notebook”
You will get binder starting Python IDE (give it some seconds), then you will see this screen below:
Step 2:
Figure 1 into My Content area.
Figure 1: Nashville Housing Data location
o Navigate to My content and make sure that you see a copy of the Nashville housing data.
o Select Create data module from action menu in Figure 2.
Figure 2: Create data module
o The new data module will open. Click on an arrow next to save icon in Figure 3 and select save as to save new data module into my content.
Figure 3: Save Data module
o Click on Nashville housing data csv in the left panel in Figure 4 to preview data.
Figure 4: Preview data
b) Handle Missing data
Cognos Analytics represents missing data as Null
Apply the approaches in the assigned readings to handle missing data, including filtering rows, removing columns, and replacing the missing value with the average. Discuss the approaches you used in your paper.
c) Handle irrelevant variables – Which variables in the data are irrelevant? Explain why they are irrelevant and how you handle them.
Make sure to save the data module before moving on to the next part of the assignment.
4. Explore the dataset:
Return to the Welcome page and expand My Content
Select Create exploration from the action menu of the Nashville housing data module in Figure 5.
Figure 5: Create Exploration
a) Are there outliers? If so, what fields have outliers and what do you recommend as solutions?
b) Pose an initial set of questions to use for data exploration. Provide any insights gained from using Cognos Analytics with this dataset
a) Develop new specific questions which provide additional insights into and answer specific questions from the dataset. Discuss how these insights could be useful. Discuss how you would improve the relevancy.
b) Develop and explain at least five different visualizations. Experiment with the available options and summarize the results. Provide insights to what the visualizations show.
c) Utilize features (e.g., filters, comparisons) with the visualizations to uncover and explain interesting aspects of the data set.
d) Create and explain at least one insightful calculation. Discuss why this would be useful.
Submission
Each student will submit a single document conforming to the guidelines and standards outlined below.
Document format:
§ limited to 8 pages (excluding title page, references, and appendix),
§ Double-spaced, 12 point Times New Roman font, 1” margins, Bottom-right page numbering.
Note: Submitted report must be either in MS Word or PDF format and titled: “Assignment2_LastName”.
Only one document will be allowed to be submitted.
Content (note that the document must have clearly marked sections for the items listed below)
1) Title page (1 page limit): course number and term, assignment number and project title, student name and contact information, instructor’s name. Format it so it looks pleasant and presentable. Follow formatting guidelines above.
2) Introduction. Provide a brief outline of the dataset you are using for this assignment. Briefly describe the content of the data. Include a screenshot of the data (not all, but partial as far as all relevant variables are visible).
3) Data preparation – handling missing data, removing irrelevant variables
4) Data exploration process. Explain and discuss what data exploration you performed (e.g., questions generated about the data set content). Include any specific ideas or suggestions as to how this could be used in your organization.
5) Visualizations created. Explain the visualizations created. Include the value-added aspects of the visualizations. Include creative aspects for increasing potential for higher assignment grade.
6) Calculation which adds insights or value to the data set. Include and explain the value of the calculation, i.e., insights provided by the calculation.
7) References (1 page limit): List all references in APA format used in preparing this report. It is strongly recommended to use outside knowledge in setting-up the analysis or discussing the results where possible.
8) Appendix (4 page limit):
a) Appendix A: Include any appropriate workbooks and/or screenshots (figures, tables, diagrams) used in this assignment. Make sure all tables, figures, or diagrams are properly numbered and titled. For example, “Table 1. Model Results”. Make sure all tables or figures or diagrams are easily readable and visually presentable.
General guidance
§ Assignments that: 1) adequately address all required tasks; 2) are submitted on time; 3) are properly formatted (APA format for references, no typos or misspelled words, no grammar errors, cover page, etc.) will receive a grade of B (80-89, depending on content).
§ In order to increase (but not guarantee) your chances of receiving a higher grade, you need to show clear evidence of critical thinking. Critical thinking can take many forms, depending on the type of assignment. In some instances, showing greater depth (e.g., such as creating more models, looking at more than one insightful fact or relationships, and comparing them on key criteria) is one method for providing evidence of critical thinking. In other cases, it might include providing more explanation to include the pros and cons of the approach used or the arguments in favor and against the proposal as well as some criteria for choosing among the alternatives. Still another example would be providing significant insights as to how the assignment outcome would benefit (or would meet resistance) in your organization and what steps might be employed to facilitate acceptance. Certainly, this is not a complete list, but gives some examples of critical thinking aspects.
Part 1 - Account access verificationscreenshot of
Part 1 - Account access verification
screenshot of your user information
Part 2 – Upload Data
screenshot of insurance data in My Content
Part 3 - Data Exploration
Variable Properties
How many variables are in the insurance data?
7
What is the datatype of Age?
Integer
What is the datatype of Charges?
decimal
What is the usage type of an Age column?
measure
What aggregation types are available for the Smoker column?
total
Explore the Relationships
What is the relationship strength between smoker and charges?
75%
What is the relationship strength between age and charges?
64%
What does the relative width of connecting line represent?
Strength of relationship
Which variables do not have a direct relationship with charges?
BMI and region
Spiral Diagram
Screenshot of the spiral diagram and the list of drivers
What driver has the highest predictive strength? What is the predictive strength?
BMI, smoker, and age is predictor of charges with predictive strength of 83%
What driver information do you see in the tooltip?
Predictive strength and composition of drivers
Which variables in insurance data are not on the spiral diagram for charges?
Sex, children, region and insuranceclaim
Summary Visualization and Using Filters
Use the summary visualization to return the average age. Take a screenshot of the visualization and paste it into the answer sheet.
Edit the summary visualization to return the minimum age. Take a screenshot of the visualization and paste it into the answer sheet.
Edit the summary visualization to show an average age of an individual with 5 children. Take a screenshot of the visualization and paste it into the answer sheet.
Edit the summary visualization to show an average age of an individual with 5 children who is a smoker (smoker=1). Take a screenshot of the visualization and paste it into the answer sheet.
Comparison Visualization of Your Choice
Visualization Screenshot
Explain the visualization purpose and summarize the insights.
1. What are the hurdles keeping your
1. What are the hurdles keeping your organization from improving its AQ? What steps would you recommend to help improve the AQ?
1. What is your organization's Analytical Quotient
1. What is your organization's Analytical Quotient (AQ)?
1. Where do you see the biggest
1. Where do you see the biggest hurdles in adopting a more 'evidence-based' approach?
1. Which approach (Davenport or CRISP-DM) would
1. Which approach (Davenport or CRISP-DM) would work better in your organization?
1. Which approach (Davenport or CRISP-DM) would work
1. Which approach (Davenport or CRISP-DM) would work better in your organization?
WK 3 – DATA 610 6/3/20 – DISCUSSION Questions/answers
1. Which methodology (Davenport or CRISP-DM) do you prefer, and why?
1. Generate an "analytics requirements
1. Generate an "analytics requirements document"
1. Complete the model (flesh out a
1. Complete the model (flesh out a full model of the decision to include decomposing the decision, refining the needed information sources for the analysis, refining authorities)
1. Specify the decision requirements (input requirements,
1. Specify the decision requirements (input requirements, analytic requirements, other knowledge about the decision, related decisions, etc.)
1. Describe the decision in full (include
1. Describe the decision in full (include business context, organizational context, and application context)
1. Identify the decision to be made
1. Identify the decision to be made (e.g., reduce churn, identify fraud, improve product mix, etc.)
1. How Good is your problem-solving? Please
1. How Good is your problem-solving? Please post the results of your Problem-Solving inventory checklist from Mindtools here. Posting of results is helpful to see how we compare as problem solvers.
1. What assistance have you seen from
1. What assistance have you seen from using analytical approaches to improve decision making?
1. Have you seen instances of poor
1. Have you seen instances of poor decision making in your organization?
1. Where does culture fit in the
1. Where does culture fit in the decision trap?
1. What is the impact of 'poor'
1. What is the impact of 'poor' or 'incomplete' decision making? What measures of effectiveness are used in your organization?
1. What are the 'decision traps' we
1. What are the 'decision traps' we all fall into?
Book Report Assignment This assignment requires that students
Book Report Assignment
This assignment requires that students read the assigned book – The Critical Chain – and submit a 5-page book report online on Canvas as a MS Word document after reading and understanding the above text. The guidelines on how to write the book report are provided below:
1. For this class, the book report must be organized around 3 major elements: Discussion, Application, and Evaluation (DAE approach).
2. Write 1-1½ pages discussing the book. Do not write a summary chapter by chapter or make lists. Write in narrative form. Make sure that your write up includes answers to these questions:
· What is the book about (briefly about 1 paragraph)?
· Describe the major themes (a couple of central ideas or gems) in the book from your perspective. Key word here is “major” themes, not just peripheral ideas such as role of communication or importance of team work etc. Remember this is operations management (OM) class and your gems should revolve around concepts related to OM.
· Give rationale why you think the above identified gems/themes are important from your perspective. (use critical thinking to add some rigor to your rationale)
3. Write 2-2½ pages on the application of the major ideas/themes/gems as identified in the discussion section above to your work place (any work place – past or present? In your own words, what is the meaning or significance of these ideas as they apply to your current or any work-setting? How does the author suggest that they be applied?
4. Finally, evaluate the book (½ to 1 page) along the following lines:
· How useful are the ideas?
· How practical are the ideas?
· How well organized and written is the book?
· Would you recommend it?
The book report must be 5 full pages, double-spaced, number 12 font with no more than one inch margins. Your write-up should make use of headings/sub-headings as needed. Rubric for grading book report is given below:
Rubric for Grading Book Report
A
A-/B+
B/B-
Exceeds Expectations
Meets Expectations
Basic Requirements
Instructions:Put your SQL in the "Problem 6"
Instructions:
Put your SQL in the "Problem 6" location in the SQL template.
Put your row count in the green box in cell D5.
Start with the same problem statement as in Problem 5, except this time generate a summary report. For each course name, list the count of the students who will need to be contacted for each format. Sort your report so it’s alphabetically sorted on Course Name, then by format (online or in person). A sample output is below (your data may vary.)
Paste a screen shot of successful execution below. Be sure your screenshots show all the SQL you use; you are welcome to include several screen shots if necessary.
Instructions:Put your SQL in the "Problem 5"
Instructions:
Put your SQL in the "Problem 5" location in the SQL template.
Put your row count in the green box in cell D5.
Professor Victoria Emmerline is set to retire immediately before spring semester. You want to generate a list of all students who are currently enrolled in any class she is teaching this spring, so you can reach out to them and let them know there will be a different professor. Assume each class she teaches could be assigned to a different professor – i.e. if Professor Emmerline was set to teach Calculus I and Calculus II, it’s possible Calculus I will be assigned to Professor X and Calculus II will be assigned to Professor Y.
Generate SQL code to make a report which will do the following
· Generate a concatenated class name/number string, such as “FIN 200” from something in the Finance Department where the course number is 200
· Print that class name/number string in the first column of your report
· Print the class description (such as “Introduction to Finance”)
· List the student’s first name and then last name
· List the offering term and the format (online or in person)
· List the professor’s first name and last name
· Sort your output ascending by the class name/number string, so “FIN 200”, “FIN 120” and “ART 100” would be sorted in the following order: “ART 100”, “FIN 120”, “FIN 200.”
· Within the same class, further sort your output so it’s alphabetical by student last name, student first name
Instructions:Put your SQL in the "Problem 4"
Instructions:
Put your SQL in the "Problem 4" location in the SQL template.
Put your row count in the green box in cell D5.
You want to see how your various students are doing, GPA-wise. Create a report which will give the student’s last name, student’s first name (should be concatenated into a single field as in the example), the student’s major, and the student’s average GPA.
Include only students who have actually taken at least one class (if they took it and earned 0 GPA points indicating they failed it, that record should be included.) Do not include a student who has not taken any classes.
The average GPA is the average of the GPA Points column in the enrollment table; display this to four or more decimal places. Sort alphabetically by “Last, First” name combination.
Paste a screen shot of successful execution below. Be sure your screenshots show all the SQL you use; you are welcome to include several screen shots if necessary.
For example, if your input data is like this: (note the in the last row – indicates student Tyrone Brown is new and has not taken any classes yet, has only declared a major.)
Instructions:Put your SQL in the "Problem 3"
Instructions:
Put your SQL in the "Problem 3" location in the SQL template.
Put your row count in the green box in cell D5.
Some faculty are grumbling that certain professors have an easy teaching load, defined as only a few courses with low capacity, while others have a heavy load of many courses with high seat counts. Run a query to help answer this question.
Your output should contain the faculty ID, faculty name (first and last – it’s fine to have this as two separate fields, also fine to concatenate together), the largest capacity class the faculty member is assigned to teach, the maximum possible total student load (sum of all the course capacities), and the number of classes the faculty member is teaching.
Include all faculty members, even if they are not assigned to teach any courses. Include courses only if they are taught by a faculty member. Sort your output ascending by the number of courses taught, with the professors with the fewest courses at the top.
For example, if you had the following input data (the blank row at the bottom means Professor Poldark did not teach anything):
Instructions:Put your SQL in the "Problem 2"
Instructions:
Put your SQL in the "Problem 2" location in the SQL template.
Put your row count in the green box in cell D5.
Write SQL which will give us the course id number (idCourse field), course description, the number of units, the offering term, whether it’s online or in person, and the capacity. Include only those courses which are more than 3 units and have a capacity of less than 40 students. We only want to list courses which have actually been offered at least once. Sort it ascending by idCourse.
Paste a screen shot of successful execution below. Be sure your screenshots show all the SQL you use; you are welcome to include several screen shots if necessary.
Instructions:Put your SQL in the "Problem 1"
Instructions:
Put your SQL in the "Problem 1" location in the SQL template.
Put your row count in the green box in cell D5.
Create SQL code to display all fields from the students table. Include only students whose major is Finance (‘FIN’) and who have an in-state residence. Sort it alphabetically by student first name.
Paste a screen shot of successful execution below. Be sure your screenshots show all the SQL you use; you are welcome to include several screen shots if necessary.
1. “XXX_Civiccenter_export.sql” where XXX are your initials.
1. “XXX_Civiccenter_export.sql” where XXX are your initials. This is an .csv file export of your “XXX_Civiccenter_query.sql” execution results. You can export this using the “export” icon in the Result Grid window in MySQL Workbench. (Note the data displayed below may or may not correspond to the data in this assignment.)
1. “XXX_Civiccenter_query.sql” where XXX are your initials.
1. “XXX_Civiccenter_query.sql” where XXX are your initials. This is an SQL script which will run a query on your database and show the results. Write SQL statement(s) which will produce an output table with the following fields in it, sorted with the most expensive tickets at the top, and then alphabetically by customer name:
Name
Opera
Cost
Date
Backstage Pass?
1. “XXX_Civiccenter_insert.sql” where XXX are your initials.
1. “XXX_Civiccenter_insert.sql” where XXX are your initials. This is an SQL script which will insert the problem data into your database.
1. “XXX_Civiccenter_create.sql” where XXX are your initials.
1. “XXX_Civiccenter_create.sql” where XXX are your initials. This is an SQL script which will drop any pre-existing Civic Center schema and create all the tables, keys, and other structures you will need. If we run this script, it should remove any existing civiccenter schema and create yours.
Assignment 3.1 This assignment is worth 70 points
Assignment 3.1
This assignment is worth 70 points and the full rubric is on the bottom of the page.
It’s time to go to the opera! You are going to use MySQL Workbench’s EER tool to create an Entity-Relationship diagram for the Civic Center Opera House. You will then engineer that to produce MySQL script(s) which will create and load the database schema with the information below. Finally, you will query your database to prove it is working.
Here is the relevant information.
· Susie attended Madame Butterfly and spent $25 on the ticket for the 1/11 performance, and then went to see La Traviata on 10/3 for $100. She also went to see Carmen for $30 on 10/10.
· Lee attended Madame Butterfly and spent $25 on the ticket for the 10/21 performance.
· Jackson attended Carmen and spent $30 on the ticket for the 5/12 performance. He also went to see La Nozze di Figaro on 1/20, and paid $100 for the ticket.
· Bobby attended Carmen and spent $30 on the ticket for the 1/26 performance, and then went to see La Boheme on 8/14 for $50.
· Margaret attended La Nozze di Figaro and spent $100 on the ticket for the 2/22 performance, and then went (with Jackson) to see Carmen on 5/12 for $30.
· Ulysses attended Rigoletto on 10/21 and spent $35 on the ticket.
· Each ticket has an option to have a “Backstage Pass” option. There is no additional cost associated with this, just a marking on the ticket that the backstage pass was selected.
· Backstage passes were selected by Margaret at all of her operas, and by Jackson when he went with Margaret to Carmen on 5/12. No other tickets chose a backstage pass.
Additional assumptions you should make:
· Assume all tickets for the same opera cost the same. For example, all tickets for Madame Butterfly are $25.
· All operas occurred last year. For example, if it is 2020 now, and the date says “November 11,” assume it was November 11, 2019.
Normalize this database and implement in MySQL Workbench using this information.
Name your database schema “CivicCenter.” Please try to use this exact name to make it easier to grade. It is not case-sensitive so it’s OK to use “civiccenter” or CIVICcenter.” Do not use “Civic_Center” or “civic-center.”
You may use as many or as few tables as you see fit.
Your deliverables are:
1. “XXX_Civiccenter_ERD.pdf” where XXX are your initials. This is a pdf of an entity-relationship diagram made using MySQL Workbench’s modeling tool (you can find this under File Menu -> Export.)
a. Entity-relationship diagrams made using other tools will not earn full points.
b. The diagram should show all entities you use (it’s OK to use more than one page if you need to, and you can cobble together screenshots if you need.)
c. All entities should show all fields and keys; expand the boxes or rearrange them if you need to.
d. All relationships and cardinalities should be easily visible
e. All relationships should have text sentence(s) labeling them. For example, if this were the pet database, you might write, “Each pet must have one and only one owner. Each owner may have zero or more pets.”
Last one! You want to know how
Last one! You want to know how many times products were shipped to customers. Note you want the total number of shipments, not the number of items shipped – one shipment of 10 items and one shipment of 20 items would be two shipments, not 30 items.
Include all products, even if they had no shipments. In this case, their count would be 0.
Include the manufacturer and the product name
Include the customer company name
Sort it so the smallest count of shipments is on top. After that, sort ascending by Manufacturer name and then product name.
Which of the following statements is true of your output? Check all that apply.
Question 6 options:
The top row of data is the Manufacturer Drill Bits R Us for its "Iron Man" product
The top row of data is from Manufacturer Born, Inc for its "To Be Wild" product
The bottom row of data shows Manufacturer Stephen, Product Name "Curried Basketball," for CompanyName Midland Imports, in 3 shipments
The bottom row of data shows Manufacturer Born, Inc, Product Name "To Be Wild," for CompanyName Bridge Fudge Retail, for 30 shipments
Question 5 (2 points) Question 5 options:[SQL Week 2
Question 5 (2 points)
Question 5 options:
[SQL Week 2 Question 200-505]
You want to report on a certain group of your products. Select
Only customers who have a balance of 0
Only shipments which were sent by employees whose employee location is California, Maryland, or New York
Only products which have had a shipment
Only employees who have made a shipment
Only customers who have received a shipment
Compute the total quantity of shipments which meet these criteria
Aggregate it by manufacturer and by product name. This means each manufacturer/product name combination appears on only one row in the table. If a manufacturer makes more than one product, it’s OK if it shows up once per product.
Sort it descending by the sum of the shipment quantity, so the largest shipment quantity shows up on top. Within shipment quantities, sort it alphabetically on manufacturer and then product name.
Your results should look something like this:
What is the total shipment quantity for General Widgets' "Widget Maximus" product? This would be in the green "A" in the screenshot above.
Which company had Total Shipments of 2 for the Twisters? This would show up in the blue letter "B" above.
Drill Bits R Us
Question 6 (2 points)
Question 4 (2 points) Question 4 options:[SQL Week 2
Question 4 (2 points)
Question 4 options:
[SQL Week 2 Question 200-504]
You want to compute the order total for your shipments. For all of your shipments, put together a report which contains the following column. Sort it ascending on idShipment
Create a new field in the output result called OrderTotal, which is the price per unit multiplied by the order quantity. You will need to calculate this field in SQL.
Your screen output should look something like this:
What is the Scantag for the entry outlined in blue above? Make sure you also type the hyphen in the middle of the scantag.
What is the quantity in the blue box? Enter your answer to the nearest integer.
What is the order total that should be in the blue box? Enter your answer to two decimal places.
Paste the SQL you used to execute this query below.
Question 3 options:[SQL Week 2 Question 200-503]You
Question 3 options:
[SQL Week 2 Question 200-503]
You suspect certain employees are not servicing very many shipments, and some may not have any shipments at all.
Create SQL to run a report which will
List all your employees and the customers they serviced along with the number of shipment for each customer. Include all employees, including those who may not have serviced any customers.
Note you want the number of shipments, not the sum of shipment quantity. For example, if you have one shipment of 10 units, and another shipment of 20 units, our number of shipments here is 2.
Sort the list descending on number of shipments, and then on employee id. If an employee has not serviced any customers, display that employee at the top.
What is the first name of the very first employee you display? (Just type the first name here.)
How many shipments did employee Sophia Moore have to Lake Erie Guitars?
If she didn't have any (or her name is not shown in the results anywhere even when you scroll down), enter the number 0.
[SQL Week 2 Question 200-502]You want to
[SQL Week 2 Question 200-502]
You want to make a report which will give you information about shipments sent by your lower-commission employees. Create a query which will
Include only all those shipments which were sent by an employee whose commission is 10% or less (include commissions of exactly 10%)
Print the state, company name, total quantity in the shipments for that customer, and the maximum employee commission in that order left to right.
Sort it alphabetically by state (ascending). If one state has several companies in it, they should be sorted within the state, with the highest total order quantity on top.
What information is found in the third row down, which should be the first one containing an entry from the State of Michigan? It corresponds to the blue box below.
Question 2 options:
Thumb Exports, 1, 0.04
Midland Imports, 57, 0.09
Something else not found here
Huron Bay Exporters, 68, 0.10
ABC Incorporated, 48, 0.09
WK 2 – DATA 620 QUIZ 2.5B – Multi-Table SQL Queries
SQL Week 2 Assignment 2.5B Questions
Question 1 (1 point)
Question 1 options:
[SQL Week 2 Question 200-501]
For every shipment in the shipment table, you want to know its shipment details, the customer information, and the employee information. Include exactly and only all the records for which a shipment ID exists. Sort it ascending by Scantag.
What is the idShipment of your first record?
What is the idCustomer of the first row returned?
What is the first name (EmpFirstName) of the employee in the first row returned?
(Make sure to type the first name only.)
Egbert
[SQL Week 2 Question 200-002] We want to
[SQL Week 2 Question 200-002]
We want to get a workload by employee printout. For every employee, print the first name, last name, all ticket ID numbers assigned to that employee, the ticket description, and ticket duration of our records. Include only tickets which have been assigned to an employee. Include all employees, even if that person doesn’t have any tickets in the current database. Sort them first alphabetically by first name (starting with letter A at the top) and then ascending by ticket duration.
Below is a screenshot of your query results. Which entry should go in the blue box?
QUIZ 2.3B – TWO TABLE SQL QUERIESSQL
QUIZ 2.3B – TWO TABLE SQL QUERIES
SQL Week 2 Assignment 2.3B Questions
Question 1 (1 point)
Question 1 options:
[SQL Week 2 Question 200-001]
Print the first name, last name, ticket ID, ticket description, and duration of the employees who are assigned with a ticket(s). Sort them first alphabetically by first name (starting with letter A at the top) and then ascending by Duration.
This query should have resulted in employee Ben Johnson as your first several rows.
What is the top row idTicket for Ben?
1. SQL Week 2 Question 200-506] Last
1. SQL Week 2 Question 200-506] Last one! You want to know how many times products were shipped to customers. Note you want the total number of shipments, not the number of items shipped – one shipment of 10 items and one shipment of 20 items would be two shipments, not 30 items.
a. Include all products, even if they had no shipments. In this case, their count would be 0.
b. Include the manufacturer and the product name
c. Include the customer company name
d. Sort it so the smallest count of shipments is on top. After that, sort ascending by Manufacturer name and then product name.
1. [SQL Week 2 Question 200-505] You
1. [SQL Week 2 Question 200-505] You want to report on a certain group of your products. Select
a. Only customers who have a balance of 0
b. Only shipments which were sent by employees whose employee location is California, Maryland, or New York
c. Only products which have had a shipment
d. Only employees who have made a shipment
e. Only customers who have received a shipment
f. Compute the total quantity of shipments which meet these criteria. Here we want the number of items shipped. If you have one shipment of 10 items, and another shipment of 20 items, this number should be 30.
g. Aggregate it by manufacturer and by product name. This means each manufacturer/product name combination appears on only one row in the table. If a manufacturer makes more than one product, it’s OK if it shows up once per product.
h. Sort it descending by the sum of the shipment quantity, so the largest shipment quantity shows up on top. Within shipment quantities, sort it alphabetically on manufacturer and then product name.
Your results should look a little bit like this. Your data may or may not match this example.
1. [SQL Week 2 Question 200-504] You
1. [SQL Week 2 Question 200-504] You want to compute the order total for your shipments. For all of your shipments, put together a report which contains the following columns.
a. Sort it ascending on idShipment
b. Create a new field in the output result called OrderTotal, which is the price per unit multiplied by the order quantity. You will need to calculate this field in SQL.
Your output will look a little bit like the following. Your numbers may vary from this example
1. [SQL Week 2 Question 200-503] You
1. [SQL Week 2 Question 200-503] You suspect certain employees are not servicing very many shipments, and some may not have any shipments at all.
Create SQL to run a report which will
a. List all your employees and the customers they serviced along with the number of shipments for each customer. Include all employees, including those who may not have serviced any customers.
b. Note you want the number of shipments, not the sum of shipment quantity. For example, if you have one shipment of 10 units, and another shipment of 20 units, our number of shipments here is 2.
c. Sort the list ascending on number of shipments (so the smallest numbers of shipments are at the top), and then on employee id. If an employee has not serviced any customers, display that employee at the very top.
For the Question 4, you may want to review the SQL Aliases functionality (“select X from Y as Z”). There is a section in WWW3 SQL schools on this.
1. [SQL Week 2 Question 200-502] You
1. [SQL Week 2 Question 200-502] You want to make a report which will give you information about shipments sent by your lower-commission employees. Create a query which will
a. Include only all those shipments which were sent by an employee whose commission is 10% or less (include commissions of exactly 10%)
b. Print the state, company name, total quantity in the shipments for that customer, and the maximum employee commission in that order left to right.
c. Sort it alphabetically by state (ascending). If one state has several companies in it, they should be sorted within the state, with the highest total order quantity on top.
Your data may or may not match the example below.
For example, if your input data looks like this
Shipment
Customer Company Name
Customer State
Quantity
Employee
Employee Commission
1
ABC
MN
1
Sarah
0.05
2
ABC
MN
2
Thomas
0.10
3
Junior
MN
5
Sarah
0.05
4
DEF
GA
1
Thomas
0.10
5
DEF
GA
1
Victor
0.11
6
G
MD
10
Wally
0.01
7
H
MD
20
Xerxes
0.09
Your answer would look like this:
State
Customer CompanyName
Total Quantity shipped to this customer
Maximum Employee Commission in these shipments
GA
DEF
1
0.10
MD
H
20
0.09
MD
G
10
0.01
MN
Junior
5
0.05
MN
ABC
3
0.10
Part 2 – Construct your Warehouse SQL
Part 2 – Construct your Warehouse SQL statements
This is where you start to turn things in. To turn in your assignment, delete the preceding pages and start with this one. Make sure to put your name up top.
Construct SQL to answer each of the following questions. For each query, paste your SQL code below, and also paste a screenshot of the first 10 or so rows the query gives you.
Give an answer to the question based only on what you see in your MySQL database. (Do not use Excel or other tools to answer these questions; since we are trying to learn MySQL here, you should do these exercises in MySQL.)
You will eventually submit this document as part of your week’s homework.
After you have completed your first draft of this document, you will use the online quiz to verify your answers. The online quiz uses the same numbering system given here, so you can quickly find the question you need.
You are welcome to update your homework document with results from the online quiz, and submit the homework document after you’ve checked your homework online.
1. [SQL Week 2 Question 200-501] For every shipment in the shipment table, you want to know its shipment details, the customer information, and the employee information.
a. Include exactly and only all the records for which a shipment ID exists.
b. Sort it ascending by Scantag.
c. Your output should look a little like this. Note the “shipment information” will be in the form of several distinct columns. (Hint: look up the “ Select * ” command to select several columns at once.)
Assignment 2.5A In this assignment, we walk you
Assignment 2.5A
In this assignment, we walk you through joins which use more than two tables.
Here’s what you are going to do for this assignment.
· Review this document. There are several parts to this document.
o Part 1 is instructions only. There is nothing to turn in, but it contains important information.
o Part 2 of this document is called the TURN IN part. This contains something you should turn in for this assignment.
· Go through this document and write SQL to answer all the questions listed below. As part of your SQL, you will capture screenshots and paste them where indicated.
· Refer to this document as you complete the online quiz which verifies your homework.
· You are welcome to use the quiz results to update your homework file.
· When you have completed the online quiz, submit the TURN IN portion of this document in the Assignment dropbox.
· Remember, you can always reach out for help to your FA or instructor if your SQL is not behaving for you.
· Your completed deliverables for this assignment are:
o The TURN IN section of this document, submitted to the LEO dropbox
o The completed online quiz results
· There is no deliverable of a SQL script for this assignment. You are welcome to create one for your own records, but the TURN IN section of this document will suffice for your faculty.
· And, you probably already know this by now, but you can always reach out to your FA for assistance.
Purpose: get more practice with multi-table SQL queries. We will practice
· Complex joins
· LEFT and FULL joins
· Order by and Group by
Part 0 – Get into your Folder
We will continue to work in the “Week2” folder inside your “DATA620” folder. Download everything into this folder and run it from there.
Part 1 – Fire up the Warehouse database
1. Go ahead and fire up MySQL Workbench and connect to your Azure MySQL database.
2. Download and run the following scripts. They will install a new database schema called “warehouse” which contains information about your customers, your products, your employees, and your shipments.
a. Warehouse_setup.sql – this will create a database schema called “warehouse”
b. Warehouse_insert.sql – this will populate your database with information about your customers, products, employees, and shipments. (Note there may be a numeric suffix on the end of your sql, so its name may be ‘warehouse_insert_v04.sql’ for example. The information in your database may or may not be exactly the same as in this example. That’s OK.)
c. You can make sure that all tables are properly populated by executing;
SELECT * FROM warehouse.<replace-table-name>;
Where <replace-table-name> is in {customer, employee, product, shipment}
d. The insert SQL also contains one query at its bottom, which should give you a list of the company names and shipments. The first one should be from ABC Incorporated, with an idShipment of 1200 and a Quantity of 9.
3. Let’s reverse engineer this database to see what the EER looks like.
a. Remember how? Database -> Reverse Engineer and then click “next” until it gives you the EER. I needed to drag my tables a little bit apart so I could see them all.
b. You can export your diagram using File -> Export -> Export as PNG. You can also grab a screenshot.
c. Verify you can see one primary key for each table. The primary key is denoted by a golden key icon.
d. Verify you understand the relationships. You should be able to see that a customer, product, and employee can exist independently.
e. You should also be able to see that a shipment ties a quantity ordered with the customer who ordered it, the product which was ordered, and the employee who fulfilled the order. A shipment also contains a Scantag and a Reference Number, two internal notations the company sometimes uses.
4. You may want to refresh your memory about how to do three-table joins. In the WWW3 Schools SQL instructions, you can see the “SQL Inner Join” on the left menu, and halfway down that page there’s a note about how to JOIN three tables.
5. There is nothing to turn in from this part.
UMGC Data 620 Assignment 4.3 Your name:Date: DeliverablesPossiblePointsTURN
UMGC Data 620 Assignment 4.3 Your name:
Date:
Deliverables
Possible
Points
TURN IN #1 – SQL script
5
TURN IN #2 – csv file
5
TOTAL
10
This assignment covers how to import data from Excel to MySQL, and how to export data from MySQL to Excel.
If you have a Mac, please see the last page (Appendix) for some helpful notes.
Reading:
? MySQL Reference Manual, Section 13.2.9.1 – how to use the SELECT INTO command
This document contains an EXAMPLE of each type of operation; you do not need to turn in anything related to the examples. The basic logic path of this assignment is:
1. Get your SQL up and running, and connected to the Cloud.
2. Prepare your Excel file into a .csv for import and get it in the right spot.
3. Import: Using Table Import Wizard
4. Export: One option presented; this sends your output file to a designated folder on your local machine.
After the examples, there is an assignment. Your deliverable here is marked with TURN IN and consists of:
? An SQL script
? An output .csv file
I have chosen to err on the side of lavish documentation here, so if you’re feeling Spartan instead, you are welcome to skip straight to the assignment on the last few pages. But if you’d like an extravagant collection of screenshots with commentary, turn straight to page 2.
EXAMPLE of an IMPORT:
Raw data stored in xls/xlsx files can be imported into MySQL databases easily. The following tutorial will demonstrate the data import process. This tutorial demonstrates a simple 50 row Excel sheet import process:
Please be aware of the following facts before you attempt the import process:
? You can only export one worksheet at a time to the MySQL database that you have created. Exporting is fast and easy provided they are in compatible formats and within a reasonable file size.
? The Excel worksheet should be clean (free of fancy formatting such as images, colors, wrap text and fancy fonts) before exporting. (The example we are doing here is well?behaved, but if you are trying to import other data from the wild, you may need to tame it a bit before doing the import.)
Get your Excel file prepared for upload to MySQL:
1. Make yourself a “Week 4” folder. Pay attention to whether you put a space between the “Week” and the “4” – I suggest you put a space there because I did, and then your filename/paths will match these directions. (If the script calls for “Week 4”, and you have “Week4” or “Week_4”, the computer won’t find it.)
UMUC Data 620 Assignment 4.1AThis assignment covers
UMUC Data 620 Assignment 4.1A
This assignment covers entity integrity and referential integrity. This is a group assignment and only one person from your group needs to post your results. It is wise for all members of the group to be familiar with the assignment.
Your group’s deliverable is participation in the Discussion Forum. Review the material below, and post your group’s answers to the discussion forum.
Feel free to read and respond to other classmates’ postings.
Reading:
· Narasimhan, Girija, “Entity Integrity Constraints,”
o https://www.youtube.com/watch?v=0r-Lu48ZTD0 (2 minutes)
· Caleb the Video Maker 2, “MySQL Lecture Series,” specifically:
o MySQL 11 - Entity Integrity: https://www.youtube.com/watch?v=wCOpSR-_VvA
o MySQL 12 - Referential Integrity: https://www.youtube.com/watch?v=Q2XnoPDWwmY
o MySQL 13 - Domain Integrity: https://youtu.be/7g0tyzy2qOk
o MySQL 14 - Primary Key (the video starts with some deer jumping in a yard, but don’t be fooled, Caleb is waiting for you): https://youtu.be/E3Bs3H14C8Y
o (We skip his MySQL 15 - Auto Increment; you can certainly watch if you want to.)
o MySQL 16 - Foreign Key: https://youtu.be/5HQzigiRb5c
Part A: Data Integrity
This week’s reading refers to entity and referential integrity within databases. Without these items, your database would probably not work at all, and if you got it to work, it would break easily or give you crazy responses.
Get the database into MySQL Workbench
This is going to require a little bit of SQL from you.
1. Log into MySQL Workbench and create a new database. Name it “my_university.”
9/7/2020 Data 620 - Lesson 4: Data Manipulation
9/7/2020 Data 620 - Lesson 4: Data Manipulation and SQL File Input/Output - DATA 620 9040 Data Management and V isualization (2208) https://learn.umgc.edu/d2l/le/content/512831/viewContent/19677149/V iew 1/3 OVERVIEW: The first three weeks of this class introduced R X W R W K H J H Q H U D O D U H D R I G D W D P D Q D J H P H Q W D Q d gave R X D W D V W H R I K R Z W R X V H 6 4 / W R W D O N W R our database. Once R X K D Y H D Q R U P D O L ] H G G D W D E D V H D Q G D V N L O O H d SQL programmer , there are additional areas to consider . This week, we cover data quality, database design and operations, data governance, and data storage and indexing. W e also expand our SQL commands to include those to read data in from outside sources, and to write the results of SQL queries to outside sources: in our case, we use a comma separated values (csv) format. Data Qualit refers to how fit R X U G D W D L V I R U W K H S X U S R V H V ou intend. You could have a beautifull Q R U P D O L ] H d relational database with perfectl X Q L T X H S U L P D U ke V D Q G I O D Z O H V V I R U H L J Q N H relationships, but if all the customers’ names are spelled incorrectly, it will still not be ver X V H I X O I R U P D U N H W L Q J S X U S R V H V 7 K L V Z H H N , we’ll cover the major wa V W K L Q J V F D Q J R Z U R Q J Z L W K our data: it could be inaccurate, out of date, incomplete, or inconsistent with itself. We’ll also cover the major wa V W K L Q J V F D Q J R Z U R Q J Z L W K our database itself: R u can fail to have referential integrity , or R X U G D W D F R X O G I D L O W R I R O O R Z E X V L Q H V V U X O H V ' D W D T X D O L W is such a huge corporate issue that man F R P S D Q L H V K D Y H H Q W L U H W H D P V R I S H R S O H G H G L F D W H G W R H Q V X U L Q J G D W D T X D O L W y . In our stud R I Database Design and Operations this week, we investigate structural problems with databases. Because databases are so precisel V S H F L I L H G D Q G E H F D X V H W K H run on computers, a structural problem can be catastrophic. We have seen in previous weeks how to set up a good database, in which all primar N H s are unique and all foreign ke V O H D G W R V R P H Z K H U H 7 K L V Z H H N Z H Z L O O V W X G integrit F R Q V W U D L Q W V Z K L F K K H O p ensure that the foreign ke V U H I H U W R H [ L V W L Q J G H V W L Q D W L R Q V D Q G W K D W L I ou delete a record, R X G R Q ’ t bring down the database. We then turn our attention awa I U R P W K H Q R U P D O L ] H G U H O D W L R Q D O G D W D E D V H V F K H P D V R R I W H Q X V H G L Q R S H U D W L R Q D l data stores. W e look towards an alternative or ganization called the Snowflake or Star Schema . Rather than several smaller tables linked with ke V W K H V Q R Z I O D N H V F K H P D K D V R Q H F H Q W U D O L ] H G I L O H Z L W K Y D U L R X V ? D U P V ? R r points of the star) attached to it. Each “arm” contains the information R X Q H H G W R T X H U along one dimension. For example, a large flat file could contain the date of a purchase, the purchased item, the shipment destination, and the customer information. One arm of one star could break the dates down into months, quarters, and H D U V Another arm of the star might break the shipment destination down into states (such as Mar O D Q G R U U H J L R Q s (such as the Pacific Northwest). This lets the customer run quick queries for things like “all orders which shipped in Quarter 1 of 2015, to the state of Mar O D Q G ? R U ? D O O R U G H U V X Q G H U Z K L F K V K L S S H G L Q 0 D of any H D r, to an Z K H U H L Q W K H 3 D F L I L F 1 R U W K Z H V W ? 4 X H U L H V R I W K L V W pe are included in a method called Dimensional Anal V L s . Data Governance: Or , the care and feeding of R X U G D W a Data is crucial to modern corporate operations, and H W L W F K D Q J H V D O O W K H W L P H - X V W D V Q H Z E X L O G L Q J F R Q V W U X F W L R n needs to be maintained, and periodicall J L Y H Q D Q H Z U R R I P R V W F R U S R U D W H G D W D Q H H G V W R X Q G H r go regular maintenance, and periodicall J L Y H Q D U H R rganization. Even if nothing went wrong with R X U G D W D S H U L R G L F D O O y there are new laws (such as HIPAA) which require new measures to be taken with existing data. Data governance includes the activities a corporation needs to undertake to identif S U R E O H P V Z L W K L W V G D W D I L [ W K H P , and deplo W K H U H V X O W V Data Storage and Indexing: How do R X I L Q G our data quickl ? With our small databases in this class, it’ s not reall Q H F H V V D U to optimize for quer V S H H G D F R P S X W H U F D n simpl O R R N W K U R X J K H D F K R Q H R I R X U I H Z K X Q G U H G D L U O L Q H U H F R U G V W R I L Q G W K H R Q H Z H Z D Q W % X W Z K D W L I ou have a few million records? Or what if R X D U H ) D F H E R R N D Q G D U H J H Q H U D W L Q J P R U H W K D Q P L O O L R Q ? O L N H V ? H Y H U y minute [1] ? Even if R X U T X H U can search one million rows a minute, if R X D U H ) D F H E R R N ou are getting 4 minutes slower in R X U T X H U L H V I R U H Y H U minute of time which passes. Y ou started off behind and R X D U e slowing down. One solution is to use a data index (plural: several data indices ) to help cut R X U V H D U F K W L P H If R X K D Y H D O L V W R I Q D P H V V R U W H G D O S K D E H W L F D O O y , and the one R X Z D Q W V W D U W V Z L W K D ? 4 ? ou don’ t need to start 9/7/2020 Data 620 - Lesson 4: Data Manipulation and SQL File Input/Output - DATA 620 9040 Data Management and V isualization (2208) https://learn.umgc.edu/d2l/le/content/512831/viewContent/19677149/V iew 2/3 searching at the “A.” Data indexing is a wa W R F R Q V W U X F W D V H U L H V R I P D S V I R U our SQL query , so it can make use of alphabetical, numeric, and other sorted orders to accelerate R X U V H D U F K . SQL File Input/Output: How do R X read and write to external files? Up until now, our databases have magicall O L Y H G Z L W K L Q W K H &