Oxla

https://docs.oxla.com
Admin
Oxla is a database system providing SQL support, client tools, and security features, designed for...
Tokens:162,384
Snippets:1,719
Trust Score:7.8
Update:10 months ago
Show doc for...
Tokens:
Raw
### SQL: Create Sample Tasks Table

Source: https://docs.oxla.com/sql-reference/sql-mutations/update

SQL statements to create a sample 'tasks' table and populate it with initial data for demonstrating UPDATE operations. This setup is crucial for running the subsequent examples.

```sql
CREATE TABLE tasks (
    task_id INT,
    task_name TEXT,
    status TEXT
);

INSERT INTO tasks (task_id, task_name, status)
VALUES
    (1001, 'Task A', 'pending'),
    (1002, 'Task B', 'in-progress'),
    (1003, 'Task C', 'pending');
```

--------------------------------

### CREATE TABLE lineorder example in SQL

Source: https://docs.oxla.com/sql-reference/sql-statements/create-index

Demonstrates creating a sample lineorder table with multiple columns including customer_id, part_key_id, quantity, unit_price, and commit_date. This table is used in subsequent examples to illustrate index usage and performance impact.

```sql
CREATE TABLE lineorder (
    customer_id              INTEGER NOT NULL,
    part_key_id              INTEGER NOT NULL,
    quantity                 INTEGER NOT NULL,
    unit_price               FLOAT NOT NULL,
    commit_date              DATE NOT NULL
);
```

--------------------------------

### Window Specification Chaining Example

Source: https://docs.oxla.com/sql-reference/sql-functions/window-functions/overview

Demonstrates window specification chaining in Oxla using the WINDOW clause to define named window specifications and the OVER clause to apply them. This example creates two related window specifications where w2 references w1, showcasing partition and frame ordering capabilities.

```sql
SELECT SUM(i0) OVER w2 FROM tb1 WINDOW w1 AS (PARTITION BY i1), w2 AS (w1 ROWS CURRENT ROW)
```

--------------------------------

### DESCRIBE TABLE Example - Part Table

Source: https://docs.oxla.com/sql-reference/sql-statements/describe

Practical example demonstrating the DESCRIBE TABLE statement on the 'part' table. Shows how to retrieve column metadata including p_partkey (INT), p_name (TEXT), p_mfgr (TEXT), and other columns with their types and nullable flags.

```sql
DESCRIBE TABLE part;
```

--------------------------------

### COPY TO with AWS S3 Bucket Example

Source: https://docs.oxla.com/sql-reference/sql-statements/copy-to/copy-to-with-endpoint

Example of exporting data from the students table to an AWS S3 bucket using COPY TO statement with AWS credentials and S3 endpoint configuration.

```sql
COPY students TO 's3://oxla-testdata/cayo/students_file' (AWS_CRED(AWS_REGION 'region1', AWS_KEY_ID 'key_id', AWS_PRIVATE_KEY 'access_key', ENDPOINT 's3.us-east-2.amazonaws.com'));
```

--------------------------------

### SQL: Example Output from pg_class Query

Source: https://docs.oxla.com/system-catalogs/catalogs/pg_class

Illustrates the expected output when querying the 'pg_class' catalog for information about a table and an index. It shows object IDs, names, kinds, and other relevant metadata.

```sql
oid  |     relname     | relkind | relhasindex | relnamespace 
------+-----------------+---------+-------------+--------------
 1013 | idx_customer_id | i       | f           |            0
 1012 | customer_orders | r       | t           |            0
(2 rows)
```

--------------------------------

### Install PostgreSQL via Homebrew on Mac

Source: https://docs.oxla.com/troubleshooting-optimization/error-handling

Install PostgreSQL on macOS using the Homebrew package manager. This resolves the 'psql.exe: command not found' error by ensuring PostgreSQL is properly installed on the system.

```bash
brew install postgresql
```

--------------------------------

### SQL Example: Create, Insert, View, and Drop Table

Source: https://docs.oxla.com/sql-reference/sql-statements/drop

This example demonstrates the full lifecycle of a table: creation, inserting data, viewing the data with a SELECT statement, and finally, dropping the table using the DROP TABLE statement. This illustrates how data is removed along with the table structure.

```sql
CREATE TABLE warehouse (
  id int,
  product text,
  qty int
);
INSERT INTO warehouse 
    (id, product, qty) 
VALUES 
    (889771,'Shirt',22),
    (777821,'Hat',99),
    (103829,'Bed Cover',12);
```

```sql
SELECT * FROM warehouse;
```

```sql
DROP TABLE warehouse;
```

--------------------------------

### Initiate Transactions in SQL

Source: https://docs.oxla.com/sql-reference/transactions

Commands to start a new transaction. Supports 'BEGIN;' and 'BEGIN TRANSACTION;' syntax. These initiate a transaction block, though execution is immediate.

```sql
BEGIN;

```

```sql
BEGIN TRANSACTION;

```

--------------------------------

### Oxla Ownership Change Examples

Source: https://docs.oxla.com/security/ownership

These SQL examples demonstrate scenarios of changing table ownership and the effect of GRANT/REVOKE operations in conjunction with ownership changes in Oxla. They illustrate how ownership grants are independent of explicit role privileges.

```sql
ALTER TABLE table1 OWNER TO user1;
GRANT SELECT ON table1 TO user1;
ALTER TABLE table1 OWNER TO oxla;
```

```sql
ALTER TABLE table1 OWNER TO user1;
REVOKE SELECT ON table1 FROM user1;
```

```sql
ALTER TABLE table1 OWNER TO user1;
GRANT SELECT ON table1 TO user1;
REVOKE SELECT ON table1 FROM user1;
ALTER TABLE table1 OWNER TO oxla;
```

--------------------------------

### Create and Populate Customer Table

Source: https://docs.oxla.com/sql-reference/sql-functions/aggregate-functions/sum

Creates a customer table with customer_id and cust_name columns, then inserts seven sample customer records. This table is used as a reference for rental transactions in subsequent examples.

```sql
CREATE TABLE customer (
  customer_id int,
  cust_name text
);
INSERT INTO customer
    (customer_id, cust_name)
VALUES
    (11112, 'Alex'),
    (11113, 'Aaron'),
    (11114, 'Alice'),
    (11115, 'Nina'),
    (11116, 'Rosy'),
    (11117, 'Martha'),
    (11118, 'John');
```

