Basic Operations

Basic SQL Operations in Firebird

This guide covers fundamental SQL operations for working with Firebird databases, perfect for beginners getting started with database management.

Connecting to Firebird

Using ISQL

# Connect to a local database
isql employee.fdb -user SYSDBA -password masterkey

# Connect to a remote database
isql server:/path/to/database.fdb -user username -password password

Creating Tables

Basic Table Creation

CREATE TABLE customers (
    customer_id INTEGER NOT NULL PRIMARY KEY,
    first_name VARCHAR(30) NOT NULL,
    last_name VARCHAR(30) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20),
    created_date DATE DEFAULT CURRENT_DATE
);

CREATE TABLE products (
    product_id INTEGER NOT NULL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    description BLOB SUB_TYPE 1,
    price NUMERIC(10,2) NOT NULL CHECK (price > 0),
    stock_quantity INTEGER DEFAULT 0,
    category VARCHAR(50)
);

CREATE TABLE orders (
    order_id INTEGER NOT NULL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount NUMERIC(10,2),
    status VARCHAR(20) DEFAULT 'PENDING',
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Using Auto-increment (Identity)

-- Firebird 3.0+ syntax
CREATE TABLE employees (
    emp_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    emp_name VARCHAR(50) NOT NULL,
    hire_date DATE DEFAULT CURRENT_DATE
);

-- Pre-3.0 method using generators
CREATE GENERATOR gen_employee_id;

CREATE TABLE employees_old (
    emp_id INTEGER NOT NULL PRIMARY KEY,
    emp_name VARCHAR(50) NOT NULL
);

-- Create trigger for auto-increment
SET TERM ^ ;
CREATE TRIGGER employees_bi FOR employees_old
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    IF (NEW.emp_id IS NULL) THEN
        NEW.emp_id = GEN_ID(gen_employee_id, 1);
END^
SET TERM ; ^

Inserting Data

Basic INSERT

-- Single row insert
INSERT INTO customers (customer_id, first_name, last_name, email)
VALUES (1, 'John', 'Doe', '[email protected]');

-- Multiple rows insert (Firebird 2.0+)
INSERT INTO products (product_id, product_name, price, stock_quantity)
VALUES 
    (1, 'Laptop', 999.99, 10),
    (2, 'Mouse', 19.99, 50),
    (3, 'Keyboard', 49.99, 30);

-- Insert with returning
INSERT INTO customers (first_name, last_name, email)
VALUES ('Jane', 'Smith', '[email protected]')
RETURNING customer_id;

INSERT from SELECT

-- Copy data from one table to another
INSERT INTO customers_archive
SELECT * FROM customers WHERE created_date < '2020-01-01';

-- Insert with transformation
INSERT INTO order_summary (customer_id, total_orders, total_amount)
SELECT 
    customer_id,
    COUNT(*),
    SUM(total_amount)
FROM orders
GROUP BY customer_id;

Querying Data

Basic SELECT

-- Select all columns
SELECT * FROM customers;

-- Select specific columns
SELECT first_name, last_name, email 
FROM customers;

-- With WHERE clause
SELECT * FROM products 
WHERE price < 100 AND stock_quantity > 0;

-- Using aliases
SELECT 
    c.first_name || ' ' || c.last_name AS full_name,
    c.email AS contact_email
FROM customers c;

Filtering Data

-- Comparison operators
SELECT * FROM products WHERE price >= 50 AND price <= 200;
SELECT * FROM products WHERE price BETWEEN 50 AND 200;

-- IN operator
SELECT * FROM orders 
WHERE status IN ('PENDING', 'PROCESSING', 'SHIPPED');

-- LIKE operator for pattern matching
SELECT * FROM customers 
WHERE email LIKE '%@gmail.com';

SELECT * FROM products 
WHERE product_name LIKE 'Lap%';  -- Starts with 'Lap'

-- NULL handling
SELECT * FROM customers 
WHERE phone IS NULL;

SELECT * FROM customers 
WHERE phone IS NOT NULL;

Sorting and Limiting

-- ORDER BY
SELECT * FROM products 
ORDER BY price DESC;

SELECT * FROM customers 
ORDER BY last_name, first_name;

-- FIRST/SKIP (Firebird's LIMIT/OFFSET)
SELECT FIRST 10 * FROM products 
ORDER BY price DESC;

SELECT FIRST 10 SKIP 20 * FROM products 
ORDER BY product_name;

-- Using ROWS (Firebird 3.0+)
SELECT * FROM products 
ORDER BY price DESC
ROWS 1 TO 10;

Updating Data

Basic UPDATE

-- Update single column
UPDATE products 
SET price = price * 1.1 
WHERE category = 'Electronics';

-- Update multiple columns
UPDATE customers 
SET 
    email = '[email protected]',
    phone = '123-456-7890'
WHERE customer_id = 1;

-- Update with returning
UPDATE products 
SET stock_quantity = stock_quantity - 1
WHERE product_id = 1
RETURNING product_name, stock_quantity;

UPDATE with JOIN

-- Update using data from another table
UPDATE orders o
SET o.total_amount = (
    SELECT SUM(od.quantity * p.price)
    FROM order_details od
    JOIN products p ON od.product_id = p.product_id
    WHERE od.order_id = o.order_id
)
WHERE EXISTS (
    SELECT 1 FROM order_details 
    WHERE order_id = o.order_id
);

Deleting Data

Basic DELETE

-- Delete specific rows
DELETE FROM customers 
WHERE customer_id = 100;

-- Delete with condition
DELETE FROM orders 
WHERE order_date < DATEADD(-1 YEAR TO CURRENT_DATE)
  AND status = 'CANCELLED';

-- Delete with returning
DELETE FROM products 
WHERE stock_quantity = 0
RETURNING product_id, product_name;

-- Delete all rows (use with caution!)
DELETE FROM temp_table;

Working with Dates

-- Current date and time
SELECT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP FROM RDB$DATABASE;

-- Date arithmetic
SELECT 
    order_date,
    order_date + 7 AS week_later,
    order_date - 30 AS month_ago,
    DATEADD(MONTH, 1, order_date) AS next_month,
    DATEDIFF(DAY, order_date, CURRENT_DATE) AS days_ago
FROM orders;

-- Extract date parts
SELECT 
    EXTRACT(YEAR FROM order_date) AS order_year,
    EXTRACT(MONTH FROM order_date) AS order_month,
    EXTRACT(DAY FROM order_date) AS order_day
FROM orders;

Aggregation

Basic Aggregates

-- Count rows
SELECT COUNT(*) FROM customers;
SELECT COUNT(DISTINCT category) FROM products;

-- Sum, Average, Min, Max
SELECT 
    COUNT(*) AS total_products,
    SUM(stock_quantity) AS total_stock,
    AVG(price) AS average_price,
    MIN(price) AS lowest_price,
    MAX(price) AS highest_price
FROM products;

GROUP BY

-- Group by single column
SELECT 
    category,
    COUNT(*) AS product_count,
    AVG(price) AS avg_price
FROM products
GROUP BY category;

-- Group by multiple columns
SELECT 
    EXTRACT(YEAR FROM order_date) AS year,
    EXTRACT(MONTH FROM order_date) AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_sales
FROM orders
GROUP BY 
    EXTRACT(YEAR FROM order_date),
    EXTRACT(MONTH FROM order_date)
ORDER BY year, month;

-- HAVING clause
SELECT 
    customer_id,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5
ORDER BY total_spent DESC;

Basic Joins

INNER JOIN

-- Simple inner join
SELECT 
    c.first_name,
    c.last_name,
    o.order_id,
    o.order_date,
    o.total_amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

-- Multiple joins
SELECT 
    c.first_name,
    o.order_id,
    p.product_name,
    od.quantity,
    od.unit_price
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_details od ON o.order_id = od.order_id
INNER JOIN products p ON od.product_id = p.product_id;

LEFT JOIN

-- Show all customers, even those without orders
SELECT 
    c.first_name,
    c.last_name,
    COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name;

Subqueries

Subqueries in WHERE

-- Find customers with above-average orders
SELECT * FROM customers
WHERE customer_id IN (
    SELECT customer_id 
    FROM orders 
    GROUP BY customer_id 
    HAVING SUM(total_amount) > (
        SELECT AVG(total_amount) FROM orders
    )
);

-- Using EXISTS
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.customer_id 
      AND o.total_amount > 1000
);

Subqueries in SELECT

SELECT 
    p.product_name,
    p.price,
    (SELECT AVG(price) FROM products) AS avg_price,
    p.price - (SELECT AVG(price) FROM products) AS price_diff
FROM products p;

Common Table Expressions (CTEs)

-- Simple CTE
WITH high_value_orders AS (
    SELECT * FROM orders 
    WHERE total_amount > 500
)
SELECT 
    customer_id,
    COUNT(*) AS high_value_count
FROM high_value_orders
GROUP BY customer_id;

-- Multiple CTEs
WITH 
customer_totals AS (
    SELECT 
        customer_id,
        SUM(total_amount) AS total_spent
    FROM orders
    GROUP BY customer_id
),
avg_spending AS (
    SELECT AVG(total_spent) AS avg_total
    FROM customer_totals
)
SELECT 
    c.first_name,
    c.last_name,
    ct.total_spent,
    a.avg_total,
    ct.total_spent - a.avg_total AS difference
FROM customers c
JOIN customer_totals ct ON c.customer_id = ct.customer_id
CROSS JOIN avg_spending a
WHERE ct.total_spent > a.avg_total;

String Operations

-- Concatenation
SELECT first_name || ' ' || last_name AS full_name
FROM customers;

-- String functions
SELECT 
    UPPER(first_name) AS upper_name,
    LOWER(email) AS lower_email,
    CHAR_LENGTH(last_name) AS name_length,
    SUBSTRING(email FROM 1 FOR POSITION('@' IN email) - 1) AS email_user,
    TRIM(LEADING ' ' FROM '  Hello  ') AS trimmed,
    REPLACE(phone, '-', '') AS phone_digits
FROM customers;

Best Practices for Beginners

Always use explicit column names instead of SELECT *
Use meaningful aliases for better readability
Test DELETE and UPDATE with SELECT first
Use transactions for multiple related operations
Create indexes on columns used in WHERE and JOIN
Validate data with CHECK constraints
Use appropriate data types to save space and improve performance

Common Mistakes to Avoid

Forgetting WHERE in UPDATE/DELETE - can affect all rows!
Not handling NULL values properly
Using the wrong JOIN type - understand the difference
Ignoring case sensitivity in string comparisons
Not using parameters in application code (SQL injection risk)

Next Steps

Learn about transactions and isolation levels
Explore stored procedures and triggers
Understand indexes and query optimization
Study advanced features like window functions
Practice with real-world scenarios

Basic Operations

Basic SQL Operations in Firebird

This guide covers fundamental SQL operations for working with Firebird databases, perfect for beginners getting started with database management.

Connecting to Firebird

Using ISQL

# Connect to a local database
isql employee.fdb -user SYSDBA -password masterkey

# Connect to a remote database
isql server:/path/to/database.fdb -user username -password password

Creating Tables

Basic Table Creation

CREATE TABLE customers (
    customer_id INTEGER NOT NULL PRIMARY KEY,
    first_name VARCHAR(30) NOT NULL,
    last_name VARCHAR(30) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20),
    created_date DATE DEFAULT CURRENT_DATE
);

