SQL Syntax

Introduction

In this chapter, we will explore the basic syntax of SQL, the language used to manage and manipulate databases. You will learn about the structure of SQL statements and how to perform common operations such as querying, inserting, updating, and deleting data.

Basic SQL Syntax

1. SQL Statements

SQL statements are used to perform tasks such as querying data, updating data, and defining database structures. SQL statements are composed of keywords, which are the instructions that tell the database what to do.

Example

SELECT * FROM employees;

Common SQL Keywords

• SELECT: Retrieves data from a database.
• INSERT: Adds new data to a database.
• UPDATE: Modifies existing data in a database.
• DELETE: Removes data from a database.
• CREATE: Creates a new table, database, index, or view.
• ALTER: Modifies an existing database object, such as a table.
• DROP: Deletes a table, database, index, or view.

2. SQL Clauses

SQL clauses are components of SQL statements. Some common clauses are:

• FROM: Specifies the table to retrieve data from.
• WHERE: Filters records based on specified conditions.
• GROUP BY: Groups rows that have the same values into summary rows.
• HAVING: Filters groups based on specified conditions.
• ORDER BY: Sorts the result set in ascending or descending order.

Example

SELECT first_name, last_name
FROM employees
WHERE last_name = 'Doe'
ORDER BY first_name ASC;

3. SQL Comments

Comments are used to explain sections of SQL statements or to disable the execution of SQL code. Comments are not executed as part of the SQL statement.

Example

-- This is a single-line comment

/*
This is a
multi-line comment
*/

SELECT first_name, last_name
FROM employees;

SQL Query Structure

1. SELECT Statement

The SELECT statement is used to query data from a database.

Syntax

SELECT column1, column2, ...
FROM table_name;

Example

SELECT first_name, last_name
FROM employees;

2. INSERT Statement

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

Syntax

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

Example

INSERT INTO employees (first_name, last_name, email)
VALUES ('Alice', 'Smith', 'alice.smith@example.com');

3. UPDATE Statement

The UPDATE statement is used to modify existing data in a table.

Syntax

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

Example

UPDATE employees
SET email = 'john.newemail@example.com'
WHERE id = 1;

4. DELETE Statement

The DELETE statement is used to remove rows from a table.

Syntax

DELETE FROM table_name
WHERE condition;

Example

DELETE FROM employees
WHERE id = 1;

5. CREATE TABLE Statement

The CREATE TABLE statement is used to create a new table in the database.

Syntax

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

Example

CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

6. ALTER TABLE Statement

The ALTER TABLE statement is used to modify an existing table's structure.

Syntax

ALTER TABLE table_name
ADD column_name datatype;

Example

ALTER TABLE employees
ADD department_id INT;

7. DROP TABLE Statement

The DROP TABLE statement is used to delete an existing table from the database.

Syntax

DROP TABLE table_name;

Example

DROP TABLE departments;

Conclusion

Understanding SQL syntax is fundamental for working with databases. This chapter covered the basic structure of SQL statements, including SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, ALTER TABLE, and DROP TABLE statements. With this knowledge, you can start writing SQL queries to interact with your database.