Simple SQL Exercises

Let’s jump to the first task!

Query all columns for all American cities in the CITY table with populations larger than 100000. The CountryCode for America is USA.

The CITY table is described as follows:

To query all columns for all American cities in the CITY Table with populations larger than 100,000, given the table structure and the specified data types, you can use the following SQL query:

SELECT * 
FROM CITY 
WHERE COUNTRYCODE = 'USA' 
  AND POPULATION > 100000;

Expected Output:

3878 Scottsdale USA Arizona 202705 
3965 Corona USA California 124966 
3973 Concord USA California 121780 
3977 Cedar Rapids USA Iowa 120758 
3982 Coral Springs USA Florida 117549 

Here’s a detailed explanation of the query components:

• SELECT *: This clause selects all columns from the table.
• FROM CITY: This specifies the table to query from, which is CITY.
• WHERE COUNTRYCODE = 'USA': This condition filters the rows to include only those where the COUNTRYCODE is ‘USA’.
• AND POPULATION > 100000: This additional condition filters the rows to include only those where the POPULATION is greater than 100,000.

Given the table structure:

• ID (NUMBER): The unique identifier for each city.
• NAME (VARCHAR2(17)): The name of the city.
• COUNTRYCODE (VARCHAR2(3)): The country code, where ‘USA’ represents American cities.
• DISTRICT (VARCHAR2(20)): The district where the city is located.
• POPULATION (NUMBER): The population of the city.

This query will return all the relevant columns (ID, NAME, COUNTRYCODE, DISTRICT, and POPULATION) for American cities with a population exceeding 100,000.

A similar task with a slight difference

To query the NAME field for all American cities in the CITY Table with populations larger than 120,000, you can use the following SQL query:

SELECT NAME
FROM CITY
WHERE COUNTRYCODE = 'USA'
  AND POPULATION > 120000;

Here’s a breakdown of the query:

• SELECT NAME: Select only the NAME column from the table.
• FROM CITY: Specifies the CITY table to query from.
• WHERE COUNTRYCODE = 'USA': Filters the rows to include only those with ‘USA’.
• AND POPULATION > 120000: Further filters the rows to include only those greater than 120,000.

This query will return the names of all American cities with populations larger than 120,000.

Query all columns (attributes) for every row

Query all columns (attributes) for every row in the CITY table.

SELECT *
FROM CITY;

This query will return all columns (ID, NAME, COUNTRYCODE, DISTRICT, and POPULATION) for every row in the CITY table. The * wildcard character is used to select all columns in the table.

The expected results should look something like this:

6 Rotterdam NLD Zuid-Holland 593321 
3878 Scottsdale USA Arizona 202705 
3965 Corona USA California 124966 
3973 Concord USA California 121780 
3977 Cedar Rapids USA Iowa 120758 
3982 Coral Springs USA Florida 117549 
4054 Fairfield USA California 92256 
4058 Boulder USA Colorado 91238 
4061 Fall River USA Massachusetts 90555 

Query all columns for a city in CITY with the ID 1661

To query all columns for a city in the CITY table with the ID 1661, you can use the following SQL query:

SELECT * 
FROM CITY 
WHERE ID = 1661;

This query will return all columns (ID, NAME, COUNTRYCODE, DISTRICT, and POPULATION) for the city that has an ID of 1661.

The expected output should be this:

1661 Sayama JPN Saitama 162472

Query all attributes

Query all attributes of every Japanese city in the CITY table. The COUNTRYCODE for Japan is JPN.

To query all attributes (columns) of every Japanese city in the CITY table, where the COUNTRYCODE for Japan is ‘JPN’, you can use the following SQL query:

SELECT * 
FROM CITY 
WHERE COUNTRYCODE = 'JPN';

This query will return all columns (ID, NAME, COUNTRYCODE, DISTRICT, and POPULATION) for all cities where the COUNTRYCODE is ‘JPN’.

Query the names

Query the names of all the Japanese cities in the CITY table. The COUNTRYCODE for Japan is JPN.

To query the names of all the Japanese cities in the CITY table, where the COUNTRYCODE for Japan is ‘JPN’, you can use the following SQL query:

SELECT NAME 
FROM CITY 
WHERE COUNTRYCODE = 'JPN';

This query will return the NAME column for all rows where the COUNTRYCODE is ‘JPN’, which represents all Japanese cities in the CITY table.

Query a list of

Query a list of CITY and STATE from the STATION table.
The STATION table is described as follows:

Where LAT_N is the northern latitude, and LONG_W is the western longitude.

To query a list of CITY and STATE from the STATION table, you can use the following SQL query:

SELECT CITY, STATE
FROM STATION;

This query will return the CITY and STATE columns from the STATION table.

Query a list of unique () with even ID Number

