Databricks Lab & Excercise – Notebook – Unity Catalog → schema → table

by

let’s make this a “Databricks SQL Quickstart – 25 Commands” guide for first-time use in the Notebook with the Unity Catalog → schema → table workflow.

I’ll order these so you can start from catalog exploration, work with schemas & tables, and query & manage data — exactly how you’d try things in a fresh Databricks SQL-enabled environment.

Part 1 – Catalog & Schema Exploration (1–7)

-- 1. List all catalogs
SHOW CATALOGS;

-- 2. Switch to a specific catalog
USE CATALOG main;

-- 3. Show schemas in the current catalog
SHOW SCHEMAS;

-- 4. Switch to a specific schema (database)
USE SCHEMA default;

-- 5. Show all tables in current schema
SHOW TABLES;

-- 6. Show current catalog and schema
SELECT current_catalog(), current_schema();

-- 7. Describe schema details
DESCRIBE SCHEMA EXTENDED default;

Part 2 – Table Exploration & Metadata (8–12)

-- 8. Describe a specific table
DESCRIBE TABLE my_table;

-- 9. Show extended metadata for a table
DESCRIBE TABLE EXTENDED my_table;

-- 10. Show columns of a table
SHOW COLUMNS IN my_table;

-- 11. Get table history (with Unity Catalog)
DESCRIBE HISTORY my_table;

-- 12. Preview table data
SELECT * FROM my_table LIMIT 10;

Part 3 – Table Creation & Data Insertion (13–17)

-- 13. Create a managed table
CREATE TABLE sales (
    id INT,
    product STRING,
    quantity INT,
    price DOUBLE
);

-- 14. Insert sample rows
INSERT INTO sales VALUES
(1, 'Laptop', 2, 800.00),
(2, 'Mouse', 5, 20.50),
(3, 'Keyboard', 3, 45.99);

-- 15. Create an external table (from a path)
CREATE TABLE ext_sales
USING PARQUET
LOCATION 'dbfs:/mnt/data/sales/';

-- 16. Create a table from query results
CREATE TABLE top_sales AS
SELECT * FROM sales WHERE quantity > 2;

-- 17. Overwrite a table with new data
INSERT OVERWRITE sales VALUES
(10, 'Monitor', 4, 150.00);

Part 4 – Querying & Filtering Data (18–22)

-- 18. Select specific columns
SELECT product, price FROM sales;

-- 19. Filter with WHERE
SELECT * FROM sales WHERE price > 50;

-- 20. Aggregate with GROUP BY
SELECT product, SUM(quantity) AS total_qty
FROM sales
GROUP BY product;

-- 21. Sort results
SELECT * FROM sales ORDER BY price DESC;

-- 22. Use LIMIT
SELECT * FROM sales ORDER BY quantity DESC LIMIT 5;

Part 5 – Maintenance & Management (23–25)

-- 23. Drop a table
DROP TABLE IF EXISTS top_sales;

-- 24. Rename a table
ALTER TABLE sales RENAME TO sales_data;

-- 25. Optimize a table for performance (Delta table)
OPTIMIZE sales_data;

Extra Notebook Tips

  • In Databricks, prefix a cell with %sql to run SQL in a Python notebook.
  • If your table is Unity Catalog-managed, you’ll use catalog.schema.table format (e.g., main.default.sales).
  • For interactive tables, use display() in a %python cell after running a %sql query.

Leave a Comment