--------------------------------

### Index Definition Output Example - PostgreSQL

Source: https://docs.oxla.com/sql-reference/sql-functions/other-functions/pg-get-indexdef

Example output showing the reconstructed CREATE INDEX command returned by pg_get_indexdef(). Displays the full index definition including schema-qualified table name and indexed columns.

```sql
CREATE INDEX sample_index ON public.sample_table(col)
```

--------------------------------

### Importing Data Manually

Source: https://docs.oxla.com/sql-reference/sql-statements/copy-from/copy-from-stdin

Guide on how to manually import data into a table using the COPY FROM STDIN command, including table creation and data entry.

```APIDOC
## Importing Data Manually

### Description
This example demonstrates how to manually enter data directly into a table using the `COPY FROM STDIN` command.

### Steps

1.  **Create Table (if it doesn't exist):**
    ```sql
    CREATE TABLE film (
        title text NOT NULL,
        length int,
        rating text
        );
    ```

2.  **Initiate Import:**
    ```sql
    COPY film FROM stdin;
    ```

3.  **Enter Data:**
    When prompted (`Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself, or an EOF signal. >>`), paste your CSV formatted data.

    **Example Data:**
    ```csv
    ATTRACTION NEWTON,83,PG-13
    CHRISTMAS MOONSHINE,150,NC-17
    DANGEROUS UPTOWN,121,PG
    KILL BROTHERHOOD,54,G
    ```

4.  **End Input:**
    On Unix-like systems, press `Ctrl + D`.

5.  **Verify Data:**
    ```sql
    SELECT * FROM film;
    ```

### Response

#### Success Response (Import Complete)
Output indicates the number of rows imported, e.g., `IMPORT 0`.

#### Example Query Result
```
    title          | length | rating 
--------------------+--------+--------
ATTRACTION NEWTON   |     83 | PG-13
CHRISTMAS MOONSHINE |    150 | NC-17
DANGEROUS UPTOWN    |    121 | PG
KILL BROTHERHOOD    |     54 | G
(4 rows)
```
```

--------------------------------

### SHOW NODES Query Result Example

Source: https://docs.oxla.com/sql-reference/sql-statements/show-nodes

Example output from the SHOW NODES query displaying cluster information with three nodes. Each row represents a node's state with columns for name, election_state, followers_count, connected_nodes_count, and degradation_error.

```sql
     name      | election_state  | followers_count | connected_nodes_count | degradation_error 
---------------+-----------------+-----------------+-----------------------+-------------------
 n_oxla_node_1 | LEADER_FOLLOWER |               0 |                     3 | 
 n_oxla_node_3 | LEADER          |               3 |                     3 | 
 n_oxla_node_2 | LEADER_FOLLOWER |               0 |                     3 | 
(3 rows)
```

--------------------------------

### Importing Data from ORC Format

Source: https://docs.oxla.com/sql-reference/sql-statements/copy-from/copy-from-with-format

Instructions and examples for importing data from ORC files, emphasizing the requirement for object storage.

```APIDOC
## Importing Data from ORC Format

### Description
This endpoint allows importing data from ORC (Optimized Row Columnar) files. This format is supported only when the ORC file is located in an object storage solution like an S3 bucket.

### Method
SQL Statement

### Endpoint
N/A

### Parameters
#### SQL Statement Parameters
- **tablename** (string) - Required - The destination table name.
- **file_path** (string) - Required - The S3 path to the ORC file (e.g., 's3://oxla-testdata/cab_types.orc').
- **format** (string) - Required - Set to 'orc'.
- **HEADER** (boolean) - Optional - Include this option if your ORC file contains column headers.

### Request Example
```sql
-- Example with HEADER option
copy cab_types from 's3://oxla-testdata/cab_types.orc' (format orc, HEADER);

-- Example without HEADER option
copy cab_types from 's3://oxla-testdata/cab_types.orc' (format orc);
```

### Response
#### Success Response
Indicates successful data import from the ORC file.

#### Response Example
N/A
```

--------------------------------

### Query Customer Table

Source: https://docs.oxla.com/sql-reference/sql-clauses/from/right-join

Retrieves all records from the customer table to display the complete dataset of customers with their IDs and names.

```sql
SELECT * FROM customer;
```

--------------------------------

### Create and Populate Student Table - SQL

Source: https://docs.oxla.com/sql-reference/sql-clauses/having

Creates a student table with student IDs and names, then inserts sample student records. Used as a reference table for joining with score data in HAVING clause examples.

```sql
CREATE TABLE student (
  stud_id int,
  stud_name text
);
INSERT INTO student 
    (stud_id, stud_name) 
VALUES 
    (992831192, 'Mary'),
    (992811191, 'Bobby'),
    (992311195, 'Sean'),
    (998311193, 'Harry'),
    (998311194, 'William'),
    (928311197, 'Kate'),
    (928311190, 'Tom'),
    (928311199, 'Sully'),
    (998311196, 'Susan');
```

--------------------------------

### Create Time Example Table in SQL

Source: https://docs.oxla.com/sql-reference/sql-functions/timestamp-functions/unix-macros

Creates a table named time_example with a timestamp column and inserts sample timestamp values for demonstrating UNIX_MICROS() function operations on multiple rows.

```sql
CREATE TABLE time_example (
  time_stamp timestamp
);

INSERT INTO time_example VALUES 
('2022-12-25 13:30:00'),
('2021-10-02 06:30:00'),
('2020-09-25 07:25:00');
```

--------------------------------

### Importing Data from Parquet Format

Source: https://docs.oxla.com/sql-reference/sql-statements/copy-from/copy-from-with-format

Instructions and examples for importing data from Parquet files, highlighting memory considerations and object storage requirements.

```APIDOC
## Importing Data from Parquet Format

### Description
This endpoint facilitates importing data from Apache Parquet files. Similar to ORC, Parquet file import is supported only when the file resides in an object storage service, such as an S3 bucket. Be aware that importing large Parquet files can be memory-intensive.

### Method
SQL Statement

### Endpoint
N/A

### Parameters
#### SQL Statement Parameters
- **tablename** (string) - Required - The destination table name.
- **file_path** (string) - Required - The S3 path to the Parquet file (e.g., 's3://oxla-testdata/cab_types.parquet').
- **format** (string) - Required - Set to 'parquet'.

### Request Example
```sql
copy cab_types from 's3://oxla-testdata/cab_types.parquet' (format parquet);
```

### Response
#### Success Response
Indicates successful data import from the Parquet file.

#### Response Example
N/A
```

