Query Ocient

General SQL Syntax

﻿
The general syntax of SQL statements supported by the  is described below.
﻿Ocient SQL Syntax﻿
This code block shows the general syntax to perform SQL querying in  and the order in which commands should go. 
For specific descriptions and syntax for the commands, see the respective command sections on this page. 
﻿Syntax﻿
SQL
Text
[ WITH ... ]
SELECT ...
  [ FROM ...
    [ JOIN ... ] ] 
      
  [ WHERE ... ]
  [ GROUP BY ... 
    [ HAVING ... ] ]
  [ ORDER BY ... ] 
  [ LIMIT ... ]
  [ OFFSET ... ] 
  [ INTERSECT ... ]
  [ EXCEPT ... ]
  [ UNION ... ] ]  
       
  [ USING ... ]
      
  [ TRACE ... ] 
[ WITH ... ]
SELECT ...
  [ FROM ...
    [ JOIN ... ] ] 
      
  [ WHERE ... ]
  [ GROUP BY ... 
    [ HAVING ... ] ]
  [ ORDER BY ... ] 
  [ LIMIT ... ]
  [ OFFSET ... ] 
  [ INTERSECT ... ]
  [ EXCEPT ... ]
  [ UNION ... ] ]  
       
  [ USING ... ]
      
  [ TRACE ... ] 
﻿
Querying Command Reference
Ocient supports the following query commands.  
WITH
Assigns a name to a common table expression, allowing an auxiliary query to be used in the main query.  This is helpful for breaking down complex queries into smaller parts. 
﻿Syntax﻿
SQL
WITH 
  <cte_name> [ ( <cte_column> [ ,... ] ) ] 
  AS ( <sub_query> )
<select_query>  
WITH 
  <cte_name> [ ( <cte_column> [ ,... ] ) ] 
  AS ( <sub_query> )
<select_query>  
﻿
Parameters
Parameter
Description
<cte_name>
A name for the common table expression populated with the result set from the sub_query statement. 
<cte_column>
Optional. 
This is a list of column names for common table expression populated with the sub_query result set. If no column names are provided, the column names are the same as the table referenced in the sub_query statement. 
<sub_query>
An auxiliary query that collects data for a common table expression. 
sub_query can use regular query commands including ORDER BY, LIMIT, OFFSET, UNION, INTERSECT, EXCEPT, WHERE, GROUP BY, and HAVING.
See the respective command reference sections for usage rules. 
<select_query>
The main SELECT query. 
Note that the FROM statement in the main SELECT query must also include the cte_name if the query uses its values. 
Example
In this example, the subquery calculates the average budget for all rows in the movies table. The main query uses that average to find all movies that spent more. 
SQL
WITH avg_budget_table (average_budget) AS (
    SELECT AVG(budget)
    FROM movies
)
SELECT title,
    budget,
    revenue
FROM movies,
    avg_budget_table
WHERE budget > avg_budget_table.average_budget;
WITH avg_budget_table (average_budget) AS (
    SELECT AVG(budget)
    FROM movies
)
SELECT title,
    budget,
    revenue
FROM movies,
    avg_budget_table
WHERE budget > avg_budget_table.average_budget;
﻿
Output
title
budget
revenue
CGI Why
237000000
2787965087
Titania
200000000
1845034188
Merchandise Vehicle 5
200000000
1066969703
The Tentpole
220000000
1519557910
Pirates of Palm Springs
140000000
655011224
Spyman 16
200000000
1108561013
Frigid
150000000
1274219009
Fury 7
190000000
1506249360
Superhero 23
250000000
1084939099
Triassic World
150000000
1513528810
Iron Chef 3
200000000
1215439994
The Last Airman
150000000
318502923
SELECT
Initiates a query statement or a subquery clause within other statements. 
For information on using SELECT as a subquery for filtering or ordering results, see the WHERE﻿ and HAVING﻿ sections.   
For information on using SELECT as a subquery for a common table expression, see the WITH﻿ section. 
Syntax
SQL
SELECT [ ALL | DISTINCT ] 
  [ * [ EXCEPT ( column_name [ , ... ] ) ] 
  | <select_list_entry> [, ...] ]
  [ <from_clause> ]
SELECT [ ALL | DISTINCT ] 
  [ * [ EXCEPT ( column_name [ , ... ] ) ] 
  | <select_list_entry> [, ...] ]
  [ <from_clause> ]
﻿
Parameters
Parameter
Description
ALL
SELECT and SELECT ALL are both the same. 
Returns all valid data rows from the database that meets the criteria of your query. 
DISTINCT
SELECT DISTINCT returns only unique rows that do not match other rows based on the criteria of your query. 
*
Returns all columns from the specified tables in the result set for the query. 
EXCEPT
When used with *, EXCEPT allows specific columns to be excluded from the query results. 
column_name
The name of one or more columns from the specified table that you want to exclude (using EXCEPT) from your query results. 
<select_list_entry>
The select_list_entry defines a column or expression to include in your query result set. 
Syntax
SQL
<select_list_entry> ::= 
  column_name | expression [ AS new_name ] 
<select_list_entry> ::= 
  column_name | expression [ AS new_name ] 
﻿
Parameters
Parameter
Description
column_name
The name of one or more columns from the specified table that you want to include in your query results. 
expression
One or more expressions that you want to include in your query results. 
Expressions can be any combination of literal values, column names, arithmetic expressions, parentheses, and function calls.
new_name
An identifier for the results of the select_list_entry of the query. 
<from_clause>
For information on the <from_clause>, see the FROM﻿ documentation. 
Examples
Using SELECT * 
This example uses SELECT * to return all the columns in the movies datebase. 
SQL
SELECT * FROM movies LIMIT 5; 
SELECT * FROM movies LIMIT 5; 
﻿
Output
movie_id
title
budget
popularity
release_date
revenue
runtime
movie_status
vote_average
vote_count
211672
Minnows
74000000
875.581305
2015-06-17
1156730962
91
Released
6.40
4571
24
Billy the Killy
30000000
79.754966
2003-10-10
180949000
111
Released
7.70
4949
19995
CGI Why
237000000
150.437577
2009-12-10
2787965087
162
Released
7.20
11800
37724
Spyman 16
200000000
93.004993
2012-10-25
1108561013
143
Released
6.90
7604
24428
The Tentpole
220000000
144.448633
2012-04-25
1519557910
143
Released
7.40
11776
Using SELECT * EXCEPT
This example uses SELECT * EXCEPT to exclude certain columns from the result set.
SQL
SELECT *
EXCEPT (movie_id, runtime, vote_average, vote_count)
FROM movies
LIMIT 5;
SELECT *
EXCEPT (movie_id, runtime, vote_average, vote_count)
FROM movies
LIMIT 5;
﻿
Output
title
budget
release_date
revenue
Swords & Scabbards
94000000
2003-12-01
1118888979
Billy the Killy
30000000
2003-10-10
180949000
Merchandise Vehicle 5
200000000
2010-06-16
1066969703
CGI Why
237000000
2009-12-10
2787965087
Space Odyssey 6000
10500000
1968-04-10
68700000
﻿
FROM
Specifies the table or view to use in a SELECT statement.
﻿Syntax﻿
SQL
<select_clause> FROM 
 { table_name | ( <sub_query> ) } [ ,... ]
 [ <join_clause> [ ,... ] ]
