{ "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 states where a customer or a vendor is from. Order in reverse alphabetical order. Include just the state abbreviation.",
"start":"",
"key":["SELECT state FROM customer UNION SELECT state FROM vendor ORDER BY state DESC;"],
"lines":12,
"points":10
},{
"text":"Using ‘UNION’, create a list of vendors who have a phone number or a fax number on file. There shouldn’t be any repeated information. Order by vendor ID. Include all information from the vendor table.",
"start":"",
"key":["SELECT * FROM vendor WHERE phone IS NOT NULL UNION SELECT * FROM vendor WHERE fax IS NOT NULL ORDER BY vendor_id;"],
"lines":16,
"points":10
},{
"text":"Write a query to list all customers who are from states that start with ‘H’. Write a separate query to list all customers who are from states that start with ‘S’. Combine the queries using ‘UNION.’ Sort the customers first by last name then first name. Include the customer's last name, first name, and state.",
"start":"",
"key":["SELECT last_name, first_name, state FROM customer WHERE state IN ('HI') UNION SELECT last_name, first_name, state FROM customer WHERE state IN ('SC', 'SD') ORDER BY last_name, first_name;"],
"lines":16,
"points":10
},{
"text":"Using ‘UNION’, create a list of vendors who are located in Hanover, PA or in CA. Order by vendor ID. Include the vendor ID, name, URL, city and state.",
"start":"",
"key":["SELECT vendor_id, name, url, city, state FROM vendor WHERE city = 'Hanover' UNION SELECT vendor_id, name, url, city, state FROM vendor WHERE state = 'CA' ORDER BY vendor_id;"],
"lines":16,
"points":10
},{
"text":"Create a list of product ID’s that include products sized as ‘1SIZE’, products with no origin listed, and products that are sold in a group of 15. Order by product ID. Include only the product ID.",
"start":"",
"key":["SELECT product_id FROM product_size WHERE product_size = '1SIZE' UNION SELECT product_id FROM product WHERE origin IS NULL UNION SELECT product_id FROM sale_product WHERE qty = 15 ORDER BY product_id;"],
"lines":20,
"points":10
},{
"text":"List the monetary amounts in the table that are $0-$1. This includes tax, shipping, total sale price, and product current price. List them in a column named ‘amount’ ordered in ascending order.",
"start":"",
"key":["SELECT tax AS amount FROM sale WHERE amount BETWEEN 0 AND 0.1 UNION SELECT shipping AS amount FROM sale WHERE amount BETWEEN 0 AND 0.1 UNION SELECT price AS amount FROM sale_product WHERE amount BETWEEN 0 AND 0.1 UNION SELECT current_price AS amount FROM product WHERE amount BETWEEN 0 AND 0.1 ORDER BY amount;"],
"lines":25,
"points":10
},{
"text":"List the monetary amounts in the table that are $0-$0.50. This includes tax, shipping, sale price, and product current price. Include repeated amounts. List them in a column named ‘amount’ ordered in ascending order.",
"start":"",
"key":["SELECT tax AS amount FROM sale WHERE amount BETWEEN 0 AND 0.5 UNION ALL SELECT shipping FROM sale WHERE shipping BETWEEN 0 AND 0.5 UNION ALL SELECT price FROM sale_product WHERE price BETWEEN 0 AND 0.5 UNION ALL SELECT current_price FROM product WHERE current_price BETWEEN 0 AND 0.5 ORDER BY amount;"],
"lines":25,
"points":10
},{
"text":"Find the customer that does not have any sales associated with them and the vendor who does not have any products associated with them. List the ID of the customer and vendor in a column named ‘ID’ and list the last name of the customer and the name of the vendor in a column named ‘NAME’. Included should be the ‘ID’ column and the ‘NAME’ column.",
"start":"",
"key":["SELECT customer.customer_id AS id, last_name AS name FROM customer LEFT OUTER JOIN sale ON sale.customer_id = customer.customer_id WHERE sale_id IS NULL UNION SELECT vendor.vendor_id, vendor.name FROM vendor LEFT OUTER JOIN product ON product.vendor_id = vendor.vendor_id WHERE product_id IS NULL ORDER BY id;"],
"lines":18,
"points":10
},{
"text":"Create a list of product ID’s that have a ‘Plus Size’ fit or a ‘3XLarge’ size option. Make sure to list all product ID’s only once. Include the product ID and a column named fit that includes the fit and size.",
"start":"",
"key":["SELECT product_id, fit FROM product WHERE fit = 'Plus Size' UNION SELECT product_id, product_size FROM product_size WHERE product_size = '3XLarge' ORDER BY product_id; "],
"lines":16,
"points":10
},{
"text":"Create a list of products who cost more than $400. This includes the current product price and the price paid for potentially more than one of the same product. Include repeated product ID’s. Order by price in descending order. Include the product ID and the price.",
"start":"",
"key":["SELECT product_id, price FROM sale_product WHERE price > 400 UNION ALL SELECT product_id, current_price FROM product WHERE current_price > 400 ORDER BY current_price DESC; "],
"lines":16,
"points":10
}]
}
"connection":"sql_book_connection",
"diagram":"hudson-sales",
"schema":"hudsonu",
"owner":"hudsonu",
"scoreVisibility":["student","professor"],
"tasks":[{
"text":"Create a list of states where a customer or a vendor is from. Order in reverse alphabetical order. Include just the state abbreviation.",
"start":"",
"key":["SELECT state FROM customer UNION SELECT state FROM vendor ORDER BY state DESC;"],
"lines":12,
"points":10
},{
"text":"Using ‘UNION’, create a list of vendors who have a phone number or a fax number on file. There shouldn’t be any repeated information. Order by vendor ID. Include all information from the vendor table.",
"start":"",
"key":["SELECT * FROM vendor WHERE phone IS NOT NULL UNION SELECT * FROM vendor WHERE fax IS NOT NULL ORDER BY vendor_id;"],
"lines":16,
"points":10
},{
"text":"Write a query to list all customers who are from states that start with ‘H’. Write a separate query to list all customers who are from states that start with ‘S’. Combine the queries using ‘UNION.’ Sort the customers first by last name then first name. Include the customer's last name, first name, and state.",
"start":"",
"key":["SELECT last_name, first_name, state FROM customer WHERE state IN ('HI') UNION SELECT last_name, first_name, state FROM customer WHERE state IN ('SC', 'SD') ORDER BY last_name, first_name;"],
"lines":16,
"points":10
},{
"text":"Using ‘UNION’, create a list of vendors who are located in Hanover, PA or in CA. Order by vendor ID. Include the vendor ID, name, URL, city and state.",
"start":"",
"key":["SELECT vendor_id, name, url, city, state FROM vendor WHERE city = 'Hanover' UNION SELECT vendor_id, name, url, city, state FROM vendor WHERE state = 'CA' ORDER BY vendor_id;"],
"lines":16,
"points":10
},{
"text":"Create a list of product ID’s that include products sized as ‘1SIZE’, products with no origin listed, and products that are sold in a group of 15. Order by product ID. Include only the product ID.",
"start":"",
"key":["SELECT product_id FROM product_size WHERE product_size = '1SIZE' UNION SELECT product_id FROM product WHERE origin IS NULL UNION SELECT product_id FROM sale_product WHERE qty = 15 ORDER BY product_id;"],
"lines":20,
"points":10
},{
"text":"List the monetary amounts in the table that are $0-$1. This includes tax, shipping, total sale price, and product current price. List them in a column named ‘amount’ ordered in ascending order.",
"start":"",
"key":["SELECT tax AS amount FROM sale WHERE amount BETWEEN 0 AND 0.1 UNION SELECT shipping AS amount FROM sale WHERE amount BETWEEN 0 AND 0.1 UNION SELECT price AS amount FROM sale_product WHERE amount BETWEEN 0 AND 0.1 UNION SELECT current_price AS amount FROM product WHERE amount BETWEEN 0 AND 0.1 ORDER BY amount;"],
"lines":25,
"points":10
},{
"text":"List the monetary amounts in the table that are $0-$0.50. This includes tax, shipping, sale price, and product current price. Include repeated amounts. List them in a column named ‘amount’ ordered in ascending order.",
"start":"",
"key":["SELECT tax AS amount FROM sale WHERE amount BETWEEN 0 AND 0.5 UNION ALL SELECT shipping FROM sale WHERE shipping BETWEEN 0 AND 0.5 UNION ALL SELECT price FROM sale_product WHERE price BETWEEN 0 AND 0.5 UNION ALL SELECT current_price FROM product WHERE current_price BETWEEN 0 AND 0.5 ORDER BY amount;"],
"lines":25,
"points":10
},{
"text":"Find the customer that does not have any sales associated with them and the vendor who does not have any products associated with them. List the ID of the customer and vendor in a column named ‘ID’ and list the last name of the customer and the name of the vendor in a column named ‘NAME’. Included should be the ‘ID’ column and the ‘NAME’ column.",
"start":"",
"key":["SELECT customer.customer_id AS id, last_name AS name FROM customer LEFT OUTER JOIN sale ON sale.customer_id = customer.customer_id WHERE sale_id IS NULL UNION SELECT vendor.vendor_id, vendor.name FROM vendor LEFT OUTER JOIN product ON product.vendor_id = vendor.vendor_id WHERE product_id IS NULL ORDER BY id;"],
"lines":18,
"points":10
},{
"text":"Create a list of product ID’s that have a ‘Plus Size’ fit or a ‘3XLarge’ size option. Make sure to list all product ID’s only once. Include the product ID and a column named fit that includes the fit and size.",
"start":"",
"key":["SELECT product_id, fit FROM product WHERE fit = 'Plus Size' UNION SELECT product_id, product_size FROM product_size WHERE product_size = '3XLarge' ORDER BY product_id; "],
"lines":16,
"points":10
},{
"text":"Create a list of products who cost more than $400. This includes the current product price and the price paid for potentially more than one of the same product. Include repeated product ID’s. Order by price in descending order. Include the product ID and the price.",
"start":"",
"key":["SELECT product_id, price FROM sale_product WHERE price > 400 UNION ALL SELECT product_id, current_price FROM product WHERE current_price > 400 ORDER BY current_price DESC; "],
"lines":16,
"points":10
}]
}