--------------------------------

### SQL Example: Dropping a Table Safely with IF EXISTS

Source: https://docs.oxla.com/sql-reference/sql-statements/drop

This example shows how to use the `IF EXISTS` clause with the DROP TABLE statement. It first demonstrates the error that occurs when trying to drop a non-existent table without `IF EXISTS`, and then shows how `IF EXISTS` allows the command to complete without error, even if the table is already gone.

```sql
DROP TABLE warehouse;
```

```sql
DROP TABLE IF EXISTS warehouse;
```

--------------------------------

### Query Orders Table

Source: https://docs.oxla.com/sql-reference/sql-clauses/from/right-join

Retrieves all records from the orders table to display the complete dataset of orders with their IDs, dates, amounts, and associated customer IDs.

```sql
SELECT * FROM orders;
```

--------------------------------

### Create and Populate petsData Table

Source: https://docs.oxla.com/sql-reference/sql-functions/string-functions/starts-with

Creates a sample petsData table with pet information including ID, name, species, breed, sex, and age. This table is used as the basis for demonstrating STARTS_WITH() function examples.

```SQL
CREATE TABLE petsData (
  petid int,
  petname text,
  species text,
  breed text,
  sex text,
  age int
);
INSERT INTO petsData 
    (petid, petname, species, breed, sex, age) 
VALUES 
    (2021001,'Bartholomeow','cat','persian','m',2),
    (2021004,'Jack','dog','boston terrier','m',1),
    (2022001,'Jesse','hamster','dzungarian','m',1),
    (2022010,'Bella','dog','dobberman','f',3),
    (2022011,'June','cat','american shorthair','f',2);
```

--------------------------------

### SUBSTRING Function Extract Characters Example

Source: https://docs.oxla.com/sql-reference/sql-functions/string-functions/substring

Demonstrates extracting the first 7 characters from a string using the three-argument syntax. This example shows how to use SUBSTRING() with a literal string and specific start position and length values to return a partial string.

```sql
SELECT SUBSTRING('OxlaDocumentation', 1, 7);
```

--------------------------------

### Create example table for pg_statio_user_tables

Source: https://docs.oxla.com/system-catalogs/catalogs/pg_statio_user_tables

Creates a new user table named example_table with three columns (data, cluster, storage) that will be tracked in pg_statio_user_tables. This table demonstrates how user-created tables appear in the system statistics view.

```sql
CREATE TABLE example_table (
    data text,
    cluster text,
    storage int
);
```

--------------------------------

### SQL SUBSTR Function Example for Text Extraction

Source: https://docs.oxla.com/sql-reference/sql-data-types/text

This SQL example shows how to use the SUBSTR function to extract a portion of a text string. The function takes the text, a starting position (1-based index), and the number of characters to extract as arguments. The output is aliased as 'Fruit'.

```sql
SELECT substr('Watermelon',6,5) AS "Fruit";
```

--------------------------------

### CREATE TABLE Simple Example

Source: https://docs.oxla.com/sql-reference/sql-statements/create-table

Creates a sample employees table with four columns of different data types. Demonstrates basic table creation with an INT primary identifier, TEXT fields for names and address, and a NOT NULL constraint on the firstName column.

```sql
CREATE TABLE employees (
    employeeID INT,
    lastName TEXT,
    firstName TEXT NOT NULL,
    address TEXT
);
```

--------------------------------

### COUNT(expression) Window Function for Non-NULL Values

Source: https://docs.oxla.com/sql-reference/sql-functions/window-functions/count

Demonstrates the COUNT(expression) variant which counts only non-NULL values in a specific column. In this example, it counts non-NULL qty_shipped values using a running window from the start of the data.

```sql
SELECT salesid, qty, qty_shipped,
  COUNT(qty_shipped) OVER (ORDER BY salesid ROWS UNBOUNDED PRECEDING) AS count
FROM winsales
ORDER BY salesid;
```

--------------------------------

### SQL: Create and Populate Movies Table

Source: https://docs.oxla.com/sql-reference/sql-clauses/from/join

This snippet demonstrates how to create a 'movies' table with columns for movie ID, name, and category ID, and then inserts sample data into it. It assumes a SQL environment where these commands are supported.

```sql
CREATE TABLE movies (
  movie_id int,
  movie_name text,
  category_id int
);
INSERT INTO movies
    (movie_id, movie_name, category_id)
VALUES
    (201011, 'The Avengers', 181893),
    (200914, 'Avatar', 181894),
    (201029, 'Shutter Island', 181891),
    (201925, 'Tune in Your Love', 181892);
```

--------------------------------

### Create Employee Schedule Table with TIME Data Type (SQL)

Source: https://docs.oxla.com/sql-reference/sql-data-types/time-type/time

Example of creating an 'employee_schedule' table using SQL, demonstrating the use of the TIME data type for the 'start_time' column. This table stores employee names and their scheduled work start times.

```sql
CREATE TABLE employee_schedule (
    employee_name TEXT,
    start_time TIME
);

INSERT INTO employee_schedule (employee_name, start_time)
VALUES
('John Doe', '08:30:00'),
('Jane Smith', '09:00:00'),
('Michael Johnson', '10:15:00');
```

--------------------------------

### SQL WHERE Clause with LIKE Operator

Source: https://docs.oxla.com/sql-reference/sql-clauses/where

Demonstrates using the LIKE operator in a WHERE clause to retrieve records based on pattern matching. This example finds all employees whose first name starts with 'Br' using the wildcard pattern 'Br%'. The pattern '%string' can be used to match names ending with a specific string.

```sql
SELECT * FROM salary
WHERE empname LIKE 'Br%';
```

--------------------------------

### CREATE TABLE with INSERT Data Example

Source: https://docs.oxla.com/sql-reference/sql-statements/create-table

Creates a products table and inserts sample data in a single operation. Demonstrates table creation followed by INSERT statement to populate three product records with IDs, names, and descriptions.