<select_clause> FROM 
 { table_name | ( <sub_query> ) } [ ,... ]
 [ <join_clause> [ ,... ] ]
﻿
Parameters
Parameter
Description
<select_clause>
A query statement using the SELECT command. For more information, see the SELECT﻿ section. 
table_name
The names of one or more tables, views, or a table-valued functions that you want to query. 
If you specify multiple tables in the FROM list, separated by commas, the effect is the same as if you explicitly perform a CROSS JOIN﻿ on all of those sources.
For more information and syntax for using table-valued functions, see the Table-Valued Functions﻿ section. 
<sub_query>
One or more subqueries using the SELECT command. A subquery generates a table from which the select_clause query references data. 
Each subquery must be enclosed in parentheses with an optional correlation name.
For more information, see the SELECT﻿ section. 
<join_clause>
A JOIN clause used to retrieve data from two or more tables for your query. 
For more information, see the JOIN﻿ section. 
JOIN
﻿Combines rows from multiple tables so they can be accessed by a query.  
﻿Syntax﻿
SQL
<select_query> 
  FROM <table_reference1> <join_operation> <table_reference2> 
    ON table1_column <boolean_operator> table2_column  
<select_query> 
  FROM <table_reference1> <join_operation> <table_reference2> 
    ON table1_column <boolean_operator> table2_column  
﻿
Parameter
Parameter
Description
<select_query>
A valid SELECT query. 
For more information, see SELECT﻿.  
<table_reference1>
﻿Either a table name, a full SELECT in parentheses, or the output of a table-valued function. 
table_reference1 takes precedence for returning rows for LEFT OUTER JOIN, SEMI JOIN, and ANTI JOIN. For specific rules, see Types of Join Operations﻿.
<table_reference2>
Either a table name, a full SELECT in parentheses, or the output of a table-valued function.  
table_reference2 takes precedence for returning rows for RIGHT OUTER JOIN. For specific rules, see Types of Join Operations﻿. 
table1_column
A column from either table_reference  
The JOIN operation uses this column to match rows with table2_column to combine data for the result set. 
<boolean_operator>
Join conditions can use any boolean expression, including =, !=, <, >, =>, and <=
table2_column
A column from the table_reference not used for table1_column  
The JOIN operation uses this column to match rows based on the table1_column to combine data for the result set. 
Types of Join Operations ( <join_operation> )
Ocient supports the following types of JOIN operations. 
﻿Syntax﻿
SQL
<join_operation> ::=   
  { INNER JOIN
  | LEFT [ OUTER ] JOIN
  | RIGHT [ OUTER ] JOIN
  | FULL [ OUTER ] JOIN
  | CROSS JOIN
  | SEMI JOIN 
  | ANTI JOIN } 
<join_operation> ::=   
  { INNER JOIN
  | LEFT [ OUTER ] JOIN
  | RIGHT [ OUTER ] JOIN
  | FULL [ OUTER ] JOIN
  | CROSS JOIN
  | SEMI JOIN 
  | ANTI JOIN } 
﻿
JOIN type descriptions
JOIN Type
Description
INNER JOIN
Returns all rows that exist in both tables.
LEFT OUTER JOIN
Returns all rows from the first table regardless of whether there are matching rows in the second table. 
LEFT JOIN performs the same operation as LEFT OUTER JOIN.  
RIGHT OUTER JOIN
Returns all rows from the second table regardless of whether there are matching rows in the first table.
 RIGHT JOIN performs the same operation as RIGHT OUTER JOIN.  
FULL OUTER JOIN
Returns all matched and unmatched rows.
FULL JOIN performs the same operation as FULL OUTER JOIN.  
CROSS JOIN
Returns the Cartesian product of the joined tables. Each row in the first table is joined with all rows in the second table. The result set contains the number of rows in the first table multiplied by the number of rows in the second table.
SEMI JOIN
Returns the rows from the first table that have at least one match in the second table. No columns from the second table are available in the query.
ANTI JOIN
Returns the rows from the first table that have no matches in the second table. No columns from the second table are available in the query.
Examples
These examples join two tables: 
games - A table of video game titles and their genre_ids. Note that some games have a NULL value assigned to their genre_id.  
game_name
genre_id
Star Battle 6000
9
Dwarf Simulator
NULL
2002 Futbol
11
Space Attack! 
9
Fantasy Fool
8
Blasto the Squirrel
5
Trucker Quest
7
Block, Stack, and Cry!
6
Basketball Day
11
Pizza Mutant
1
Italian Plumber
5
Zombies!
1
Space Ninjas
12
Moon Mayor
10
Spreadsheet Hero
NULL
Zoom Tycoon
12
Porch Poacher
6
genre - A table of video game genres, which are identified by ids. 
genre_name
id
Racing
7
Puzzle
6
Adventure
2
Simulation
10
Shooter
9
Misc
4
Action
1
Sports
11
Role-Playing
8
Platform
5
Strategy
12
Fighting
3
INNER JOIN 
This example uses an INNER JOIN operation to capture only the rows that exist in both tables. Games with NULL values for their genre_id are eliminated from the result set.
SQL
SELECT game_name,
    genre_name
FROM video_games.game
    INNER JOIN video_games.genre ON game.genre_id = genre.id;
