Friendly SQL with DuckDB (part 2)

friendly-duckdb-02.sql

/*
    Friendly SQL with DuckDB (part 2)

    DuckDB version: 1.0.0

    Bill Wallis, 2024-07-27

    Demonstrating some DuckDB features described by Alex Monahan at:

        https://duckdb.org/docs/sql/dialect/friendly_sql
*/


select version();


create or replace table suppliers (
    supplier_id integer primary key,
    stock_level integer,
    last_delivery timestamp,
    is_active boolean,
);
insert into suppliers
values
    (1, 251, '2024-01-01 12:04:58', false),
    (2,  24, '2024-06-02 04:10:19', true),
;

create or replace table depots (
    depot_id integer primary key,
    is_active boolean,
    stock_level integer,
    last_delivery timestamp,
);
insert into depots
values
    (3, true,  56, '2024-06-04 02:45:23'),
    (4, true, 192, '2024-06-05 03:14:41'),
;

create or replace table stores (
    store_id integer primary key,
    last_delivery timestamp,
    is_active boolean,
    stock_level integer,
);
insert into stores
values
    (5, '2024-06-07 05:02:33', true,  17),
    (6, '2024-06-08 08:34:02', true, 319),
;


    select supplier_id as location_id, * exclude (supplier_id) from suppliers
union all by name
    select depot_id as location_id, * exclude (depot_id) from depots
union all by name
    select store_id as location_id, * exclude (store_id) from stores
;

/* FROM-first approach */
    from suppliers select supplier_id as location_id, * exclude (supplier_id)
union all by name
    from depots    select depot_id as location_id,    * exclude (depot_id)
union all by name
    from stores    select store_id as location_id,    * exclude (store_id)
;


/* Column positions */
with

suppliers as (from information_schema.columns where table_name = 'suppliers'),
depots    as (from information_schema.columns where table_name = 'depots'),
stores    as (from information_schema.columns where table_name = 'stores')

from suppliers
    full join depots using (ordinal_position)
    full join stores using (ordinal_position)
select
    ordinal_position,
    suppliers.column_name as "suppliers.column_name",
    depots.column_name as "depots.column_name",
    stores.column_name as "stores.column_name",
;

This is part of the following LinkedIn post:

https://www.linkedin.com/posts/bill-wallis_sql-analyticsengineering-dataengineering-activity-7226512368413913092-cZ2F