Easy Database Challenges

Pizzeria Galactica - All Distinct Cities
Difficulty: Easy	Published: 2020-04-07
Summary Query a list of unique city names from physical_address table
Description Given a physical_address table, write a query to get a list of unique city names, along with their state_province and country. Sort the query results alphabetically by the city name, state/province, and country. The physical_address table has the following schema: +---------------------+---------+ | COLUMN              | TYPE    | +---------------------+---------+ | physical_address_id | BIGINT  | +---------------------+---------+ | address_type        | VARCHAR | +---------------------+---------+ | is_primary          | BOOLEAN | +---------------------+---------+ | line_1              | VARCHAR | +---------------------+---------+ | line_2              | VARCHAR | +---------------------+---------+ | city                | VARCHAR | +---------------------+---------+ | state_province      | VARCHAR | +---------------------+---------+ | country             | VARCHAR | +---------------------+---------+ | postal_code         | VARCHAR | +---------------------+---------+

Pizzeria Galactica - Count Customers Missing Titles and Suffixes
Difficulty: Easy	Published: 2020-04-07
Summary Query a count of customers that have no titles and no suffixes in their names
Description Given a customer table, write a query to get a count of customers that have no titles and no suffixes in their names. The customer table has the following schema: +---------------------+---------+ | COLUMN              | TYPE    | +---------------------+---------+ | customer_id         | BIGINT  | +---------------------+---------+ | title               | VARCHAR | +---------------------+---------+ | first_name          | VARCHAR | +---------------------+---------+ | last_name           | VARCHAR | +---------------------+---------+ | suffix              | VARCHAR | +---------------------+---------+ | password            | BYTEA   | +---------------------+---------+

Pizzeria Galactica - Count Titles
Difficulty: Easy	Published: 2020-04-07
Summary Query a count of customers that have titles in their names
Description Given a customer table, write a query to get the number of customers that have a given title in their name. Ignore names with no title given. Sort the results alphabetically by title The customer table has the following schema: +---------------------+---------+ | COLUMN              | TYPE    | +---------------------+---------+ | customer_id         | BIGINT  | +---------------------+---------+ | title               | VARCHAR | +---------------------+---------+ | first_name          | VARCHAR | +---------------------+---------+ | last_name           | VARCHAR | +---------------------+---------+ | suffix              | VARCHAR | +---------------------+---------+ | password            | BYTEA   | +---------------------+---------+

Pizzeria Galactica - Countries with Stores
Difficulty: Easy	Published: 2019-12-17
Summary Query all countries where stores are located
Description Write an SQL statement to find all the countries where Pizzeria Galactica stores are located. The schema of the database is shown in the attachments tab. Sort the query results alphabetically and exclude any duplicates.
Attachments

Pizzeria Galactica - Largest Area Code in the Country
Difficulty: Easy	Published: 2020-04-07
Summary Query the largest area code for each country
Description Given a phone_number table, write a query to find the greatest value of phone_area_code in each country. Sort the result in ascending order by phone_country_code. The phone_number table has the following schema: +---------------------------+----------+ | COLUMN                    | TYPE     | +---------------------------+----------+ | phone_number_id           | BIGINT   | +---------------------------+----------+ | customer_id               | BIGINT   | +---------------------------+----------+ | phone_type                | VARCHAR  | +---------------------------+----------+ | is_primary                | BOOLEAN  | +---------------------------+----------+ | iso_country_code          | VARCHAR  | +---------------------------+----------+ | phone_country_code        | SMALLINT | +---------------------------+----------+ | phone_area_code           | SMALLINT | +---------------------------+----------+ | phone_number              | INTEGER  | +---------------------------+----------+

Pizzeria Galactica - Postal Codes per Country
Difficulty: Easy	Published: 2020-04-07
Summary Query a count of postal codes in each country with exclusions
Description Given a physical_address table, write a query to get the number of unique postal codes for each country except Sweden and Brazil. Sort the results by country in ascending order. The physical_address table has the following schema: +---------------------+---------+ | COLUMN              | TYPE    | +---------------------+---------+ | physical_address_id | BIGINT  | +---------------------+---------+ | address_type        | VARCHAR | +---------------------+---------+ | is_primary          | BOOLEAN | +---------------------+---------+ | line_1              | VARCHAR | +---------------------+---------+ | line_2              | VARCHAR | +---------------------+---------+ | city                | VARCHAR | +---------------------+---------+ | state_province      | VARCHAR | +---------------------+---------+ | country             | VARCHAR | +---------------------+---------+ | postal_code         | VARCHAR | +---------------------+---------+ NOTE: The maximum length of postal_code for all specified countries is 10 digits.

Pizzeria Galactica - Primary Store Addresses
Difficulty: Easy	Published: 2020-04-07
Summary Query a list of Primary addresses for each Store
Description Write an SQL statement to query the store_number and all columns of the physical_address table for each primary store address. The schema of the database is shown in the attachments tab. Sort the query result by country alphabetically and then by store_number in descending order.
Attachments

