sql-quick-guide

SQL: The Modern Quick Guide

SQL - Overview

Structured Query Language (SQL) is the standard language for managing and manipulating data in relational databases. From creating databases and tables to fetching and modifying data, SQL is the fundamental tool for data professionals. While SQL is an ANSI/ISO standard, most database systems have their own dialects with specific extensions.

What is SQL?

SQL is a declarative language designed for storing, manipulating, and retrieving data from relational databases. Unlike imperative languages (like Python or Java) where you describe how to do something, in SQL you declare what you want, and the database engine figures out the most efficient way to get it.

All major Relational Database Management Systems (RDBMS) use SQL as their core language. This includes systems like PostgreSQL, MySQL, SQL Server, Oracle, and SQLite.

Common SQL dialects include:

• T-SQL (Transact-SQL): Used by Microsoft SQL Server.
• PL/pgSQL (Procedural Language/PostgreSQL): Used by PostgreSQL.
• PL/SQL (Procedural Language/SQL): Used by Oracle.

Why is SQL Still Essential?

In an era of Big Data and NoSQL, SQL remains more relevant than ever due to its power, simplicity, and ubiquity.

• Universal Data Access: It’s the standard for interacting with structured data, a skill required in data analysis, data science, backend development, and database administration.
• Powerful Data Manipulation: Allows for complex data definition, querying, and modification with concise syntax.
• Foundation of Modern Data Stacks: Many modern data warehousing (e.g., Snowflake, BigQuery) and stream-processing systems (e.g., Flink) use SQL or SQL-like interfaces.
• Robust and Mature: Decades of development have made SQL databases reliable, secure, and highly performant.

The SQL Query Process

When you execute an SQL query, the database engine performs a series of steps to return your result:

1. 1. Parsing: The engine checks the SQL syntax for correctness. Is the spelling right? Are the keywords in the correct order?
2. 2. Binding (Validation): It verifies that the tables and columns mentioned in the query actually exist and that you have permission to access them.
3. 3. Optimization: This is the ‘magic’ step. The query optimizer analyzes your query and evaluates many different ways to execute it (e.g., which index to use, what join algorithm is best). It then selects the most efficient execution plan.
4. 4. Execution: The engine runs the chosen execution plan, retrieving the data from storage, performing joins, filtering, sorting, and finally returning the result set to you.

Categories of SQL Commands

SQL commands are typically grouped into sub-languages based on their function:

DDL - Data Definition Language

Used to define or modify the database structure.

Command	Description
CREATE	Creates database objects like tables, views, and indexes.
ALTER	Modifies the structure of an existing object, such as adding a column to a table.
DROP	Deletes an entire database object.
TRUNCATE	Quickly removes all records from a table.

DML - Data Manipulation Language

Used to manage data within the objects.

Command	Description
SELECT	Retrieves data from one or more tables.
INSERT	Adds new rows of data into a table.
UPDATE	Modifies existing records in a table.
DELETE	Removes records from a table.

DCL - Data Control Language

Used to manage user access and permissions.

Command	Description
GRANT	Gives a user specific permissions (e.g., SELECT, INSERT) on a database object.
REVOKE	Removes permissions from a user.

TCL - Transaction Control Language

Used to manage transactions to ensure data integrity.

Command	Description
COMMIT	Saves all work done in the current transaction.
ROLLBACK	Undoes all work done in the current transaction.
SAVEPOINT	Sets a point within a transaction to which you can later roll back.

SQL - RDBMS Concepts

What is an RDBMS?

An RDBMS (Relational Database Management System) is software used to create, manage, and administer relational databases. It’s based on the relational model, which organizes data into tables (or ‘relations’) of columns and rows. Modern RDBMSs like PostgreSQL, MySQL, and SQL Server are the foundation of countless applications.

Core Building Blocks

Data in an RDBMS is structured using a few key concepts:

• Table: A collection of related data entries, organized into columns and rows. For example, a Users table would store information about all users.
• Column (or Field): A vertical entity in a table that contains a specific attribute for each record. In a Users table, you would have columns like UserID, Username, and Email.
• Row (or Record): A horizontal entity in a table representing a single entry. One row in the Users table represents one specific user.
• NULL Value: A special marker used to indicate that a data value does not exist in the database. It’s important to understand that NULL is not the same as zero or an empty string. It represents the absence of a value. This leads to three-valued logic in comparisons: TRUE, FALSE, and UNKNOWN.

