[Task]: Polish Beam SQL user experience #35637

What needs to happen?

We have added two critical pieces to Beam SQL: Catalog and CatalogManager (#35223), but some features are still missing:

• Support DDL for DATABASEs (create, use, drop) [Beam SQL] Support DATABASE concept in Beam SQL, with implementation for Iceberg #35641
• If a user has set a DATABASE, they should be able to read an existing table without having to run a redundant CREATE EXTERNAL TABLE... Implement a hierarchy for Beam SQL Calcite Schemas #35787
• We should infer the table name from the reference, instead of requiring an explicit LOCATION property Implement a hierarchy for Beam SQL Calcite Schemas #35787
• Support SHOW statements [SQL] add SHOW command #36509
• Support ALTER TABLE, CATALOG [SQL] Alter Catalog and Table statements #36571
• Add Iceberg CDC table

For reference, Flink and Spark syntax

Issue Priority

Priority: 2 (default / most normal work should be filed as P2)

Issue Components

• Component: Python SDK
• Component: Java SDK
• Component: Go SDK
• Component: Typescript SDK
• Component: IO connector
• Component: Beam YAML
• Component: Beam examples
• Component: Beam playground
• Component: Beam katas
• Component: Website
• Component: Infrastructure
• Component: Spark Runner
• Component: Flink Runner
• Component: Samza Runner
• Component: Twister2 Runner
• Component: Hazelcast Jet Runner
• Component: Google Cloud Dataflow Runner