Skip to content

CREATE Tables with SQL statement in BigQuery

Let’s start with a simple example to get familiar with the syntax.

CREATE OR REPLACE TABLE `instruction-415216.examples.test` AS
(SELECT "apple" AS fruit, "potato" AS Vegetable
UNION ALL
SELECT "banana" AS fruit, "tomato" AS Vegetable)

A table called “test” has been created and the data looks like this:

RowfruitVegetable
1bananatomato
2applepotato

Create Tables

Step1: log in Google Cloud Console

Step2: Create a Dataset called “examples”

Step 3: Write the SQL statement to create three tables called Orders, Products and Clients

-- Create the Orders table
CREATE TABLE examples.orders (
    order_id INT64,
    product_id INT64,
    client_id INT64,
    order_date DATE,
    quantity INT64,
    price NUMERIC,
    -- Nullable columns
    discount NUMERIC,
);

-- Create the Products table
CREATE TABLE examples.products (
    product_id INT64,
    product_name STRING,
    category STRING,
    price NUMERIC,
    -- Nullable column
    description STRING
);

-- Create the Clients table
CREATE TABLE examples.clients (
    client_id INT64,
    client_name STRING,
    city STRING,
    country STRING
);

Step 4: Insert some records into each table.

-- Insert records into the Orders table
INSERT INTO examples.orders (order_id, product_id, client_id, order_date, quantity, price, discount)
VALUES
    (1, 101, 201, '2024-05-01', 3, 799.99, 0.00),
    (2, 102, 202, '2024-05-02', 1, 1299.99, 100.00),
    (3, 103, 203, '2024-05-03', 2, 149.99, 10.00),
    (4, 104, 204, '2024-05-04', 1, 299.99, NULL),
    (5, 105, 205, '2024-05-05', 4, 899.99, 50.00),
    (6, 106, 201, '2024-05-06', 1, 999.99, NULL),
    (7, 107, 202, '2024-05-07', 2, 79.99, 5.00),
    (8, 108, 203, '2024-05-08', 1, 199.99, NULL),
    (9, 109, 204, '2024-05-09', 3, 499.99, 20.00),
    (10, 110, 205, '2024-05-10', 2, 499.99, 0.00),
    (11, 101, 201, '2024-05-11', 1, 799.99, NULL),
    (12, 102, 202, '2024-05-12', 1, 1299.99, NULL),
    (13, 103, 203, '2024-05-13', 2, 149.99, NULL),
    (14, 104, 204, '2024-05-14', 1, 299.99, NULL),
    (15, 105, 205, '2024-05-15', 3, 899.99, 30.00);

    

-- Insert records into the Products table
INSERT INTO examples.products (product_id, product_name, category, price, description)
VALUES
    (101, 'Smartphone X', 'Electronics', 799.99, '5.8" Super Retina XDR display, A14 Bionic chip, Ceramic Shield'),
    (102, 'Laptop Y', 'Computers', 1299.99, '15.6" Full HD display, Intel Core i7 processor, 16GB RAM, 512GB SSD'),
    (103, 'Headphones Z', 'Electronics', 149.99, 'Noise-canceling, Bluetooth connectivity, 30 hours battery life'),
    (104, 'Smartwatch W', 'Wearable Tech', 299.99, 'Water-resistant, Heart rate monitor, GPS tracking'),
    (105, 'Camera V', 'Photography', 899.99, '24.2MP CMOS sensor, 4K video recording, 3.2" LCD touchscreen'),
    (106, 'Drone U', 'Electronics', 999.99, '4K camera, 30 minutes flight time, GPS positioning'),
    (107, 'Fitness Tracker T', 'Fitness', 79.99, 'Heart rate monitor, Step counter, Sleep tracking'),
    (108, 'Wireless Speaker S', 'Audio', 199.99, 'Bluetooth connectivity, 360-degree sound, IPX7 waterproof'),
    (109, 'Gaming Console R', 'Gaming', 499.99, '4K gaming, High dynamic range, SSD storage'),
    (110, 'Tablet Q', 'Electronics', 499.99, '10.2" Retina display, A12 Bionic chip, Apple Pencil support');


-- Insert records into the Clients table
INSERT INTO examples.clients (client_id, client_name, city, country)
VALUES
    (201, 'Tom', 'New  York', 'US'),
    (202, 'Jane', NULL, 'Canada'),
    (203, 'Maria', 'Ottawa', 'Canada'),
    (204, 'Luca', 'Berlin', 'Germany'),
    (205, 'Juan', 'Austin', 'US')
Tags: