SQL

SQL questions can be in the form of MCQs or questions that require you to write a solution. This is a detailed topic about how to answer SQL questions that require you to write a solution.

Parts of an SQL question

Allowed databases
HackerEarth Recruit supports the following databases:

• MySQL
• PostgreSQL
• Microsoft SQL
• Oracle database

Answering SQL questions

To answer the SQL questions, follow these steps:

1. Read the question and instructions carefully.
2. Write your SQL query in the code editor.
3. Click SAVE.
4. Click EXECUTE & TEST.
   Note: Your query should pass through sample input and output test cases. It doesn't go through rigorous checks.
5. Click SUBMIT.

Note

• Once you have submitted your code, you cannot edit it. However, you can make new submissions. All the submissions will be judged.
• HackerEarth provides the feature of automatically submitting the answer.

You have answered an SQL question successfully.

Your code is evaluated automatically by a HackerEarth SQL service that we call SQL judge.

Evaluating queries

When you submit your query, we do the following:

1. Setup a new database
2. Create tables
3. Load data specified in the test case
4. Execute your query on the newly set up database and compare the result with expected output.

If the output matches the expected result, then it is an accepted submission (AC). If the output does not match the expected result, then it is a wrong answer (WA).

Determining whether the solution is correct

Each problem will have the following:

One or more input files each of which is based on the specifications mentioned in the problem statement. Each input file has a  corresponding output file.

Your program is run on each input files. To be judged as a correct submission, the output that is generated by your queries must match the output in the corresponding output files. Therefore, ensure that your statements are in order

Notes

• Your output files must match the output files of the problem statement irrespective of what point in the program execution the output is written.
• It is recommended that you do not read all the input before starting the output. Instead, you can print each result as you are reading through the input.

Your code will be judged as an incorrect submission if:

1. You start your query in a way other than what is mentioned in the problem statement. 
   For example, if your query starts with ‘Enter the number’ and it is not mentioned in the problem statement, then your script will be judged as an incorrect submission irrespective of what the rest of your queries do.
2. You use a method other than using the standard input and output streams
   For example, if you use command line arguments, read from a file, open a dialog box, etc., your code will be judged as an incorrect submission.

Errors

Time Limit Exceeded (TLE)

Total execution time <= (time limit*number of input files)

In an online test, the judge allocates resources such as memory and so on for evaluating each submission. However, your submission cannot continue to run for an infinite amount of time, and therefore, the judge will stop your submission from running after a predefined (set by the recruiter) time. The TLE error will be displayed in the following cases:

• If your SQL script contains “sleep” and your queries stops executing.
• Your code terminates outside the time limit that was indicated 
  For example, if the time limit of an SQL script is 2 seconds and your script terminate on 2.001 seconds, it means:
  • Your script exceeded the time limit by 0.001 seconds
  • The judge stopped evaluating your code after 2 seconds

• If your solution exceeds the amount of time that is allotted for the problem or test case, or your solution never finished running in the allotted time because it was stopped in between. There is no definite way to say whether the code is correct or not.

Wrong Answer (WA)

This error indicates that your script ran successfully but the answer is incorrect. It will be displayed in the following cases:

• Your SQL script contains a bug
• You are not interpreting the problem statement correctly
• Your code matches the input test cases but does not match the output test cases

Runtime Error (RE)

This error will be displayed when your program compiled successfully but exited with an error or crashed. It indicates that your query has failed to execute. You can avoid this error by ensuring that:

• Your queries are syntactically correct
• Your script is not violating any of the problem constraints

Constraints Violation (CV)

This error will be displayed when one of the problem constraints is violated. The most common reasons for this error are as follows:

• You have created too many tables
• You have inserted too many rows inside a table

The constraints include the following:

• Maximum tables per database will be restricted constrained and you can only create tables up to the number that is specified in the problem

• Maximum rows per table will be restricted constrained and you can only insert rows up to the number that is specified in the problem.

• Your queries collectively have a hard time limit of 5 seconds.