SELECT c.cust_id, ANY_VALUE(cust_last_name), SUM(amount_sold)
FROM customers c, sales s
WHERE s.cust_id = c.cust_id
GROUP BY c.cust_id;
CUST_ID ANY_VALUE(CUST_LAST_NAME) SUM(AMOUNT_SOLD)
------- -------------------------- ----------------
6950 Sandburg 78
17920 Oliver 3201
66800 Case 2024
37280 Edwards 2256
109850 Lindegreen 757
3910 Oddell 185
84700 Marker 164.4
26380 Remler 118
11600 Oppy 158
23030 Rothrock 533
42780 Zanis 182
...
630 rows selected.
SELECT department_id, job_id,
APPROX_COUNT(*)
FROM employees
GROUP BY department_id, job_id
HAVING
APPROX_RANK (
PARTITION BY department_id
ORDER BY APPROX_COUNT(*)
DESC ) <= 10;
SELECT APPROX_COUNT_DISTINCT(manager_id) AS "Active Managers"
FROM employees;
Active Managers
---------------
18
SELECT prod_id, APPROX_COUNT_DISTINCT(cust_id) AS "Number of Customers"
FROM sales
GROUP BY prod_id
ORDER BY prod_id;
PROD_ID Number of Customers
---------- -------------------
13 2516
14 2030
15 2105
16 2367
17 2093
18 2975
19 2630
20 3791
. . .
CREATE MATERIALIZED VIEW daily_prod_count_mv AS
SELECT t.calendar_year year,
t.calendar_month_number month,
t.day_number_in_month day,
APPROX_COUNT_DISTINCT_DETAIL(s.prod_id) daily_detail
FROM times t, sales s
WHERE t.time_id = s.time_id
GROUP BY t.calendar_year, t.calendar_month_number, t.day_number_in_month;
CREATE MATERIALIZED VIEW monthly_prod_count_mv AS
SELECT year,
month,
APPROX_COUNT_DISTINCT_AGG(daily_detail) monthly_detail
FROM daily_prod_count_mv
GROUP BY year, month;
CREATE MATERIALIZED VIEW annual_prod_count_mv AS
SELECT year,
APPROX_COUNT_DISTINCT_AGG(daily_detail) annual_detail
FROM daily_prod_count_mv
GROUP BY year;
SELECT year,
month,
day,
TO_APPROX_COUNT_DISTINCT(daily_detail) "NUM PRODUCTS"
FROM daily_prod_count_mv
ORDER BY year, month, day;
YEAR MONTH DAY NUM PRODUCTS
---------- ---------- ---------- ------------
1998 1 1 24
1998 1 2 25
1998 1 3 11
1998 1 4 34
1998 1 5 10
1998 1 6 8
1998 1 7 37
1998 1 8 26
1998 1 9 25
1998 1 10 38
. . .
SELECT year,
month,
TO_APPROX_COUNT_DISTINCT(monthly_detail) "NUM PRODUCTS"
FROM monthly_prod_count_mv
ORDER BY year, month;
YEAR MONTH NUM PRODUCTS
---------- ---------- ------------
1998 1 57
1998 2 56
1998 3 55
1998 4 49
1998 5 49
1998 6 48
1998 7 54
1998 8 56
1998 9 55
1998 10 57
. . .
SELECT year,
TO_APPROX_COUNT_DISTINCT(annual_detail) "NUM PRODUCTS"
FROM annual_prod_count_mv
ORDER BY year;
YEAR NUM PRODUCTS
---------- ------------
1998 60
1999 72
2000 72
2001 71
SELECT department_id "Department",
APPROX_MEDIAN(salary DETERMINISTIC) "Median Salary"
FROM employees
GROUP BY department_id
ORDER BY department_id;
Department Median Salary
---------- -------------
10 4400
20 6000
30 2765
40 6500
50 3100
60 4800
70 10000
80 9003
90 17000
100 7739
110 8300
7000
SELECT department_id "Department",
APPROX_MEDIAN(salary DETERMINISTIC, 'ERROR_RATE') "Error Rate"
FROM employees
GROUP BY department_id
ORDER BY department_id;
Department Error Rate
---------- ----------
10 .002718282
20 .021746255
30 .021746255
40 .002718282
50 .019027973
60 .019027973
70 .002718282
80 .021746255
90 .021746255
100 .019027973
110 .019027973
.002718282
SELECT department_id "Department",
APPROX_MEDIAN(salary DETERMINISTIC, 'CONFIDENCE') "Confidence Level"
FROM employees
GROUP BY department_id
ORDER BY department_id;
Department Confidence Level
---------- ----------------
10 .997281718
20 .999660215
30 .999660215
40 .997281718
50 .999611674
60 .999611674
70 .997281718
80 .999660215
90 .999660215
100 .999611674
110 .999611674
.997281718
SELECT department_id "Department",
APPROX_MEDIAN(hire_date) "Median Hire Date"
FROM employees
GROUP BY department_id
ORDER BY department_id;
Department Median Hire Date
---------- ----------------
10 17-SEP-03
20 17-FEB-04
30 24-JUL-05
40 07-JUN-02
50 15-MAR-06
60 05-FEB-06
70 07-JUN-02
80 23-MAR-06
90 17-JUN-03
100 28-SEP-05
110 07-JUN-02
24-MAY-07
SELECT department_id "Department",
APPROX_PERCENTILE(0.25 DETERMINISTIC)
WITHIN GROUP (ORDER BY salary ASC) "25th Percentile Salary",
APPROX_PERCENTILE(0.50 DETERMINISTIC)
WITHIN GROUP (ORDER BY salary ASC) "50th Percentile Salary",
APPROX_PERCENTILE(0.75 DETERMINISTIC)
WITHIN GROUP (ORDER BY salary ASC) "75th Percentile Salary"
FROM employees
GROUP BY department_id
ORDER BY department_id;
Department 25th Percentile Salary 50th Percentile Salary 75th Percentile Salary
---------- ---------------------- ---------------------- ----------------------
10 4400 4400 4400
20 6000 6000 13000
30 2633 2765 3100
40 6500 6500 6500
50 2600 3100 3599
60 4800 4800 6000
70 10000 10000 10000
80 7400 9003 10291
90 17000 17000 24000
100 7698 7739 8976
110 8300 8300 12006
7000 7000 7000
SELECT department_id "Department",
APPROX_PERCENTILE(0.25 DETERMINISTIC, 'ERROR_RATE')
WITHIN GROUP (ORDER BY salary ASC) "Error Rate"
FROM employees
GROUP BY department_id
ORDER BY department_id;
Department Error Rate
---------- ----------
10 .002718282
20 .021746255
30 .021746255
40 .002718282
50 .019027973
60 .019027973
70 .002718282
80 .021746255
90 .021746255
100 .019027973
110 .019027973
.002718282
SELECT department_id "Department",
APPROX_PERCENTILE(0.25 DETERMINISTIC, 'CONFIDENCE')
WITHIN GROUP (ORDER BY salary ASC) "Confidence"
FROM employees
GROUP BY department_id
ORDER BY department_id;
Department Confidence
---------- ----------
10 .997281718
20 .999660215
30 .999660215
40 .997281718
50 .999611674
60 .999611674
70 .997281718
80 .999660215
90 .999660215
100 .999611674
110 .999611674
.997281718
SELECT department_id "Department",
APPROX_PERCENTILE(0.25)
WITHIN GROUP (ORDER BY salary ASC) "25th Percentile Salary",
APPROX_PERCENTILE(0.50)
WITHIN GROUP (ORDER BY salary ASC) "50th Percentile Salary",
APPROX_PERCENTILE(0.75)
WITHIN GROUP (ORDER BY salary ASC) "75th Percentile Salary"
FROM employees
GROUP BY department_id
ORDER BY department_id;
Department 25th Percentile Salary 50th Percentile Salary 75th Percentile Salary
---------- ---------------------- ---------------------- ----------------------
10 4400 4400 4400
20 6000 6000 13000
30 2600 2800 3100
40 6500 6500 6500
50 2600 3100 3600
60 4800 4800 6000
70 10000 10000 10000
80 7300 8800 10000
90 17000 17000 24000
100 7700 7800 9000
110 8300 8300 12008
7000 7000 7000
CREATE MATERIALIZED VIEW amt_sold_by_city_mv
ENABLE QUERY REWRITE AS
SELECT c.country_id country,
c.cust_state_province state,
c.cust_city city,
APPROX_PERCENTILE_DETAIL(s.amount_sold) city_detail
FROM customers c, sales s
WHERE c.cust_id = s.cust_id
GROUP BY c.country_id, c.cust_state_province, c.cust_city;
CREATE MATERIALIZED VIEW amt_sold_by_state_mv AS
SELECT country,
state,
APPROX_PERCENTILE_AGG(city_detail) state_detail
FROM amt_sold_by_city_mv
GROUP BY country, state;
CREATE MATERIALIZED VIEW amt_sold_by_country_mv AS
SELECT country,
APPROX_PERCENTILE_AGG(city_detail) country_detail
FROM amt_sold_by_city_mv
GROUP BY country;
SELECT country,
state,
city,
TO_APPROX_PERCENTILE(city_detail, .25, 'NUMBER') "25th Percentile",
TO_APPROX_PERCENTILE(city_detail, .50, 'NUMBER') "50th Percentile",
TO_APPROX_PERCENTILE(city_detail, .75, 'NUMBER') "75th Percentile"
FROM amt_sold_by_city_mv
ORDER BY country, state, city;
COUNTRY STATE CITY 25th Percentile 50th Percentile 75th Percentile
------- ------------ -------------- --------------- --------------- ---------------
52769 Kuala Lumpur Kuala Lumpur 19.29 38.1 53.84
52769 Penang Batu Ferringhi 21.51 42.09 57.26
52769 Penang Georgetown 19.15 33.25 56.12
52769 Selangor Klang 18.08 32.06 51.29
52769 Selangor Petaling Jaya 19.29 35.43 60.2
. . .
SELECT country,
state,
TO_APPROX_PERCENTILE(state_detail, .25, 'NUMBER') "25th Percentile",
TO_APPROX_PERCENTILE(state_detail, .50, 'NUMBER') "50th Percentile",
TO_APPROX_PERCENTILE(state_detail, .75, 'NUMBER') "75th Percentile"
FROM amt_sold_by_state_mv
ORDER BY country, state;
COUNTRY STATE 25th Percentile 50th Percentile 75th Percentile
------- ------------ --------------- --------------- ---------------
52769 Kuala Lumpur 19.29 38.1 53.84
52769 Penang 20.19 36.84 56.12
52769 Selangor 16.97 32.41 52.69
52770 Drenthe 16.76 31.7 53.89
52770 Flevopolder 20.38 39.73 61.81
. . .
SELECT country,
TO_APPROX_PERCENTILE(country_detail, .25, 'NUMBER') "25th Percentile",
TO_APPROX_PERCENTILE(country_detail, .50, 'NUMBER') "50th Percentile",
TO_APPROX_PERCENTILE(country_detail, .75, 'NUMBER') "75th Percentile"
FROM amt_sold_by_country_mv
ORDER BY country;
COUNTRY 25th Percentile 50th Percentile 75th Percentile
--------- --------------- --------------- ---------------
52769 19.1 35.43 52.78
52770 19.29 38.99 59.58
52771 11.99 44.99 561.47
52772 18.08 33.72 54.16
52773 15.67 29.61 50.65
. . .
SELECT country,
TO_APPROX_PERCENTILE(APPROX_PERCENTILE_AGG(city_detail), .25, 'NUMBER') "25th Percentile"
FROM amt_sold_by_city_mv
GROUP BY country
ORDER BY country;
COUNTRY 25th Percentile
---------- ---------------
52769 19.1
52770 19.29
52771 11.99
52772 18.08
52773 15.67
. . .
SELECT c.country_id country,
APPROX_MEDIAN(s.amount_sold) amount_median
FROM customers c, sales s
WHERE c.cust_id = s.cust_id
GROUP BY c.country_id;
SET LINESIZE 300
SET PAGESIZE 0
COLUMN plan_table_output FORMAT A150
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=>'BASIC'));
EXPLAINED SQL STATEMENT:
------------------------
SELECT c.country_id country, APPROX_MEDIAN(s.amount_sold)
amount_median FROM customers c, sales s WHERE c.cust_id = s.cust_id
GROUP BY c.country_id
Plan hash value: 2232676046
-------------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH GROUP BY APPROX | |
| 2 | MAT_VIEW REWRITE ACCESS FULL| AMT_SOLD_BY_CITY_MV |
-------------------------------------------------------------
SELECT job_id,
APPROX_SUM(sal),
APPROX_RANK(PARTITION BY department_id ORDER BY APPROX_SUM(salary) DESC)
FROM employees
GROUP BY department_id, job_id
HAVING
APPROX_RANK(
PARTITION BY department_id
ORDER BY APPROX_SUM (salary)
DESC) <= 10;
SELECT department_id, job_id,
APPROX_SUM(salary)
FROM employees
GROUP BY department_id, job_id
HAVING
APPROX_RANK (
PARTITION BY department_id
ORDER BY APPROX_SUM(salary)
DESC ) <= 10;
SELECT AVG(salary) "Average"
FROM employees;
Average
--------------
6461.83178
SELECT manager_id, last_name, hire_date, salary,
AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg
FROM employees
ORDER BY manager_id, hire_date, salary;
MANAGER_ID LAST_NAME HIRE_DATE SALARY C_MAVG
---------- ------------------------- --------- ---------- ----------
100 De Haan 13-JAN-01 17000 14000
100 Raphaely 07-DEC-02 11000 11966.6667
100 Kaufling 01-MAY-03 7900 10633.3333
100 Hartstein 17-FEB-04 13000 9633.33333
100 Weiss 18-JUL-04 8000 11666.6667
100 Russell 01-OCT-04 14000 11833.3333
100 Partners 05-JAN-05 13500 13166.6667
100 Errazuriz 10-MAR-05 12000 11233.3333
. . .
SELECT '011' num, bin_to_num(0,1,1) bits FROM dual
UNION ALL SELECT '101' num, bin_to_num(1,0,1) bits FROM dual;
NUM BITS
--- ----------
011 3
101 5
SELECT bit_and_agg(bits)
FROM (SELECT '011' num, bin_to_num(0,1,1) bits FROM dual
UNION ALL SELECT '101' num, bin_to_num(1,0,1) bits FROM dual);
BIT_AND_AGG(BITS)
-----------------
1
CREATE TYPE phone_book_t AS TABLE OF phone_list_typ;
/
SELECT CAST(COLLECT(phone_numbers) AS phone_book_t) "Income Level L Phone Book"
FROM customers
WHERE income_level = 'L: 300,000 and above';
Income Level L Phone Book
--------------------------------------------------------------------------------
PHONE_BOOK_T(PHONE_LIST_TYP('+1 414 123 4307'), PHONE_LIST_TYP('+1 608 123 4344'
), PHONE_LIST_TYP('+1 814 123 4696'), PHONE_LIST_TYP('+1 215 123 4721'), PHONE_L
IST_TYP('+1 814 123 4755'), PHONE_LIST_TYP('+91 11 012 4817', '+91 11 083 4817')
, PHONE_LIST_TYP('+91 172 012 4837'), PHONE_LIST_TYP('+41 31 012 3569', '+41 31
083 3569'))
CREATE TYPE warehouse_name_t AS TABLE OF VARCHAR2(35);
/
SELECT CAST(COLLECT(warehouse_name ORDER BY warehouse_name)
AS warehouse_name_t) "Warehouses"
FROM warehouses;
Warehouses
--------------------------------------------------------------------------------
WAREHOUSE_NAME_TYP('Beijing', 'Bombay', 'Mexico City', 'New Jersey', 'San Franci
sco', 'Seattle, Washington', 'Southlake, Texas', 'Sydney', 'Toronto')
SELECT weight_class, CORR(list_price, min_price) "Correlation"
FROM product_information
GROUP BY weight_class
ORDER BY weight_class, "Correlation";
WEIGHT_CLASS Correlation
------------ -----------
1 .999149795
2 .999022941
3 .998484472
4 .999359909
5 .999536087
SELECT employee_id, job_id,
TO_CHAR((SYSDATE - hire_date) YEAR TO MONTH ) "Yrs-Mns", salary,
CORR(SYSDATE-hire_date, salary)
OVER(PARTITION BY job_id) AS "Correlation"
FROM employees
WHERE department_id in (50, 80)
ORDER BY job_id, employee_id;
EMPLOYEE_ID JOB_ID Yrs-Mns SALARY Correlation
----------- ---------- ------- ---------- -----------
145 SA_MAN +04-09 14000 .912385598
146 SA_MAN +04-06 13500 .912385598
147 SA_MAN +04-04 12000 .912385598
148 SA_MAN +01-08 11000 .912385598
149 SA_MAN +01-05 10500 .912385598
150 SA_REP +04-05 10000 .80436755
151 SA_REP +04-03 9500 .80436755
152 SA_REP +03-10 9000 .80436755
153 SA_REP +03-03 8000 .80436755
154 SA_REP +02-07 7500 .80436755
155 SA_REP +01-07 7000 .80436755
. . .
SELECT COUNT(*) count,
CORR_S(salary, commission_pct) commission,
CORR_S(salary, employee_id) empid
FROM employees;
COUNT COMMISSION EMPID
---------- ---------- ----------
107 .735837022 -.04473016
SELECT CORR_K(salary, commission_pct, 'COEFFICIENT') coefficient,
CORR_K(salary, commission_pct, 'TWO_SIDED_SIG') two_sided_p_value
FROM employees;
COEFFICIENT TWO_SIDED_P_VALUE
----------- -----------------
.603079768 3.4702E-07
SELECT COUNT(*) "Total"
FROM employees;
Total
----------
107
SELECT COUNT(*) "Allstars"
FROM employees
WHERE commission_pct > 0;
Allstars
---------
35
SELECT COUNT(commission_pct) "Count"
FROM employees;
Count
----------
35
SELECT COUNT(DISTINCT manager_id) "Managers"
FROM employees;
Managers
----------
18
SELECT last_name, salary,
COUNT(*) OVER (ORDER BY salary RANGE BETWEEN 50 PRECEDING AND
150 FOLLOWING) AS mov_count
FROM employees
ORDER BY salary, last_name;
LAST_NAME SALARY MOV_COUNT
------------------------- ---------- ----------
Olson 2100 3
Markle 2200 2
Philtanker 2200 2
Gee 2400 8
Landry 2400 8
Colmenares 2500 10
Marlow 2500 10
Patel 2500 10
. . .
SELECT job_id,
COVAR_POP(SYSDATE-hire_date, salary) AS covar_pop,
COVAR_SAMP(SYSDATE-hire_date, salary) AS covar_samp
FROM employees
WHERE department_id in (50, 80)
GROUP BY job_id
ORDER BY job_id, covar_pop, covar_samp;
JOB_ID COVAR_POP COVAR_SAMP
---------- ----------- -----------
SA_MAN 660700 825875
SA_REP 579988.466 600702.34
SH_CLERK 212432.5 223613.158
ST_CLERK 176577.25 185870.789
ST_MAN 436092 545115
SELECT product_id, supplier_id,
COVAR_POP(list_price, min_price)
OVER (ORDER BY product_id, supplier_id)
AS CUM_COVP,
COVAR_SAMP(list_price, min_price)
OVER (ORDER BY product_id, supplier_id)
AS CUM_COVS
FROM product_information p
WHERE category_id = 29
ORDER BY product_id, supplier_id;
PRODUCT_ID SUPPLIER_ID CUM_COVP CUM_COVS
---------- ----------- ---------- ----------
1774 103088 0
1775 103087 1473.25 2946.5
1794 103096 1702.77778 2554.16667
1825 103093 1926.25 2568.33333
2004 103086 1591.4 1989.25
2005 103086 1512.5 1815
2416 103088 1475.97959 1721.97619
. . .
SELECT CUME_DIST(15500, .05) WITHIN GROUP
(ORDER BY salary, commission_pct) "Cume-Dist of 15500"
FROM employees;
Cume-Dist of 15500
------------------
.972222222
SELECT job_id, last_name, salary, CUME_DIST()
OVER (PARTITION BY job_id ORDER BY salary) AS cume_dist
FROM employees
WHERE job_id LIKE 'PU%'
ORDER BY job_id, last_name, salary, cume_dist;
JOB_ID LAST_NAME SALARY CUME_DIST
---------- ------------------------- ---------- ----------
PU_CLERK Baida 2900 .8
PU_CLERK Colmenares 2500 .2
PU_CLERK Himuro 2600 .4
PU_CLERK Khoo 3100 1
PU_CLERK Tobias 2800 .6
PU_MAN Raphaely 11000 1
SELECT DENSE_RANK(15500, .05) WITHIN GROUP
(ORDER BY salary DESC, commission_pct) "Dense Rank"
FROM employees;
Dense Rank
----------
3
SELECT department_id, last_name, salary,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary) DENSE_RANK
FROM employees WHERE department_id = 60
ORDER BY DENSE_RANK, last_name;
DEPARTMENT_ID LAST_NAME SALARY DENSE_RANK
------------- ------------------------- ---------- ----------
60 Lorentz 4200 1
60 Austin 4800 2
60 Pataballa 4800 2
60 Ernst 6000 3
60 Hunold 9000 4
SELECT department_id,
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct) "Worst",
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct) "Best"
FROM employees
GROUP BY department_id
ORDER BY department_id;
DEPARTMENT_ID Worst Best
------------- ---------- ----------
10 4400 4400
20 6000 13000
30 2500 11000
40 6500 6500
50 2100 8200
60 4200 9000
70 10000 10000
80 6100 14000
90 17000 24000
100 6900 12008
110 8300 12008
7000 7000
SELECT co.country_region, co.country_subregion,
SUM(s.amount_sold) "Revenue", GROUP_ID() g
FROM sales s, customers c, countries co
WHERE s.cust_id = c.cust_id
AND c.country_id = co.country_id
AND s.time_id = '1-JAN-00'
AND co.country_region IN ('Americas', 'Europe')
GROUP BY GROUPING SETS ( (co.country_region, co.country_subregion),
(co.country_region, co.country_subregion) )
ORDER BY co.country_region, co.country_subregion, "Revenue", g;
COUNTRY_REGION COUNTRY_SUBREGION Revenue G
-------------------- ------------------------------ ---------- ----------
Americas Northern America 944.6 0
Americas Northern America 944.6 1
Europe Western Europe 566.39 0
Europe Western Europe 566.39 1
SELECT
DECODE(GROUPING(department_name), 1, 'ALL DEPARTMENTS', department_name)
AS department,
DECODE(GROUPING(job_id), 1, 'All Jobs', job_id) AS job,
COUNT(*) "Total Empl",
AVG(salary) * 12 "Average Sal"
FROM employees e, departments d
WHERE d.department_id = e.department_id
GROUP BY ROLLUP (department_name, job_id)
ORDER BY department, job;
DEPARTMENT JOB Total Empl Average Sal
------------------------------ ---------- ---------- -----------
ALL DEPARTMENTS All Jobs 106 77481.0566
Accounting AC_ACCOUNT 1 99600
Accounting AC_MGR 1 144096
Accounting All Jobs 2 121848
Administration AD_ASST 1 52800
Administration All Jobs 1 52800
Executive AD_PRES 1 288000
Executive AD_VP 2 204000
Executive All Jobs 3 232000
Finance All Jobs 6 103216
Finance FI_ACCOUNT 5 95040
. . .
SELECT channel_id, promo_id, sum(amount_sold) s_sales,
GROUPING(channel_id) gc,
GROUPING(promo_id) gp,
GROUPING_ID(channel_id, promo_id) gcp,
GROUPING_ID(promo_id, channel_id) gpc
FROM sales
WHERE promo_id > 496
GROUP BY CUBE(channel_id, promo_id)
ORDER BY channel_id, promo_id, s_sales, gc;
CHANNEL_ID PROMO_ID S_SALES GC GP GCP GPC
---------- ---------- ---------- ---------- ---------- ---------- ----------
2 999 25797563.2 0 0 0 0
2 25797563.2 0 1 1 2
3 999 55336945.1 0 0 0 0
3 55336945.1 0 1 1 2
4 999 13370012.5 0 0 0 0
4 13370012.5 0 1 1 2
999 94504520.8 1 0 2 1
94504520.8 1 1 3 3
CREATE TABLE id_table (id NUMBER);
INSERT INTO id_table VALUES(624);
INSERT INTO id_table VALUES(null);
INSERT INTO id_table VALUES(925);
INSERT INTO id_table VALUES(585);
SELECT JSON_ARRAYAGG(id ORDER BY id RETURNING VARCHAR2(100)) ID_NUMBERS
FROM id_table;
ID_NUMBERS
-------------
[585,624,925]
SELECT JSON_OBJECTAGG(KEY department_name VALUE department_id) "Department Numbers"
FROM departments
WHERE department_id <= 30;
Department Numbers
----------------------------------------------------
{"Administration":10,"Marketing":20,"Purchasing":30}
SELECT LISTAGG(last_name, '; ')
WITHIN GROUP (ORDER BY hire_date, last_name) "Emp_list",
MIN(hire_date) "Earliest"
FROM employees
WHERE department_id = 30;
Emp_list Earliest
------------------------------------------------------------ ---------
Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares 07-DEC-02
SELECT department_id "Dept.",
LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date) "Employees"
FROM employees
GROUP BY department_id
ORDER BY department_id;
Dept. Employees
------ ------------------------------------------------------------
10 Whalen
20 Hartstein; Fay
30 Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares
40 Mavris
50 Kaufling; Ladwig; Rajs; Sarchand; Bell; Mallin; Weiss; Davie
s; Marlow; Bull; Everett; Fripp; Chung; Nayer; Dilly; Bissot
; Vollman; Stiles; Atkinson; Taylor; Seo; Fleaur; Matos; Pat
el; Walsh; Feeney; Dellinger; McCain; Vargas; Gates; Rogers;
Mikkilineni; Landry; Cabrio; Jones; Olson; OConnell; Sulliv
an; Mourgos; Gee; Perkins; Grant; Geoni; Philtanker; Markle
60 Austin; Hunold; Pataballa; Lorentz; Ernst
70 Baer
. . .
SELECT department_id "Dept.",
LISTAGG(last_name, '; ' ON OVERFLOW TRUNCATE '...')
WITHIN GROUP (ORDER BY hire_date) "Employees"
FROM employees
GROUP BY department_id
ORDER BY department_id;
Dept. Employees
------ ------------------------------------------------------------
10 Whalen
20 Hartstein; Fay
30 Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares
40 Mavris
50 Kaufling; Ladwig; Rajs; Sarchand; Bell; Mallin; Weiss; Davie
s; Marlow; Bull; Everett; Fripp; Chung; Nayer; Dilly; Bissot
; Vollman; Stiles; Atkinson; Taylor; Seo; Fleaur; ... (23)
70 Baer
. . .
SELECT department_id "Dept", hire_date "Date", last_name "Name",
LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date, last_name)
OVER (PARTITION BY department_id) as "Emp_list"
FROM employees
WHERE hire_date < '01-SEP-2003'
ORDER BY "Dept", "Date", "Name";
Dept Date Name Emp_list
----- --------- --------------- ---------------------------------------------
30 07-DEC-02 Raphaely Raphaely; Khoo
30 18-MAY-03 Khoo Raphaely; Khoo
40 07-JUN-02 Mavris Mavris
50 01-MAY-03 Kaufling Kaufling; Ladwig
50 14-JUL-03 Ladwig Kaufling; Ladwig
70 07-JUN-02 Baer Baer
90 13-JAN-01 De Haan De Haan; King
90 17-JUN-03 King De Haan; King
100 16-AUG-02 Faviet Faviet; Greenberg
100 17-AUG-02 Greenberg Faviet; Greenberg
110 07-JUN-02 Gietz Gietz; Higgins
110 07-JUN-02 Higgins Gietz; Higgins
SELECT MAX(salary) "Maximum"
FROM employees;
Maximum
----------
24000
SELECT manager_id, last_name, salary,
MAX(salary) OVER (PARTITION BY manager_id) AS mgr_max
FROM employees
ORDER BY manager_id, last_name, salary;
MANAGER_ID LAST_NAME SALARY MGR_MAX
---------- ------------------------- ---------- ----------
100 Cambrault 11000 17000
100 De Haan 17000 17000
100 Errazuriz 12000 17000
100 Fripp 8200 17000
100 Hartstein 13000 17000
100 Kaufling 7900 17000
100 Kochhar 17000 17000
. . .
SELECT manager_id, last_name, salary
FROM (SELECT manager_id, last_name, salary,
MAX(salary) OVER (PARTITION BY manager_id) AS rmax_sal
FROM employees)
WHERE salary = rmax_sal
ORDER BY manager_id, last_name, salary;
MANAGER_ID LAST_NAME SALARY
---------- ------------------------- ----------
100 De Haan 17000
100 Kochhar 17000
101 Greenberg 12008
101 Higgins 12008
102 Hunold 9000
103 Ernst 6000
108 Faviet 9000
114 Khoo 3100
120 Nayer 3200
120 Taylor 3200
121 Sarchand 4200
122 Chung 3800
123 Bell 4000
124 Rajs 3500
145 Tucker 10000
146 King 10000
147 Vishney 10500
148 Ozer 11500
149 Abel 11000
201 Fay 6000
205 Gietz 8300
King 24000
22 rows selected.
SELECT department_id, MEDIAN(salary)
FROM employees
GROUP BY department_id
ORDER BY department_id;
DEPARTMENT_ID MEDIAN(SALARY)
------------- --------------
10 4400
20 9500
30 2850
40 6500
50 3100
60 4800
70 10000
80 8900
90 17000
100 8000
110 10154
7000
SELECT manager_id, employee_id, salary,
MEDIAN(salary) OVER (PARTITION BY manager_id) "Median by Mgr"
FROM employees
WHERE department_id > 60
ORDER BY manager_id, employee_id;
MANAGER_ID EMPLOYEE_ID SALARY Median by Mgr
---------- ----------- ---------- -------------
100 101 17000 13500
100 102 17000 13500
100 145 14000 13500
100 146 13500 13500
100 147 12000 13500
100 148 11000 13500
100 149 10500 13500
101 108 12008 12008
101 204 10000 12008
101 205 12008 12008
108 109 9000 7800
108 110 8200 7800
108 111 7700 7800
108 112 7800 7800
108 113 6900 7800
145 150 10000 8500
145 151 9500 8500
145 152 9000 8500
. . .
SELECT MIN(hire_date) "Earliest"
FROM employees;
Earliest
---------
13-JAN-01
SELECT manager_id, last_name, hire_date, salary,
MIN(salary) OVER(PARTITION BY manager_id ORDER BY hire_date
RANGE UNBOUNDED PRECEDING) AS p_cmin
FROM employees
ORDER BY manager_id, last_name, hire_date, salary;
MANAGER_ID LAST_NAME HIRE_DATE SALARY P_CMIN
---------- ------------------------- --------- ---------- ----------
100 Cambrault 15-OCT-07 11000 6500
100 De Haan 13-JAN-01 17000 17000
100 Errazuriz 10-MAR-05 12000 7900
100 Fripp 10-APR-05 8200 7900
100 Hartstein 17-FEB-04 13000 7900
100 Kaufling 01-MAY-03 7900 7900
100 Kochhar 21-SEP-05 17000 7900
100 Mourgos 16-NOV-07 5800 5800
100 Partners 05-JAN-05 13500 7900
100 Raphaely 07-DEC-02 11000 11000
100 Russell 01-OCT-04 14000 7900
. . .
SELECT PERCENT_RANK(15000, .05) WITHIN GROUP
(ORDER BY salary, commission_pct) "Percent-Rank"
FROM employees;
Percent-Rank
------------
.971962617
SELECT department_id, last_name, salary, PERCENT_RANK()
OVER (PARTITION BY department_id ORDER BY salary DESC) AS pr
FROM employees
ORDER BY pr, salary, last_name;
DEPARTMENT_ID LAST_NAME SALARY PR
------------- ------------------------- ---------- ----------
10 Whalen 4400 0
40 Mavris 6500 0
Grant 7000 0
. . .
80 Vishney 10500 .181818182
80 Zlotkey 10500 .181818182
30 Khoo 3100 .2
. . .
50 Markle 2200 .954545455
50 Philtanker 2200 .954545455
50 Olson 2100 1
. . .
SELECT department_id,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary DESC) "Median cont",
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary DESC) "Median disc"
FROM employees
GROUP BY department_id
ORDER BY department_id;
DEPARTMENT_ID Median cont Median disc
------------- ----------- -----------
10 4400 4400
20 9500 13000
30 2850 2900
40 6500 6500
50 3100 3100
60 4800 4800
70 10000 10000
80 8900 9000
90 17000 17000
100 8000 8200
110 10154 12008
7000 7000
SELECT last_name, salary, department_id,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary DESC)
OVER (PARTITION BY department_id) "Percentile_Cont",
PERCENT_RANK()
OVER (PARTITION BY department_id ORDER BY salary DESC) "Percent_Rank"
FROM employees
WHERE department_id IN (30, 60)
ORDER BY last_name, salary, department_id;
LAST_NAME SALARY DEPARTMENT_ID Percentile_Cont Percent_Rank
------------------------- ---------- ------------- --------------- ------------
Austin 4800 60 4800 .5
Baida 2900 30 2850 .4
Colmenares 2500 30 2850 1
Ernst 6000 60 4800 .25
Himuro 2600 30 2850 .8
Hunold 9000 60 4800 0
Khoo 3100 30 2850 .2
Lorentz 4200 60 4800 1
Pataballa 4800 60 4800 .5
Raphaely 11000 30 2850 0
Tobias 2800 30 2850 .6
SELECT last_name, salary, department_id,
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary DESC)
OVER (PARTITION BY department_id) "Percentile_Disc",
CUME_DIST() OVER (PARTITION BY department_id
ORDER BY salary DESC) "Cume_Dist"
FROM employees
WHERE department_id in (30, 60)
ORDER BY last_name, salary, department_id;
LAST_NAME SALARY DEPARTMENT_ID Percentile_Disc Cume_Dist
------------------------- ---------- ------------- --------------- ----------
Austin 4800 60 4800 .8
Baida 2900 30 2900 .5
Colmenares 2500 30 2900 1
Ernst 6000 60 4800 .4
Himuro 2600 30 2900 .833333333
Hunold 9000 60 4800 .2
Khoo 3100 30 2900 .333333333
Lorentz 4200 60 4800 1
Pataballa 4800 60 4800 .8
Raphaely 11000 30 2900 .166666667
Tobias 2800 30 2900 .666666667
SELECT RANK(15500, .05) WITHIN GROUP
(ORDER BY salary, commission_pct) "Rank"
FROM employees;
Rank
----------
105
SELECT RANK(15500) WITHIN GROUP
(ORDER BY salary DESC) "Rank of 15500"
FROM employees;
Rank of 15500
--------------
4
SELECT department_id, last_name, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary) RANK
FROM employees WHERE department_id = 60
ORDER BY RANK, last_name;
DEPARTMENT_ID LAST_NAME SALARY RANK
------------- ------------------------- ---------- ----------
60 Lorentz 4200 1
60 Austin 4800 2
60 Pataballa 4800 2
60 Ernst 6000 4
60 Hunold 9000 5
SELECT job_id, employee_id ID, salary,
REGR_SLOPE(SYSDATE-hire_date, salary)
OVER (PARTITION BY job_id) slope,
REGR_INTERCEPT(SYSDATE-hire_date, salary)
OVER (PARTITION BY job_id) intcpt,
REGR_R2(SYSDATE-hire_date, salary)
OVER (PARTITION BY job_id) rsqr,
REGR_COUNT(SYSDATE-hire_date, salary)
OVER (PARTITION BY job_id) count,
REGR_AVGX(SYSDATE-hire_date, salary)
OVER (PARTITION BY job_id) avgx,
REGR_AVGY(SYSDATE-hire_date, salary)
OVER (PARTITION BY job_id) avgy
FROM employees
WHERE department_id in (50, 80)
ORDER BY job_id, employee_id;
JOB_ID ID SALARY SLOPE INTCPT RSQR COUNT AVGX AVGY
---------- ----- ---------- ----- --------- ----- ------ ---------- ---------
SA_MAN 145 14000 .355 -1707.035 .832 5 12200.000 2626.589
SA_MAN 146 13500 .355 -1707.035 .832 5 12200.000 2626.589
SA_MAN 147 12000 .355 -1707.035 .832 5 12200.000 2626.589
SA_MAN 148 11000 .355 -1707.035 .832 5 12200.000 2626.589
SA_MAN 149 10500 .355 -1707.035 .832 5 12200.000 2626.589
SA_REP 150 10000 .257 404.763 .647 29 8396.552 2561.244
SA_REP 151 9500 .257 404.763 .647 29 8396.552 2561.244
SA_REP 152 9000 .257 404.763 .647 29 8396.552 2561.244
SA_REP 153 8000 .257 404.763 .647 29 8396.552 2561.244
SA_REP 154 7500 .257 404.763 .647 29 8396.552 2561.244
SA_REP 155 7000 .257 404.763 .647 29 8396.552 2561.244
SA_REP 156 10000 .257 404.763 .647 29 8396.552 2561.244
. . .
SELECT job_id,
REGR_SLOPE(SYSDATE-hire_date, salary) slope,
REGR_INTERCEPT(SYSDATE-hire_date, salary) intercept
FROM employees
WHERE department_id in (50,80)
GROUP BY job_id
ORDER BY job_id;
JOB_ID SLOPE INTERCEPT
---------- ----- ------------
SA_MAN .355 -1707.030762
SA_REP .257 404.767151
SH_CLERK .745 159.015293
ST_CLERK .904 134.409050
ST_MAN .479 -570.077291
SELECT job_id,
REGR_COUNT(SYSDATE-hire_date, salary) count
FROM employees
WHERE department_id in (30, 50)
GROUP BY job_id
ORDER BY job_id, count;
JOB_ID COUNT
---------- ----------
PU_CLERK 5
PU_MAN 1
SH_CLERK 20
ST_CLERK 20
ST_MAN 5
SELECT job_id,
REGR_R2(SYSDATE-hire_date, salary) Regr_R2
FROM employees
WHERE department_id in (80, 50)
GROUP by job_id
ORDER BY job_id, Regr_R2;
JOB_ID REGR_R2
---------- ----------
SA_MAN .83244748
SA_REP .647007156
SH_CLERK .879799698
ST_CLERK .742808493
ST_MAN .69418508
SELECT job_id,
REGR_AVGY(SYSDATE-hire_date, salary) avgy,
REGR_AVGX(SYSDATE-hire_date, salary) avgx
FROM employees
WHERE department_id in (30,50)
GROUP BY job_id
ORDER BY job_id, avgy, avgx;
JOB_ID AVGY AVGX
---------- ---------- ----------
PU_CLERK 2950.3778 2780
PU_MAN 4026.5778 11000
SH_CLERK 2773.0778 3215
ST_CLERK 2872.7278 2785
ST_MAN 3140.1778 7280
SELECT job_id,
REGR_SXY(SYSDATE-hire_date, salary) regr_sxy,
REGR_SXX(SYSDATE-hire_date, salary) regr_sxx,
REGR_SYY(SYSDATE-hire_date, salary) regr_syy
FROM employees
WHERE department_id in (80, 50)
GROUP BY job_id
ORDER BY job_id;
JOB_ID REGR_SXY REGR_SXX REGR_SYY
---------- ---------- ----------- ----------
SA_MAN 3303500 9300000.0 1409642
SA_REP 16819665.5 65489655.2 6676562.55
SH_CLERK 4248650 5705500.0 3596039
ST_CLERK 3531545 3905500.0 4299084.55
ST_MAN 2180460 4548000.0 1505915.2
SELECT AVG(DECODE(cust_gender, 'M', 1, 0)) real_proportion,
STATS_BINOMIAL_TEST
(cust_gender, 'M', 0.68, 'EXACT_PROB') exact,
STATS_BINOMIAL_TEST
(cust_gender, 'M', 0.68, 'ONE_SIDED_PROB_OR_LESS') prob_or_less
FROM sh.customers;
SELECT STATS_CROSSTAB
(cust_gender, cust_income_level, 'CHISQ_OBS') chi_squared,
STATS_CROSSTAB
(cust_gender, cust_income_level, 'CHISQ_SIG') p_value,
STATS_CROSSTAB
(cust_gender, cust_income_level, 'PHI_COEFFICIENT') phi_coefficient
FROM sh.customers;
CHI_SQUARED P_VALUE PHI_COEFFICIENT
----------- ---------- ---------------
251.690705 1.2364E-47 .067367056
SELECT VARIANCE(DECODE(cust_gender, 'M', cust_credit_limit, null)) var_men,
VARIANCE(DECODE(cust_gender, 'F', cust_credit_limit, null)) var_women,
STATS_F_TEST(cust_gender, cust_credit_limit, 'STATISTIC', 'F') f_statistic,
STATS_F_TEST(cust_gender, cust_credit_limit) two_sided_p_value
FROM sh.customers;
VAR_MEN VAR_WOMEN F_STATISTIC TWO_SIDED_P_VALUE
---------- ---------- ----------- -----------------
12879896.7 13046865 1.01296348 .311928071
SELECT stats_ks_test(cust_gender, amount_sold, 'STATISTIC') ks_statistic,
stats_ks_test(cust_gender, amount_sold) p_value
FROM sh.customers c, sh.sales s
WHERE c.cust_id = s.cust_id;
KS_STATISTIC P_VALUE
------------ ----------
.003841396 .004080006
SELECT department_id, STATS_MODE(salary) FROM employees
GROUP BY department_id
ORDER BY department_id, stats_mode(salary);
DEPARTMENT_ID STATS_MODE(SALARY)
------------- ------------------
10 4400
20 6000
30 2500
40 6500
50 2500
60 4800
70 10000
80 9500
90 17000
100 6900
110 8300
7000
SELECT commission_pct FROM
(SELECT commission_pct, COUNT(commission_pct) AS cnt1 FROM employees
GROUP BY commission_pct)
WHERE cnt1 =
(SELECT MAX (cnt2) FROM
(SELECT COUNT(commission_pct) AS cnt2
FROM employees GROUP BY commission_pct))
ORDER BY commission_pct;
COMMISSION_PCT
--------------
.2
.3
SELECT STATS_MW_TEST
(cust_gender, amount_sold, 'STATISTIC') z_statistic,
STATS_MW_TEST
(cust_gender, amount_sold, 'ONE_SIDED_SIG', 'F') one_sided_p_value
FROM sh.customers c, sh.sales s
WHERE c.cust_id = s.cust_id;
Z_STATISTIC ONE_SIDED_P_VALUE
----------- -----------------
-1.4011509 .080584471
SELECT cust_gender,
STATS_ONE_WAY_ANOVA(cust_income_level, amount_sold, 'F_RATIO') f_ratio,
STATS_ONE_WAY_ANOVA(cust_income_level, amount_sold, 'SIG') p_value
FROM sh.customers c, sh.sales s
WHERE c.cust_id = s.cust_id
GROUP BY cust_gender
ORDER BY cust_gender;
C F_RATIO P_VALUE
- ---------- ----------
F 5.59536943 4.7840E-09
M 9.2865001 6.7139E-17
SELECT AVG(prod_list_price) group_mean,
STATS_T_TEST_ONE(prod_list_price, 60, 'STATISTIC') t_observed,
STATS_T_TEST_ONE(prod_list_price, 60) two_sided_p_value
FROM sh.products;
GROUP_MEAN T_OBSERVED TWO_SIDED_P_VALUE
---------- ---------- -----------------
139.545556 2.32107746 .023158537
SELECT SUBSTR(cust_income_level, 1, 22) income_level,
AVG(DECODE(cust_gender, 'M', amount_sold, null)) sold_to_men,
AVG(DECODE(cust_gender, 'F', amount_sold, null)) sold_to_women,
STATS_T_TEST_INDEP(cust_gender, amount_sold, 'STATISTIC', 'F') t_observed,
STATS_T_TEST_INDEP(cust_gender, amount_sold) two_sided_p_value
FROM sh.customers c, sh.sales s
WHERE c.cust_id = s.cust_id
GROUP BY ROLLUP(cust_income_level)
ORDER BY income_level, sold_to_men, sold_to_women, t_observed;
INCOME_LEVEL SOLD_TO_MEN SOLD_TO_WOMEN T_OBSERVED TWO_SIDED_P_VALUE
---------------------- ----------- ------------- ---------- -----------------
A: Below 30,000 105.28349 99.4281447 -1.9880629 .046811482
B: 30,000 - 49,999 102.59651 109.829642 3.04330875 .002341053
C: 50,000 - 69,999 105.627588 110.127931 2.36148671 .018204221
D: 70,000 - 89,999 106.630299 110.47287 2.28496443 .022316997
E: 90,000 - 109,999 103.396741 101.610416 -1.2544577 .209677823
F: 110,000 - 129,999 106.76476 105.981312 -.60444998 .545545304
G: 130,000 - 149,999 108.877532 107.31377 -.85298245 .393671218
H: 150,000 - 169,999 110.987258 107.152191 -1.9062363 .056622983
I: 170,000 - 189,999 102.808238 107.43556 2.18477851 .028908566
J: 190,000 - 249,999 108.040564 115.343356 2.58313425 .009794516
K: 250,000 - 299,999 112.377993 108.196097 -1.4107871 .158316973
L: 300,000 and above 120.970235 112.216342 -2.0642868 .039003862
107.121845 113.80441 .686144393 .492670059
106.663769 107.276386 1.08013499 .280082357
14 rows selected.
SELECT SUBSTR(cust_income_level, 1, 22) income_level,
AVG(DECODE(cust_gender, 'M', amount_sold, null)) sold_to_men,
AVG(DECODE(cust_gender, 'F', amount_sold, null)) sold_to_women,
STATS_T_TEST_INDEPU(cust_gender, amount_sold, 'STATISTIC', 'F') t_observed,
STATS_T_TEST_INDEPU(cust_gender, amount_sold) two_sided_p_value
FROM sh.customers c, sh.sales s
WHERE c.cust_id = s.cust_id
GROUP BY ROLLUP(cust_income_level)
ORDER BY income_level, sold_to_men, sold_to_women, t_observed;
INCOME_LEVEL SOLD_TO_MEN SOLD_TO_WOMEN T_OBSERVED TWO_SIDED_P_VALUE
---------------------- ----------- ------------- ---------- -----------------
A: Below 30,000 105.28349 99.4281447 -2.0542592 .039964704
B: 30,000 - 49,999 102.59651 109.829642 2.96922332 .002987742
C: 50,000 - 69,999 105.627588 110.127931 2.3496854 .018792277
D: 70,000 - 89,999 106.630299 110.47287 2.26839281 .023307831
E: 90,000 - 109,999 103.396741 101.610416 -1.2603509 .207545662
F: 110,000 - 129,999 106.76476 105.981312 -.60580011 .544648553
G: 130,000 - 149,999 108.877532 107.31377 -.85219781 .394107755
H: 150,000 - 169,999 110.987258 107.152191 -1.9451486 .051762624
I: 170,000 - 189,999 102.808238 107.43556 2.14966921 .031587875
J: 190,000 - 249,999 108.040564 115.343356 2.54749867 .010854966
K: 250,000 - 299,999 112.377993 108.196097 -1.4115514 .158091676
L: 300,000 and above 120.970235 112.216342 -2.0726194 .038225611
107.121845 113.80441 .689462437 .490595765
106.663769 107.276386 1.07853782 .280794207
14 rows selected.
SELECT STDDEV(salary) "Deviation"
FROM employees;
Deviation
----------
3909.36575
SELECT last_name, salary,
STDDEV(salary) OVER (ORDER BY hire_date) "StdDev"
FROM employees
WHERE department_id = 30
ORDER BY last_name, salary, "StdDev";
LAST_NAME SALARY StdDev
------------------------- ---------- ----------
Baida 2900 4035.26125
Colmenares 2500 3362.58829
Himuro 2600 3649.2465
Khoo 3100 5586.14357
Raphaely 11000 0
Tobias 2800 4650.0896
SELECT STDDEV_POP(amount_sold) "Pop",
STDDEV_SAMP(amount_sold) "Samp"
FROM sales;
Pop Samp
---------- ----------
896.355151 896.355592
SELECT department_id, last_name, salary,
STDDEV_POP(salary) OVER (PARTITION BY department_id) AS pop_std
FROM employees
ORDER BY department_id, last_name, salary, pop_std;
DEPARTMENT_ID LAST_NAME SALARY POP_STD
------------- ------------------------- ---------- ----------
10 Whalen 4400 0
20 Fay 6000 3500
20 Hartstein 13000 3500
30 Baida 2900 3069.6091
. . .
100 Urman 7800 1644.18166
110 Gietz 8300 1850
110 Higgins 12000 1850
Grant 7000 0
SELECT STDDEV_POP(amount_sold) "Pop",
STDDEV_SAMP(amount_sold) "Samp"
FROM sales;
Pop Samp
---------- ----------
896.355151 896.355592
SELECT department_id, last_name, salary,
STDDEV_POP(salary) OVER (PARTITION BY department_id) AS pop_std
FROM employees
ORDER BY department_id, last_name, salary, pop_std;
DEPARTMENT_ID LAST_NAME SALARY POP_STD
------------- ------------------------- ---------- ----------
10 Whalen 4400 0
20 Fay 6000 3500
20 Hartstein 13000 3500
30 Baida 2900 3069.6091
. . .
100 Urman 7800 1644.18166
110 Gietz 8300 1850
110 Higgins 12000 1850
Grant 7000 0
SELECT department_id, last_name, hire_date, salary,
STDDEV_SAMP(salary) OVER (PARTITION BY department_id
ORDER BY hire_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_sdev
FROM employees
ORDER BY department_id, last_name, hire_date, salary, cum_sdev;
DEPARTMENT_ID LAST_NAME HIRE_DATE SALARY CUM_SDEV
------------- --------------- --------- ---------- ----------
10 Whalen 17-SEP-03 4400
20 Fay 17-AUG-05 6000 4949.74747
20 Hartstein 17-FEB-04 13000
30 Baida 24-DEC-05 2900 4035.26125
30 Colmenares 10-AUG-07 2500 3362.58829
30 Himuro 15-NOV-06 2600 3649.2465
30 Khoo 18-MAY-03 3100 5586.14357
30 Raphaely 07-DEC-02 11000
. . .
100 Greenberg 17-AUG-02 12008 2126.9772
100 Popp 07-DEC-07 6900 1804.13155
100 Sciarra 30-SEP-05 7700 1929.76233
100 Urman 07-MAR-06 7800 1788.92504
110 Gietz 07-JUN-02 8300 2621.95194
110 Higgins 07-JUN-02 12008
Grant 24-MAY-07 7000
SELECT SUM(salary) "Total"
FROM employees;
Total
----------
691400
SELECT manager_id, last_name, salary,
SUM(salary) OVER (PARTITION BY manager_id ORDER BY salary
RANGE UNBOUNDED PRECEDING) l_csum
FROM employees
ORDER BY manager_id, last_name, salary, l_csum;
MANAGER_ID LAST_NAME SALARY L_CSUM
---------- ------------------------- ---------- ----------
100 Cambrault 11000 68900
100 De Haan 17000 155400
100 Errazuriz 12000 80900
100 Fripp 8200 36400
100 Hartstein 13000 93900
100 Kaufling 7900 20200
100 Kochhar 17000 155400
100 Mourgos 5800 5800
100 Partners 13500 107400
100 Raphaely 11000 68900
100 Russell 14000 121400
. . .
149 Hutton 8800 39000
149 Johnson 6200 6200
149 Livingston 8400 21600
149 Taylor 8600 30200
201 Fay 6000 6000
205 Gietz 8300 8300
King 24000 24000
SELECT SUM(salary) "Total"
FROM employees;
Total
----------
691400
SELECT manager_id, last_name, salary,
SUM(salary) OVER (PARTITION BY manager_id ORDER BY salary
RANGE UNBOUNDED PRECEDING) l_csum
FROM employees
ORDER BY manager_id, last_name, salary, l_csum;
MANAGER_ID LAST_NAME SALARY L_CSUM
---------- ------------------------- ---------- ----------
100 Cambrault 11000 68900
100 De Haan 17000 155400
100 Errazuriz 12000 80900
100 Fripp 8200 36400
100 Hartstein 13000 93900
100 Kaufling 7900 20200
100 Kochhar 17000 155400
100 Mourgos 5800 5800
100 Partners 13500 107400
100 Raphaely 11000 68900
100 Russell 14000 121400
. . .
149 Hutton 8800 39000
149 Johnson 6200 6200
149 Livingston 8400 21600
149 Taylor 8600 30200
201 Fay 6000 6000
205 Gietz 8300 8300
King 24000 24000
CREATE MATERIALIZED ZONEMAP sales_zmap
AS
SELECT SYS_OP_ZONE_ID(rowid), MIN(time_id), MAX(time_id)
FROM sales
GROUP BY SYS_OP_ZONE_ID(rowid);
CREATE MATERIALIZED ZONEMAP sales_zmap
SCALE 8
AS
SELECT SYS_OP_ZONE_ID(rowid), MIN(time_id), MAX(time_id)
FROM sales
GROUP BY SYS_OP_ZONE_ID(rowid);
CREATE MATERIALIZED ZONEMAP sales_zmap
SCALE 8
AS
SELECT SYS_OP_ZONE_ID(rowid,12), MIN(time_id), MAX(time_id)
FROM sales
GROUP BY SYS_OP_ZONE_ID(rowid,12);
CREATE MATERIALIZED ZONEMAP sales_zmap
AS
SELECT SYS_OP_ZONE_ID(s.rowid),
MIN(prod_category), MAX(prod_category),
MIN(country_id), MAX(country_id)
FROM sales s, products p, customers c
WHERE s.prod_id = p.prod_id(+) AND
s.cust_id = c.cust_id(+)
GROUP BY SYS_OP_ZONE_ID(s.rowid);
SELECT SYS_XMLAGG(SYS_XMLGEN(last_name)) XMLAGG
FROM employees
WHERE last_name LIKE 'R%'
ORDER BY xmlagg;
XMLAGG
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
<LAST_NAME>Rajs</LAST_NAME>
<LAST_NAME>Raphaely</LAST_NAME>
<LAST_NAME>Rogers</LAST_NAME>
<LAST_NAME>Russell</LAST_NAME>
</ROWSET>
SELECT VAR_POP(salary) FROM employees;
VAR_POP(SALARY)
---------------
15141964.9
SELECT t.calendar_month_desc,
VAR_POP(SUM(s.amount_sold))
OVER (ORDER BY t.calendar_month_desc) "Var_Pop",
VAR_SAMP(SUM(s.amount_sold))
OVER (ORDER BY t.calendar_month_desc) "Var_Samp"
FROM sales s, times t
WHERE s.time_id = t.time_id AND t.calendar_year = 1998
GROUP BY t.calendar_month_desc
ORDER BY t.calendar_month_desc, "Var_Pop", "Var_Samp";
CALENDAR Var_Pop Var_Samp
-------- ---------- ----------
1998-01 0
1998-02 2269111326 4538222653
1998-03 5.5849E+10 8.3774E+10
1998-04 4.8252E+10 6.4336E+10
1998-05 6.0020E+10 7.5025E+10
1998-06 5.4091E+10 6.4909E+10
1998-07 4.7150E+10 5.5009E+10
1998-08 4.1345E+10 4.7252E+10
1998-09 3.9591E+10 4.4540E+10
1998-10 3.9995E+10 4.4439E+10
1998-11 3.6870E+10 4.0558E+10
1998-12 4.0216E+10 4.3872E+10
SELECT VAR_SAMP(salary) FROM employees;
VAR_SAMP(SALARY)
----------------
15284813.7
SELECT VARIANCE(salary) "Variance"
FROM employees;
Variance
----------
15283140.5
SELECT last_name, salary, VARIANCE(salary)
OVER (ORDER BY hire_date) "Variance"
FROM employees
WHERE department_id = 30
ORDER BY last_name, salary, "Variance";
LAST_NAME SALARY Variance
------------------------- ---------- ----------
Baida 2900 16283333.3
Colmenares 2500 11307000
Himuro 2600 13317000
Khoo 3100 31205000
Raphaely 11000 0
Tobias 2800 21623333.3
SELECT XMLELEMENT("Department",
XMLAGG(XMLELEMENT("Employee",
e.job_id||' '||e.last_name)
ORDER BY last_name))
as "Dept_list"
FROM employees e
WHERE e.department_id = 30;
Dept_list
-------------------------------------------------------------
<Department>
<Employee>PU_CLERK Baida</Employee>
<Employee>PU_CLERK Colmenares</Employee>
<Employee>PU_CLERK Himuro</Employee>
<Employee>PU_CLERK Khoo</Employee>
<Employee>PU_MAN Raphaely</Employee>
<Employee>PU_CLERK Tobias</Employee>
</Department>
SELECT XMLELEMENT("Department",
XMLAGG(XMLELEMENT("Employee", e.job_id||' '||e.last_name)))
AS "Dept_list"
FROM employees e
GROUP BY e.department_id;
Dept_list
---------------------------------------------------------
<Department>
<Employee>AD_ASST Whalen</Employee>
</Department>
<Department>
<Employee>MK_MAN Hartstein</Employee>
<Employee>MK_REP Fay</Employee>
</Department>
<Department>
<Employee>PU_MAN Raphaely</Employee>
<Employee>PU_CLERK Khoo</Employee>
<Employee>PU_CLERK Tobias</Employee>
<Employee>PU_CLERK Baida</Employee>
<Employee>PU_CLERK Colmenares</Employee>
<Employee>PU_CLERK Himuro</Employee>
</Department>
. . .
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
