SELECT

SELECT ALL playerid,year,stint FROM scoring
  WHERE year = 2009 AND playerid LIKE 'boy%' ORDER BY playerid;
PLAYERID  YEAR  STINT
 --------- ----- ------
 boychjo01 2009    1
 boychza01 2009    1
 boyddu01  2009    1
 boyddu01  2009    2
 boyesbr01 2009    1
 boylebr01 2009    1
 boyleda01 2009    1
 boyntni01 2009    1
 boyntni01 2009    2

SELECT DISTINCT playerid,year FROM scoring
  WHERE year = 2009 AND playerid LIKE 'boy%' ORDER BY playerid;
 PLAYERID  YEAR
 --------- -----
 boychjo01 2009
 boychza01 2009
 boyddu01  2009
 boyesbr01 2009
 boylebr01 2009
 boyleda01 2009
 boyntni01 2009

SELECT cast(birthmon||'/'||birthday||'/'||birthyear as date) AS BIRTH_DATE,
       CASE birthcountry
           WHEN 'USA' THEN 'United States'
           ELSE 'Other'
       END AS BIRTH_COUNTRY,
       LASTNHL-FIRSTNHL AS YEARS,
       UPPER(FIRSTNAME), UPPER(LASTNAME), WEIGHT
   FROM players WHERE birthday > 0 AND weight > 250;

 BIRTH_DATE  BIRTH_COUNTRY  YEARS  FIRSTNAME  LASTNAME  WEIGHT
 ----------- -------------- ------ ---------- --------- -------
 1983-04-04  Other            4     EVGENY    ARTYUKHIN   254
 1982-06-23  Other            5     DEREK     BOOGAARD    254
 1985-03-27  United States    6     DUSTIN    BYFUGLIEN   265
 1977-03-18  Other            14    ZDENO     CHARA       255
 1967-10-16  Other            1     GERRY     FLEMING     253
 1978-12-28  Other            1     KYLE      FREADRICH   260
 1979-01-01  Other            6     RAITIS    IVANANS     263
 1981-05-21  Other            3     KRISTIAN  KUDROC      255
 1980-08-08  Other            3     STEVE     MACINTYRE   265
 1973-08-21  Other            7     STEVE     MCKENNA     252
 1982-09-26  Other            3     JOHN      SCOTT       258

SELECT s.PLAYERID, s.year, s.stint, s.teamid,
       (SELECT name FROM teams t WHERE t.teamid = s.teamid and t.year = s.year)
  FROM scoring s
  WHERE s.YEAR = 2011 AND s.teamid like 'B%';

 PLAYERID  YEAR  STINT  TEAMID
 --------- ----- ------ ------- --------------
 adamlu01  2011    1      BUF   Buffalo Sabres
 bartkma01 2011    1      BOS   Boston Bruins
 bergepa01 2011    1      BOS   Boston Bruins
 boychjo01 2011    1      BOS   Boston Bruins
 boyesbr01 2011    1      BUF   Buffalo Sabres
 brenntj01 2011    1      BUF   Buffalo Sabres
...

SELECT s.PLAYERID, s.year, s.stint, s.teamid,
       (SELECT name FROM teams t WHERE t.teamid = s.teamid and t.year = s.year) AS TEAM
  FROM scoring s
  WHERE s.YEAR = 2011 AND s.teamid like 'B%';

PLAYERID  YEAR  STINT  TEAMID       TEAM
 --------- ----- ------ ------- --------------
 adamlu01  2011    1      BUF   Buffalo Sabres
 bartkma01 2011    1      BOS   Boston Bruins
 bergepa01 2011    1      BOS   Boston Bruins
 boychjo01 2011    1      BOS   Boston Bruins
...

SELECT a.team_year, a.team_teamid, a.team_conf, a.team_div
  FROM (SELECT t.year AS team_year,t.teamid AS team_teamid, t.rank AS team_rank ,
                t.conferenceid AS team_conf, t.divisionid AS team_div
          FROM teams t) a
 WHERE a.team_rank = 1 AND a.team_year = 2011;

 TEAM_YEAR  TEAM_TEAMID  TEAM_CONF  TEAM_DIV
 ---------- ------------ ---------- ---------
    2011        BOS          EC        NE
    2011        FLO          EC        SE
    2011        NYR          EC        AT
    2011        PHO          WC        PC
    2011        STL          WC        CE
    2011        VAN          WC        NW

