Neoview SQL Reference Manual (R2.4 SP2)

FROM sales.parts P, sales.odetail OD
WHERE OD.partnum = P.partnum AND OD.ordernum IN
(SELECT O.ordernum
FROM sales.orders O
WHERE custnum IN
(SELECT custnum
FROM sales.customer
WHERE state = 'CALIFORNIA'))
GROUP BY OD.ordernum;
ORDERNUM (EXPR)
---------- ---------------------
200490 1030.00
300350 71025.00
300380 28560.00
--- 3 row(s) selected.
The price for the total quantity ordered is computed for each order number.
• Show employees, their salaries, and the percentage of the total payroll that their salaries
represent. Note the subquery as part of the expression in the select list:
SELECT empnum, first_name, last_name, salary,
CAST(salary * 100 / (SELECT SUM(salary) FROM persnl.employee)
AS NUMERIC(4,2))
FROM persnl.employee
ORDER BY salary, empnum;
Employee/Number First Name Last Name salary (EXPR)
--------------- --------------- -------------------- ----------- -------
209 SUSAN CHAPMAN 17000.00 .61
235 MIRIAM KING 18000.00 .65
224 MARIA JOSEF 18000.10 .65
...
23 JERRY HOWARD 137000.10 4.94
32 THOMAS RUDLOFF 138000.40 4.98
1 ROGER GREEN 175500.00 6.33
...
--- 62 row(s) selected.
• Examples of using expressions in the GROUP BY clause:
SELECT a+1 FROM t GROUP BY a+1;
SELECT cast(a AS int) FROM t GROUP BY cast(a AS int);
SELECT a+1 FROM t GROUP BY 1;
• Examples of unsupported expressions in the GROUP BY clause:
SELECT sum(a) FROM t GROUP BY sum(a);
SELECT (SELECT a FROM t1) FROM t GROUP BY (SELECT a FROM t1);
SELECT a+1 FROM t GROUP BY 1+a;
Examples for Embedded INSERT
• Table identity_table is created with an identity column. As rows are inserted into
identity_table, the customer obtains the values of the identity column.
CREATE TABLE identity_table
(a LARGEINT GENERATED BY DEFAULT AS IDENTITY
NOT NULL,
b INT UNSIGNED NOT NULL,
c INT UNSIGNED,
primary key(a));
• Inserts new rows into the identity_table table and retrieves IDENTITY column values
using the SELECT statement syntax.
182 SQL Statements