---
layout: default
title: Learn the Basics
parent: Getting Started
nav_order: 1 
---

# Learn the Basics

This page will guide you through creating your first database with Oxibase and
performing basic operations.

## Installation

Before starting, ensure you have Oxibase installed. If not, follow the
[Installation Guide]({% link _docs/tutorials/installation.md %}).

```bash
# Install with Cargo
cargo install oxibase

# Or build from source
git clone https://github.com/oxibase/oxibase.git
cd oxibase
cargo build --release
```

## Starting the CLI

Oxibase includes a command-line interface (CLI) for interactive use:

```bash
# Start with an in-memory database (data is lost when the CLI exits)
oxibase

# Or with persistent storage (data is saved to disk)
oxibase --db "file:///path/to/data"

# Execute a query directly
oxibase -q -e "SELECT 1 + 1 as sum;"
```

## Creating a Table

Let's create a simple table to store product information:

```sql
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT,
    price FLOAT NOT NULL,
    category TEXT,
    in_stock BOOLEAN,
    created_at TIMESTAMP
);
```

## Inserting Data

Now let's add some sample products:

```sql
-- Insert a single product
INSERT INTO products (id, name, description, price, category, in_stock, created_at)
VALUES (1, 'Laptop', 'High-performance laptop with 16GB RAM', 1299.99, 'Electronics', TRUE, NOW());

-- Insert multiple products
INSERT INTO products (id, name, description, price, category, in_stock, created_at) VALUES 
(2, 'Smartphone', '5G smartphone with 128GB storage', 799.99, 'Electronics', TRUE, NOW()),
(3, 'Headphones', 'Wireless noise-cancelling headphones', 249.99, 'Accessories', TRUE, NOW()),
(4, 'Monitor', '27-inch 4K monitor', 349.99, 'Electronics', FALSE, NOW()),
(5, 'Keyboard', 'Mechanical gaming keyboard', 129.99, 'Accessories', TRUE, NOW());
```

## Querying Data

### Basic SELECT

Retrieve all products:

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

### Filtering with WHERE

Retrieve products in a specific category:

```sql
SELECT name, price FROM products WHERE category = 'Electronics';
```

### Sorting with ORDER BY

Sort products by price from highest to lowest:

```sql
SELECT name, price FROM products ORDER BY price DESC;
```

### Limiting Results

Get only the 3 most expensive products:

```sql
SELECT name, price FROM products ORDER BY price DESC LIMIT 3;
```

## Updating Data

Let's update the price of a product:

```sql
UPDATE products SET price = 1199.99 WHERE id = 1;
```

Update multiple fields:

```sql
UPDATE products 
SET price = 349.99, description = 'Updated description'
WHERE id = 2;
```

## Deleting Data

Remove a product from the database:

```sql
DELETE FROM products WHERE id = 5;
```

## Creating an Index

Indexes speed up queries on frequently searched columns:

```sql
-- Create an index on the category column
CREATE INDEX idx_category ON products(category);

-- Create a unique index on the name column
CREATE UNIQUE INDEX idx_name ON products(name);
```

## Working with Transactions

Transactions ensure that multiple operations succeed or fail as a unit:

```sql
-- Start a transaction
BEGIN TRANSACTION;

-- Perform operations
UPDATE products SET price = price * 0.9 WHERE category = 'Electronics';

INSERT INTO products (id, name, description, price, category, in_stock, created_at) VALUES 
(6, 'Tablet', '12.9-inch iPad Pro', 999.99, 'Electronics', TRUE, NOW());
SELECT * FROM products;
-- Roll back to discard changes
ROLLBACK;

SELECT * FROM products;
-- 
-- Or comit the transaction to save changes
-- COMMIT;
```

## Using Joins and Foreign Keys

Let's create a categories table and join it with our products, enforcing the relationship with a Foreign Key constraint:

```sql
-- Create categories table
CREATE TABLE categories (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT
);

-- Add some categories
INSERT INTO categories (id, name, description) VALUES
(1, 'Electronics', 'Electronic devices and gadgets'),
(2, 'Accessories', 'Peripherals and accessories for devices');

-- Add a category_id column to products
ALTER TABLE products ADD COLUMN category_id INTEGER;

-- Update products to use category ids
UPDATE products SET category_id = 1 WHERE category = 'Electronics';
UPDATE products SET category_id = 2 WHERE category = 'Accessories';

-- Enforce referential integrity by adding a Foreign Key constraint
-- Setting ON DELETE SET NULL ensures that if a category is deleted, its products are kept but their category_id is set to NULL
ALTER TABLE products ADD CONSTRAINT fk_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL;

-- Join tables to get category information
SELECT p.id, p.name, p.price, c.name AS category_name, c.description AS category_description
FROM products p
JOIN categories c ON p.category_id = c.id;
```

## Using Aggregation Functions

Get summary statistics for your products:

```sql
-- Count products by category
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category;

-- Get average price by category
SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category;

-- Get price range by category
SELECT 
    category,
    MIN(price) AS min_price,
    MAX(price) AS max_price,
    AVG(price) AS avg_price
FROM products
GROUP BY category;
```

## Creating User-Defined Functions

Extend SQL with custom functions written in PL/SQL, Rhai, or Python:

```sql
-- Create a function that calculates discount
CREATE FUNCTION calculate_discount(price FLOAT, discount_rate FLOAT) RETURNS FLOAT
LANGUAGE RHAI AS 'price * (1.0 - discount_rate)';

-- Use the function in queries
SELECT name, price, calculate_discount(price, 0.1) as discounted_price
FROM products;
```

```sql
-- Function with string manipulation
CREATE FUNCTION format_name(first_name TEXT, last_name TEXT) RETURNS TEXT
LANGUAGE RHAI AS 'first_name + " " + last_name.to_upper()';

SELECT format_name('John', 'Doe') as full_name;
```

## Working with Common Table Expressions (CTEs)

CTEs make complex queries more readable:

```sql
-- Find top products by category
WITH category_stats AS (
    SELECT 
        category,
        AVG(price) as avg_price,
        MAX(price) as max_price
    FROM products
    GROUP BY category
)
SELECT 
    p.name,
    p.price,
    cs.avg_price,
    ROUND((p.price / cs.avg_price - 1) * 100, 2) as pct_above_avg
FROM products p
JOIN category_stats cs ON p.category = cs.category
WHERE p.price > cs.avg_price
ORDER BY pct_above_avg DESC;
```

## Next Steps

Now that you've learned the basics, you might want to explore:

- [Connection Strings]({% link _docs/references/connection-strings.md %}) - More connection options
- [SQL Commands]({% link _docs/references/sql-commands.md %}) - Comprehensive SQL reference
- [Data Types]({% link _docs/references/data-types.md %}) - Detailed information on data types
- [Indexing]({% link _docs/explanations/architecture/indexing.md %}) - How to optimize queries with indexes
- [Transaction Isolation]({% link _docs/explanations/architecture/transaction-isolation.md %}) - How transactions work

For a more comprehensive reference, browse the [Documentation](/docs/).
