ADVANCED SELECT (world.db)

Question 1

SPECIAL ANNOUNCEMENT!

These flash cards are NOT designed to trick you. They contain VERY TYPICAL queries that any person knowing SQL can be expected to answer with very little difficulty. So if you find them difficult - which is normal in the beginning - DO NOT GIVE UP!

Answer 1

Please use these flash cards in good health and good happiness!

Question 2

Do Ukraine and Russia have any cities in common?

Answer 2

SELECT city 
FROM world_cities 
WHERE country = "Ukraine" 
INTERSECT 
SELECT city 
FROM world_cities WHERE country = "Russia"

INTERSECT is a powerful command. If you use A INTERSECT B you will get all the rows that A and B share in common

Question 3

The United States has many cities that have the same names. Print out a table for the United States that looks like (city name, no. of occurances of city)

Answer 3

SELECT city, COUNT ( city ) 
FROM world_cities 
WHERE country = "United States" 
GROUP BY city
ORDER BY city ASC;

This is a trick “pattern” you must memorize because it will occur many times when you work on databases!

Question 4

Many cities in the U.S. have the same name. Which city in the U.S. occurs the most frequently?

Answer 4

The easiest approach is to just read it out of the table. The SQL that prints the names of the cities and the number of times they occur:

SELECT city, COUNT ( city ) 
FROM world_cities 
WHERE country = "United States" 
GROUP BY city
ORDER BY city ASC;

When we ORDER BY city ASC this means the very last entries in the table will occur the most often.

Question 5

Print out a table of the countries and how many cities each country has?

Answer 5

SELECT country, COUNT ( country ) FROM world_cities GROUP BY country ORDER BY country ASC;

Question 6

Which countries have the most cities?

Answer 6

You can do this most easily by just printing out a sorted table of (countries, no. cities), like this:

SELECT country, COUNT ( country )
FROM world_cities
GROUP BY country
ORDER BY COUNT ( country ) ASC;

Question 7

Print out a list of all the states in the United States

Answer 7

Note: This is wrong:

SELECT subcountry FROM world_cities WHERE country = “United States”

because it prints out duplicates. Try this instead:

SELECT DISTINCT subcountry FROM world_cities WHERE country = “United States”

Question 8

INTERESTING FACT

Answer 8

Did you know that smoke detectors in your house are actually radioactive? Nobody likes the publicize this fact because it scares people, but in fact they contain a radioactive Ameicium-241 source that emits alpha particles.

What’s worse is that even today the soft cloth “mantles” that burn inside of lanterns are radioactive - and if you handle them incorrectly, they can be a health hazard.

So be careful!

Question 9

The US was settled by many immigrants from the UK who wanted the same city name as they had in the UK. So, how many city names are in both the United States and the United Kingdom.

This is quite tricky. Recommend first displaying the list of cities using INTERSECT, the embedding this query into a simple query using COUNT.

Answer 9

To display the list of cities, you can use an INTERSECT, like this:

SELECT city FROM world_cities 
WHERE country = "United States" 
INTERSECT 
SELECT city FROM world_cities 
WHERE country = "United Kingdom";

Because the result of this query is simply a table, you can embed this query in a very simple SELECT COUNT query, like this:

SELECT COUNT ( city ) FROM
(
SELECT city FROM world_cities 
WHERE country = "United States" 
INTERSECT 
SELECT city FROM world_cities 
WHERE country = "United Kingdom"
);

Be sure to delete the unneeded “;”

Question 10

Germans love their baths. Nobody really knows if they are clean, but they love baths. How many cities in Germany start with “Bad”?

Answer 10

SELECT COUNT ( city ) FROM world_cities
WHERE Country = “Germany”
AND
city like “bad%”;

Question 11

We saw that Germans love their baths and have many cities named “Bad . . . “ - but there are also other cities in the world like this. Print out a list of all the world cities starting with “Bad . . . “ EXCEPT the German cities; exclude those from the list.

Answer 11

SELECT city, country FROM world_cities
WHERE
city LIKE “bad%” AND
country <> “Germany”;

Question 12

Print out a table with all the Swiss cities and how many letters are in their name.

Hint: use the function LENGTH()

Answer 12

SELECT city, LENGTH ( city )
FROM world_cities
WHERE country = “Switzerland”
GROUP BY city;

Question 13

Print out a table with all the Swiss cities and how many letters are in the city name, but this time order the table in ascending order of length, so that the cities with the shortest names are on the top, and the cities with the longest names are on the bottom.

Hint: use the function LENGTH ()

Answer 13

SELECT city, LENGTH ( city )
FROM world_cities
WHERE country = "Switzerland"
GROUP BY city
ORDER BY LENGTH ( city ) ;

Question 14

How many characters are in city with the longest name in the world?

Answer 14

Be careful: it is easy if you do not do more than you are asked for. Because it is only asking for the number of characters, this is easy to find using:

SELECT LENGTH ( city ) FROM world_cities
ORDER BY LENGTH ( city );

so that, down at the very bottom, you’ll see the number of characters, which is the requested answer.

Question 15

What world city has the longest name?

Answer 15

If we just wanted a table containing the lengths, this would be easy:

SELECT LENGTH ( city ) FROM world_cities
ORDER BY LENGTH ( city );

But because we want both the value and the LENGTH of the value, we need to use the GROUP BY paradigm, like this:

SELECT city, LENGTH ( city ), country FROM world_cities
GROUP BY city
ORDER BY LENGTH ( city );

Question 16

What country has the longest name?

