SQL Online Editor

Query Results 

# Ready to execute SQL queries...
# Write your queries above and click "Run"

Online SQL Editor - Write, Execute & Test SQL Queries Instantly

Our online SQL editor provides a powerful and convenient way to write, execute, and test SQL queries directly in your browser. Whether you're learning SQL, developing database applications, or need to quickly test a query, our editor offers a seamless experience with real-time results.

Why Use Our Online SQL Editor?

  • No installation required - Run SQL queries directly from your browser
  • Multiple database support - Works with MySQL, PostgreSQL, and SQLite syntax
  • Real-time results - See query output immediately in a formatted table
  • Sample database - Comes with pre-loaded tables for practice
  • User-friendly interface - Clean, intuitive design with SQL syntax highlighting
  • Free to use - No registration or subscription needed
  • Mobile-friendly - Write queries on the go from any device

Getting Started with SQL

SQL (Structured Query Language) is the standard language for managing and manipulating relational databases. It was first developed at IBM in the 1970s and has since become essential for working with data in applications ranging from small websites to enterprise systems.

Basic SQL Commands

SQL commands can be divided into several categories:

  • DDL (Data Definition Language): CREATE, ALTER, DROP
  • DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE
  • DCL (Data Control Language): GRANT, REVOKE
  • TCL (Transaction Control Language): COMMIT, ROLLBACK

Note: SQL is not case sensitive for keywords (SELECT, FROM, WHERE, etc.), but it's a common convention to write SQL keywords in uppercase for better readability.

SQL Query Structure

The most common SQL statement is the SELECT query, which retrieves data from database tables. A basic SELECT statement has this structure:

SELECT column1, column2, ...
FROM table_name
WHERE condition
GROUP BY column
HAVING group_condition
ORDER BY column
LIMIT number;

Example SELECT Query

SELECT name, salary
FROM employees
WHERE department = 'Engineering'
ORDER BY salary DESC
LIMIT 5;

Database Tables and Relationships

Relational databases store data in tables that can be related to each other. The main types of table relationships are:

  • One-to-One: Each record in Table A relates to one record in Table B
  • One-to-Many: Each record in Table A relates to multiple records in Table B
  • Many-to-Many: Multiple records in Table A relate to multiple records in Table B (requires a junction table)

Creating Tables with Relationships

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Tip: Primary keys uniquely identify each record in a table, while foreign keys establish relationships between tables by referencing the primary key of another table.

SQL Joins

JOIN operations combine rows from two or more tables based on related columns. The main types of joins are:

  • INNER JOIN: Returns records with matching values in both tables
  • LEFT JOIN: Returns all records from the left table and matched records from the right
  • RIGHT JOIN: Returns all records from the right table and matched records from the left
  • FULL JOIN: Returns all records when there's a match in either table

Join Examples

-- INNER JOIN (default)
SELECT e.name, d.name AS department
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

-- LEFT JOIN (all employees, even without department)
SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

-- RIGHT JOIN (all departments, even without employees)
SELECT e.name, d.name AS department
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

SQL Functions

SQL provides many built-in functions for working with data:

Aggregate Functions

SELECT 
    COUNT(*) AS total_employees,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary,
    SUM(salary) AS total_salary
FROM employees;

String Functions

SELECT 
    UPPER(name) AS uppercase_name,
    LOWER(name) AS lowercase_name,
    LENGTH(name) AS name_length,
    SUBSTRING(name, 1, 3) AS first_three_chars,
    CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

Date Functions

SELECT 
    name,
    hire_date,
    CURRENT_DATE AS today,
    DATE_PART('year', hire_date) AS hire_year,
    AGE(hire_date) AS time_with_company
FROM employees;

Grouping Data

The GROUP BY clause groups rows that have the same values into summary rows:

SELECT 
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 2
ORDER BY avg_salary DESC;

The HAVING clause filters groups after aggregation, similar to WHERE for individual rows.

Subqueries

Subqueries (nested queries) allow you to use the result of one query within another:

-- Subquery in WHERE clause
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Subquery in FROM clause
SELECT dept.name, emp_stats.avg_salary
FROM departments dept
JOIN (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) emp_stats ON dept.name = emp_stats.department;

Indexes and Performance

Indexes improve query performance by creating a quick lookup structure:

-- Create an index
CREATE INDEX idx_employee_name ON employees(name);

-- Composite index
CREATE INDEX idx_dept_salary ON employees(department, salary);

-- View query execution plan
EXPLAIN ANALYZE SELECT * FROM employees WHERE name = 'John Smith';

Common indexing strategies:

  • Index columns frequently used in WHERE clauses
  • Index columns used for JOIN conditions
  • Consider composite indexes for multiple columns often queried together
  • Avoid over-indexing as it slows down INSERT/UPDATE operations

Transactions

Transactions ensure database operations are completed as a single unit:

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- If both updates succeed
COMMIT;

-- If any operation fails
ROLLBACK;

