SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
S-30
Examples—SELECT
+> WHERE O.PARTNUM = P.PARTNUM AND ORDERNUM IN
+> (SELECT ORDERNUM FROM ORDERS O, CUSTOMER C
+> WHERE O.CUSTNUM = C.CUSTNUM AND STATE = "CALIFORNIA")
+> GROUP BY ORDERNUM;
>> VOLUME $VOL1.SALES;
>> SELECT ORDERNUM, SUM (QTY_ORDERED * PRICE)
+> FROM PARTS P, ODETAIL O
+> WHERE O.PARTNUM = P.PARTNUM AND ORDERNUM IN
+> (SELECT ORDERNUM FROM ORDERS WHERE CUSTNUM IN
+> (SELECT CUSTNUM FROM CUSTOMER
+> WHERE STATE = "CALIFORNIA" ) )
+> GROUP BY ORDERNUM;

This SQLCI example selects the value in the AUDIT column of the FILES catalog
table in the PERSNL catalog. By displaying this column, you can see whether a
table is defined as audited (Y) or nonaudited (N).
>> SELECT AUDIT FROM PERSNL.FILES
+> WHERE FILENAME = "\SYS1.$VOL1.PERSNL.JOB";
AUDIT
-----
Y
--- 1 row(s) selected.

This SQLCI example uses a table, T, that has columns C (of data type
CHARACTER) and I (of data type INTEGER). There are n possible values of C,
any of which can occur multiple times, but the values of I are unique. The query
returns the percent distribution of any value of C across the entire table.
The SQLCI query and the result:
>> --Print the percents
>> SELECT Y.C, (100.0*COUNT(*))/(COUNT(DISTINCT X.I) *
+> COUNT(DISTINCT X.I))
+> FROM T X, T Y
+> GROUP BY Y.C;
C (EXPR)
-- ---------------------
N1 50.0
N2 16.6
Sample Table T:
CI
-- -
N1 1
N1 4
N1 6
N2 2
N3 3
N3 5