Answer 16

SELECT country, LENGTH ( country )
FROM world_cities
GROUP BY country
ORDER BY LENGTH ( country ) ASC;

Question 17

INTERESTING FACT

Answer 17

South India has a delicious sauce called sambar, that is made of savory vegetables, chilis, tamarind, and spices. It is so famous in Southern India it is served with every dish. But the most amazing part: people say that every village and city in South India has their own unique flavor, so if you just taste the sambar, you immediately know in what city or village you are!

Question 18

TRICKY but you need to learn the trick!

Write a query that returns a table containing the country with the largest number of characters in its name.

Hint: use the LENGTH ( ) function

Answer 18

It is easiest to do problems like this in two steps. First., we can use our “GROUP BY” paradigm to print out a table with (country, length of country name). Then, we can use this query as a sub-query in a rather simple query involving MAX.

First, the subquery, which uses our GROUP BY paradigm to put the function next to the argument:

SELECT country, LENGTH ( country )
FROM world_cities
GROUP BY LENGTH ( country );

Since this is just a table like any other table, we can use it like this:

SELECT country, MAX ( LENGTH ( country ) ) FROM
(
SELECT country, LENGTH ( country )
FROM world_cities
GROUP BY LENGTH ( country )
);

Special note: because we are asked to just output the MAX, we do not need the GROUP BY paradigm in the outer query.

Question 19

Which countries have the shortest names?

Hint: Use LENGTH

Answer 19

Because we want rows that contain a value and then some function of the value, we need to use our GROUP BY paradigm. First, we can produce the list of all countries and their lengths:

SELECT country, LENGTH ( country )
FROM world_cities
GROUP BY country;

But it is hard to see the shortest because the lengths are all mixed up. So we can add ORDER BY to the query above:

SELECT country, LENGTH ( country )
FROM world_cities
GROUP BY country
ORDER BY LENGTH ( country );

But it is even easier if we order it DESCENDING, like this:

SELECT country, LENGTH ( country )
FROM world_cities
GROUP BY country
ORDER BY LENGTH ( country ) DESC;

Question 20

Let’s say we want to know how many countries have 4-letter names, 5-letter name, 6-letter names, etc.

Print out a table that looks like
[ No. Characters // How many countries ]

HINT: This is actually quite easy if you write two queries. First, write a query that prints out
[ country, nr. letters ]

Then, you can use this table as a subquery in a rather standard query (using the GROUP BY paradigm) that tries to print out
[LENGTH(country), COUNT(LENGTH(country)]

Answer 20

First, let’s print out
[ country, nr. letters ]
which we can do with this query:

SELECT country, LENGTH ( country )
FROM world_cities
GROUP BY country
ORDER BY LENGTH ( country ) DESC;

Now, we can forget all the details and more or less “blindly” use this query as a subquery, like this:

SELECT 
LENGTH ( country ), count(length(country)) 
FROM 
( 
SELECT country, LENGTH ( country ) 
FROM world_cities 
GROUP BY country 
ORDER BY LENGTH ( country ) DESC 
) 
GROUP BY LENGTH ( country );

or, to make it even prettier,

SELECT 
LENGTH ( country ) AS "Name length", count(length(country)) AS "Frequency"
FROM 
( 
SELECT country, LENGTH ( country ) 
FROM world_cities 
GROUP BY country 
ORDER BY LENGTH ( country ) DESC 
) 
GROUP BY LENGTH ( country );

Question 21

Show the first five rows of the table only

Answer 21

SELECT * FROM world_cities LIMIT 5;

Question 22

Print out all the countries that begin with the letter C.

Answer 22

SELECT DISTINCT country FROM world_cities WHERE country LIKE “c%”;

Question 23

Tricky! Print out all the countries that begin with the letter “C” but do not use the word DISTINCT in your query.

This is a good test if you can think of other approaches!

Answer 23

Probably the easiest way is to use the keyword DISTINCT, like in this query:

SELECT DISTINCT country FROM world_cities WHERE country LIKE “c%”;

But if we cannot use the word DISTINCT, then we can use GROUP BY like this:

SELECT country FROM world_cities WHERE country LIKE “c%” GROUP BY country;

Question 24

Print out a table showing how many cities are in each country.

Answer 24

SELECT country, COUNT ( city )
FROM world_cities
GROUP BY country;

Question 25

Print out a list of how many cities are in each country, but only for countries with more than 1000 cities.

Answer 25

This is not so tricky. At first you might think by putting in WHERE COUNT(city) > 1000 will solve your problems - but you would only be half-right. You need to do exactly this, but add it via a HAVING, as shown here:

SELECT country, COUNT ( city ) 
FROM world_cities
GROUP BY country
HAVING COUNT( city ) > 1000;

Question 26

Print out a list of all cities (and their countries) where the city begins with the letter Y or Z

Answer 26

There are a few ways to do this. One is by using OR, such as this shows:

SELECT city, country
FROM world_cities
WHERE
city LIKE “y%” OR city LIKE “z%”;

Another way is via UNION, like this:

SELECT city, country
FROM world_cities
WHERE
city LIKE "y%"
UNION
SELECT city, country
FROM world_cities
WHERE
city LIKE "z%";

Question 27

Print out a list of how many cities are in each country, but only the cases where the country has more than 500 cities, and also print the list in ascending order of city size.

Answer 27

SELECT country, COUNT ( city ) 
FROM world_cities
GROUP BY country
HAVING COUNT( city ) > 500
ORDER BY COUNT ( city ) ASC;