Neoview SQL Reference Manual (R2.5)

(last_name > 'MOSS') OR
(last_name = 'MOSS' AND first_name > 'DUNCAN')
• Compare two datetime values START_DATE and the result of the CURRENT_DATE function:
START_DATE < CURRENT_DATE
• Compare two datetime values START_DATE and SHIP_TIMESTAMP:
CAST (start_date AS TIMESTAMP) < ship_timestamp
• Compare two INTERVAL values:
JOB1_TIME < JOB2_TIME
Suppose that JOB1_TIME, defined as INTERVAL DAY TO MINUTE, is 2 days 3 hours, and
JOB2_TIME, defined as INTERVAL DAY TO HOUR, is 3 days.
To evaluate the predicate, Neoview SQL converts the two INTERVAL values to MINUTE.
The comparison predicate is true.
• The next examples contain a subquery in a comparison predicate. Each subquery operates
on a separate logical copy of the EMPLOYEE table.
The processing sequence is outer to inner. A row selected by an outer query allows an inner
query to be evaluated, and a single value is returned. The next inner query is evaluated
when it receives a value from its outer query.
Find all employees whose salary is greater than the maximum salary of employees in
department 1500:
SELECT first_name, last_name, deptnum, salary
FROM persnl.employee
WHERE salary > (SELECT MAX (salary)
FROM persnl.employee
WHERE deptnum = 1500);
FIRST_NAME LAST_NAME DEPTNUM SALARY
--------------- -------------------- ------- -----------
ROGER GREEN 9000 175500.00
KATHRYN HALL 4000 96000.00
RACHEL MCKAY 4000 118000.00
THOMAS RUDLOFF 2000 138000.40
JANE RAYMOND 3000 136000.00
JERRY HOWARD 1000 137000.10
--- 6 row(s) selected.
Find all employees from other departments whose salary is less than the minimum salary
of employees (not in department 1500) that have a salary greater than the average salary for
department 1500:
SELECT first_name, last_name, deptnum, salary
FROM persnl.employee
WHERE deptnum <> 1500 AND
salary < (SELECT MIN (salary)
FROM persnl.employee
WHERE deptnum <> 1500 AND
salary > (SELECT AVG (salary)
FROM persnl.employee
WHERE deptnum = 1500));
FIRST_NAME LAST_NAME DEPTNUM SALARY
--------------- -------------------- ------- -----------
JESSICA CRINER 3500 39500.00
ALAN TERRY 3000 39500.00
DINAH CLARK 9000 37000.00
BILL WINN 2000 32000.00
MIRIAM KING 2500 18000.00
290 SQL Language Elements