This section is not intended to be a course in the SQL language. Hence, the focus will be put on the minimal set of differences that you must know in order to write queries properly.

Show tables and columns

• show tables

• show columns from <table>

 Example

show tables

Run the statement above to list all the tables available in the database model

And

show columns from <table>

to display the column and their metadata of a given table:

Strings vs aliases

Like in the rest of the database implementations a string is enclosed between single quotes.

On the other hand aliases of column and table names must be surrounded by accent characters.

 Example

SELECT
 'This is a string' as `Column Alias` 
 FROM 
  DUAL `Table alias`

Table, column and function names are case sensitive

• Use Uppercase names

 Example

select * from PROJECT

will work.

Whereas

select * from project

will not.

No schema names

Many database vendors group their tables into schemas (i.e.: PUBLIC). SQL Cloud does not support them.

We use the following convention for the table names:

• No prefix: Jira Work

• SF_ prefix: Jira Software

• SM_ prefix: Jira Service Manager

Aggregation conditions do not support aliases

Please pay attention to the having condition: count(*) > 2

SELECT `i`.`KEY`as `Issue`, count(*) as `Num. comments`
FROM ISSUE `i`
LEFT JOIN ISSUECOMMENT `ic` ON `ic`.`ISSUEID` = `i`.`ID`
WHERE `i`.`JQL` = 'PROJECT = TALH'
GROUP BY `i`.`KEY`
HAVING count(*) > 2

Works!

In this query the condition has been modified to uses column aliases: `Num. comments` > 2

SELECT `i`.`KEY`as `Issue`, count(*) as `Num. comments`
FROM ISSUE `i`
LEFT JOIN ISSUECOMMENT `ic` ON `ic`.`ISSUEID` = `i`.`ID`
WHERE `i`.`JQL` = 'PROJECT = TALH'
GROUP BY `i`.`KEY`
HAVING `Num. comments` > 2

Not works!