BASIC SELECT (world.db) Flashcards
The basics of using SELECT to pull data out of a database and filter it
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!
Please use these flash cards in good health and good happiness!
What are the columns in the table?
If you are using SQLite, the command that let’s you see the column names is
.schema
How many rows are there in this table?
SELECT COUNT(*) FROM world_cities;
Print a list of the cities are in countries with the same name as the city e.g. name=Singapore, country=Singapore?
SELECT city FROM world_cities WHERE city = country;
Print the list of cities that start with the letter “C”
SELECT city FROM world_cities WHERE city LIKE “C%”
How many cities start with the letter “C”?
SELECT COUNT(city) FROM world_cities WHERE city LIKE “C%”
Print a list of unique country names e.g. each country name only appears once.
SELECT DISTINCT country FROM world_cities;
TRIVIA
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.
How many unique country names are there?
SELECT COUNT ( DISTINCT country ) FROM world_cities;
Print a list of unique country names (e.g. each country only appears once) but printed in reverse alphabetical order
SELECT DISTINCT country FROM world_cities ORDER BY country DESC;
Print a unique list of all countries that have a space “ “ in their name
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!
How many countries have whitespace “ “ in their name?
SELECT COUNT( DISTINCT country) FROM world_cities WHERE country LIKE “% %”;
How many cities are there in the United States?
SELECT COUNT ( city ) FROM world_cities WHERE country = “United States”;
How many different city names are there in the United States?
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”;
How many cities in the U.S. have a whitespace “ “ in their name?
SELECT city FROM world_cities WHERE country = “United States” AND city LIKE “% %”;
How many cities are named after Capt. Kirk’s starship?
SELECT city FROM world_cities WHERE city = "Enterprise"; -or- SELECT COUNT( city ) FROM world_cities WHERE city = "Enterprise";
INTERESTING FACT
Did you know that William Shakespeare invented the word “vomit”?
How many cities are in Switzerland?
SELECT COUNT ( city ) FROM world_cities WHERE country = “Switzerland”;
Which cities are in the Swiss canton of Bern?
SELECT * FROM world_cities WHERE subcountry = “Bern”;
How many states are there in Germany?
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”;
How many times does the word “Zürich” appear in the list of Swiss cities?
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%”;
Homer Simpson and his family live in Springfield. How many Springfields are there in the U.S.?
SELECT city FROM world_cities WHERE city = “Springfield”;
-or-
SELECT COUNT( city ) FROM world_cities WHERE city = “Springfield”;
INTERESTING FACT
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.
How many cities have 6 spaces in their name e.g. 6 occurances of “ “?
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.)
How many cities have 5 whitespaces in their name? e.g. 5 separate occurances of “ “?
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.)
How many cities are there in the list with the same name for the subcountry?
SELECT COUNT ( * ) FROM world_cities WHERE city = subcountry;
In what country will you find the city “Lake Zurich”?
SELECT city, country FROM world_cities WHERE city = “Lake Zurich”;
In the movie “The Omen,” the number 666 was considered evil. How many locations have 666 that appear in their location id?
To see them,
SELECT * FROM world_cities WHERE geonameid LIKE “%666%;
To count them,
SELECT COUNT ( * ) FROM world_cities WHERE geonameid LIKE “%666%;
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?
To see them,
SELECT * FROM world_cities WHERE geonameid = “46%46”;
To count them,
SELECT COUNT ( * ) FROM world_cities WHERE geonameid = “46%46”;
