Pizzeria Galactica - All Distinct Cities | |
| Difficulty: Easy | Published: 2020-04-07 |
SummaryQuery a list of unique city names from physical_address table | |
DescriptionGiven 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 |
SummaryQuery a count of customers that have no titles and no suffixes in their names | |
DescriptionGiven 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 |
SummaryQuery a count of customers that have titles in their names | |
DescriptionGiven 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 |
SummaryQuery all countries where stores are located | |
DescriptionWrite 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 |
SummaryQuery the largest area code for each country | |
DescriptionGiven 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 |
SummaryQuery a count of postal codes in each country with exclusions | |
DescriptionGiven 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 |
SummaryQuery a list of Primary addresses for each Store | |
DescriptionWrite 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 |
SummaryQuery the customer names that consist of three or more words | |
DescriptionGiven 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 |
SummaryQuery the names of customers that start with a consonant | |
DescriptionGiven 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 |
SummaryQuery the customers' names that start with a vowel and end with a consonant | |
DescriptionGiven 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 |
SummaryQuery all columns of addresses within a given ID range | |
DescriptionGiven 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 |
SummaryQuery all columns of USA addresses | |
DescriptionGiven 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 |
SummaryQuery a list of unique area codes for each country | |
DescriptionGiven 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 | +---------------------------+----------+
| |