Transactions follow ACID properties:

  • Atomicity: All operations succeed or none do
  • Consistency: Database remains in a valid state
  • Isolation: Concurrent transactions don't interfere
  • Durability: Committed transactions persist

Views and Stored Procedures

Views

Views are virtual tables based on SQL query results:

CREATE VIEW high_paid_employees AS
SELECT name, salary, department
FROM employees
WHERE salary > 70000;

-- Use the view like a table
SELECT * FROM high_paid_employees;

Stored Procedures

Stored procedures are reusable SQL code blocks:

CREATE PROCEDURE raise_salary(IN emp_id INT, IN raise_amount DECIMAL(10,2))
BEGIN
    UPDATE employees
    SET salary = salary + raise_amount
    WHERE id = emp_id;
END;

-- Execute the procedure
CALL raise_salary(101, 5000);

Note: Our online SQL editor supports basic SQL execution. For advanced features like stored procedures, you may need a full database management system like MySQL or PostgreSQL installed locally.

Database Normalization

Normalization organizes data to minimize redundancy. The main normal forms are:

  1. 1NF: Each table cell should contain a single value, and each record needs to be unique
  2. 2NF: Be in 1NF and have no partial dependencies (all non-key attributes depend on the entire primary key)
  3. 3NF: Be in 2NF and have no transitive dependencies (non-key attributes don't depend on other non-key attributes)

Example of Normalization

-- Before normalization (all in one table)
CREATE TABLE orders (
    order_id INT,
    customer_name VARCHAR(100),
    customer_email VARCHAR(100),
    product_name VARCHAR(100),
    product_price DECIMAL(10,2),
    quantity INT
);

-- After normalization (3NF)
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),
    order_date DATE
);

CREATE TABLE order_items (
    order_id INT REFERENCES orders(order_id),
    product_id INT REFERENCES products(product_id),
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

Frequently Asked Questions

What's the difference between SQL and MySQL?

SQL is the standard language for querying relational databases, while MySQL is a specific database management system that uses SQL. Other SQL-based databases include PostgreSQL, SQL Server, and Oracle.

How do I install a database to practice SQL?

You can install MySQL, PostgreSQL, or SQLite on your computer. Alternatively, use our online SQL editor to practice without installation. For local setup, MySQL Community Edition is free to download.

What's the difference between WHERE and HAVING?

WHERE filters rows before grouping, while HAVING filters groups after aggregation. Use WHERE for conditions on individual rows and HAVING for conditions on grouped data.

How do I optimize slow SQL queries?

Use EXPLAIN to analyze query execution, add appropriate indexes, avoid SELECT *, limit results with WHERE clauses, and consider query restructuring for complex operations.

What are SQL injections and how to prevent them?

SQL injection is a security vulnerability where attackers insert malicious SQL. Prevent it by using parameterized queries, prepared statements, and input validation.

What's the difference between DELETE, TRUNCATE, and DROP?

DELETE removes specific rows with WHERE clause and can be rolled back. TRUNCATE removes all rows quickly but can't be rolled back. DROP removes the entire table structure.

How do I backup and restore a database?

Most databases have export/import functions. For MySQL: mysqldump -u user -p database > backup.sql to backup and mysql -u user -p database < backup.sql to restore.

Advanced SQL Concepts

Once you've mastered the basics, explore these advanced topics:

  • Window Functions: Perform calculations across related rows
    • SELECT 
    name, 
    salary,
    department,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;
  • Common Table Expressions (CTEs): Temporary result sets for complex queries
    • WITH high_earners AS (
    SELECT * FROM employees WHERE salary > 70000
)
SELECT department, COUNT(*) 
FROM high_earners
GROUP BY department;
  • Recursive Queries: For hierarchical data (org charts, folders)
  • Pivoting Data: Transform rows into columns
  • Full-Text Search: Advanced text searching capabilities
  • JSON Support: Modern databases can store and query JSON data

Remember: Our online SQL editor is perfect for practicing all these concepts. Try writing queries for each topic to reinforce your learning!

SQL Best Practices

Follow these guidelines for writing efficient, maintainable SQL:

  • Use meaningful table and column names (avoid spaces/special characters)
  • Consistently format your SQL for readability
  • Use comments to document complex queries
  • Always specify columns in INSERT statements (don't rely on column order)
  • Use transactions for multiple related operations
  • Consider indexing strategies for performance
  • Validate and sanitize user input to prevent SQL injection
  • Use JOINs instead of subqueries when possible for better performance
  • Limit results with WHERE clauses before processing large datasets

Learning Resources

To continue your SQL learning journey, check out these resources:

  • Books: "SQL in 10 Minutes" by Ben Forta, "Learning SQL" by Alan Beaulieu
  • Online courses: SQLZoo, Mode Analytics SQL Tutorial, Khan Academy
  • Practice platforms: LeetCode SQL, HackerRank SQL, StrataScratch
  • Documentation: MySQL Documentation, PostgreSQL Documentation

Our online SQL editor is the perfect tool to practice what you learn from these resources. The more queries you write, the better you'll become!