Feeds:
Posts
Comments

A while ago, I shared my Oracle Advanced SQL presentation in this blog along with demo scripts I used in the presentation.

On 22.03.2012 in TROUG’s BI/DW SIG Event I have presented a new version of my demo: Advanced SQL in Oracle 11g. In this version examples are different (and they are in English) and the focus is on Oracle Database 11g new features. Also I have made some improvements by including regular expressions and flashback query.

To run following demo you will need an Oracle Database 11g and a user with CONNECT, RESOURCE, and SELECT ANY DICTIONARY privileges. You can use following code to create a demo user and grant necessary privileges.

CREATE USER demo
  IDENTIFIED BY demo;

GRANT CONNECT,
      RESOURCE TO demo;

GRANT SELECT ANY DICTIONARY TO demo;

Introduction

Every football team has 11 players, but not all of them have Messi, Iniesta or Xavi – only Barcelona does. If these three sat on the bench while some other players were playing on the pitch, it would be, well, ridiculous. If Barcelona’s board has the money to transfer good players, they want them to play. This is simple business.

As in football, if you have Oracle Database, you would want to exploit features it provides. This demo is about some of those features.

British Royal Family

I will start the demo by creating the first example table. The table is HOUSE_OF_WINDSOR and it will contain information about British Royal Family. I will create a unique index on ID column to create a primary key and PARENT_ID will be a self-referencing column.

CREATE TABLE house_of_windsor (
  ID         INTEGER,
  parent_id  INTEGER,
  NAME       VARCHAR2(64),
  gender     VARCHAR2(1),
  birth_year NUMBER
);

CREATE UNIQUE INDEX idx_house_of_windsor
  ON house_of_windsor (ID);

ALTER TABLE house_of_windsor
  ADD CONSTRAINT pk_house_of_windsor
  PRIMARY KEY (ID)
  USING INDEX idx_house_of_windsor;

ALTER TABLE house_of_windsor
  ADD CONSTRAINT fk_house_of_windsor
  FOREIGN KEY (parent_id)
  REFERENCES house_of_windsor (ID);

Now, I will insert all British Royal Family members into this table.

INSERT INTO house_of_windsor
            (ID, parent_id, NAME, gender, birth_year
            )
     VALUES (1, NULL, 'Elizabeth II', 'F', 1926
            );

INSERT INTO house_of_windsor
            (ID, parent_id, NAME, gender, birth_year
            )
     VALUES (2, 1, 'Charles, Prince of Wales', 'M', 1948
            );

INSERT INTO house_of_windsor
            (ID, parent_id, NAME, gender, birth_year
            )
     VALUES (3, 1, 'Anne, Princess Royal', 'F', 1950
            );

INSERT INTO house_of_windsor
            (ID, parent_id, NAME, gender, birth_year
            )
     VALUES (4, 1, 'Prince Andrew, Duke of York', 'M', 1960
            );

INSERT INTO house_of_windsor
            (ID, parent_id, NAME, gender, birth_year
            )
     VALUES (5, 1, 'Prince Edward, Earl of Wessex', 'M', 1964
            );

INSERT INTO house_of_windsor
            (ID, parent_id, NAME, gender, birth_year
            )
     VALUES (6, 3, 'Peter Phillips', 'M', 1977
            );

INSERT INTO house_of_windsor
            (ID, parent_id, NAME, gender, birth_year
            )
     VALUES (7, 3, 'Zara Phillips', 'F', 1981
            );

INSERT INTO house_of_windsor
            (ID, parent_id, NAME, gender, birth_year
            )
     VALUES (8, 2, 'Prince William, Duke of Cambridge', 'M', 1982
            );

INSERT INTO house_of_windsor
            (ID, parent_id, NAME, gender, birth_year
            )
     VALUES (9, 2, 'Prince Harry of Wales', 'M', 1984
            );

INSERT INTO house_of_windsor
            (ID, parent_id, NAME, gender, birth_year
            )
     VALUES (10, 4, 'Princess Beatrice of York', 'F', 1988
            );

INSERT INTO house_of_windsor
            (ID, parent_id, NAME, gender, birth_year
            )
     VALUES (11, 4, 'Princess Eugenie of York', 'F', 1990
            );

INSERT INTO house_of_windsor
            (ID, parent_id, NAME, gender, birth_year
            )
     VALUES (12, 5, 'Lady Louise Windsor', 'F', 2003
            );

INSERT INTO house_of_windsor
            (ID, parent_id, NAME, gender, birth_year
            )
     VALUES (13, 5, 'James, Viscount Severn', 'M', 2007
            );

INSERT INTO house_of_windsor
            (ID, parent_id, NAME, gender, birth_year
            )
     VALUES (14, 6, 'Savannah Phillips', 'F', 2010
            );

COMMIT ;

Obviously, this table represents the family tree. You can find a visual demonstration here (with the exception of Savannah Philips who is daughter of Peter Philips).

Flashback Query

Now, I will give a very brief demonstration of flashback query.

SELECT *
  FROM house_of_windsor;

SELECT current_scn
  FROM v$database;

DELETE FROM house_of_windsor;

COMMIT ;

SELECT *
  FROM house_of_windsor;

SELECT *
  FROM house_of_windsor AS OF SCN &scn;

CREATE TABLE house_of_windsor_backup AS
SELECT *
  FROM house_of_windsor AS OF SCN &scn;

DROP TABLE house_of_windsor;

ALTER TABLE house_of_windsor_backup
  RENAME TO house_of_windsor;

SELECT *
  FROM house_of_windsor;

In lines 1-2 the table is queried. You should see that the data is in the table. Lines 4-5 is to query the current SCN (system change number) of Orace Database. After noting down the current SCN, in lines 7-12 the data in the table is deleted and the table is queried to see it is empty. AS OF SCN syntax is used to query the table in lines 14-15. Note the table data with the specific SCN (we noted down earlier) is fetched. In lines 17-27 the table is created with the original data to go on with the demo.

CONNECT BY LEVEL Clause

One would say that flashback query is a nice idea but it’s perfectly sensible to doubt it’s performance. Well, we always need a big table to test some feature’s performance. So let’s create a big table by adding ALL_OBJECTS to itself 10 times.

CREATE TABLE t1 AS
SELECT *
  FROM all_objects
UNION ALL
SELECT *
  FROM all_objects
UNION ALL
SELECT *
  FROM all_objects
UNION ALL
SELECT *
  FROM all_objects
UNION ALL
SELECT *
  FROM all_objects
UNION ALL
SELECT *
  FROM all_objects
UNION ALL
SELECT *
  FROM all_objects
UNION ALL
SELECT *
  FROM all_objects
UNION ALL
SELECT *
  FROM all_objects
UNION ALL
SELECT *
  FROM all_objects;

DROP TABLE t1;

SELECT     LEVEL
      FROM DUAL
CONNECT BY LEVEL <= 10;

CREATE TABLE t1 AS
SELECT *
  FROM all_objects
CONNECT BY LEVEL <= 10;