SELECT game_name,
    genre_name
FROM video_games.game
    INNER JOIN video_games.genre ON game.genre_id = genre.id;
﻿
Output
game_name
genre_name
Fantasy Fool
Role-Playing
Blasto the Squirrel
Platform
Italian Plumber
Platform
Porch Poacher
Puzzle
Space Ninjas
Strategy
Basketball Day
Sports
2002 Futbol
Sports
Moon Mayor
Simulation
Block, Stack, and Cry!
Puzzle
Zombies!
Action
Pizza Mutant
Action
Space Attack! 
Shooter
Trucker Quest
Racing
Zoom Tycoon
Strategy
LEFT OUTER JOIN
This example uses a LEFT OUTER JOIN operation to capture all rows from the left table (game), even if they have no matching row in the right table (genre). 
SQL
SELECT game_name,
    genre_name
FROM video_games.game
    LEFT OUTER JOIN video_games.genre ON game.genre_id = genre.id;
SELECT game_name,
    genre_name
FROM video_games.game
    LEFT OUTER JOIN video_games.genre ON game.genre_id = genre.id;
﻿
Output
game_name
genre_name
Fantasy Fool
Role-Playing
Space Ninjas
Strategy
Zoom Tycoon
Strategy
Zombies!
Action
Pizza Mutant
Action
Trucker Quest
Racing
Blasto the Squirrel
Platform
2002 Futbol
Sports
Basketball Day
Sports
Italian Plumber
Platform
Block, Stack, and Cry!
Puzzle
Star Battle 6000
Shooter
Dwarf Simulator
NULL
Moon Mayor
Simulation
Porch Poacher
Puzzle
Spreadsheet Hero
NULL
Space Attack! 
Shooter
RIGHT OUTER JOIN
This example uses a RIGHT OUTER JOIN operation to capture all rows from the right table (genre), even if they have no matching row in the left table (game). 
SQL
SELECT game_name,
    genre_name
FROM video_games.game
    RIGHT OUTER JOIN video_games.genre ON game.genre_id = genre.id;
SELECT game_name,
    genre_name
FROM video_games.game
    RIGHT OUTER JOIN video_games.genre ON game.genre_id = genre.id;
﻿
Output
game_name
genre_name
NULL
Adventure
Trucker Quest
Racing
Pizza Mutant
Action
Moon Mayor
Simulation
Fantasy Fool
Role-Playing
Space Attack! 
Shooter
NULL
Misc
Star Battle 6000
Shooter
Space Ninjas
Strategy
Porch Poacher
Puzzle
Block, Stack, and Cry!
Puzzle
Blasto the Squirrel
Platform
Italian Plumber
Platform
Zoom Tycoon
Strategy
NULL
Fighting
Zombies!
Action
2002 Futbol
Sports
Basketball Day
Sports
FULL OUTER JOIN
This example uses a FULL OUTER JOIN operation to capture all rows from both tables, even if they do not match. 
SQL
SELECT game_name,
    genre_name
FROM video_games.game
    FULL OUTER JOIN video_games.genre ON game.genre_id = genre.id;
SELECT game_name,
    genre_name
FROM video_games.game
    FULL OUTER JOIN video_games.genre ON game.genre_id = genre.id;
﻿
Output
game_name
genre_name
Basketball Day
Sports
2002 Futbol
Sports
Zombies!
Action
Blasto the Squirrel
Platform
Moon Mayor
Simulation
Trucker Quest
Racing
NULL
Misc
Star Battle 6000
Shooter
Space Attack! 
Shooter
Spreadsheet Hero
NULL
NULL
Adventure
NULL
Fighting
Italian Plumber
Platform
Pizza Mutant
Action
Dwarf Simulator
NULL
Fantasy Fool
Role-Playing
Block, Stack, and Cry!
Puzzle
Space Ninjas
Strategy
Zoom Tycoon
Strategy
Porch Poacher
Puzzle
CROSS JOIN
This example uses a CROSS JOIN operation to capture every possible combination of rows from both tables, regardless of whether they match. Note that unlike other JOIN operations, CROSS JOIN does not require an ON statement. 
SQL
SELECT game_name, 
  genre_name 
FROM video_games.genre 
  CROSS JOIN video_games.game;
SELECT game_name, 
  genre_name 
FROM video_games.genre 
  CROSS JOIN video_games.game;
﻿
Output
As the result set for this CROSS JOIN example is more than 200 rows, the results are abbreviated. 
genre_name
genre_id
Spreadsheet Hero
Role-Playing
Spreadsheet Hero
Misc
Spreadsheet Hero
Sports
Spreadsheet Hero
Platform
Spreadsheet Hero
Strategy
Spreadsheet Hero
Shooter
Spreadsheet Hero
Fighting
Spreadsheet Hero
Action
Spreadsheet Hero
Simulation
Spreadsheet Hero
Racing
Spreadsheet Hero
Puzzle
Spreadsheet Hero
Adventure
Space Attack! 
Role-Playing
Space Attack! 
Misc
Space Attack! 
Sports
Space Attack! 
Platform
Space Attack! 
Strategy
Space Attack! 
Shooter
Space Attack! 
Fighting
Space Attack! 
Action
Space Attack! 
Simulation
Space Attack! 
Racing
Space Attack! 
Puzzle
Space Attack! 
Adventure
...
...
SEMI JOIN
This example uses a SEMI JOIN operation to capture genre names that have at least one match in the games table. Rows are only included once, even if there are multiple matches. 
SQL
SELECT genre_name, 
  id 
FROM video_games.genre 
  SEMI JOIN video_games.game ON genre.id = game.genre_id;
SELECT genre_name, 
  id 
FROM video_games.genre 
  SEMI JOIN video_games.game ON genre.id = game.genre_id;
﻿
Output
genre_name
genre_id
Simulation
10
Shooter
9
Racing
7
Strategy
12
Puzzle
6
Platform
5
Action
1
Sports
11
Role-Playing
8
ANTI JOIN
This example uses a ANTI JOIN operation to capture any rows in the game table that do not match any rows in the genre table. 
SQL
SELECT game_name
FROM video_games.game 
  ANTI JOIN video_games.genre ON game.genre_id = genre.id;
SELECT game_name
FROM video_games.game 
  ANTI JOIN video_games.genre ON game.genre_id = genre.id;