SELECT s.playerid, s.teamid, t.name, t.conferenceid, t.divisionid, t.rank
FROM scoring s
  INNER JOIN teams t ON s.year = t.year AND s.teamid = t.teamid and t.rank =1
WHERE s.position = 'G' AND t.year = 2011;

 PLAYERID  TEAMID        NAME        CONFERENCEID  DIVISIONID  RANK
 --------- ------- ----------------- ------------- ----------- -----
 bironma01   NYR   New York Rangers       EC           AT        1
 clemmsc01   FLO   Florida Panthers       EC           SE        1
 elliobr01   STL   St. Louis Blues        WC           CE        1
 fostebr01   FLO   Florida Panthers       EC           SE        1
 halakja01   STL   St. Louis Blues        WC           CE        1
...

SELECT p.firstname,p.lastname,p.firstnhl,p.lastnhl,s.teamid,s.stint,gamesplayed
FROM players p
  LEFT OUTER JOIN scoring s ON p.playerid = s.playerid
                           AND p.firstnhl = s.year
                           AND s.position = 'G'
WHERE p.firstnhl = 2011 AND s.gamesplayed IS NOT NULL ORDER BY LASTNAME,FIRSTNAME,TEAMID;

 FIRSTNAME  LASTNAME  FIRSTNHL  LASTNHL  TEAMID  STINT  GAMESPLAYED
 ---------- --------- --------- -------- ------- ------ ------------
   Brian    Foster      2011      2011     FLO     1          1
   Matt     Hackett     2011      2011     MIN     1         12
   Shawn    Hunwick     2011      2011     CBS     1          1
   Leland   Irving      2011      2011     CAL     1          7
   Mike     Murphy      2011      2011     CAR     1          2
   Anders   Nilsson     2011      2011     NYI     1          4
   Jussi    Rynnas      2011      2011     TOR     1          2
   Ben      Scrivens    2011      2011     TOR     1         12
   Iiro     Tarkki      2011      2011     AND     1          1
   Brad     Thiessen    2011      2011     PIT     1          5
   Allen    York        2011      2011     CBS     1         11

SELECT p.playerid, p.firstname, p.lastname FROM players p
 WHERE EXISTS (SELECT 'x' FROM scoring s WHERE s.playerid = p.playerid
                                 AND s.position = 'G' AND s.year = 2011);
PLAYERID    FIRSTNAME         LASTNAME
 --------- -------------- ------------------
 andercr01 Craig          Anderson
 auldal01  Alex           Auld
 bachmri01 Richard        Bachman
 backsni01 Niklas         Backstrom
 bernijo01 Jonathan       Bernier
 bironma01 Martin         Biron
 bishobe01 Ben            Bishop
 bobrose01 Sergei         Bobrovsky
...

SELECT p.playerid, p.firstname, p.lastname FROM players p
 WHERE p.playerid IN (SELECT s.playerid FROM scoring s
                         WHERE s.position = 'G' AND s.year = 2011);
 PLAYERID    FIRSTNAME         LASTNAME
 --------- -------------- ------------------
 andercr01 Craig          Anderson
 auldal01  Alex           Auld
 bachmri01 Richard        Bachman
 backsni01 Niklas         Backstrom
 bernijo01 Jonathan       Bernier
 bironma01 Martin         Biron
 bishobe01 Ben            Bishop
 bobrose01 Sergei         Bobrovsky
...

SELECT s.year, s.teamid, count(*) AS NUMBER_OF_PLAYERS
   FROM scoring s
  WHERE s.year = 2011
  GROUP BY s.year, s.teamid;
 YEAR  TEAMID  NUMBER_OF_PLAYERS
 ----- ------- ------------------
 2011    MTL           34
 2011    CAL           40
 2011    CBS           42
 2011    PHO           35
 2011    NYI           36
