SQL Query Reference

ExcelSQL supports SELECT queries with WHERE, GROUP BY, HAVING, ORDER BY and JOINS clauses. This document details Aqua Data Studio's ExcelSQL syntax.

"Database" refers to the directory where the Excel file resides. "Schema" refers to the Excel file. "Tables" are the worksheets in the Excel file.

An object name may be quoted or unquoted. If an object name contains special characters, you must quote it whenever you refer to it. The quote identifiers supported by ExcelSQL are the square brackets ([object name]) and double quotes ("object name").

Contents

Keywords are not case-sensitive. In this document, keywords such as SELECT are capitalized for illustration purposes.

Query Syntax

ExcelSQL queries are SELECT statements of below form:

SELECT [NO_HEADER_ROW] * | COUNT(*) | column1, column2, ...
   [FROM worksheet]
GO

ExcelSQL supports WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, OFFSET and JOIN clauses.

SELECT [NO_HEADER_ROW] [DISTINCT] * | column1 [AS alias1], column2 [AS alias2], ...
   [FROM worksheet]
   [WHERE condition]
   [GROUP BY column1, column2, ...]
   [HAVING condition]
   [ORDER BY column1 [DESC|ASC], column2 [DESC|ASC], ...]
   [LIMIT number [OFFSET number]
GO

Back to top

SELECT clause

The SELECT clause is used to select data from an Excel worksheet.

SELECT * | COUNT(*) | column1, column2, ...

SELECT * | column1 [AS alias1], column2 [AS alias2] 

Example

SELECT * FROM Sheet1
GO
SELECT city, zipcode FROM zips
GO
SELECT COUNT(*) FROM zips
GO
SELECT SUM(population) AS [Total Population] FROM zips
GO

Back to top

NO_HEADER_ROW option

NO_HEADER_ROW

By default, the first row is considered as headers. If there is no header, specify the NO_HEADER_ROW option and Excel's headers will be used as column names.

Example

SELECT NO_HEADER_ROW * FROM Sheet2
GO
SELECT NO_HEADER_ROW COUNT(*) FROM Sheet2
GO
SELECT NO_HEADER_ROW B, C FROM Sheet2
GO
SELECT NO_HEADER_ROW [employee].A, [project].B AS Project FROM employee INNER JOIN project ON [employee].B = [project].B
GO

Back to top

DISTINCT clause 

DISTINCT

A column can contain duplicate values, and to list the distinct values, use the SELECT DISTINCT clause. The DISTINCT clause can be used to return only distinct values from a set of records.

Example

SELECT DISTINCT city FROM zips
GO
SELECT DISTINCT * FROM employee
GO

Back to top

FROM clause

Determines the specific dataset to examine to retrieve data. For Excel, this would be indicated as a specific worksheet.

We support the following syntax for specifying a worksheet that is in a different schema (Excel file) than the currently connected schema.

SELECT * FROM workbook.worksheet

where the Excel file name is workbook and the worksheet name is worksheet.

Back to top

SubTable feature

In addition, to query a range of cells in the worksheet, use the following syntax to specify the starting cell and the ending cell of the range.

SELECT * FROM [worksheet$A1:F500]

where the worksheet name is worksheet, the starting cell is A1, and the ending cell is F500.

Note: You must enclose the table and range in quote identifiers [ ] or " ".

Examples

SELECT EID, fname FROM [employee$A1:F10] WHERE EID = 10001 AND Fname = 'Jon'
GO
SELECT COUNT(EID) FROM [Worksheet1$A1:F500] HAVING COUNT(EID) > 5
GO
SELECT * FROM [Worksheet1$A1:F10] LEFT JOIN [Worksheet2$A1:F10] ON [Worksheet1$A1:F10].[PID]=[Worksheet2$A1:F10].[EID]
GO
SELECT NO_HEADER_ROW * FROM "employee$A1:F10" LIMIT 100 OFFSET 5
GO

You can also use the LIMIT and OFFSET clauses to query a range of cells in the worksheet.

SELECT * FROM worksheet LIMIT 500 OFFSET 10

Examples

SELECT NO_HEADER_ROW B, C, D FROM orders LIMIT 1000 OFFSET 5
GO

Back to top

WHERE clause 

The WHERE clause, sometimes called the predicate, states the qualifying conditions for a query. You can combine the ExcelSQL Arithmetic Operators and the ExcelSQL Comparison Operators in the WHERE clause. Multiple conditions can be joined by the AND and OR clauses, optionally surrounded by (parentheses) to group them. Only the records that satisfy the specified criteria are returned by the query.

SELECT column FROM worksheet WHERE column operator value …

Conditions:

condition AND condition
condition ANY condition
condition OR condition
column = predicate
column != predicate
column <> predicate
column >= predicate
column > predicate
column <= predicate
column < predicate
column EXISTS
column NOT EXISTS
column BETWEEN value1 AND value2
column NOT BETWEEN value1 AND value2
column IN (value [, value ...])
column LIKE like_expr
column IS NULL
column IS NOT NULL
column NOT (value [, value ...])
column NOT IN (value [, value ...])
column NOT LIKE like_expr

When specifying the condition, value must be an exact match of the column value in the worksheet. String value comparisons are case sensitive. You can use the UPPER() function to perform case insensitive comparisons. You can find more information here. Or you can use the SET IGNORECASE TRUE command to make string comparisons be case insensitive.

When using AND and OR to specify multiple conditions, use (parentheses) to group the conditions. If no parentheses are specified, the conditions specified with AND are evaluated together.

condition1 AND condition2 AND condition3 OR condition4 
is equivalent to
(condition1) AND (condition2) AND (condition3 OR condition4)

condition1 AND condition2 OR condition3 AND condition4
is equivalent to
(condition1) AND (condition2 OR condition3) AND (condition4)

Examples

SELECT * FROM Sheet1 WHERE "col1" = 'USD' AND "col2" = 'INR' 
GO
SELECT city, zipcode FROM zips WHERE state LIKE '%MA%'
GO
SELECT COUNT(*) FROM zips WHERE state IN ('MA','PA')
GO
SELECT COUNT(*) FROM zips WHERE country = 'Germany' OR city='Berlin'
GO
SELECT * FROM Project WHERE EXISTS (SELECT * FROM Employee)
GO
SELECT * FROM Employee WHERE E_id NOT BETWEEN 1 AND 2
GO
SELECT * FROM Employee WHERE Joining_date = '2014-6-28 10:50:53'
GO

Notes

• Timestamp values will be displayed using the format specified in the locale. When you specify the criteria in the WHERE clause, you need to use the JDBC timestamp format. If you want to specify the timestamp criteria using a different format, you can use the PARSEDATETIME function.

Example

SELECT * FROM sheet1
WHERE P_date_time = PARSEDATETIME('6/28/2014 10:50:53 AM', 'MM/dd/yyyy HH:mm:ss a')

• WHERE clause cannot be used on a column with mixed data types. An error message "Data conversion error converting" will be displayed.
  
  If a column contains data with mixed data types, the data needs to be converted to one data type. Use the data conversion functions in the query.

Example

SELECT * FROM mixedDataTypes.Sheet1
WHERE TO_CHAR(column2) = '11.0'
GO
SELECT * FROM mixedDataTypes.Sheet1
WHERE TO_CHAR(column2) = '11.0' OR TO_CHAR(column2) = 'test'
GO

Back to top

GROUP BY clause

The GROUP BY clause is used in combination with aggregate functions to group the results by one or more columns. See the SQL Functions page for a list of aggregate functions supported by ExcelSQL.

SELECT column1, aggregate_function(column2) FROM worksheet
GROUP BY column1

Example

SELECT name, COUNT(orders) FROM zips GROUP BY name
GO

Back to top

HAVING clause

The HAVING clause states the qualifying conditions for aggregated values. It is used in conjunction with aggregate functions to filter aggregated values.

SELECT column1, aggregate_function(column2) FROM worksheet
GROUP BY column1
HAVING aggregate_function(column2) operator value

Example

SELECT name, COUNT(orders) FROM zips GROUP BY name HAVING COUNT(orders) > 25
GO

Back to top

ORDER BY clause

ORDER BY is used to sort the results by one or more columns and sorts in ascending order by default. To sort in descending order, use the DESC keyword.

SELECT column1, column2 FROM worksheet ORDER BY column1 ASC | DESC, column2 ASC | DESC

Examples

SELECT * FROM zips ORDER BY country DESC
GO
SELECT city, zipcode FROM zips ORDER BY city, zipcode
GO

Note

• ORDER BY clause cannot be used on a column with mixed data types. An error message "Data conversion error converting" will be displayed.
  
  If a column contains data with mixed data types, the data needs to be converted to one data type. Use the data conversion functions in the query.

Example

SELECT PostalCode FROM zips ORDER BY TO_CHAR(PostalCode)
GO

Back to top

LIMIT and OFFSET clause

LIMIT and OFFSET allow you to retrieve just a portion of the result set returned by the query. LIMIT restricts the number of rows returned by the SELECT statement. OFFSET indicates how many rows to skip. OFFSET 0 is the same as omitting the OFFSET clause.

SELECT column FROM worksheet LIMIT number OFFSET number

Examples

SELECT * FROM zips LIMIT 5
GO
SELECT * FROM city LIMIT 2 OFFSET 3

Back to top

Set Operators

ExcelSQL supports the below set operators. Set operators combine results from two or more queries into a single result set.

MINUS, EXCEPT, INTERSECT

UNION and UNION ALL

MINUS

The MINUS operator returns only unique rows that are returned by the first query but not by the second query.

Examples

 SELECT city, zipcode FROM zips
 MINUS
 SELECT city, zipcode FROM pins

Back to top

EXCEPT 

The EXCEPT operator returns rows that are returned by the first query but not by the second query.

Examples

 SELECT city, zipcode FROM zips
 EXCEPT
 SELECT city, zipcode FROM pins

Back to top

INTERSECT 

The INTERSECT operator returns only rows that are returned by both queries.

Examples

 SELECT city, zipcode FROM zips
 INTERSECT
 SELECT city, zipcode FROM pins

Back to top

UNION Operator

The UNION operator combines the results of two queries and eliminates duplicate rows.

SELECT column_names(s) FROM table1 UNION SELECT column_names(s) FROM table2

Examples

SELECT city, zipcode FROM zips 
UNION
SELECT city, zipcode FROM pins
GO

Back to top

UNION ALL Operator

The UNION ALL operator combines the results of two queries. It does not remove duplicate rows. All rows are returned.

SELECT city, zipcode FROM zips 
UNION ALL
SELECT city, zipcode FROM pins
GO

SUBQUERY 

A subquery is a SELECT statement nested inside a SELECT statement or inside another subquery. It is usually added within the WHERE clause of another ExcelSQL SELECT statement. 

Examples

SELECT (SELECT P_id FROM Project where E_id=1), Fname FROM Employee WHERE E_id=1
GO
SELECT a,b AS total_sum FROM (SELECT SUM(expenses) AS a, SUM(Salary) AS b FROM Employee)
GO
SELECT * FROM Employee E WHERE E_id IN (SELECT P_id FROM Project WHERE P_id = 10001)
GO

Back to top

SQL Aliases

SQL aliases are used to temporarily assign a different name to a table or column heading. Basically aliases are created to make column names more readable. The WHERE, ORDER BY, GROUP BY, HAVING and JOIN clauses also support aliases for ExcelSQL.

SQL Alias Syntax for Columns

SELECT column_name AS alias_name FROM worksheet

SQL Alias Syntax for Tables

SELECT column_name FROM worksheet AS alias_name

Examples

SELECT zipc AS ZipCode FROM zips
GO
SELECT city FROM zips AS Z
GO
SELECT A.E_id FROM employee AS A WHERE A.E_id = 1
GO
SELECT COUNT(A.E_id) as B FROM Employee AS A HAVING COUNT(A.age) < 10
GO
SELECT A.E_id, B.P_id FROM Employee AS A INNER JOIN Project AS B ON B.p_id = A.e_id
GO

Back to top

Joins 

There are different types of joins available in ExcelSQL: INNER JOIN, LEFT JOIN and RIGHT JOIN. INNER JOIN returns rows when there is a match on both tables. LEFT JOIN returns all rows from the left table even if there are no matches in the right table. RIGHT JOIN returns all rows from the right table even if there are no matches in the left table. You can find the syntax for the different joins below.

INNER JOIN clause

SELECT column1, column2 FROM worksheet1 INNER JOIN worksheet2 ON worksheet1.column1 = worksheet2.column2

The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables.

Joins can work across different schema. The tables do not have to be within the same schema (Excel file).

The valid operator for the ON clause are AND, OR, =, >, <, <>, >=, <=, !=.

In addition, nested joins are supported.

Example

SELECT C.name, O.customerID FROM Customers C INNER JOIN Orders O ON C.customerID = O.CustomerID

Example for 3 table joins in the same schema (same workbook) 

SELECT C.name, O.customerID FROM [pubs].Customers C
INNER JOIN [pubs].Orders O ON C.customerID = O.customerID
RIGHT JOIN [pubs].Sales S ON S.orderID = C.customerID  

Example for 3 table joins between different schemas (different workbooks)

SELECT C.name, O.customerID from [pubs].Customers C
INNER JOIN [bi].Orders O ON C.customerID = O.customerID
INNER JOIN [pubs].Sales S ON S.orderID > C.customerID

Back to top

LEFT JOIN clause

SELECT column1, column2 FROM worksheet1 LEFT JOIN worksheet2 ON worksheet1.column1 = worksheet2.column2

The LEFT JOIN clause returns all rows from the left table (worksheet1) with the matching rows in the right table (worksheet2). The result set returns no values in the right table when there is no match.

Joins can work across different schema. The tables do not have to be within the same schema (Excel file).

The valid operator for the ON clause are AND, OR, =, >, <, <>, >=, <=, !=.

In addition, nested joins are supported.

Example

SELECT C.name, O.customerID FROM Customers C LEFT JOIN Orders O ON C.customerID = O.customerID

Back to top

RIGHT JOIN clause

SELECT column1, column2 FROM worksheet1 RIGHT JOIN worksheet2 ON worksheet1.column1 = worksheet2.column2

The RIGHT JOIN clause returns all rows from the right table (worksheet2) with the matching rows in the left table (worksheet1). The result set returns no values in the left table when there is no match.

Joins can work across different schema. The tables do not have to be within the same schema (Excel file).

The valid operator for the ON clause are AND, OR, =, >, <, <>, >=, <=, !=.

In addition, nested joins are supported.

Example

SELECT C.name, O.customerID FROM Customers C RIGHT JOIN Orders O ON C.customerID = O.customerID

Note that you can use the UNION ALL clause to combine two joins to achieve a FULL JOIN.

Example

SELECT C.name, O.customerID FROM Customers C LEFT JOIN Orders O ON C.customerID = O.CustomerID
UNION ALL
SELECT C.name, O.customerID FROM Customers C RIGHT JOIN Orders O ON C.customerID = O.customerID

Back to top

Self Join

SELECT Column1, Column2 FROM Worksheet1 A, Worksheet1 B  WHERE  A.Column1 = B.Column1

Self join is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

The valid operator for the ON clause are AND, OR, =, >, <, <>, >=, <=, !=.

Example

SELECT A.E_id, B.E_id FROM employee A, employee B WHERE A.E_id = B.Mgr_id

Back to top

CROSS JOIN clause

SELECT Column1,Column2 FROM Worksheet1 CROSS JOIN Worksheet2

The CROSS JOIN (or CARTESIAN JOIN) returns the Cartesian product of the sets of records from two or more joined tables.

Example

SELECT A.E_id, B.P_id, A.fname FROM employee A CROSS JOIN project B

Back to top

Multiple AND/OR Conditions on Joins

Example

SELECT * FROM employee INNER JOIN project ON employee.E_id = Project.P_id AND employee.fname = project.p_name AND employee.salary = project.p_profit
GO
SELECT * FROM employee LEFT JOIN project ON employee.E_id = Project.P_id OR employee.fname = project.p_name OR employee.salary = project.p_profit

Back to top

Derived Tables

The Derived table is a technique for creating a temporary set of records which can be used within another query in ExcelSQL. You can use derived tables to shorten long SQL queries.

Examples

 SELECT Project.P_id, Project.budget FROM 
        (SELECT E.E_id, E.Salary FROM Employee E) emp 
                INNER JOIN Project on emp.E_id = Project.P_id

Back to top

ExcelSQL Arithmetic Operators 

SELECT 15+10-5*5/5 FROM [bi].zips
GO
SELECT cust_name, openg_amt, receiv_amt, outstand_amt, (receiv_amt*5/ 100) AS commission  
FROM [bi].customer  
WHERE outstanding_amt <= 4000
GO
SELECT StockUnits, StockUnits%3 AS "Modulo by 3"
FROM [bi].products

SELECT * FROM [bi].zips
WHERE city = 'Cochin'
GO
SELECT * FROM [bi].zips
WHERE city <> 'Cochin'
GO
SELECT *
FROM  [bi].zips
WHERE zipcode <= 682017
GO

Configuration Functions

SELECT @@database

This function returns information on the current database context.

Example

SELECT @@database

SELECT @@schema

This function returns information on the current schema context.

Example

SELECT @@schema

Back to top

Utility Statements

DATABASE database_name

This statement sets the database context and keeps this context for subsequent statements until the end of the session or another DATABASE statement is encountered.

Example

DATABASE Folder2

SET SCHEMA schema_name

This statement sets the schema context and keeps this context for subsequent statements until the end of the session or another SET SCHEMA statement is encountered.

Example

SET SCHEMA ExcelFile2

SET IGNORECASE TRUE|FALSE

This statement specifies whether text columns are case sensitive. Default is FALSE (i.e. case sensitive). SET IGNORECASE TRUE changes text comparisons to be case insensitive.

Example

SET IGNORECASE FALSE

SET COLLATION { OFF | collationName [ STRENGTH { PRIMARY | SECONDARY } TERTIARY | IDENTICAL } ] }

