| Query
|
An SQL statement used to query the database. The query can be a simple query or a complex query. A complex query has nested SQL statements. Prepared SQL queries can be constructed by using substitution variables (or substitution parameters) of the form
?<fieldname> in the query statement. For example
select * from classroom where building like ?building;
Each substitution variable identifies an input parameter whose mapped value will be substituted into the substitution variable at runtime. The substitution variable can be reused for the same input parameter elsewhere in the query. Input parameters used in the WHERE clause, and output parameters used in the SELECT clause, and their corresponding type information is automatically fetched from the database using the selected connection for the entered query. Input and output fields in the
Input and
Output tabs of the activity are also automatically generated.
Note: Be sure to include the semicolon (;) at the end of the query. This activity expects a query to end with a semicolon to indicate the end of the query. A missing semicolon at the end of the query results in the query hanging. Typically, semi-colon is entered after the query has been entered in full, not before to prevent unnecessary query validation. When the semi-colon is entered the query field treats the query as complete. Upon successful processing of the query, the table field at the bottom of the query entry field is populated with column metadata information
Following are some examples of simple and complex queries:
-
Simple query example:
SELECT * FROM classroom;
For the above query, the output fields are generated from the table user's column information.
SELECT name, dept_name, tot_cred
FROM university.student
WHERE dept_name = ?dept_name and tot_cred >= ?tot_cred order by tot_cred;
For the above query, output fields are generated for
name,
dept_name and
tot_cred and input fields are generated for
dept_name and
tot_cred. Also the mapped values for the fields
dept_name and
tot_cred are substituted into the substitution variables
?dept_name
and
?tot_cred at runtime.
-
Nested query example:
SELECT firstname, lastname, total_quantity
FROM (SELECT buyerid, sum(qtysold) total_quantity
FROM sales
GROUP BY buyerid
ORDER BY total_quantity desc limit 10) Q, users
WHERE Q.buyerid = userid
ORDER BY Q.total_quantity desc;
|
