{ "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":"Caroline needs to print a phone directory of vendors. Help her by writing a query to show an alphabetical list of vendors and their phone numbers.",
"start":"Select name, phone\nfrom vendor\norder by name",
"key":["Select name, phone\nfrom vendor\norder by name"],
"lines":12,
"points":10
},{
"text":"Caroline also wants a phone list organized by state. Help her by writing a query to show a list of vendors sorted by state, then by vednor name. Show the state, name, and phone columns.",
"start":"Select state, name, phone\nfrom vendor\norder by state, name",
"key":["Select state, name, phone\nfrom vendor\norder by state, name"],
"lines":12,
"points":10
},{
"text":"Jarod needs to review the customers living in Utah; he expects that he will need to contact a few of them. Help him by writing a query to show a list of customers in Utah sorted by last name, then by first name. Show each customer's first name, last name, telephone number and email address",
"start":"SELECT first_name\n ,last_name\n ,phone\n ,email\nFROM customer\nWHERE state = 'UT'\nORDER BY last_name, first_name",
"key":["SELECT first_name\n ,last_name\n ,phone\n ,email\nFROM customer\nWHERE state = 'UT'\nORDER BY last_name, first_name"],
"lines":12,
"points":10
},{
"text":"Jarod needs a phone list of all customers in the pacific division. Help him by writing a query to show a list of customers in that division sorted by full state name then by last name, then by first name. Show each customer's first name, last name, telephone number, and full state name. Only include those customers with phone numbers.",
"start":"SELECT region.state_name\n ,customer.first_name\n ,customer.last_name\n ,customer.phone\nFROM region\n JOIN customer\n ON customer.state = region.state_code\nWHERE division='Pacific'\n and phone is not null",
"key":["SELECT region.state_name\n ,customer.first_name\n ,customer.last_name\n ,customer.phone\nFROM region\n JOIN customer\n ON customer.state = region.state_code\nWHERE division='Pacific'\n and phone is not null"],
"lines":12,
"points":10
}]
}
"connection":"sql_book_connection",
"diagram":"hudson-sales",
"schema":"hudsonu",
"owner":"hudsonu",
"scoreVisibility":["student","professor"],
"tasks":[{
"text":"Caroline needs to print a phone directory of vendors. Help her by writing a query to show an alphabetical list of vendors and their phone numbers.",
"start":"Select name, phone\nfrom vendor\norder by name",
"key":["Select name, phone\nfrom vendor\norder by name"],
"lines":12,
"points":10
},{
"text":"Caroline also wants a phone list organized by state. Help her by writing a query to show a list of vendors sorted by state, then by vednor name. Show the state, name, and phone columns.",
"start":"Select state, name, phone\nfrom vendor\norder by state, name",
"key":["Select state, name, phone\nfrom vendor\norder by state, name"],
"lines":12,
"points":10
},{
"text":"Jarod needs to review the customers living in Utah; he expects that he will need to contact a few of them. Help him by writing a query to show a list of customers in Utah sorted by last name, then by first name. Show each customer's first name, last name, telephone number and email address",
"start":"SELECT first_name\n ,last_name\n ,phone\n ,email\nFROM customer\nWHERE state = 'UT'\nORDER BY last_name, first_name",
"key":["SELECT first_name\n ,last_name\n ,phone\n ,email\nFROM customer\nWHERE state = 'UT'\nORDER BY last_name, first_name"],
"lines":12,
"points":10
},{
"text":"Jarod needs a phone list of all customers in the pacific division. Help him by writing a query to show a list of customers in that division sorted by full state name then by last name, then by first name. Show each customer's first name, last name, telephone number, and full state name. Only include those customers with phone numbers.",
"start":"SELECT region.state_name\n ,customer.first_name\n ,customer.last_name\n ,customer.phone\nFROM region\n JOIN customer\n ON customer.state = region.state_code\nWHERE division='Pacific'\n and phone is not null",
"key":["SELECT region.state_name\n ,customer.first_name\n ,customer.last_name\n ,customer.phone\nFROM region\n JOIN customer\n ON customer.state = region.state_code\nWHERE division='Pacific'\n and phone is not null"],
"lines":12,
"points":10
}]
}