This statement sets the collation used for comparing and sorting strings. The COLLATION setting takes precedence over the IGNORECASE setting.

collationName: ALBANIAN, ARABIC, BELARUSIAN, BULGARIAN, CATALAN, CHINESE, CROATIAN, CZECH, DANISH, DUTCH, ENGLISH, ESTONIAN, FINNISH, FRENCH, GERMAN, GREEK, HEBREW, HINDI, HUNGARIAN, ICELANDIC, INDONESIAN, IRISH, ITALIAN, JAPANESE, KOREAN, LATVIAN, LITHUANIAN, MACEDONIAN, MALAY, MALTESE, NORWEGIAN, POLISH, PORTUGUESE, ROMANIAN, RUSSIAN, SERBIAN, SLOVAK, SLOVENIAN, SPANISH, SWEDISH, THAI, TURKISH, UKRAINIAN, VIETNAMESE.

STRENGTH option: PRIMARY is usually case- and umlaut-insensitive; SECONDARY is case-insensitive but umlaut-sensitive; TERTIARY is both case- and umlaut-sensitive; IDENTICAL is sensitive to all differences and only affects ordering.

Example

SET COLLATION ENGLISH STRENGTH PRIMARY

See the SHOW Commands page for additional information about commands used to describe database schema objects.

Back to top