```sql
CREATE TABLE products (
  product_id INT,         
  product_name TEXT NOT NULL, 
  product_description TEXT
);
INSERT INTO products (product_id, product_name, product_description) 
VALUES 
    (101, 'Laptop', 'A high-performance laptop for professionals.'),
    (102, 'Smartphone', 'A latest model smartphone with excellent features.'),
    (103, 'Headphones', 'Noise-cancelling headphones for immersive audio experience.');
```

--------------------------------

### Create and Populate Test Table - SQL

Source: https://docs.oxla.com/sql-reference/sql-clauses/limit

Creates a sample comporders table with order information including order_id, customer name, product details, price, and status. Demonstrates table creation and data insertion for testing LIMIT clause functionality.

```sql
CREATE TABLE comporders
(
    order_id int,
    cust_name text,
    prod_name text,
    prod_price float,
    status text
);

INSERT INTO comporders
VALUES
(1002, 'Mike', 'Lenovo IdeaPad Flex 5', 600, 'PAID'),
(1003, 'Sean', 'Acer Aspire 3', 450, 'PAID'),
(1004, 'Victor', 'Microsoft Surface Laptop Go 2', 500, 'PENDING'),
(1005, 'Lewis', 'Lenovo Duet 5i', 700, 'PAID'),
(1006, 'David', 'Acer Swift 3', 640, 'PAID'),
(1007, 'Meghan', 'Lenovo IdeaPad Duet 5 Chromebook', 750, 'PAID'),
(1008, 'Harry', 'Apple iPad Air', 449, 'PENDING'),
(1009, 'Steve', 'Microsoft Surface Go 3', 680, 'PENDING'),
(1010, 'Omar', 'HP Victus 16', 800, 'PAID');
```

--------------------------------

### Upload File to S3 Using AWS CLI

Source: https://docs.oxla.com/sql-reference/sql-statements/copy-from/copy-from

AWS CLI command to upload local files to S3 server storage. Transfers files from local computer to server location, making them accessible for database import operations. Requires AWS CLI installed and configured.

```typescript
aws s3 cp ~/[file location on your local computer] s3://[server location]/[file name]
```

```typescript
aws s3 cp ~/Documents/feature2.csv s3://oxla-testdata/test/feature2.csv
```

--------------------------------

### Create and Populate Client Table

Source: https://docs.oxla.com/sql-reference/sql-clauses/from/from

Creates a sample client table with three columns (client_id, client_name, client_origin) and inserts three example records. This table is used as a foundation for demonstrating FROM clause functionality.

```sql
CREATE TABLE client (
  client_id int,
  client_name text,
  client_origin text
);
INSERT INTO client 
    (client_id, client_name, client_origin) 
VALUES 
    (181891,'Oxla','Poland'),
    (181892,'Google','USA'),
    (181893,'Samsung','South Korea');
```

--------------------------------

### STARTS_WITH Function Syntax and Parameters

Source: https://docs.oxla.com/sql-reference/sql-functions/string-functions/starts-with

Defines the basic syntax and parameters for the STARTS_WITH() function. The function takes two arguments: the search reference (first_argument) and search keywords (second_argument), both of STRING type, and returns a BOOL value.

```SQL
STARTS_WITH(first_argument, 'second_argument')
```

--------------------------------

### Basic STRPOS Query Example

Source: https://docs.oxla.com/sql-reference/sql-functions/string-functions/strpos

Demonstrates finding the position of a substring within a literal string. In this example, 'ut' is found at position 5 within 'computer'.

```sql
SELECT STRPOS('computer', 'ut') AS "Position of ut";
```

--------------------------------

### SQL: Create and Populate Categories Table

Source: https://docs.oxla.com/sql-reference/sql-clauses/from/join

This snippet demonstrates how to create a 'categories' table with columns for category ID and name, and then inserts sample data into it. It assumes a SQL environment where these commands are supported.

```sql
CREATE TABLE categories (
  id int,
  category_name text
);
INSERT INTO categories
    (id, category_name)
VALUES
    (181891, 'Psychological Thriller'),
    (181892, 'Romance'),
    (181893, 'Fantasy'),
    (181894, 'Science Fiction'),
    (181895, 'Action');
```

--------------------------------

### SIGN() Function with Table Data

Source: https://docs.oxla.com/sql-reference/sql-functions/math-functions/sign

Complete example demonstrating how to create a table, populate it with numeric values, and use the SIGN() function to determine the sign of all values in a specific column. This shows practical application of SIGN() with database tables.

```sql
CREATE TABLE signTable(value float);

INSERT INTO signTable(value)
VALUES 
(1000),
(-200),
(0),
(0.22),
(-12.3),
(-0.0);

SELECT value, SIGN(value) AS sign
FROM signTable;
```

--------------------------------

### SQL COPY FROM with Dash Delimiter Example

Source: https://docs.oxla.com/sql-reference/sql-statements/copy-from/copy-from-with-delimiter

An example of the SQL COPY FROM command specifically using a dash (-) as the delimiter to import data into the 'featurelisttable'. This demonstrates how to handle non-standard delimiters.

```SQL
COPY featurelisttable FROM 's3://oxla-testdata/cayo/feature2.csv' (DELIMITER '-');
```

```SQL
--
(0 rows)
```

```SQL
ERROR: unexpected data at line: 1 col: 0 position: 108, expected , but got:
```

--------------------------------

### TO_CHAR Example: Formatting Interval to Minute-Second

Source: https://docs.oxla.com/sql-reference/sql-functions/timestamp-functions/to-char

An SQL query illustrating how to format an interval of seconds into a string that displays minutes and seconds. This example highlights formatting smaller time units within an interval.

```sql
SELECT TO_CHAR('65 seconds'::INTERVAL, '"MINUTE": MI "SECOND": SS') AS FORMATTED_INTERVAL;
```

--------------------------------

### REGR_SLOPE() SQL Function Syntax and Example

Source: https://docs.oxla.com/sql-reference/sql-functions/aggregate-functions/regr-slope

Demonstrates the SQL syntax for the REGR_SLOPE() function and provides a complete example including table creation, data insertion, and querying to calculate the regression slope.

