{ "text":"Write SQL statements to satisfy the following information requests",
"connection":"sql_book_connection",
"diagram":"hudson-sales",
"schema":"hudsonu",
"owner":"hudsonu",
"scoreVisibility":["student","professor"],
"tasks":[{
"text":"Find the average shipping and tax amounts for all sales. Round up to the nearest whole number. Include the rounded up average shipping and the rounded up average tax amounts.",
"start":"",
"key":["Select CEIL(AVG(shipping)), CEIL(AVG(tax)) From sale"],
"lines":5,
"points":10
},{
"text":"Find the maximum and minimum product price. Include just the maximum and minimum product price.",
"start":"",
"key":["Select MAX(price), MIN(price) From sale_product"],
"lines":5,
"points":10
},{
"text":"Find the average quantity of products in a sale. Round to 4 decimal places. Include the average quantity.",
"start":"",
"key":["SELECT ROUND(AVG(qty),4) FROM sale_product"],
"lines":5,
"points":10
},{
"text":"Find the earliest sale time and the latest sale time. The time should be the hour, minute, and second. Name the earliest time ‘earliest’ and the latest time ‘latest.’ Include just the earliest and latest columns in the result.",
"start":"",
"key":["SELECT MIN(strftime('%H:%M:%S', sale_time)) AS earliest, MAX(strftime('%H:%M:%S', sale_time)) AS latest FROM sale"],
"lines":7,
"points":10
},{
"text":"Find how many customer phone numbers there are on file and how many customers are on file. Then calculate the difference (make sure the difference is a positive number). Include the number of phone numbers, number of customers, and the difference.",
"start":"",
"key":["Select COUNT(phone), COUNT(*), COUNT(*)-COUNT(phone) From customer"],
"lines":5,
"points":10
},{
"text":"Find how many different product sizes are available. Include just this number.",
"start":"",
"key":["Select COUNT (DISTINCT product_size) From product_size"],
"lines":5,
"points":10
},{
"text":"Find how much it would cost to buy one of every product at the current product price. Format this amount in dollar form rounded to two decimal places. Include just this formatted amount.",
"start":"",
"key":["Select FORMAT(\"$%.2f\",ROUND(SUM(current_price),2)) From product"],
"lines":5,
"points":10
},{
"text":"Find the average, maximum, and minimum amounts of tax. Name these amounts ‘Average’, ‘Max’, and ‘Min’. Format in dollar format to two decimal places. Include just these three amounts.",
"start":"",
"key":["Select FORMAT(\"$%.2f\",AVG(tax)) AS Average,FORMAT(\"$%.2f\", MAX(tax)) AS Max, FORMAT(\"$%.2f\", MIN(tax)) AS Min From sale"],
"lines":6,
"points":10
},{
"text":"Find the amount of products vendor ‘108’ has produced. Include just the number of products.",
"start":"",
"key":["Select COUNT (product_id) From product WHERE vendor_id = 108"],
"lines":6,
"points":10
},{
"text":"Find the total cost for products in sale 10531270. Include just this sale amount in dollar format.",
"start":"",
"key":["Select FORMAT(\"$%.2f\", SUM(price)) from sale_product WHERE sale_id = 10531270"],
"lines":6,
"points":10
}]
}
"connection":"sql_book_connection",
"diagram":"hudson-sales",
"schema":"hudsonu",
"owner":"hudsonu",
"scoreVisibility":["student","professor"],
"tasks":[{
"text":"Find the average shipping and tax amounts for all sales. Round up to the nearest whole number. Include the rounded up average shipping and the rounded up average tax amounts.",
"start":"",
"key":["Select CEIL(AVG(shipping)), CEIL(AVG(tax)) From sale"],
"lines":5,
"points":10
},{
"text":"Find the maximum and minimum product price. Include just the maximum and minimum product price.",
"start":"",
"key":["Select MAX(price), MIN(price) From sale_product"],
"lines":5,
"points":10
},{
"text":"Find the average quantity of products in a sale. Round to 4 decimal places. Include the average quantity.",
"start":"",
"key":["SELECT ROUND(AVG(qty),4) FROM sale_product"],
"lines":5,
"points":10
},{
"text":"Find the earliest sale time and the latest sale time. The time should be the hour, minute, and second. Name the earliest time ‘earliest’ and the latest time ‘latest.’ Include just the earliest and latest columns in the result.",
"start":"",
"key":["SELECT MIN(strftime('%H:%M:%S', sale_time)) AS earliest, MAX(strftime('%H:%M:%S', sale_time)) AS latest FROM sale"],
"lines":7,
"points":10
},{
"text":"Find how many customer phone numbers there are on file and how many customers are on file. Then calculate the difference (make sure the difference is a positive number). Include the number of phone numbers, number of customers, and the difference.",
"start":"",
"key":["Select COUNT(phone), COUNT(*), COUNT(*)-COUNT(phone) From customer"],
"lines":5,
"points":10
},{
"text":"Find how many different product sizes are available. Include just this number.",
"start":"",
"key":["Select COUNT (DISTINCT product_size) From product_size"],
"lines":5,
"points":10
},{
"text":"Find how much it would cost to buy one of every product at the current product price. Format this amount in dollar form rounded to two decimal places. Include just this formatted amount.",
"start":"",
"key":["Select FORMAT(\"$%.2f\",ROUND(SUM(current_price),2)) From product"],
"lines":5,
"points":10
},{
"text":"Find the average, maximum, and minimum amounts of tax. Name these amounts ‘Average’, ‘Max’, and ‘Min’. Format in dollar format to two decimal places. Include just these three amounts.",
"start":"",
"key":["Select FORMAT(\"$%.2f\",AVG(tax)) AS Average,FORMAT(\"$%.2f\", MAX(tax)) AS Max, FORMAT(\"$%.2f\", MIN(tax)) AS Min From sale"],
"lines":6,
"points":10
},{
"text":"Find the amount of products vendor ‘108’ has produced. Include just the number of products.",
"start":"",
"key":["Select COUNT (product_id) From product WHERE vendor_id = 108"],
"lines":6,
"points":10
},{
"text":"Find the total cost for products in sale 10531270. Include just this sale amount in dollar format.",
"start":"",
"key":["Select FORMAT(\"$%.2f\", SUM(price)) from sale_product WHERE sale_id = 10531270"],
"lines":6,
"points":10
}]
}