...

SELECT s.year, s.teamid, count(*) AS NUMBER_OF_PLAYERS
   FROM scoring s
  WHERE s.year = 2011
  GROUP BY s.year, s.teamid HAVING count(*) > 40;
 YEAR  TEAMID  NUMBER_OF_PLAYERS
 ----- ------- ------------------
 2011    CBS           42
 2011    MIN           47
 2011    FLO           41

SELECT playerid, firstname, lastname, height, weight
  FROM players
  WHERE playerid = 'macinst01'
UNION ALL
SELECT playerid, firstname, lastname, height, weight
  FROM players
  WHERE weight > 260;

 PLAYERID  FIRSTNAME  LASTNAME  HEIGHT  WEIGHT
 --------- ---------- --------- ------- -------

 macinst01   Steve    MacIntyre   78      265
 byfugdu01   Dustin   Byfuglien   75      265
 ivanara01   Raitis   Ivanans     75      263
 macinst01   Steve    MacIntyre   78      265


SELECT playerid, firstname, lastname, height, weight
  FROM players
  WHERE playerid = 'macinst01'
UNION DISTINCT
SELECT playerid, firstname, lastname, height, weight
  FROM players
  WHERE weight > 260;

 PLAYERID  FIRSTNAME  LASTNAME  HEIGHT  WEIGHT
 --------- ---------- --------- ------- -------

 macinst01   Steve    MacIntyre   78      265
 byfugdu01   Dustin   Byfuglien   75      265
 ivanara01   Raitis   Ivanans     75      263

SELECT playerid, firstname, lastname, height, weight
  FROM players
  WHERE weight > 260
 ORDER BY lastname;

 PLAYERID  FIRSTNAME  LASTNAME  HEIGHT  WEIGHT
 --------- ---------- --------- ------- -------

 byfugdu01   Dustin   Byfuglien   75      265
 ivanara01   Raitis   Ivanans     75      263
 macinst01   Steve    MacIntyre   78      265

SELECT playerid, firstname, lastname AS lname, height, weight
     FROM players
    WHERE playerid like 'zo%'
UNION ALL
   SELECT playerid, firstname, lastname AS lname, height, weight
     FROM players
    WHERE playerid like 'un%'
ORDER BY firstname;

 PLAYERID  FIRSTNAME     LNAME    HEIGHT  WEIGHT
 --------- ---------- ----------- ------- -------

 ungerga01   Garry    Unger         71      170
 zolniha01   Harry    Zolnierczyk   71      160
 zoborma01   Marty    Zoborosky     70      180
 underma01   Matt     Underhill     74      195
 zombori01   Rick     Zombo         73      202

SELECT * FROM
  (SELECT playerid, firstname, lastname AS lname, height, weight
     FROM players
    WHERE playerid like 'zo%'
    ORDER BY firstname) a
UNION
SELECT * FROM
  (SELECT playerid, firstname, lastname AS lname, height, weight
     FROM players
    WHERE playerid like 'un%'
    ORDER BY firstname) b;

 PLAYERID  FIRSTNAME     LNAME    HEIGHT  WEIGHT
 --------- ---------- ----------- ------- -------

 zolniha01   Harry    Zolnierczyk   71      160
 zoborma01   Marty    Zoborosky     70      180
 zombori01   Rick     Zombo         73      202
 ungerga01   Garry    Unger         71      170
 underma01   Matt     Underhill     74      195

DROP TABLE tst_table IF EXISTS;
CREATE TABLE tst_table (column1 VARCHAR(12));
INSERT INTO tst_table VALUES ('abc'),('ABC');
INSERT INTO tst_table VALUES ('bcd'),('BCD');
SELECT * FROM tst_table ORDER BY column1;
 COLUMN1
 --------
   ABC
   BCD
   abc
   bcd

 SELECT * FROM tst_table ORDER BY column1 COLLATE case_insensitive;
 COLUMN1
 --------
   abc
   ABC
   bcd
   BCD