```sql
DROP TABLE IF EXISTS film;
CREATE TABLE film (
  title text NOT NULL,
  length int,
  rating int
);
INSERT INTO film(title, length, rating) VALUES
  ('ATTRACTION NEWTON', 83, 5),
  ('CHRISTMAS MOONSHINE', 150, 7),
  ('DANGEROUS UPTOWN', 121, 4),
  ('KILL BROTHERHOOD', 54, 3),
  ('HALLOWEEN NUTS', 47, 5),
  ('HOURS RAGE', 122, 7),
  ('PIANIST OUTFIELD', 136, 7),
  ('PICKUP DRIVING', 77, 3),
  ('INDEPENDENCE HOTEL', 157, 7),
  ('PRIVATE DROP', 106, 4),
  ('SAINTS BRIDE', 125, 3),
  ('FOREVER CANDIDATE', 131, 7),
  ('MILLION ACE', 142, 5),
  ('SLEEPY JAPANESE', 137, 4),
  ('WRATH MILE', 176, 7),
  ('YOUTH KICK', 179, 7),
  ('CLOCKWORK PARADISE', 143, 5);

SELECT
    REGR_SLOPE(rating, length) AS Slope
FROM film;
```

--------------------------------

### SQL AT TIME ZONE Example: Convert Timestamp to MST

Source: https://docs.oxla.com/sql-reference/sql-data-types/timestamp-without-time-zone

Provides a practical example of converting a specific timestamp ('2001-02-16 10:28:30') to the MST time zone using the AT TIME ZONE operator.

```sql
SELECT TIMESTAMP '2001-02-16 10:28:30' AT TIME ZONE 'MST';
```

--------------------------------

### Create Product Table and Insert Sample Data

Source: https://docs.oxla.com/sql-reference/sql-clauses/from/from

Creates a product table with columns for id, product name, category, and price. Inserts six sample product records representing items from different categories (Shoes, Shirt, Hat, Bag, Skirt) with varying prices ranging from 6500 to 8500.

```sql
CREATE TABLE product (
  id int,
  product text,
  category text,
  price int
);
INSERT INTO product 
    (id, product, category, price) 
VALUES 
    (445747,'Court vision women's shoes nike','Shoes', 8000),
    (445641,'Disney kids h&m','Shirt', 6500),
    (477278,'Defacto adidas','Hat', 8500),
    (481427,'Sophie shopping bag','Bag', 6500),
    (411547,'Candy skirt zara','Skirt', 6500),
    (488198,'Slim cut skirt hush puppies','Skirt', 7600);
```

--------------------------------

### SUBSTRING Function Syntax - Two Arguments

Source: https://docs.oxla.com/sql-reference/sql-functions/string-functions/substring

Extracts a substring starting from the specified position to the end of the string. Takes a string and start position as input parameters and returns the extracted substring.

```sql
SUBSTRING( string, start_position )
```

--------------------------------

### Create student_data table and insert sample records using SQL

Source: https://docs.oxla.com/sql-reference/sql-statements/select

Demonstrates table creation with three columns (id, name, domicile) and population with three student records. This example table is used throughout the documentation to illustrate SELECT query functionality.

```sql
CREATE TABLE student_data (
  id int,
  name text,
  domicile text
);
INSERT INTO student_data 
    (id, name, domicile) 
VALUES 
    (119291,'Jordan','Los Angeles'),
    (119292,'Mike','Melbourne'),
    (119293,'Will','Sydney');
```

--------------------------------

### Create Table Statement in SQL

Source: https://docs.oxla.com/sql-reference/transactions

Example of a SQL CREATE TABLE statement to define a new table named 'productsnew' with columns for product name, price, and stock quantity. This is a prerequisite for data insertion examples.

```sql
CREATE TABLE productsnew(
    product_name TEXT,
    price INT,
    stock_quantity INT
);

```

--------------------------------

### Query all data from student_data table using SQL

Source: https://docs.oxla.com/sql-reference/sql-statements/select

Practical example retrieving all columns and rows from the student_data table. Demonstrates the basic SELECT * syntax and shows the expected output format with all student records including IDs, names, and domiciles.

```sql
SELECT * FROM student_data;
```

--------------------------------

### Basic LENGTH Function Example - SQL

Source: https://docs.oxla.com/sql-reference/sql-functions/string-functions/length

Simple example demonstrating the LENGTH() function to count characters in a string literal. This query returns 24 as the output, representing the character count in 'Oxla PostgreSQL Tutorial'.

```sql
SELECT LENGTH ('Oxla PostgreSQL Tutorial');
```

--------------------------------

### Create and Populate Test Table

Source: https://docs.oxla.com/sql-reference/sql-clauses/order-by

Creates the salaryemp table with employee data including ID, name, division, and salary. Inserts 10 sample employee records for use in ORDER BY examples and demonstrations.

```sql
CREATE TABLE salaryemp
(
    emp_id int,
    emp_name text,
    emp_div text,
    emp_sal int
);

INSERT INTO salaryemp
VALUES
(1002, 'Mike', 'Marketing', 6000),
(1003, 'Sean', 'Marketing', 6500),
(1004, 'Victor', 'Finance', 7000),
(1005, 'Lewis', 'Sales', 5500),
(1006, 'David', 'Marketing', 8000),
(1007, 'Omar', 'Finance', 8000),
(1008, 'Meghan', 'Finance', 7500),
(1009, 'Harry', 'Operations', 4500),
(1010, 'Steve', 'Marketing', 6800),
(1011, 'David', 'Sales', 8200);
```

--------------------------------

### Oxla SQL CTE Alias Renaming Example

Source: https://docs.oxla.com/sql-reference/sql-clauses/with

Shows how to explicitly change the alias of a CTE when it's used in a subsequent part of the query. This example demonstrates using CROSS JOIN with the same CTE aliased differently.

```sql
WITH b AS (SELECT 1 AS c1) SELECT b.c1, b1.c1 FROM b CROSS JOIN b AS b1;
```

--------------------------------

### Create timestamp table and insert sample data

Source: https://docs.oxla.com/sql-reference/sql-functions/timestamp-functions/unix-seconds

Creates a table named 'time_example' with a timestamp column and inserts four sample timestamp values for demonstration purposes. This table is used in subsequent examples to show batch conversion of multiple timestamps.

```sql
CREATE TABLE time_example (
  time_stampvalues timestamp
);

INSERT INTO time_example VALUES 
('2022-12-25 13:30:00'),
('2020-09-25 07:25:00'),
('2008-12-25 15:30:00'),
('2021-10-02 06:30:00');
```