SQL Constraints

Constraints are rules enforced on data columns to ensure the accuracy, reliability, and integrity of the data.

• NOT NULL: Ensures a column cannot have a NULL value.
• UNIQUE: Ensures that all values in a column (or a set of columns) are distinct.
• PRIMARY KEY: A combination of NOT NULL and UNIQUE. It uniquely identifies each row in a table. A table can have only one primary key.
• FOREIGN KEY: A key used to link two tables together. It’s a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table. This enforces referential integrity.
• CHECK: Ensures that the values in a column satisfy a specific condition (e.g., Price > 0).
• DEFAULT: Provides a default value for a column when no value is specified.

Database Normalization

Normalization is the process of organizing columns and tables in a relational database to minimize data redundancy and improve data integrity. It involves dividing larger tables into smaller, well-structured tables and defining relationships between them.

The main goals are:

• Eliminating Redundancy: Storing the same piece of data in only one place.
• Ensuring Logical Data Dependencies: Making sure that related data is stored together.

Normalization is typically discussed in terms of ‘normal forms’. For most applications, achieving the Third Normal Form (3NF) is a good goal:

• First Normal Form (1NF): Ensures that the table has a primary key and that each cell contains a single, atomic value (no lists or repeating groups).
• Second Normal Form (2NF): Must be in 1NF. All non-key attributes must be fully dependent on the entire primary key (relevant for composite keys).
• Third Normal Form (3NF): Must be in 2NF. All attributes must depend only on the primary key, not on other non-key attributes.

A Look at Modern RDBMSs

While dozens of RDBMSs exist, a few dominate the landscape. Here’s a brief overview of the most popular choices today.

PostgreSQL

Often called ‘Postgres’, it’s a powerful, open-source object-relational database system known for its strong adherence to SQL standards, reliability, and rich feature set. It’s a favorite in the startup world and for complex, data-intensive applications.

Key Features

• Advanced Data Types: Excellent support for JSONB (a binary JSON format), arrays, and geometric data.
• Extensibility: Users can define their own data types, functions, and operators.
• Concurrency: Robust Multi-Version Concurrency Control (MVCC) for handling many simultaneous readers and writers.
• Open Source: A vibrant community and a permissive license.

MySQL

The world’s most popular open-source database, MySQL is a cornerstone of the web, famously part of the LAMP (Linux, Apache, MySQL, PHP/Python/Perl) stack. It’s known for its ease of use, speed, and reliability. It is now owned by Oracle Corp.

Key Features

• High Performance: Excellent for read-heavy workloads, making it ideal for many web applications.
• Ease of Use: Simple to set up and manage.
• Large Ecosystem: Widely supported by hosting providers, tools, and frameworks.
• Storage Engines: Supports different storage engines (like InnoDB and MyISAM) for different use cases.

Microsoft SQL Server

A comprehensive, enterprise-grade RDBMS from Microsoft. Traditionally a Windows-only product, it is now available on Linux and Docker, increasing its reach. It is a dominant force in corporate environments, especially those invested in the Microsoft ecosystem.

Key Features

• Deep Integration: Seamless integration with other Microsoft products like .NET, Azure, and Power BI.
• Business Intelligence: Strong built-in tools for reporting, analytics, and machine learning.
• Performance and Security: Known for its high performance and robust security features.
• T-SQL: A powerful and feature-rich SQL dialect.

SQLite

SQLite is unique: it’s not a client-server database engine. Instead, it is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. It is the most widely deployed database engine in the world, used in countless mobile apps, web browsers, and embedded devices.

Key Features

• Serverless: The entire database (definitions, tables, indexes, and data) is stored in a single file on the host machine.
• Zero-Configuration: No installation or administration needed.
• Lightweight: Extremely small footprint, ideal for resource-constrained environments.
• Reliable: Fully ACID compliant, making it a trustworthy choice for local data storage.

Oracle Database

A multi-model database management system produced and marketed by Oracle Corporation. It is a dominant player in the enterprise market, known for its scalability, performance, and vast feature set, especially for large-scale transaction processing and data warehousing.

Key Features