﻿
Output
game_name
Spreadsheet Hero
Dwarf Simulator
WHERE
﻿Filters rows based on a specified condition. 
Syntax
SQL
<select_query> 
  WHERE column_name <filter_condition> filter_value
<select_query> 
  WHERE column_name <filter_condition> filter_value
﻿
Parameters
Parameter
Description
<select_query>
A valid SELECT query. 
column_name
A column used for grouping that is evaluated by the filter_condition. 
filter_value
A value used to evaluate the specified column_name. 
For more information, see the <filter_condition>﻿ section
<filter_condition>
A logical combination of predicates used to evaluate the referenced column_name based on the filter_value. 
Syntax
SQL
<filter_condition> ::= 
  { =
    | ==
    | <>
    | !=
    | [ NOT ] EQUALS
    | <
    | <=
    | >
    | >=
    | [ NOT ] IN
    | [ NOT ] LIKE
    | [ NOT ] SIMILAR TO
    | BETWEEN
    | FOR SOME
    | FOR ALL
    | IS [ NOT ] NULL
    | IS [ NOT ] DISTINCT FROM 
    }
<filter_condition> ::= 
  { =
    | ==
    | <>
    | !=
    | [ NOT ] EQUALS
    | <
    | <=
    | >
    | >=
    | [ NOT ] IN
    | [ NOT ] LIKE
    | [ NOT ] SIMILAR TO
    | BETWEEN
    | FOR SOME
    | FOR ALL
    | IS [ NOT ] NULL
    | IS [ NOT ] DISTINCT FROM 
    }
﻿
Definitions
Operator
Description
Example
=, ==, EQUALS
Equal to. 
Author = 'Alcott'
<>, !=
Not equal to
Dept <> 'Sales'
>
Greater than
Hire_Date > '2012-01-31'
<
Less than
Bonus < 50000.00
>=
Greater than or equal
Dependents >= 2
<=
Less than or equal
Rate <= 0.05
[ NOT ] LIKE
Begins with a character pattern to match. 
The pattern is enclosed in parentheses and can include the wildcard characters %, representing zero, one or multiple characters, and/or _ representing a single character. To have a literal % or _ that does not act as a wildcard on the right hand size of a LIKE or NOT LIKE expression, put a backslash before the character. 
Full_Name LIKE 'Will%'
[ NOT ] SIMILAR TO
For usage information, see the  SIMILAR TO Operator﻿ page. 
﻿
BETWEEN value1 AND value2
Requires two values. 
The filter evaluates to TRUE if the specified column is within the range between the two values. BETWEEN bounds are inclusive.  Values can be times, numbers, or strings. 
Sales BETWEEN 1000 AND 5000
FOR_SOME() 
Evaluates an array to determine if at least one value meets the filter criteria. 
In the example, the FOR_SOME() operator would return all rows of the column col_int_array where at least one value is greater than 10. 
For more information, see Array Filters﻿﻿
FOR_SOME(col_int_array) > 10
﻿
FOR_SOME(col_int_array) > 10
FOR_ALL()
Evaluates all values of an array to determine if all meet the filter criteria. 
In the example, the FOR_ALL() operator would return any rows of the column col_int_array where all values are greater than 10. 
For more information, see Array Filters﻿﻿
FOR_ALL(col_int_array) > 10
﻿
FOR_ALL(col_int_array) = 1
[ NOT ] IN
Equal to one of multiple possible values
DeptCode IN (101, 103, 209)
IS [ NOT ] NULL
Compare to null (missing data)
Address IS NOT NULL
IS [ NOT ] DISTINCT FROM
Is equal to value or both are nulls (missing data)
Debt IS NOT DISTINCT FROM Receivables
﻿Array Filters﻿
A filter expression can use the functions FOR_SOME() and FOR_ALL() to apply a predicate against all values of an input array. 
Array filter functions have the following rules. 
They can only evaluate array types. 
They can go on either the left or right side of a boolean comparison expression, but not both sides at the same time. 
They must directly use a boolean comparison operator.
They cannot use the SOME and ALL SQL keywords. 
Filter Behavior with Empty Arrays
Array filter functions have unique behavior when evaluating empty arrays. 
FOR_ALL() evaluates an empty array as TRUE. 
FOR_SOME() evaluates an empty array as FALSE.
If empty arrays must be evaluated for a different result, you can use the ARRAY_LENGTH function to specify a minimum array length. For example, this statement would evaluate an array as TRUE only if it is not empty and all values matched %ocient%:
SQL
ARRAY_LENGTH(array_col) > 0 AND FOR_ALL(array_col) LIKE '%ocient%
ARRAY_LENGTH(array_col) > 0 AND FOR_ALL(array_col) LIKE '%ocient%
﻿
For more information on array functions, see the Array Functions and Operators﻿ page. 
Filter Behavior with NULL rows
Filtering with array functions can have different outcomes depending on whether the array row is NULL or if the values contained in the array are NULL. 
If FOR_SOME() or FOR_ALL() evaluate a NULL row (the row itself is NULL, not that the array contains NULL values), then the result is always FALSE. 
To check an array for the presence of NULL values, you must use the IS NULL operator. For example: 
SQL
FOR_SOME(array_col) IS NULL
FOR_SOME(array_col) IS NULL
﻿
Array comparison operators, such as @>, <@, and && ignore NULL values. For details, see the Array Functions and Operators﻿ page.
GROUP BY
﻿Groups rows that have the same values into summary rows, based on a specified aggregate function.  
Syntax
SQL
SELECT <select_list_clause> FROM table_name 
  GROUP BY { column_name | expression | integer } [ , ... ]
    [ HAVING <filter_condition> ] 
SELECT <select_list_clause> FROM table_name 
  GROUP BY { column_name | expression | integer } [ , ... ]
    [ HAVING <filter_condition> ] 