Query a list of CITY names from STATION for cities with an even ID number. Print the results in any order, but exclude duplicates from the answer.
The STATION table is described as follows:

To query a list of unique CITY names from the STATION table for cities that have an even ID Number, you can use the following SQL query:

SELECT DISTINCT CITY
FROM STATION
WHERE MOD(ID, 2) = 0;

Here’s an explanation of the query:

• SELECT DISTINCT CITY: Select unique city names to exclude duplicates.
• FROM STATION: Specifies the STATION table to query from.
• WHERE MOD(ID, 2) = 0: Filters the rows to include only those with an even ID number. The MOD function returns the remainder of dividing ID by 2, and it will be 0 for even numbers.

Find the difference

Find the difference between the total number of CITY entries in the table and the number of distinct CITY entries in the table.
The STATION table is described as follows:

To find the difference between the total number of CITY entries in the table and the number of distinct CITY entries in the table, you can use the following SQL query:

SELECT 
    (SELECT COUNT(CITY) FROM STATION) - 
    (SELECT COUNT(DISTINCT CITY) FROM STATION) AS Difference
FROM DUAL;

Here’s an explanation of the query:

• (SELECT COUNT(CITY) FROM STATION): This subquery returns the total number of CITY entries in the STATION table.
• (SELECT COUNT(DISTINCT CITY) FROM STATION): This subquery returns the number of distinct CITY entries in the STATION table.
• The difference between these two values gives you the difference between the total number of CITY entries and the number of distinct CITY entries.
• AS Difference: Labels the result of the calculation as Difference.

Note: The FROM DUAL clause is used in databases like Oracle. If you’re using a different database system, such as MySQL, you might not need FROM DUAL.

In MySQL, you can also write:

SELECT
(SELECT COUNT(CITY) FROM STATION) -
(SELECT COUNT(DISTINCT CITY) FROM STATION) AS Difference;

This will work without needing the FROM DUAL clause.

Query shortest and longest names

Query the two cities in STATION with the shortest and longest CITY names, as well as their respective lengths (i.e.: number of characters in the name). If there is more than one smallest or largest city, choose the one that comes first when ordered alphabetically.

The STATION table is described as follows:

Sample Input

For example, CITY has four entries: DEF, ABC, PQRS and WXY.

Sample Output

ABC 3
PQRS 4

Explanation

When ordered alphabetically, the CITY names are listed as ABC, DEF, PQRS, and WXY, with lengths and. The longest name is PQRS, but options exist for the shortest-named city. Choose ABC because it comes first alphabetically.

Note
You can write two separate queries to get the desired output. It need not be a single query.

You can write two separate queries to get the cities with the shortest and longest names to achieve the desired result. Here’s how you can do it:

1. Query to get the city with the shortest name:

SELECT CITY, LENGTH(CITY) AS NAME_LENGTH
FROM STATION
ORDER BY LENGTH(CITY) ASC, CITY ASC
LIMIT 1;

2. Query to get the city with the longest name:

SELECT CITY, LENGTH(CITY) AS NAME_LENGTH
FROM STATION
ORDER BY LENGTH(CITY) DESC, CITY ASC
LIMIT 1;

These two queries will provide the shortest and longest city names, respectively, considering the alphabetical order in case of ties.

Here’s how you can execute both queries together:

(SELECT CITY, LENGTH(CITY) AS NAME_LENGTH
 FROM STATION
 ORDER BY LENGTH(CITY) ASC, CITY ASC
 LIMIT 1)
 
UNION ALL

(SELECT CITY, LENGTH(CITY) AS NAME_LENGTH
 FROM STATION
 ORDER BY LENGTH(CITY) DESC, CITY ASC
 LIMIT 1);

This combined query uses UNION ALL to merge the results of the two separate queries. The first part selects the city with the shortest name, and the second selects the town with the longest name. Both results are combined into a single output.

The expected output should look something like this:

Amo 3
Marine On Saint Croix 21

Here’s an explanation of the query:

1. The first SELECT Statement:
   • Selects CITY and its length (LENGTH(CITY) AS NAME_LENGTH).
   • Orders the results by the length of the city name in ascending order (LENGTH(CITY) ASC), and then alphabetically by city name (CITY ASC).
   • Limits the results to the first row (LIMIT 1), which gives the city with the shortest name. If there are ties, the one that comes first alphabetically is selected.
2. The second SELECT statement:
   • Similar to the first SELECT statement but ordered by the length of the city name in descending order (LENGTH(CITY) DESC).
   • Limits the results to the first row, which gives the city with the longest name. If there are ties, the one that comes first alphabetically is selected.
3. UNION ALL:
   • Combines the results of the two queries to return the shortest and longest city names and their lengths.

This query will return the desired cities with their name lengths.