SELECT * FROM tst_table ORDER BY column1 COLLATE case_insensitive DESC;
 COLUMN1
 --------
   bcd
   BCD
   abc
   ABC

SELECT
        name AS teamname,
        conferenceid,
        divisionid,
        rank,
        wins||'-'||losses||'-'||ties AS RECORD
  FROM teams
  WHERE year = 2009
  ORDER BY rank, conferenceid, divisionid LIMIT 10;
      TEAMNAME       CONFERENCEID  DIVISIONID  RANK  RECORD
 ------------------- ------------- ----------- ----- -------
 New Jersey Devils        EC           AT        1   48-27-0
 Buffalo Sabres           EC           NE        1   45-27-0
 Washington Capitals      EC           SE        1   54-15-0
 Chicago Blackhawks       WC           CE        1   52-22-0
 Vancouver Canucks        WC           NW        1   49-28-0
 San Jose Sharks          WC           PC        1   51-20-0
 Pittsburgh Penguins      EC           AT        2   47-28-0
 Ottawa Senators          EC           NE        2   44-32-0
 Atlanta Thrashers        EC           SE        2   35-34-0
 Detroit Red Wings        WC           CE        2   44-24-0

SELECT
        name AS teamname,
        conferenceid,
        divisionid,
        rank,
        wins||'-'||losses||'-'||ties AS RECORD
  FROM teams
  WHERE year = 2009
  ORDER BY rank, conferenceid, divisionid FETCH FIRST 3;
      TEAMNAME       CONFERENCEID  DIVISIONID  RANK  RECORD
 ------------------- ------------- ----------- ----- -------
 New Jersey Devils        EC           AT        1   48-27-0
 Buffalo Sabres           EC           NE        1   45-27-0
 Washington Capitals      EC           SE        1   54-15-0

SELECT
        name AS teamname,
        conferenceid,
        divisionid,
        rank,
        wins||'-'||losses||'-'||ties AS RECORD
  FROM teams
  WHERE year = 2009
  ORDER BY rank, conferenceid, divisionid LIMIT 10 OFFSET 2;
      TEAMNAME       CONFERENCEID  DIVISIONID  RANK  RECORD
 ------------------- ------------- ----------- ----- -------
 Washington Capitals      EC           SE        1   54-15-0
 Chicago Blackhawks       WC           CE        1   52-22-0
 Vancouver Canucks        WC           NW        1   49-28-0
 San Jose Sharks          WC           PC        1   51-20-0
 Pittsburgh Penguins      EC           AT        2   47-28-0
 Ottawa Senators          EC           NE        2   44-32-0
 Atlanta Thrashers        EC           SE        2   35-34-0
 Detroit Red Wings        WC           CE        2   44-24-0
 Colorado Avalanche       WC           NW        2   43-30-0
 Phoenix Coyotes          WC           PC        2   50-25-0

SELECT
        name AS teamname,
        conferenceid,
        divisionid,
        rank,
        wins||'-'||losses||'-'||ties AS RECORD
  FROM teams
  WHERE year = 2009
  ORDER BY rank, conferenceid, divisionid LIMIT 2,10;
      TEAMNAME       CONFERENCEID  DIVISIONID  RANK  RECORD
 ------------------- ------------- ----------- ----- -------
 Washington Capitals      EC           SE        1   54-15-0
 Chicago Blackhawks       WC           CE        1   52-22-0
 Vancouver Canucks        WC           NW        1   49-28-0
 San Jose Sharks          WC           PC        1   51-20-0
 Pittsburgh Penguins      EC           AT        2   47-28-0
 Ottawa Senators          EC           NE        2   44-32-0
 Atlanta Thrashers        EC           SE        2   35-34-0
 Detroit Red Wings        WC           CE        2   44-24-0
 Colorado Avalanche       WC           NW        2   43-30-0
 Phoenix Coyotes          WC           PC        2   50-25-0

