Use your common sense on these. Show the single SQL query and the output AND the

ID: 3830784 • Letter: U

Question

Use your common sense on these. Show the single SQL query and the output AND the answer to the

question. DO NOT provide IDs or codes in the final answer; all tables should have meaningful column

headers. Use whichever database is appropriate. Many of these will be difficult unless you use

SUBQUERIES. For this assignment, define “large city” to be one with more than 3 million people in it.

1. What is the pending balance in all non-business customers’ checking accounts?

2. What are the full names of all vendors who can supply more than one item or are based in Illinois?

3. What is the minimum available balance in all accounts for each customer and overall for all customers?

4. What is the full name of the countries that have more than 3 official languages, and how many does

each one have?

5. Display the number of countries that speak 1 official language, 2 official languages, and so on.

6. Which cities of over three million people are in countries where English is an official language?

7. What is the number of large cities on each continent such that the total “large city population” on the

continent is at least 25 million?

8. Which large cities are in countries with no more than 2 languages spoken?

9. In order of number of languages, what are the names of the countries where 10 or more languages are

spoken and how many languages are spoken in each? Use a single query.

10. Which countries have average city populations for the cities recorded in the database of at least 3

million but no more than 7 million?

1. What is the pending balance in all non-business customers’ checking accounts?
For this question let suppose we have a following table customer

customer_table(cust_name, pending_bal, business_type)

select sum(pending_bal) from customer_table where business_type = 'non-business'

2. What are the full names of all vendors who can supply more than one item or are based in Illinois?

For this question let suppose we have a following table vendor.
vendor_table(full_name, id, item_quanitity, state)

select full_name from vendor_table where item_quanitity > 1 or state = 'Illinois'

6. Which cities of over three million people are in countries where English is an official language?

For this question let suppose we have following table country.
country_table(city, country, official_lang)

select city from country_table where official_lang = 'English'

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.