SQL assignment
UMUC DATA 620 Assignment 5.2 - SQL ORDER and GROUP_2175 1 UMUC Data 620 Assignment 5.2 Your name: Date: TURN IN: There are two things to turn in here. <0026015d018c0190019a0355000301c7017d01b5000301c1015d016f016f0003019001b5010f0175015d019a000301020003005e0059003e000301900110018c015d0189019a00030128015d016f011e00030190015a017d01c1015d01760150000301c701 7d01b5018c000301020176019001c1011e018c019000030128Lor all of the problems below, based on the template at the end of this document. Name it “XXX‐Assignment‐5‐2.sql”, where XXX is your initials or your name. Second, you will take screenshots of your results and put them into this document in the space provided after each question. Deliverables Possible Points Notes TURN IN #1 3 Points encompass TURN IN #2 3 Both the screen shots TURN IN #3 4 And the SQL code TOTAL 10 In this Assignment, you will be using our SQL programming to do more complex ORDER BY and GROUP by commands. Reading: Review the SQL Reference Manual as needed. This Assignment requires you to have our Order Entry database from Week 2, Assignment 2.3 <01b50189000301020176011a0003018c01b501760176015d017601500003015d0176000301c7017d01b5018c0003004401c7005e0059003e000301900110018c011e011e01760358000300030003007a017d01b500030175010201c7000301c10102017601 9a0003019a017d0003018c011e0128011e018c0003010f0102Lck to that assignment for the ERD. You want to make sure your database is current. If you have been playing with it (for example, deleting records), it’s smart to refresh your database before starting to do this assignment. To refresh your database, go back to Assignment 2.3 and rerun Parts A and B, in which you download the script, re‐run the script, and then verify you have a fresh copy of the database available. UMUC DATA 620 Assignment 5.2 - SQL ORDER and GROUP_2175 2 1. Display the product name, product number, order number, and the order state for all the orders for which the OrderTbl state is Colorado. Sort it ascending by product name. Don’t group it by anything. Write a sentence: how many records do you have, and do you have any duplicate product names? Why might this be? <00640068005a00450003002f00450357000300030102000301900110018c011e011e017600030190015a017d019a0003017d0128000301c7017d01b5018c0003018c011e019001b5016f019a01900003037e018901020190019a011e0003015d019a000301 0f011e016f017d01c1037f0356000301c7017d01b5018c0003LSQL code for this goes under “Problem 1” in the template. 2. Write a new query. Display only the product name, product number, and the order state for all the orders for which the OrderTbl state is Colorado. Do *not* use the <0027018c017d01b501890003001101c700030110017d0175017501020176011a03580003000300440102016c011e0003015d019a00030189018c015d0176019a0003017d0176016f01c7000301b50176015d018b01b5011e0003037e011a015d0190019a01 5d01760110019a037f00030189018c017d011a01b50110019aL names. Sort it ascending by product name. Write a sentence: how many records do you have, and do you have any duplicate product names? Why might this be? What is the difference between this and the query you did first? (Hint: look up SELECT DISTINCT) <00640068005a00450003002f00450357000300030102000301900110018c011e011e017600030190015a017d019a0003017d0128000301c7017d01b5018c0003018c011e019001b5016f019a01900003037e018901020190019a011e0003015d019a000301 0f011e016f017d01c1037f0356000301c7017d01b5018c0003LSQL code for this goes under “Problem 2” in the template. 3. For each employee with a commission less than 0.04, compute the number of orders taken. The result should include the employee number, employee last name, employee <0110017d01750175015d01900190015d017d01760003018c0102019a011e0355000301020176011a0003019a015a011e0003019a017d019a0102016f0003017601b50175010f011e018c0003017d01280003017d018c011a011e018c01900003019a010201 6c011e01760003010f01c70003019a015a0102019a0003011eLmployee. Assume each distinct OrdNo in the OrderTbl counts as one distinct order. Each employee should be displayed only once; for example, if employee Mickey Mouse took five orders, there <0190015a017d01b5016f011a0003010f011e0003017d0176011e0003016f015d0176011e0003011a015d01900189016f010201c7015d017601500003015a015d01900003017601020175011e035500030176017d019a00030128015d01c0011e0358000300 030003L <00640068005a00450003002f00450357000300030102000301900110018c011e011e017600030190015a017d019a0003017d0128000301c7017d01b5018c0003018c011e019001b5016f019a01900003037e018901020190019a011e0003015d019a000301 0f011e016f017d01c1037f0356000301c7017d01b5018c0003LSQL code for this goes under “Problem 3” in the template. UMUC DATA 620 Assignment 5.2 - SQL ORDER and GROUP_2175 3 Here is a template for your sql code file. Use this, making the relevant changes. Submit your <03580190018b016f00030128015d016f011e0003010201900003010200030190011e01890102018c0102019a011e00030102019a019a01020110015a0175011e0176019a035800030003004501020175011e0003015d019a0003035e007900790079037200 0401900190015d015001760175011e0176019a037203f10372L2.sql”, where XXX is your initials or your name. # DATA 620 Assignment 5.2 # Written by # Semester # Professor # # When you submit this, rename it to "XXX‐Assignment 5.2.sql" # where XXX is your initials or your name # # You will submit the SQL for all the programming problems in this single file # # Problem 1 # < Your SQL for problem 1 goes here > # Problem 2 # < Your SQL for problem 2 goes here > # Problem 3 # < Your SQL for problem 3 goes here > … etc …