--------------------------------

### SUBSTRING Function Syntax - Three Arguments

Source: https://docs.oxla.com/sql-reference/sql-functions/string-functions/substring

Extracts a substring of specified length starting from the given position. Takes a string, start position, and length as parameters and returns the extracted substring with the specified character count.

```sql
SUBSTRING(string, start_position, length)
```

--------------------------------

### LEAD() with Expression and Offset Parameters

Source: https://docs.oxla.com/sql-reference/sql-functions/window-functions/lead

Demonstrates LEAD() with expression and offset parameters specified, retrieving the next quantity value for each buyer. Returns NULL when no subsequent row exists.

```sql
SELECT buyerid, dateid, qty,
  LEAD(qty,1) OVER (ORDER BY buyerid, dateid) AS next_qty
FROM winsales WHERE buyerid = 'c' 
ORDER BY buyerid, dateid;
```

--------------------------------

### SQL DOUBLE PRECISION Data Type Examples

Source: https://docs.oxla.com/sql-reference/sql-data-types/numeric-type/numeric

Demonstrates the usage of the DOUBLE PRECISION data type for 64-bit floating-point numbers. Examples include table creation, inserting values, and observing rounding behavior with high precision.

```sql
CREATE TABLE numbersdouble (
    column_1 DOUBLE PRECISION
);
INSERT into numbersdouble (column_1)
VALUES (1.234568817283122);
SELECT * FROM numbersdouble;
```

```sql
CREATE TABLE numbersdouble1 (
    column_1 DOUBLE PRECISION
);
INSERT into numbersdouble1 (column_1)
VALUES (1.234568817283122773);
SELECT * FROM numbersdouble1;
```

--------------------------------

### SQL REAL Data Type Examples

Source: https://docs.oxla.com/sql-reference/sql-data-types/numeric-type/numeric

Provides examples of using the REAL data type for 32-bit floating-point numbers. It covers creating a table, inserting values, and demonstrates potential rounding issues with high precision inputs.

```sql
CREATE TABLE numbers (
    column_1 REAL
);
INSERT into numbers (column_1)
VALUES (1.234568);
SELECT * FROM numbers;
```

```sql
CREATE TABLE numbers1 (
column_1 REAL
);
INSERT into numbers1 (column_1)
VALUES (1.2345689);
SELECT * FROM numbers1;
```

```sql
CREATE TABLE numbers2 (
    column_1 REAL
);
INSERT into numbers2 (column_1)
VALUES (1.2345682991822);
SELECT * FROM numbers2;
```

--------------------------------

### SQL BIGINT Data Type Example

Source: https://docs.oxla.com/sql-reference/sql-data-types/numeric-type/numeric

Illustrates the use of the BIGINT data type for storing large whole numbers that exceed the standard INT range. The example shows table creation, insertion of large numbers, and querying.

```sql
CREATE TABLE galaxies (
    galaxy_name TEXT,
    star BIGINT
);
INSERT INTO galaxies (galaxy_name, star)
VALUES 
('Milky Way', 100000000000),
('Cigar', 30000000000),
('Andromeda', 1000000000000),
('Cosmos', 2000000000000000000);
SELECT * FROM galaxies;
```

--------------------------------

### SQL: Create customer_orders Table

Source: https://docs.oxla.com/system-catalogs/catalogs/pg_class

Creates a table named 'customer_orders' with columns for order ID, customer ID, order date, and total amount. This is a foundational step for storing order data.

```sql
CREATE TABLE customer_orders (
    order_id INT,
    customer_id INT,
    order_date DATE,
    total_amount INT
);
```

--------------------------------

### Create a Sample Table (SQL)

Source: https://docs.oxla.com/system-catalogs/catalogs/pg_attribute

This SQL statement creates a new table named 'books' with several columns including book_id, title, author, genre, and publication_year. This is a prerequisite for demonstrating column filtering.

```sql
CREATE TABLE books (
    book_id int,
    title text,
    author text,
    genre text,
    publication_year int
);
```

--------------------------------

### MIN() Window Function Example with Partition and Running Minimum

Source: https://docs.oxla.com/sql-reference/sql-functions/window-functions/min

Demonstrates MIN() function usage with two window specifications: one partitioned by rating to find minimum film length per rating category, and another for calculating running minimum length ordered by film length.

```sql
SELECT
   title,
   length,
   rating,
   MIN(length) OVER ( PARTITION BY rating ) AS MinLengthByRating,
   MIN(length) OVER ( 
ORDER BY
   length ROWS BETWEEN unbounded preceding AND CURRENT ROW ) AS RunningMinLength 
FROM film 
ORDER BY length;
```

--------------------------------

### Basic GROUP BY Query Example in SQL

Source: https://docs.oxla.com/sql-reference/sql-clauses/group-by

A simple GROUP BY example that groups orders by product name to retrieve distinct products ordered. Returns one row for each unique product, demonstrating the fundamental use case of GROUP BY for obtaining distinct grouped values.

```sql
SELECT order_prod
FROM orders
GROUP BY order_prod;
```

--------------------------------

### Create and Populate Book Inventory Table - SQL

Source: https://docs.oxla.com/sql-reference/sql-statements/copy-to/copy-to-stdout

Creates a book_inventory table with title and copies_available columns, then inserts five sample book records. This demonstrates table creation and data insertion before exporting with COPY TO STDOUT.

```sql
CREATE TABLE book_inventory (
    title TEXT,
    copies_available INT
);
INSERT INTO book_inventory (title, copies_available) VALUES
('To Kill a Mockingbird', 5),
('1984', 8),
('The Great Gatsby', 3),
('Moby Dick', 2),
('War and Peace', 4);
```

--------------------------------

### LEAD() Function Syntax in SQL

Source: https://docs.oxla.com/sql-reference/sql-functions/window-functions/lead

Defines the basic syntax structure for the LEAD() window function with PARTITION BY and ORDER BY clauses. The function accepts an expression, optional offset (defaults to 1), and optional default value (defaults to NULL).

```sql
LEAD (expression, offset, default) 
OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)
```

--------------------------------

### Oxla SQL: Create Table for Data Import

Source: https://docs.oxla.com/sql-reference/sql-statements/copy-from/copy-from-with-null