SELECT
        name AS teamname,
        conferenceid,
        divisionid,
        rank,
        wins||'-'||losses||'-'||ties AS RECORD
  FROM teams
  WHERE year = 2009
  ORDER BY rank, conferenceid, divisionid OFFSET 2 FETCH NEXT 4;
      TEAMNAME       CONFERENCEID  DIVISIONID  RANK  RECORD
 ------------------- ------------- ----------- ----- -------
 Washington Capitals      EC           SE        1   54-15-0
 Chicago Blackhawks       WC           CE        1   52-22-0
 Vancouver Canucks        WC           NW        1   49-28-0
 San Jose Sharks          WC           PC        1   51-20-0

/* Create the views to be used for this example */
CREATE VIEW vw_players AS SELECT playerid,firstname,lastname FROM players;
CREATE VIEW vw_scoring AS SELECT playerid,year,stint,teamid,position FROM scoring;

/* SELECT using JOIN of the above two views. The first three columns are from */
   vw_players, the next five columns are from vw_scoring */
SELECT * FROM vw_players p JOIN vw_scoring s ON p.playerid = s.playerid
 WHERE s.position = 'G' AND s.year = 1975 ORDER BY p.playerid;
 PLAYERID  FIRSTNAME   LASTNAME   PLAYERID  YEAR  STINT  TEAMID  POSITION
 --------- ---------- ----------- --------- ----- ------ ------- ---------
 abrahch01 Christer   Abrahamsson abrahch01 1975    1      NEW       G
 aubryse01 Serge      Aubry       aubryse01 1975    1      CIN       G
 belanyv01 Yves       Belanger    belanyv01 1975    1      STL       G
 belhumi01 Michel     Belhumeur   belhumi01 1975    1      WAS       G
...

/* Now JOIN with USING on common column playerid. The result set column list is
   reduced by one column, the playerid, which is the column specified as USING. USING
   columns appear only once in the column list of the result set. The remaining columns
   are ordered the same as they appear in the tables. The columns from the first table
   listed in the JOIN appear first. */
SELECT * FROM vw_players p JOIN vw_scoring s USING (playerid)
 WHERE s.position = 'G' AND s.year = 1975 ORDER BY p.playerid;
 PLAYERID  FIRSTNAME   LASTNAME   YEAR  STINT  TEAMID  POSITION
 --------- ---------- ----------- ----- ------ ------- ---------
 abrahch01 Christer   Abrahamsson 1975    1      NEW       G
 aubryse01 Serge      Aubry       1975    1      CIN       G
 belanyv01 Yves       Belanger    1975    1      STL       G
 belhumi01 Michel     Belhumeur   1975    1      WAS       G
...

CREATE TABLE foo (a INTEGER, b INTEGER, c INTEGER, d INTEGER);

CREATE INDEX foo_a ON foo(a);
CREATE INDEX foo_b ON foo(b);
CREATE INDEX foo_c ON foo(c);
CREATE INDEX foo_d ON foo(d);

-- Choose a specified index; the optimizer has the freedom to choose
-- a streamed / bitmap scan over the specified index.
SELECT /*+ USE_INDEX(foo, foo_a) */ *
    FROM foo WHERE a > 100 ORDER BY a;

-- Choose a streamed index
SELECT /*+ USE_INDEX(foo, KEY_ORDERED foo_a) */ *
    FROM foo WHERE a > 100 ORDER BY a;

-- Choose a bitmap index
SELECT /*+ USE_INDEX(foo, ID_ORDERED foo_a) */ *
    FROM foo WHERE a > 100 ORDER BY a;

-- Choose a streamed index
SELECT /*+ USE_INDEX(foo, KEY_ORDERED foo_a) */ *
    FROM foo WHERE a > 100 AND b > 100 ORDER BY a;

-- Choose a bitmap index
SELECT /*+ USE_INDEX (foo, ID_ORDERED foo_b) */ *
    FROM foo WHERE a > 100 AND b > 100 ORDER BY a;

-- Consider: streamed foo_a, bitmap foo_b
SELECT /*+ USE_INDEX(foo, KEY_ORDERED foo_a, ID_ORDERED foo_b) */ *
    FROM foo WHERE a > 100 AND b > 100 ORDER BY a;

