SQL Syntax and Statements

Overview of SQL Statements

SQL (Structured Query Language) enables users to perform various operations on a database using statements composed of intuitive and straightforward keywords. These operations include querying, updating, inserting, and managing data.

Example of an SQL Statement

The following SQL statement retrieves all records from a table named Customers:

SELECT * FROM Customers;

Database Tables

A database typically consists of one or more tables, each identified by a unique name (e.g., Customers, Orders).
Each table contains records (rows), which hold the actual data, organized into columns.

Example: Customers Table (Northwind Database)

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK
5	Berglunds snabbköp	Christina Berglund	Berguvsvägen 8	Luleå	S-958 22	Sweden

This table contains:

5 records (rows) representing customers.
7 columns representing customer details: CustomerID, CustomerName, ContactName, Address, City, PostalCode, Country.

Key Points About SQL Syntax

SQL Keywords Are Case-Insensitive
- For example, select and SELECT are the same.
- By convention, keywords are written in UPPER-CASE in this tutorial for clarity.
Semicolon Usage
- Some database systems require a semicolon (;) to terminate SQL statements.
- The semicolon is especially useful when executing multiple SQL statements in a single call to the server.
- Example:
```
SELECT * FROM Customers;
SELECT * FROM Orders;
```

Essential SQL Commands

Data Manipulation Commands

SELECT: Extracts data from a database.
```
SELECT * FROM Customers;
```

INSERT INTO: Adds new data to a table.

INSERT INTO Customers (CustomerName, ContactName) VALUES ('New Customer', 'John Doe');

UPDATE: Modifies existing records.

UPDATE Customers SET ContactName = 'Jane Doe' WHERE CustomerID = 1;

DELETE: Removes records from a table.

DELETE FROM Customers WHERE CustomerID = 5;

Data Definition Commands

CREATE DATABASE: Creates a new database.
```
CREATE DATABASE MyDatabase;
```
ALTER DATABASE: Modifies database properties.

CREATE TABLE: Creates a new table.

CREATE TABLE Orders (
    OrderID int,
    OrderDate date,
    CustomerID int
);

ALTER TABLE: Modifies the structure of an existing table.
```
ALTER TABLE Orders ADD ColumnName datatype;
```
DROP TABLE: Deletes a table.
```
DROP TABLE Orders;
```

Index Commands

CREATE INDEX: Creates an index for faster searches.

CREATE INDEX idx_customer_name ON Customers (CustomerName);

DROP INDEX: Deletes an index.

Understanding and mastering these SQL commands is essential for working with relational databases, enabling efficient and organized data management. This knowledge forms the foundation of all database-related tasks, from basic queries to advanced operations.