This assignment consists of a series of SQL questions. For each question, please include your: • Your final SQL query
MIS531 1 MIS 531: Homework SQL-2 This assignment consists of a series of SQL questions. For each question, please include your: • Your final SQL query Group-work Instructions Remember to first solve the homework on your own before meeting with your group partner to discuss any "challenging aspects". That will maximize learning, efficiency and fairness. If you get stuck on part of a homework, make a note of it and continue working on the remainder of the homework. The goal of this two-step approach is: • Allows you to become familiar with the material • You come prepared (with the concepts and the SQL questions) for the group discussion • Maximizes the value of time spent together since you each know which pieces you are stuck on, and where you and your partner(s) need to brainstorm. Similarly, your partners will be expected to come prepared and ready to contribute to the team learning process. It’s not fair to your group to show up to a meeting unprepared. Please do not ask for code from others nor share your code with others (i.e., outside your team). Unfortunately, once code is shared people stop learning and resort to copying the code. The homework does not require a perfect score to do well. The homework (in entirety) is designed to allow you to lose over 20 points and still earn 100 points. Please note, the maximum score is 100. Therefore, if you score over 100, your score will be assumed to be 100 points. The idea is to encourage students to learn. Thus, if you put in a good effort, you can get full points without being perfect. This should reduce the worry about missing a few points here or there. Another purpose is to reduce the incentive to cheat. Submission Instructions 1) Integrity affirmation: By submitting each query: you affirm that the work submitted is your own and that you did not receive unauthorized assistance from those outside your homework group, nor did you provide unauthorized assistance to others. You can always approach the instructor with questions or clarifications. 2) We want to grade the final query. So, if you submit intermediate queries, please keep the final query at the top of your file so the graders can find it easily. 3) Use the D2L Turnitin dropbox to submit a soft copy of EACH question in your assignment. 4) One submission per group. 5) Each query you submit (final version) must be executable, in other words: to grade them, we must be able to copy-paste from your submitted file into SQL Developer and run the queries without error. Please test this (e.g., after preparing your document) to ensure you do not lose points. MIS Department 2 6) Submit an ASCII text file (.txt) file for each question. Do not use other formats, e.g., Word, PDF, etc. 7) Please start early. SQL requires a good amount of practice and learning. By leaving work for the last minute, you incur many risks including a potential server outage affecting your work. 8) The submission process takes time (and requires confirmation). Please do NOT wait till close to the deadline to submit your individual queries on D2L (as you may cross over into the late period and automatically incur the 10% penalty). To be fair to all students, we do NOT selectively waive the late penalty. 9) Keep the following instructions in mind while solving the queries. • The sub-parts of a query are CUMULATIVE (i.e., build on previous parts). • Please solve the queries in stages, i.e., write your SQL query in stages, to cumulatively answer each part. This reduces the likelihood of making errors and helps you find errors easily. Unless you’re experienced with SQL, writing a large query all together is very difficult. Reminders • Submit one file (with your final query) for each question • All column Headings are case sensitive (for the entire homework) • The sub-parts of each query are CUMULATIVE (requirement b) builds on requirement a), and so on ) Part-A Question-1 a) For every customer and shipper display the following information: o Type: to denote if it’s a customer or a shipper (show: Customer for customers and Shipper for shippers) o Company Name: the company name of the customer or shipper o Number of Orders: the number of orders this customer has placed (or shipper has handled). If there are none, show 0. b) Restrict yourself to customers with 3 or fewer orders and shippers with 50 or fewer orders. c) Sort by company name Your output should be like the following (including column headings): Type Company Name Number of Orders Customer A Customer 2 Customer Customer A 1 Shipper Shipper A 42 … Customer Test Customer2 0 Hint: use outer join, UNION. Review the SQL Queries tutorial on D2L on how to display a literal string. 3 Reminder: the sub-parts are cumulative (for the entire homework). For example, since we’re on question 1, that means by the end of Q1 you should have a single query that satisfies ALL requirements across ALL sub-parts. Question-2 a) For each employee, display his/her employee number (heading: Employee ID), the combination of the employee first name and employee last name in one column separated by a space (e.g., Wilma Wildcat, heading: Employee Name). b) Additionally, display for each employee the job title of their manager (if they have a manager). If they do not have a manager, display No Manager (heading: Manager Title). c) For each employee, show the average lead time their orders have taken to ship (heading: Avg Time to Ship). E.g., if employee Wilma manages an order with the order date of 01-SEP-2020 and shipped date of 10-SEP-2020, the lead time to ship that order is 9 days (format: 99.99). d) For each employee, also show the number of orders they have managed over all time (heading: Number of Orders). e) Restrict your results to employees that have more than 60 orders. f) Sort the results by employee ID. Hint: you may need to use two copies of a table. Question-3 g) For each quarter in 2020, display the Quarter (heading: Quarters). Note: a year has four quarters: Q1, Q2, Q3, Q4. For each quarter, display a cross-tabulation of the gross revenue (we refer to this as: Sales). Gross Revenue means the dollar value of sales (i.e., without considering discount). Use a $999,999.99 format. To illustrate for one order: suppose Wilma’s order has one item priced at $20 and the quantity sold was 50. The gross revenue is 20 x 50 = 1,000. a) Restrict your results to the following product lines: Condiments, Confections and Dairy Products. For the headings, we demonstrate by example. E.g., for Condiments the heading is: Sales - Condiments. See the example output below, for headings and formatting. b) Sort the results by the Quarters. c) We use the following (hypothetical) table to illustrate (showing Q1, Q2; your results should have all 4 quarters). Quarters Sales - Condiments Sales - Confections Sales – Dairy Products Q1 $20,000.00 $10,000.00 $30,000.01 Q2 $18,000.01 $10,000.00 $10,000.01 Hint: look up the use of the Oracle PIVOT() function (e.g., in the SQL Queries tutorial). Using a WITH Clause or inline select(s) should help you. 4 Part-B Question-4 Notes (this query): • Consider priced 25 or below as low-priced. • Ignore discounts when calculating sales revenue Do not assume the product line name will always be unique (even though in the current dataset it is). a) For each product line, display the product line name (heading: Line Name) b) Show the number of low-priced products (heading: Num Low Priced) c) Show the Year to date (YTD) revenue (heading: YTD Revenue, format $999,999.99). YTD revenue is revenue from orders placed (use the orderdate) since the beginning of the current year till today. Hint: Use WITH (or equivalent), sysdate (see the tutorial). Question-5 a) For each employee, display the employee number (heading: Employee Num), employee last, first name in one column, e.g., Wildcat, Wilbur (heading: Employee Name). Sort the results by the employee number. b) Display a loyalty category for each employee based on the number of years the employee has served with CORP (heading: Employee Loyalty). Use the following table to determine loyalty. No. of Years Employee Loyalty Category Fewer than 5 Bronze At least 5 but less than 10 Silver At least 10 but less than 20 Gold At least 20 Platinum c) Display the total number of orders an employee has managed (heading: Total Orders). If the employee has no orders, display 0. d) Display the employee rank based on the total number of orders the employee has managed (highest value should be ranked 1). Use a heading: Ranking based on Orders. Hint: Use WITH or equivalent, CASE(), RANK(). Homework FAQ Question: Where can I run and test homework queries? 5 Answer: The queries for your homework are to be executed using your Oracle accounts with SQL Developer Question: Where can I find the dataset to answer the questions? Answer: The queries are based on the CORP database (owner is CORP). It is recommended that you get familiar with both the structure and the contents of the database to help you better understand the query requirements and validate your answers. As a quick recap, the “describe ” command lets you see the structure of tables (this is also posted on the website). Doing a “SELECT * FROM
.;” will let you see all the data in the table. Question: Can I