Task One - SQL Queries Question 1 Show the names of people who have purchase the...

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.