{ "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":"Create a list of customers who are from NY. List in alphabetical order according to last name. Include the last name and the state.",
"start":"",
"key":["SELECT last_name state from customer WHERE state = 'NY' ORDER BY last_name;"],
"lines":7,
"points":10
},{
"text":"Create a list of products that cost $6.95. Sort by product name alphabetically. Include the product ID and name.",
"start":"",
"key":["SELECT product_id, name FROM product WHERE current_price = 6.95 ORDER BY name;"],
"lines":7,
"points":10
},{
"text":"Create a list of sales greater than or equal to $300. Order the list from most expensive to least expensive sale total. Include the sale ID and total.",
"start":"",
"key":["SELECT sale_id , total from sale WHERE total >= 300 ORDER BY total DESC;"],
"lines":7,
"points":10
},{
"text":"Create a list of sales that have a tax greater than $25. Sort by sale ID in ascending order. Include the sale ID and customer ID.",
"start":"",
"key":["SELECT sale_id, customer_id FROM sale WHERE tax >25 ORDER BY sale_id;"],
"lines":7,
"points":10
},{
"text":"Create a list of vendors from Wyoming. Order alphabetically by vendor name. Include the vendor ID, name, and state.",
"start":"",
"key":["SELECT vendor_id, name, state from vendor WHERE state = 'WY' ORDER BY name;"],
"lines":7,
"points":10
},{
"text":"Create a list of holiday products. List by product name in alphabetical order. Include the product name.",
"start":"",
"key":["SELECT name FROM product WHERE season_code = 'Holiday'"],
"lines":6,
"points":10
},{
"text":"Create a list of product sales where 10-12 products were bought in the same sale. List from least expensive to most expensive price. List the sale ID, product ID, quantity, and price.",
"start":"",
"key":["SELECT sale_id, product_id, qty, price FROM sale_product WHERE qty BETWEEN 10 AND 12 ORDER BY price;"],
"lines":7,
"points":10
},{
"text":"Create a list of items with a current price between $200 and $250, including those values. List from most expensive to least expensive item. Include just the product name.",
"start":"",
"key":["SELECT name FROM product WHERE current_price BETWEEN 200 AND 250 ORDER BY current_price DESC;"],
"lines":7,
"points":10
},{
"text":"Create a list of products that don’t have any embellishments. List by product ID in ascending order. Include the product name and ID.",
"start":"",
"key":["SELECT name, product_id from product WHERE embellishment_type IS NULL ORDER BY product_id;"],
"lines":7,
"points":10
},{
"text":"Create a list of customers who did not provide an email address. Order by last name in alphabetical order. Include the customers first and last name.",
"start":"",
"key":["SELECT email, first_name, last_name FROM customer WHERE email IS NULL ORDER BY last_name;"],
"lines":5,
"points":10
}]
}
"connection":"sql_book_connection",
"diagram":"hudson-sales",
"schema":"hudsonu",
"owner":"hudsonu",
"scoreVisibility":["student","professor"],
"tasks":[{
"text":"Create a list of customers who are from NY. List in alphabetical order according to last name. Include the last name and the state.",
"start":"",
"key":["SELECT last_name state from customer WHERE state = 'NY' ORDER BY last_name;"],
"lines":7,
"points":10
},{
"text":"Create a list of products that cost $6.95. Sort by product name alphabetically. Include the product ID and name.",
"start":"",
"key":["SELECT product_id, name FROM product WHERE current_price = 6.95 ORDER BY name;"],
"lines":7,
"points":10
},{
"text":"Create a list of sales greater than or equal to $300. Order the list from most expensive to least expensive sale total. Include the sale ID and total.",
"start":"",
"key":["SELECT sale_id , total from sale WHERE total >= 300 ORDER BY total DESC;"],
"lines":7,
"points":10
},{
"text":"Create a list of sales that have a tax greater than $25. Sort by sale ID in ascending order. Include the sale ID and customer ID.",
"start":"",
"key":["SELECT sale_id, customer_id FROM sale WHERE tax >25 ORDER BY sale_id;"],
"lines":7,
"points":10
},{
"text":"Create a list of vendors from Wyoming. Order alphabetically by vendor name. Include the vendor ID, name, and state.",
"start":"",
"key":["SELECT vendor_id, name, state from vendor WHERE state = 'WY' ORDER BY name;"],
"lines":7,
"points":10
},{
"text":"Create a list of holiday products. List by product name in alphabetical order. Include the product name.",
"start":"",
"key":["SELECT name FROM product WHERE season_code = 'Holiday'"],
"lines":6,
"points":10
},{
"text":"Create a list of product sales where 10-12 products were bought in the same sale. List from least expensive to most expensive price. List the sale ID, product ID, quantity, and price.",
"start":"",
"key":["SELECT sale_id, product_id, qty, price FROM sale_product WHERE qty BETWEEN 10 AND 12 ORDER BY price;"],
"lines":7,
"points":10
},{
"text":"Create a list of items with a current price between $200 and $250, including those values. List from most expensive to least expensive item. Include just the product name.",
"start":"",
"key":["SELECT name FROM product WHERE current_price BETWEEN 200 AND 250 ORDER BY current_price DESC;"],
"lines":7,
"points":10
},{
"text":"Create a list of products that don’t have any embellishments. List by product ID in ascending order. Include the product name and ID.",
"start":"",
"key":["SELECT name, product_id from product WHERE embellishment_type IS NULL ORDER BY product_id;"],
"lines":7,
"points":10
},{
"text":"Create a list of customers who did not provide an email address. Order by last name in alphabetical order. Include the customers first and last name.",
"start":"",
"key":["SELECT email, first_name, last_name FROM customer WHERE email IS NULL ORDER BY last_name;"],
"lines":5,
"points":10
}]
}