BASIC SELECT (world.db)

Question 1

SPECIAL ANNOUNCEMENT

Don’t feel like you are being tricked - you are NOT. These flash cards contain SQL queries that are very legitimate, so that any person learning SQL would be expected to answer these. Some people can do this faster, some people need a cup of coffee and more time - but please do not feel like you are being tricked. Practice, practice, practice!

Answer 1

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

Question 2

What are the columns in the table?

Answer 2

If you are using SQLite, the command that let’s you see the column names is

.schema

Question 3

How many rows are there in this table?

Answer 3

SELECT COUNT(*) FROM world_cities;

Question 4

Print a list of the cities are in countries with the same name as the city e.g. name=Singapore, country=Singapore?

Answer 4

SELECT city FROM world_cities WHERE city = country;

Question 5

Print the list of cities that start with the letter “C”

Answer 5

SELECT city FROM world_cities WHERE city LIKE “C%”

Question 6

How many cities start with the letter “C”?

Answer 6

SELECT COUNT(city) FROM world_cities WHERE city LIKE “C%”

Question 7

Print a list of unique country names e.g. each country name only appears once.

Answer 7

SELECT DISTINCT country FROM world_cities;

Question 8

TRIVIA

Answer 8

Not a lot of people know this, but neutron stars are stars where the gravity is so high, all the atoms have been squeezed down to pure neutrons. One teaspoon of neutron star stuff weighs as much as 900 Giza pyramids in Egypt.

Question 9

How many unique country names are there?

Answer 9

SELECT COUNT ( DISTINCT country ) FROM world_cities;

Question 10

Print a list of unique country names (e.g. each country only appears once) but printed in reverse alphabetical order

Answer 10

SELECT DISTINCT country FROM world_cities ORDER BY country DESC;

Question 11

Print a unique list of all countries that have a space “ “ in their name

Answer 11

SELECT DISTINCT country FROM world_cities WHERE country LIKE “% %”; It is good to keep in mind that a space is a character just like any other characters. The wildcard %k% will return all values with a “k” in them; in the same way, % % will return all values with a space in them!

Question 12

How many countries have whitespace “ “ in their name?

Answer 12

SELECT COUNT( DISTINCT country) FROM world_cities WHERE country LIKE “% %”;

Question 13

How many cities are there in the United States?

Answer 13

SELECT COUNT ( city ) FROM world_cities WHERE country = “United States”;

Question 14

How many different city names are there in the United States?

Answer 14

Special note: SELECT COUNT( city ) FROM . . . will not work, because a city like Springfield occurs in many different states. Therefore you need

SELECT COUNT( DISTINCT city ) FROM world_cities WHERE country = “United States”;

Question 15

How many cities in the U.S. have a whitespace “ “ in their name?

Answer 15

SELECT city FROM world_cities WHERE country = “United States” AND city LIKE “% %”;

Question 16

How many cities are named after Capt. Kirk’s starship?

Answer 16

SELECT city FROM world_cities WHERE city = "Enterprise"; 
-or-
SELECT COUNT( city ) FROM world_cities WHERE city = "Enterprise";

Question 17

INTERESTING FACT

Answer 17

Did you know that William Shakespeare invented the word “vomit”?

Question 18

How many cities are in Switzerland?

Answer 18

SELECT COUNT ( city ) FROM world_cities WHERE country = “Switzerland”;

Question 19

Which cities are in the Swiss canton of Bern?

Answer 19

SELECT * FROM world_cities WHERE subcountry = “Bern”;

Question 20

How many states are there in Germany?

Answer 20

You can see the states with

SELECT subcountry FROM world_cities WHERE country = “Germany”;

but they are not unique. In order to see the list of unique states,

SELECT DISTINCT subcountry FROM world_cities WHERE country = “Germany”;

And if you want just the count, then

SELECT COUNT( DISTINCT subcountry ) FROM world_cities WHERE country = “Germany”;

Question 21

How many times does the word “Zürich” appear in the list of Swiss cities?

Answer 21

You can filter for this using

SELECT city FROM world_cities WHERE country = “Switzerland” AND city LIKE “%Zür%”;

and if you want the count, then

SELECT COUNT ( city ) FROM world_cities WHERE country = “Switzerland” AND city LIKE “%Zür%”;

Question 22

Homer Simpson and his family live in Springfield. How many Springfields are there in the U.S.?

Answer 22

SELECT city FROM world_cities WHERE city = “Springfield”;

-or-

SELECT COUNT( city ) FROM world_cities WHERE city = “Springfield”;

Question 23

INTERESTING FACT

Answer 23

Temperatures in Fahrenheit are more human-friendly than temperatures in Celsius. Celsius tells you when a glass of water will freeze - that’s probably important if you are a glass of water.

But you are a human! Fahrenheit makes more sense. Above 100 F you die without support. Below 0 the insides of your nose freeze.

Question 24

How many cities have 6 spaces in their name e.g. 6 occurances of “ “?

Answer 24

SELECT * FROM world_cities WHERE city LIKE “% % % % % % %”;

-or-

SELECT COUNT ( * ) FROM world_cities WHERE city LIKE “% % % % % % %”;

(Look closely: between the % you will see 6 whitespaces.)

Question 25

How many cities have 5 whitespaces in their name? e.g. 5 separate occurances of “ “?

Answer 25

SELECT * FROM world_cities WHERE city LIKE “% % % % % %”;

-or-

SELECT COUNT ( * ) FROM world_cities WHERE city LIKE “% % % % % %”;

(Look closely: between the % you will see 5 whitespaces.)

Question 26

How many cities are there in the list with the same name for the subcountry?

Answer 26

SELECT COUNT ( * ) FROM world_cities WHERE city = subcountry;

Question 27

In what country will you find the city “Lake Zurich”?

Answer 27

SELECT city, country FROM world_cities WHERE city = “Lake Zurich”;

Question 28

In the movie “The Omen,” the number 666 was considered evil. How many locations have 666 that appear in their location id?

Answer 28

To see them,

SELECT * FROM world_cities WHERE geonameid LIKE “%666%;

To count them,

SELECT COUNT ( * ) FROM world_cities WHERE geonameid LIKE “%666%;

Question 29

William Shakespeare is thought to have authored the King James version of the Bible. The supposed “proof” of this is that he finished the book on his 46th birthday. In Psalm 46, the 46th word from the beginning is “Shake” and the 46th world from the end is “Spear.” How many locations have the property that they start with 46 and end with 46?

Answer 29

To see them,

SELECT * FROM world_cities WHERE geonameid = “46%46”;

To count them,

SELECT COUNT ( * ) FROM world_cities WHERE geonameid = “46%46”;