[Community Puzzle] Remove insert statements

Puzzles
#1

Coding Games and Programming Challenges to Code Better

Send your feedback or ask for help here!

Created by @nguyen.lam,validated by @Razovsky,@Regulus136 and @Drunkey.
If you have any issues, feel free to ping them.

#2

I think the test cases are missing many edge cases: e.g.

  • the insert, begin, end, or -- strings within a string literal;
  • the insert, begin, end, or ' strings within a comment;
  • multi-line insert with several of its lines ending with a comment (not just after the final ; )
  • nested begin… end blocks
#3

I’m afraid writing a proper SQL tokenizer is no longer “easy”… So the text should indeed have mentioned the simplifications made, especially regarding strings.
Other missing tests include spaces before or after a ‘;’, as well as at the start of a line (which has been specifically pointed out as possible for ‘insert’).

#4

:wave: Hi to all, and thanks to @nguyen.lam for this “parsing” puzzle :wink: !
I don’t know yet about SQL requests, but i know a bit about parsing text data using machine state …
→ and this one is not specially a difficult one.

Thanks for this pleasant exercise, and to the Aprovers too (Razovsky, @Regulus136 and @Drunkey) :slight_smile: !

Have sun :sun_with_face: , fun :dark_sunglasses: and CodinGames :keyboard: … :rofl: !

#6

Hi, I passed all tests, but my code still fails validators 6 (functions + insert) and 7 (multi-line insert instruction), however I didn’t find an issue. Does anyone have some edge cases for these two ?
I can also send my code by private message.
Thanks a lot.

#7

You may send me your code by private message and I’ll take a look.

#8

So I managed to complete the puzzle with the following test case :

Input:

25
--
-- Name: fp_insert(integer)
--
CREATE FUNCTION func.fp_insert(pid integer) RETURNS integer
    LANGUAGE plpgsql
    AS $$
declare
Begin
  return 1;
end;
$$;
-- TOC entry 49324
INSERT INTO func.ap VALUES ('PLAYER1', 'CODINGAME', 0);
INSERT into func.ap VALUES ('PLAYER2', 'CODINGAME', 1);
insert INTO func.ap VALUES ('PLAYER3', 'CODINGAME', 2);
--
CREATE TABLE winners (
    date_creation timestamp without time zone,
    game_name text,
    id character integer NOT NULL,
);
insert into winners(date_creation, game_name, id_ch) 
values('BOSS', 'CODINGAME', -1);
Insert INTO func.wr VALUES ('DON', '2024-04-28 21:00:00');
-- END Batch

Output:

--
-- Name: fp_insert(integer)
--
CREATE FUNCTION func.fp_insert(pid integer) RETURNS integer
    LANGUAGE plpgsql
    AS $$
declare
Begin
  return 1;
end;
$$;
-- TOC entry 49324
--
CREATE TABLE winners (
    date_creation timestamp without time zone,
    game_name text,
    id character integer NOT NULL,
);
-- END Batch

For me, the point was that I looked for begin followed by a space, and I forgot the line could end right after the keyword. Also, I looked for a closing parenthesis to detect when a multi-line insert would end, but it was the semicolon.

Many thanks to 5DN1L who shared this example

1 Like
#9

Hi, I passed all the tests except for 7-2) multi-line INSERT instructions validation. I don’t understand this test because I’m not familiar with SQL, so it’s hard for me to grasp what is expected in terms of the return. Could you please clarify what needs to be done for this test? thx =)

#10

Have you tried the custom case here?

#11

yes this custom is working for me

#12

It’s difficult to tell what the differences are between the validator your code fails and the corresponding test.

If you want, you may send me your code via private message and I’ll take a look.

#13

“Yes, I understand, it’s difficult without code. How can I send you a private message? This is my first time here. =)”

#14

I’ve sent you a private message just now. You may proceed there.

Edit

For those who fail the validator “7-2 ) multi-line INSERT instructions val”, this custom case may be helpful for debugging:

Input

27
-- Create a table to store transaction details
CREATE TABLE purchase (
    transaction_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customer(customer_id),
    transaction_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    product_name TEXT NOT NULL,
    amount DOUBLE PRECISION NOT NULL,
    status VARCHAR(20) DEFAULT 'Completed'
);
-- Insert sample data into the purchase table
INSERT INTO purchase (customer_id, transaction_date, product_name, amount, status)
VALUES
(1,
'2023-05-01 11:00:00',
'Wireless Headphones',
150.00,
'Completed'),
(1,
 '2023-05-10 09:45:00',
  'Laptop Sleeve',
   100.75,
    'Completed'),
(2,
 '2023-06-20 13:20:00',
'Phone Case',
 20.50,
NULL);

Output

-- Create a table to store transaction details
CREATE TABLE purchase (
    transaction_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customer(customer_id),
    transaction_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    product_name TEXT NOT NULL,
    amount DOUBLE PRECISION NOT NULL,
    status VARCHAR(20) DEFAULT 'Completed'
);
-- Insert sample data into the purchase table
#15

My opinion is that it should have been explicitly stated that Insert is a keyword in this puzzle only if it is followed by a space or the end of a line.