---
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 Rhai, Python, or JavaScript:
```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/).