﻿
Parameters
Parameter
Description
<select_list_clause>
A list of one or more columns for the SELECT query. 
To use GROUP BY to summarize the query result set, at least one of the columns in column_list must include an aggregate function, such as SUM(), COUNT(), or MAX(). 
For a list of supported functions, see the Aggregate Functions﻿ page. 
table_name
The table to use for the query. 
column_name
The name of one or more columns to use to group the result set. 
If you specify multiple columns, the result set is grouped by each unique combination of values from the columns.
expression
Any combination of literal values, column names, arithmetic expressions, parentheses, and function calls. 
integer
An integer representing the position of the columns referenced in the column_list. The first position starts at 1.   
<filter_condition>
A HAVING clause that filters the aggregated groups based on a specified condition. 
For more information, see the <filter_condition>﻿ section. 
Example
This example uses a movie database to calculate the total amount spent on movie production per year. 
SQL
SELECT YEAR(release_date),
    SUM(budget)
FROM movies
GROUP BY 1
ORDER BY 1 ASC;
SELECT YEAR(release_date),
    SUM(budget)
FROM movies
GROUP BY 1
ORDER BY 1 ASC;
﻿
Output
year(release_date)
sum(budget)
1968
10500000
1979
31500000
1981
18000000
1982
28000000
1992
14000000
1997
200000000
2003
264000000
2009
237000000
2010
350000000
2012
670000000
2013
350000000
2015
414000000
HAVING
﻿Filters aggregated rows based on a specified condition. 
HAVING operates in a GROUP BY statement by setting a filter for the rows to be aggregated and grouped. 
For information on using GROUP BY in a query statement, see GROUP BY﻿. 
Syntax
SQL
GROUP BY column_name HAVING <filter_condition>
GROUP BY column_name HAVING <filter_condition>
﻿
Parameters
Parameter
Description
column_name
A column used for grouping that is evaluated by the filter_condition. 
<filter_condition>
A logical combination of boolean predicates. 
For information on supported logical predicates, see <filter_condition>﻿﻿
Example
This example calculates the total amount spent on movie production per year. The HAVING clause filters out any rows that do not have a sum of at least $100 million.  
SQL
SELECT YEAR(release_date),
    SUM(budget)
FROM movies
GROUP BY 1
HAVING SUM(budget) > 100000000
ORDER BY 1 ASC;
SELECT YEAR(release_date),
    SUM(budget)
FROM movies
GROUP BY 1
HAVING SUM(budget) > 100000000
ORDER BY 1 ASC;
﻿
Output
year(release_date)
sum(budget)
1997
200000000
2003
264000000
2009
237000000
2010
350000000
2012
670000000
2013
350000000
2015
414000000
ORDER BY
Sorts the result set in ascending or descending order, based on one or more specified columns.  
If you specify multiple columns, they are sorted hierarchically from left to right.  
Syntax
SQL
ORDER BY { column_position | column_name } [ ASC | DESC ] 
  [ NULLS FIRST | NULLS LAST ] [ , ... ]
ORDER BY { column_position | column_name } [ ASC | DESC ] 
  [ NULLS FIRST | NULLS LAST ] [ , ... ]
﻿
Parameters
Parameter
Data Type
Description
column_position
Integer
The position of a column to be used for the ORDER BY sorting. 
Column positions start at 1. 
column_name
String
The name of a column to be used for the ORDER BY sorting. 
ASC | DESC
String
Optional. 
Specifies whether to sort the column in ascending (ASC) or descending (DESC) order. 
If unspecified, defaults to ASC.
NULLS FIRST | NULLS LAST
String
Optional. 
NULLS FIRST means null values go at the start of the result set. 
NULLS LAST means null values go at the end of the result set. 
﻿
If unspecified, defaults to NULLS FIRST.
Example
In this example, the ORDER BY statement orders the movies from newest to oldest.  
SQL
SELECT release_date, title FROM movies 
  ORDER BY release_date DESC; 
SELECT release_date, title FROM movies 
  ORDER BY release_date DESC; 
﻿
Output
release_date
title
2015-06-17
Minnows
2015-06-09
Triassic World
2015-04-01
Fury 7
2013-11-27
Frigid
2013-04-18
Iron Chef 3
2012-10-25
Spyman 16
2012-07-16
Superhero 23
2012-04-25
The Tentpole
2010-06-30
The Last Airman
2010-06-16
Merchandise Vehicle 5
2009-12-10
CGI Why
2003-12-01
Swords & Scabbards
2003-10-10
Billy the Killy
2003-07-09
Pirates of Palm Springs
1997-11-18
Titania
1992-08-07
Bang Bang Western
1982-06-25
Blade Walker
1981-06-12
Raiders of the Jungle Temple
1979-08-15
Apocalypse None
1968-04-10
Space Odyssey 6000
LIMIT
﻿Limits the number of returned rows from a query to a specified amount. 
The rows that are returned are nondeterministic unless you use an ORDER BY﻿ clause. 
Syntax
SQL
<select_query> LIMIT limit_number
<select_query> LIMIT limit_number
﻿
Parameters
Parameter
Description
<select_query>
A valid SELECT query 
limit_number
The number of rows to return from the query. This number must be a non-negative integer. 
Example
This example uses LIMIT to restrict the number of returned movie titles to only three. 
SQL
SELECT title FROM movies LIMIT 3; 
SELECT title FROM movies LIMIT 3; 
﻿
Output
SQL
Minnows
CGI Why
Billy the Killy
Minnows
CGI Why
Billy the Killy
﻿
OFFSET
﻿Skips a specified number of rows from the result set. 
The rows that are returned are nondeterministic unless you use an ORDER BY﻿ clause. 
Syntax
SQL
<select_query> OFFSET offset_number
<select_query> OFFSET offset_number
﻿
Parameters
Parameter
Description
<select_query>
A valid SELECT query. 
offset_number
The number of rows to skip when returning results from the query. This number must be a non-negative integer. 
Example
In this example, the ORDER BY statement orders the results chronologically. As a result, the OFFSET clause removes the oldest six movies from the result set. 
SQL
SELECT release_date,
    title
FROM movies
ORDER BY release_date OFFSET 6;
SELECT release_date,
    title
