Practice Parsing and SQL with the exercise "Remove insert statements"

Learning Opportunities

This puzzle can be solved using the following concepts. Practice using these concepts and improve your skills.

Parsing

Statement

Goal

Given a SQL file, you must:
- remove the INSERT statements that are actually inserting data in the database
- keep the INSERT statements that are embedded in functions.
- All others statements must remains the same

SQL syntax to consider:
- INSERT statement is a statement that starts with the INSERT keyword and ends with the semicolon ;. It can be written anywhere in the SQL file, it can be written on one or many lines. There is no constraint on where INSERT keyword appears first. It can start a new line or after spaces or it may have some indentation before the INSERT keyword
- comments begin with -- and comments must be kept in the script. Comments end at the EOL.
- the syntax used to denote the body of functions:

  BEGIN start of body
  END end of body
  BEGIN and END keywords are always at the start of a line.

Body is the main part of the function where SQL statements must be checked. If INSERT statements are written in this part, INSERT statements must be kept.
- INSERT, BEGIN and END are keywords, keywords are case-insensitive

EXAMPLES:
- Example of INSERT statement that must be removed:

  INSERT INTO ref_metric 
  VALUES (767, 'make it clear', 'some description', 2, 'LOW');

=> VALUES are hardcoded values or string literals

- Example of INSERT statement that must be kept:

  CREATE FUNCTION my_function(pObj integer, pUser integer) 
  LANGUAGE plpgsql
  AS $$
  declare
  begin 
    INSERT INTO lk_table(nb_object, id_user, stat) 
    VALUES ( pObj, pUser);
  end;

=> VALUES are variables, INSERT is embedded in the body of a function. The body is defined by BEGIN and END keywords.

TIP:
- You can copy/paste input file on some sql beautifier tools online to help you to identify relevant lines to remove.
- SQL statements are real statements that can be executed in SQL tools.

Input

Line 1: An integer n for the number of lines to read
Next n lines: SQL statements to parse

Output

m lines: SQL statements cleaned up without INSERT statements that are doing data insertion in the database. comments must remain in the output. m is the number of lines that remain.

Constraints

Example

Input

8
CREATE TABLE vendeur (
    id character integer NOT NULL,
    date_creation timestamp without time zone,
    name text,
    id_createur character varying(32),
    amount double precision,
);
insert into vendeur(id,date_creation, name, id_createur, amount) values(1,'12/08/2020','John',1,100);

Output

CREATE TABLE vendeur (
    id character integer NOT NULL,
    date_creation timestamp without time zone,
    name text,
    id_createur character varying(32),
    amount double precision,
);

Solve it

A higher resolution is required to access the IDE