• Scalability: Features like Real Application Clusters (RAC) allow for high availability and scalability.
• Robustness: Renowned for its stability and comprehensive toolset for managing mission-critical applications.
• Feature-Rich: Includes a wide array of features for data warehousing, security, and high availability.

SQL - Syntax

SQL follows a specific set of rules and guidelines known as syntax. While most commands are standard across databases, some functions and data types can vary. This section provides a quick reference for the most common SQL statements.

Key points to remember: SQL is generally case-insensitive for keywords (SELECT is the same as select), but often case-sensitive for table and column names, especially on Linux-based systems. Statements are terminated with a semicolon (;), which is required by some tools and good practice in all.

Common SQL Statement Syntax

These examples use standard SQL syntax. Specific implementations might have slight variations.

Data Query Language (DQL)

-- Select all columns from a table
SELECT * FROM table_name;

-- Select specific columns
SELECT column1, column2 FROM table_name;

-- Filter rows with a WHERE clause
SELECT column1, column2 FROM table_name WHERE condition;

-- Remove duplicate rows from the result
SELECT DISTINCT column1 FROM table_name;

-- Sort the results
SELECT column1, column2 FROM table_name ORDER BY column1 ASC, column2 DESC;

-- Limit the number of rows returned (Standard SQL)
SELECT column1, column2 FROM table_name FETCH FIRST 10 ROWS ONLY;
-- (LIMIT 10 in MySQL/PostgreSQL, SELECT TOP 10 in SQL Server)

-- Group rows and use aggregate functions
SELECT column1, COUNT(*) FROM table_name GROUP BY column1;

-- Filter groups with a HAVING clause
SELECT column1, COUNT(*) FROM table_name GROUP BY column1 HAVING COUNT(*) > 5;

Data Manipulation Language (DML)

-- Insert a new row
INSERT INTO table_name (column1, column2) VALUES (value1, value2);

-- Update existing rows
UPDATE table_name SET column1 = new_value1 WHERE condition;

-- Delete rows
DELETE FROM table_name WHERE condition;

Data Definition Language (DDL)

-- Create a new table
CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    PRIMARY KEY (column1)
);

-- Add a new column to an existing table
ALTER TABLE table_name ADD COLUMN new_column_name datatype;

-- Modify an existing column
ALTER TABLE table_name ALTER COLUMN column_name TYPE new_datatype;

-- Drop an existing table
DROP TABLE table_name;

-- Create an index to improve query performance
CREATE INDEX index_name ON table_name (column_to_index);

SQL - Data Types

A data type is an attribute that specifies the type of data a column can hold, such as integer data, character data, or date and time data. Choosing the correct data type is crucial for data integrity and performance. While many data types are standardized, there are variations between different RDBMSs.

Common SQL Data Types

Numeric Types

Used for storing numerical data.

Data Type	Description
INTEGER or INT	A standard whole number. Typical range is -2,147,483,648 to 2,147,483,647.
BIGINT	A large whole number, for values outside the INTEGER range.
SMALLINT	A small whole number. Often used for optimization when values are known to be small.
DECIMAL(p, s) or NUMERIC(p, s)	Exact fixed-point number. p is the total number of digits (precision), and s is the number of digits after the decimal point (scale). Ideal for financial data.
FLOAT(p), REAL	Approximate floating-point numbers. Used for scientific calculations where exact precision is not required.

Character String Types

Used for storing text data.

Data Type	Description
CHAR(n)	Fixed-length string of n characters. If the string is shorter than n, it is padded with spaces. Use for fixed-length codes like country codes (‘US’, ‘DE’).
VARCHAR(n)	Variable-length string with a maximum length of n characters. Uses only the space needed for the actual string. Most common choice for text.
TEXT	Variable-length string for holding large amounts of text. The maximum length is RDBMS-specific but is typically very large.

Note on Unicode: Modern databases handle Unicode (like UTF-8) by default or through collation settings. SQL Server has specific types like NVARCHAR and NCHAR for Unicode data.

Date and Time Types

Used for storing temporal information.

Data Type	Description
DATE	Stores a date (year, month, day).
TIME	Stores a time (hour, minute, second).
TIMESTAMP	Stores both date and time. TIMESTAMP WITH TIME ZONE (or TIMESTAMPTZ) is the recommended type as it stores the time zone information, preventing ambiguity.

Boolean Type

Used for storing true/false values.

Data Type	Description
BOOLEAN	Stores TRUE, FALSE, or NULL. Some databases like MySQL use TINYINT(1) as an alias for this.

Modern and Specialized Types

Modern RDBMSs support more complex data structures.

Data Type	Description
JSON / JSONB	Stores JSON (JavaScript Object Notation) data. JSONB (in PostgreSQL) is a binary, indexed format that is highly efficient for querying.
UUID	Stores a Universally Unique Identifier. Ideal for primary keys in distributed systems to avoid collisions.
XML	Stores XML data. Less common now than JSON but still used in some enterprise systems.
ARRAY	Stores an array of values of another type (e.g., INTEGER[]). Supported by PostgreSQL and some other systems.

SQL - Operators

What is an Operator?

An operator is a reserved word or character used primarily in an SQL WHERE clause to perform operations, such as comparisons and arithmetic. Operators are used to specify conditions and to connect multiple conditions in a statement.

SQL Arithmetic Operators

Perform mathematical operations on numeric data.

Operator	Description	Example
+ (Addition)	Adds two numeric values.	Price + Tax will give the total cost.
- (Subtraction)	Subtracts the right operand from the left.	Salary - Deduction will give net pay.
* (Multiplication)	Multiplies two numeric values.	Quantity * UnitPrice will give the line total.
/ (Division)	Divides the left operand by the right.	TotalSales / NumberOfUnits will give the average price.
% (Modulus)	Returns the integer remainder of a division.	10 % 3 will give 1.

SQL Comparison Operators

Used to compare two expressions. The result is a boolean value (TRUE, FALSE, or UNKNOWN).

Operator	Description	Example
=	Checks if two values are equal.	Country = 'USA'
<> or !=	Checks if two values are not equal. <> is the SQL standard.	Status <> 'Completed'
>	Checks if the left value is greater than the right.	Price > 100.00
<	Checks if the left value is less than the right.	StockLevel < 10
>=	Checks if the left value is greater than or equal to the right.	Age >= 18
<=	Checks if the left value is less than or equal to the right.	Discount <= 0.5

SQL Logical Operators

Used to combine multiple conditions in a WHERE clause.

Operator	Description
ALL	Returns true if all of the subquery values meet the condition.
AND	Returns true if all connected conditions are true.
ANY / SOME	Returns true if any of the subquery values meet the condition.
BETWEEN	Returns true if a value is within a given range (inclusive). age BETWEEN 18 AND 30.
EXISTS	Returns true if a subquery returns one or more records.
IN	Returns true if a value matches any value in a list. Country IN ('USA', 'Canada', 'Mexico').
LIKE	Used for pattern matching in strings with wildcards (% for multiple characters, _ for a single character).
NOT	Reverses the result of any other logical operator (e.g., NOT IN, NOT BETWEEN, NOT EXISTS).
OR	Returns true if any of the connected conditions are true.
IS NULL	Checks if a value is NULL.
IS NOT NULL	Checks if a value is not NULL.

SQL - Expressions

An SQL expression is a combination of one or more values, operators, and SQL functions that evaluates to a single value. Expressions are used widely in SQL, from SELECT lists to WHERE and HAVING clauses.

Syntax

Expressions can be used in many parts of a query. For example, in the SELECT list and the WHERE clause:

SELECT expression1, expression2, ...
FROM table_name
WHERE condition_expression;

There are several types of SQL expressions:

Numeric Expressions

These expressions perform mathematical calculations and return a numeric value.

-- A simple calculation with an alias
SELECT (Price * 1.08) AS PriceWithTax FROM Products;

-- Using aggregate functions
SELECT AVG(Salary) AS AverageSalary FROM Employees;

-- Counting rows that meet a condition
SELECT COUNT(*) FROM Orders WHERE Status = 'Shipped';

String Expressions

These expressions operate on string data.

-- Concatenating strings (standard SQL uses ||)
SELECT FirstName || ' ' || LastName AS FullName FROM Users;

-- In SQL Server, you might use +
-- SELECT FirstName + ' ' + LastName AS FullName FROM Users;

-- Using a string function
SELECT UPPER(ProductName) FROM Products;

Date Expressions

These expressions return date/time values or perform calculations on them.

-- Get the current timestamp (standard SQL)
SELECT CURRENT_TIMESTAMP;

-- Extract a part from a date (e.g., the year)
SELECT EXTRACT(YEAR FROM OrderDate) AS OrderYear FROM Orders;

-- Date arithmetic
SELECT OrderDate + INTERVAL '30 days' AS DueDate FROM Orders;

Conditional Expressions (CASE)

The CASE expression is a powerful tool for implementing if-then-else logic within an SQL query. It allows you to return different values based on specified conditions.

SELECT OrderID, TotalAmount,
       CASE
           WHEN TotalAmount > 1000 THEN 'High Value'
           WHEN TotalAmount > 500  THEN 'Medium Value'
           ELSE 'Low Value'
       END AS OrderCategory
FROM Orders;

SQL - CREATE Database

The CREATE DATABASE statement is used to create a new database in your RDBMS. This is typically the first step before you can create tables and store data.

Syntax

The basic syntax for CREATE DATABASE is simple:

CREATE DATABASE DatabaseName;

The database name must be unique within the RDBMS instance. Advanced options allow specifying character sets, collations, and owners.

-- A more advanced example (PostgreSQL)
CREATE DATABASE my_app_db
    WITH
    OWNER = app_user
    ENCODING = 'UTF8'
    CONNECTION LIMIT = 100;

Example

To create a new database named my_store, you would run:

CREATE DATABASE my_store;

You must have the appropriate administrative privileges to create a database. After creation, you can typically see it by listing all available databases.

-- In MySQL client
SHOW DATABASES;

-- In PostgreSQL's psql client
\l

The output would list my_store among the other databases.

Databases vs. Schemas

It’s important to note a difference in terminology. In systems like MySQL, you often create multiple databases to separate applications. In PostgreSQL and Oracle, it is more common to have a single database for an environment (e.g., ‘development’) and use schemas within that database to separate applications. A schema is a namespace that contains tables, views, and other objects.

SQL - DROP Database

The DROP DATABASE statement is used to permanently delete an existing database, including all of its tables, data, and other objects.

Syntax

The basic syntax for DROP DATABASE is as follows:

DROP DATABASE DatabaseName;

To avoid an error if the database does not exist, many RDBMSs support the IF EXISTS clause:

DROP DATABASE IF EXISTS DatabaseName;

Example

To delete the database named my_store that we created earlier:

DROP DATABASE my_store;

Warning: This is an Irreversible Operation

Use extreme caution with this command. Dropping a database is a permanent action. All data, tables, indexes, views, and stored procedures within that database will be lost forever. Always ensure you have a reliable, recent backup before dropping a production database. You must also have administrative privileges to perform this operation.

After the command executes successfully, verifying with SHOW DATABASES (MySQL) or \l (PostgreSQL) will show that the database is no longer in the list.

SQL - Selecting and Using a Database

When working with an RDBMS that contains multiple databases, you must specify which database you intend to work with for your current session. Subsequent commands like CREATE TABLE or SELECT will then be executed within the context of that chosen database.

The USE Statement (MySQL)

In MySQL and some other systems like SQL Server, the USE statement selects a database to be the default for the current session.

Syntax

USE DatabaseName;

Example

First, list the available databases to see your options:

-- In MySQL client
SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| my_store           |
+--------------------+

To start working with the my_store database, you execute:

USE my_store;
-- Output: Database changed

Now, any SELECT * FROM products; query will look for the products table inside the my_store database.

The \c or \connect Command (PostgreSQL)

In PostgreSQL, you connect to a specific database when you start the psql client. To switch to a different database within the same session, you use the \c or \connect meta-command.

Syntax

\c DatabaseName
-- or
\connect DatabaseName

Example

If you are currently connected to the default postgres database and want to switch to my_store:

postgres=# \c my_store
-- Output: You are now connected to database "my_store" as user "your_user".

Fully Qualified Names

As an alternative to switching the default database, you can always refer to a table using its fully qualified name, which is database_name.schema_name.table_name. This can be useful for queries that need to join data across different databases (if supported by your RDBMS).

-- Example in MySQL
SELECT u.username, p.product_name
FROM my_app.users u
JOIN my_store.products p ON u.id = p.created_by;

SQL - CREATE TABLE

The CREATE TABLE statement is used to create a new table in your database. This is a fundamental DDL operation where you define the table’s name, its columns, and the data type and constraints for each column.

Syntax

The basic syntax for the CREATE TABLE statement is as follows:

CREATE TABLE table_name (
   column1_name data_type [column_constraints],
   column2_name data_type [column_constraints],
   ...
   [table_constraints]
);

Key components:

• table_name: The unique name for your new table.
• column_name: The name of each column.
• data_type: The type of data the column will store (e.g., INT, VARCHAR, TIMESTAMP).
• column_constraints: Rules applied to a single column (e.g., NOT NULL, UNIQUE, DEFAULT).
• table_constraints: Rules applied to one or more columns (e.g., PRIMARY KEY, FOREIGN KEY, CHECK).

Example: Creating a Users Table

Let’s create a well-structured Users table with modern data types and constraints. This example uses standard SQL features where possible.

CREATE TABLE Users (
    UserID INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- Standard identity column and PK constraint
    Username VARCHAR(50) NOT NULL UNIQUE,                 -- Must be provided and must be unique
    Email VARCHAR(255) NOT NULL UNIQUE,                   -- Must be provided and must be unique
    PasswordHash CHAR(60) NOT NULL,                       -- For storing hashed passwords (e.g., from bcrypt)
    IsActive BOOLEAN DEFAULT TRUE,                        -- A boolean flag with a default value
    CreatedAt TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, -- Automatically records creation time

    -- A table-level constraint
    CHECK (length(Username) >= 3)
);

Dialect-Specific Notes:

• MySQL: Would use INT AUTO_INCREMENT PRIMARY KEY instead of GENERATED BY DEFAULT AS IDENTITY.
• PostgreSQL: Often uses SERIAL PRIMARY KEY as a shorthand for the identity column.
• SQL Server: Would use INT IDENTITY(1,1) PRIMARY KEY.

Verifying Table Creation

After executing the CREATE TABLE statement, most database clients will confirm its success. You can also inspect the table’s structure using a specific command:

-- In MySQL client
DESCRIBE Users;

-- In PostgreSQL's psql client
\d Users

A more standard way to get table metadata is to query the INFORMATION_SCHEMA, which is available in most RDBMSs:

SELECT column_name, data_type, is_nullable
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'users';

SQL - DROP or DELETE Table

The DROP TABLE statement is a DDL command used to permanently delete a table, including its structure, data, indexes, triggers, and constraints.

Syntax

The basic syntax for DROP TABLE is very simple:

DROP TABLE table_name;

To prevent an error if the table doesn’t exist, it’s best practice to use the IF EXISTS clause, which is supported by most modern RDBMSs.

DROP TABLE IF EXISTS table_name;

Some systems, like PostgreSQL, also support a CASCADE option to automatically drop any objects that depend on the table (like views or foreign key constraints).

-- PostgreSQL example
DROP TABLE IF EXISTS table_name CASCADE;

Example

Let’s assume we have our Users table and we want to remove it from the database.

First, we can verify its existence:

-- In PostgreSQL's psql client
\dt

-- Lists tables, 'users' should appear.

Now, we execute the DROP TABLE command:

DROP TABLE Users;

After the command executes, trying to describe or select from the table will result in an error, because the table no longer exists.

SELECT * FROM Users;

-- ERROR:  relation "users" does not exist

Critical Warning

DROP TABLE is a powerful and irreversible operation. Once a table is dropped, all its data and structure are gone permanently. There is no simple ‘undo’ command. Always double-check which table you are dropping, especially in a production environment, and ensure you have a reliable backup.

SQL - INSERT INTO Statement

The INSERT INTO statement is a DML command used to add new rows of data to a table.

Syntax

There are two primary ways to use the INSERT INTO statement.

1. Specifying Column Names

This is the most common and recommended syntax, as it’s explicit and not affected by changes in column order.

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

2. Without Specifying Column Names

If you are providing a value for every column in the table in the correct order, you can omit the column list. This is fragile and not recommended for production code.

INSERT INTO table_name VALUES (value1, value2, value3, ...);

Inserting Multiple Rows

Most modern RDBMSs allow you to insert multiple rows in a single statement for better efficiency.

INSERT INTO table_name (column1, column2)
VALUES
    (value1a, value2a),
    (value1b, value2b),
    (value1c, value2c);

Example

Let’s use our Users table and insert a few records. Note that we don’t need to provide values for UserID (it’s auto-generated) or CreatedAt and IsActive (they have DEFAULT values).

-- Insert a single user
INSERT INTO Users (Username, Email, PasswordHash)
VALUES ('alice', 'alice@example.com', 'some_bcrypt_hash_string_1');

-- Insert multiple users at once
INSERT INTO Users (Username, Email, PasswordHash, IsActive)
VALUES
    ('bob', 'bob@example.com', 'some_bcrypt_hash_string_2', TRUE),
    ('charlie', 'charlie@example.com', 'some_bcrypt_hash_string_3', FALSE);

After these statements, the Users table would contain:

SELECT UserID, Username, Email, IsActive, CreatedAt FROM Users;

+--------+----------+-----------------------+----------+-------------------------------+
| UserID | Username | Email                 | IsActive | CreatedAt                     |
+--------+----------+-----------------------+----------+-------------------------------+
|      1 | alice    | alice@example.com     | true     | 2023-10-27 10:00:00.123456+00 |
|      2 | bob      | bob@example.com       | true     | 2023-10-27 10:00:01.654321+00 |
|      3 | charlie  | charlie@example.com   | false    | 2023-10-27 10:00:01.654321+00 |
+--------+----------+-----------------------+----------+-------------------------------+

Populating a Table from Another Table

You can use a SELECT statement to provide the data for an INSERT statement. This is extremely useful for copying or archiving data.

Syntax:

INSERT INTO destination_table (column1, column2, ...)
SELECT columnA, columnB, ...
FROM source_table
WHERE condition;

This query takes the results from the SELECT statement and inserts them directly into the destination_table.

SQL - SELECT Query

The SELECT statement is the most frequently used command in SQL. Its purpose is to retrieve data from one or more database tables. The data returned is stored in a result table, which is also known as the result-set.

Syntax

The basic syntax of the SELECT statement is:

SELECT column1, column2, ...
FROM table_name;

Here, column1, column2, … are the names of the columns in the table from which you want to retrieve data.

To fetch all available columns without listing them individually, you can use the asterisk (*) wildcard. While convenient for exploration, it is not recommended for production code because it can hurt performance and make code less predictable.

SELECT *
FROM table_name;

Example

Let’s use a Customers table for our examples. Assume it has the following records:

+----+----------+-----+-------------+----------+
| ID | Name     | Age | City        | Salary   |
+----+----------+-----+-------------+----------+
|  1 | Ramesh   |  32 | Ahmedabad   |  2000.00 |
|  2 | Khilan   |  25 | Delhi       |  1500.00 |
|  3 | Kaushik  |  23 | Kota        |  2000.00 |
|  4 | Chaitali |  25 | Mumbai      |  6500.00 |
|  5 | Hardik   |  27 | Bhopal      |  8500.00 |
|  6 | Komal    |  22 | Hyderabad   |  4500.00 |
|  7 | Muffy    |  24 | Indore      | 10000.00 |
+----+----------+-----+-------------+----------+

The following query fetches just the ID, Name, and Salary for all customers.

SELECT ID, Name, Salary
FROM Customers;

This would produce the following result-set:

+----+----------+----------+
| ID | Name     | Salary   |
+----+----------+----------+
|  1 | Ramesh   |  2000.00 |
|  2 | Khilan   |  1500.00 |
|  3 | Kaushik  |  2000.00 |
|  4 | Chaitali |  6500.00 |
|  5 | Hardik   |  8500.00 |
|  6 | Komal    |  4500.00 |
|  7 | Muffy    | 10000.00 |
+----+----------+----------+

To fetch all columns for every customer, you would use:

SELECT * FROM Customers;

This would return the entire table as shown in the initial example.

Best Practice: Avoid SELECT * in Applications

In application code, always specify the exact columns you need. There are several reasons for this:

• Performance: Requesting unnecessary columns (especially large TEXT or BLOB fields) increases network traffic and database load.
• Readability: Explicitly listing columns makes your query’s intent clear to other developers.
• Stability: If the underlying table structure changes (e.g., a column is added or removed), SELECT * can break your application code, whereas an explicit column list will not.

SQL - WHERE Clause

The WHERE clause is used to filter records. It extracts only those records that fulfill a specified condition. This is one of the most critical parts of SQL, allowing you to narrow down vast amounts of data to just the information you need.

The WHERE clause is not only used with SELECT statements, but also with UPDATE and DELETE statements to specify which rows to modify or remove.

Syntax

The basic syntax of a SELECT statement with a WHERE clause is:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

The condition is an expression that evaluates to true, false, or unknown for each row. You can use various comparison (=, >, <) and logical (AND, OR, NOT) operators to build your conditions.

Example

Let’s use our Customers table again:

+----+----------+-----+-------------+----------+
| ID | Name     | Age | City        | Salary   |
+----+----------+-----+-------------+----------+
|  1 | Ramesh   |  32 | Ahmedabad   |  2000.00 |
|  2 | Khilan   |  25 | Delhi       |  1500.00 |
|  3 | Kaushik  |  23 | Kota        |  2000.00 |
|  4 | Chaitali |  25 | Mumbai      |  6500.00 |
|  5 | Hardik   |  27 | Bhopal      |  8500.00 |
|  6 | Komal    |  22 | Hyderabad   |  4500.00 |
|  7 | Muffy    |  24 | Indore      | 10000.00 |
+----+----------+-----+-------------+----------+

The following query retrieves the ID, Name, and Salary for customers whose salary is greater than 2000:

SELECT ID, Name, Salary
FROM Customers
WHERE Salary > 2000;

This would produce the following result:

+----+----------+----------+
| ID | Name     | Salary   |
+----+----------+----------+
|  4 | Chaitali |  6500.00 |
|  5 | Hardik   |  8500.00 |
|  6 | Komal    |  4500.00 |
|  7 | Muffy    | 10000.00 |
+----+----------+----------+

The next query fetches details for a customer with a specific name. Note that string literals must be enclosed in single quotes (').

SELECT ID, Name, Salary
FROM Customers
WHERE Name = 'Hardik';

This would produce the following result:

+----+--------+---------+
| ID | Name   | Salary  |
+----+--------+---------+
|  5 | Hardik | 8500.00 |
+----+--------+---------+

SQL - AND, OR, and NOT Operators

The AND, OR, and NOT operators are used in the WHERE clause to combine or negate multiple conditions, allowing for more complex data filtering.

The AND Operator

The AND operator displays a record if all the conditions separated by AND are TRUE.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND ...;

Example

Let’s find all customers from the Customers table who are under 25 years old AND have a salary greater than 2000.

SELECT ID, Name, Salary, Age
FROM Customers
WHERE Age < 25 AND Salary > 2000;

Based on our sample data, this would produce the following result:

+----+-------+----------+-----+
| ID | Name  | Salary   | Age |
+----+-------+----------+-----+
|  6 | Komal |  4500.00 |  22 |
|  7 | Muffy | 10000.00 |  24 |
+----+-------+----------+-----+

The OR Operator

The OR operator displays a record if any of the conditions separated by OR is TRUE.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR ...;

Example

Let’s find all customers who are from ‘Delhi’ OR have a salary less than 2000.

SELECT ID, Name, City, Salary
FROM Customers
WHERE City = 'Delhi' OR Salary < 2000;

This would produce the following result:

+----+--------+-------+---------+
| ID | Name   | City  | Salary  |
+----+--------+-------+---------+
|  2 | Khilan | Delhi | 1500.00 |
+----+--------+-------+---------+

The NOT Operator

The NOT operator displays a record if the condition(s) is NOT TRUE. It can be used to negate expressions.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

Example

Let’s find all customers who are NOT from ‘Mumbai’.

SELECT ID, Name, City
FROM Customers
WHERE NOT City = 'Mumbai';

This is equivalent to using the <> or != operator.

Combining Operators

You can combine AND, OR, and NOT operators. When doing so, use parentheses () to ensure conditions are evaluated in the correct order, as AND has higher precedence than OR.

For example, to find customers who are from ‘Mumbai’ AND (are older than 25 OR have a salary over 8000):

SELECT *
FROM Customers
WHERE City = 'Mumbai' AND (Age > 25 OR Salary > 8000);

Without the parentheses, the query would be interpreted as (City = 'Mumbai' AND Age > 25) OR Salary > 8000, which would yield a different result.