SQL - Syntax

SQL - Core Syntax and Statements

1. What is SQL Syntax?

SQL (Structured Query Language) syntax is the set of rules and guidelines for writing valid SQL queries. All SQL statements begin with a keyword (like SELECT, INSERT, CREATE) and typically end with a semicolon (;). The semicolon acts as a statement terminator, allowing you to run multiple commands in a single batch.

2. Case Sensitivity and Naming Conventions

SQL keywords (SELECT, FROM, WHERE, etc.) are case-insensitive. SELECT is the same as select. However, the case sensitivity of table and column names depends on the database system and its configuration. For example, MySQL on Linux is case-sensitive for table names by default.

Best Practice: For maximum readability and consistency, write SQL keywords in UPPERCASE and table/column names in snake_case (e.g., product_orders). This makes queries much easier to read.

3. The Main Categories of SQL Statements

SQL commands are logically grouped into several categories. The three most common are:

• Data Definition Language (DDL): Defines and manages the database structure. (e.g., CREATE TABLE, ALTER TABLE).
• Data Manipulation Language (DML): Used for adding, modifying, and deleting data. (e.g., INSERT, UPDATE, DELETE).
• Data Query Language (DQL): The most common category, used to retrieve data. (e.g., SELECT).

4. Data Definition Language (DDL)

DDL statements are used to build and modify the structure of your database objects.

CREATE TABLE

Creates a new table in the database.

CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT, -- AUTO_INCREMENT is for MySQL/MariaDB
    name VARCHAR(255) NOT NULL,
    age INT,
    address VARCHAR(255),
    salary DECIMAL(10, 2)
);

ALTER TABLE

Modifies an existing table, for example, by adding or dropping a column.

-- Add a new column
ALTER TABLE customers ADD COLUMN email VARCHAR(255) UNIQUE;

-- Drop an existing column
ALTER TABLE customers DROP COLUMN age;

DROP TABLE

Permanently deletes a table and all its data. Use with caution!

DROP TABLE customers;

5. Data Manipulation Language (DML)

DML statements are used to manage the data within the tables.

INSERT INTO

Adds new rows of data to a table. Specifying columns is the recommended, safer syntax.

-- Recommended: Specify columns
INSERT INTO customers (name, age, salary)
VALUES ('Alice', 30, 75000.00);

-- Insert multiple rows at once
INSERT INTO customers (name, age, salary)
VALUES
    ('Bob', 25, 60000.00),
    ('Charlie', 35, 90000.00);

UPDATE

Modifies existing records. Always use a WHERE clause to avoid updating all rows in the table.

UPDATE customers
SET salary = 80000.00, address = 'New York'
WHERE name = 'Alice';

DELETE

Removes existing records. Always use a WHERE clause to avoid deleting all data.

DELETE FROM customers
WHERE name = 'Charlie';

6. Data Query Language (DQL)

SELECT is the workhorse of SQL, used to retrieve data. It has many optional clauses to refine the results.

-- Sample reference table for DQL examples
SELECT * FROM customers;

id	name	age	address	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

SELECT with WHERE Clause

Filters records based on specific conditions.

SELECT name, salary FROM customers WHERE salary > 5000;

SELECT with AND / OR / NOT

Combine multiple conditions.

SELECT name, age, salary FROM customers
WHERE salary > 2000 AND age < 30;

SELECT with IN

Checks if a value matches any value in a list.

SELECT * FROM customers WHERE name IN ('Khilan', 'Hardik');

SELECT with LIKE

Performs pattern matching on strings. % is a wildcard for zero or more characters.

SELECT * FROM customers WHERE address LIKE 'M%'; -- Finds addresses starting with 'M'

SELECT with ORDER BY

Sorts the result set. ASC for ascending (default), DESC for descending.

SELECT name, salary FROM customers ORDER BY salary DESC;

SELECT with GROUP BY and HAVING

Groups rows that have the same values into summary rows and then filters those groups.

-- Find ages with more than one customer
SELECT age, COUNT(id) AS number_of_customers
FROM customers
GROUP BY age
HAVING COUNT(id) > 1;

7. Advanced Query Structuring: Common Table Expressions (CTEs)

For complex queries, subqueries can become difficult to read. A Common Table Expression (CTE), defined with the WITH clause, allows you to create a temporary, named result set that you can reference within your main query. This dramatically improves readability and organization.

-- Using a CTE to find high-earning customers in Delhi
WITH delhi_customers AS (
    SELECT id, name, salary
    FROM customers
    WHERE address = 'Delhi'
)
SELECT name, salary
FROM delhi_customers
WHERE salary > 1000;