{ "text":"Welcome to Hudson University Bookstore! As the newly appointed Sales Record Auditor, you have a significant role in ensuring accuracy and transparency in the financial operations. With a commitment to maintaining high standards of integrity, your expertise will be essential in thoroughly examining the store records.
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":"Previously, the bookstore did not realize tax is based on the shipping address. To rectify this mistake, you need to know where customers are from. To start, retrieve information about customers who are from NY. List in alphabetical order according to last name. Include the last name and the state.", "start":"", "key":["SELECT last_name state from customer WHERE state = 'NY' ORDER BY last_name;"], "lines":7, "points":10 },{ "text":"Knowing the base price of products is essential to knowing the accuracy of sales records. To get familiar with product pricing, create a list of products that cost $6.95. Sort by product name alphabetically. Include the product ID and name.", "start":"", "key":["SELECT product_id, name FROM product WHERE current_price = 6.95 ORDER BY name;"], "lines":7, "points":10 },{ "text":"To check the accuracy of sales records, you need to look into the most expensive sales. Retrieve the price of sales greater than or equal to $300. Order from most expensive to least expensive price. Include the sale ID and price.", "start":"", "key":["SELECT sale_id , price from sale WHERE price >= 300 ORDER BY price DESC;"], "lines":7, "points":10 },{ "text":"As stated previously, the bookstore has had some confusion on the amount of tax that should be charged and you're trying to figure out what states customers are from in order to correctly calculate tax. To continue this search, list the different states customers are from excluding NY, since you've already found the customers from NY. Include just the state in the list.", "start":"", "key":["SELECT DISTINCT state from customer WHERE state <>'WY' ORDER BY state;"], "lines":7, "points":10 },{ "text":"Vendor location impacts the price of the product and will let you know if product pricing is reasonable. Start getting a better idea of where products are coming from by looking at vendors from specific states. Create a list of vendors from Wyoming. Order alphabetically by vendor name. Include the vendor ID, name, and state.", "start":"", "key":["SELECT vendor_id, name, state from vendor WHERE state = 'WY' ORDER BY name;"], "lines":7, "points":10 },{ "text":"The prices for holiday products change from year to year as inflation increases. You want to see if the new pricing for the holiday products is reasonable. Create a list of holiday products. List by product name in alphabetical order. Include the product name and current price. ", "start":"", "key":["SELECT DISTINCT name, current_price FROM product WHERE season_code = 'Holiday'"], "lines":6, "points":10 },{ "text":"Continuing to look at the most expensive sales by retrieving data for product sales where 10-12 products were bought in the same sale. Order from least expensive to most expensive price. Include the sale ID, product ID, quantity, and price.", "start":"", "key":["SELECT sale_id, product_id, qty, price FROM sale_product WHERE qty BETWEEN 10 AND 12 ORDER BY price;"], "lines":7, "points":10 },{ "text":"Now that you've gotten an idea of the most expensive sales, take a look at some expensive product prices. Retrieve the names of items with a current price between $200 and $250, including those values. Order from most expensive to least expensive item. Include just the product name.", "start":"", "key":["SELECT name FROM product WHERE current_price BETWEEN 200 AND 250 ORDER BY current_price DESC;"], "lines":7, "points":10 },{ "text":"You continue to look to see if the current product pricing is reasonable. You know that it is cheaper to create products that don't have any embellishments and you want to make sure these products are cheaper than embellished products. Start by creating a list of products that don't have any embellishments. List by product ID in ascending order. Include the product name and ID.", "start":"", "key":["SELECT DISTINCT name, product_id from product WHERE embellishment_type IS NULL ORDER BY product_id;"], "lines":7, "points":10 },{ "text":"You need to contact customers who overpaid on tax to give them a refund. The easiest way to contact them would be through email. However, you're not sure if all customers provided an email. Find the names of customers who did not provide an email address. Order by last name in alphabetical order. Include the customers first and last name.", "start":"", "key":["SELECT first_name, last_name FROM customer WHERE email IS NULL ORDER BY last_name;"], "lines":5, "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":"Previously, the bookstore did not realize tax is based on the shipping address. To rectify this mistake, you need to know where customers are from. To start, retrieve information about customers who are from NY. List in alphabetical order according to last name. Include the last name and the state.", "start":"", "key":["SELECT last_name state from customer WHERE state = 'NY' ORDER BY last_name;"], "lines":7, "points":10 },{ "text":"Knowing the base price of products is essential to knowing the accuracy of sales records. To get familiar with product pricing, create a list of products that cost $6.95. Sort by product name alphabetically. Include the product ID and name.", "start":"", "key":["SELECT product_id, name FROM product WHERE current_price = 6.95 ORDER BY name;"], "lines":7, "points":10 },{ "text":"To check the accuracy of sales records, you need to look into the most expensive sales. Retrieve the price of sales greater than or equal to $300. Order from most expensive to least expensive price. Include the sale ID and price.", "start":"", "key":["SELECT sale_id , price from sale WHERE price >= 300 ORDER BY price DESC;"], "lines":7, "points":10 },{ "text":"As stated previously, the bookstore has had some confusion on the amount of tax that should be charged and you're trying to figure out what states customers are from in order to correctly calculate tax. To continue this search, list the different states customers are from excluding NY, since you've already found the customers from NY. Include just the state in the list.", "start":"", "key":["SELECT DISTINCT state from customer WHERE state <>'WY' ORDER BY state;"], "lines":7, "points":10 },{ "text":"Vendor location impacts the price of the product and will let you know if product pricing is reasonable. Start getting a better idea of where products are coming from by looking at vendors from specific states. Create a list of vendors from Wyoming. Order alphabetically by vendor name. Include the vendor ID, name, and state.", "start":"", "key":["SELECT vendor_id, name, state from vendor WHERE state = 'WY' ORDER BY name;"], "lines":7, "points":10 },{ "text":"The prices for holiday products change from year to year as inflation increases. You want to see if the new pricing for the holiday products is reasonable. Create a list of holiday products. List by product name in alphabetical order. Include the product name and current price. ", "start":"", "key":["SELECT DISTINCT name, current_price FROM product WHERE season_code = 'Holiday'"], "lines":6, "points":10 },{ "text":"Continuing to look at the most expensive sales by retrieving data for product sales where 10-12 products were bought in the same sale. Order from least expensive to most expensive price. Include the sale ID, product ID, quantity, and price.", "start":"", "key":["SELECT sale_id, product_id, qty, price FROM sale_product WHERE qty BETWEEN 10 AND 12 ORDER BY price;"], "lines":7, "points":10 },{ "text":"Now that you've gotten an idea of the most expensive sales, take a look at some expensive product prices. Retrieve the names of items with a current price between $200 and $250, including those values. Order from most expensive to least expensive item. Include just the product name.", "start":"", "key":["SELECT name FROM product WHERE current_price BETWEEN 200 AND 250 ORDER BY current_price DESC;"], "lines":7, "points":10 },{ "text":"You continue to look to see if the current product pricing is reasonable. You know that it is cheaper to create products that don't have any embellishments and you want to make sure these products are cheaper than embellished products. Start by creating a list of products that don't have any embellishments. List by product ID in ascending order. Include the product name and ID.", "start":"", "key":["SELECT DISTINCT name, product_id from product WHERE embellishment_type IS NULL ORDER BY product_id;"], "lines":7, "points":10 },{ "text":"You need to contact customers who overpaid on tax to give them a refund. The easiest way to contact them would be through email. However, you're not sure if all customers provided an email. Find the names of customers who did not provide an email address. Order by last name in alphabetical order. Include the customers first and last name.", "start":"", "key":["SELECT first_name, last_name FROM customer WHERE email IS NULL ORDER BY last_name;"], "lines":5, "points":10 }] }