Write, Run & Share Firebird queries online using OneCompiler's Firebird online editor for free. It's one of the robust, feature-rich online editors for Firebird. Getting started with the OneCompiler's Firebird editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose language as 'Firebird' and start writing queries to learn and test online without worrying about tedious process of installation.
Firebird is a free and open-source relational database management system that runs on Linux, Windows, macOS, and several Unix platforms. Originally forked from Borland InterBase, Firebird is known for its small footprint, excellent performance, and powerful SQL support. It's well-suited for embedded applications, small to medium businesses, and as a backend for multi-tier applications.
Creates a new table with specified columns, data types, and constraints. Firebird supports standard SQL data types including INTEGER, VARCHAR, DECIMAL, DATE, TIMESTAMP, and BLOB. Use NOT NULL for required fields and PRIMARY KEY for unique identifiers. Firebird enforces referential integrity with FOREIGN KEY constraints.
CREATE TABLE EMPLOYEE (
empId INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
dept VARCHAR(20),
salary DECIMAL(10,2),
hire_date DATE
);
-- Table with foreign key
CREATE TABLE orders (
order_id INTEGER NOT NULL PRIMARY KEY,
emp_id INTEGER REFERENCES EMPLOYEE(empId),
amount DECIMAL(10,2),
order_date DATE DEFAULT CURRENT_DATE
);
Adds new rows to a table. Specify column names and values explicitly for clarity, or provide values for all columns in order. Firebird supports INSERT with DEFAULT VALUES for creating rows with all default values. Batch inserts can be done with multiple INSERT statements.
INSERT INTO EMPLOYEE (empId, name, dept, salary) VALUES (1, 'Clark', 'Sales', 50000);
INSERT INTO EMPLOYEE (empId, name, dept, salary) VALUES (2, 'Dave', 'Accounting', 45000);
INSERT INTO EMPLOYEE (empId, name, dept, salary) VALUES (3, 'Ava', 'Sales', 55000);
-- Insert with defaults for missing columns
INSERT INTO EMPLOYEE (empId, name) VALUES (4, 'Bob');
-- Insert from select
INSERT INTO employee_backup SELECT * FROM EMPLOYEE;
Retrieves data from tables with filtering, sorting, and limiting capabilities. Firebird uses FIRST and SKIP for limiting results (similar to LIMIT and OFFSET in other databases). The SELECT statement supports all standard SQL features including joins, subqueries, and aggregate functions.
-- Select all
SELECT * FROM EMPLOYEE;
-- Select with condition
SELECT * FROM EMPLOYEE WHERE dept = 'Sales';
-- Select specific columns
SELECT name, salary FROM EMPLOYEE ORDER BY salary DESC;
-- Limit results (Firebird syntax)
SELECT FIRST 10 * FROM EMPLOYEE;
-- Skip and limit
SELECT FIRST 10 SKIP 5 * FROM EMPLOYEE;
-- Select with multiple conditions
SELECT * FROM EMPLOYEE WHERE dept = 'Sales' AND salary > 50000;
Modifies existing rows in a table. Always use a WHERE clause to target specific rows - without it, all rows will be updated. Firebird supports updating multiple columns in a single statement and can use subqueries to compute new values. Use transactions for safe updates.
-- Update single column
UPDATE EMPLOYEE SET dept = 'Marketing' WHERE empId = 1;
-- Update multiple columns
UPDATE EMPLOYEE SET dept = 'Sales', salary = 52000 WHERE empId = 2;
-- Update with calculation
UPDATE EMPLOYEE SET salary = salary * 1.10 WHERE dept = 'Engineering';
-- Update with subquery
UPDATE EMPLOYEE SET salary = (SELECT AVG(salary) FROM EMPLOYEE)
WHERE empId = 3;
Removes rows from a table based on conditions. Always include a WHERE clause to avoid deleting all data. For removing all rows while keeping the table structure, use DELETE without WHERE or recreate the table. Firebird supports DELETE with subqueries for complex conditions.
-- Delete specific rows
DELETE FROM EMPLOYEE WHERE empId = 1;
-- Delete with condition
DELETE FROM EMPLOYEE WHERE dept = 'Sales' AND salary < 45000;
-- Delete with subquery
DELETE FROM EMPLOYEE
WHERE salary < (SELECT AVG(salary) FROM EMPLOYEE);
-- Delete all rows
DELETE FROM EMPLOYEE;
Combines rows from multiple tables based on related columns. Firebird supports INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN. Use table aliases for cleaner queries when joining multiple tables. Join conditions are specified in the ON clause.
CREATE TABLE departments (
dept_id INTEGER NOT NULL PRIMARY KEY,
dept_name VARCHAR(30)
);
CREATE TABLE employees (
emp_id INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(30),
dept_id INTEGER
);
INSERT INTO departments VALUES (1, 'Sales'), (2, 'Engineering');
INSERT INTO employees VALUES (1, 'Alice', 1), (2, 'Bob', 2), (3, 'Charlie', 1);
-- Inner join
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;
-- Left join
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;
Firebird supports standard aggregate functions for data analysis including COUNT, SUM, AVG, MIN, and MAX. Use GROUP BY to aggregate by categories and HAVING to filter grouped results. Aggregate functions can be combined with CASE for conditional aggregation.
-- Basic aggregates
SELECT
COUNT(*) as total_employees,
SUM(salary) as total_salary,
AVG(salary) as avg_salary,
MIN(salary) as min_salary,
MAX(salary) as max_salary
FROM EMPLOYEE;
-- Group by
SELECT dept, COUNT(*) as emp_count, AVG(salary) as avg_salary
FROM EMPLOYEE
GROUP BY dept;
-- Having clause
SELECT dept, COUNT(*) as emp_count
FROM EMPLOYEE
GROUP BY dept
HAVING COUNT(*) > 1;
Firebird supports subqueries in SELECT, FROM, WHERE, and HAVING clauses. Subqueries can return single values, lists, or entire result sets. Use correlated subqueries when the inner query references the outer query. EXISTS and IN are commonly used with subqueries.
-- Subquery in WHERE
SELECT * FROM EMPLOYEE
WHERE salary > (SELECT AVG(salary) FROM EMPLOYEE);
-- Subquery with IN
SELECT * FROM EMPLOYEE
WHERE dept IN (SELECT dept FROM EMPLOYEE WHERE salary > 50000);
-- EXISTS subquery
SELECT * FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id);
Provides conditional logic in SQL queries. Use CASE for creating computed columns, categorizing data, or handling different conditions. Firebird supports both simple CASE (comparing values) and searched CASE (evaluating conditions). CASE can be used in SELECT, WHERE, and ORDER BY clauses.
SELECT name, salary,
CASE
WHEN salary >= 55000 THEN 'Senior'
WHEN salary >= 45000 THEN 'Mid-Level'
ELSE 'Junior'
END as level
FROM EMPLOYEE;
-- Simple CASE
SELECT name,
CASE dept
WHEN 'Sales' THEN 'Revenue Team'
WHEN 'Engineering' THEN 'Tech Team'
ELSE 'Other'
END as team
FROM EMPLOYEE;
Firebird provides string functions for text manipulation. Common functions include UPPER, LOWER, SUBSTRING, TRIM, CHAR_LENGTH, and concatenation using ||. These functions are useful for data cleaning, formatting, and string-based searching.
SELECT
UPPER('hello') as upper_case,
LOWER('HELLO') as lower_case,
CHAR_LENGTH('hello') as length,
'Hello' || ' ' || 'World' as combined,
SUBSTRING('Hello World' FROM 1 FOR 5) as sub,
TRIM(' hello ') as trimmed;
-- Pattern matching
SELECT * FROM EMPLOYEE WHERE name LIKE 'A%';
SELECT * FROM EMPLOYEE WHERE name STARTING WITH 'A';
Firebird has built-in date and time functions for working with temporal data. Use CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP for current values. EXTRACT retrieves components like year, month, and day. Date arithmetic uses DATEADD and DATEDIFF functions.
SELECT
CURRENT_DATE as today,
CURRENT_TIMESTAMP as now,
DATEADD(DAY, 7, CURRENT_DATE) as next_week,
DATEADD(MONTH, -1, CURRENT_DATE) as last_month,
EXTRACT(YEAR FROM CURRENT_DATE) as year,
EXTRACT(MONTH FROM CURRENT_DATE) as month,
EXTRACT(DAY FROM CURRENT_DATE) as day;
-- Date difference
SELECT DATEDIFF(DAY, hire_date, CURRENT_DATE) as days_employed
FROM EMPLOYEE;
A view is a virtual table based on a SELECT query. Views simplify complex queries, provide data abstraction, and can restrict access to specific columns or rows. Firebird supports updatable views for simple cases where the view maps directly to a single table.
-- Create view
CREATE VIEW employee_summary AS
SELECT dept, COUNT(*) as emp_count, AVG(salary) as avg_salary
FROM EMPLOYEE
GROUP BY dept;
-- Query the view
SELECT * FROM employee_summary WHERE emp_count > 1;
-- Drop view
DROP VIEW employee_summary;
ALTER TABLE modifies existing table structure - add, modify, or drop columns and constraints. DROP TABLE removes the table and all its data permanently. Always backup data before making structural changes. Firebird supports IF EXISTS for conditional drops.
-- Add column
ALTER TABLE EMPLOYEE ADD email VARCHAR(100);
-- Drop column
ALTER TABLE EMPLOYEE DROP email;
-- Add constraint
ALTER TABLE EMPLOYEE ADD CONSTRAINT chk_salary CHECK (salary > 0);
-- Drop table
DROP TABLE EMPLOYEE;
-- Recreate table (drop if exists pattern)
DROP TABLE EMPLOYEE;
CREATE TABLE EMPLOYEE (empId INTEGER NOT NULL PRIMARY KEY, name VARCHAR(50));