This SQL code defines a table named 'idqty' with two integer columns, 'id' and 'quantity'. This table structure is used in the examples to import data from a CSV file, demonstrating how NULL values are handled.

```sql
CREATE TABLE idqty (id INTEGER, quantity INTEGER);
```

--------------------------------

### Oxla SQL: FLOOR() Syntax and Basic Examples

Source: https://docs.oxla.com/sql-reference/sql-functions/math-functions/floor

Demonstrates the basic syntax of the FLOOR() function in Oxla SQL and provides examples of rounding down positive and negative decimal numbers. The function takes one argument 'x', which is a decimal number or an expression evaluating to one.

```sql
SELECT FLOOR(345.6765467);
-- Returns 345
```

```sql
SELECT FLOOR(-0.987657);
-- Returns -1
```

--------------------------------

### Create and Insert Data into Orders Table - SQL

Source: https://docs.oxla.com/system-catalogs/oxla_home_files

Create an orders table with five columns (order_id, order_date, total_amount, shipping_address, status) and insert five sample records. This demonstrates how data insertion populates the oxla_home_files table with corresponding file metadata.

```sql
CREATE TABLE orders (
  order_id INT,
  order_date DATE,
  total_amount INT,
  shipping_address TEXT,
  status TEXT
);

INSERT INTO orders 
    (order_id, order_date, total_amount, shipping_address, status) 
VALUES 
    (1001, '2024-07-13', 150.75, '123 Main St, Anytown, USA', 'Shipped'),
    (1002, '2024-07-12', 200.50, '456 Elm St, Othertown, USA', 'Delivered'),
    (1003, '2024-07-12', 350.25, '789 Oak St, Anotherplace, USA', 'Processing'),
    (1001, '2024-07-11', 100.00, '321 Pine St, Somewhere, USA', 'Cancelled'),
    (1004, '2024-07-10', 500.00, '555 Maple St, Nowhere, USA', 'Pending');
```

--------------------------------

### Oxla SQL: COPY FROM with HEADER ON Example

Source: https://docs.oxla.com/sql-reference/sql-statements/copy-from/copy-from-with-header

Demonstrates importing data using the COPY FROM statement with the HEADER ON option. This explicitly tells Oxla SQL to skip the first line of the CSV file, treating it as a header and not importing it as data. The example shows successful import and verification.

```sql
COPY idqty FROM 's3://oxla-testdata/cayo/idvals.csv'(HEADER ON);
```

```sql
--
(0 rows)
```

```sql
SELECT * FROM idqty;
+----+----------+
| id | quantity |
+---------------+ 
| 1  | 5        |
| 2  | 2        |
| 3  | 1        |
| 4  | 8        |
| 5  | 4        |
| 6  | 3        |
+----+----------+ 
```

--------------------------------

### SELECT Query Verification

Source: https://docs.oxla.com/sql-reference/sql-statements/create-table

Retrieves all records from the products table to verify successful data insertion. Returns all columns and rows to display the complete dataset.

```sql
SELECT * FROM products;
```

--------------------------------

### Create and Populate Sample Payments Table

Source: https://docs.oxla.com/sql-reference/sql-functions/aggregate-functions/for-min

SQL script to create a payments table with customer payment records and apply sample data. This table is used in subsequent examples to demonstrate FOR_MIN() functionality.

```sql
CREATE TABLE payments (
  paymentid int,
  customer_name text,
  price real,
  discount real);

INSERT INTO
  payments (paymentid, customer_name, price, discount)
VALUES
  (1, 'Alex', 280.12, 0.1),
  (2, NULL, 35.75, NULL),
  (3, 'Alex', 45.1, 0.05),
  (4, 'Alex', NULL, 0.4),
  (5, 'John', NULL, 0.1),
  (6, 'Bob', 50.45, 0.07),
  (7, 'Bob', 120.5, 0.0);
```

--------------------------------

### SQL LENGTH Function Example for Text Character Count

Source: https://docs.oxla.com/sql-reference/sql-data-types/text

This SQL example demonstrates the use of the LENGTH function to count the number of characters in a given text string, including spaces. The function takes the text as a parameter and returns an integer representing the total character count.

```sql
SELECT LENGTH ('UNITED STATES');
```

--------------------------------

### SQL Example: Using REGR_COUNT() with Film Data

Source: https://docs.oxla.com/sql-reference/sql-functions/aggregate-functions/regr-count

This example demonstrates how to use the REGR_COUNT() function in SQL. It first creates and populates a temporary 'film' table with 'title', 'length', and 'rating' columns. Then, it queries the table to count the number of rows where both 'rating' and 'length' are non-null, illustrating the function's practical application.

```sql
DROP TABLE IF EXISTS film;
CREATE TABLE film (
  title text NOT NULL,
  length int,
  rating int
);
INSERT INTO film(title, length, rating) VALUES
  ('ATTRACTION NEWTON', 83, 5),
  ('CHRISTMAS MOONSHINE', 150, 7),
  ('DANGEROUS UPTOWN', 121, 4),
  ('KILL BROTHERHOOD', 54, 3),
  ('HALLOWEEN NUTS', 47, 5),
  ('HOURS RAGE', 122, 7),
  ('PIANIST OUTFIELD', 136, 7),
  ('PICKUP DRIVING', 77, 3),
  ('INDEPENDENCE HOTEL', 157, 7),
  ('PRIVATE DROP', 106, 4),
  ('SAINTS BRIDE', 125, 3),
  ('FOREVER CANDIDATE', 131, 7),
  ('MILLION ACE', 142, 5),
  ('SLEEPY JAPANESE', 137, 4),
  ('WRATH MILE', 176, 7),
  ('YOUTH KICK', 179, 7),
  ('CLOCKWORK PARADISE', 143, 5);
```

```sql
SELECT
    REGR_COUNT(rating, length) AS NonNullPairsCount
FROM film;
```

--------------------------------

### Create and Insert Customer Table

Source: https://docs.oxla.com/sql-reference/sql-clauses/from/right-join

Creates a customer table with id and customer_name columns, then inserts four customer records. This table serves as the left table in subsequent RIGHT JOIN operations.

```sql
CREATE TABLE customer (
  id int NOT NULL,
  customer_name text
);

INSERT INTO customer
    (id, customer_name)
VALUES
    (201011,'James'),
    (200914,'Harry'),
    (201029,'Ellie'),
    (201925,'Mary');
```

