{ "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 vendors. Make all vendor names uppercase in a column called ‘name_up’. Order by vendor name in alphabetical order. Include the uppercase vendor name and phone number.",
"start":"",
"key":["SELECT UPPER (name) AS name_up, phone FROM vendor ORDER BY name;"],
"lines":7,
"points":10
},{
"text":"Create a list of products that are not currently in the bookstore. If any of the product names start with ‘Hudson University’, take the phrase out of the beginning of the product name. The new list should be called ‘new_name’ and should be ordered alphabetically. Include just a list of the new names.",
"start":"",
"key":["SELECT LTRIM(name, 'Hudson University') AS new_name FROM product WHERE in_store = 'N' ORDER BY new_name;"],
"lines":7,
"points":10
},{
"text":"Create a list of products that are size ‘3XLarge’. In this list of product ID’s, make sure there isn’t white space before or after the product ID. List by product ID in ascending order. Include only the product ID.",
"start":"",
"key":["SELECT RTRIM(LTRIM(product_id)) FROM product_size WHERE product_size = '3XLarge' ORDER BY product_ID;"],
"lines":7,
"points":10
},{
"text":"Create a list of sales made on January 1st - 4th. Order in ascending order first by customer ID, then sale ID, then sale time. Include the customer ID, sale ID and sale time.",
"start":"",
"key":["SELECT sale_id, sale_time, customer_id FROM sale WHERE strftime('%m', sale_time) = '01' AND strftime('%d', sale_time) < '05' ORDER BY customer_id, sale_id, sale_time;"],
"lines":8,
"points":10
},{
"text":"Create a list of sales placed on the 31st of any month. Include just the sale ID ordered in descending numerical order.",
"start":"",
"key":["SELECT sale_id FROM sale WHERE strftime('%d', sale_time) = '31' ORDER BY sale_id DESC;"],
"lines":7,
"points":10
},{
"text":"Create a list of sales made any day exactly at noon. Sort by sale ID in ascending order. Include the entire sale time information and the sale ID.",
"start":"",
"key":["SELECT sale_time, sale_id FROM sale WHERE (strftime('%H:%M:%S', sale_time)) = "12:00:00" ORDER BY sale_id;"],
"lines":7,
"points":10
},{
"text":"Create a list of current product prices that are more than $200. Format the list so that it looks like a dollar amount with two decimal places (EX: $200.00). Call the new formatted value ‘format_current_price’. Order by the product's current price in ascending order. Include the new formatted price, the product name, and if the product is in the store.",
"start":"",
"key":["SELECT FORMAT(\"$%.2f\", current_price) AS format_current_price, name, in_store FROM product WHERE current_price > 200 ORDER BY current_price;"],
"lines":8,
"points":10
},{
"text":"Create a list of customers from Utah. List in alphabetical order by last name. Create a new column called ‘full_address’ that includes the customers address, city, state, and zip code formatted in the correct way using commas. Include the customers first name, last name, and full address.",
"start":"",
"key":["SELECT first_name, last_name, FORMAT('%s, %s, %s %s', address, city, state, zip) AS full_address FROM customer WHERE state = 'UT' ORDER BY last_name;"],
"lines":8,
"points":10
},{
"text":"Create a list of prices of men or women sweatshirts. Round the price to the nearest dollar. Order first by product price in descending order then by product ID in ascending order. Include the rounded price, product ID, and product name.",
"start":"",
"key":["SELECT Round(current_price), product_id, name FROM product Where (category = 'Men' OR category = 'Women') AND subcategory = 'Sweatshirts' ORDER BY current_price DESC, product_id;"],
"lines":8,
"points":10
},{
"text":"Create a list of vendors based off of the square root of the vendor address zip code. Create a new column called ‘sqr_rounded_zip’ that is the square root of the zip code rounded to two decimal places. The list should be in ascending order based on this new number. Include this number, the vendor name, and the vendor URL.",
"start":"",
"key":["SELECT ROUND(SQRT(zip),2) AS sqr_rounded_zip, name, url FROM vendor ORDER BY sqr_rounded_zip;"],
"lines":5,
"points":10
}]
}
"connection":"sql_book_connection",
"diagram":"hudson-sales",
"schema":"hudsonu",
"owner":"hudsonu",
"scoreVisibility":["student","professor"],
"tasks":[{
"text":"Create a list of vendors. Make all vendor names uppercase in a column called ‘name_up’. Order by vendor name in alphabetical order. Include the uppercase vendor name and phone number.",
"start":"",
"key":["SELECT UPPER (name) AS name_up, phone FROM vendor ORDER BY name;"],
"lines":7,
"points":10
},{
"text":"Create a list of products that are not currently in the bookstore. If any of the product names start with ‘Hudson University’, take the phrase out of the beginning of the product name. The new list should be called ‘new_name’ and should be ordered alphabetically. Include just a list of the new names.",
"start":"",
"key":["SELECT LTRIM(name, 'Hudson University') AS new_name FROM product WHERE in_store = 'N' ORDER BY new_name;"],
"lines":7,
"points":10
},{
"text":"Create a list of products that are size ‘3XLarge’. In this list of product ID’s, make sure there isn’t white space before or after the product ID. List by product ID in ascending order. Include only the product ID.",
"start":"",
"key":["SELECT RTRIM(LTRIM(product_id)) FROM product_size WHERE product_size = '3XLarge' ORDER BY product_ID;"],
"lines":7,
"points":10
},{
"text":"Create a list of sales made on January 1st - 4th. Order in ascending order first by customer ID, then sale ID, then sale time. Include the customer ID, sale ID and sale time.",
"start":"",
"key":["SELECT sale_id, sale_time, customer_id FROM sale WHERE strftime('%m', sale_time) = '01' AND strftime('%d', sale_time) < '05' ORDER BY customer_id, sale_id, sale_time;"],
"lines":8,
"points":10
},{
"text":"Create a list of sales placed on the 31st of any month. Include just the sale ID ordered in descending numerical order.",
"start":"",
"key":["SELECT sale_id FROM sale WHERE strftime('%d', sale_time) = '31' ORDER BY sale_id DESC;"],
"lines":7,
"points":10
},{
"text":"Create a list of sales made any day exactly at noon. Sort by sale ID in ascending order. Include the entire sale time information and the sale ID.",
"start":"",
"key":["SELECT sale_time, sale_id FROM sale WHERE (strftime('%H:%M:%S', sale_time)) = "12:00:00" ORDER BY sale_id;"],
"lines":7,
"points":10
},{
"text":"Create a list of current product prices that are more than $200. Format the list so that it looks like a dollar amount with two decimal places (EX: $200.00). Call the new formatted value ‘format_current_price’. Order by the product's current price in ascending order. Include the new formatted price, the product name, and if the product is in the store.",
"start":"",
"key":["SELECT FORMAT(\"$%.2f\", current_price) AS format_current_price, name, in_store FROM product WHERE current_price > 200 ORDER BY current_price;"],
"lines":8,
"points":10
},{
"text":"Create a list of customers from Utah. List in alphabetical order by last name. Create a new column called ‘full_address’ that includes the customers address, city, state, and zip code formatted in the correct way using commas. Include the customers first name, last name, and full address.",
"start":"",
"key":["SELECT first_name, last_name, FORMAT('%s, %s, %s %s', address, city, state, zip) AS full_address FROM customer WHERE state = 'UT' ORDER BY last_name;"],
"lines":8,
"points":10
},{
"text":"Create a list of prices of men or women sweatshirts. Round the price to the nearest dollar. Order first by product price in descending order then by product ID in ascending order. Include the rounded price, product ID, and product name.",
"start":"",
"key":["SELECT Round(current_price), product_id, name FROM product Where (category = 'Men' OR category = 'Women') AND subcategory = 'Sweatshirts' ORDER BY current_price DESC, product_id;"],
"lines":8,
"points":10
},{
"text":"Create a list of vendors based off of the square root of the vendor address zip code. Create a new column called ‘sqr_rounded_zip’ that is the square root of the zip code rounded to two decimal places. The list should be in ascending order based on this new number. Include this number, the vendor name, and the vendor URL.",
"start":"",
"key":["SELECT ROUND(SQRT(zip),2) AS sqr_rounded_zip, name, url FROM vendor ORDER BY sqr_rounded_zip;"],
"lines":5,
"points":10
}]
}