{ "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 white, spring products ordered alphabetically by product name. Include the name of the product.",
"start":"",
"key":["SELECT name from product WHERE season_code = 'Spring' AND color_code = 'White' ORDER BY name;"],
"lines":7,
"points":10
},{
"text":"Create a list of products that are screen printed and maroon. List alphabetically by graphic type then product name. Include just the graphic type and product name in the final list.",
"start":"",
"key":["SELECT graphic_type, name FROM product WHERE embellishment_type = 'Screen Print' AND color_code = 'Maroon' ORDER BY graphic_type, name;"],
"lines":7,
"points":10
},{
"text":"Create a list of products specifically for basketball or football season. Order by most expensive to least expensive item. List the product name and current price.",
"start":"",
"key":["SELECT name, current_price from product WHERE season_code = 'Basketball Hot Market' OR season_code = 'Football Hot Market' ORDER BY current_price DESC;"],
"lines":8,
"points":10
},{
"text":"Create a list of customers from Alaska or Hawaii. Order alphabetically by last name then first name. Include the first name, last name, and state.",
"start":"",
"key":["SELECT first_name, last_name, state FROM customer WHERE state = 'AK' OR state = 'HI' ORDER BY last_name, first_name; "],
"lines":7,
"points":10
},{
"text":"Create a list of graphic types for products for men or women that are 100% cotton. The final list should be in alphabetical order. Use the OR operator. Include the graphic type.",
"start":"",
"key":["SELECT DISTINCT graphic_type from product WHERE (category = 'Men' OR category = 'Women') AND fabric ='100% Cotton' ORDER BY graphic_type;"],
"lines":7,
"points":10
},{
"text":"Create a list of products that are in stock at the store and in the electronic catalog, electronic category, or the electronic subcategory. Sort alphabetically by product name. Include just the product name.",
"start":"",
"key":["SELECT name FROM product WHERE in_store = 'Y' AND (catalog = 'Electronics' OR category = 'Electronics' OR subcategory = 'Electronics') ORDER BY name; "],
"lines":8,
"points":10
},{
"text":"Create a list of graphic types for products for men or women that are 100% cotton. The final list should be in alphabetical order. Use the IN operator. Include the graphic type.",
"start":"",
"key":["SELECT DISTINCT graphic_type from product WHERE category IN ('Men','Women') AND fabric ='100% Cotton' ORDER BY graphic_type;"],
"lines":7,
"points":10
},{
"text":"Create a list of products from vendors 120-125 that do not have a single sku catalog entry. Order by vendor ID in ascending order. Include the vendor ID and the product name.",
"start":"",
"key":["Select vendor_id, name from product WHERE vendor_id IN (120, 121, 122, 123, 124, 125) AND single_sku_catalog_entry IS NULL ORDER BY vendor_id;"],
"lines":8,
"points":10
},{
"text":"Create a list of vendor states that are not California. Order by state in reverse alphabetical order. Include the state.",
"start":"",
"key":["SELECT DISTINCT state from vendor WHERE NOT state = 'CA' ORDER BY state DESC;"],
"lines":7,
"points":10
},{
"text":"Using the NOT operator, create a list of vendors who do have a fax number. List in reverse alphabetical order by vendor name. Include the fax number and the vendor name.",
"start":"",
"key":["Select fax, name from vendor WHERE NOT fax IS NULL ORDER BY name DESC;"],
"lines":7,
"points":10
}]
}
"connection":"sql_book_connection",
"diagram":"hudson-sales",
"schema":"hudsonu",
"owner":"hudsonu",
"scoreVisibility":["student","professor"],
"tasks":[{
"text":"Create a list of white, spring products ordered alphabetically by product name. Include the name of the product.",
"start":"",
"key":["SELECT name from product WHERE season_code = 'Spring' AND color_code = 'White' ORDER BY name;"],
"lines":7,
"points":10
},{
"text":"Create a list of products that are screen printed and maroon. List alphabetically by graphic type then product name. Include just the graphic type and product name in the final list.",
"start":"",
"key":["SELECT graphic_type, name FROM product WHERE embellishment_type = 'Screen Print' AND color_code = 'Maroon' ORDER BY graphic_type, name;"],
"lines":7,
"points":10
},{
"text":"Create a list of products specifically for basketball or football season. Order by most expensive to least expensive item. List the product name and current price.",
"start":"",
"key":["SELECT name, current_price from product WHERE season_code = 'Basketball Hot Market' OR season_code = 'Football Hot Market' ORDER BY current_price DESC;"],
"lines":8,
"points":10
},{
"text":"Create a list of customers from Alaska or Hawaii. Order alphabetically by last name then first name. Include the first name, last name, and state.",
"start":"",
"key":["SELECT first_name, last_name, state FROM customer WHERE state = 'AK' OR state = 'HI' ORDER BY last_name, first_name; "],
"lines":7,
"points":10
},{
"text":"Create a list of graphic types for products for men or women that are 100% cotton. The final list should be in alphabetical order. Use the OR operator. Include the graphic type.",
"start":"",
"key":["SELECT DISTINCT graphic_type from product WHERE (category = 'Men' OR category = 'Women') AND fabric ='100% Cotton' ORDER BY graphic_type;"],
"lines":7,
"points":10
},{
"text":"Create a list of products that are in stock at the store and in the electronic catalog, electronic category, or the electronic subcategory. Sort alphabetically by product name. Include just the product name.",
"start":"",
"key":["SELECT name FROM product WHERE in_store = 'Y' AND (catalog = 'Electronics' OR category = 'Electronics' OR subcategory = 'Electronics') ORDER BY name; "],
"lines":8,
"points":10
},{
"text":"Create a list of graphic types for products for men or women that are 100% cotton. The final list should be in alphabetical order. Use the IN operator. Include the graphic type.",
"start":"",
"key":["SELECT DISTINCT graphic_type from product WHERE category IN ('Men','Women') AND fabric ='100% Cotton' ORDER BY graphic_type;"],
"lines":7,
"points":10
},{
"text":"Create a list of products from vendors 120-125 that do not have a single sku catalog entry. Order by vendor ID in ascending order. Include the vendor ID and the product name.",
"start":"",
"key":["Select vendor_id, name from product WHERE vendor_id IN (120, 121, 122, 123, 124, 125) AND single_sku_catalog_entry IS NULL ORDER BY vendor_id;"],
"lines":8,
"points":10
},{
"text":"Create a list of vendor states that are not California. Order by state in reverse alphabetical order. Include the state.",
"start":"",
"key":["SELECT DISTINCT state from vendor WHERE NOT state = 'CA' ORDER BY state DESC;"],
"lines":7,
"points":10
},{
"text":"Using the NOT operator, create a list of vendors who do have a fax number. List in reverse alphabetical order by vendor name. Include the fax number and the vendor name.",
"start":"",
"key":["Select fax, name from vendor WHERE NOT fax IS NULL ORDER BY name DESC;"],
"lines":7,
"points":10
}]
}