--------------------------------

### LN() Function with CREATE TABLE and INSERT - SQL

Source: https://docs.oxla.com/sql-reference/sql-functions/math-functions/ln

Shows how to create a table with an integer column and insert sample data for use with the LN() function. This sets up data that can be processed by the natural logarithm function.

```sql
CREATE TABLE LNtable(initValue int);
INSERT INTO LNtable(initValue)
VALUES (75), (18), (28);
```

--------------------------------

### Oxla SQL: COPY FROM with HEADER OFF Example

Source: https://docs.oxla.com/sql-reference/sql-statements/copy-from/copy-from-with-header

Illustrates importing data using COPY FROM without explicitly specifying a header option (defaulting to HEADER OFF). This example shows the potential error when the CSV file contains headers that do not match the table's column data types. It also demonstrates the successful import after removing headers from the CSV.

```sql
COPY idqty FROM 's3://oxla-testdata/cayo/idvals.csv';
```

```sql
Error while parsing f32 from csv at line:0 col:6 position:26, parsing error
```

```sql
COPY idqty FROM 's3://oxla-testdata/cayo/idvals.csv';
```

```sql
--
(0 rows)
```

```sql
SELECT * FROM idqty;
+----+----------+
| id | quantity |
+---------------+ 
| 1  | 5        |
| 2  | 2        |
| 3  | 1        |
| 4  | 8        |
| 5  | 4        |
| 6  | 3        |
+----+----------+ 
```

--------------------------------

### FOR_MAX() with GROUP BY Clause

Source: https://docs.oxla.com/sql-reference/sql-functions/aggregate-functions/for-max

Demonstrates FOR_MAX() usage with GROUP BY to get the discount for the highest price paid by each customer group.

```sql
SELECT customer_name, FOR_MAX(price, discount) AS discount
FROM payments
GROUP BY customer_name;
```

--------------------------------

### Create and Populate Table for STRPOS

Source: https://docs.oxla.com/sql-reference/sql-functions/string-functions/strpos

Creates a sample table named 'listofwords' with a text column and inserts sample word data for demonstration of STRPOS function with table columns.

```sql
CREATE TABLE listofwords (
  words text
);
INSERT INTO listofwords 
    (words) 
VALUES 
    ('corral'),
    ('traditionally'),
    ('real'),
    ('communal'),
    ('challenge'),
    ('fall'),
    ('wall'),
    ('gallop'),
    ('albatross');
```

--------------------------------

### Create and Insert Sample Data for WHERE Examples

Source: https://docs.oxla.com/sql-reference/sql-clauses/where

Sets up a sample salary table with employee records including ID, name, department, address, and salary information. This table is used for demonstrating various WHERE clause operations with different comparison operators.

```sql
CREATE TABLE salary (
  empid int,
  empname text,
  empdept text,
  empaddress text,
  empsalary int
);
INSERT INTO salary 
    (empid, empname, empdept, empaddress, empsalary) 
VALUES 
    (2001,'Paul','HR', 'California', null ),
    (2002,'Brandon','Product', 'Norway', 15000),
    (2003,'Bradley','Marketing', 'Texas', null),
    (2004,'Lisa','Marketing', 'Houston', 10000),
    (2005,'Emily','Marketing', 'Texas', 20000),
    (2006,'Bobby','Finance', 'Seattle', 20000),
    (2007,'Parker','Project', 'Texas', 45000);
```

--------------------------------

### LEAD() with Expression, Offset, and Default Parameters

Source: https://docs.oxla.com/sql-reference/sql-functions/window-functions/lead

Uses LEAD() with all three parameters to retrieve the next buyer ID, substituting 'unknown' as the default value when no subsequent row exists at the specified offset.

```sql
SELECT buyerid, dateid, qty,
  LEAD(buyerid,1,'unknown') OVER (ORDER BY dateid) AS next_buyerid
FROM winsales 
ORDER BY dateid;
```

--------------------------------

### Query Time Example Table in SQL

Source: https://docs.oxla.com/sql-reference/sql-functions/timestamp-functions/unix-macros

Retrieves all records from the time_example table to display the original timestamp values before conversion to UNIX microseconds.

```sql
SELECT * FROM time_example;
```

--------------------------------

### Complete PostgreSQL Connection Setup on Windows

Source: https://docs.oxla.com/troubleshooting-optimization/error-handling

Full sequence of commands to navigate to PostgreSQL bin directory, set UTF8 encoding, activate the UTF8 code page, and connect to the Oxla server. This resolves both command recognition and encoding issues.

```batch
C:\Program Files\PostgreSQL\14\bin>SET PGCLIENTENCODING=UTF8

C:\Program Files\PostgreSQL\14\bin>chcp 65001
Active code page: 65001

C:\Program Files\PostgreSQL\14\bin>psql.exe -h 44.210.23.203
```

--------------------------------

### CEIL Function with Positive Decimal - SQL

Source: https://docs.oxla.com/sql-reference/sql-functions/math-functions/ceil

Rounds up a positive decimal value to the nearest greater integer. In this example, 300.55 is rounded up to 301.

```sql
SELECT CEIL(300.55);
```

--------------------------------

### Time Series Analysis with LEAD() for Daily Sales Comparison

Source: https://docs.oxla.com/sql-reference/sql-functions/window-functions/lead

Applies LEAD() in a time series context to compare each day's sales quantity with the next day's quantity and calculate the difference. Useful for trend analysis and change tracking.

```sql
SELECT dateid, qty,
  LEAD(qty) OVER (ORDER BY dateid) AS next_day_qty,
  LEAD(qty) OVER (ORDER BY dateid) - qty AS qty_change
FROM winsales
ORDER BY dateid;
```

--------------------------------

### Create SQL Table Safely with IF NOT EXISTS

Source: https://docs.oxla.com/sql-reference/sql-statements/create-table

This SQL snippet demonstrates the creation of a table named 'products' using the `CREATE TABLE IF NOT EXISTS` syntax. This approach ensures that the table is only created if it does not already exist, preventing potential errors during script execution. The table includes columns for product ID, name, category, and price.

```sql
CREATE TABLE IF NOT EXISTS products (
  productID int,
  productName text,
  category text NOT NULL,
  price real
);
```
Tokens:
Raw