-- Consider only streamed scans over foo_a and foo_b
SELECT /*+ USE_INDEX(foo, KEY_ORDERED foo_a, KEY_ORDERED foo_b) */ *
    FROM foo WHERE a > 100 AND b > 100 ORDER BY a;

-- Consider: bitmap foo_a, bitmap foo_b, BitmapAND(bitmap foo_a, bitmap foo_b)
SELECT /*+ USE_INDEX(foo, ID_ORDERED foo_a, ID_ORDERED foo_b) */ *
    FROM foo WHERE a > 100 AND b > 100 ORDER BY a;

-- Let the optimizer choose an optimal IIU plan over foo_a and foo_b
SELECT /*+ USE_INDEX(foo, foo_a, foo_b) */ *
    FROM foo WHERE a > 100 AND b > 100 ORDER BY a;

-- Use a streamed index (even though there is no group by/ORDER BY)
SELECT /*+ USE_INDEX(foo, KEY_ORDERED foo_a) */ *
    FROM foo WHERE a > 100 AND b > 100;

-- In the context of joins
CREATE TABLE bar (a INTEGER, b INTEGER, c INTEGER, d INTEGER);

SELECT /*+ USE_INDEX(foo, foo_a) */ *
    FROM foo, bar WHERE foo.a = bar.b AND foo.a > 100 AND bar.b > 100;

-- In the context of joins (with indexes ON both tables)
CREATE INDEX bar_b ON bar(b);

SELECT /*+ USE_INDEX(foo, foo_a), USE_INDEX(bar, bar_b) */ *
    FROM foo, bar WHERE foo.a = bar.b AND foo.a > 100 AND bar.b > 100;

-- With joins and a group by (specify both ordered and index hints)
SELECT /*+ ordered, USE_INDEX(foo, KEY_ORDERED foo_a) */ foo.a
    FROM foo, bar WHERE foo.a = bar.b AND foo.a > 100 AND bar.b > 100
    GROUP BY foo.a;

-- With joins and an ORDER BY (specify both ordered and index hints)
SELECT /*+ ordered, USE_INDEX (foo, KEY_ORDERED foo_a) */ *
    FROM foo, bar WHERE foo.a = bar.b AND foo.a > 100 AND bar.b > 100
    ORDER BY foo.a;

CREATE TABLE foo (a INTEGER, b INTEGER, c INTEGER, d INTEGER);

CREATE INDEX foo_a ON foo(a);
CREATE INDEX foo_b ON foo(b);
CREATE INDEX foo_c ON foo(c);
CREATE INDEX foo_d ON foo(d);

-- Do not choose index 'foo_a'
SELECT /*+ SKIP_INDEX(foo, foo_a) */ *
    FROM foo WHERE a > 100 ORDER BY a;

-- Do not choose a streamed scan over foo_a; the optimizer can choose a
-- bitmap scan over foo_a.
SELECT /*+ SKIP_INDEX(foo, KEY_ORDERED foo_a) */ *
    FROM foo WHERE a > 100 ORDER BY a;

-- Do not choose a bitmap scan over foo_a; the optimizer can choose a
-- streamed scan over foo_a
SELECT /*+ SKIP_INDEX(foo, ID_ORDERED foo_a) */ *
    FROM foo WHERE a > 100 ORDER BY a;

-- Equivalent to /*+ SKIP_INDEX(foo foo_a) */
SELECT /*+ SKIP_INDEX(foo, KEY_ORDERED foo_a, ID_ORDERED foo_a) */ *
    FROM foo WHERE a > 100 ORDER BY a;

-- Do not consider streamed scans over foo_a and foo_b
SELECT /*+ SKIP_INDEX(foo, KEY_ORDERED foo_a, ID_ORDERED foo_b) */ *
    FROM foo WHERE a > 100 AND b > 100 ORDER BY a;

-- In the context of joins
SELECT /*+ SKIP_INDEX(foo, foo_a) */ *
    FROM foo, bar WHERE foo.a = bar.b AND foo.a > 100 AND bar.b > 100;