SELECT    'to_timestamp('''
       || TO_CHAR (SYSDATE, 'dd.mm.yyyy hh24:mi:ss')
       || ''', ''dd.mm.yyyy hh24:mi:ss'')'
  FROM DUAL;

DELETE FROM t1;

COMMIT ;

SELECT COUNT (*)
  FROM t1;

SELECT COUNT (*)
  FROM t1 AS OF TIMESTAMP &timestamp;

DROP TABLE t1;

In lines 1-30 using UNION ALL clause a table with 10 ALL_OBJECTS data is created. But there is a very much simpler way to do this! After dropping the table on line 32, lines 34-36 shows how CONNECT BY LEVEL clause works. If you use it in a statement like in lines 38-41 you will get the same result with the previous CREATE TABLE statement. Lines 43-56 demonstrate how to use AS OF TIMESTAMP clause (which is again flashback query). The performance comparison is beyond the scope of this demo, so I will skip it.

Though, one thing must be said about flashback query. There are a lot of limitations and restrictions to Oracle’s flashback query feature. You can find a useful reading here. Despite it can be a life-saving tool in some critical times it is not recommended to use this feature as a part of your application.

CONNECT BY PRIOR Clause

SELECT *
  FROM house_of_windsor;

WITH temp (ID, parent_id, NAME, gender, birth_year) AS (
  SELECT ID, parent_id, NAME, gender, birth_year
    FROM house_of_windsor
   WHERE parent_id = 3
  UNION ALL
  SELECT t1.ID, t1.parent_id, t1.NAME, t1.gender, t1.birth_year
    FROM house_of_windsor t1, temp t2
   WHERE t1.parent_id = t2.ID)
SELECT *
  FROM temp;

SELECT     NAME, gender, birth_year
      FROM house_of_windsor
START WITH parent_id IS NULL
CONNECT BY PRIOR ID = parent_id;

SELECT     NAME, gender, birth_year
      FROM house_of_windsor
START WITH parent_id IS NULL
CONNECT BY PRIOR ID = parent_id
  ORDER SIBLINGS BY gender DESC, birth_year;

SELECT     SYS_CONNECT_BY_PATH (NAME, '/') PATH, LEVEL level_
      FROM house_of_windsor
START WITH parent_id IS NULL
CONNECT BY PRIOR ID = parent_id;

DROP TABLE house_of_windsor;

Now, let’s try to find the line of succession to the British throne. (You can find a complete line of succession to the British throne here.)

In lines 1-2 we inspect the data in the HOUSE_OF_WINDSOR table. Lines 4-13 show how to query the family tree starting with root as Anne, Princess Royal, showing her children first, then her grandchildren(, and so on if there were any). This recursive usage of WITH clause is new in Oracle 11g. With the rule of declaring the names of columns of the query inside WITH clause, you can reference this temporary data inside the WITH clause. What we do in this spesific query is to get children of Anne, Princess Royal and go on querying recursively until somebody has no child.

This is the way you can use recursive querying of a self-referencing table in other databases as well. In Oracle, we have a very much simpler and elegant way of doing this. In lines 15-18 this way is shown. START WITH clause is to tell where to start querying (here we start with Elizabeth II, who does not have any parent in the table). CONNECT BY clause works with a PRIOR and a default side. You can read it like this: Take the ID’s you have already and look who has them as PARENT_ID and insert them to the result as children. Do this recursively.

British monarchy has the following system (in brief) for line of succession: The system works depth-first (Prince William, Duke of Cambridge follows his father Charles, Prince of Wales). Older has priority over young (Prince William, Duke of Cambridge has priority over his younger brother Prince Harry of Wales). Males have priority over females (Even if he is younger than her, Prince Andrew, Duke of York has priority over Anne, Princess Royal).

Lines 20-24 shows the implementation of this algorithm. Here, ORDER SIBLINGS BY clause helps to, well, order siblings by some criteria. Depth-first-search is just default for CONNECT BY clause. This is only good luck for this problem.

In line 31 we drop the HOUSE_OF_WINDSOR table that we don’t need any more.

Football

Well, we started with British Monarchy, why not go on with a British game? Our second example table is FOOTBALL which includes some data about Turkey’s biggest 3 football clubs: Beşiktaş, Fenerbahçe, and Galatasaray. Table includes data about ranks, points, goals for and goals against of these 3 clubs in the last 10 years. You can find the necesseary scripts to create the table and insert data below.

CREATE TABLE football (
  team          VARCHAR2(64),
  season        VARCHAR2(8),
  RANK          INTEGER,
  points        INTEGER,
  goals_for     INTEGER,
  goals_against INTEGER);

INSERT INTO football
            (team, season, RANK, points, goals_for, goals_against
            )
     VALUES ('Beşiktaş', '2010/11', 5, 54, 53, 36
            );

INSERT INTO football
            (team, season, RANK, points, goals_for, goals_against
            )
     VALUES ('Beşiktaş', '2009/10', 4, 64, 47, 25
            );

INSERT INTO football
            (team, season, RANK, points, goals_for, goals_against
            )
     VALUES ('Beşiktaş', '2008/09', 1, 71, 60, 30
            );

INSERT INTO football
            (team, season, RANK, points, goals_for, goals_against
            )
     VALUES ('Beşiktaş', '2007/08', 3, 73, 58, 32
            );

INSERT INTO football
            (team, season, RANK, points, goals_for, goals_against
            )
     VALUES ('Beşiktaş', '2006/07', 2, 61, 43, 32
            );

INSERT INTO football
            (team, season, RANK, points, goals_for, goals_against
            )
     VALUES ('Beşiktaş', '2005/06', 3, 54, 52, 39
            );

INSERT INTO football
            (team, season, RANK, points, goals_for, goals_against
            )
     VALUES ('Beşiktaş', '2004/05', 4, 69, 70, 39
            );

INSERT INTO football
            (team, season, RANK, points, goals_for, goals_against
            )
     VALUES ('Beşiktaş', '2003/04', 3, 62, 65, 45
            );

INSERT INTO football
            (team, season, RANK, points, goals_for, goals_against
            )
     VALUES ('Beşiktaş', '2002/03', 1, 85, 63, 21
            );

INSERT INTO football
            (team, season, RANK, points, goals_for, goals_against
            )
     VALUES ('Beşiktaş', '2001/02', 3, 62, 69, 39
            );

INSERT INTO football
            (team, season, RANK, points, goals_for, goals_against
            )
     VALUES ('Fenerbahçe', '2010/11', 1, 82, 84, 34
            );

INSERT INTO football
            (team, season, RANK, points, goals_for, goals_against
            )
     VALUES ('Fenerbahçe', '2009/10', 2, 74, 61, 28
            );

INSERT INTO football
            (team, season, RANK, points, goals_for, goals_against
            )
     VALUES ('Fenerbahçe', '2008/09', 4, 61, 60, 36
            );

INSERT INTO football
            (team, season, RANK, points, goals_for, goals_against
            )
     VALUES ('Fenerbahçe', '2007/08', 2, 73, 72, 37
            );

INSERT INTO football
            (team, season, RANK, points, goals_for, goals_against
            )
     VALUES ('Fenerbahçe', '2006/07', 1, 70, 65, 31
            );

INSERT INTO football
            (team, season, RANK, points, goals_for, goals_against
            )
     VALUES ('Fenerbahçe', '2005/06', 2, 81, 90, 34
            );

INSERT INTO football
            (team, season, RANK, points, goals_for, goals_against
            )
     VALUES ('Fenerbahçe', '2004/05', 1, 80, 77, 24
            );

INSERT INTO football
            (team, season, RANK, points, goals_for, goals_against
            )
     VALUES ('Fenerbahçe', '2003/04', 1, 76, 82, 41
            );

INSERT INTO football
            (team, season, RANK, points, goals_for, goals_against
            )
     VALUES ('Fenerbahçe', '2002/03', 6, 51, 55, 42
            );

INSERT INTO football
            (team, season, RANK, points, goals_for, goals_against
            )
     VALUES ('Fenerbahçe', '2001/02', 2, 75, 70, 31
            );

INSERT INTO football
            (team, season, RANK, points, goals_for, goals_against
            )
     VALUES ('Galatasaray', '2010/11', 8, 46, 41, 46
            );

INSERT INTO football
            (team, season, RANK, points, goals_for, goals_against
            )
     VALUES ('Galatasaray', '2009/10', 3, 64, 61, 35
            );

INSERT INTO football
            (team, season, RANK, points, goals_for, goals_against
            )
     VALUES ('Galatasaray', '2008/09', 5, 61, 57, 39
            );

INSERT INTO football
            (team, season, RANK, points, goals_for, goals_against
            )
     VALUES ('Galatasaray', '2007/08', 1, 79, 64, 23
            );

INSERT INTO football
            (team, season, RANK, points, goals_for, goals_against
            )
     VALUES ('Galatasaray', '2006/07', 3, 56, 58, 37
            );

INSERT INTO football
            (team, season, RANK, points, goals_for, goals_against
            )
     VALUES ('Galatasaray', '2005/06', 1, 83, 82, 34
            );

INSERT INTO football
            (team, season, RANK, points, goals_for, goals_against
            )
     VALUES ('Galatasaray', '2004/05', 3, 76, 64, 25
            );

INSERT INTO football
            (team, season, RANK, points, goals_for, goals_against
            )
     VALUES ('Galatasaray', '2003/04', 6, 54, 56, 47
            );

INSERT INTO football
            (team, season, RANK, points, goals_for, goals_against
            )
     VALUES ('Galatasaray', '2002/03', 2, 77, 61, 27
            );

INSERT INTO football
            (team, season, RANK, points, goals_for, goals_against
            )
     VALUES ('Galatasaray', '2001/02', 1, 78, 75, 31
            );

COMMIT ;

Regular Expressions

SELECT *
  FROM football;

SELECT *
  FROM football
 WHERE season LIKE '2004%' OR season LIKE '2005%' OR season LIKE '2006%';

SELECT *
  FROM football
 WHERE REGEXP_LIKE (season, '200(4|5|6)/*');

SELECT REGEXP_SUBSTR ('demo/demo@localhost:1521:venus', '[^:]+', 1, 3) AS SID
  FROM DUAL;

Another feature of Oracle SQL is regular expressions. They can be very handy if you know how to use them.

Lines 1-2 queries the data from the table. Lines 4-6 shows how to query the table to retrieve data for years 2004, 2005, and 2006. This is the classical way. A modern and a better way is to use REGEXP_LIKE function as in lines 8-10. Anoter regular expression function REGEXP_SUBSTR is shown in lines 12-13.

PIVOT and UNPIVOT Operators

SELECT SUM (DECODE (team, 'Beşiktaş', points, 0)) AS bjk_sum_points,
       SUM (DECODE (team, 'Fenerbahçe', points, 0)) AS fb_sum_points,
       SUM (DECODE (team, 'Galatasaray', points, 0)) AS gs_sum_points
  FROM football;

SELECT   team, SUM (DECODE (season, '2010/11', points, 0)) AS s2010_11_points,
         SUM (DECODE (season, '2009/10', points, 0)) AS s2009_10_points,
         SUM (DECODE (season, '2008/09', points, 0)) AS s2008_09_points,
         SUM (DECODE (season, '2007/08', points, 0)) AS s2007_08_points,
         SUM (DECODE (season, '2006/07', points, 0)) AS s2006_07_points,
         SUM (DECODE (season, '2005/06', points, 0)) AS s2005_06_points,
         SUM (DECODE (season, '2004/05', points, 0)) AS s2004_05_points,
         SUM (DECODE (season, '2003/04', points, 0)) AS s2003_04_points,
         SUM (DECODE (season, '2002/03', points, 0)) AS s2002_03_points,
         SUM (DECODE (season, '2001/02', points, 0)) AS s2001_02_points
    FROM football
GROUP BY team;

SELECT *
  FROM (SELECT team, points, season
          FROM football)
       PIVOT (SUM(points) AS points FOR (season) IN ('2010/11' AS s2010_11,
                                                     '2009/10' AS s2009_10,
                                                     '2008/09' AS s2008_09,
                                                     '2007/08' AS s2007_08,
                                                     '2006/07' AS s2006_07,
                                                     '2005/06' AS s2005_06,
                                                     '2004/05' AS s2004_05,
                                                     '2003/04' AS s2003_04,
                                                     '2002/03' AS s2002_03,
                                                     '2001/02' AS s2001_02));

CREATE TABLE t1 AS
SELECT *
  FROM (SELECT team, points, season
          FROM football)
       PIVOT (SUM(points) AS points FOR (season) IN ('2010/11' AS s2010_11,
                                                     '2009/10' AS s2009_10,
                                                     '2008/09' AS s2008_09,
                                                     '2007/08' AS s2007_08,
                                                     '2006/07' AS s2006_07,
                                                     '2005/06' AS s2005_06,
                                                     '2004/05' AS s2004_05,
                                                     '2003/04' AS s2003_04,
                                                     '2002/03' AS s2002_03,
                                                     '2001/02' AS s2001_02));

SELECT *
  FROM t1;

SELECT team,
       DECODE (level_,
               1, '2010/11',
               2, '2009/10',
               3, '2008/09',
               4, '2007/08',
               5, '2006/07',
               6, '2005/06',
               7, '2004/05',
               8, '2003/04',
               9, '2002/03',
               10, '2001/02'
              ) AS season,
       DECODE (level_,
               1, s2010_11_points,
               2, s2009_10_points,
               3, s2008_09_points,
               4, s2007_08_points,
               5, s2006_07_points,
               6, s2005_06_points,
               7, s2004_05_points,
               8, s2003_04_points,
               9, s2002_03_points,
               10, s2001_02_points
              ) AS points
  FROM t1,
       (SELECT     LEVEL AS level_
              FROM DUAL
        CONNECT BY LEVEL <= 10);

SELECT *
  FROM t1
UNPIVOT (points FOR (season) IN (s2010_11_points AS '2010/11',
                                 s2009_10_points AS '2009/10',
                                 s2008_09_points AS '2008/09',
                                 s2007_08_points AS '2007/08',
                                 s2006_07_points AS '2006/07',
                                 s2005_06_points AS '2005/06',
                                 s2004_05_points AS '2004/05',
                                 s2003_04_points AS '2003/04',
                                 s2002_03_points AS '2002/03',
                                 s2001_02_points AS '2001/02'));

DROP TABLE t1;

On lines 1-4, there is a classical way to calculate total points for all three teams, using DECODE. The SQL statement between lines 6 and 17 is another example of classical pivoting. In this SQL we use team names to pivot the data and retreive years as columns and teams as columns. What you can do in Oracle 11g is you can write the same query like lines 19-31. In my opinon, It’s just a bit less complex, but harder to read for an unfamiliar eye.

On lines 33-46, we create a table including these pivoted data, to study unpivoting. Lines 51-79, show the SQL to unpivot the data to its original form using classical SQL methods. On lines 81-92, you can see Oracle 11g’s new UNPIVOT operator on work to do the same job. This time syntax looks less complex but it’s only because the classical method is harder than pivoting.

On line 94 we drop the temporary T1 table.

Analytic Functions

SELECT team, season,
         ROUND
            (  points
             / FIRST_VALUE (points) OVER (PARTITION BY season ORDER BY points DESC,
                goals_for - goals_against DESC),
             2
            )
       * 100 points_if_1st_100
  FROM football;

SELECT team, season,
         ROUND
            (  points
             / LAST_VALUE (points) OVER (PARTITION BY season ORDER BY points DESC,
                goals_for - goals_against DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
             2
            )
       * 100 points_if_3rd_100
  FROM football;

SELECT team, season,
         ROUND
            (  points
             / NTH_VALUE (points, 2) OVER (PARTITION BY season ORDER BY points DESC,
                goals_for - goals_against DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
             2
            )
       * 100 points_if_2nd_100
  FROM football;

SELECT   t1.team, t1.season,
         ROUND (t1.points / t2.sum_points, 2) * 100 points_percent_by_season
    FROM football t1,
         (SELECT   season, SUM (points) sum_points
              FROM football
          GROUP BY season) t2
   WHERE t1.season = t2.season
ORDER BY 3 DESC;

SELECT   team, season,
           ROUND
              (ratio_to_report (points) OVER (PARTITION BY season),
               2
              )
         * 100 points_percent_by_season
    FROM football
ORDER BY 3 DESC;

SELECT season, team, RANK,
       RANK () OVER (PARTITION BY season ORDER BY points DESC,
        goals_for - goals_against DESC) rank_in_between
  FROM football;

Analytic functions are a very powerful feature of Oracle Database. Using this functions one can make use of the data on other rows while calculating a column value. One of many analytic functions is first_value. On lines 1-9 of the demo there is a SQL statement demonstrating the usage of this function. Let’s say we want to calculate proportional points for every team assuming that the champions got 100 points for every season. For that purpose ‘for each season’ we need to know the points of ‘the most successful team’. On lines 4-5 FIRST_VALUE function partitions the data into seasons and order every partition inside itself according to points and goal average. Note that we can use other row’s data to calculate the value for one row. If we had wanted to calculate the points as if the 3rd team had got 100 points we would have used LAST_VALUE function as on lines number 11-19. These functions were there before Oracle Database 11g, but NTH_VALUE function, which is demonstrated on lines 21-29 is new.

To calculate percent of every teams point with respect to total points one can use the SQL statement between lines 31-38.  While this is a valid method to do the job there is an easier and elegant way of doing this: Use of RATIO_TO_REPORT function is shown on lines 40-47. Note that, you read the table only once to calculate ratios.

One other analytic function shown in the demo is RANK, which is demonstrated on lines 49-52 and returns the rank of the line according to the given partitioning and ordering rules.

SELECT DISTINCT
 season,
 ROUND (CORR (goals_for, points) OVER (PARTITION BY season), 2)
 corr_of_goals_for_and_points
 FROM football;

SELECT DISTINCT
 season,
 ROUND (CORR (goals_against, points) OVER (PARTITION BY season), 2)
 corr_of_goals_agnst_and_points
 FROM football;

SELECT DISTINCT
 team,
 ROUND (CORR (goals_for, goals_against) OVER (PARTITION BY team), 2)
 corr_of_goals_for_and_against
 FROM football;

SELECT DISTINCT
 team,
 ROUND (REGR_SLOPE (goals_for, points) OVER (PARTITION BY team), 2)
 slope,
 ROUND (REGR_INTERCEPT (goals_for, points) OVER (PARTITION BY team),
 2)
 intercept,
 ROUND (REGR_COUNT (goals_for, points) OVER (PARTITION BY team), 2)
 COUNT,
 ROUND (REGR_R2 (goals_for, points) OVER (PARTITION BY team), 2) r2,
 ROUND (REGR_AVGX (goals_for, points) OVER (PARTITION BY team), 2)
 avgx,
 ROUND (REGR_AVGY (goals_for, points) OVER (PARTITION BY team), 2)
 avgy
 FROM football;

DROP TABLE football;

In this part of the demo, some other analytic functions to analyze statistical information is shown. In the first SQL statement on lines 1-5 use of CORR function is shown to calculate the correlation between goals for and points. Lines 7-11 shows another use of CORR function which results in negative correlation values. Correlation of two variables is a value between -1 and 1, -1 meaning negative correlation, 1 meaning positive correlation and 0 meaning no correlation. The calculation of correlation between goals for and goals against for all seasons is shown on lines 13-17.

Some other analytic functions for regression is shown on lines 19-33.

On line 35 we drop FOOTBALL table.

Children out of School

CREATE TABLE children_out_of_school (
  country VARCHAR2(32),
  YEAR    INTEGER,
  gender  VARCHAR2(1),
  VALUE   INTEGER
);

INSERT INTO children_out_of_school
            (country, YEAR, gender, VALUE
            )
     VALUES ('Sweden', 2007, 'F', 10986
            );

INSERT INTO children_out_of_school
            (country, YEAR, gender, VALUE
            )
     VALUES ('Sweden', 2008, 'F', 4957
            );

INSERT INTO children_out_of_school
            (country, YEAR, gender, VALUE
            )
     VALUES ('Sweden', 2009, 'F', 2829
            );

INSERT INTO children_out_of_school
            (country, YEAR, gender, VALUE
            )
     VALUES ('Sweden', 2007, 'M', 10420
            );

INSERT INTO children_out_of_school
            (country, YEAR, gender, VALUE
            )
     VALUES ('Sweden', 2008, 'M', 3193
            );

INSERT INTO children_out_of_school
            (country, YEAR, gender, VALUE
            )
     VALUES ('Sweden', 2009, 'M', 1103
            );

INSERT INTO children_out_of_school
            (country, YEAR, gender, VALUE
            )
     VALUES ('Russian Federation', 2007, 'F', 129842
            );

INSERT INTO children_out_of_school
            (country, YEAR, gender, VALUE
            )
     VALUES ('Russian Federation', 2008, 'F', 118331
            );

INSERT INTO children_out_of_school
            (country, YEAR, gender, VALUE
            )
     VALUES ('Russian Federation', 2009, 'F', 92839
            );

INSERT INTO children_out_of_school
            (country, YEAR, gender, VALUE
            )
     VALUES ('Russian Federation', 2007, 'M', 156351
            );

INSERT INTO children_out_of_school
            (country, YEAR, gender, VALUE
            )
     VALUES ('Russian Federation', 2008, 'M', 141410
            );

INSERT INTO children_out_of_school
            (country, YEAR, gender, VALUE
            )
     VALUES ('Russian Federation', 2009, 'M', 127868
            );

INSERT INTO children_out_of_school
            (country, YEAR, gender, VALUE
            )
     VALUES ('Pakistan', 2007, 'F', 3979834
            );

INSERT INTO children_out_of_school
            (country, YEAR, gender, VALUE
            )
     VALUES ('Pakistan', 2008, 'F', 3796212
            );

INSERT INTO children_out_of_school
            (country, YEAR, gender, VALUE
            )
     VALUES ('Pakistan', 2009, 'F', 3500674
            );

INSERT INTO children_out_of_school
            (country, YEAR, gender, VALUE
            )
     VALUES ('Pakistan', 2010, 'F', 3241203
            );

INSERT INTO children_out_of_school
            (country, YEAR, gender, VALUE
            )
     VALUES ('Pakistan', 2007, 'M', 2623098
            );

INSERT INTO children_out_of_school
            (country, YEAR, gender, VALUE
            )
     VALUES ('Pakistan', 2008, 'M', 2470039
            );

INSERT INTO children_out_of_school
            (country, YEAR, gender, VALUE
            )
     VALUES ('Pakistan', 2009, 'M', 2209029
            );

INSERT INTO children_out_of_school
            (country, YEAR, gender, VALUE
            )
     VALUES ('Pakistan', 2010, 'M', 1884170
            );

COMMIT ;

This data set shows the number of male and female children out of school in a country, on a specific year.

ROOLUP and CUBE Operators

SELECT *
  FROM children_out_of_school;

SELECT DISTINCT country,
                SUM (VALUE) OVER (PARTITION BY country)
                                                       total_value_by_country
           FROM children_out_of_school
       ORDER BY country;

SELECT   country, gender, YEAR,
         AVG (VALUE) OVER (PARTITION BY country, gender ORDER BY YEAR ROWS 1 PRECEDING)
                                                                    avg_value
    FROM children_out_of_school
ORDER BY country, gender;

SELECT   country, YEAR, SUM (VALUE) total_value
    FROM children_out_of_school
GROUP BY ROLLUP (country, YEAR)
ORDER BY country, YEAR;

SELECT   country, YEAR, SUM (VALUE) total_value
    FROM children_out_of_school
GROUP BY CUBE (country, YEAR)
ORDER BY country, YEAR;

On lines 1-2 we have a look at the data in the table. Lines 4-8 shows how to use SUM analytic function to calculate sum of the number of children out of school for every country. This is a very simple question which you don’t need analytic SQL to solve. But if the question was “for every country, gender and year, calculate the average of the numbers of  children out of school for that year and if there are any data previous year” then classic SQL would not be sufficient. Answer for those who does not want to use Java or any other programming language to do this after fetching data is on lines 10-14 of the demo.

Two other nice extensions of Oracle SQL is ROLLUP and CUBE. Using ROLLUP (lines 16-19) the SQL statement returns the summary for the major key and using CUBE (lines 21-24) the SQL statement returns the summary for each key combination possible.

Model Clause

SELECT *
  FROM children_out_of_school;

SELECT   *
    FROM children_out_of_school
 MODEL PARTITION BY (country)
       DIMENSION BY (YEAR, gender)
       MEASURES (VALUE)
       RULES UPSERT
       (VALUE[2011, 'F'] = 0,
        VALUE[2011, 'M'] = 0)
ORDER BY gender;

SELECT   *
    FROM children_out_of_school
 MODEL RETURN UPDATED ROWS
       PARTITION BY (country)
       DIMENSION BY (YEAR, gender)
       MEASURES (VALUE)
       RULES UPSERT
       (VALUE[2011, 'F'] = 0,
        VALUE[2011, 'M'] = 0)
ORDER BY gender;

SELECT   *
    FROM children_out_of_school
 MODEL RETURN UPDATED ROWS
       PARTITION BY (country)
       DIMENSION BY (YEAR, gender)
       MEASURES (VALUE)
       RULES UPSERT
       (VALUE[2011, 'F'] = 0,
        VALUE[2011, 'M'] = 0,
        VALUE[2010, 'T'] = SUM(VALUE)[CV(YEAR), ANY])
ORDER BY gender;

SELECT *
  FROM children_out_of_school
 MODEL RETURN UPDATED ROWS
       PARTITION BY (country)
       DIMENSION BY (YEAR, gender)
       MEASURES (VALUE)
       RULES UPSERT
       (VALUE[2011, 'F'] = FLOOR(AVG(VALUE)[ANY, 'F']),
        VALUE[2011, 'M'] = FLOOR(AVG(VALUE)[ANY, 'M']));

SELECT *
  FROM children_out_of_school
 MODEL RETURN UPDATED ROWS
       PARTITION BY (country)
       DIMENSION BY (YEAR, gender)
       MEASURES (VALUE, 0 AS population)
       RULES UPSERT
       (VALUE[2011, 'F'] = FLOOR(AVG(VALUE)[ANY, 'F']),
        VALUE[2011, 'M'] = FLOOR(AVG(VALUE)[ANY, 'M']),
        population[ANY, ANY] = 0);

DROP TABLE children_out_of_school;

On lines 1-2 we check the data in the table. On lines 4-12, I introduce you the model clause of Oracle SQL. Let’s say we are interested in the data in the table but we want to modify it slightly – e. g. we want to get the data as if all values in the column value is 0 for all female and male students in year 2011. We partition the data according to country and dimension it according to year and gender, and finally upsert (update if exists, insert if not) values into the data. In order to see only the updated rows one can use RETURN UPDATED ROWS statement as shown on lines 14-23.

The SQL statement on lines  25-35 shows some more features of model clause. Using model clause you can create new rows using the data in other rows. For example, in this SQL we create including total number of children out of school for that country and year regardless of gender and we assign ‘T’ for the gender column. Note that, if there is no data for that spesific row the resulting value is NULL. Another example is shown on lines 37-45.

Yet another feature of Oracle SQL model clause is that you can add columns to the data and populate them. An example is shown on lines 47-56.

On line 58 we drop the table we created for the demo.

Clean-up

You can clean up changes in the database just by dropping the demo user you created.

DROP USER demo CASCADE;

I published a blog post about C a few years earlier [1]. Bilal Hatipoğlu published a follow up on this blog post shortly after its publication [2].

In my post my intention was to make readers understand that C is not a silver bullet which can kill any vampire (problem). The name of the article was “Is C A Vitamin?” implying that C is not a vitamin that you can use all the time without any complications or side effects.

It was exactly my point that some people feel that they have to advocate C! Thanks Bilal for proving it. Now, listen to me very carefully: C is probably the most successful programming language of all times. Almost 40 years of very wide usage, one of the best performing programming languages of the world, operating systems, games, programs written in C, etc. C does not need your advocacy!

Nevertheless, Bilal felt the urge to advocate C against the villain: myself. He presents me as a Javamaniac: Well, I am not! I don’t even like Java or maybe I hate it! I hate it so much that if I was God I would wipe Java programming language along with Java coffee and Java island from the surface of earth. But still, I use Java when I need it.

Most of the people who think C needs their advocacy do that by saying that C is faster than all other common programming languages. Go and check [3]. I know this guy: He is a world-wide known engineer but still he spares his time to prove C is faster than Java. Hello! We already know that! Performance is very important, probably the most important feature of a programming language. But it only counts when the program runs without a problem. Do I need a(nother) proof if I quote “When C is your hammer, everything start to look like your thumb”? It is obvious that C provides the programmer with wonderful features, but it is also obvious that they are very dangerous. Hence, we have to consider if we really need to use C for each project before we decide it. I’m basically saying that think before decide! Not a bad thing, eh?

And then there is the business aspect: C development costs are higher than most of those slower languages. Also, maintenance of C is very hard. I wonder if Bilal knows Python is written purely in C. (Here we have to remember one of the most crucial rules of all time: Do not reinvent the wheel!) You can also write some code in C and integrate it to your Python program. Does he understand what this means? You can write your performance critical modules in C and integrate them with your Python application in which you don’t have to deal with all those C stuff. So you can now spend your energy on your problem without giving up the performance.

I believe the ego of the programmer takes her/him to a place that (s)he thinks (s)he is the most intelligent human being in the world. To prove this to her/himself (s)he thinks that (s)he can write a bug-free code even using C! This false judgement leads this person to great misery because the most intelligent person in the world can understand that programs we develop in the real world gets more and more complicated everyday, even in the micro level. To use such an old and complicated language to solve these kind of problems, well, may cause even bigger problems than we try to solve in the first place.

References

[1] https://ozgurmacit.wordpress.com/2011/09/04/is-c-a-vitamin/ :: Is C A Vitamin? by Özgür Macit
[2] http://bhatipoglu.com/entry.asp?id=50 :: Is C a Vitamin? Yes, of course… by Bilal Hatipoğlu
[3] http://husnusensoy.blogspot.com/2006/06/c-vs-java-in-number-crunching.html :: C vs. Java in number crunching

… or Problems of C Programming Language

In my university, students are thought C as the first programming language – not only computer engineering program’s students, but also some of other engineering programs’ students. Although I had always liked writing code in C programming language, there are some reasons that why I think it is not a proper language to teach the concept of programming or even it is not a proper language to write most of the programs.

According to Wikipedia, C is a general-purpose, procedural, imperative computer programming language developed in 1972 by Dennis Ritchie at the Bell Telephone Laboratories for use with the Unix operating system [5]. Sure it is procedural and imperative, but talking about purpose of language, though we must categorize C as a general-purpose language, who can advocate correctness of implementing every problem in C? C was originally designed to be a portable assembly language for easier implementation of UNIX [2]. Thus, it is a low-level programming language to use when hardware-intended or performance-critical software is needed.

I will mention about some problems or pseudo-problems of C programming language in this article. A very wide criticism of C programming language may be found at [6].

Readability

We do not need to say a lot about C and readability: C is not readable at all – don’t forget it’s a kind of enhancement to assembly language. Let us look at an example (example is taken from [1]). Have a look at code listings 1 and 2. First one is an implementation of a list of integers in C and second is Python equivalent of same code. We are not interested in how much readable Python code is, but we are interested in how much hard to read C code.

While writing code in C, you have to help compiler a lot. For favor of compiler you put braces ({ and }) to represent blocks. Stars here and stars there confuses the programmer (and then after code-reader). When you want to use some memory you have to allocate it and make sure all pointers are set to NULL initially. Also when you want to update the list, you must take care of all pointers again. Code becomes a whole mess full with statements those are not related with real problem and anyone who want to read and understand code finds (her/him)self inside this mess. (S)he has to fight with all these stars and braces and allocation statements and etc.

Have a look at table under Expressiveness section of [4]. According to this table you have to write 6 lines of C code to do same job with a single line of Python or Perl code. Also, lines of code needed to implement a problem in C is 2.5 times of lines of code to implement same problem in Java or Fortran. Thus, we can say C is not an efficient language for either writing or reading code.

Orthogonality

Have a look at two function definitions in code listing 3 (example is taken from [1]). The first function (double_int) takes an integer parameter and doubles its value and saves inside a local variable. After that value of local variable is returned. Second function (double_str) takes a character pointer as a parameter and doubles its value inside a local variable using some library functions. Like the first function, at last, value of local variable is returned back. But, second function cannot be even compiled because we are trying to return a local pointer variable.

Programmer occasionally does not have chance to write a similar code for two similar instances of same problem in C. For this reason, C is said to be not orthogonal.

Safety

C code tends to need more maintenance because of its unsafe properties. Some of these properties are:

  • Though type checking is done by compiler, programmer is free to make type casting.
  • C never and never makes index range checking. It is claimed that C encourages buffer overflows with this property in [2]. Same source gives a list of functions that may cause buffer overflow accidentally.
  • Programmer has the memory! No safety check is done about memory allocation.
  • You must not free same pointer twice – again accidentally, if you do you are in trouble. There is no internal mechanisms to avoid this. Programmer must always check the pointer while allocating and freeing memory.

Redundancy

[3] says “In many ways, the C language evolved into a collection of overlapping features, providing too many ways to say the same thing, while in many cases not providing needed features.[2] has a lot of examples about that, I will not rewrite all of them. Just consider gets and fgets functions. They both do same job (OK, they don’t, but fgets does what gets can do) one is not encouraged due to some safety issues. But like a lot of similar things it cannot be fully thrown out of language, because backward compatibility is needed.

String type

In [2], James A. C. Joyce wrote about strings in C:

Most sane programming languages have a string type which allows one to just say “this is a string” “nd let the compiler take care of the rest. Not so with C. It’s so stubborn and dumb that it only has three types of variable; everything is either a number, a bigger number, a pointer or a combination of those three. Thus, we don’t have proper strings but “arrays of unsigned integers”. “char””is basically only a really small number. And now we have to start using unsigned ints to represent multibyte characters.

Since C has no string type you cannot do a string copying or string concetenation operation via its own syntax and you have to do this with help of functions. Hence, string operations are not a part of language, they are library functions and you must include appropriate header file to use them (string.h in ANSI C). Assignment of array variables is also not allowed inside C code, bacause an array is nothing but just a pointer. Also number to string or string to number conversion can be only done via functions. Look at those two syntax types – no comments.

C style:

strncpy(source + 2, target, 5);

Python style:

target = source[2:7]

Besides all, C is a low-level programming language. It is not a string manipulation language. Expecting high-level string manipulation operations from it is not reasonable.

Reaching elements

Another complaint about C is why do we have both . and -> to use for the same purpose. Firstly, they are not for the same purpose. . is structure offset and -> is used for dereferencing. Sure we should expect from compiler to take care of this difference or we can simply do not want to help the compiler to ease its job. But in my opinion understandability of code increases with this small difference. Looking at the code we can easily see what is a pointer and we are dereferencing its indigrents and what is a name of a real structure. Let me say I never liked Java way of avoiding pointers.

goto statement

James A. C. Joyce claims that using a goto statement is the only way of breaking out of nested for or while loops in [2]. I am not sure his claim is true but I don’t know a more efficient way of doing this either. [3] says 90% of goto statements used to break out of nested loops by investigated 100,000 lines of code. A number of other languages uses multi-level breaking to avoid this.
Perhaps goto statement is the worst feature of C language. You can go somewhere in a loop / nested loops using it. You can make your code ten times hard to read using a single goto statement inside a loop.

enums

C’s enum structure has a very significant and important problem which may be easily solved with object-oriented programming. If you use a name in an enum, you cannot use this name in another enum. If you use an object-oriented programming language like Java, you may put the same constant name inside different classes.

Error handling

Error handling may be done (is done by library functions) in the following ways (complete list is taken from [2]):

  • Returning zero
  • Returning nonzero
  • Returning a NULL pointer
  • Setting errno
  • Requiring a call to another function
  • Outputting a diagnostic message to the user

There is no exception handling mechanism in C. This results in two critical problems: First is what we know already: C is not appropriate for high-level programming. Secondly, since there is no single error code convension programmer gets confused when writing code.

What C doesn’t have

  • Exception handling mechanism
  • Specialized data types
  • Function overloading
  • Garbage collection

A nice joke :) [2]

“Hey, Thompson, how can I make C’s syntax even more obfuscated and difficult to understand?”

“How about you allow 5[var] to mean the same as var[5]?”

“Wow; unnecessary and confusing syntactic idiocy! Thanks!”

“You’re welcome, Dennis.”

Conclusion

C is not the evil in this story, I think. Just, it is not really proper for high-level programming. It must be used for what it is designed for.

Some bloody properties cannot be abandoned due to backward compatibility. Newbies always discouraged using these features by experienced programmers.

While coding in C, I feel myself in the middle of 70s while memory was so important and that I shouldn’t use a single byte if I really don’t need it. This makes me sick about C :)

I think it is something related with comfort and habits. For example Fortran programmers have complaints about ability to change the loop variable inside the loop, but I cannot even dream about a world where I cannot change it :) Java programmers find pointers confusing, I am confused when I don’t see those stars inside code :)

To sum up, C or its features are not real problem, problem is using it where not to use. Never forget: C is not a vitamin that is useful in every condition, it is a low-level language!

References

[1] http://www.ce.itu.edu.tr/undergraduate/courses/blg437e/presentations/introduction.pdf : Programming Languages – Introduction by H. Turgut Uyar
[2] http://www.kuro5hin.org/story/2004/2/7/144019/8872 : Why C Is Not My Favourite Programming Language by James A. C. Joyce
[3] http://java.sun.com/docs/white/langenv/Simple.doc2.html : The Java Language Environment
[4] http://en.wikipedia.org/wiki/Comparison_of_programming_languages : Comparison of programming languages
[5] http://en.wikipedia.org/wiki/C_%28programming_language%29 : C (programming language)
[6] http://en.wikipedia.org/wiki/Criticism_of_the_C_programming_language : Criticism of the C programming language

Code listings

[1] https://docs.google.com/leaf?id=0B0-uGeBFEDYyOWZlNTAwNjUtYjlhYy00NzVlLTgxYmEtNjRmN2Y3MjNiNDBj&hl=tr
[2] https://docs.google.com/leaf?id=0B0-uGeBFEDYyN2E5NWE1ZDUtYTNlYy00YzUxLTgxMWUtYWNlOGYyNjdiMzQz&hl=tr
[3] https://docs.google.com/leaf?id=0B0-uGeBFEDYyODJlNWRiOWMtM2M0Ni00NWY2LTk4Y2ItNGY2MDA4YjMzMzli&hl=tr

Unit Testing

I have been working on computer software since I had entered university in September 2002. It has been more than four years and no single unit testing experimantation all I have. Of course I test my code, but not having any theoretical or methodological background. I only gave a few suspicious input to my program and checked the output, that is all. Thus, big black boxes, all my programs were.

Test Driven Development

On this site Test Driven Development is summarized in this way:

Test Driven Development (TDD) is a software engineering methodology that states that all code should have a set of clear, repeatable unit tests written for it. Furthermore, it states that these tests should be written before the corresponding code is written, thus driving the development of the code base.

That sounds good: While I prepare my test cases before development phase I am able to think about them free of implementation. Moreover, once I have thought of test cases (that is, possible failure cases) I may write my code much more efficient and bug-free. There is no need to discuss how big effect bugs have in software production. Just let me say, software bugs may cause a big money loss, or even human life in some cases. We don’t love them, we will never love :)

Extreme Programming

Extreme Programming is defined as “Extreme Programming differs from traditional methodologies primarily in placing a higher value on adaptability than on predictability” in Wikipedia. The methodology of Extreme Programming is not to predict all requirements of product at the begining of project but make project adaptable to changing requirements. That’s the way a software project has a chance to be successful in our day. Projects are developed in months or even years occasionally. While software is being developed, customer may change mind or requirements of market may change. This requires a fast-alterible programming methodology like Extreme Programming.

Qute

Unit testing is the corner stone of Extreme Programming ([1]). That’s why Steven Feuerstein works on a unit testing tool for PL/SQL :)

Lucas Jellema gives a brief introduction about Qute (Feuerstein’s unit testing tool) before giving helpful information about how to install the program and a tutorial to explain how the program runs in his article. In the same article he quotes from Steven Feuerstein “Qute is the Quick Unit Test Engine. It is a tool for defining unit tests, generating test code, and running those tests, all within a graphical interface”.
The thing I found exciting in Qute is it has an easy to use GUI, so you do not need to write even one line of code to create test cases or run them againist your code. With four years in school and six months of work experience with them, I learned that developer is somebody who is lazy as a cat :) This tool is something for them (us?).

You can download Quest Code Tester for Oracle here (you must be a registered user to download the file – and smile, registration is free for now).

Unfortunately, I have faced a problem while installing this software on my computer. Since a new user does not have some privilages required, installation had failed. I created a user of my own and re-installed the software:

DROP USER qute;
CREATE USER qute IDENTIFIED BY qute;
GRANT CONNECT, RESOURCE, DBA TO qute;
GRANT EXECUTE ON utl_file TO qute;

(You must have necessary privileges to execute the script.)

I tested the test tool with a simple package. All I have done is writing this code and compiling it on any schema, say HR, then granting execute on this package to the user qute, or whatever schema Qute is installed. Then I open Qute and generate test cases for this package and simply run the test. That’s all. I did not write a simple line of code for testing.

What I don’t like about Qute is:

  • It needs a schema to keep its own tables, etc. This is an obligation, though.
  • It needs several privilages to be given to that user to run test cases againist objects on other schemas.

What I like about Qute is, I can now say “here is my documentation, if you update my code, your new code should success those cases”. That’s enough for me.

Unit testing activities

Following list is completely copied from [3].

  1. Perform test planning phase
    1. Plan the general approach, resources, and schedule
    2. Determine features to be tested
    3. Refine the general plan
  2. Acquire test set phase
    1. Design the set of tests
    2. Implement the refined plan and design
  3. Measure test unit phase
    1. Execute the test procedures
    2. Check for termination
    3. Evaluate the test effort and unit

Benefits of unit testing

In Wikipedia it is claimed that “Unit testing is typically done by the developers and not by end-users“. This fact, however it seems to be expense of unit testing to developer, is actually a benefit of unit testing.
First of all, since unit testing methodology assumes that test cases are prepared before the implementation phase, test cases becomes roadmaps of actual implementation. Programmers just have to be aware of test cases to reduce the cost of maintenance.
Secondly, Unit Testing provides a sort of “living document” [2]. Same article on Wikipedia continues like this:

Unit test cases embody characteristics that are critical to the success of the unit. These characteristics can indicate appropriate/inappropriate use of a unit as well as negative behaviors that are to be trapped by the unit.

Unit testing documentation methodology serves a permanent information about the software. This information is what is expected from the software and this does not change unless expectations are modified. That results in an easily maintained software. Whereas, ordinary documentation is highly related with how the program is implemented and requires to be updated frequently [2].

References

[1] http://en.wikipedia.org/wiki/Extreme_Programming : Extreme Programming
[2] http://en.wikipedia.org/wiki/Unit_test : Unit testing
[3] http://iteso.mx/~pgutierrez/calidad/Estandares/IEEE%201008.pdf : IEEE Standard for Software Unit Testing
[4] http://technology.amis.nl/blog/?p=1041 : Qute – new Unit Testing Engine for PL/SQL (successor for utPLSQL) by Lucas Jellema
[5] http://www.apollo-pro.com/help/pl_unit.htm

In this video you will find my Oracle Advanced SQL presentation in an event supported by Oracle Türkiye.

Özgür Macit: Oracle Analytic functions, Hierarchical Queries, Model Clause, Regular Expressions from Hasan Tonguç YILMAZ on Vimeo.

Here is the demo I present in the video:

-- tablonun yaratilmasi

DROP TABLE yas;

CREATE TABLE yas (
ad_soyad VARCHAR2(30),
yas      NUMBER
);

-- tabloya verilerin eklenmesi

INSERT INTO yas ( ad_soyad, yas ) VALUES ( 'Kenan Muftu', 24 );
INSERT INTO yas ( ad_soyad, yas ) VALUES ( 'Aysegul Macit', 8 );
INSERT INTO yas ( ad_soyad, yas ) VALUES ( 'Erol Macit', 20 );
INSERT INTO yas ( ad_soyad, yas ) VALUES ( 'Erturk Diriksoy', 22 );
INSERT INTO yas ( ad_soyad, yas ) VALUES ( 'Ozgur Macit', 23 );
INSERT INTO yas ( ad_soyad, yas ) VALUES ( 'Zeynep Derelioglu', 22 );
INSERT INTO yas ( ad_soyad, yas ) VALUES ( 'Fatma Macit', 42 );
INSERT INTO yas ( ad_soyad, yas ) VALUES ( 'Emir Omer Macit', 53 );
INSERT INTO yas ( ad_soyad, yas ) VALUES ( 'Huseyin Tansu Ozer', 23 );
INSERT INTO yas ( ad_soyad, yas ) VALUES ( 'Mumin Irican', 24 );

COMMIT;

-- tablonun kontrol edilmesi

SELECT y.* FROM yas y;

-- NTILE ornegi

SELECT y.*,
NTILE(4) OVER ( ORDER BY y.yas ) grup
FROM yas y;

-- WIDTH_BUCKET ornegi

SELECT y.ad_soyad,
WIDTH_BUCKET( y.yas, 10, 30, 3 ) yukari_grup,
WIDTH_BUCKET( y.yas, 30, 10, 3 ) asagi_grup
FROM yas y;

-- tablonun dusurulmesi

DROP TABLE yas;

-- tablonun yaratilmasi

DROP TABLE dogum_gunleri;

CREATE TABLE dogum_gunleri (
ad_soyad VARCHAR2(30),
tip      VARCHAR2(10),
ay       NUMBER,
gun      NUMBER
);

-- tabloya ornek verilerin eklenmesi

INSERT INTO dogum_gunleri ( ad_soyad, tip, ay, gun ) VALUES ( 'Kenan Muftu', 'Arkadas', 9, 9 );
INSERT INTO dogum_gunleri ( ad_soyad, tip, ay, gun ) VALUES ( 'Aysegul Macit', 'Aile', 3, 23 );
INSERT INTO dogum_gunleri ( ad_soyad, tip, ay, gun ) VALUES ( 'Erol Macit', 'Aile', 3, 23 );
INSERT INTO dogum_gunleri ( ad_soyad, tip, ay, gun ) VALUES ( 'Erturk Diriksoy' , 'Arkadas', 9, 26 );
INSERT INTO dogum_gunleri ( ad_soyad, tip, ay, gun ) VALUES ( 'Ozgur Macit', 'Aile', 5, 10 );
INSERT INTO dogum_gunleri ( ad_soyad, tip, ay, gun ) VALUES ( 'Zeynep Derelioglu', 'Arkadas', 6, 15 );
INSERT INTO dogum_gunleri ( ad_soyad, tip, ay, gun ) VALUES ( 'Fatma Macit', 'Aile', 7, 20 );
INSERT INTO dogum_gunleri ( ad_soyad, tip, ay, gun ) VALUES ( 'Emir Omer Macit', 'Aile', 3, 27 );
INSERT INTO dogum_gunleri ( ad_soyad, tip, ay, gun ) VALUES ( 'Huseyin Tansu Ozer', 'Arkadas', 8, 19 );
INSERT INTO dogum_gunleri ( ad_soyad, tip, ay, gun ) VALUES ( 'Mumin Irican', 'Arkadas', 8, 6 );

COMMIT;

-- tablonun kontrol edilmesi

SELECT dg.* FROM dogum_gunleri dg;

-- LAG ornegi

SELECT dg.ad_soyad,
LAG( dg.ad_soyad, 1, '' ) OVER ( ORDER BY dg.ay, dg.gun ) onceki
FROM dogum_gunleri dg
WHERE tip = 'Arkadas';

-- LEAD ornegi

SELECT dg.ad_soyad,
LEAD( dg.ad_soyad, 1, '' ) OVER ( ORDER BY dg.ay, dg.gun ) sonraki
FROM dogum_gunleri dg
WHERE tip = 'Arkadas';

-- Bu yil icinde arkadaslarimin dogum gunlerini hangi sirayla kutlayacagiz?

SELECT dg.ad_soyad,
dg.ay,
dg.gun,
RANK() OVER ( ORDER BY dg.ay, dg.gun) sira
FROM dogum_gunleri dg
WHERE dg.tip = 'Arkadas'
ORDER BY sira;

-- Bu yil icinde arkadaslarimin ve ailemin dogum gunlerini ayri ayri hangi sirayla kutlayacagiz?

SELECT dg.*,
RANK() OVER ( PARTITION BY dg.tip
ORDER BY dg.ay, dg.gun) sira
FROM dogum_gunleri dg
ORDER BY dg.tip,
sira;

-- Bu yil icinde herkesin dogum gunlerini hangi sirayla kutlayacagiz?

SELECT dg.*,
RANK() OVER ( ORDER BY dg.ay, dg.gun) sira
FROM dogum_gunleri dg
ORDER BY sira;

-- DENSE_RANK ornegi

SELECT dg.ad_soyad,
dg.ay,
dg.gun,
DENSE_RANK() OVER ( ORDER BY dg.ay, dg.gun) sira
FROM dogum_gunleri dg
WHERE dg.tip = 'Aile'
ORDER BY sira;

-- tablonun dusurulmesi

DROP TABLE dogum_gunleri;

-- tablonun yaratilmasi

DROP TABLE telefon_gorusmeleri;

CREATE TABLE telefon_gorusmeleri (
ad_soyad       VARCHAR2(20),
yil            NUMBER,
gorusme_sayisi NUMBER
);

-- tabloya verilerin eklenmesi

INSERT INTO telefon_gorusmeleri ( ad_soyad, yil, gorusme_sayisi ) VALUES ( 'Kenan Muftu', 2004, 12 );
INSERT INTO telefon_gorusmeleri ( ad_soyad, yil, gorusme_sayisi ) VALUES ( 'Kenan Muftu', 2005, 16 );
INSERT INTO telefon_gorusmeleri ( ad_soyad, yil, gorusme_sayisi ) VALUES ( 'Kenan Muftu', 2006, 28 );
INSERT INTO telefon_gorusmeleri ( ad_soyad, yil, gorusme_sayisi ) VALUES ( 'Kenan Muftu', 2007, 65 );
INSERT INTO telefon_gorusmeleri ( ad_soyad, yil, gorusme_sayisi ) VALUES ( 'Fatma Macit', 2004, 109 );
INSERT INTO telefon_gorusmeleri ( ad_soyad, yil, gorusme_sayisi ) VALUES ( 'Fatma Macit', 2005, 243 );
INSERT INTO telefon_gorusmeleri ( ad_soyad, yil, gorusme_sayisi ) VALUES ( 'Fatma Macit', 2006, 98 );
INSERT INTO telefon_gorusmeleri ( ad_soyad, yil, gorusme_sayisi ) VALUES ( 'Fatma Macit', 2007, 78 );
INSERT INTO telefon_gorusmeleri ( ad_soyad, yil, gorusme_sayisi ) VALUES ( 'Mumin Irican', 2004, 1 );
INSERT INTO telefon_gorusmeleri ( ad_soyad, yil, gorusme_sayisi ) VALUES ( 'Mumin Irican', 2005, 3 );
INSERT INTO telefon_gorusmeleri ( ad_soyad, yil, gorusme_sayisi ) VALUES ( 'Mumin Irican', 2006, 0 );
INSERT INTO telefon_gorusmeleri ( ad_soyad, yil, gorusme_sayisi ) VALUES ( 'Mumin Irican', 2007, 2 );

COMMIT;

-- tablonun kontrol edilmesi

SELECT * FROM telefon_gorusmeleri;

-- Her kisi icin her yil, o yil ve onceki yillarda toplam kac telefon gorusmesi yapmisim?

SELECT tg.ad_soyad,
tg.yil,
SUM( tg.gorusme_sayisi) OVER ( PARTITION BY tg.ad_soyad
ORDER BY tg.yil ) toplam_gs
FROM telefon_gorusmeleri tg
ORDER BY tg.ad_soyad,
tg.yil;

-- Her kisi icin her yil, o yil ve onceki yil ortalamada kac telefon gorusmesi yapmisim?

SELECT tg.ad_soyad,
tg.yil,
AVG( tg.gorusme_sayisi) OVER ( PARTITION BY tg.ad_soyad
ORDER BY tg.yil
ROWS 1 PRECEDING ) ortalama_gs
FROM telefon_gorusmeleri tg
ORDER BY tg.ad_soyad,
tg.yil;

-- ROLLUP ornegi

SELECT tg.ad_soyad,
tg.yil,
SUM ( tg.gorusme_sayisi ) toplam_gs
FROM telefon_gorusmeleri tg
WHERE tg.ad_soyad IN ( 'Fatma Macit', 'Mumin Irican' )
GROUP BY ROLLUP( tg.ad_soyad,
tg.yil );

-- CUBE ornegi

SELECT tg.ad_soyad,
tg.yil,
SUM ( tg.gorusme_sayisi ) toplam_gs
FROM telefon_gorusmeleri tg
WHERE tg.ad_soyad IN ( 'Fatma Macit', 'Mumin Irican' )
GROUP BY CUBE( tg.ad_soyad,
tg.yil );

-- tablonun dusurulmesi

DROP TABLE telefon_gorusmeleri;

-- tablonun yaratılması

DROP TABLE kaplumbaga_ailesi;

CREATE TABLE kaplumbaga_ailesi (
kimlik_no INTEGER PRIMARY KEY,
ata       REFERENCES kaplumbaga_ailesi,
isim      VARCHAR2(30)
);

-- tabloya verilerin girilmesi

INSERT INTO kaplumbaga_ailesi (kimlik_no, ata, isim ) VALUES ( 1, NULL, 'Dede Kaplumbaga' );
INSERT INTO kaplumbaga_ailesi (kimlik_no, ata, isim ) VALUES ( 2, 1, 'Hala Kaplumbaga' );
INSERT INTO kaplumbaga_ailesi (kimlik_no, ata, isim ) VALUES ( 3, 1, 'Baba Kaplumbaga' );
INSERT INTO kaplumbaga_ailesi (kimlik_no, ata, isim ) VALUES ( 4, 3, 'Evlat Kaplumbaga' );
INSERT INTO kaplumbaga_ailesi (kimlik_no, ata, isim ) VALUES ( 5, 4, 'Torun Kaplumbaga' );
INSERT INTO kaplumbaga_ailesi (kimlik_no, ata, isim ) VALUES ( 6, 1, 'Amca Kaplumbaga' );
INSERT INTO kaplumbaga_ailesi (kimlik_no, ata, isim ) VALUES ( 7, 6, 'Erkek Kuzen Kaplumbaga' );
INSERT INTO kaplumbaga_ailesi (kimlik_no, ata, isim ) VALUES ( 8, 6, 'Disi Kuzen Kaplumbaga' );

COMMIT;

-- tablonun kontrol edilmesi

SELECT * FROM kaplumbaga_ailesi;

-- CONNECT BY PRIOR ... START WITH ornegi

SELECT ka.isim,
ka.kimlik_no,
ka.ata
FROM kaplumbaga_ailesi ka
CONNECT BY PRIOR ka.kimlik_no = ka.ata
START WITH ka.ata IS NULL ;

-- CONNECT BY PRIOR ... START WITH ornegi

SELECT SYS_CONNECT_BY_PATH( isim, '/' ) agac
FROM kaplumbaga_ailesi ka
CONNECT BY PRIOR ka.kimlik_no = ka.ata
START WITH ka.ata IS NULL ;

-- tablonun dusurulmesi

DROP TABLE kaplumbaga_ailesi;

In this video you will find my Oracle PL/SQL Best Practices presentation in an event supported by Oracle Türkiye.

Özgür Macit: Oracle PL/SQL Best Practices from Hasan Tonguç YILMAZ on Vimeo.