Back
Close
Employers: discover CodinGame for tech hiring
EVENTS
UPCOMINGPAST
SOLO
PUZZLESCODE GOLFOPTIMIZATION
MULTI
LEADERBOARDSCLASH OF CODEBOT PROGRAMMINGCODING ESCAPE
COMMUNITY
CONTRIBUTIONSLEARNDISCORDFORUMLIVE STREAMS

Parse SQL Queries

Statement

 Goal

Puzzle is meant to be a quick intro to SQL. Nothing too wild.

Your inputs are a basic SQL table and a SELECT query command for each test case. Your program needs to parse these and output the correct query results.

For simplicity there are no commands to manipulate tables and only one table to select from. Nothing more advanced than basic SELECT.

Basic structure of query is as follows:
SELECT column_name1, column_name2 FROM table_name1

SELECT tells you which columns to keep from the input table for your output table. FROM specifies the name of the table. For this puzzle it won't matter, therefore simply keeping the relevant columns is enough for this query.

SELECT * will select all columns.

Slightly more advanced query:
SELECT column_name1, column_name2 FROM table_name1 WHERE column_name1 = 
ColumnValue
ORDER BY column_name2 DESC

WHERE tells you what conditions must be met to display a row. Multiple conditions can be concatenated with AND, OR. Since this is an easy puzzle the only condition you need to check for is if the values are equal.

ORDER BY value DESC is the criteria for sorting the table rows. The sorting can be either ascending ASC or descending DESC

NOTE: sort numerical columns by their numerical value and not by their string value.

For more info on SQL syntax:
https://www.w3schools.com/sql/default.asp


