Welcome to the world of SQL queries in HarborDB! This guide will teach you the fundamentals of writing, executing, and managing SQL queries using HarborDB's powerful query editor.

Introduction to SQL in HarborDB

HarborDB's query editor is designed to make writing and executing SQL queries intuitive and efficient. Whether you're retrieving data, updating records, or creating database objects, HarborDB provides a streamlined workflow with features like syntax highlighting, auto-completion, and real-time results.

Key Features of the Query Editor

Syntax Highlighting: SQL keywords, functions, and literals are color-coded for readability
Auto-completion: Table names, column names, and SQL keywords are suggested as you type
Multiple Tabs: Work with several queries simultaneously in separate tabs
Results Pane: View query results immediately below your editor
Export Options: Export results to CSV or JSON with one click
Query History: Access recently executed queries
Bookmarks: Save frequently used queries for quick access

Writing Your First Query

Basic SELECT Statement

The most fundamental SQL query retrieves data from a table:

SELECT * FROM users;

This query:

SELECT *: Returns all columns from the table
FROM users: Specifies the table to query
;: Terminates the statement (optional in HarborDB)

Selecting Specific Columns

To retrieve only specific columns:

SELECT first_name, last_name, email FROM users;

Limiting Results

When working with large tables, it's good practice to limit results:

SELECT * FROM orders LIMIT 10;

The Query Editor Interface

Main Components

Editor Area: Write your SQL queries here
Toolbar: Execute, cancel, format, and export controls
Results Pane: Displays query results and execution information
Status Bar: Shows connection info, row count, and execution time

Keyboard Shortcuts

| Shortcut | Action | | ------------- | ------------------ | | ⌘ + R | Execute query | | ⌘ + . | Cancel query | | ⌘ + T | New query tab | | ⌘ + W | Close current tab | | ⌘ + S | Save query | | ⌘ + F | Find in query | | ⌘ + / | Toggle comment | | Tab | Indent selection | | Shift + Tab | Unindent selection |

Executing Queries

Step-by-Step Execution

Write your query in the editor
Click the Run button (▶) or press ⌘ + R
View results in the results pane
Check execution info in the status bar

Partial Query Execution

You can execute only part of a query:

Select the text you want to execute
Right-click and choose "Execute Selection"
Or use the keyboard shortcut ⌘ + Shift + R

Execution Results

After running a query, you'll see:

Results Grid: Data displayed in a sortable, filterable table
Row Count: Total number of rows returned
Execution Time: How long the query took to execute
Messages: Any messages from PostgreSQL (especially for DML queries)

Filtering Data with WHERE

Basic WHERE Clause

SELECT * FROM products WHERE price > 100;

Multiple Conditions

SELECT * FROM orders
WHERE status = 'shipped'
  AND order_date >= '2024-01-01';

Pattern Matching with LIKE

SELECT * FROM customers
WHERE email LIKE '%@gmail.com';

SELECT * FROM products
WHERE name LIKE 'Pro%';

NULL Values

-- Find rows where email is NULL
SELECT * FROM users WHERE email IS NULL;

-- Find rows where email is NOT NULL
SELECT * FROM users WHERE email IS NOT NULL;

Sorting Results with ORDER BY

Ascending Order (Default)

SELECT * FROM products ORDER BY price;

Descending Order

SELECT * FROM products ORDER BY price DESC;

Multiple Sort Columns

SELECT * FROM employees
ORDER BY department, last_name, first_name;

Working with Results

Viewing Results

Scroll: Navigate through results with mouse or keyboard
Sort: Click column headers to sort ascending/descending
Filter: Right-click column headers to filter values
Resize: Drag column borders to adjust width
Reorder: Drag column headers to rearrange columns

Cell Operations

Copy Cell: Double-click a cell or press ⌘ + C
Edit Cell: Double-click and edit (for local changes only)
View Full Content: Hover over truncated cells to see tooltip

Exporting Results

Click the Export button in the toolbar
Choose format: CSV or JSON
Select destination: Save to file or copy to clipboard
Configure options: Delimiters, encoding, headers

Saving and Organizing Queries

Saving Queries

Click Save (⌘ + S) or the save icon
Choose location: Local file or HarborDB library
Add metadata: Name, description, tags

Query Library

Organize saved queries in folders:

Favorites: Star frequently used queries
Recent: Automatically track recently used queries
Tags: Categorize queries with custom tags

Query Templates

Create reusable templates for common queries:

-- Template: Find inactive users
SELECT user_id, email, last_login
FROM users
WHERE last_login < CURRENT_DATE - INTERVAL '90 days'
  AND active = true;

Common Query Patterns

Counting Records

SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT country) FROM customers;

Aggregating Data

SELECT
  category,
  COUNT(*) as product_count,
  AVG(price) as avg_price,
  MIN(price) as min_price,
  MAX(price) as max_price
FROM products
GROUP BY category;

Date/Time Queries

-- Today's orders
SELECT * FROM orders WHERE order_date = CURRENT_DATE;

-- Last 7 days
SELECT * FROM logs
WHERE log_date >= CURRENT_DATE - INTERVAL '7 days';

-- By month
SELECT
  EXTRACT(MONTH FROM order_date) as month,
  COUNT(*) as order_count
FROM orders
GROUP BY EXTRACT(MONTH FROM order_date)
ORDER BY month;

Joining Tables

SELECT
  o.order_id,
  o.order_date,
  c.first_name,
  c.last_name,
  c.email
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.status = 'completed';

Best Practices

1. Start with SELECT *

When exploring a new table, use SELECT * to see all columns, then refine to specific columns.

2. Use LIMIT

Always use LIMIT when querying unfamiliar tables to avoid overwhelming results.

3. Format Your Queries

Use consistent formatting for readability:

Keywords in uppercase
Indentation for complex queries
Line breaks for long clauses

4. Comment Your Code

Add comments for complex queries:

-- Calculate monthly revenue by product category
-- Excluding refunded orders and test accounts
SELECT
  DATE_TRUNC('month', order_date) as month,
  category,
  SUM(amount) as total_revenue
FROM orders
WHERE status != 'refunded'
  AND customer_id NOT IN (SELECT customer_id FROM test_accounts)
GROUP BY DATE_TRUNC('month', order_date), category
ORDER BY month DESC, total_revenue DESC;

5. Test with WHERE 1=0

To see column structure without fetching data:

SELECT * FROM large_table WHERE 1=0;

Troubleshooting Common Issues

"Relation does not exist"

Check table name spelling and case sensitivity
Verify you're connected to the correct database
Ensure you have appropriate permissions

"Column does not exist"

Verify column names in the SELECT clause
Check for typos
Use SELECT * to see all available columns first

Slow Queries

Add LIMIT to test performance
Check for missing indexes
Use EXPLAIN to analyze query plan
Consider server-side performance factors

Connection Errors

Verify connection is active
Check network connectivity
Confirm PostgreSQL server is running

Next Steps

Now that you understand query basics:

Practice with your own database tables
Explore the Advanced Query Techniques guide
Learn about Exporting Data
Try Performance Optimization techniques

Quick Reference

Essential SQL Clauses

SELECT - Choose columns to return
FROM - Specify table to query
WHERE - Filter rows
ORDER BY - Sort results
LIMIT - Restrict number of rows
GROUP BY - Aggregate data
HAVING - Filter aggregated data

Common Functions

COUNT() - Count rows
SUM() - Sum values
AVG() - Average values
MIN()/MAX() - Find extremes
COALESCE() - Handle NULL values
CONCAT() - Combine strings
EXTRACT() - Get date parts

Query Basics