Operating Systems: Analyzing Market Share

As a sector analyst for one of the regional banks, you are responsible for reviewing and analyzing data to determine trends. Your analyses are used by bankers to inform prospective and current clients of possible investment opportunities. Currently, you are looking at the market trends of operating systems and have obtained historical market share data for mobile/tablet operating systems in the U.S., global market share data for desktop and table operating systems, and market share data for all operating systems for select countries around the world. You will chart the data to determine trends.

Instructions

1. Open TIA_Ch5_Start and save as TIA_Ch5_LastFirst.
2. Create a 2-D Area chart using data in the range A3:G7.

Hint: Charts are on the Insert tab in the Chart group. Click Insert Line or Area Chart.

• Modify the chart as follows:
  • Position the chart so it fills cells J2:S19
  • Add a title: Change in Mobile OS Market Share 2013-2018. Place 2013-2018 on a separate line.

Hint: Place insertion point before 2013 and press Shift + Enter to place dates on separate line.

• Change the font size of 2013-2018 to 10.
  • Filter out the OthersandWindows data so only AndroidandiOS data displays.

Hint: Use the Chart Filters icon (the Funnel) on the right of the chart. Uncheck Windows and Others. Click Apply.

• Add a Callout: Line shape to the chart with the line pointing at the top of the orange area between 2014 and 2015.

Hint: Callout: Line is in the Callouts section in the Insert Shape group on the Format Chart Tools tab.

• Add text to the callout: Android Market Share Takes Off!
• Format the callout with Subtle Effect - Gold, Accent 4 Shape Style.
• Create a 2-D Pie chart using the ranges A15:A21 and G15:G21.

Hint: Select A15:A21, then press the Ctrl key while selecting G15:G21.

• Modify the chart as follows:
  • Add a title: Operating System 2018 Global Market Share
  • Use Quick Layout 1 to add data % and Series data labels to each data point.

Hint: Quick Layouts are on the Chart Tools Design tab in the Chart Layouts group. 

• Move the chart so it is on its own worksheet. Label the worksheet Global OS.

Hint: Use Move Chart on the Chart Tools Design Tab. Do not copy and paste.

• On the Data worksheet, add a Column Sparkline to cell range H16:H21 using the data in range B16:G21. Increase the width of column H to 12.
• Create a Filled Map Chart from the data in ranges A26:A39 and D26:D39. Place the chart to fill the range I26:S45. Add a title: 2018 Android Market Share by Country
  • Click Chart Elements and add Data Labels
• Save the document and submit based on your instructor’s directions.
• In this lab, you will learn how to write Advanced queries using Aggregate Functions and Table Joins. You will write 10 SQL SELECT statements to query the STUDENT schema. Your Select Statements should run error-free and should be valid.
• Submit two separate files: one plain text file (.txt or .sql file) with your SELECT statements only; and the other document (doc/docx/pdf) include both your SELECT statements and your query results (copy and paste text or screen shots). The instructor and TA should be able to run your plain text source file as script and generate the same output as shown in your result document.
• Q1. List all courses that have “Intro to Information Systems” as prerequisite. (HINT: use a subquery)
• Q2. List all courses with above average cost. Display course description, cost, and the average cost of all courses. (HINT: use a subquery)
• Q3. For each zip that has at least one instructor, list the total number of instructors in that zip code.
• Q4. For each city in the state of CT, list the total number of students live in that city. Display city, state, number of students in descending order.
• — AGGREGATION OF FULL RESULT SET
• Q5. Find the minimum, maximum, and average grade of final exams in sections taught by Todd Smythe.
• Q6: For all students who took “Intro to Information Systems”, calculate the highest, lowest, and average midterm exam grade for each section. Display Section No and calculation results.
• — TABLE JOIN WITH HAVING-CLAUSE
• Q7. List the instructor id and name of the instructors that teach fewer than 10 sections regardless of student enrollment.
• Q8. Show which city has the most students. Display city and state, and number of students.
• Q9: List all zip codes where at least three students AND at least four instructors reside. Show zip, state and city.
• Q10: List all cities that have 10 or more students and instructor combined. Show city, state, number of student residents, number of instructor residents, and total student/instructor residents in that city. Sort by total in descending order.
• Learning Resources
• https://www.w3schools.com/sql/sql_join.asp
• https://docs.oracle.com/database/121/SQLRF/functions.htm#SQLRF006

Oracle

In this lab, you will learn how to write Advanced queries using Aggregate Functions and Table Joins. You will write 10 SQL SELECT statements to query the STUDENT schema. Your Select Statements should run error-free and should be valid.

Submit two separate files: one plain text file (.txt or .sql file) with your SELECT statements only; and the other document (doc/docx/pdf) include both your SELECT statements and your query results (copy and paste text or screen shots). The instructor and TA should be able to run your plain text source file as script and generate the same output as shown in your result document.

Q1. List all courses that have “Intro to Information Systems” as prerequisite. (HINT: use a subquery)

Q2. List all courses with above average cost. Display course description, cost, and the average cost of all courses. (HINT: use a subquery)

Q3. For each zip that has at least one instructor, list the total number of instructors in that zip code.

