Medium Database Challenges

Pizzeria Galactica - Cities and States With Odd ID
Difficulty: Medium	Published: 2020-04-07
Summary Query city and state names for rows having odd ID
Description Given a physical_address table, write a query to get the city and state names for rows having an odd physical_address_id Substitute null values with three dashes --- in the output. 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 - Customer Names, Countries and Phones
Difficulty: Medium	Published: 2020-04-07
Summary Query the list of customers' names, their countries, and phone numbers
Description Write an SQL query to get the list of customers' full names with their countries and phone numbers. The schema of the database is shown in the attachments tab. The customer full name is a space-delimited concatenation of the title (if it exists), first_name, and last_name fields. If the suffix field is present, it should be appended enclosed in parenthesis. In other words, output the full name in the following format: <title> <first_name> <last_name> (suffix) Output phone numbers in the following format: <phone_country_code>-<phone_area_code>-<phone_number> Be sure to include all customers, even if they are missing some information Sort the results alphabetically by country and then by phone number in ascending order within each country.
Attachments

Pizzeria Galactica - Customers Missing Address
Difficulty: Medium	Published: 2020-04-07
Summary Query the list of customers that don't have a physical address
Description Imagine that your manager asks you to send marketing emails to all customers of Pizzeria Galactica who have not included their physical address during registration. Write an SQL query to get the list of such customers. The list should contain the following fields for each customer: customer_id title first_name last_name email_address The schema of the database is shown in the attachments tab. Sort the result by the customer_id in ascending order.
Attachments

Pizzeria Galactica - Most Popular Pizza in the City
Difficulty: Medium	Published: 2020-11-19
Summary Query 3 most popular pizzas in each city
Description Write an SQL query to get the 3 most popular pizzas for each city. The schema of the database is shown in the attachments tab. The goal is to rank the pizza by the count of orders in descending order for each city where orders were made. Pizzas with the same number of orders should be sorted alphabetically in ascending order. For each city, the top 3 pizzas must be selected and concatenated into a final list. In the final list the pizza names must be delimited by comma and space characters. Items in the list must be sorted alphabetically. NOTE: Some cities may have less than 3 pizza names in the top list.
Attachments

Pizzeria Galactica - Query a Phone Number Page
Difficulty: Medium	Published: 2020-04-07
Summary Query a specified subset of phone numbers from a larger block of results
Description Given a phone_number table, write a query to sort its rows by the phone_number field in ascending order. Include their types, country codes, and area codes in the results. Here's the twist: just return the second page of results, where each page has 5 rows. The query result should contain two columns, type and phone number, where phone number has the following format: <phone_country_code>-<phone_area_code>-<phone_number> 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 - Range of Address Lengths
Difficulty: Medium	Published: 2020-04-07
Summary Query the shortest and the longest length of the address
Description Given a physical_address table, write a query to get the shortest and longest length of the complete address contained in the table. The complete address is a concatenation of the line_1, line_2, city, state_province, and country fields. All parts of the complete address should be separated by exactly one space character. The space should still be kept when any part of the address is NULL. The address length is equal to the total number of characters in the concatenated string. 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 - Shift Work
Difficulty: Medium	Published: 2020-11-19
Summary Query pairs of employees reporting the same manager
Description According to Pizzeria Galactica company policy, employees must be scheduled in pairs for each shift. To help the managers create schedules properly, each manager needs to know the pairs of employees reporting to them. Write an SQL query to get all possible pairs of employees reporting to the same manager. Make a selection of employees regardless of their order. In other words, Bob, Alice and Alice, Bob are really the same pair and should only be included in the result once. The result should have following columns: manager id manager name employee 1 id employee 1 name employee 2 id employee 2 name Where: employee 1 id < employee 2 id each name is a space-delimited concatenation of the first_name and the last_name fields Sort the query results by manager id in ascending order. NOTE: Some employees are no longer working in Pizzeria Galactica and must be excluded from the result. These employees will have an end_date set in the database. The employee table has the following schema: +-------------+---------+ | COLUMN      | TYPE    | +-------------+---------+ | employee_id | BIGINT  | +-------------+---------+ | store_id    | BIGINT  | +-------------+---------+ | manager_id  | BIGINT  | +-------------+---------+ | title       | VARCHAR | +-------------+---------+ | first_name  | VARCHAR | +-------------+---------+ | last_name   | VARCHAR | +-------------+---------+ | suffix      | VARCHAR | +-------------+---------+ | password    | BYTEA   | +-------------+---------+ | start_date  | DATE    | +-------------+---------+ | end_date    | DATE    | +-------------+---------+

Pizzeria Galactica - Sum Postal Codes
Difficulty: Medium	Published: 2020-04-07
Summary Query a sum using type-converted values
Description Given a physical_address table, write a query to calculate the numeric sum of the postal codes including all countries except Sweden and Brazil. The postal_code for all specified countries consists of decimal digits only. The maximum length of the postal code is 10 digits. 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 | +---------------------+---------+