{ "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 total tax paid and total shipping paid by customers of each state. Order alphabetically by state. Include the state, total tax, and total shipping costs.",
"start":"",
"key":["SELECT customer.state, SUM(tax) AS sum_tax, SUM(shipping) AS sum_ship FROM customer LEFT OUTER JOIN sale ON sale.customer_id = customer.customer_id GROUP BY customer.state ORDER BY state;"],
"lines":10,
"points":10
},{
"text":"Find the amount of sales each vendor had a product in. Order by vendor ID. Include the vendor ID and amount of sales as ‘sale_num’.",
"start":"",
"key":["SELECT product.vendor_id, COUNT(sale_product.sale_id) AS sale_num FROM product LEFT OUTER JOIN sale_product ON product.product_id = sale_product.product_id GROUP BY vendor_id ORDER BY vendor_id;"],
"lines":12,
"points":10
},{
"text":"Find the average price paid by the customers with the last names ‘Abdee’, ‘Beat’, and ‘Sellick’. Have the average price formatted in correct dollar form. Order by the average price. Include the customer’s last name and the calculated average price as ‘avg_price’.",
"start":"",
"key":["SELECT customer.last_name, FORMAT(\"$%.2f\",AVG(sale_product.price)) AS avg_price FROM customer, sale, sale_product WHERE customer.customer_id = sale.customer_id AND sale.sale_id = sale_product.sale_id AND (last_name = 'Abdee' OR last_name = 'Beat' OR last_name = 'Sellick') GROUP BY last_name ORDER BY avg_price;"],
"lines":16,
"points":10
},{
"text":"Create a list of customers who have had exactly 4 sales. Order by customer last name in reverse alphabetical order. Include the customers last name, the customer ID and the amount of sales as ‘total_sales’.",
"start":"",
"key":["SELECT customer.last_name, sale.customer_id, COUNT(sale.sale_id) AS total_sales FROM sale, customer WHERE customer.customer_id = sale.customer_id GROUP BY sale.customer_id HAVING total_sales = 4 ORDER BY last_name DESC;"],
"lines":14,
"points":10
},{
"text":"Create a list of products that have an embellishment type of 'Tackle Twill Applique'. Include a column that is the number of different sizes available for the product called ‘sizes’. Order by this new column ‘sizes’ in descending order and then by product ID. Include the product name, product ID, and the new ‘sizes’ column.",
"start":"",
"key":["SELECT product.name, product.product_id, COUNT(product_size.product_size) as sizes FROM product LEFT OUTER JOIN product_size ON product.product_id = product_size.product_id GROUP BY product.product_id HAVING product.embellishment_type = 'Tackle Twill Applique' ORDER BY sizes DESC, product.product_id;"],
"lines":16,
"points":10
},{
"text":"Find the maximum amount of products sold in a sale for each product. Create a list of products with a maximum quantity of 8 to 10 items. Order first by the maximum quantity amount and then by sale ID, both in ascending order. Include the product ID, sale ID, and the maximum quantity.",
"start":"",
"key":["SELECT product_id, sale_id, MAX(qty) AS max_qty FROM sale_product GROUP BY product_id HAVING max_qty BETWEEN 8 AND 10 ORDER BY max_qty, sale_id;"],
"lines":9,
"points":10
},{
"text":"Find the minimum sale price for each product. Create a list of products that have a minimum price greater than $100. Order by product ID. Include the product ID, sale ID, and minimum price amount.",
"start":"",
"key":["SELECT product_id, sale_id, MIN(price) AS min_price FROM sale_product GROUP BY product_id HAVING min_price > 100 ORDER BY product_id;"],
"lines":9,
"points":10
},{
"text":"For each vendor with a product, find the average current price of products that have a single sku catalog entry. Order by vendor ID. Include the vendor ID and average price as ‘avg_price’.",
"start":"",
"key":["SELECT vendor_id, AVG(current_price) AS avg_price FROM product WHERE single_sku_catalog_entry IS NOT NULL GROUP BY vendor_id ORDER BY vendor_id;"],
"lines":9,
"points":10
},{
"text":"Find the sum of product prices for products with a ‘Classic’ fit. Split the total by vendors. Order by the total cost in descending order. Include the name of the vendor and the total cost.",
"start":"",
"key":["SELECT vendor.name, SUM(product.current_price) AS total_cost FROM vendor LEFT OUTER JOIN product ON vendor.vendor_id = product.vendor_id WHERE fit = 'Classic' GROUP BY vendor.vendor_id ORDER BY total_cost DESC;"],
"lines":12,
"points":10
},{
"text":"Find the total number sold of each imported product. Create a list of these products that have a total of 0 or 1 item sold. Order alphabetically by product name. Include the product name and the total quantity.",
"start":"",
"key":["SELECT product.name, SUM(qty) AS total_qty FROM product LEFT OUTER JOIN sale_product ON sale_product.product_id = product.product_id WHERE product.origin = 'Imported' GROUP BY product.product_id HAVING total_qty IS NULL OR total_qty = 1 ORDER BY product.name;"],
"lines":16,
"points":10
}]
}
"connection":"sql_book_connection",
"diagram":"hudson-sales",
"schema":"hudsonu",
"owner":"hudsonu",
"scoreVisibility":["student","professor"],
"tasks":[{
"text":"Find the total tax paid and total shipping paid by customers of each state. Order alphabetically by state. Include the state, total tax, and total shipping costs.",
"start":"",
"key":["SELECT customer.state, SUM(tax) AS sum_tax, SUM(shipping) AS sum_ship FROM customer LEFT OUTER JOIN sale ON sale.customer_id = customer.customer_id GROUP BY customer.state ORDER BY state;"],
"lines":10,
"points":10
},{
"text":"Find the amount of sales each vendor had a product in. Order by vendor ID. Include the vendor ID and amount of sales as ‘sale_num’.",
"start":"",
"key":["SELECT product.vendor_id, COUNT(sale_product.sale_id) AS sale_num FROM product LEFT OUTER JOIN sale_product ON product.product_id = sale_product.product_id GROUP BY vendor_id ORDER BY vendor_id;"],
"lines":12,
"points":10
},{
"text":"Find the average price paid by the customers with the last names ‘Abdee’, ‘Beat’, and ‘Sellick’. Have the average price formatted in correct dollar form. Order by the average price. Include the customer’s last name and the calculated average price as ‘avg_price’.",
"start":"",
"key":["SELECT customer.last_name, FORMAT(\"$%.2f\",AVG(sale_product.price)) AS avg_price FROM customer, sale, sale_product WHERE customer.customer_id = sale.customer_id AND sale.sale_id = sale_product.sale_id AND (last_name = 'Abdee' OR last_name = 'Beat' OR last_name = 'Sellick') GROUP BY last_name ORDER BY avg_price;"],
"lines":16,
"points":10
},{
"text":"Create a list of customers who have had exactly 4 sales. Order by customer last name in reverse alphabetical order. Include the customers last name, the customer ID and the amount of sales as ‘total_sales’.",
"start":"",
"key":["SELECT customer.last_name, sale.customer_id, COUNT(sale.sale_id) AS total_sales FROM sale, customer WHERE customer.customer_id = sale.customer_id GROUP BY sale.customer_id HAVING total_sales = 4 ORDER BY last_name DESC;"],
"lines":14,
"points":10
},{
"text":"Create a list of products that have an embellishment type of 'Tackle Twill Applique'. Include a column that is the number of different sizes available for the product called ‘sizes’. Order by this new column ‘sizes’ in descending order and then by product ID. Include the product name, product ID, and the new ‘sizes’ column.",
"start":"",
"key":["SELECT product.name, product.product_id, COUNT(product_size.product_size) as sizes FROM product LEFT OUTER JOIN product_size ON product.product_id = product_size.product_id GROUP BY product.product_id HAVING product.embellishment_type = 'Tackle Twill Applique' ORDER BY sizes DESC, product.product_id;"],
"lines":16,
"points":10
},{
"text":"Find the maximum amount of products sold in a sale for each product. Create a list of products with a maximum quantity of 8 to 10 items. Order first by the maximum quantity amount and then by sale ID, both in ascending order. Include the product ID, sale ID, and the maximum quantity.",
"start":"",
"key":["SELECT product_id, sale_id, MAX(qty) AS max_qty FROM sale_product GROUP BY product_id HAVING max_qty BETWEEN 8 AND 10 ORDER BY max_qty, sale_id;"],
"lines":9,
"points":10
},{
"text":"Find the minimum sale price for each product. Create a list of products that have a minimum price greater than $100. Order by product ID. Include the product ID, sale ID, and minimum price amount.",
"start":"",
"key":["SELECT product_id, sale_id, MIN(price) AS min_price FROM sale_product GROUP BY product_id HAVING min_price > 100 ORDER BY product_id;"],
"lines":9,
"points":10
},{
"text":"For each vendor with a product, find the average current price of products that have a single sku catalog entry. Order by vendor ID. Include the vendor ID and average price as ‘avg_price’.",
"start":"",
"key":["SELECT vendor_id, AVG(current_price) AS avg_price FROM product WHERE single_sku_catalog_entry IS NOT NULL GROUP BY vendor_id ORDER BY vendor_id;"],
"lines":9,
"points":10
},{
"text":"Find the sum of product prices for products with a ‘Classic’ fit. Split the total by vendors. Order by the total cost in descending order. Include the name of the vendor and the total cost.",
"start":"",
"key":["SELECT vendor.name, SUM(product.current_price) AS total_cost FROM vendor LEFT OUTER JOIN product ON vendor.vendor_id = product.vendor_id WHERE fit = 'Classic' GROUP BY vendor.vendor_id ORDER BY total_cost DESC;"],
"lines":12,
"points":10
},{
"text":"Find the total number sold of each imported product. Create a list of these products that have a total of 0 or 1 item sold. Order alphabetically by product name. Include the product name and the total quantity.",
"start":"",
"key":["SELECT product.name, SUM(qty) AS total_qty FROM product LEFT OUTER JOIN sale_product ON sale_product.product_id = product.product_id WHERE product.origin = 'Imported' GROUP BY product.product_id HAVING total_qty IS NULL OR total_qty = 1 ORDER BY product.name;"],
"lines":16,
"points":10
}]
}