FROM movies
ORDER BY release_date OFFSET 6;
﻿
Output
release_date
title
2003-07-09
Pirates of Palm Springs
2003-10-10
Billy the Killy
2003-12-01
Swords & Scabbards
2009-12-10
CGI Why
2010-06-16
Merchandise Vehicle 5
2010-06-30
The Last Airman
2012-04-25
The Tentpole
2012-07-16
Superhero 23
2012-10-25
Spyman 16
2013-04-18
Iron Chef 3
2013-11-27
Frigid
2015-04-01
Fury 7
2015-06-09
Triassic World
2015-06-17
Minnows
INTERSECT
﻿Returns any rows that match between two separate SELECT queries. To use INTERSECT, the two queries must be compatible, meaning they must return the same number of columns and have similar data types.   
By default, the command eliminates duplicate rows unless the query includes the optional ALL keyword.  Using the DISTINCT keyword is the same as this default behavior. 
Syntax
SQL
<select_query1> INTERSECT [ ALL | DISTINCT ] <select_query2>
<select_query1> INTERSECT [ ALL | DISTINCT ] <select_query2>
﻿
Parameters
Parameter
Description
<select_query1>
A valid SELECT query. 
<select_query2>
A valid SELECT query. 
Example
This example has two SELECT queries with an INTERSECT statement. The full query retrieves movies that had a budget of at least $200 million, but also earned more than $1 billion in revenues. 
SQL
SELECT *
FROM movies
WHERE revenue > 1000000000
INTERSECT
SELECT *
FROM movies
WHERE budget > 20000000;
SELECT *
FROM movies
WHERE revenue > 1000000000
INTERSECT
SELECT *
FROM movies
WHERE budget > 20000000;
﻿
Output
movie_id
title
budget
popularity
release_date
revenue
runtime
movie_status
vote_average
vote_count
597
Titania
200000000
100.025899
1997-11-18
1845034188
194
Released
7.5
7562
19995
CGI Why
237000000
150.437577
2009-12-10
2787965087
162
Released
7.2
11800
49026
Superhero 23
250000000
112.31295
2012-07-16
1084939099
165
Released
7.6
9106
10193
Merchandise Vehicle 5
200000000
59.995418
2010-06-16
1066969703
103
Released
7.6
4597
211672
Minnows
74000000
875.581305
2015-06-17
1156730962
91
Released
6.4
4571
122
Swords & Scabbards
94000000
123.630332
2003-12-01
1118888979
201
Released
8.1
8064
37724
Spyman 16
200000000
93.004993
2012-10-25
1108561013
143
Released
6.9
7604
109445
Frigid
150000000
165.125366
2013-11-27
1274219009
102
Released
7.3
5295
168259
Furious 7
190000000
102.322217
2015-04-01
1506249360
137
Released
7.3
4176
68721
Iron Chef 3
200000000
77.68208
2013-04-18
1215439994
130
Released
6.8
8806
135397
Triassic World
150000000
418.708552
2015-06-09
1513528810
124
Released
6.5
8662
24428
The Tentpole
220000000
144.448633
2012-04-25
1519557910
143
Released
7.4
11776
EXCEPT
﻿The EXCEPT keyword returns the result set of a first SELECT query minus any matching rows from a second SELECT query.
EXCEPT requires the two queries to be compatible, meaning they must return the same number of columns and have similar data types. By default, the result set eliminates duplicate rows unless the query includes the optional ALL keyword.  Using the DISTINCT keyword is the same as this default behavior. 
The EXCEPT keyword can also be used to exclude specific columns from a SELECT * query. For information on that alternate usage, see the SELECT﻿ syntax and example.    
Syntax
SQL
<select_query1> EXCEPT [ ALL | DISTINCT ] <select_query2>
<select_query1> EXCEPT [ ALL | DISTINCT ] <select_query2>
﻿
Parameters
Parameter
Description
<select_query1>
A valid SELECT query that is compared to select_query2.
The results of an EXCEPT statement are the rows in select_query1 that do not match any row in select_query2.  
<select_query2>
A valid SELECT query that is compared to select_query1. 
Example
In this example, the first SELECT statement queries all rows in the movies database. The EXCEPT clause and the second SELECT statement eliminate from the results any rows with movies that had a budget greater than 20000000.
SQL
SELECT *
FROM movies
EXCEPT
SELECT *
FROM movies
WHERE budget > 20000000;
SELECT *
FROM movies
EXCEPT
SELECT *
FROM movies
WHERE budget > 20000000;
﻿
Output
title
budget
popularity
release_date
revenue
runtime
movie_status
vote_average
vote_count
Bang Bang Western
14000000
37.380435
1992-08-07
159157447
131
Released
7.7
1113
Space Odyssey 6000
10500000
86.201184
1968-04-10
68700000
149
Released
7.9
2998
Raiders of the Jungle Temple
18000000
68.159596
1981-06-12
389925971
115
Released
7.7
3854
﻿UNION﻿
﻿Returns the combined result set of two or more SELECT queries. 
By default, UNION eliminates duplicate rows from the result set unless you specify the ALL keyword.  Using the DISTINCT keyword is the same as this default behavior. 
Syntax
SQL
<select_query1> UNION [ ALL | DISTINCT ] <select_query2> [ ... ] 
<select_query1> UNION [ ALL | DISTINCT ] <select_query2> [ ... ] 
﻿
Parameters
Parameter
Data Type
Description
select_query1
String
A valid SELECT query that is combined with select_query2.
select_query2

Parameter	Description
<cte_name>	A name for the common table expression populated with the result set from the sub_query statement.
<cte_column>	Optional. This is a list of column names for common table expression populated with the sub_query result set. If no column names are provided, the column names are the same as the table referenced in the sub_query statement.
<sub_query>	An auxiliary query that collects data for a common table expression. sub_query can use regular query commands including ORDER BY, LIMIT, OFFSET, UNION, INTERSECT, EXCEPT, WHERE, GROUP BY, and HAVING. See the respective command reference sections for usage rules.
<select_query>	The main SELECT query. Note that the FROM statement in the main SELECT query must also include the cte_name if the query uses its values.

title	budget	revenue
CGI Why	237000000	2787965087
Titania	200000000	1845034188
Merchandise Vehicle 5	200000000	1066969703
The Tentpole	220000000	1519557910
Pirates of Palm Springs	140000000	655011224
Spyman 16	200000000	1108561013
Frigid	150000000	1274219009
Fury 7	190000000	1506249360
Superhero 23	250000000	1084939099
Triassic World	150000000	1513528810
Iron Chef 3	200000000	1215439994
The Last Airman	150000000	318502923

Parameter	Description
ALL	SELECT and SELECT ALL are both the same. Returns all valid data rows from the database that meets the criteria of your query.
DISTINCT	SELECT DISTINCT returns only unique rows that do not match other rows based on the criteria of your query.
*	Returns all columns from the specified tables in the result set for the query.
EXCEPT	When used with *, EXCEPT allows specific columns to be excluded from the query results.
column_name	The name of one or more columns from the specified table that you want to exclude (using EXCEPT) from your query results.

Parameter	Description
column_name	The name of one or more columns from the specified table that you want to include in your query results.
expression	One or more expressions that you want to include in your query results. Expressions can be any combination of literal values, column names, arithmetic expressions, parentheses, and function calls.
new_name	An identifier for the results of the select_list_entry of the query.

movie_id	title	budget	popularity	release_date	revenue	runtime	movie_status	vote_average	vote_count
211672	Minnows	74000000	875.581305	2015-06-17	1156730962	91	Released	6.40	4571
24	Billy the Killy	30000000	79.754966	2003-10-10	180949000	111	Released	7.70	4949
19995	CGI Why	237000000	150.437577	2009-12-10	2787965087	162	Released	7.20	11800
37724	Spyman 16	200000000	93.004993	2012-10-25	1108561013	143	Released	6.90	7604
24428	The Tentpole	220000000	144.448633	2012-04-25	1519557910	143	Released	7.40	11776

title	budget	release_date	revenue
Swords & Scabbards	94000000	2003-12-01	1118888979
Billy the Killy	30000000	2003-10-10	180949000
Merchandise Vehicle 5	200000000	2010-06-16	1066969703
CGI Why	237000000	2009-12-10	2787965087
Space Odyssey 6000	10500000	1968-04-10	68700000

Parameter	Description
<select_clause>	A query statement using the SELECT command. For more information, see the SELECT section.
table_name	The names of one or more tables, views, or a table-valued functions that you want to query. If you specify multiple tables in the FROM list, separated by commas, the effect is the same as if you explicitly perform a CROSS JOIN on all of those sources. For more information and syntax for using table-valued functions, see the Table-Valued Functions section.
<sub_query>	One or more subqueries using the SELECT command. A subquery generates a table from which the select_clause query references data. Each subquery must be enclosed in parentheses with an optional correlation name. For more information, see the SELECT section.
<join_clause>	A JOIN clause used to retrieve data from two or more tables for your query. For more information, see the JOIN section.

Parameter	Description
<select_query>	A valid SELECT query. For more information, see SELECT.
<table_reference1>	Either a table name, a full SELECT in parentheses, or the output of a table-valued function. table_reference1 takes precedence for returning rows for LEFT OUTER JOIN, SEMI JOIN, and ANTI JOIN. For specific rules, see Types of Join Operations.
<table_reference2>	Either a table name, a full SELECT in parentheses, or the output of a table-valued function. table_reference2 takes precedence for returning rows for RIGHT OUTER JOIN. For specific rules, see Types of Join Operations.
table1_column	A column from either table_reference The JOIN operation uses this column to match rows with table2_column to combine data for the result set.
<boolean_operator>	Join conditions can use any boolean expression, including =, !=, <, >, =>, and <=
table2_column	A column from the table_reference not used for table1_column The JOIN operation uses this column to match rows based on the table1_column to combine data for the result set.

JOIN Type	Description
INNER JOIN	Returns all rows that exist in both tables.
LEFT OUTER JOIN	Returns all rows from the first table regardless of whether there are matching rows in the second table. LEFT JOIN performs the same operation as LEFT OUTER JOIN.
RIGHT OUTER JOIN	Returns all rows from the second table regardless of whether there are matching rows in the first table. RIGHT JOIN performs the same operation as RIGHT OUTER JOIN.
FULL OUTER JOIN	Returns all matched and unmatched rows. FULL JOIN performs the same operation as FULL OUTER JOIN.
CROSS JOIN	Returns the Cartesian product of the joined tables. Each row in the first table is joined with all rows in the second table. The result set contains the number of rows in the first table multiplied by the number of rows in the second table.
SEMI JOIN	Returns the rows from the first table that have at least one match in the second table. No columns from the second table are available in the query.
ANTI JOIN	Returns the rows from the first table that have no matches in the second table. No columns from the second table are available in the query.

game_name	genre_id
Star Battle 6000	9
Dwarf Simulator	NULL
2002 Futbol	11
Space Attack!	9
Fantasy Fool	8
Blasto the Squirrel	5
Trucker Quest	7
Block, Stack, and Cry!	6
Basketball Day	11
Pizza Mutant	1
Italian Plumber	5
Zombies!	1
Space Ninjas	12
Moon Mayor	10
Spreadsheet Hero	NULL
Zoom Tycoon	12
Porch Poacher	6

genre_name	id
Racing	7
Puzzle	6
Adventure	2
Simulation	10
Shooter	9
Misc	4
Action	1
Sports	11
Role-Playing	8
Platform	5
Strategy	12
Fighting	3

Parameter	Description
<select_query>	A valid SELECT query.
column_name	A column used for grouping that is evaluated by the filter_condition.
filter_value	A value used to evaluate the specified column_name. For more information, see the <filter_condition> section

Operator	Description	Example
=, ==, EQUALS	Equal to.	Author = 'Alcott'
<>, !=	Not equal to	Dept <> 'Sales'
>	Greater than	Hire_Date > '2012-01-31'
<	Less than	Bonus < 50000.00
>=	Greater than or equal	Dependents >= 2
<=	Less than or equal	Rate <= 0.05
[ NOT ] LIKE	Begins with a character pattern to match. The pattern is enclosed in parentheses and can include the wildcard characters %, representing zero, one or multiple characters, and/or _ representing a single character. To have a literal % or _ that does not act as a wildcard on the right hand size of a LIKE or NOT LIKE expression, put a backslash before the character.	Full_Name LIKE 'Will%'
[ NOT ] SIMILAR TO	For usage information, see the SIMILAR TO Operator page.
BETWEEN value1 AND value2	Requires two values. The filter evaluates to TRUE if the specified column is within the range between the two values. BETWEEN bounds are inclusive. Values can be times, numbers, or strings.	Sales BETWEEN 1000 AND 5000
FOR_SOME()	Evaluates an array to determine if at least one value meets the filter criteria. In the example, the FOR_SOME() operator would return all rows of the column col_int_array where at least one value is greater than 10. For more information, see Array Filters	FOR_SOME(col_int_array) > 10 FOR_SOME(col_int_array) > 10
FOR_ALL()	Evaluates all values of an array to determine if all meet the filter criteria. In the example, the FOR_ALL() operator would return any rows of the column col_int_array where all values are greater than 10. For more information, see Array Filters	FOR_ALL(col_int_array) > 10 FOR_ALL(col_int_array) = 1
[ NOT ] IN	Equal to one of multiple possible values	DeptCode IN (101, 103, 209)
IS [ NOT ] NULL	Compare to null (missing data)	Address IS NOT NULL
IS [ NOT ] DISTINCT FROM	Is equal to value or both are nulls (missing data)	Debt IS NOT DISTINCT FROM Receivables

