|| ||      

---

Table VxSegmentation_tab

CREATE TABLE VxSegmentation_tab (
    Id                NUMBER         NOT NULL,
    Name              VARCHAR2(40)   NOT NULL,
    VideoName         VARCHAR2(40)   NOT NULL,
    Description       VARCHAR2(80),
  CONSTRAINT VxSegmentation_pk   PRIMARY KEY(id),
  CONSTRAINT VxSegmentation_uniq UNIQUE(Name, VideoName)
);

VxSegmentation_tab contains information about the segmentations: name of segmentation, name of the video and an optional description. Each segmentation stored in the database receives an Id used to reference a segmentation from other tables.

Query examples:

SQL> SELECT * FROM VxSegmentation_tab;

 ID NAME                 VIDEONAME                      DESCRIPTION
--- -------------------- ------------------------------ --------------------
 61 blocks               aviewtoakill_cd1
 22 anchorman            20010125_20uur_journaal
 23 blocks               20010125_20uur_journaal
 24 blocks_flash         20010125_20uur_journaal
 25 blocks_kineticEnergy 20010125_20uur_journaal
 26 cam                  20010125_20uur_journaal
 27 dst                  20010125_20uur_journaal
 28 faceout              20010125_20uur_journaal
 29 faces                20010125_20uur_journaal
 30 genre                20010125_20uur_journaal
 62 cam                  aviewtoakill_cd1
 63 dst                  aviewtoakill_cd1
 64 raw.blocks           aviewtoakill_cd1
 65 scenes               aviewtoakill_cd1
 66 scenes.hanjalic      aviewtoakill_cd1
 67 scenes.rui           aviewtoakill_cd1
 68 scenes.yeung         aviewtoakill_cd1
 69 trackout             aviewtoakill_cd1
 70 blocks               friends418
 71 cam                  friends418
 72 dst                  friends418
 73 raw.blocks           friends418
 74 scenes               friends418
 75 scenes.hanjalic      friends418
 76 scenes.rui           friends418
 77 scenes.yeung         friends418
 55 ling_topic           20010125_20uur_journaal
 56 persons              20010125_20uur_journaal
 57 silence              20010125_20uur_journaal
 58 teletekst            20010125_20uur_journaal
 59 trackout             20010125_20uur_journaal

31 rows selected.

Use the following commands to format the query output as shown by the example:

COLUMN id FORMAT 99
COLUMN name FORMAT a20
COLUMN videoname FORMAT a30
COLUMN description FORMAT a20

Next query will sort the results:

SQL> SELECT * FROM VxSegmentation_tab ORDER BY VideoName, Name;

And this one will show only the name of segmentations of video 'friends418':

SQL> SELECT Name FROM VxSegmentation_tab WHERE VideoName='friends418';

Sequence VxSegmentation_id

CREATE SEQUENCE VxSegmentation_id INCREMENT BY 1 START WITH 1;

This sequence has been defined to assign Ids to new segmentations. Example:

INSERT INTO VxSegmentation_tab
 SELECT VxSegmentation_id.NEXTVAL, 'test', 'friends418', 'A test' FROM dual;

This statement will introduce a new segmentation to VxSegmentation_tab. To retrieve the last assigned id, use:

SELECT VxSegmentation_id.CURRVAL FROM dual;

dual is a table that only has one row. It's useful to evaluate expressions, like:

SQL> SELECT sysdate FROM dual;

SYSDATE
---------
04-JUL-02

SQL> SELECT sin(3) FROM dual;

    SIN(3)
----------
.141120008

sysdate and sin(3) are not columns of the dual table. We can also select sysdate and sin(3) from VxSegmentation_tab, but we will obtain the result 31 times. We use dual to get the result just one time. That's the purpose of the dual table.

We will now remove the segmentation we have added:

SQL> DELETE FROM VxSegmentation_tab WHERE (videoName='friends418') AND (Name='test');

---

Go to the next section or return to the index.

---