Github Copilot Chat にてマニュアルデータを表示するクエリ（SQL の SELECT 文）を作成した実践例

概要

Github Copilot にて マニュアルデータを表示するクエリを作成した際のプロンプトと出力結果を共有します。本記事では、Teradata Vantage の SQL を生成します。

マニュアルデータを表示するクエリとは、正確な名称を確認できいないのですが、Oracle Database では DUAL テーブルを利用してリテラルを SELECT 文を記載する方法です。

実践例

1. プロンプト

下記のデータからは、次のクエリが作成できます。

データ:"""
(801, NULL, 'Smith', 'John'),
(1003, 801, 'Johnson', 'Jane'),
"""

クエリ："""
SELECT CAST(801 AS INT) AS employee_number,
    CAST(NULL AS INT) AS manager_employee_number,
    CAST('Smith' AS CHAR(20)) AS last_name,
    CAST('John' AS VARCHAR(30)) AS first_name
    FROM (
    SELECT 1 AS "DUMMY") AS "DUAL"
UNION ALL
SELECT CAST(1003 AS INT) AS employee_number,
    CAST(801 AS INT) AS manager_employee_number,
    CAST('Johnson' AS CHAR(20)) AS last_name,
    CAST('Jane' AS VARCHAR(30)) AS first_name
    FROM (
    SELECT 1 AS "DUMMY") AS "DUAL"
"""

下記のデータから、クエリを作成してください。

データ："""
(801, NULL, 'Smith', 'John'),
(1003, 801, 'Johnson', 'Jane'),
(1019, 801, 'Williams', 'Bob'),
(1016, 801, 'Jones', 'Alice'),
(1008, 1019, 'Brown', 'Mike'),
(1006, 1019, 'Davis', 'Sara'),
(1014, 1019, 'Miller', 'Tom'),
(1011, 1019, 'Wilson', 'Emily'),
(1010, 1003, 'Taylor', 'Olivia'),
(1001, 1003, 'Anderson', 'Daniel'),
(1004, 6, 'Thomas', 'Sophia'),
(1012, 1004, 'Jackson', 'William'),
(1002, 1004, 'White', 'Isabella'),
(1015, 1004, 'Harris', 'Liam'),
"""

2. 出力結果

出力結果

SELECT CAST(801 AS INT) AS employee_number,
    CAST(NULL AS INT) AS manager_employee_number,
    CAST('Smith' AS CHAR(20)) AS last_name,
    CAST('John' AS VARCHAR(30)) AS first_name
    FROM (
    SELECT 1 AS "DUMMY") AS "DUAL"
UNION ALL
SELECT CAST(1003 AS INT) AS employee_number,
    CAST(801 AS INT) AS manager_employee_number,
    CAST('Johnson' AS CHAR(20)) AS last_name,
    CAST('Jane' AS VARCHAR(30)) AS first_name
    FROM (
    SELECT 1 AS "DUMMY") AS "DUAL"
UNION ALL
SELECT CAST(1019 AS INT) AS employee_number,
    CAST(801 AS INT) AS manager_employee_number,
    CAST('Williams' AS CHAR(20)) AS last_name,
    CAST('Bob' AS VARCHAR(30)) AS first_name
    FROM (
    SELECT 1 AS "DUMMY") AS "DUAL"
UNION ALL
SELECT CAST(1016 AS INT) AS employee_number,
    CAST(801 AS INT) AS manager_employee_number,
    CAST('Jones' AS CHAR(20)) AS last_name,
    CAST('Alice' AS VARCHAR(30)) AS first_name
    FROM (
    SELECT 1 AS "DUMMY") AS "DUAL"
UNION ALL
SELECT CAST(1008 AS INT) AS employee_number,
    CAST(1019 AS INT) AS manager_employee_number,
    CAST('Brown' AS CHAR(20)) AS last_name,
    CAST('Mike' AS VARCHAR(30)) AS first_name
    FROM (
    SELECT 1 AS "DUMMY") AS "DUAL"
UNION ALL
SELECT CAST(1006 AS INT) AS employee_number,
    CAST(1019 AS INT) AS manager_employee_number,
    CAST('Davis' AS CHAR(20)) AS last_name,
    CAST('Sara' AS VARCHAR(30)) AS first_name
    FROM (
    SELECT 1 AS "DUMMY") AS "DUAL"
UNION ALL
SELECT CAST(1014 AS INT) AS employee_number,
    CAST(1019 AS INT) AS manager_employee_number,
    CAST('Miller' AS CHAR(20)) AS last_name,
    CAST('Tom' AS VARCHAR(30)) AS first_name
    FROM (
    SELECT 1 AS "DUMMY") AS "DUAL"
UNION ALL
SELECT CAST(1011 AS INT) AS employee_number,
    CAST(1019 AS INT) AS manager_employee_number,
    CAST('Wilson' AS CHAR(20)) AS last_name,
    CAST('Emily' AS VARCHAR(30)) AS first_name
    FROM (
    SELECT 1 AS "DUMMY") AS "DUAL"
UNION ALL
SELECT CAST(1010 AS INT) AS employee_number,
    CAST(1003 AS INT) AS manager_employee_number,
    CAST('Taylor' AS CHAR(20)) AS last_name,
    CAST('Olivia' AS VARCHAR(30)) AS first_name
    FROM (
    SELECT 1 AS "DUMMY") AS "DUAL"
UNION ALL
SELECT CAST(1001 AS INT) AS employee_number,
    CAST(1003 AS INT) AS manager_employee_number,
    CAST('Anderson' AS CHAR(20)) AS last_name,
    CAST('Daniel' AS VARCHAR(30)) AS first_name
    FROM (
    SELECT 1 AS "DUMMY") AS "DUAL"
UNION ALL
SELECT CAST(1004 AS INT) AS employee_number,
    CAST(6 AS INT) AS manager_employee_number,
    CAST('Thomas' AS CHAR(20)) AS last_name,
    CAST('Sophia' AS VARCHAR(30)) AS first_name
    FROM (
    SELECT 1 AS "DUMMY") AS "DUAL"
UNION ALL
SELECT CAST(1012 AS INT) AS employee_number,
    CAST(1004 AS INT) AS manager_employee_number,
    CAST('Jackson' AS CHAR(20)) AS last_name,
    CAST('William' AS VARCHAR(30)) AS first_name
    FROM (
    SELECT 1 AS "DUMMY") AS "DUAL"
UNION ALL
SELECT CAST(1002 AS INT) AS employee_number,
    CAST(1004 AS INT) AS manager_employee_number,
    CAST('White' AS CHAR(20)) AS last_name,
    CAST('Isabella' AS VARCHAR(30)) AS first_name
    FROM (
    SELECT 1 AS "DUMMY") AS "DUAL"
UNION ALL
SELECT CAST(1015 AS INT) AS employee_number,
    CAST(1004 AS INT) AS manager_employee_number,
    CAST('Harris' AS CHAR(20)) AS last_name,
    CAST('Liam' AS VARCHAR(30)) AS first_name
    FROM (
    SELECT 1 AS "DUMMY") AS "DUAL"

3. 実行結果

想定通りに結果が出力されることを確認できました。