Skip to main content

Basic Query Examples

Learn by example! Here are common BigQuery queries for the MCP server.

Product Queries​

Find Product by SKU​

-- Search by Artikelnummer or External Article Number
SELECT 
    Artikelnummer,
    `Externe Artikelnummer`,
    Name,
    Brand,
    Status
FROM `your-project.products.Produkt`
WHERE Artikelnummer = '142300' 
   OR `Externe Artikelnummer` = '142300'
tip

Always check both Artikelnummer and Externe Artikelnummer fields when searching by SKU, as products may use either.

List Active Products​

SELECT 
    Name,
    Brand,
    Price,
    Status
FROM `your-project.products.Produkt`
WHERE Status = 'Aktiv'
ORDER BY Name
LIMIT 100

Search by Brand​

SELECT 
    Artikelnummer,
    Name,
    Price
FROM `your-project.products.Produkt`
WHERE Brand = 'Nike'
  AND Status = 'Aktiv'
ORDER BY Price DESC

Aggregation Queries​

Count Products by Status​

SELECT 
    Status,
    COUNT(*) as ProductCount
FROM `your-project.products.Produkt`
GROUP BY Status
ORDER BY ProductCount DESC

Average Price by Brand​

SELECT 
    Brand,
    COUNT(*) as ProductCount,
    AVG(Price) as AvgPrice,
    MIN(Price) as MinPrice,
    MAX(Price) as MaxPrice
FROM `your-project.products.Produkt`
WHERE Price IS NOT NULL
GROUP BY Brand
HAVING COUNT(*) >= 5
ORDER BY AvgPrice DESC

Top 10 Most Expensive Products​

SELECT 
    Name,
    Brand,
    Price,
    Artikelnummer
FROM `your-project.products.Produkt`
WHERE Price IS NOT NULL
ORDER BY Price DESC
LIMIT 10

Filtering Queries​

Products in Price Range​

SELECT 
    Name,
    Brand,
    Price
FROM `your-project.products.Produkt`
WHERE Price BETWEEN 50 AND 150
  AND Status = 'Aktiv'
ORDER BY Price

Search by Product Name​

-- Case-insensitive search
SELECT 
    Artikelnummer,
    Name,
    Brand
FROM `your-project.products.Produkt`
WHERE LOWER(Name) LIKE '%shoe%'
LIMIT 50

Multiple Conditions​

SELECT 
    Name,
    Brand,
    Price,
    Status
FROM `your-project.products.Produkt`
WHERE Brand IN ('Nike', 'Adidas', 'Puma')
  AND Status = 'Aktiv'
  AND Price < 100
ORDER BY Brand, Price

GTIN Queries​

Find by GTIN​

SELECT 
    Artikelnummer,
    Name,
    GTIN,
    `GTIN VPE`,
    `GTIN Karton`,
    `GTIN Palette`
FROM `your-project.products.Produkt`
WHERE GTIN = '4250847198292'
   OR `GTIN VPE` = '4250847198292'
   OR `GTIN Karton` = '4250847198292'
   OR `GTIN Palette` = '4250847198292'

Products with Missing GTIN​

SELECT 
    Artikelnummer,
    Name,
    Brand
FROM `your-project.products.Produkt`
WHERE GTIN IS NULL
  AND Status = 'Aktiv'
LIMIT 20

Date-Based Queries​

Recently Updated Products​

SELECT 
    Name,
    Brand,
    UpdatedAt
FROM `your-project.products.Produkt`
WHERE UpdatedAt >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
ORDER BY UpdatedAt DESC

Products Created This Month​

SELECT 
    Name,
    CreatedAt,
    Status
FROM `your-project.products.Produkt`
WHERE CreatedAt >= DATE_TRUNC(CURRENT_DATE(), MONTH)
ORDER BY CreatedAt DESC

Interactive Examples​

Try It Yourself!​

Here's a template you can modify:

SELECT 
    -- Add columns you want to see
    Name,
    Brand,
    Price
FROM `your-project.products.Produkt`
WHERE 
    -- Add your conditions
    Status = 'Aktiv'
    -- AND Brand = 'YourBrand'
    -- AND Price > 0
ORDER BY 
    -- Choose sort order
    Price DESC
LIMIT 10  -- Adjust limit as needed
Quick Tips
  • Use -- for comments in SQL
  • Press Ctrl+Space for autocomplete (in some tools)
  • Always use LIMIT when testing queries

Common Patterns​

NULL Handling​

-- Find products without prices
SELECT Name, Brand
FROM `your-project.products.Produkt`
WHERE Price IS NULL

-- Exclude NULL values
SELECT AVG(Price) as AvgPrice
FROM `your-project.products.Produkt`
WHERE Price IS NOT NULL

Case Statements​

SELECT 
    Name,
    Price,
    CASE 
        WHEN Price < 50 THEN 'Budget'
        WHEN Price < 150 THEN 'Mid-range'
        ELSE 'Premium'
    END as PriceCategory
FROM `your-project.products.Produkt`
WHERE Price IS NOT NULL

String Operations​

-- Concatenate fields
SELECT 
    CONCAT(Brand, ' - ', Name) as FullName,
    Artikelnummer
FROM `your-project.products.Produkt`

-- Extract substring
SELECT 
    Name,
    SUBSTR(Artikelnummer, 1, 3) as CategoryCode
FROM `your-project.products.Produkt`

Performance Tips​

1. Always Use Limits When Testing​

-- Good: Limited results for testing
SELECT * FROM Produkt LIMIT 10

-- Avoid: May return thousands of rows
SELECT * FROM Produkt

2. Filter Early​

-- Efficient: Filter before join
SELECT p.Name, i.Quantity
FROM Produkt p
JOIN Inventory i ON p.Artikelnummer = i.SKU
WHERE p.Status = 'Aktiv'

-- Less efficient: Filter after join
SELECT p.Name, i.Quantity
FROM Produkt p
JOIN Inventory i ON p.Artikelnummer = i.SKU
WHERE p.Status = 'Aktiv'

3. Use Specific Columns​

-- Good: Only needed columns
SELECT Name, Price FROM Produkt

-- Avoid: All columns when not needed
SELECT * FROM Produkt

Next Steps​

Ready for more complex queries? Check out: