Average Population

  • + 1 comment

    It seems there is a bug with parsing the SQL syntax for this problem; the AS keyword causes a failure when it is perfectly valid. When aliasing the table name "City":

    This works

    City c

    This does not

    City AS c

    • + 1 comment

      why are you aliasing a table name when it is not required? it will throw an error here because alias name is not being used anywhere later.

      • + 2 comments

        Your comment doesn't actually address the problem. The problem is: using the AS keyword when aliasing a table is perfectly valid T-SQL syntax yet the submission produces errors.

        I alias tables regardless of necessity because I feel it is a best practice and also does not affect the performance of the query in any negative way. To your second point, consider the following (which fails): SELECT c.ID FROM City AS c. The alias is referenced in the SELECT query-part which, in terms of the T-SQL interpreter, is compiled after the FROM query-part where the alias is defined.

        • + 1 comment

          It's not broken; your syntax is OK for MS SQL but not for Oracle; for Oracle, AS is only valid syntax when aliasing columns and not when aliasing table names. If you were to try

          SELECT 
              c.ID AS i
          FROM 
              City c;
          

          it will execute fine, because that is correct syntax for Oracle. I don't know the specific reason for it (though there are some legitimate arguments out there against using AS), but it's mentioned in many places such as some Oracle forums, techonthenet, and Oracle's SELECT documentation's mentions of Column Alias:

          c_alias 
          Specify an alias for the column expression. Oracle Database will use this alias in the column heading of the result set. The AS keyword is optional. The alias effectively renames the select list item for the duration of the query. The alias can be used in the order_by_clause but not other clauses in the query.
          

          and Table Alias

          t_alias
          Specify a correlation name, which is alias for the table, view, materialized view, or subquery for evaluating the query. This alias is required if the select list references any object type attributes or object type methods. Correlation names are most often used in a correlated query. Other references to the table, view, or materialized view throughout the query must refer to this alias.
          

          There is no mention of AS syntax being allowable for t_alias in Oracle's Database SQL Language Reference, and indeed every example they have (in the lang ref for correlated subqueries, subclauses, etc.) uses the syntax:

          FROM table t_alias
          
          • + 1 comment

            This all makes sense and I really appreciate you spending the time to reply. Unfortunately, when I wrote the post I did not specify that I was actually using "MS SQL Server" as the language/parser. According to the MSDN documentation for Microsoft Transact-SQL, AS is perfectly valid in a FROM clause to alias a table or view. This is a snippet from their documentation which holds true back to SQL Server 2005:

            [ FROM { <table_source> } [ ,...n ] ] 
            <table_source> ::= 
            {
                table_or_view_name [ [ AS ] table_alias ]
                ...
            

            I should have been more clear when I commented about this and I apologize. I tested the submission again using the AS keyword and it now works; it would seem that this issue has been resolved since the time I posted. I had submitted a bug to HackerRank so I suppose they finally got to it.

            • + 1 comment

              Because I checked it and knew it was working, my assumption was you either didnt know the syntax difference or were accidentally trying to submit a MS SQL solution (due to the TSQL references) as an Oracle solution.

              • + 0 comments

                Perfectly reasonable assumption; sorry for not being clear the first time around.

        • + 0 comments

          My best guess as to the reason is that a table alias is a correlation name, and a correlation name cannot be used for columns (hence the syntax difference): http://docs.oracle.com/javadb/10.8.3.0/ref/rrefcorrelationname.html