1
Q

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!

A

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

2
Q

Do Ukraine and Russia have any cities in common?

A
```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

3
Q

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)

A
```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!

4
Q

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

A

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.

5
Q

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

A

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

6
Q

Which countries have the most cities?

A

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;

7
Q

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

A

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”

8
Q

INTERESTING FACT

A

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!

9
Q

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.

A

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 “;”

10
Q

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

A

SELECT COUNT ( city ) FROM world_cities
WHERE Country = “Germany”
AND

11
Q

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.

A

SELECT city, country FROM world_cities
WHERE
country <> “Germany”;

12
Q

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

Hint: use the function LENGTH()

A

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

13
Q

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 ()

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

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

A

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.

15
Q

What world city has the longest name?

A

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 );

16
Q

What country has the longest name?

A

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

17
Q

INTERESTING FACT

A

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!

18
Q

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

A

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.

19
Q

Which countries have the shortest names?

Hint: Use LENGTH

A

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;

20
Q

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)]

A

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 );```
21
Q

Show the first five rows of the table only

A

SELECT * FROM world_cities LIMIT 5;

22
Q

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

A

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

23
Q

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!

A

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;

24
Q

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

A

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

25
Q

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

A

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;```
26
Q

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

A

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%";```
27
Q

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.

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