-- In the context of joins; do not use an index only ON the specified instance of foo
SELECT /*+ SKIP_INDEX(foo_inst_a, foo_a)  */ *
    FROM foo as foo_inst_a, foo as foo_inst_b
    WHERE foo_inst_a.a = foo_inst_b.a AND foo_inst_a.a > 50 AND foo_inst_b.a < 100;

-- In the context of joins
SELECT /*+ ordered, SKIP_INDEX(foo, KEY_ORDERED foo_a) */ foo.a
    FROM foo, bar WHERE foo.a = bar.b AND foo.a > 100 AND bar.b > 100
    GROUP BY foo.a;

-- In the context of joins
SELECT /*+ ordered, SKIP_INDEX(foo, foo_a) */ *
    FROM foo, bar WHERE foo.a = bar.b AND foo.a > 100 AND bar.b > 100
    ORDER BY foo.a;

DROP TABLE IF EXISTS hockey_dup;
CREATE TABLE hockey_dup (
    id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
    number INTEGER,
    name STRING,
    position STRING );
INSERT INTO hockey_dup (number,name,position)
    SELECT number,name,position
    FROM hockey WHERE name like 'A%' or name like 'D%';
SELECT id,number,name,position,rownum() FROM hockey_dup;
 ID  NUMBER        NAME        POSITION  ROWNUM
 --- ------- ----------------- --------- -------

  1    46    DAVID KREJCI       Forward     1
  2    20    DANIEL PAILLE      Forward     2
  3    21    ANDREW FERENCE     Defense     3
  4    27    DOUGIE HAMILTON    Defense     4
  5    45    AARON JOHNSON      Defense     5
  6    54    ADAM MCQUAID       Defense     6
  7    44    DENNIS SEIDENBERG  Defense     7
  8    35    ANTON KHUDOBIN     Goalie      8

/* delete a row which will reset what the ROWNUM() function returns */
DELETE FROM hockey_dup WHERE id = 6;
SELECT id,number,name,position,rownum() FROM hockey_dup;
 ID  NUMBER        NAME        POSITION  ROWNUM
 --- ------- ----------------- --------- -------

  1    46    DAVID KREJCI       Forward     1
  2    20    DANIEL PAILLE      Forward     2
  3    21    ANDREW FERENCE     Defense     3
  4    27    DOUGIE HAMILTON    Defense     4
  5    45    AARON JOHNSON      Defense     5
  7    44    DENNIS SEIDENBERG  Defense     6
  8    35    ANTON KHUDOBIN     Goalie      7

/* add a predicate that will change the position of some rows in the result set */
SELECT id,number,name,position,record_number,rownum()
    FROM hockey_dup WHERE name LIKE 'D%';

 ID  NUMBER        NAME        POSITION  RECORD_NUMBER  ROWNUM
 --- ------- ----------------- --------- -------------- -------

  1    46    DAVID KREJCI       Forward        1           1
  2    20    DANIEL PAILLE      Forward        2           2
  4    27    DOUGIE HAMILTON    Defense        4           3
  7    44    DENNIS SEIDENBERG  Defense        7           4

SELECT id,number,name,position,rownum()
    FROM hockey_dup WHERE name LIKE 'D%';
 ID  NUMBER        NAME        POSITION  ROWNUM
 --- ------- ----------------- --------- -------

  1    46    DAVID KREJCI       Forward     1
  2    20    DANIEL PAILLE      Forward     2
  4    27    DOUGIE HAMILTON    Defense     3
  7    44    DENNIS SEIDENBERG  Defense     4

/* since the rownum() function returns its identifier before any ORDER BY,
   the rownum() value is not always sequential */

SELECT id, number,name,position,rownum()
    FROM hockey_dup WHERE name LIKE 'D%' ORDER BY name;
 ID  NUMBER        NAME        POSITION  ROWNUM
 --- ------- ----------------- --------- -------

  2    20    DANIEL PAILLE      Forward     2
  1    46    DAVID KREJCI       Forward     1
  7    44    DENNIS SEIDENBERG  Defense     4
  4    27    DOUGIE HAMILTON    Defense     3