{ "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 from the ‘New England’ division. Order by customer ID in ascending order. Include the customer ID and state.",
"start":"",
"key":["SELECT customer_id, state FROM customer, region WHERE customer.state = region.state_code AND region.division = 'New England' ORDER BY customer.customer_id;"],
"lines":10,
"points":10
},{
"text":"Create a list of ‘Small’ sized products that are not currently in the store. Order in ascending order by product ID. Include the product ID and the product name.",
"start":"",
"key":["SELECT p.product_id, p.name FROM product AS p, product_size AS ps WHERE p.product_id = ps.product_id AND p.in_store = 'N' AND ps.product_size = 'Small' ORDER BY p.product_id;"],
"lines":10,
"points":10
},{
"text":"Create a list of customers who bought the ‘Hudson University Dog Collar’. Order alphabetically by last name. Include the customer's first name, last name, and the sale ID.",
"start":"",
"key":["SELECT c.first_name, c.last_name, s.sale_id FROM customer AS c, sale AS s, sale_product AS sp, product AS p WHERE c.customer_id = s.customer_id AND s.sale_id = sp.sale_id AND sp.product_id = p. product_id AND p.name = 'Hudson University Dog Collar' ORDER BY c.last_name;"],
"lines":12,
"points":10
},{
"text":"Create a list of products in the same sale as product ‘88717602’. Include the product ID, sale ID, product size, and quantity.",
"start":"",
"key":["SELECT sp1.product_id, sp1.sale_id, sp1.product_size, sp1.price, sp1.qty FROM sale_product AS sp1, sale_product AS sp2 WHERE sp1.sale_id = sp2.sale_id AND sp2.product_id = 88717602;"],
"lines":7,
"points":10
},{
"text":"Create a list of vendors that are in the same state as the vendor called ‘Picnic Time’. Order in ascending order by vendor ID. Include the vendor ID and vendor name.",
"start":"",
"key":["SELECT v1.vendor_id, v1.name FROM vendor AS v1, vendor AS v2 WHERE v1.state = v2.state AND v2.name = 'Picnic Time' ORDER BY v1.vendor_id;"],
"lines":9,
"points":10
},{
"text":"Find the products not included in any sales. Order alphabetically by product name. Include the product ID and product name.",
"start":"",
"key":["SELECT product.product_id, product.name FROM product LEFT OUTER JOIN sale_product ON product.product_id = sale_product.product_id WHERE sale_id IS NULL ORDER BY product.name;"],
"lines":10,
"points":10
},{
"text":"Create a list of products under $20. Order the list first by price in descending order, then name, then product size. Include the current price, product name, and product size.",
"start":"",
"key":["SELECT product.current_price, product.name, product_size.product_size FROM product LEFT OUTER JOIN product_size ON product.product_id = product_size.product_id WHERE current_price < 20 ORDER BY current_price DESC, name, product_size;"],
"lines":10,
"points":10
},{
"text":"Create a list of emails for customers who bought a product from vendor ‘100’. Order alphabetically by email. Include just the email.",
"start":"",
"key":["SELECT DISTINCT customer.email FROM customer, sale, sale_product, product WHERE customer.customer_id = sale.customer_id AND sale.sale_id = sale_product.sale_id AND sale_product.product_id = product.product_id AND vendor_id = 100 ORDER BY email;"],
"lines":10,
"points":10
},{
"text":"Find the sale information for sales with shipping between $5 and $10 and tax less than $3. Order by product ID then sale ID. Include all columns in the sale product table and the shipping and tax columns from the sale table.",
"start":"",
"key":["SELECT sale_product.*, sale.shipping, sale.tax FROM sale_product, sale WHERE sale_product.sale_id = sale.sale_id AND (shipping BETWEEN 5 AND 10) AND Tax > 3 ORDER BY product_id, sale_id;"],
"lines":8,
"points":10
},{
"text":"Find sales and customer information for sales without any shipping or tax purchased by customers from Delaware. Order by zip code then customer ID then sale ID. Include all information from the sale table and the customer’s zip code.",
"start":"",
"key":["SELECT sale.*, customer.zip FROM sale, customer WHERE customer.customer_id = sale.customer_id AND (shipping IS NULL OR tax IS NULL) AND state = 'DE' ORDER BY zip, customer_id, sale_id;"],
"lines":10,
"points":10
}]
}
"connection":"sql_book_connection",
"diagram":"hudson-sales",
"schema":"hudsonu",
"owner":"hudsonu",
"scoreVisibility":["student","professor"],
"tasks":[{
"text":"Create a list of customers from the ‘New England’ division. Order by customer ID in ascending order. Include the customer ID and state.",
"start":"",
"key":["SELECT customer_id, state FROM customer, region WHERE customer.state = region.state_code AND region.division = 'New England' ORDER BY customer.customer_id;"],
"lines":10,
"points":10
},{
"text":"Create a list of ‘Small’ sized products that are not currently in the store. Order in ascending order by product ID. Include the product ID and the product name.",
"start":"",
"key":["SELECT p.product_id, p.name FROM product AS p, product_size AS ps WHERE p.product_id = ps.product_id AND p.in_store = 'N' AND ps.product_size = 'Small' ORDER BY p.product_id;"],
"lines":10,
"points":10
},{
"text":"Create a list of customers who bought the ‘Hudson University Dog Collar’. Order alphabetically by last name. Include the customer's first name, last name, and the sale ID.",
"start":"",
"key":["SELECT c.first_name, c.last_name, s.sale_id FROM customer AS c, sale AS s, sale_product AS sp, product AS p WHERE c.customer_id = s.customer_id AND s.sale_id = sp.sale_id AND sp.product_id = p. product_id AND p.name = 'Hudson University Dog Collar' ORDER BY c.last_name;"],
"lines":12,
"points":10
},{
"text":"Create a list of products in the same sale as product ‘88717602’. Include the product ID, sale ID, product size, and quantity.",
"start":"",
"key":["SELECT sp1.product_id, sp1.sale_id, sp1.product_size, sp1.price, sp1.qty FROM sale_product AS sp1, sale_product AS sp2 WHERE sp1.sale_id = sp2.sale_id AND sp2.product_id = 88717602;"],
"lines":7,
"points":10
},{
"text":"Create a list of vendors that are in the same state as the vendor called ‘Picnic Time’. Order in ascending order by vendor ID. Include the vendor ID and vendor name.",
"start":"",
"key":["SELECT v1.vendor_id, v1.name FROM vendor AS v1, vendor AS v2 WHERE v1.state = v2.state AND v2.name = 'Picnic Time' ORDER BY v1.vendor_id;"],
"lines":9,
"points":10
},{
"text":"Find the products not included in any sales. Order alphabetically by product name. Include the product ID and product name.",
"start":"",
"key":["SELECT product.product_id, product.name FROM product LEFT OUTER JOIN sale_product ON product.product_id = sale_product.product_id WHERE sale_id IS NULL ORDER BY product.name;"],
"lines":10,
"points":10
},{
"text":"Create a list of products under $20. Order the list first by price in descending order, then name, then product size. Include the current price, product name, and product size.",
"start":"",
"key":["SELECT product.current_price, product.name, product_size.product_size FROM product LEFT OUTER JOIN product_size ON product.product_id = product_size.product_id WHERE current_price < 20 ORDER BY current_price DESC, name, product_size;"],
"lines":10,
"points":10
},{
"text":"Create a list of emails for customers who bought a product from vendor ‘100’. Order alphabetically by email. Include just the email.",
"start":"",
"key":["SELECT DISTINCT customer.email FROM customer, sale, sale_product, product WHERE customer.customer_id = sale.customer_id AND sale.sale_id = sale_product.sale_id AND sale_product.product_id = product.product_id AND vendor_id = 100 ORDER BY email;"],
"lines":10,
"points":10
},{
"text":"Find the sale information for sales with shipping between $5 and $10 and tax less than $3. Order by product ID then sale ID. Include all columns in the sale product table and the shipping and tax columns from the sale table.",
"start":"",
"key":["SELECT sale_product.*, sale.shipping, sale.tax FROM sale_product, sale WHERE sale_product.sale_id = sale.sale_id AND (shipping BETWEEN 5 AND 10) AND Tax > 3 ORDER BY product_id, sale_id;"],
"lines":8,
"points":10
},{
"text":"Find sales and customer information for sales without any shipping or tax purchased by customers from Delaware. Order by zip code then customer ID then sale ID. Include all information from the sale table and the customer’s zip code.",
"start":"",
"key":["SELECT sale.*, customer.zip FROM sale, customer WHERE customer.customer_id = sale.customer_id AND (shipping IS NULL OR tax IS NULL) AND state = 'DE' ORDER BY zip, customer_id, sale_id;"],
"lines":10,
"points":10
}]
}