【TOPSIC SQL】ルール・用語

受験ルール

SQLiteの制約

TOPSIC SQLでは、SQLの実行環境にSQLiteを使用しています。SQLite以外のRDBMSの文法を使用することはできません。問題を解く際に注意すべきSQLiteの制約について、以下に記載してありますのでご確認下さい。

  1. NUMERIC、INTEGER、REALは、自身のクラスへの変換が推奨されていますが、変換できなかった場合でも、そのまま格納されます。
  2. 新しくデータを追加した場合、既に格納されているデータの中で ROWID の値が最大のものを探し、それに1を加えた値が新しく追加されるデータの ROWID の値として保存されます。
  3. 外部結合は、左外部結合を行う LEFT OUTER JOIN 句のみ使用できます。
    ※2022年12月以降、LEFT / RIGHT / FULL OUTER JOIN が使用可能となりました。
  4. 指定した日付と時刻のタイムゾーンはUTCとして扱われます。
  5. 日付関数や文字列関数については、SQLiteで固有の関数があります。
  6. テーブル項目の論理名が「◯◯日」となっている場合は、日付項目の時分秒は設定されていません。論理名が「◯◯日時」となっている場合は、日付項目の時分秒まで設定されています。
    例）
    「受注日」の場合は「2021-01-01」、
    「受注日時」の場合は「2021-01-01 15:15:15」

その他のSQLiteの制約については、SQLiteの公式ドキュメントをご確認ください。

ER図の読み方

TOPSIC SQLの問題文で使用されるER図の読み方について説明します。

  1. エンティティ(表)の区切り線より上のアトリビュート(属性)が主キーになります。以下の例では、“社員コード"を主キーとする"社員マスタ"というテーブルを表しています。

  2. (FK)はフォーリンキー(外部キー)を表しています。以下の例では、“売上明細"テーブルの"伝票NO"が外部キーになっています。

  3. 実線は依存型リレーション、点線は非依存型リレーションを表しています。以下の例は、依存型リレーション(実線)を表しています。親子関係であるテーブルの子テーブルが、親の存在に依存している場合に使用されます。

    以下の例は、非依存型リレーション(点線)を表しています。親子関係であるテーブルの子テーブルが、親の存在に依存していない場合に使用されます。

  4. カーディナリティ(多重度)について、リレーション終端が「直線」と「丸印」の場合は、1対n結合を表しています。以下の例では、“売上ヘッダ"と"売上明細"が1対n結合になります。

採点状態について

採点状態は以下のいずれかになります。

表記 ステータス 説明
AC Accepted 正しい結果が得られています。
WA Wrong Answer 正しい結果が得られていません。
RE Runtime Error 実行時にDBMSのエラーが発生しました。
TLE Time Limit Exceeded 採点時に時間制限を超過しました。
MLE Memory Limit Exceeded 採点時に使用した最大メモリが制限メモリを超過しました。
IE Internal Error 採点時に予期しないエラーが発生しました。このステータスの場合は採点が正しく行われていません。

答案全体のステータスは、各テストケースのステータスのうち、上記のリストの最も下にあるステータスが採用されます。また、誤答ではありませんが、以下のステータスがあります。

表記 ステータス 説明
WJ Waiting for Judging まだ採点処理が完了していません。

（参考）SQLiteの関数および構文

TOPSIC-SQLで使用可能なSQLiteの関数および構文の例を以下に掲載します。

1. スカラー関数

数値の絶対値を計算
ABS(X)

X の絶対値をとった結果を返す。

数値の四捨五入
ROUND(X)
ROUND(X, Y)

X を小数点以下 Y 桁に丸めた値を返す。 Y を省略した場合 0 とみなされる。

整数の擬似乱数を生成
RANDOM()

-9223372036854775808 から +9223372036854775807 までの範囲で整数の擬似乱数を返す。

BLOB 型の値を16進数表記に変換
HEX(X)

X を BLOB 型として解釈し、それを大文字の16進数で表現したときの文字列を返す。

文字列の文字数をカウント
LENGTH(X)

X が文字列の場合文字数を返す。 BLOB 型 の場合バイト数を返し、 NULL の場合 NULL を返す。

文字列を別の文字列に変換
REPLACE(X, Y, Z)

文字列 X 中に出現する文字列 Y を文字列 Z に置き換えた値を返す。

値が等しい場合 NULL を返す
NULLIF(X, Y)

X と Y が等しい場合 NULL を返す。異なる場合 X を返す。

値が NULL の場合に指定した値を返す
IFNULL(V1, V2)

引数の中で最初に NULL でない値を返す。両方が NULL の場合は NULL を返す。他RDBMSのISNULLやNVLに相当する。

COALESCE(V1, V2, ... , Vn)

引数の中で最初に NULL でない値を返す。すべて NULL の場合は NULL を返す。

文字列を全て大文字に変換
UPPER(X)

文字列 X を全て大文字に変換した値を返す。

文字列を全て小文字に変換
LOWER(X)

文字列 X を全て小文字に変換した値を返す。