Pizzeria Galactica - Query Customer Full Names
Difficulty: Easy	Published: 2020-04-07
Summary Query the customer names that consist of three or more words
Description Given a customer table, write a query to get the full names of customers that contain three or more words. Words are separated with at least one space or hyphen (-) character. A given customer's full name is a space-delimited concatenation of the first_name and last_name fields. Sort the query results alphabetically. The customer table has the following schema: +---------------------+---------+ | COLUMN              | TYPE    | +---------------------+---------+ | customer_id         | BIGINT  | +---------------------+---------+ | title               | VARCHAR | +---------------------+---------+ | first_name          | VARCHAR | +---------------------+---------+ | last_name           | VARCHAR | +---------------------+---------+ | suffix              | VARCHAR | +---------------------+---------+ | password            | BYTEA   | +---------------------+---------+

Pizzeria Galactica - Query Customer Names
Difficulty: Easy	Published: 2020-04-07
Summary Query the names of customers that start with a consonant
Description Given a customer table, write a query to get the first names of customers that start with a consonant. Sort the query results alphabetically. HINT: A, E, I, O, U are vowels, all other letters are consonants. The customer table has the following schema: +---------------------+---------+ | COLUMN              | TYPE    | +---------------------+---------+ | customer_id         | BIGINT  | +---------------------+---------+ | title               | VARCHAR | +---------------------+---------+ | first_name          | VARCHAR | +---------------------+---------+ | last_name           | VARCHAR | +---------------------+---------+ | suffix              | VARCHAR | +---------------------+---------+ | password            | BYTEA   | +---------------------+---------+

Pizzeria Galactica - Query Customer Names II
Difficulty: Easy	Published: 2020-04-07
Summary Query the customers' names that start with a vowel and end with a consonant
Description Given a customer table, write a query to get the first names of customers that start with a vowel and end with a consonant. Sort the query result alphabetically. HINT: A, E, I, O, U are vowels, all other letters are consonants. The customer table has the following schema: +---------------------+---------+ | COLUMN              | TYPE    | +---------------------+---------+ | customer_id         | BIGINT  | +---------------------+---------+ | title               | VARCHAR | +---------------------+---------+ | first_name          | VARCHAR | +---------------------+---------+ | last_name           | VARCHAR | +---------------------+---------+ | suffix              | VARCHAR | +---------------------+---------+ | password            | BYTEA   | +---------------------+---------+

Pizzeria Galactica - Select Addresses by ID Range
Difficulty: Easy	Published: 2020-04-07
Summary Query all columns of addresses within a given ID range
Description Given a physical_address table, write a query to get all columns of addresses with the physical_address_id greater than 20 and less than 30. Sort the query result by country name alphabetically. Sort addresses of the same country in descending order by physical_address_id. The physical_address table has the following schema: +---------------------+---------+ | COLUMN              | TYPE    | +---------------------+---------+ | physical_address_id | BIGINT  | +---------------------+---------+ | address_type        | VARCHAR | +---------------------+---------+ | is_primary          | BOOLEAN | +---------------------+---------+ | line_1              | VARCHAR | +---------------------+---------+ | line_2              | VARCHAR | +---------------------+---------+ | city                | VARCHAR | +---------------------+---------+ | state_province      | VARCHAR | +---------------------+---------+ | country             | VARCHAR | +---------------------+---------+ | postal_code         | VARCHAR | +---------------------+---------+

Pizzeria Galactica - Select All USA Addresses
Difficulty: Easy	Published: 2020-04-07
Summary Query all columns of USA addresses
Description Given the physical_address table shown below, write a query to retrieve all columns of addresses that are in the USA. The country column of addresses must contain the value USA. Sort the query result in ascending order based on the physical_address_id. The physical_address table has the following schema: +---------------------+---------+ | COLUMN              | TYPE    | +---------------------+---------+ | physical_address_id | BIGINT  | +---------------------+---------+ | address_type        | VARCHAR | +---------------------+---------+ | is_primary          | BOOLEAN | +---------------------+---------+ | line_1              | VARCHAR | +---------------------+---------+ | line_2              | VARCHAR | +---------------------+---------+ | city                | VARCHAR | +---------------------+---------+ | state_province      | VARCHAR | +---------------------+---------+ | country             | VARCHAR | +---------------------+---------+ | postal_code         | VARCHAR | +---------------------+---------+

Pizzeria Galactica - Unique Area Codes
Difficulty: Easy	Published: 2020-04-07
Summary Query a list of unique area codes for each country
Description Given a phone_number table, write a query to get a list of unique area codes for each country. Sort the result by country code in ascending order. For each country, sort area codes in descending order. The phone_number table has the following schema: +---------------------------+----------+ | COLUMN                    | TYPE     | +---------------------------+----------+ | phone_number_id           | BIGINT   | +---------------------------+----------+ | customer_id               | BIGINT   | +---------------------------+----------+ | phone_type                | VARCHAR  | +---------------------------+----------+ | is_primary                | BOOLEAN  | +---------------------------+----------+ | iso_country_code          | VARCHAR  | +---------------------------+----------+ | phone_country_code        | SMALLINT | +---------------------------+----------+ | phone_area_code           | SMALLINT | +---------------------------+----------+ | phone_number              | INTEGER  | +---------------------------+----------+