test/sql/Order/exp.test002.ksh

   1 echo CREATE TABLE Articles ( ArticleID SMALLINT, ArticleTitle CHAR(30) NOT NULL, Copyright INT NOT NULL,PRIMARY KEY(ArticleID));
   2 Statement Executed
   3 echo INSERT INTO Articles VALUES (12786, 'How write a paper',1934);
   4 Statement Executed: Rows Affected = 1
   5 echo INSERT INTO Articles VALUES (13331, 'Publish a paper  ',1919);
   6 Statement Executed: Rows Affected = 1
   7 echo INSERT INTO Articles VALUES (14356, 'Sell a paper     ',1966);
   8 Statement Executed: Rows Affected = 1
   9 echo INSERT INTO Articles VALUES (15729, 'Buy a paper      ',1932);
  10 Statement Executed: Rows Affected = 1
  11 echo INSERT INTO Articles VALUES (16284, 'Conferences      ',1996);
  12 Statement Executed: Rows Affected = 1
  13 echo INSERT INTO Articles VALUES (17695, 'Journal          ',1980);
  14 Statement Executed: Rows Affected = 1
  15 echo INSERT INTO Articles VALUES (19264, 'Information      ',1992);
  16 Statement Executed: Rows Affected = 1
  17 echo INSERT INTO Articles VALUES (19354, 'AI               ',1993);
  18 Statement Executed: Rows Affected = 1
  19 echo CREATE TABLE Authors (AuthID SMALLINT, AuthorFirstName CHAR(20), AuthorMiddleName CHAR(20), AuthorLastName CHAR(20),PRIMARY KEY(AuthID));
  20 Statement Executed
  21 echo INSERT INTO Authors VALUES (1006, 'Henry', 'S.', 'Thompson');
  22 Statement Executed: Rows Affected = 1
  23 echo INSERT INTO Authors VALUES(1007, 'Jason', 'Carol', 'Oak');
  24 Statement Executed: Rows Affected = 1
  25 echo INSERT INTO Authors VALUES(1008, 'James', NULL, 'Elk');
  26 Statement Executed: Rows Affected = 1
  27 echo INSERT INTO Authors VALUES(1009, 'Tom', 'M', 'Ride');
  28 Statement Executed: Rows Affected = 1
  29 echo INSERT INTO Authors VALUES(1010, 'Jack', 'K', 'Ken');
  30 Statement Executed: Rows Affected = 1
  31 echo INSERT INTO Authors VALUES(1011, 'Mary', 'G.', 'Lee');
  32 Statement Executed: Rows Affected = 1
  33 echo INSERT INTO Authors VALUES(1012, 'Annie', NULL, 'Peng');
  34 Statement Executed: Rows Affected = 1
  35 echo INSERT INTO Authors VALUES(1013, 'Alan', NULL, 'Wang');
  36 Statement Executed: Rows Affected = 1
  37 echo INSERT INTO Authors VALUES(1014, 'Nelson', NULL, 'Yin');
  38 Statement Executed: Rows Affected = 1
  39 echo CREATE TABLE AuthorArticle (AuthID SMALLINT NOT NULL, ArticleID SMALLINT NOT NULL,FOREIGN KEY(AuthID) REFERENCES Authors(AuthID), FOREIGN KEY(ArticleID) REFERENCES Articles(ArticleID));
  40 Statement Executed
  41 echo INSERT INTO AuthorArticle VALUES (1006, 14356);
  42 Statement Executed: Rows Affected = 1
  43 echo INSERT INTO AuthorArticle VALUES (1008, 15729);
  44 Statement Executed: Rows Affected = 1
  45 echo INSERT INTO AuthorArticle VALUES (1009, 12786);
  46 Statement Executed: Rows Affected = 1
  47 echo INSERT INTO AuthorArticle VALUES (1010, 17695);
  48 Statement Executed: Rows Affected = 1
  49 echo INSERT INTO AuthorArticle VALUES (1011, 15729);
  50 Statement Executed: Rows Affected = 1
  51 echo INSERT INTO AuthorArticle VALUES (1012, 19264);
  52 Statement Executed: Rows Affected = 1
  53 echo INSERT INTO AuthorArticle VALUES (1012, 19354);
  54 Statement Executed: Rows Affected = 1
  55 echo INSERT INTO AuthorArticle VALUES (1014, 16284);
  56 Statement Executed: Rows Affected = 1
  57 echo SELECT * FROM Articles;
  58 ---------------------------------------------------------
  59         Articles.ArticleID      Articles.ArticleTitle   Articles.Copyright
  60 ---------------------------------------------------------
  61         12786   How write a paper       1934
  62         13331   Publish a paper         1919
  63         14356   Sell a paper            1966
  64         15729   Buy a paper             1932
  65         16284   Conferences             1996
  66         17695   Journal                 1980
  67         19264   Information             1992
  68         19354   AI                      1993
  69
  70 echo SELECT * FROM Authors;
  71 ---------------------------------------------------------
  72         Authors.AuthID  Authors.AuthorFirstName Authors.AuthorMiddleName        Authors.AuthorLastName
  73 ---------------------------------------------------------
  74         1006    Henry   S.      Thompson
  75         1007    Jason   Carol   Oak
  76         1008    James   NULL    Elk
  77         1009    Tom     M       Ride
  78         1010    Jack    K       Ken
  79         1011    Mary    G.      Lee
  80         1012    Annie   NULL    Peng
  81         1013    Alan    NULL    Wang
  82         1014    Nelson  NULL    Yin
  83
  84 echo SELECT * FROM AuthorArticle;
  85 ---------------------------------------------------------
  86         AuthorArticle.AuthID    AuthorArticle.ArticleID
  87 ---------------------------------------------------------
  88         1006    14356
  89         1008    15729
  90         1009    12786
  91         1010    17695
  92         1011    15729
  93         1012    19264
  94         1012    19354
  95         1014    16284
  96
  97 echo SELECT ArticleTitle, Copyright FROM Articles  LEFT JOIN AuthorArticle  ON Articles.ArticleID=AuthorArticle.ArticleID LEFT JOIN Authors ON AuthorArticle.AuthID=Authors.AuthID;
  98 ---------------------------------------------------------
  99         ArticleTitle    Copyright
 100 ---------------------------------------------------------
 101         How write a paper       1934
 102         Publish a paper         1919
 103         Sell a paper            1966
 104         Buy a paper             1932
 105         Buy a paper             1932
 106         Conferences             1996
 107         Journal                 1980
 108         Information             1992
 109         AI                      1993
 110
 111 echo SELECT ArticleTitle, Copyright FROM Articles  LEFT JOIN AuthorArticle  ON Articles.ArticleID=AuthorArticle.ArticleID LEFT JOIN Authors ON AuthorArticle.AuthID=Authors.AuthID ORDER BY ArticleTitle;
 112 ---------------------------------------------------------
 113         ArticleTitle    Copyright
 114 ---------------------------------------------------------
 115         AI                      1993
 116         Buy a paper             1932
 117         Buy a paper             1932
 118         Conferences             1996
 119         How write a paper       1934
 120         Information             1992
 121         Journal                 1980
 122         Publish a paper         1919
 123         Sell a paper            1966
 124
 125 echo DROP table AuthorArticle;
 126 Statement Executed
 127 echo DROP table Authors;
 128 Statement Executed
 129 echo DROP table Articles;
 130 Statement Executed