文字列から連続する部分文字列を取得
SUBSTR(X, Y, Z)
SUBSTR(X, Y)

文字列 X について、 Y 番目の文字から Z 文字分(省略した場合末尾まで)の連続する部分文字列を取得する。 Y < 0の場合は末尾から逆算した地点を起点とする。Z < 0の場合、 Y + Z 番目の文字から -Z 文字を取得する。

文字列の先頭・末尾から指定文字を取り除く

TRIM(X)
TRIM(X, Y)

文字列 X の先頭および末尾から文字列 Y に含まれる要素を取り除いた結果を返す。 Y が省略された場合空白文字が除去される。類似する関数に RTRIM(末尾のみ) 、LTRIM(先頭のみ) がある。

2. 日付と時刻の関数

SQLiteは型としての日付型を持たないが、日付形式のフォーマットを変換する関数が実装されている。フォーマットはISO-8601に準拠するが、具体例などは公式ドキュメントを参照。

日付と時刻を変換
DATE(X, modifier, modifier, ...)
TIME(X, modifier, modifier, ...)
DATETIME(X, modifier, modifier, ...)
JULIANDAY(X, modifier, modifier, ...)

あるフォーマットに沿った X を各関数で定められたフォーマットに変換して返す。タイムゾーンにはUTCが用いられる。 X は通常 date(“2021-01-01 12:00:00”) のように文字列として与えられるが、ユリウス日として数値を渡すこともできる。

フォーマット指定で日付と時刻を変換
STRFTIME(format, X, modifier, modifier, ...)

X を指定したフォーマットに変換して返す。

3. 集合関数

行数のカウント
COUNT(X)

X(列名など) に含まれる NULL でない値の個数を返す。 X に * を指定した場合、 NULL を含む行数を返す。

カラムの最大値を取得
MAX(X)

X の中で最大の値を返す。 NULL でない値が存在しない場合のみ NULL を返す。

カラムの最小値を取得
MIN(X)

X の中で最小の値を返す。 NULL でない値が存在しない場合のみ NULL を返す。

カラムの合計値を取得
SUM(X)

X に含まれる NULL でない値を合計した結果を返す。 NULL でない値が存在しない場合 NULL を返す。

カラムの値の平均値を取得
AVG(X)

X の中で NULL でない値の平均を返す。 NULL でない値が存在しない場合 NULL を返す。

カラムに含まれる値を連結
GROUP_CONCAT(X)
GROUP_CONCAT(X, Y)

X のうちの全ての NULL でない値を区切り文字 Y で連結した文字列を返す。 Y を省略した場合はカンマ “,” を区切り文字とする。

4. 式と演算子

文字列結合
X || Y

文字列 X と 文字列 Y を結合する。他のRDBMSにおける文字列同士の + やCONCAT関数に相当する。

値が範囲内に含まれているかを比較
X BETWEEN Y AND Z

X が Y と Z の間に含まれている場合に真を返す。X >= Y AND X <= Z と同等。

複数の値に対する検索
X IN (V1, V2, ...)

X の値が V1、 V2、 … のいずれかに一致している場合に真を返す。 NOT IN を用いることで否定条件による検索も行うことができる。

パターンマッチングによる比較
X LIKE Y

文字列 X がパターン Y に合致している場合に真を返す。 Y には下記ワイルドカードを用いることができる。SQLiteにおいてはASCII文字の大文字/小文字を区別しない。

  • % : 任意の0文字以上の文字列
  • _ : 任意の1文字
X GLOB Y

文字列 X がパターン Y に合致している場合に真を返す。ワイルドカードとしてUnix系のglob構文を用いることができる。GLOBではLIKEと異なり大文字/小文字を区別する。

  • * : 任意の0文字以上の文字列
  • ? : 任意の1文字
  • [abc] : a、b、cのいずれかに一致
  • [a-d] : aからdまでのいずれかに一致
値が NULL かどうかを判定
X IS NULL

X が NULL の場合に真を返す。 IS の右側は NULL 以外の値もとることができるが、通常 IS NULL がセットで用いられる。

条件分岐
CASE
  WHEN 条件式1 THEN 式1
  WHEN 条件式2 THEN 式2
  ELSE 式3
END

各 WHEN 式の評価結果のうち最初に真となるときの THEN 式の評価結果を返す。どの WHEN 式も TRUE ではなかった場合、 ELSE 式の評価結果を返す。このとき、 ELSE 式が省略されている場合 NULL を返す。

5. その他構文・キーワード等

カラム等に別名を付ける
X AS Y

X(カラム名、テーブル名等) に対してエイリアス Y をつける。

重複データの除外
DISTINCT X ...

結果を返す前に重複行を削除する。

上限と開始位置を指定したデータの取得
LIMIT X
LIMIT Y, X
LIMIT X OFFSET Y

SELECT 文によって返される結果の行数上限を規定する。 OFFSET 句を追加した場合、先頭の Y 行が省略される。他のRDBMSとの互換性のために LIMIT Y, X の構文によっても OFFSET 句と同等の処理を行うことができる(公式ドキュメントでは非推奨)。