Given
a proper SQL (Structured Query Language) statement, identify the objects and
group by operations in the SQL statement. Assume SQL-92 standard specification
to which all the input SQLs will adhere to. Assume that SQL queries are
composed only on tables (no views).
1.
Your
program should implement a SQL Analyzer which will break up the query into
number of tables and number of group by clauses.
2.
If
a table is accessed more than once (say twice), print table name twice. Print
table names in case-insensitive sorted order. (A-Z)
3.
Print
group by statements in sorted order of the columns used in group by clause
(A-Z)
Input Format:
Input will consists 1
item as described below.
|
Line 1
|
Absolute path to file containing the SQL query
|
Output Format:
Lets say, the SQL query
in the input is
select count(uname),name from present_address pr,country cn,customer_details
cd, address a
where a.addrid=cd.addrid and
a.adtid=pr.adtid and
cn.countryid=pr.countryid
group by name;
then,output should print
the same query in first line of the output followed by the
analysis.
select count(uname),name from present_address pr,country cn,customer_details
cd,address a where a.addrid=cd.addrid and a.adtid=pr.adtid and
cn.countryid=pr.countryid group by name;
Table : address
Table : country
Table : customer_details
Table : present_address
Group By : name
Note:
This is a sample data file only. Actual data file may different types of
queries, but is guaranteed to adhere to SQL-92 format.
Sample
Input and Output
|
SNo.
|
Input
|
Output
|
Comments
|
|
1
|
/tmp/query1.txt
|
select count(uname),name from present_address pr,country cn,customer_details
cd,address a where a.addrid=cd.addrid and a.adtid=pr.adtid and
cn.countryid=pr.countryid group by name;
Table : address
Table : country
Table : customer_details
Table : present_address
Group By : name
|
The entire SQL is Line 1.
Line 2 – 5 are names of tables used in the query, printed in sorted order
(A-Z) of the tablename.
Next Line(s) is Group By
|
0 Comments