Built-in functions
WHERE EXISTS
(SELECT * FROM Flights WHERE dest_airport = 'SFO'
AND orig_airport = 'GRU')
-- a subquery used with IN
SELECT flight_id, segment_number
FROM Flights
WHERE flight_id IN
(SELECT flight_ID
FROM Flights WHERE orig_airport = 'SFO'
OR dest_airport = 'SCL')
-- a subquery with ORDER BY and FETCH FIRST clauses
SELECT flight_id, segment_number
FROM Flights
WHERE flight_id IN
(SELECT flight_ID
FROM Flights WHERE orig_airport = 'SFO'
OR dest_airport = 'SCL' ORDER BY flight_id FETCH FIRST 12 ROWS ONLY)
-- a subquery used with a quantified comparison
SELECT NAME, COMM
FROM STAFF
WHERE COMM >
(SELECT AVG(BONUS + 800)
FROM EMPLOYEE
WHERE COMM < 5000)
executes some operation. Built-in functions use keywords or special built-in operators.
Built-ins are SQL92Identifiers and are case-insensitive. Note that escaped functions like
TIMESTAMPADD and TIMESTAMPDIFF are only accessible using the JDBC escape
function syntax, and can be found in