Q4. For each city in the state of CT, list the total number of students live in that city. Display city, state, number of students in descending order.

— AGGREGATION OF FULL RESULT SET

Q5. Find the minimum, maximum, and average grade of final exams in sections taught by Todd Smythe.

Q6: For all students who took “Intro to Information Systems”, calculate the highest, lowest, and average midterm exam grade for each section. Display Section No and calculation results.

— TABLE JOIN WITH HAVING-CLAUSE

Q7. List the instructor id and name of the instructors that teach fewer than 10 sections regardless of student enrollment.

Q8. Show which city has the most students. Display city and state, and number of students.

Q9: List all zip codes where at least three students AND at least four instructors reside. Show zip, state and city.

Q10: List all cities that have 10 or more students and instructor combined. Show city, state, number of student residents, number of instructor residents, and total student/instructor residents in that city. Sort by total in descending order.

Learning Resources

https://www.w3schools.com/sql/sql_join.asp

https://docs.oracle.com/database/121/SQLRF/functions.htm#SQLRF006

Data modelling and Database design

Context: The MIS602 Data Modelling & Database Design subject is designed for you to progressively add to your understanding of data and database management and its relevance with in business context. It also introduces you to some of the key features of database management system and designing database systems that will feature in later modules of this topic. In order for you to do well in this subject, it is imperative that you undertake all of the learning activities in the modules. The learning activities are presented as a way of scaffolding your learning so that you can attempt the building blocks of the assessments and be in a safe environment to fail and to learn from them. Therefore, doing your learning activities and seeking feedback from them from peers and from the learning facilitator is the single best way of preparing for doing well in this assessment. Instructions: You need to create the database tables as per below entities and complete the tasks listed in this instruction. Entities:

Task 1: Create three tables with relevant keys as suggested in the above diagram

Task 2: Insert record of 10 employees in the employee table

Task 3: Insert record of 5 departments in the department table

Task 4: Insert record of 5 salary levels in the salary table

Task 5: Write a query to display the information about the employees in the employee table Task 6: Write a query to display the name of all the employees

Task 7: Write a query to display the name of all the employees and their jobname.

Task 8: Write a query in SQL to display the unique jobname for all the employees

Task 9: Write a query to increase the salary for all the employees by 12%. Display the empname, jobname and salary after the increment

Task 10: Write a query to display the employee names with minimum and maximum salary. Task 11: Write a query to display the employee id, employee name, jobname of all the employees whose salary is greater than 90,000 P.A.

Task 12: write a query to display the all the details of all the employees whose jobname is Manager. (Hint: While entering the records for employee, make sure to add manager as jobname for a few employees.)

Task 13: Write a query to display the all the details of the employee whose name is Robert. (Hint: While entering the records for employee, make sure to add Robert as empname for at least one employee.)

Task 14: Write a query to display all the details of the employee who work as a manager and have salary greater than 95000 P.A.

Task 15: Write a query to display employee Id, employee name, jobname and date of joining of all the employees who joined after year 2001.

Task 16: Write a query to display the list of all the employees whose annual salary is within the range 55000 and 95000.( Hint: make sure to add the salary in this range while entering records in the employee table)

Task 17: Write a query to display the list of all the employees in the descending order of their salaries.

Task 18: Write a query to count the number of employees in the employee table. Task 19: Insert a new record in the employee table and add ANALYST as their jobname. The other fields can be added as per your choice

Task 20: Insert a new record in the employee table with the following data fields employeeId= 1011 empname= Janet jobname= PROGRAMMER managerid= 5095 dateofhire= 12-10-2014 salary= 90000 department_id=2011

Task 21: Write a query to delete the record of the employee whose name is ‘Flynn’. (Hint: Make sure to add a record with employee name ‘Flynn’ in the beginning.

Task 22: Write a query to update the salary by 15% of the employee whose employee name is ROBERT.

Task 23: Write a query to find the number of staff working in each department and the sum of their salaries.

Task 24: Write a query to find all employees with the string ‘Avenue’ in their address When you are finished this, prepare a document with all SQL commands used for each task and summarize your experience. On the morning following submission, the learning facilitator will allocate you another student’s document to assess. It is then your turn to assess another student’s work. You will have three days to assess the other student’s work and to upload your response to Blackboard. In no more than 500 words, provide a critique of that student’s work to them. You can choose whether to identify yourself but you should provide constructive feedback, balancing good points and points where the student can improve. You should provide feedback to the other student on their usage of commands and summary document. You are free to use whatever framework you like and the following should be minimum inclusions:

• Comment on the overall usage of SQL statements and commands used

• Provide constructive criticism on how the other student can improve their understanding of SQL statements

• Provide some useful readings the other student may peruse that helps them develop their understanding of SQL statements

Submission Instructions:

Your spreadsheets and summary should be submitted by Thursday at midnight (Sydney time) of week 7. Do not include any identifying information on your submission (i.e. not your student number, name or anything else that might identify you). This is important because the peer review is to be done anonymously. Non-submission or lateness will be treated in accordance with the late assessment policy of the university. Please note that late submissions mean that another student will effectively have less time to do their peer assessment of your work.