SQL Commands

Common SQL commands in the DuckDB CLI

The DuckDB CLI supports standard SQL commands for data definition, manipulation, and querying.

Data Definition Language (DDL)

CREATE TABLE

Create a new table:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name VARCHAR NOT NULL,
    department VARCHAR,
    salary DECIMAL(10,2),
    hire_date DATE
);

Create a table from a query:

CREATE TABLE high_earners AS 
SELECT * FROM employees 
WHERE salary > 100000;

CREATE VIEW

Create a view:

CREATE VIEW employee_summary AS
SELECT department, COUNT(*) as emp_count, AVG(salary) as avg_salary
FROM employees
GROUP BY department;

CREATE INDEX

Create an index:

CREATE INDEX idx_department ON employees(department);

DROP TABLE

Drop a table:

DROP TABLE employees;

Drop if exists:

DROP TABLE IF EXISTS employees;

ALTER TABLE

Add a column:

ALTER TABLE employees ADD COLUMN email VARCHAR;

Drop a column:

ALTER TABLE employees DROP COLUMN email;

Data Manipulation Language (DML)

INSERT

Insert single row:

INSERT INTO employees VALUES (1, 'Alice Smith', 'Engineering', 95000, '2020-01-15');

Insert multiple rows:

INSERT INTO employees VALUES 
    (2, 'Bob Johnson', 'Sales', 75000, '2019-03-20'),
    (3, 'Carol Williams', 'Engineering', 105000, '2018-07-10'),
    (4, 'David Brown', 'Marketing', 68000, '2021-02-01');

Insert from a query:

INSERT INTO high_earners 
SELECT * FROM employees WHERE salary > 100000;

UPDATE

Update rows:

UPDATE employees 
SET salary = salary * 1.1 
WHERE department = 'Engineering';

Update with conditions:

UPDATE employees 
SET department = 'Technology' 
WHERE department = 'Engineering' AND hire_date < '2020-01-01';

DELETE

Delete rows:

DELETE FROM employees WHERE hire_date < '2015-01-01';

Delete all rows:

DELETE FROM employees;

Data Query Language (DQL)

SELECT

Basic select:

SELECT * FROM employees;

Select specific columns:

SELECT name, department, salary FROM employees;

With WHERE clause:

SELECT name, salary 
FROM employees 
WHERE department = 'Engineering' AND salary > 90000;

Aggregations

Group by with aggregates:

SELECT 
    department,
    COUNT(*) as employee_count,
    AVG(salary) as avg_salary,
    MAX(salary) as max_salary,
    MIN(salary) as min_salary
FROM employees
GROUP BY department;

Having clause:

SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 80000;

Joins

Inner join:

SELECT e.name, e.salary, d.department_name
FROM employees e
INNER JOIN departments d ON e.department = d.dept_code;

Left join:

SELECT e.name, e.salary, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department = d.dept_code;

Order By

Order results:

SELECT name, salary 
FROM employees 
ORDER BY salary DESC;

Multiple columns:

SELECT name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

Limit

Limit results:

SELECT name, salary 
FROM employees 
ORDER BY salary DESC 
LIMIT 10;

With offset:

SELECT name, salary 
FROM employees 
ORDER BY salary DESC 
LIMIT 10 OFFSET 20;

Common Table Expressions (CTEs)

Use WITH for CTEs:

WITH high_earners AS (
    SELECT * FROM employees WHERE salary > 100000
),
average_by_dept AS (
    SELECT department, AVG(salary) as avg_sal
    FROM employees
    GROUP BY department
)
SELECT h.name, h.salary, a.avg_sal
FROM high_earners h
JOIN average_by_dept a ON h.department = a.department;

Window Functions

Ranking:

SELECT 
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;

Running totals:

SELECT 
    name,
    salary,
    SUM(salary) OVER (ORDER BY hire_date) as running_total
FROM employees;

Transaction Commands

BEGIN TRANSACTION

Start a transaction:

BEGIN TRANSACTION;

or simply:

BEGIN;

COMMIT

Commit changes:

COMMIT;

ROLLBACK

Rollback changes:

ROLLBACK;

Transaction Example

BEGIN TRANSACTION;

UPDATE employees SET salary = salary * 1.1 WHERE department = 'Engineering';
INSERT INTO salary_changes VALUES ('2024-01-01', 'Engineering raise', 10.0);

COMMIT;

Rollback on error:

BEGIN TRANSACTION;

UPDATE employees SET salary = salary * 1.1 WHERE department = 'Engineering';
-- Oops, made a mistake
ROLLBACK;

Utility Commands

DESCRIBE

Show table structure:

DESCRIBE employees;

SHOW TABLES

List all tables:

SHOW TABLES;

EXPLAIN

Show query plan:

EXPLAIN SELECT * FROM employees WHERE salary > 100000;

Detailed analysis:

EXPLAIN ANALYZE SELECT * FROM employees WHERE salary > 100000;