SQL Syntax

What is SQL Syntax?

SQL (Structured Query Language) syntax refers to the rules and structure used to write SQL statements. These statements are the foundation for interacting with relational databases, allowing users to perform operations such as retrieving, updating, and managing data.

General SQL Syntax

SQL syntax consists of three key components:

Keywords: Predefined words like SELECT, INSERT, WHERE, etc.
Identifiers: Names of database objects like tables, columns, or constraints.
Operators: Used for comparisons, logical operations, or arithmetic.

Basic SQL Statement Structure

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

Each clause in this structure has a specific purpose:

Clause	Description
`SELECT`	Specifies the columns to retrieve.
`FROM`	Indicates the table to query data from.
`WHERE`	Filters rows based on conditions.
`GROUP BY`	Group rows share a property and apply aggregate functions.
`HAVING`	Filters groups created by `GROUP BY`.
`ORDER BY`	Sorts the result set by specified columns.

Common SQL Statements

1. Retrieve Data

SELECT column1, column2 
FROM table_name
WHERE condition;

Example: Get all customers from the USA.

SELECT name, country 
FROM customers 
WHERE country = 'USA';

2. Insert Data

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

Example: Add a new customer.

INSERT INTO customers (name, country)
VALUES ('John Doe', 'Canada');

3. Update Data

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example: Update a customer's country.

UPDATE customers
SET country = 'UK'
WHERE name = 'John Doe';

4. Delete Data

DELETE FROM table_name
WHERE condition;

Example: Remove a customer from the database.

DELETE FROM customers
WHERE name = 'John Doe';

5. Create Table

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    ...
);

Example: Create a customers table.

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    country VARCHAR(50)
);

6. Alter Table

Modify an existing table by adding or removing columns.

ALTER TABLE table_name
ADD column_name datatype;

Example: Add a new column for phone numbers.

ALTER TABLE customers
ADD phone_number VARCHAR(15);

7. Drop Table

Remove a table from the database.

DROP TABLE table_name;

Example: Delete the customers table.

DROP TABLE customers;

SQL Data Types

SQL uses various data types to define the kind of data that can be stored in a column. Some common types are:

Category	Data Type	Description
Numeric	`INT`, `FLOAT`, `DECIMAL`, `BIGINT`	For storing numbers.
Character String	`CHAR`, `VARCHAR`, `TEXT`	For storing text.
Date and Time	`DATE`, `TIME`, `DATETIME`, `TIMESTAMP`	For storing date and time values.
Binary	`BLOB`, `BINARY`, `VARBINARY`	For storing binary data like images or files.

SQL Keywords

Some commonly used SQL keywords include:

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

Best Practices for Writing SQL Syntax

Use Consistent Formatting:
Write SQL keywords in uppercase and align clauses for readability.
```
SELECT name, country
FROM customers
WHERE country = 'USA';
```

Use Aliases for Readability:
Simplify queries with aliases for tables and columns.

SELECT c.name, o.order_date
FROM customers AS c
JOIN orders AS o
ON c.id = o.customer_id;

Avoid Selecting All Columns:
Specify column names instead of using SELECT * to improve performance.
```
SELECT name, email FROM users;
```
Test Queries:
Always test your queries on a small dataset before applying them to the entire database.

Example: Combining Multiple Clauses

Retrieve customers who placed more than 5 orders, grouped by country, and sort the results by the number of orders.

SELECT country, COUNT(*) AS total_orders
FROM customers
JOIN orders ON customers.id = orders.customer_id
WHERE orders.order_date >= '2023-01-01'
GROUP BY country
HAVING COUNT(*) > 5
ORDER BY total_orders DESC;

Conclusion

Understanding and mastering SQL syntax is the foundation of working with relational databases. By learning the structure, keywords, and common operations, you can effectively retrieve, manipulate, and manage data in a database system.

Souy Soeng