Image source: https://unsplash.com/photos/PkbZahEG2Ng
Input
Line 1: A string containing the query you need to parse.
Line 2: An integer n for the number of table rows (or entries).
Line 3: A string representing a table header, containing the title of each table column, separated by space.
Following n Lines: A string representing the values for each column, separated by space.
Output
Line 1: A string representing the header of the table, containing the selected column titles separated by space.
Following Lines: A string representing the values for each selected column, separated by space.
Constraints
2 ≤ Columns ≤ 10
2 ≤ N ≤ 30
Example
Input
SELECT * FROM cats
3
Name Breed
McFur Peterbald
Squeeky Bambino
Greta Laperm
Output
Name Breed
McFur Peterbald
Squeeky Bambino
Greta Laperm
Tags
SQL, Parsing, Data Types, DataBases, Tables
Difficulty
Easy
Test cases
Select All Test
Input
SELECT * FROM cats 3 Name Breed McFur Peterbald Squeeky Bambino Greta Laperm
Output
Name Breed McFur Peterbald Squeeky Bambino Greta Laperm
Random Validator
Input
SELECT * FROM Raccoons 3 Name Breed Josh Pygmaeus Alexa Cancrivorus Odin Lotor
Output
Name Breed Josh Pygmaeus Alexa Cancrivorus Odin Lotor
Select From Where Test
Input
SELECT Name, FriesSize FROM burgers WHERE FriesSize = plenty 5 Name FriesSize Sauce JustBaconNoPatty NoFriesThanks PlainSauce Classic plenty Ketchup Classic++ 3MichelineStarFries MustardFoam BlueBurger plenty BlueCheese Vegan CelerySticks BambooSugar
Output
Name FriesSize Classic plenty BlueBurger plenty
Drop Tables Validator
Input
SELECT Name, Sauce FROM burgers WHERE Sauce = PlainSauce 6 Name FriesSize Sauce JustBaconNoPatty NoFriesThanks PlainSauce Classic 3Fries Ketchup Classic++ plenty Mustard BlueBurger plenty BlueCheese Vegan CelerySticks BambooSugar BeetRoot SweetFries PlainSauce
Output
Name Sauce JustBaconNoPatty PlainSauce BeetRoot PlainSauce
ORDER BY DESC Test
Input
SELECT * FROM Fishmonger WHERE Environment = FreshWater ORDER BY Weight DESC 8 Name Environment Type Weight Prawns FreshWater ShellFish 0.25 Beluga SaltWater Caviar 1 Salmon FreshWater Fish 15 SeaBream SaltWater Fish 0.5 BlueFinTuna SaltWater Fish 600 Salmon SaltWater Fish 5 Trout FreshWater Fish 2 Lobster SaltWater ShellFish 6
Output
Name Environment Type Weight Salmon FreshWater Fish 15 Trout FreshWater Fish 2 Prawns FreshWater ShellFish 0.25
Sub queries Validator
Input
SELECT * FROM Fishmonger WHERE Environment = SaltWater ORDER BY Weight ASC 8 Name Environment Type Weight Salmon FreshWater Fish 15 Beluga SaltWater Caviar 0.1 SeaBream SaltWater Fish 0.5 BlueFinTuna SaltWater Fish 678 Salmon SaltWater Fish 5 Trout FreshWater Fish 2 Prawns FreshWater ShellFish 0.25 Lobster SaltWater ShellFish 6
Output
Name Environment Type Weight Beluga SaltWater Caviar 0.1 SeaBream SaltWater Fish 0.5 Salmon SaltWater Fish 5 Lobster SaltWater ShellFish 6 BlueFinTuna SaltWater Fish 678
Slightly bigger table Test
Input
SELECT Suburb, Regionname FROM Housing WHERE Rooms = 4 ORDER BY CouncilArea DESC 10 Suburb Rooms CouncilArea Regionname Bulleen 3 Manningham EasternMetropolitan Hawthorn 1 Boroondara SouthernMetropolitan Hawthorn 3 Boroondara SouthernMetropolitan Hawthorn 2 Boroondara SouthernMetropolitan Hawthorn 4 Boroondara SouthernMetropolitan Hawthorn 2 Boroondara SouthernMetropolitan Healesville 3 YarraRanges NorthernVictoria HeidelbergHeights 3 Banyule EasternMetropolitan HeidelbergHeights 2 Banyule EasternMetropolitan HeidelbergWest 3 Banyule EasternMetropolitan
Output
Suburb Regionname Hawthorn SouthernMetropolitan
Ternary Operator Validator
Input
SELECT Rooms, Regionname FROM Housing WHERE Suburb = Heidelberg ORDER BY Rooms ASC 10 Suburb Rooms CouncilArea Regionname Watsonia 3 Banyule NorthernMetropolitan Ivanhoe 4 Banyule EasternMetropolitan Heidelberg 3 Banyule EasternMetropolitan HeidelbergWest 2 Banyule EasternMetropolitan Heidelberg 4 Banyule EasternMetropolitan HeidelbergWest 2 Banyule EasternMetropolitan Eaglemont 4 Banyule EasternMetropolitan HeidelbergWest 2 Banyule EasternMetropolitan Viewbank 4 Banyule EasternMetropolitan HeidelbergWest 2 Banyule EasternMetropolitan
Output
Rooms Regionname 3 EasternMetropolitan 4 EasternMetropolitan
Titanic Table Test
Input
SELECT Name, Age, Ticket FROM Titanic ORDER BY Age ASC 30 PassengerId Survived Pclass Name Sex Age SibSp Parch Ticket Fare 760 1 1 Rothes female 33 0 0 110152 86.5000 258 1 1 Cherry female 30 0 0 110152 86.5000 505 1 1 Maioni female 16 0 0 110152 86.5000 586 1 1 Taussig female 18 0 2 110413 79.6500 559 1 1 Taussig female 39.1 1 1 110413 79.6500 263 0 1 Taussig male 52 1 1 110413 79.6500 111 0 1 Porter male 47.1 0 0 110465 52000 431 1 1 Bjornstrom-Steffansson male 28 0 0 110564 26.5500 367 1 1 Warren female 60 1 0 110813 75.2500 171 0 1 Van male 61 0 0 111240 33.5000 463 0 1 Gee male 47 0 0 111320 38.5000 524 1 1 Hippach female 44 0 1 111361 57.9792 330 1 1 Hippach female 16.1 0 1 111361 57.9792 890 1 1 Behr male 26 0 0 111369 30000 605 1 1 Homer male 35 0 0 111426 26.5500 188 1 1 Romaine male 45 0 0 111428 26.5500 807 0 1 Andrews male 39 0 0 112050 0000 888 1 1 Graham female 19 0 0 112053 30000 264 0 1 Harrison male 40 0 0 112059 0000 210 1 1 Blank male 40.1 0 0 112277 31000 332 0 1 Partner male 45.5 0 0 113043 28.5000 537 0 1 Butt male 45.1 0 0 113050 26.5500 453 0 1 Foreman male 30.1 0 0 113051 27.7500 858 1 1 Daly male 51 0 0 113055 26.5500 84 0 1 Carrau male 28.1 0 0 113059 47.1000 783 0 1 Long male 29 0 0 113501 30000 378 0 1 Widener male 27 0 2 113503 211.5000 357 1 1 Bowerman female 22 0 1 113505 55000 55 0 1 Ostby male 65 0 1 113509 61.9792 253 0 1 Stead male 62 0 0 113514 26.5500
Output
Name Age Ticket Maioni 16 110152 Hippach 16.1 111361 Taussig 18 110413 Graham 19 112053 Bowerman 22 113505 Behr 26 111369 Widener 27 113503 Bjornstrom-Steffansson 28 110564 Carrau 28.1 113059 Long 29 113501 Cherry 30 110152 Foreman 30.1 113051 Rothes 33 110152 Homer 35 111426 Andrews 39 112050 Taussig 39.1 110413 Harrison 40 112059 Blank 40.1 112277 Hippach 44 111361 Romaine 45 111428 Butt 45.1 113050 Partner 45.5 113043 Gee 47 111320 Porter 47.1 110465 Daly 51 113055 Taussig 52 110413 Warren 60 110813 Van 61 111240 Stead 62 113514 Ostby 65 113509
Alternate Keys Validator
Input
SELECT Name, Age, Ticket FROM Titanic ORDER BY Age DESC 30 PassengerId Survived Pclass Name Sex Age SibSp Parch Ticket Fare 760 1 1 Rothes female 33 0 0 110152 86.5000 258 1 1 Cherry female 30 0 0 110152 86.5000 505 1 1 Maioni female 16 0 0 110152 86.5000 586 1 1 Taussig female 18 0 2 110413 79.6500 559 1 1 Taussig female 39.1 1 1 110413 79.6500 263 0 1 Taussig male 52 1 1 110413 79.6500 111 0 1 Porter male 47.1 0 0 110465 52000 431 1 1 Bjornstrom-Steffansson male 28 0 0 110564 26.5500 367 1 1 Warren female 60 1 0 110813 75.2500 171 0 1 Van male 61 0 0 111240 33.5000 463 0 1 Gee male 47 0 0 111320 38.5000 524 1 1 Hippach female 44 0 1 111361 57.9792 330 1 1 Hippach female 16.1 0 1 111361 57.9792 890 1 1 Behr male 26 0 0 111369 30000 605 1 1 Homer male 35 0 0 111426 26.5500 188 1 1 Romaine male 45 0 0 111428 26.5500 807 0 1 Andrews male 39 0 0 112050 0000 888 1 1 Graham female 19 0 0 112053 30000 264 0 1 Harrison male 40 0 0 112059 0000 210 1 1 Blank male 40.1 0 0 112277 31000 332 0 1 Partner male 45.5 0 0 113043 28.5000 537 0 1 Butt male 45.1 0 0 113050 26.5500 453 0 1 Foreman male 30.1 0 0 113051 27.7500 858 1 1 Daly male 51 0 0 113055 26.5500 84 0 1 Carrau male 28.1 0 0 113059 47.1000 783 0 1 Long male 29 0 0 113501 30000 378 0 1 Widener male 27 0 2 113503 211.5000 357 1 1 Bowerman female 22 0 1 113505 55000 55 0 1 Ostby male 65 0 1 113509 61.9792 253 0 1 Stead male 62 0 0 113514 26.5500
Output
Name Age Ticket Ostby 65 113509 Stead 62 113514 Van 61 111240 Warren 60 110813 Taussig 52 110413 Daly 51 113055 Porter 47.1 110465 Gee 47 111320 Partner 45.5 113043 Butt 45.1 113050 Romaine 45 111428 Hippach 44 111361 Blank 40.1 112277 Harrison 40 112059 Taussig 39.1 110413 Andrews 39 112050 Homer 35 111426 Rothes 33 110152 Foreman 30.1 113051 Cherry 30 110152 Long 29 113501 Carrau 28.1 113059 Bjornstrom-Steffansson 28 110564 Widener 27 113503 Behr 26 111369 Bowerman 22 113505 Graham 19 112053 Taussig 18 110413 Hippach 16.1 111361 Maioni 16 110152
Solution language
Solution
Stub generator input
ABOUT USCAREERSPRIVACYFAQBLOG
ABOUT USCAREERSPRIVACYFAQBLOG

The #1 tech hiring platform

SCREENINGINTERVIEWING
FacebookYouTubeTwitterLinkedIn