Parameter	Description
<select_list_clause>	A list of one or more columns for the SELECT query. To use GROUP BY to summarize the query result set, at least one of the columns in column_list must include an aggregate function, such as SUM(), COUNT(), or MAX(). For a list of supported functions, see the Aggregate Functions page.
table_name	The table to use for the query.
column_name	The name of one or more columns to use to group the result set. If you specify multiple columns, the result set is grouped by each unique combination of values from the columns.
expression	Any combination of literal values, column names, arithmetic expressions, parentheses, and function calls.
integer	An integer representing the position of the columns referenced in the column_list. The first position starts at 1.
<filter_condition>	A HAVING clause that filters the aggregated groups based on a specified condition. For more information, see the <filter_condition> section.

year(release_date)	sum(budget)
1968	10500000
1979	31500000
1981	18000000
1982	28000000
1992	14000000
1997	200000000
2003	264000000
2009	237000000
2010	350000000
2012	670000000
2013	350000000
2015	414000000

Parameter	Data Type	Description
column_position	Integer	The position of a column to be used for the ORDER BY sorting. Column positions start at 1.
column_name	String	The name of a column to be used for the ORDER BY sorting.
ASC \| DESC	String	Optional. Specifies whether to sort the column in ascending (ASC) or descending (DESC) order. If unspecified, defaults to ASC.
NULLS FIRST \| NULLS LAST	String	Optional. NULLS FIRST means null values go at the start of the result set. NULLS LAST means null values go at the end of the result set. If unspecified, defaults to NULLS FIRST.

release_date	title
2015-06-17	Minnows
2015-06-09	Triassic World
2015-04-01	Fury 7
2013-11-27	Frigid
2013-04-18	Iron Chef 3
2012-10-25	Spyman 16
2012-07-16	Superhero 23
2012-04-25	The Tentpole
2010-06-30	The Last Airman
2010-06-16	Merchandise Vehicle 5
2009-12-10	CGI Why
2003-12-01	Swords & Scabbards
2003-10-10	Billy the Killy
2003-07-09	Pirates of Palm Springs
1997-11-18	Titania
1992-08-07	Bang Bang Western
1982-06-25	Blade Walker
1981-06-12	Raiders of the Jungle Temple
1979-08-15	Apocalypse None
1968-04-10	Space Odyssey 6000

Parameter	Description
<select_query>	A valid SELECT query
limit_number	The number of rows to return from the query. This number must be a non-negative integer.

Parameter	Description
<select_query>	A valid SELECT query.
offset_number	The number of rows to skip when returning results from the query. This number must be a non-negative integer.

Parameter	Description
<select_query1>	A valid SELECT query.
<select_query2>	A valid SELECT query.

General SQL Syntax

﻿Ocient SQL Syntax﻿

Querying Command Reference

WITH

SELECT

<select_list_entry>

<from_clause>

Using SELECT *

Using SELECT * EXCEPT

﻿

FROM

JOIN

Types of Join Operations ( <join_operation> )

INNER JOIN

LEFT OUTER JOIN

RIGHT OUTER JOIN

FULL OUTER JOIN

CROSS JOIN

SEMI JOIN

ANTI JOIN

WHERE

<filter_condition>

﻿Array Filters﻿

GROUP BY

HAVING

ORDER BY

LIMIT

OFFSET

INTERSECT

EXCEPT

﻿UNION﻿

Ocient SQL Syntax

Array Filters

UNION

movie_id	title	budget	popularity	release_date	revenue	runtime	movie_status	vote_average	vote_count
597	Titania	200000000	100.025899	1997-11-18	1845034188	194	Released	7.5	7562
19995	CGI Why	237000000	150.437577	2009-12-10	2787965087	162	Released	7.2	11800
49026	Superhero 23	250000000	112.31295	2012-07-16	1084939099	165	Released	7.6	9106
10193	Merchandise Vehicle 5	200000000	59.995418	2010-06-16	1066969703	103	Released	7.6	4597
211672	Minnows	74000000	875.581305	2015-06-17	1156730962	91	Released	6.4	4571
122	Swords & Scabbards	94000000	123.630332	2003-12-01	1118888979	201	Released	8.1	8064
37724	Spyman 16	200000000	93.004993	2012-10-25	1108561013	143	Released	6.9	7604
109445	Frigid	150000000	165.125366	2013-11-27	1274219009	102	Released	7.3	5295
168259	Furious 7	190000000	102.322217	2015-04-01	1506249360	137	Released	7.3	4176
68721	Iron Chef 3	200000000	77.68208	2013-04-18	1215439994	130	Released	6.8	8806
135397	Triassic World	150000000	418.708552	2015-06-09	1513528810	124	Released	6.5	8662
24428	The Tentpole	220000000	144.448633	2012-04-25	1519557910	143	Released	7.4	11776

Parameter	Description
<select_query1>	A valid SELECT query that is compared to select_query2. The results of an EXCEPT statement are the rows in select_query1 that do not match any row in select_query2.
<select_query2>	A valid SELECT query that is compared to select_query1.

title	budget	popularity	release_date	revenue	runtime	movie_status	vote_average	vote_count
Bang Bang Western	14000000	37.380435	1992-08-07	159157447	131	Released	7.7	1113
Space Odyssey 6000	10500000	86.201184	1968-04-10	68700000	149	Released	7.9	2998
Raiders of the Jungle Temple	18000000	68.159596	1981-06-12	389925971	115	Released	7.7	3854

Parameter	Data Type	Description
select_query1	String	A valid SELECT query that is combined with select_query2.
select_query2