1
2
3
4
5
6
7
8
9
10
# Install gcloud CLI
curl https://sdk.cloud.google.com | bash

# Initialize and authenticate
gcloud init
gcloud auth login

# Set active project
gcloud config set project YOUR_PROJECT_ID

1
2
3
4
5
6
7
-- Standard SQL
CREATE SCHEMA `project.dataset`
OPTIONS (
  description = 'My analytics dataset',
  labels = [('environment', 'production')]
);

1
2
3
4
5
6
7
8
9
10
11
12
13
-- Create table with schema
CREATE TABLE `project.dataset.table` (
  id INT64,
  name STRING,
  created_at TIMESTAMP
);

-- Create table from query
CREATE TABLE `project.dataset.derived_table` AS
SELECT column1, column2
FROM `source_table`
WHERE condition;

1
2
3
4
5
6
7
8
9
10
-- Creating a STRUCT
SELECT STRUCT(
  'John' AS first_name, 
  'Doe' AS last_name
) AS person;

-- Accessing STRUCT fields
SELECT person.first_name
FROM table;

1
2
3
4
5
6
7
-- Creating an ARRAY
SELECT ['apple', 'banana', 'cherry'] AS fruits;

-- Unnesting ARRAY
SELECT fruit
FROM table, UNNEST(fruits) AS fruit;

1
2
3
4
5
6
7
-- Creating a geographic point
SELECT GEOGRAPHY('POINT(-122.4194 37.7749)') AS san_francisco;

-- Calculating distance
SELECT ST_DISTANCE(point1, point2) AS distance
FROM geo_table;

1
2
3
4
5
6
7
8
9
10
SELECT 
  column1, 
  column2
FROM `project.dataset.table`
WHERE 
  column1 IS NOT NULL
  AND column2 BETWEEN 10 AND 100
ORDER BY column1 DESC
LIMIT 1000;

1
2
3
4
5
INSERT INTO `project.dataset.table` 
  (id, name, created_at)
VALUES 
  (1, 'Test', CURRENT_TIMESTAMP());

1
2
3
4
5
6
UPDATE `project.dataset.table`
SET 
  status = 'active',
  updated_at = CURRENT_TIMESTAMP()
WHERE id = 123;

1
2
3
4
DELETE FROM `project.dataset.table`
WHERE status = 'inactive'
  AND created_at < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY);

1
2
3
4
5
6
7
8
-- Ranking within partitions
SELECT 
  department,
  employee_name,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees;

1
2
3
4
5
6
7
-- Advanced statistical analysis
SELECT 
  AVG(value) AS mean,
  STDDEV(value) AS standard_deviation,
  APPROX_QUANTILES(value, 4) AS quartiles
FROM metrics;

1
2
3
4
5
6
7
-- Transform rows to array
SELECT 
  user_id, 
  ARRAY_AGG(DISTINCT product) AS purchased_products
FROM purchases
GROUP BY user_id;

1
2
3
4
5
6
7
8
9
10
-- Complex struct operations
SELECT 
  ARRAY(
    SELECT AS STRUCT 
      name, 
      age, 
      ARRAY(SELECT score FROM UNNEST(scores)) AS test_scores
    FROM users
  ) AS user_details;

1
2
3
4
5
6
7
-- Date arithmetic and parsing
SELECT 
  DATE_ADD(current_date, INTERVAL 30 DAY) AS future_date,
  EXTRACT(YEAR FROM timestamp) AS year,
  FORMAT_DATE('%Y-%m', current_date) AS month_year
FROM table;

1
2
3
4
5
6
7
8
-- Find points within a region
SELECT 
  location, 
  ST_WITHIN(location, region) AS is_inside
FROM geo_data
WHERE ST_WITHIN(location, 
  ST_GEOGFROMTEXT('POLYGON((...))'));

1
2
3
4
5
6
7
CREATE OR REPLACE MODEL `project.dataset.model`
OPTIONS(
  model_type='linear_reg',
  input_label_cols=['target']
) AS
SELECT * FROM training_data;

1
2
3
4
5
6
SELECT * 
FROM ML.PREDICT(
  MODEL `project.dataset.model`, 
  (SELECT * FROM prediction_data)
);

1
2
3
4
5
-- Create partitioned table
CREATE TABLE `project.dataset.partitioned_table`
PARTITION BY DATE(timestamp)
AS SELECT * FROM source_table;

1
2
3
4
5
-- Create clustered table
CREATE TABLE `project.dataset.clustered_table`
CLUSTER BY user_id, country
AS SELECT * FROM source_table;

1
2
3
4
5
6
CREATE EXTERNAL TABLE `project.dataset.external_table`
OPTIONS (
  format = 'CSV',
  uris = ['gs://bucket/path/*.csv']
);