Query and Explore Data in Databricks SQL

Table of Contents

1. Training overview
2. Understanding query execution in Databricks SQL

• 1.1 Executing ANSI-compliant queries against Delta tables
• 1.2 Analyzing query execution and performance characteristics
• 1.3 Navigating the query editor and results pane
• 1.4 Organizing and saving queries in workspaces

3. Writing SQL queries for analysis

• 2.1 Retrieving data with SELECT and WHERE conditions
• 2.2 Improving query readability with aliases, sorting, and expressions
• 2.3 Aggregating data with GROUP BY and HAVING
• 2.4 Combining tables with JOIN operations
• 2.5 Transforming data with built-in SQL functions

4. Organizing data for repeatable workflows

• 3.1 Browsing catalogs, schemas, and tables in Unity Catalog
• 3.2 Creating and managing views for organized query logic
• 3.3 Simplifying complex queries with CTEs and subqueries
• 3.4 Managing query history and tracking exploration progress

5. Demo Database Schema
6. Full demo query reference
7. Summary of key concepts

1. Training overview

Context and motivation

This training addresses a concrete problem that every data team encounters: the data lives in the lakehouse, and the team needs answers. Before Databricks SQL, the typical workflow looked like this: export the data to CSV, open it in Excel, build formulas, and get a basic answer 30 minutes later. The next day, someone asks the same question with a different filter — and it starts again.

Databricks SQL offers a straightforward alternative: write standard SQL directly against Delta tables, get results in seconds from the lakehouse, and save these queries for the entire team to reuse.

Educational objectives

This training covers three main skills:

1. Write SQL against Delta tables — ANSI-compliant syntax, filters, aggregations, joins, functions
2. Get quick results — understand result caching, Serverless SQL warehouses, performance mechanisms
3. Save and share work — organization of queries, version history, sharing with the team, permanent views, CTEs

Demo Database

All training uses a sales database main.sales made up of three tables:

Note: All demo files (table creation script, all queries) are included in the course materials.

2. Understanding query execution in Databricks SQL

2.1 Executing ANSI-compliant queries against Delta tables

Concepts covered

Databricks SQL is a SQL-native interface for querying data in the lakehouse. If you’ve written SQL before, you already know most of what you need in Databricks SQL: the syntax is ANSI-compliant, meaning it conforms to the SQL standard.

The customers table

The customers table stores basic information about buyers.

-- Voir toutes les colonnes de la table customers
SELECT * 
FROM main.sales.customers;

Result:

Observations: real data is often imperfect — inconsistent case (MICHAEL, sarah, JAMES), NULL value for Ava Thomas email, duplicate email between Sarah Johnson and Liam Taylor.

-- Sélectionner uniquement les colonnes nécessaires
SELECT first_name, 
       last_name, 
       email 
FROM main.sales.customers;

Key principle: SELECT * is convenient for mining, but it is better to name the columns explicitly to limit the amount of data transferred and to clarify the intent of the query.

The products table

SELECT * 
FROM main.sales.products;

Result:

Note: The Coffee Mug has no assigned category (NULL).

The orders table

SELECT * 
FROM main.sales.orders;
-- (25 commandes au total)

-- Sélectionner les colonnes d'intérêt
SELECT order_id, 
       customer_id, 
       order_amount, 
       order_date 
FROM main.sales.orders;

Partial result (first 10 out of 25):

-- Filtrer sur l'année en cours
SELECT order_id, 
       customer_id, 
       order_amount, 
       order_date 
FROM main.sales.orders 
WHERE order_date >= '2026-01-01';
-- (20 résultats)

-- Filtrer sur l'année en cours ET les montants élevés
SELECT order_id, 
       customer_id, 
       order_amount, 
       order_date 
FROM main.sales.orders 
WHERE order_date >= '2026-01-01' 
AND order_amount > 500;
-- (4 résultats)

2.2 Analyzing query execution and performance characteristics

Result caching — key performance mechanism

Databricks SQL automatically caches query results. This mechanism is one of the main performance advantages.

How ​​it works:

1. First run — Databricks reads from Delta tables, calculates the results, returns them to the screen AND automatically caches them.
2. Second execution of the same query — Databricks recognizes the identical query and returns the cached results instead of rereading the Delta tables → 6x faster.
3. Slightly modified query — even if only one alias changes, Databricks executes it from scratch (cache miss).

Concrete example observed:

-- Première exécution : ~0.3 secondes
SELECT * 
FROM main.sales.products;

-- Deuxième exécution (identique) : ~0.05 secondes (cache hit)
SELECT * 
FROM main.sales.products;

-- Requête légèrement différente : ~6 secondes (cache miss)
SELECT product_id, 
       product_name AS name, 
       price 
FROM main.sales.products;

Important rule: Caching works on requests identical up to the character. Any change — even a column alias — causes a cache miss and a complete execution.

The Serverless SQL Warehouse

Behind the queries is a SQL warehouse, the calculation engine that processes the SQL. In Databricks SQL, Serverless SQL warehouse is the recommended type:

• It boots in seconds (not minutes like traditional clusters)
• It automatically adapts to the load
• It supports automatic sleep after a period of inactivity

Execution architecture:

Requête SQL (SQL editor)
        ↓
  SQL Warehouse (moteur de calcul)
        ↓
   Delta Tables (stockage dans le lakehouse)
        ↓
   Results pane (résultats affichés)
        ↓
   Result Cache (stockage automatique des résultats)

Query Profile — performance analysis

After each execution, the Query Profile in the results pane exposes:

• The physical execution plan
• Time spent in each step
• The number of rows read and processed
• Delta files read
• Whether the cache was used or not

2.3 Navigating the query editor and results pane

SQL Editor interface

The Databricks SQL Editor is made up of four main areas:

Editor Features

• Syntax highlighting — SQL keywords (SELECT, FROM, WHERE) are colored
• Autocomplete — when entering FROM main., the editor suggests the available schemas; an extra dot shows the tables (customers, orders, products)
• These features reduce typos and help discover available items on the fly

Results pane interactivity

The results pane isn’t just a display — it’s an interactive tool for understanding the data:

• Click on a column header — sort ascending on first click, sort descending on second
• Example: click on order_amount → sort ascending (44.99 on top) → click again → sort descending (1299.99 on top)
• No additional SQL required for this initial exploration

Recommended iterative approach

-- Étape 1 : voir la forme des données
SELECT * 
FROM main.sales.orders;
-- → 25 commandes, 7 colonnes

-- Étape 2 : affiner les colonnes d'intérêt
SELECT order_id, 
       customer_id, 
       order_amount, 
       order_date 
FROM main.sales.orders;

-- Étape 3 : ajouter des filtres
SELECT order_id, 
       customer_id, 
       order_amount, 
       order_date 
FROM main.sales.orders 
WHERE order_date >= '2026-01-01';

Principle: Start broad, look at the results, then refine. It’s a natural exploration loop, and the SQL editor is designed for that.

2.4 Organizing and saving queries in workspaces

Save a query

A query not saved in a tab disappears when closing the browser. To keep it:

1. Click Save in the toolbar
2. Give a clear and descriptive name (e.g. High Value Orders rather than Untitled query)
3. The query is now in the personal workspace and can be reopened, edited, re-run

Example of saved queries:

-- Requête "High Value Orders" (seuil initial : 500)
SELECT order_id, 
       customer_id, 
       order_amount, 
       order_date 
FROM main.sales.orders 
WHERE order_amount > 500;

-- Requête "New Customers 2026"
SELECT first_name, 
       last_name, 
       email, 
       registration_date 
FROM main.sales.customers 
WHERE registration_date >= '2026-01-01';

Result of “New Customers 2026”:

Version History

Each saved change is saved automatically. Example with High Value Orders:

-- Version 1 : seuil à 500 → 6 résultats
SELECT order_id, 
       customer_id, 
       order_amount, 
       order_date 
FROM main.sales.orders 
WHERE order_amount > 500;

-- Version 2 : seuil monté à 750 → 5 résultats (la commande à 549 disparaît)
SELECT order_id, 
       customer_id, 
       order_amount, 
       order_date 
FROM main.sales.orders 
WHERE order_amount > 750;

To access the history: click on the name of the query → Version history → restore any previous version in one click.

Advantage: You can experiment freely without fear of losing a version that worked.

Query sharing

Via the Share button, you can add team members by email and grant them view or edit access. Once shared, the query appears in their workspace. No more need for each member to reconstruct what someone else has already written.

3. Writing SQL queries for analysis

3.1 Retrieving data with SELECT and WHERE conditions

Basic structure of an SQL query

SELECT colonnes    -- obligatoire : spécifie les colonnes à retourner
FROM table         -- obligatoire : spécifie la table source
WHERE conditions;  -- optionnel : filtre les lignes

SELECT and FROM are required. WHERE is optional but it is what transforms a raw data dump into a precise response.

Simple comparison operators

The simplest WHERE filters use comparison operators. The pattern is always: column operator value. They work with numbers, dates and text.

-- Égalité sur du texte : trouver le client Smith
SELECT first_name, 
       last_name, 
       email 
FROM main.sales.customers 
WHERE last_name = 'Smith';

-- Supérieur ou égal sur une date : clients de cette année
SELECT first_name, 
       last_name, 
       email, 
       registration_date 
FROM main.sales.customers 
WHERE registration_date >= '2026-01-01';

Specialized operators

BETWEEN — range of values ​​(including limits)

-- Produits entre 100 et 500 euros
SELECT product_name, 
       category, 
       price 
FROM main.sales.products 
WHERE price BETWEEN 100 AND 500;

IN — match with a list of values

-- Produits dans deux catégories
SELECT product_name, 
       category, 
       price 
FROM main.sales.products 
WHERE category IN ('Electronics','Furniture');

IS NULL — missing values

-- Produits sans catégorie assignée
SELECT product_name, 
       category, 
       price 
FROM main.sales.products 
WHERE category IS NULL;

Warning: Never use = NULL. In SQL, NULL cannot be compared with =. Always use IS NULL or IS NOT NULL.

LIKE — pattern matching

-- Produits dont le nom commence par 'S'
SELECT product_name, 
       category, 
       price 
FROM main.sales.products 
WHERE product_name LIKE 'S%';

Wildcards: % matches any zero or more characters. _ matches exactly any character.

Combination of conditions: AND, OR, parentheses

OR — inclusive: at least one condition is true

-- Commandes de haute valeur OU en attente
SELECT order_id, 
       customer_id, 
       order_amount, 
       status 
FROM main.sales.orders 
WHERE order_amount > 500 
OR status = 'pending';
-- 9 résultats

AND — restrictive: all conditions must be true

-- Commandes de haute valeur ET en attente
SELECT order_id, 
       customer_id, 
       order_amount, 
       status 
FROM main.sales.orders 
WHERE order_amount > 500 
AND status = 'pending';

Parenthesis — evaluation priority control

-- Commandes de haute valeur ET (en attente OU en traitement)
SELECT order_id, 
       customer_id, 
       order_amount, 
       status 
FROM main.sales.orders 
WHERE order_amount > 500 
AND (status = 'pending' OR status = 'processing');

Important rule: Without parentheses, AND is evaluated before OR. Parentheses make the intention explicit and avoid surprises.

3.2 Improving query readability with aliases, sorting, and expressions

Column aliases with AS

Database column names (order_amount, customer_id) are suitable for developers but confusing for others. The AS alias allows you to assign a display name to any column.

-- Noms de colonnes bruts (peu lisibles)
SELECT order_id, 
       customer_id, 
       order_amount, 
       order_date 
FROM main.sales.orders;

-- Avec des aliases lisibles
SELECT order_id AS `Order ID`, 
       customer_id AS `Customer ID`, 
       order_amount AS `Amount`, 
       order_date AS `Date` 
FROM main.sales.orders;

Syntax: Aliases with spaces must be enclosed in backticks (`) or single quotes. Single-word aliases work without quotes, but using quotes consistently is recommended.

Important: Aliases do not change anything in the underlying table. They only rename columns in the query output.

Sorting results with ORDER BY

Without ORDER BY, the order of results is not guaranteed. A change in data or an execution plan could rearrange them differently tomorrow.

-- Tri par date décroissant : les plus récents en premier
SELECT order_id AS `Order ID`, 
       customer_id AS `Customer ID`, 
       order_amount AS `Amount`, 
       order_date AS `Date` 
FROM main.sales.orders 
ORDER BY order_date DESC;

-- Tri multi-colonnes : statut A→Z, puis montant du plus grand au plus petit
SELECT order_id AS `Order ID`, 
       status AS `Status`, 
       order_amount AS `Amount` 
FROM main.sales.orders 
ORDER BY status ASC, order_amount DESC;

Partial result:

Expressions calculated in SELECT

Expressions allow you to create derived columns on the fly, without modifying the data.

Arithmetic calculations:

-- Calcul de taxe et total avec taxe
SELECT order_id AS `Order ID`, 
       order_amount AS `Subtotal`, 
       order_amount * 0.10 AS `Tax`, 
       order_amount * 1.10 AS `Total with Tax` 
FROM main.sales.orders 
ORDER BY order_amount DESC;

-- Expressions chaînées avec parenthèses
SELECT order_id AS `Order ID`, 
       order_amount AS `Original Amount`, 
       order_amount - 50 AS `After Discount`, 
       (order_amount - 50) * 1.10 AS `After Discount with Tax` 
FROM main.sales.orders 
ORDER BY order_amount DESC;

String concatenation:

-- Nom complet depuis deux colonnes (opérateur || )
SELECT customer_id AS `Customer ID`, 
       first_name || ' ' || last_name AS `Full Name`, 
       email AS `Email` 
FROM main.sales.customers 
ORDER BY last_name ASC, first_name ASC;

Note: The || operator is equivalent to CONCAT(). Both syntaxes work in Databricks SQL. Aliases are essential on calculated expressions — without them, the header displays the raw formula.

3.3 Aggregating data with GROUP BY and HAVING

Aggregate functions — the fundamental five

An aggregate function takes multiple rows and returns a single summary value. It answers the questions: how many?, how many total?, what is the average?, what is the smallest/largest?

-- COUNT(*) : effondre toute la table en un seul nombre
SELECT COUNT(*) AS `Total Orders` 
FROM main.sales.orders;
-- Résultat : 25

-- Les cinq agrégats côte à côte
SELECT COUNT(*) AS `Total Orders`, 
       SUM(order_amount) AS `Total Revenue`, 
       AVG(order_amount) AS `Average Order`, 
       MIN(order_amount) AS `Smallest Order`, 
       MAX(order_amount) AS `Largest Order` 
FROM main.sales.orders;

The three variants of COUNT

-- COUNT(*) vs COUNT(colonne) vs COUNT(DISTINCT colonne)
SELECT COUNT(*) AS `Total Customers`, 
       COUNT(email) AS `With Email`, 
       COUNT(DISTINCT email) AS `Unique Emails` 
FROM main.sales.customers;

Interpretation:

• COUNT(*) = 12: counts all lines
• COUNT(email) = 11: Ava Thomas has no email (NULL ignored)
• COUNT(DISTINCT email) = 10: sarah johnson and liam taylor share the same address (sarah.j@example.com)

Rule: COUNT(*) counts all lines. COUNT(column) ignores NULLs. COUNT(DISTINCT column) ignores NULLs AND duplicates.

GROUP BY — aggregation by category

-- Par statut de commande : une ligne par statut avec métriques
SELECT status AS `Status`, 
       COUNT(*) AS `Order Count`, 
       SUM(order_amount) AS `Revenue` 
FROM main.sales.orders 
GROUP BY status;

-- Par client : les plus gros dépensiers en premier
SELECT customer_id AS `Customer`, 
       COUNT(*) AS `Orders`, 
       SUM(order_amount) AS `Total Spent`, 
       AVG(order_amount) AS `Avg Order` 
FROM main.sales.orders 
GROUP BY customer_id 
ORDER BY SUM(order_amount) DESC;

HAVING — filter groups

WHERE filters individual rows before grouping. HAVING filters groups after aggregation. You cannot use WHERE with an aggregate function.

-- HAVING seul : clients avec total > 1000
SELECT customer_id AS `Customer`, 
       COUNT(*) AS `Orders`, 
       SUM(order_amount) AS `Total Spent` 
FROM main.sales.orders 
GROUP BY customer_id 
HAVING SUM(order_amount) > 1000 
ORDER BY SUM(order_amount) DESC;

-- WHERE + GROUP BY + HAVING combinés
-- Commandes complétées seulement, clients avec total > 500
SELECT customer_id AS `Customer`, 
       COUNT(*) AS `Completed Orders`, 
       SUM(order_amount) AS `Completed Revenue` 
FROM main.sales.orders 
WHERE status = 'completed' 
GROUP BY customer_id 
HAVING SUM(order_amount) > 500 
ORDER BY SUM(order_amount) DESC;

Logical execution order of an SQL query:

FROM       → 1. Identifier la table source
WHERE      → 2. Filtrer les lignes individuelles
GROUP BY   → 3. Regrouper les lignes restantes
HAVING     → 4. Filtrer les groupes résultants
SELECT     → 5. Calculer les expressions et renommer les colonnes
ORDER BY   → 6. Trier le résultat final

3.4 Combining tables with JOIN operations

The problem and the solution

Interesting data lives in connections between tables. Customer names are in customers, their orders in orders. A JOIN matches rows from two tables based on a shared column.

INNER JOIN — only matching rows

-- INNER JOIN : version longue avec noms de tables complets
SELECT customers.first_name AS `First Name`, 
       customers.last_name AS `Last Name`, 
       orders.order_id AS `Order ID`, 
       orders.order_amount AS `Amount`, 
       orders.order_date AS `Date` 
FROM main.sales.customers 
INNER JOIN main.sales.orders ON customers.customer_id = orders.customer_id 
ORDER BY orders.order_date DESC;

-- INNER JOIN : version avec aliases de tables (syntaxe préférée)
SELECT c.first_name AS `First Name`, 
       c.last_name AS `Last Name`, 
       o.order_id AS `Order ID`, 
       o.order_amount AS `Amount`, 
       o.order_date AS `Date` 
FROM main.sales.customers c 
INNER JOIN main.sales.orders o ON c.customer_id = o.customer_id 
ORDER BY o.order_date DESC;

Partial result:

Key behavior of the INNER JOIN: 25 rows returned = as many as orders. Customers without orders (Sophia Brown, Noah GARCIA) do not appear. Customers with multiple orders (John Smith, MICHAEL CHEN) appear multiple times. Only rows that have a match in both tables are returned.

LEFT JOIN — keep all left records

-- LEFT JOIN : garde les clients sans commandes (NULLs côté droit)
SELECT c.first_name AS `First Name`, 
       c.last_name AS `Last Name`, 
       o.order_id AS `Order ID`, 
       o.order_amount AS `Amount` 
FROM main.sales.customers c 
LEFT JOIN main.sales.orders o ON c.customer_id = o.customer_id 
ORDER BY o.order_amount DESC;

Anti-join pattern — customers who never ordered:

-- Clients qui n'ont jamais passé de commande
SELECT c.first_name AS `First Name`, 
       c.last_name AS `Last Name` 
FROM main.sales.customers c 
LEFT JOIN main.sales.orders o ON c.customer_id = o.customer_id 
WHERE o.order_id IS NULL;

Anti-join pattern: LEFT JOIN + WHERE right_column IS NULL = find rows from the left table that have no matches in the right table.

FULL OUTER JOIN — preserve both sides

-- FULL OUTER JOIN : préserve les deux côtés sans correspondance
SELECT c.first_name AS `First Name`, 
       c.last_name AS `Last Name`, 
       o.order_id AS `Order ID`, 
       o.order_amount AS `Amount` 
FROM main.sales.customers c 
FULL OUTER JOIN main.sales.orders o ON c.customer_id = o.customer_id 
ORDER BY c.last_name;

Partial result:

JOIN on three tables

-- Three-table join : customers + orders + products
SELECT c.first_name || ' ' || c.last_name AS `Customer`, 
       p.product_name AS `Product`, 
       o.quantity AS `Quantity`, 
       o.order_amount AS `Amount`, 
       o.order_date AS `Date` 
FROM main.sales.customers c 
INNER JOIN main.sales.orders o ON c.customer_id = o.customer_id 
INNER JOIN main.sales.products p ON o.product_id = p.product_id 
ORDER BY o.order_date DESC;

Partial result:

Summary of JOIN types

3.5 Transforming data with built-in SQL functions

Text functions

UPPER() and LOWER() — case normalization

SELECT first_name, 
       UPPER(first_name) AS `Uppercase`, 
       LOWER(first_name) AS `Lowercase` 
FROM main.sales.customers;

Important use case: without case normalization, 'Smith' and 'SMITH' would be treated as different values ​​in comparisons and groupings.

CONCAT() and text extraction

-- CONCAT + SUBSTRING : extraire la partie avant le @ dans un email
SELECT CONCAT(first_name, ' ', last_name) AS `Full Name`, 
       substr(email, 1, instr(email,'@') - 1) AS `Email Username` 
FROM main.sales.customers 
WHERE email IS NOT NULL;

Explanation: instr(email, '@') finds the position of the @ symbol. substr(email, 1, position-1) extracts everything from the beginning to the character before @.

TRIM() — remove invisible spaces

SELECT TRIM(first_name) AS `Trimmed Name` 
FROM main.sales.customers;

Why it’s critical: leading/trailing spaces are not visible but cause silent problems: joins fail, groupings split into unexpected categories, comparisons return false.

Numeric functions

ROUND(), CEIL(), FLOOR() — rounded

-- Trois comportements d'arrondi côte à côte
SELECT order_amount AS `Raw Amount`, 
       ROUND(order_amount, 0) AS `Rounded`, 
       CAST((order_amount + 0.999999) AS INTEGER) AS `Rounded Up`, 
       CAST(order_amount AS INTEGER) AS `Rounded Down` 
FROM main.sales.orders 
ORDER BY order_amount DESC;

ABS() — absolute value

-- Distance par rapport à 500, triée par proximité
SELECT order_amount AS `Amount`, 
       order_amount - 500 AS `Difference from 500`, 
       ABS(order_amount - 500) AS `Distance from 500` 
FROM main.sales.orders 
ORDER BY ABS(order_amount - 500);

Date functions

DATEDIFF() — number of days between two dates

-- Jours écoulés depuis chaque commande
SELECT order_id AS `Order ID`, 
       order_date AS `Order Date`, 
       DATE('2026-05-07') AS `Today`, 
       DATEDIFF('2026-05-07', order_date) AS `Days Since Order` 
FROM main.sales.orders 
ORDER BY order_date DESC;

DATE_FORMAT() — format a date

-- Forme lisible et forme de regroupement
SELECT order_id AS `Order ID`, 
       order_date AS `Raw Date`, 
       DATE_FORMAT(order_date, 'MMMM d, yyyy') AS `Formatted Date`, 
       DATE_FORMAT(order_date, 'yyyy-MM') AS `Year-Month` 
FROM main.sales.orders 
ORDER BY order_date DESC;

4. Organizing data for repeatable workflows

4.1 Browsing catalogs, schemas, and tables in Unity Catalog

The three-level namespace

Each table in Databricks lives at a specific address: catalog.schema.table. You used this three-part path in every query in this course (main.sales.customers, main.sales.orders). Here is what these three levels mean:

catalog                        ← niveau le plus haut (département ou limite de projet)
  └── schema                   ← organisation par thème ou domaine
        ├── table_1            ← données réelles
        ├── table_2
        └── view_1

This three-tier structure is part of Unity Catalog, the governance layer that controls who can access what. Writing main.sales.customers specifies exactly where this table is located in the hierarchy — no ambiguity.

Explore visually with Data Explorer

1. Click on the Data icon in the left sidebar of the SQL editor
2. A list of catalogs appears — expand main
3. Schemas appear — expand sales
4. The tables appear: customers, orders, products
5. Click on customers: the columns and their data type are displayed
6. Sample Data tab: see the first lines without writing SQL

Explore with DESCRIBE

-- Afficher la structure (colonnes, types, commentaires) d'une table
DESCRIBE main.sales.customers;

DESCRIBE is particularly useful in environments where the GUI is not available, or when you want to document the structure of a table in a query.

4.2 Creating and managing views for organized query logic

Two types of views in Databricks SQL

A view is a saved query that you reference by name, as a shortcut to a specific slice of your data. Databricks supports two types:

Temporary views

-- Créer une vue temporaire pour les clients à haute valeur
CREATE OR REPLACE TEMPORARY VIEW high_value_customers AS 
SELECT c.customer_id, 
       c.first_name, 
       c.last_name, 
       SUM(o.order_amount) AS total_spent 
FROM main.sales.customers c 
JOIN main.sales.orders o ON c.customer_id = o.customer_id 
GROUP BY c.customer_id, c.first_name, c.last_name 
HAVING SUM(o.order_amount) > 500;
-- (view created — aucune ligne retournée)

-- Interroger la vue : tous les clients au-dessus de 500
SELECT * 
FROM high_value_customers 
ORDER BY total_spent DESC;

-- Réutiliser la vue pour un COUNT
SELECT COUNT(*) AS high_value_count 
FROM high_value_customers;
-- Résultat : 8

-- Réutiliser la vue pour une moyenne
SELECT ROUND(AVG(total_spent), 2) AS avg_high_value_spend 
FROM high_value_customers;
-- Résultat : 1237.12

Permanent views

-- Créer une vue permanente dans le catalog
CREATE OR REPLACE VIEW main.sales.high_value_customers AS 
SELECT c.customer_id, 
       c.first_name, 
       c.last_name, 
       SUM(o.order_amount) AS total_spent 
FROM main.sales.customers c 
JOIN main.sales.orders o ON c.customer_id = o.customer_id 
GROUP BY c.customer_id, c.first_name, c.last_name 
HAVING SUM(o.order_amount) > 500;

-- Mettre à jour la vue permanente (changer le seuil à 1000)
CREATE OR REPLACE VIEW main.sales.high_value_customers AS 
SELECT c.customer_id, 
       c.first_name, 
       c.last_name, 
       SUM(o.order_amount) AS total_spent 
FROM main.sales.customers c 
JOIN main.sales.orders o ON c.customer_id = o.customer_id 
GROUP BY c.customer_id, c.first_name, c.last_name 
HAVING SUM(o.order_amount) > 1000;

-- Après CREATE OR REPLACE avec seuil 1000 : moins de clients
SELECT * 
FROM main.sales.high_value_customers 
ORDER BY total_spent DESC;

-- Supprimer une vue permanente
DROP VIEW IF EXISTS main.sales.high_value_customers;

-- Supprimer une vue temporaire
DROP VIEW IF EXISTS high_value_customers;

Behavior of CREATE OR REPLACE VIEW: if the view does not exist, it is created. If it already exists, it is replaced without error. No more need for DROP then CREATE. This is the recommended pattern.

4.3 Simplifying complex queries with CTEs and subqueries

Two tools for organizing logic inside a query

Difference with views: subqueries and CTEs disappear at the end of query execution. No session persistence, no catalog entry, no cleanup required.

Nested subqueries

-- Clients dont le total dépasse la moyenne des totaux de tous les clients
SELECT c.first_name, 
       c.last_name, 
       SUM(o.order_amount) AS total_spent 
FROM main.sales.customers c 
JOIN main.sales.orders o ON c.customer_id = o.customer_id 
GROUP BY c.first_name, c.last_name 
HAVING SUM(o.order_amount) > (SELECT AVG(total) 
                              FROM (SELECT SUM(order_amount) AS total 
                                    FROM main.sales.orders 
                                    GROUP BY customer_id)
                              ) 
ORDER BY total_spent DESC;

Logic breakdown:

1. The innermost subquery calculates the total per customer: SUM(order_amount) GROUP BY customer_id
2. The external subquery calculates the average of these totals: AVG(total)
3. The external query filters customers whose total exceeds this average

CTEs — Common Table Expressions

The same logic, written as a multi-step CTE for better readability:

-- CTE en deux étapes : customer lifetime value vs. moyenne
WITH customer_totals AS (
    SELECT c.customer_id, 
           c.first_name, 
           c.last_name, 
           SUM(o.order_amount) AS total_spent, 
           COUNT(o.order_id) AS order_count 
    FROM main.sales.customers c 
    JOIN main.sales.orders o ON c.customer_id = o.customer_id 
    GROUP BY c.customer_id, c.first_name, c.last_name
), 
avg_spending AS (
    SELECT ROUND(AVG(total_spent), 2) AS avg_total 
    FROM customer_totals
) 
SELECT ct.first_name, 
       ct.last_name, 
       ct.total_spent, 
       ct.order_count, 
       a.avg_total, 
       ROUND(ct.total_spent - a.avg_total, 2) AS above_average_by 
FROM customer_totals ct 
CROSS JOIN avg_spending a 
WHERE ct.total_spent > a.avg_total 
ORDER BY ct.total_spent DESC;

Structure of a CTE:

WITH nom_cte_1 AS (
    -- requête 1
),
nom_cte_2 AS (
    -- requête 2 (peut référencer nom_cte_1)
)
SELECT ...
FROM nom_cte_1 ct
JOIN nom_cte_2 a ON ...

Advantages of CTE vs. Subquery:

• Reads from top to bottom like a plan, each step is named
• Each block can be tested independently
• Less nesting = fewer errors
• The same CTE may be referenced multiple times in the same query

4.4 Managing query history and tracking exploration progress

Query History — automatically saves everything

Unlike saved queries (which require explicitly clicking Save), Query History captures everything you run, including throwaway experiments and happy discoveries that you didn’t think to save.

Access: Click on Query History in the left sidebar.

Each entry displays:

• The full SQL text (first lines)
• Execution timestamp
• Execution time
• Status (success or failure)
• The warehouse that processed it

History filtering

Open a query from history

1. Click on an entry in the history (eg: the CTE of the previous clip)
2. The full SQL text is displayed, with execution details (duration, rows returned, warehouse)
3. Click on Open in Editor → the query loads in a new tab, ready to be relaunched or modified

History value for long crawl sessions

During a long exploration session — where you start with a vague question and end up somewhere unexpected — history becomes particularly valuable:

• You try a filter, adjust a threshold, obtain a surprising result
• You want to return to a previous query that returned something interesting
• Without history, you would have to remember exactly what you typed
• With the history, you find and repropose any stage of the exploration

Practical tip: Combine Query History (find past work) with Saved Queries (share validated work with the team) and Views (encapsulate reusable logic forever).

5. Demo Database Schema

main.sales
├── customers
│   ├── customer_id        INT          (PK)
│   ├── first_name         STRING
│   ├── last_name          STRING
│   ├── email              STRING       (peut être NULL, peut être dupliqué)
│   └── registration_date  DATE
│
├── products
│   ├── product_id         INT          (PK)
│   ├── product_name       STRING
│   ├── category           STRING       (peut être NULL)
│   └── price              DECIMAL
│
└── orders
    ├── order_id           INT          (PK)
    ├── customer_id        INT          (FK → customers)
    ├── order_date         DATE
    ├── product_id         INT          (FK → products)
    ├── quantity           INT
    ├── order_amount       DECIMAL
    └── status             STRING       ('completed', 'pending', 'processing', 'cancelled')

Relationships:

• orders.customer_id → customers.customer_id
• orders.product_id → products.product_id

Product categories: Electronics, Furniture, Stationery (+ NULL) Order statuses: completed (15), pending (5), processing (2), canceled (3)

6. Complete demo query reference

Module 1 — Fundamental queries

-- CLIP 1.1 — Première exploration de la table customers
SELECT * 
FROM main.sales.customers;

-- CLIP 1.1 — Sélection de colonnes précises
SELECT first_name, 
       last_name, 
       email 
FROM main.sales.customers;

-- CLIP 1.2 — Table products (démonstration du caching)
SELECT * 
FROM main.sales.products;

-- CLIP 1.2 — Requête légèrement différente (cache miss)
SELECT product_id, 
       product_name AS name, 
       price 
FROM main.sales.products;

-- CLIP 1.3 — Table orders complète
SELECT * 
FROM main.sales.orders;

-- CLIP 1.3 — Colonnes sélectionnées
SELECT order_id, 
       customer_id, 
       order_amount, 
       order_date 
FROM main.sales.orders;

-- CLIP 1.3 — Filtre sur l'année en cours
SELECT order_id, 
       customer_id, 
       order_amount, 
       order_date 
FROM main.sales.orders 
WHERE order_date >= '2026-01-01';

-- CLIP 1.3 — Double filtre : cette année ET montant élevé
SELECT order_id, 
       customer_id, 
       order_amount, 
       order_date 
FROM main.sales.orders 
WHERE order_date >= '2026-01-01' 
AND order_amount > 500;

-- CLIP 1.4 — Requête "New Customers 2026"
SELECT first_name, 
       last_name, 
       email, 
       registration_date 
FROM main.sales.customers 
WHERE registration_date >= '2026-01-01';

-- CLIP 1.4 — "High Value Orders" seuil 500
SELECT order_id, 
       customer_id, 
       order_amount, 
       order_date 
FROM main.sales.orders 
WHERE order_amount > 500;

-- CLIP 1.4 — "High Value Orders" seuil 750 (version mise à jour)
SELECT order_id, 
       customer_id, 
       order_amount, 
       order_date 
FROM main.sales.orders 
WHERE order_amount > 750;

Module 2 — Analysis queries

-- CLIP 2.1 — Opérateurs de filtre : égalité sur texte
SELECT first_name, 
       last_name, 
       email 
FROM main.sales.customers 
WHERE last_name = 'Smith';

-- CLIP 2.1 — Filtre sur date
SELECT first_name, 
       last_name, 
       email, 
       registration_date 
FROM main.sales.customers 
WHERE registration_date >= '2026-01-01';

-- CLIP 2.1 — BETWEEN : produits entre 100 et 500
SELECT product_name, 
       category, 
       price 
FROM main.sales.products 
WHERE price BETWEEN 100 AND 500;

-- CLIP 2.1 — IN : deux catégories
SELECT product_name, 
       category, 
       price 
FROM main.sales.products 
WHERE category IN ('Electronics','Furniture');

-- CLIP 2.1 — IS NULL : produits sans catégorie
SELECT product_name, 
       category, 
       price 
FROM main.sales.products 
WHERE category IS NULL;

-- CLIP 2.1 — LIKE : noms commençant par 'S'
SELECT product_name, 
       category, 
       price 
FROM main.sales.products 
WHERE product_name LIKE 'S%';

-- CLIP 2.1 — OR : haute valeur OU en attente
SELECT order_id, 
       customer_id, 
       order_amount, 
       status 
FROM main.sales.orders 
WHERE order_amount > 500 
OR status = 'pending';

-- CLIP 2.1 — AND : haute valeur ET en attente
SELECT order_id, 
       customer_id, 
       order_amount, 
       status 
FROM main.sales.orders 
WHERE order_amount > 500 
AND status = 'pending';

-- CLIP 2.1 — AND/OR avec parenthèses
SELECT order_id, 
       customer_id, 
       order_amount, 
       status 
FROM main.sales.orders 
WHERE order_amount > 500 
AND (status = 'pending' OR status = 'processing');

-- CLIP 2.2 — Aliases de colonnes
SELECT order_id AS `Order ID`, 
       customer_id AS `Customer ID`, 
       order_amount AS `Amount`, 
       order_date AS `Date` 
FROM main.sales.orders;

-- CLIP 2.2 — ORDER BY date décroissante
SELECT order_id AS `Order ID`, 
       customer_id AS `Customer ID`, 
       order_amount AS `Amount`, 
       order_date AS `Date` 
FROM main.sales.orders 
ORDER BY order_date DESC;

-- CLIP 2.2 — ORDER BY multi-colonnes
SELECT order_id AS `Order ID`, 
       status AS `Status`, 
       order_amount AS `Amount` 
FROM main.sales.orders 
ORDER BY status ASC, order_amount DESC;

-- CLIP 2.2 — Calcul de taxe
SELECT order_id AS `Order ID`, 
       order_amount AS `Subtotal`, 
       order_amount * 0.10 AS `Tax`, 
       order_amount * 1.10 AS `Total with Tax` 
FROM main.sales.orders 
ORDER BY order_amount DESC;

-- CLIP 2.2 — Expression chaînée avec parenthèses
SELECT order_id AS `Order ID`, 
       order_amount AS `Original Amount`, 
       order_amount - 50 AS `After Discount`, 
       (order_amount - 50) * 1.10 AS `After Discount with Tax` 
FROM main.sales.orders 
ORDER BY order_amount DESC;

-- CLIP 2.2 — Concaténation de chaînes
SELECT customer_id AS `Customer ID`, 
       first_name || ' ' || last_name AS `Full Name`, 
       email AS `Email` 
FROM main.sales.customers 
ORDER BY last_name ASC, first_name ASC;

-- CLIP 2.3 — COUNT(*)
SELECT COUNT(*) AS `Total Orders` 
FROM main.sales.orders;

-- CLIP 2.3 — Cinq agrégats
SELECT COUNT(*) AS `Total Orders`, 
       SUM(order_amount) AS `Total Revenue`, 
       AVG(order_amount) AS `Average Order`, 
       MIN(order_amount) AS `Smallest Order`, 
       MAX(order_amount) AS `Largest Order` 
FROM main.sales.orders;

-- CLIP 2.3 — Trois variantes de COUNT
SELECT COUNT(*) AS `Total Customers`, 
       COUNT(email) AS `With Email`, 
       COUNT(DISTINCT email) AS `Unique Emails` 
FROM main.sales.customers;

-- CLIP 2.3 — GROUP BY statut
SELECT status AS `Status`, 
       COUNT(*) AS `Order Count`, 
       SUM(order_amount) AS `Revenue` 
FROM main.sales.orders 
GROUP BY status;

-- CLIP 2.3 — GROUP BY client, trié par total décroissant
SELECT customer_id AS `Customer`, 
       COUNT(*) AS `Orders`, 
       SUM(order_amount) AS `Total Spent`, 
       AVG(order_amount) AS `Avg Order` 
FROM main.sales.orders 
GROUP BY customer_id 
ORDER BY SUM(order_amount) DESC;

-- CLIP 2.3 — HAVING : clients avec total > 1000
SELECT customer_id AS `Customer`, 
       COUNT(*) AS `Orders`, 
       SUM(order_amount) AS `Total Spent` 
FROM main.sales.orders 
GROUP BY customer_id 
HAVING SUM(order_amount) > 1000 
ORDER BY SUM(order_amount) DESC;

-- CLIP 2.3 — WHERE + GROUP BY + HAVING combinés
SELECT customer_id AS `Customer`, 
       COUNT(*) AS `Completed Orders`, 
       SUM(order_amount) AS `Completed Revenue` 
FROM main.sales.orders 
WHERE status = 'completed' 
GROUP BY customer_id 
HAVING SUM(order_amount) > 500 
ORDER BY SUM(order_amount) DESC;

-- CLIP 2.4 — INNER JOIN avec noms de tables complets
SELECT customers.first_name AS `First Name`, 
       customers.last_name AS `Last Name`, 
       orders.order_id AS `Order ID`, 
       orders.order_amount AS `Amount`, 
       orders.order_date AS `Date` 
FROM main.sales.customers 
INNER JOIN main.sales.orders ON customers.customer_id = orders.customer_id 
ORDER BY orders.order_date DESC;

-- CLIP 2.4 — INNER JOIN avec aliases de tables
SELECT c.first_name AS `First Name`, 
       c.last_name AS `Last Name`, 
       o.order_id AS `Order ID`, 
       o.order_amount AS `Amount`, 
       o.order_date AS `Date` 
FROM main.sales.customers c 
INNER JOIN main.sales.orders o ON c.customer_id = o.customer_id 
ORDER BY o.order_date DESC;

-- CLIP 2.4 — LEFT JOIN : garder tous les clients
SELECT c.first_name AS `First Name`, 
       c.last_name AS `Last Name`, 
       o.order_id AS `Order ID`, 
       o.order_amount AS `Amount` 
FROM main.sales.customers c 
LEFT JOIN main.sales.orders o ON c.customer_id = o.customer_id 
ORDER BY o.order_amount DESC;

-- CLIP 2.4 — Pattern anti-join : clients sans commandes
SELECT c.first_name AS `First Name`, 
       c.last_name AS `Last Name` 
FROM main.sales.customers c 
LEFT JOIN main.sales.orders o ON c.customer_id = o.customer_id 
WHERE o.order_id IS NULL;

-- CLIP 2.4 — FULL OUTER JOIN
SELECT c.first_name AS `First Name`, 
       c.last_name AS `Last Name`, 
       o.order_id AS `Order ID`, 
       o.order_amount AS `Amount` 
FROM main.sales.customers c 
FULL OUTER JOIN main.sales.orders o ON c.customer_id = o.customer_id 
ORDER BY c.last_name;

-- CLIP 2.4 — JOIN sur trois tables
SELECT c.first_name || ' ' || c.last_name AS `Customer`, 
       p.product_name AS `Product`, 
       o.quantity AS `Quantity`, 
       o.order_amount AS `Amount`, 
       o.order_date AS `Date` 
FROM main.sales.customers c 
INNER JOIN main.sales.orders o ON c.customer_id = o.customer_id 
INNER JOIN main.sales.products p ON o.product_id = p.product_id 
ORDER BY o.order_date DESC;

-- CLIP 2.5 — UPPER et LOWER
SELECT first_name, 
       UPPER(first_name) AS `Uppercase`, 
       LOWER(first_name) AS `Lowercase` 
FROM main.sales.customers;

-- CLIP 2.5 — CONCAT + SUBSTRING extraction avant @
SELECT CONCAT(first_name, ' ', last_name) AS `Full Name`, 
       substr(email, 1, instr(email,'@') - 1) AS `Email Username` 
FROM main.sales.customers 
WHERE email IS NOT NULL;

-- CLIP 2.5 — TRIM
SELECT TRIM(first_name) AS `Trimmed Name` 
FROM main.sales.customers;

-- CLIP 2.5 — ROUND, CEIL simulé, FLOOR simulé
SELECT order_amount AS `Raw Amount`, 
       ROUND(order_amount, 0) AS `Rounded`, 
       CAST((order_amount + 0.999999) AS INTEGER) AS `Rounded Up`, 
       CAST(order_amount AS INTEGER) AS `Rounded Down` 
FROM main.sales.orders 
ORDER BY order_amount DESC;

-- CLIP 2.5 — ABS : distance par rapport à 500
SELECT order_amount AS `Amount`, 
       order_amount - 500 AS `Difference from 500`, 
       ABS(order_amount - 500) AS `Distance from 500` 
FROM main.sales.orders 
ORDER BY ABS(order_amount - 500);

-- CLIP 2.5 — DATEDIFF
SELECT order_id AS `Order ID`, 
       order_date AS `Order Date`, 
       DATE('2026-05-07') AS `Today`, 
       DATEDIFF('2026-05-07', order_date) AS `Days Since Order` 
FROM main.sales.orders 
ORDER BY order_date DESC;

-- CLIP 2.5 — DATE_FORMAT
SELECT order_id AS `Order ID`, 
       order_date AS `Raw Date`, 
       DATE_FORMAT(order_date, 'MMMM d, yyyy') AS `Formatted Date`, 
       DATE_FORMAT(order_date, 'yyyy-MM') AS `Year-Month` 
FROM main.sales.orders 
ORDER BY order_date DESC;

Module 3 — Organization Queries

-- CLIP 3.1 — DESCRIBE : structure d'une table
DESCRIBE main.sales.customers;

-- CLIP 3.2 — Créer une vue temporaire
CREATE OR REPLACE TEMPORARY VIEW high_value_customers AS 
SELECT c.customer_id, 
       c.first_name, 
       c.last_name, 
       SUM(o.order_amount) AS total_spent 
FROM main.sales.customers c 
JOIN main.sales.orders o ON c.customer_id = o.customer_id 
GROUP BY c.customer_id, c.first_name, c.last_name 
HAVING SUM(o.order_amount) > 500;

-- CLIP 3.2 — Interroger la vue temporaire
SELECT * 
FROM high_value_customers 
ORDER BY total_spent DESC;

-- CLIP 3.2 — Réutilisation de la vue : COUNT
SELECT COUNT(*) AS high_value_count 
FROM high_value_customers;

-- CLIP 3.2 — Réutilisation de la vue : AVG
SELECT ROUND(AVG(total_spent), 2) AS avg_high_value_spend 
FROM high_value_customers;

-- CLIP 3.2 — Créer une vue permanente (seuil 500)
CREATE OR REPLACE VIEW main.sales.high_value_customers AS 
SELECT c.customer_id, 
       c.first_name, 
       c.last_name, 
       SUM(o.order_amount) AS total_spent 
FROM main.sales.customers c 
JOIN main.sales.orders o ON c.customer_id = o.customer_id 
GROUP BY c.customer_id, c.first_name, c.last_name 
HAVING SUM(o.order_amount) > 500;

-- CLIP 3.2 — Mettre à jour la vue permanente (seuil 1000)
CREATE OR REPLACE VIEW main.sales.high_value_customers AS 
SELECT c.customer_id, 
       c.first_name, 
       c.last_name, 
       SUM(o.order_amount) AS total_spent 
FROM main.sales.customers c 
JOIN main.sales.orders o ON c.customer_id = o.customer_id 
GROUP BY c.customer_id, c.first_name, c.last_name 
HAVING SUM(o.order_amount) > 1000;

-- CLIP 3.2 — Supprimer la vue permanente
DROP VIEW IF EXISTS main.sales.high_value_customers;

-- CLIP 3.2 — Supprimer la vue temporaire
DROP VIEW IF EXISTS high_value_customers;

-- CLIP 3.3 — Subquery imbriquée : clients au-dessus de la moyenne
SELECT c.first_name, 
       c.last_name, 
       SUM(o.order_amount) AS total_spent 
FROM main.sales.customers c 
JOIN main.sales.orders o ON c.customer_id = o.customer_id 
GROUP BY c.first_name, c.last_name 
HAVING SUM(o.order_amount) > (SELECT AVG(total) 
                              FROM (SELECT SUM(order_amount) AS total 
                                    FROM main.sales.orders 
                                    GROUP BY customer_id)
                              ) 
ORDER BY total_spent DESC;

-- CLIP 3.3 — CTE multi-étapes : customer lifetime value
WITH customer_totals AS (
    SELECT c.customer_id, 
           c.first_name, 
           c.last_name, 
           SUM(o.order_amount) AS total_spent, 
           COUNT(o.order_id) AS order_count 
    FROM main.sales.customers c 
    JOIN main.sales.orders o ON c.customer_id = o.customer_id 
    GROUP BY c.customer_id, c.first_name, c.last_name
), 
avg_spending AS (
    SELECT ROUND(AVG(total_spent), 2) AS avg_total 
    FROM customer_totals
) 
SELECT ct.first_name, 
       ct.last_name, 
       ct.total_spent, 
       ct.order_count, 
       a.avg_total, 
       ROUND(ct.total_spent - a.avg_total, 2) AS above_average_by 
FROM customer_totals ct 
CROSS JOIN avg_spending a 
WHERE ct.total_spent > a.avg_total 
ORDER BY ct.total_spent DESC;

7. Summary of key concepts

Architecture and performance

SQL Fundamentals

Filter operators

Aggregation functions

Built-in functions

JOIN Types

Query organization tools

Search Terms

query · explore · data · databricks · sql · azure · spark · engineering · analytics · functions · join · queries · history · performance · tables · views · analysis · editor · expressions · operators · organizing · types · aggregation · aliases