CREATE TABLE products (
    product_id INTEGER NOT NULL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    description BLOB SUB_TYPE 1,
    price NUMERIC(10,2) NOT NULL CHECK (price > 0),
    stock_quantity INTEGER DEFAULT 0,
    category VARCHAR(50)
);

CREATE TABLE orders (
    order_id INTEGER NOT NULL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount NUMERIC(10,2),
    status VARCHAR(20) DEFAULT 'PENDING',
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Using Auto-increment (Identity)

-- Firebird 3.0+ syntax
CREATE TABLE employees (
    emp_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    emp_name VARCHAR(50) NOT NULL,
    hire_date DATE DEFAULT CURRENT_DATE
);

-- Pre-3.0 method using generators
CREATE GENERATOR gen_employee_id;

CREATE TABLE employees_old (
    emp_id INTEGER NOT NULL PRIMARY KEY,
    emp_name VARCHAR(50) NOT NULL
);

-- Create trigger for auto-increment
SET TERM ^ ;
CREATE TRIGGER employees_bi FOR employees_old
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    IF (NEW.emp_id IS NULL) THEN
        NEW.emp_id = GEN_ID(gen_employee_id, 1);
END^
SET TERM ; ^

Inserting Data

Basic INSERT

-- Single row insert
INSERT INTO customers (customer_id, first_name, last_name, email)
VALUES (1, 'John', 'Doe', '[email protected]');

-- Multiple rows insert (Firebird 2.0+)
INSERT INTO products (product_id, product_name, price, stock_quantity)
VALUES 
    (1, 'Laptop', 999.99, 10),
    (2, 'Mouse', 19.99, 50),
    (3, 'Keyboard', 49.99, 30);

-- Insert with returning
INSERT INTO customers (first_name, last_name, email)
VALUES ('Jane', 'Smith', '[email protected]')
RETURNING customer_id;

INSERT from SELECT

-- Copy data from one table to another
INSERT INTO customers_archive
SELECT * FROM customers WHERE created_date < '2020-01-01';

-- Insert with transformation
INSERT INTO order_summary (customer_id, total_orders, total_amount)
SELECT 
    customer_id,
    COUNT(*),
    SUM(total_amount)
FROM orders
GROUP BY customer_id;

Querying Data

Basic SELECT

-- Select all columns
SELECT * FROM customers;

-- Select specific columns
SELECT first_name, last_name, email 
FROM customers;

-- With WHERE clause
SELECT * FROM products 
WHERE price < 100 AND stock_quantity > 0;

-- Using aliases
SELECT 
    c.first_name || ' ' || c.last_name AS full_name,
    c.email AS contact_email
FROM customers c;

Filtering Data

-- Comparison operators
SELECT * FROM products WHERE price >= 50 AND price <= 200;
SELECT * FROM products WHERE price BETWEEN 50 AND 200;

-- IN operator
SELECT * FROM orders 
WHERE status IN ('PENDING', 'PROCESSING', 'SHIPPED');

-- LIKE operator for pattern matching
SELECT * FROM customers 
WHERE email LIKE '%@gmail.com';

SELECT * FROM products 
WHERE product_name LIKE 'Lap%';  -- Starts with 'Lap'

-- NULL handling
SELECT * FROM customers 
WHERE phone IS NULL;

SELECT * FROM customers 
WHERE phone IS NOT NULL;

Sorting and Limiting

-- ORDER BY
SELECT * FROM products 
ORDER BY price DESC;

SELECT * FROM customers 
ORDER BY last_name, first_name;

-- FIRST/SKIP (Firebird's LIMIT/OFFSET)
SELECT FIRST 10 * FROM products 
ORDER BY price DESC;

SELECT FIRST 10 SKIP 20 * FROM products 
ORDER BY product_name;

-- Using ROWS (Firebird 3.0+)
SELECT * FROM products 
ORDER BY price DESC
ROWS 1 TO 10;

Updating Data

Basic UPDATE

-- Update single column
UPDATE products 
SET price = price * 1.1 
WHERE category = 'Electronics';

-- Update multiple columns
UPDATE customers 
SET 
    email = '[email protected]',
    phone = '123-456-7890'
WHERE customer_id = 1;

-- Update with returning
UPDATE products 
SET stock_quantity = stock_quantity - 1
WHERE product_id = 1
RETURNING product_name, stock_quantity;

UPDATE with JOIN

-- Update using data from another table
UPDATE orders o
SET o.total_amount = (
    SELECT SUM(od.quantity * p.price)
    FROM order_details od
    JOIN products p ON od.product_id = p.product_id
    WHERE od.order_id = o.order_id
)
WHERE EXISTS (
    SELECT 1 FROM order_details 
    WHERE order_id = o.order_id
);

Deleting Data

Basic DELETE

-- Delete specific rows
DELETE FROM customers 
WHERE customer_id = 100;

-- Delete with condition
DELETE FROM orders 
WHERE order_date < DATEADD(-1 YEAR TO CURRENT_DATE)
  AND status = 'CANCELLED';

-- Delete with returning
DELETE FROM products 
WHERE stock_quantity = 0
RETURNING product_id, product_name;

-- Delete all rows (use with caution!)
DELETE FROM temp_table;

Working with Dates

-- Current date and time
SELECT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP FROM RDB$DATABASE;

-- Date arithmetic
SELECT 
    order_date,
    order_date + 7 AS week_later,
    order_date - 30 AS month_ago,
    DATEADD(MONTH, 1, order_date) AS next_month,
    DATEDIFF(DAY, order_date, CURRENT_DATE) AS days_ago
FROM orders;

-- Extract date parts
SELECT 
    EXTRACT(YEAR FROM order_date) AS order_year,
    EXTRACT(MONTH FROM order_date) AS order_month,
    EXTRACT(DAY FROM order_date) AS order_day
FROM orders;

Aggregation

Basic Aggregates

-- Count rows
SELECT COUNT(*) FROM customers;
SELECT COUNT(DISTINCT category) FROM products;

-- Sum, Average, Min, Max
SELECT 
    COUNT(*) AS total_products,
    SUM(stock_quantity) AS total_stock,
    AVG(price) AS average_price,
    MIN(price) AS lowest_price,
    MAX(price) AS highest_price
FROM products;

GROUP BY

-- Group by single column
SELECT 
    category,
    COUNT(*) AS product_count,
    AVG(price) AS avg_price
FROM products
GROUP BY category;

-- Group by multiple columns
SELECT 
    EXTRACT(YEAR FROM order_date) AS year,
    EXTRACT(MONTH FROM order_date) AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_sales
FROM orders
GROUP BY 
    EXTRACT(YEAR FROM order_date),
    EXTRACT(MONTH FROM order_date)
ORDER BY year, month;

-- HAVING clause
SELECT 
    customer_id,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5
ORDER BY total_spent DESC;

Basic Joins

INNER JOIN

-- Simple inner join
SELECT 
    c.first_name,
    c.last_name,
    o.order_id,
    o.order_date,
    o.total_amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

-- Multiple joins
SELECT 
    c.first_name,
    o.order_id,
    p.product_name,
    od.quantity,
    od.unit_price
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_details od ON o.order_id = od.order_id
INNER JOIN products p ON od.product_id = p.product_id;

LEFT JOIN

-- Show all customers, even those without orders
SELECT 
    c.first_name,
    c.last_name,
    COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name;

Subqueries

Subqueries in WHERE

-- Find customers with above-average orders
SELECT * FROM customers
WHERE customer_id IN (
    SELECT customer_id 
    FROM orders 
    GROUP BY customer_id 
    HAVING SUM(total_amount) > (
        SELECT AVG(total_amount) FROM orders
    )
);

-- Using EXISTS
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.customer_id 
      AND o.total_amount > 1000
);

Subqueries in SELECT

SELECT 
    p.product_name,
    p.price,
    (SELECT AVG(price) FROM products) AS avg_price,
    p.price - (SELECT AVG(price) FROM products) AS price_diff
FROM products p;

Common Table Expressions (CTEs)

-- Simple CTE
WITH high_value_orders AS (
    SELECT * FROM orders 
    WHERE total_amount > 500
)
SELECT 
    customer_id,
    COUNT(*) AS high_value_count
FROM high_value_orders
GROUP BY customer_id;

-- Multiple CTEs
WITH 
customer_totals AS (
    SELECT 
        customer_id,
        SUM(total_amount) AS total_spent
    FROM orders
    GROUP BY customer_id
),
avg_spending AS (
    SELECT AVG(total_spent) AS avg_total
    FROM customer_totals
)
SELECT 
    c.first_name,
    c.last_name,
    ct.total_spent,
    a.avg_total,
    ct.total_spent - a.avg_total AS difference
FROM customers c
JOIN customer_totals ct ON c.customer_id = ct.customer_id
CROSS JOIN avg_spending a
WHERE ct.total_spent > a.avg_total;

String Operations

-- Concatenation
SELECT first_name || ' ' || last_name AS full_name
FROM customers;

-- String functions
SELECT 
    UPPER(first_name) AS upper_name,
    LOWER(email) AS lower_email,
    CHAR_LENGTH(last_name) AS name_length,
    SUBSTRING(email FROM 1 FOR POSITION('@' IN email) - 1) AS email_user,
    TRIM(LEADING ' ' FROM '  Hello  ') AS trimmed,
    REPLACE(phone, '-', '') AS phone_digits
FROM customers;

Best Practices for Beginners

Always use explicit column names instead of SELECT *
Use meaningful aliases for better readability
Test DELETE and UPDATE with SELECT first
Use transactions for multiple related operations
Create indexes on columns used in WHERE and JOIN
Validate data with CHECK constraints
Use appropriate data types to save space and improve performance

Common Mistakes to Avoid

Forgetting WHERE in UPDATE/DELETE - can affect all rows!
Not handling NULL values properly
Using the wrong JOIN type - understand the difference
Ignoring case sensitivity in string comparisons
Not using parameters in application code (SQL injection risk)

Next Steps

Learn about transactions and isolation levels
Explore stored procedures and triggers
Understand indexes and query optimization
Study advanced features like window functions
Practice with real-world scenarios