{ "text":"Welcome to the Hudson University Bookstore! As the Merchandise Coordinator, you have a crucial role in curating an exceptional selection of specialty items that capture the spirit and essence of Hudson University. From branded apparel and accessories to customized gifts and unique memorabilia, you will be responsible for ensuring that the merchandise reflects the pride and identity of Hudson University.
Remember that lists don’t include duplicates. When a question asks for a list, make sure your query produces a record set with no duplicates.", "connection":"sql_book_connection", "diagram":"hudson-sales", "schema":"hudsonu", "owner":"hudsonu", "scoreVisibility":["student","professor"], "tasks":[{ "text":"Different seasons warrant different apparel offerings. As the merchandise coordinator, you need to know what is already being offered for each season. To start, create a list of white, spring products ordered alphabetically by product name. Include the name of the product.", "start":"", "key":["SELECT DISTINCT name from product WHERE season_code = 'Spring' AND color_code = 'White' ORDER BY name;"], "lines":7, "points":10 },{ "text":"Since you are new, you need to get to know the products currently offered at the bookstore. To start, Create a list of products that are screen printed and maroon. List alphabetically by graphic type then product name. Include the graphic type and product name in the final list.", "start":"", "key":["SELECT DISTINCT graphic_type, name FROM product WHERE embellishment_type = 'Screen Print' AND color_code = 'Maroon' ORDER BY graphic_type, name;"], "lines":7, "points":10 },{ "text":"Different seasons not only mean the natural season but also sport seasons. Create a list of products specifically for “Basketball Hot Market” or “Football Hot Market” seasons. Order by most expensive to least expensive item. List the product name and current price.", "start":"", "key":["SELECT DISTINCT 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":"People from different places usually buy different types of items. To get to know your customer basis, retrieve the names of customers from Alaska or Hawaii, the two weather extremes. 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":"You want to make sure the bookstore offerings are not only for the trendy and stylish but also for the college student who just wants to be comfy. 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":"As you're looking through the products, you realize that you aren’t just in charge of clothing. The bookstore also offers electronics! To get to know more of the products offered at the bookstore, retrieve the names 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":"As you go through the products, you realize you’ve deleted the query result that showed the comfy clothing option. Go back and recreate a list of graphic types for products for men or women that are 100% cotton. The final list should be in alphabetical order. This time, 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":"As you’re getting more of an idea of what products are available in the catalog, you realize that some products may not have a single SKU catalog entry in the bookstore database. You want to start adding SKUs for all products but need to know what products are missing them. 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":"You want to create a good relationship with your current vendors in order to more easily ask them for specific products. To start, 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":"As you continue to get to know your vendors, you want to send them a faxed “thank you for doing business with us” letter. To make sure you’re able to contact all the vendors, using the NOT operator, retrieve the names of vendors who do have a fax number. Order by vendor name in reverse alphabetical order. 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 }] }
Remember that lists don’t include duplicates. When a question asks for a list, make sure your query produces a record set with no duplicates.", "connection":"sql_book_connection", "diagram":"hudson-sales", "schema":"hudsonu", "owner":"hudsonu", "scoreVisibility":["student","professor"], "tasks":[{ "text":"Different seasons warrant different apparel offerings. As the merchandise coordinator, you need to know what is already being offered for each season. To start, create a list of white, spring products ordered alphabetically by product name. Include the name of the product.", "start":"", "key":["SELECT DISTINCT name from product WHERE season_code = 'Spring' AND color_code = 'White' ORDER BY name;"], "lines":7, "points":10 },{ "text":"Since you are new, you need to get to know the products currently offered at the bookstore. To start, Create a list of products that are screen printed and maroon. List alphabetically by graphic type then product name. Include the graphic type and product name in the final list.", "start":"", "key":["SELECT DISTINCT graphic_type, name FROM product WHERE embellishment_type = 'Screen Print' AND color_code = 'Maroon' ORDER BY graphic_type, name;"], "lines":7, "points":10 },{ "text":"Different seasons not only mean the natural season but also sport seasons. Create a list of products specifically for “Basketball Hot Market” or “Football Hot Market” seasons. Order by most expensive to least expensive item. List the product name and current price.", "start":"", "key":["SELECT DISTINCT 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":"People from different places usually buy different types of items. To get to know your customer basis, retrieve the names of customers from Alaska or Hawaii, the two weather extremes. 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":"You want to make sure the bookstore offerings are not only for the trendy and stylish but also for the college student who just wants to be comfy. 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":"As you're looking through the products, you realize that you aren’t just in charge of clothing. The bookstore also offers electronics! To get to know more of the products offered at the bookstore, retrieve the names 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":"As you go through the products, you realize you’ve deleted the query result that showed the comfy clothing option. Go back and recreate a list of graphic types for products for men or women that are 100% cotton. The final list should be in alphabetical order. This time, 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":"As you’re getting more of an idea of what products are available in the catalog, you realize that some products may not have a single SKU catalog entry in the bookstore database. You want to start adding SKUs for all products but need to know what products are missing them. 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":"You want to create a good relationship with your current vendors in order to more easily ask them for specific products. To start, 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":"As you continue to get to know your vendors, you want to send them a faxed “thank you for doing business with us” letter. To make sure you’re able to contact all the vendors, using the NOT operator, retrieve the names of vendors who do have a fax number. Order by vendor name in reverse alphabetical order. 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 }] }