Task One - SQL Queries Question 1 Show the names of people who have purchase the...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Task One - SQL Queries Question 1 Show the names of people who have purchase the most goods (ie total number of items). Hint: 1/ get the sum of number of for each customer 2/ get the max of this sum 3/ compare the sum of number of for each customer to the max and return the customer ID --4/ Join customer ID from 3 to customer table to get the names. Data Driven Web Technologies Practical One // Paste your query here 1 firstName v lastName Summer Question 2 Show the parent category name and how many subcategories it has. // Paste your query here 1 2 3 Clothing 4 Food Borovansky categoryName subcategories Animals Books 5 Furniture 6 Gardening 7 Instruments 8 Kitchen 9 Non-Fiction 10 Personal Care 11 Phones 12 Teaching 13 Toys 5 2 1 1 3 1 1 1 1 1 1 2 Question 3 Show the day, week day name, month name and year as individual columns of all orders that were made after January 1st, 2021. Show the month as the word month (January, February... etc) instead of the number value. For each day show the number of orders that were made for that day and the total number of items across all the orders. Show the date with the most orders first, for ties then order by the number of items from lowest to highest // Paste your query here datInMonth weekday orderosth orderYearunberforders totalitens 1 26 2 3 3 6 5 6 12 13 14 1 17 9 19 10 30 3 9 4 5 17 29 20 1 5019 2 5820 3 5822 Friday Wednesday March Saturday Thursday 5023 5824 6 5025 7 5826 8 5827 5029 9 5030 Sunday Sunday Tuesday Tuesday Saturday January February February January February Saturday March February March January January February January 41.35 256.23 252.60 82.50 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 120.10 1116.78 150.75 357.19 205.60 28 19 19 18 18 18 17 2021 2021 Col1423 selected) Spaces: 4 UTF-8 LF SQL 66 MSSQL 00:00:00 127.0.0.1: AmazonOrders Question 4 Ahh no, our front-end developer has set our order values to be Strings and the boss cannot generate his reports. --We need to run a query that returns the order number, order date and paid date as strings whilst the total cost of the order can be a decimal type. Dates should be in long format (Monday 15, Monthname Year). // Paste your query here orderNumbertotalorderCostorderDate 17 16 15 24 77 83 50 61 45 68 47 51 53 46 60 Monday 1, March 2021 Monday 1, March 2021 Monday 1, March 2021 Monday 1, March 2021 Monday 1, March 2021 Monday 1, March 2021 Monday 1, March 2021 Monday 1, March 2021 Monday 1, March 2021 Tuesday 2, March 2021 44 orderDate Friday 19, March 2021 Tuesday 16, March 2021 Tuesday 16, March 2821 Thursday 11, March 2021 Thursday 11, March 2021 Tuesday 9, March 2821 Monday 8, March 2021 Saturday 6, March 2821 Monday 1, March 2021 Honday 22, March 2821 11 CARE Ln 72, Col1 (548 selected) Spaces: 4 UTF-8 LF SQL 3,702 rows MSSQL 00:00:00 127.0.0.1: Amazon Question 5 Find out the number of the items that has been purchased for each item each category. If the item is purchased more than 500 times, then should show it as 'most popular', if it is between 200 to 500, then show it as 'popular', if it is between 100 to 200, then show 'less popular', if it is less than 100, then show'not popular'. Order the table based on the category name, and within the category name, then order by customers' preference. // Paste your query here 1 2 3 5 7 ite 7 13 30 38 24 10 11 22 12 50 28 44 21 13 32 14 31 5 6 7 8 9 10 itemare Real Human Finger Bones Suture Practice Kit Human Body Fat Replica Lincoln Throw Pillow Nicolas Cage Sequin Pillow Dog or Cat Stuffed Bed Pi... Waterphone Mesotherapy Gun Phone Case Zombie Chocolate Easter B. categoryNane customers' preference Popular Popular Popular Inflatable Unicorn Hornf. Pet Carrier Sweater Cases Chocolate Coats Coats Chicken Harness Collars Baby Hands Beeswax Candles Decorations Artificial Garlic Decorations Anatomy Bedding Bedding 11 (482 selected) Spaces: 4 UTF-8 CRLF SQL 50 rows MSSQL 00:00:00 127.0.0.1: AmazonOrders Question 6 We need to find out who our whales are so we can send them advertising materials. Find out how much each person has spent in our stores. If they have spent over $10000 label them a 'goldmine', for those who've spent over $1000 they're a 'big spender', for over $500 they're an average spender' and anyone else can be deemed 'unworthy' // Hint firstName lastName --1/ get the total spent for each customer -- 2/ merge the result with the customer table -- 3/ convert the max numbers to words // Paste your query here Neil Anoce most popular Popular Popular most popular Popular most popular Popular Popular O'Hinchcliffe Borovansky Weingarth Rudall Rudall Lola Hermann Demaine Rutherford most popular most popular Popular 1 Dakota 2 Summer 3 Sarah 4 Joel Steven Caleb Elijah Unworthy Ali Big Spender Alica Big Spender Stephanie Big Spender Joel Big Spender Ln 120, Col 34 (451 selected) Spaces: 4 UTF-8 LF SQL 3,774 rows spenderworth Big Spender Big Spender Big Spender Unworthy Big Spender Big Spender // Hint -- 1/ get the purchased item price 2/ calculate the purchased markup Question 7 Find the customers names who purchased the items that is in sale in 2020. -- 3/ compare with the markup in history // Paste your query here Hint: you need to calculate the purchased markup and then compare with the markup in the history. 1 Jack Lisandra Tammy Spencer 5 Petra 6 Camille 7 Jorden 8 Desiree 9 Wanda 10 Javier 11 Hannah 12 Jolene 13 Alisha 14 Jarrod 15 Clayton 11 (772 selected) Spaces: 4 2 firstname lastname region Alston SA Alvarado SA Arthur SA Bavin South Australia Blake SA Blake SA Bowers SA Boyer SA Browning SA Browning SA Burrows South Australia Castro SA Chander SA Chandra SA SA UTF-8 CRLF SQL 85 rows 3 4 Chen Question 8 Show how much each region has spent on houses. For those that spent nothing, remove the NULLS and replace it with 0.00. Order by the highest spent. // Paste your query here 1 2 3 4 NSW QLD VIC SA WA TAS 7 South Australia 8 Western Australia 9 New South Wales Victoria 5 6 region 10 totalSpent 3600000.00 1965600.00 1782000.00 820800.00 396000.00 129600.00 100800.00 50400.00 50400.00 50400.00 ACT Ln 127, Col 1 (552 selected) Spaces: 4 UTF-8 LF SQL 13 rows Question 9 If you ran the previous query you will note that region is a mix of abbreviations and full names. Fix up the remaining regions by converting the full names to their corresponding abbreviations (eg, South Australia -> SA). Can you work out how to do this using a single query? // Paste your query here Task TWO - Revision So, the last couple weeks has been quite intense with a lot of content. Take the rest of this practical time to look over past practicals, materials you need to catch up on or need some more time practising. Task One - SQL Queries Question 1 Show the names of people who have purchase the most goods (ie total number of items). Hint: 1/ get the sum of number of for each customer 2/ get the max of this sum 3/ compare the sum of number of for each customer to the max and return the customer ID --4/ Join customer ID from 3 to customer table to get the names. Data Driven Web Technologies Practical One // Paste your query here 1 firstName v lastName Summer Question 2 Show the parent category name and how many subcategories it has. // Paste your query here 1 2 3 Clothing 4 Food Borovansky categoryName subcategories Animals Books 5 Furniture 6 Gardening 7 Instruments 8 Kitchen 9 Non-Fiction 10 Personal Care 11 Phones 12 Teaching 13 Toys 5 2 1 1 3 1 1 1 1 1 1 2 Question 3 Show the day, week day name, month name and year as individual columns of all orders that were made after January 1st, 2021. Show the month as the word month (January, February... etc) instead of the number value. For each day show the number of orders that were made for that day and the total number of items across all the orders. Show the date with the most orders first, for ties then order by the number of items from lowest to highest // Paste your query here datInMonth weekday orderosth orderYearunberforders totalitens 1 26 2 3 3 6 5 6 12 13 14 1 17 9 19 10 30 3 9 4 5 17 29 20 1 5019 2 5820 3 5822 Friday Wednesday March Saturday Thursday 5023 5824 6 5025 7 5826 8 5827 5029 9 5030 Sunday Sunday Tuesday Tuesday Saturday January February February January February Saturday March February March January January February January 41.35 256.23 252.60 82.50 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 2021 120.10 1116.78 150.75 357.19 205.60 28 19 19 18 18 18 17 2021 2021 Col1423 selected) Spaces: 4 UTF-8 LF SQL 66 MSSQL 00:00:00 127.0.0.1: AmazonOrders Question 4 Ahh no, our front-end developer has set our order values to be Strings and the boss cannot generate his reports. --We need to run a query that returns the order number, order date and paid date as strings whilst the total cost of the order can be a decimal type. Dates should be in long format (Monday 15, Monthname Year). // Paste your query here orderNumbertotalorderCostorderDate 17 16 15 24 77 83 50 61 45 68 47 51 53 46 60 Monday 1, March 2021 Monday 1, March 2021 Monday 1, March 2021 Monday 1, March 2021 Monday 1, March 2021 Monday 1, March 2021 Monday 1, March 2021 Monday 1, March 2021 Monday 1, March 2021 Tuesday 2, March 2021 44 orderDate Friday 19, March 2021 Tuesday 16, March 2021 Tuesday 16, March 2821 Thursday 11, March 2021 Thursday 11, March 2021 Tuesday 9, March 2821 Monday 8, March 2021 Saturday 6, March 2821 Monday 1, March 2021 Honday 22, March 2821 11 CARE Ln 72, Col1 (548 selected) Spaces: 4 UTF-8 LF SQL 3,702 rows MSSQL 00:00:00 127.0.0.1: Amazon Question 5 Find out the number of the items that has been purchased for each item each category. If the item is purchased more than 500 times, then should show it as 'most popular', if it is between 200 to 500, then show it as 'popular', if it is between 100 to 200, then show 'less popular', if it is less than 100, then show'not popular'. Order the table based on the category name, and within the category name, then order by customers' preference. // Paste your query here 1 2 3 5 7 ite 7 13 30 38 24 10 11 22 12 50 28 44 21 13 32 14 31 5 6 7 8 9 10 itemare Real Human Finger Bones Suture Practice Kit Human Body Fat Replica Lincoln Throw Pillow Nicolas Cage Sequin Pillow Dog or Cat Stuffed Bed Pi... Waterphone Mesotherapy Gun Phone Case Zombie Chocolate Easter B. categoryNane customers' preference Popular Popular Popular Inflatable Unicorn Hornf. Pet Carrier Sweater Cases Chocolate Coats Coats Chicken Harness Collars Baby Hands Beeswax Candles Decorations Artificial Garlic Decorations Anatomy Bedding Bedding 11 (482 selected) Spaces: 4 UTF-8 CRLF SQL 50 rows MSSQL 00:00:00 127.0.0.1: AmazonOrders Question 6 We need to find out who our whales are so we can send them advertising materials. Find out how much each person has spent in our stores. If they have spent over $10000 label them a 'goldmine', for those who've spent over $1000 they're a 'big spender', for over $500 they're an average spender' and anyone else can be deemed 'unworthy' // Hint firstName lastName --1/ get the total spent for each customer -- 2/ merge the result with the customer table -- 3/ convert the max numbers to words // Paste your query here Neil Anoce most popular Popular Popular most popular Popular most popular Popular Popular O'Hinchcliffe Borovansky Weingarth Rudall Rudall Lola Hermann Demaine Rutherford most popular most popular Popular 1 Dakota 2 Summer 3 Sarah 4 Joel Steven Caleb Elijah Unworthy Ali Big Spender Alica Big Spender Stephanie Big Spender Joel Big Spender Ln 120, Col 34 (451 selected) Spaces: 4 UTF-8 LF SQL 3,774 rows spenderworth Big Spender Big Spender Big Spender Unworthy Big Spender Big Spender // Hint -- 1/ get the purchased item price 2/ calculate the purchased markup Question 7 Find the customers names who purchased the items that is in sale in 2020. -- 3/ compare with the markup in history // Paste your query here Hint: you need to calculate the purchased markup and then compare with the markup in the history. 1 Jack Lisandra Tammy Spencer 5 Petra 6 Camille 7 Jorden 8 Desiree 9 Wanda 10 Javier 11 Hannah 12 Jolene 13 Alisha 14 Jarrod 15 Clayton 11 (772 selected) Spaces: 4 2 firstname lastname region Alston SA Alvarado SA Arthur SA Bavin South Australia Blake SA Blake SA Bowers SA Boyer SA Browning SA Browning SA Burrows South Australia Castro SA Chander SA Chandra SA SA UTF-8 CRLF SQL 85 rows 3 4 Chen Question 8 Show how much each region has spent on houses. For those that spent nothing, remove the NULLS and replace it with 0.00. Order by the highest spent. // Paste your query here 1 2 3 4 NSW QLD VIC SA WA TAS 7 South Australia 8 Western Australia 9 New South Wales Victoria 5 6 region 10 totalSpent 3600000.00 1965600.00 1782000.00 820800.00 396000.00 129600.00 100800.00 50400.00 50400.00 50400.00 ACT Ln 127, Col 1 (552 selected) Spaces: 4 UTF-8 LF SQL 13 rows Question 9 If you ran the previous query you will note that region is a mix of abbreviations and full names. Fix up the remaining regions by converting the full names to their corresponding abbreviations (eg, South Australia -> SA). Can you work out how to do this using a single query? // Paste your query here Task TWO - Revision So, the last couple weeks has been quite intense with a lot of content. Take the rest of this practical time to look over past practicals, materials you need to catch up on or need some more time practising.
Expert Answer:
Related Book For
Income Tax Fundamentals 2013
ISBN: 9781285586618
31st Edition
Authors: Gerald E. Whittenburg, Martha Altus Buller, Steven L Gill
Posted Date:
Students also viewed these databases questions
-
Izmir A.S. issued convertible bonds at their face value of 100,000 lira on December 31, 2020. The bonds have a 10-year life with interest of 10 percent payable annually. At the date of issue, the...
-
The table gives the annual cost per person age 65 and older for home health care from 2006 and projected through 2021. While these annual costs per person may seem modest, the majorities of older...
-
1. Analyze the ethical culture at TAP. Does the culture appear to be in alignment? Misalignment? 2. Based on the facts in the case and what you have learned in this chapter, evaluate the culture...
-
At a historic UN Summit in September 2015, world leaders adopted the 2030 Agenda for Sustainable Development. This agenda contains 17 sustainable development goals to be implemented by countries over...
-
Presented below is information taken from a bond investment amortization schedule with related fair values provided. These bonds are classified as available-for-sale. Instructions(a) Indicate whether...
-
1. An unpolarized light beam is incident on the polarizes of a polarization experiment and the intensity of light beam emerging from the analyzer is measured as 100 Lumens. Now, if the analyzer is...
-
A skydiver weighs 125 pounds, and her parachute and equipment combined weigh another 35 pounds. After exiting from a plane at an altitude of 15,000 feet, she waits 15 seconds and opens her parachute....
-
Rank HO, Ar, and HCI in order of increasing strength of intermolecular forces. Items (3 items) (Drag and drop into the appropriate area) Intermolecular force ranking HO 16 OF 17 QUESTIONS COMPLETED...
-
1. Assume that the market interest rate is 50% (r = 0.50) and that an enterprise has an initial endowment of two units. Assuming the available productive opportunity is as described in the first two...
-
Develop an audit plan for your organization's safety management system (SMS). Determine the type of audit that will be performed, determine the frequency of the audit, and describe the required...
-
Which statement about the acquisition of Rohm and Hass by Dow is correct? Group of answer choices The Revlon Standard implied that Rohm could not consider the welfare of its employees in the...
-
AVC Company sold 250 units on account for $ 1 comma 100 each. The standard cost of each unit is $ 800. Under a justminusinminustime costing system, the journal entries that are recorded for the 250...
-
3. You decide to finance the purchase a used $10,000 car with $2500 down, borrowing $7500 from a local bank. They tell you the interest being charged is 6% per year. You agree to repay the loan in 24...
-
Assume that grouping of inventory items is not appropriate in this case. Apply the lower of cost and net realizable value test and provide the required adjusting journal entry for the period ending...
-
In the operation of an automated production line with storage buffers, what does it mean if a buffer is nearly always empty or nearly always full?
-
Mary paid $2,000 of state income taxes in 2012. The total sales tax she paid during 2012 was $5,500, which included $3,000 for the cost of a new car. How should Mary treat the taxes paid on her 2012...
-
Kent Pham, CPA, is a 45-year-old single taxpayer living at 169 Trendie Street, La Jolla, CA 92037. His Social Security number is 865-68-9635. In 2012, Kent's W-2 as the controller of a local...
-
Charlie's Green Lawn Care is a cash basis taxpayer. Charlie Adame, the sole proprietor, is considering delaying some of his December 2012 customer billings for lawn care into the next year. In...
-
A particle moves on a predetermined trajectory with the equation of motion \(s(t)=k t^{2}\), with \(k\) constant and with magnitude of the acceleration equal to \(a=2 k\). Show by using (3.74) that...
-
In the case considered in the question (14), show what the trajectory corresponds to in case the magnitude of acceleration is \(a=2 k \sqrt{1+\frac{t}{T}}\), where \(T=\) cost. Question 14 A particle...
-
The position vector along a trajectory expressed in terms of the scalar distance \(s\) from the origin is given by the relation \(\mathbf{r}=\mathbf{a} s^{2}+\mathbf{b} s+\mathbf{c}\), with the...
Study smarter with the SolutionInn App