sql_syntax

Understanding Basic SQL Syntax

Database Tables: The Foundation of SQL

A relational database typically consists of one or more tables. Each table is a structured collection of data, identified by a unique name (e.g., Customers, Products, Orders). Tables are organized into rows (records) and columns (fields). Each row represents a single item or entity, and each column represents an attribute of that item.

For illustrative purposes, many SQL tutorials, including this one, may refer to schemas similar to the Northwind sample database. This database was historically bundled with Microsoft Access and SQL Server and provides a good set of related tables for practicing queries.

Example: A Customers Table Snippet

Consider a selection from a Customers table:

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 snippet shows five records (rows), each representing a customer. It has seven columns detailing attributes like CustomerID, CustomerName, etc.

SQL Statements: Communicating with the Database

Most interactions with a database are performed using SQL statements. These statements are commands that tell the database what action to perform, such as retrieving data, inserting new data, updating existing data, or deleting data.

For example, to select all records from the Customers table, you would use the following SQL statement:

SELECT * FROM Customers;

This tutorial will guide you through various SQL statements and their uses.

Key SQL Syntax Considerations

Case Sensitivity:

• SQL Keywords: Generally, SQL keywords (like SELECT, FROM, WHERE, UPDATE) are case-insensitive. So, SELECT is the same as select or SeLeCt.
• Table and Column Names: The case sensitivity of table and column names can vary depending on the database system and its configuration. For instance, PostgreSQL is case-sensitive by default if names are quoted, while MySQL’s behavior can depend on the underlying operating system. SQL Server is often case-insensitive by default but can be configured to be case-sensitive.
• Best Practice: For readability and consistency, it’s a common convention to write SQL keywords in UPPERCASE and user-defined names (tables, columns) in lowercase or snake_case (e.g., customer_orders).

Semicolons (;) after SQL Statements:

In many database systems, a semicolon is used to terminate an SQL statement. It’s the standard way to separate multiple SQL statements if you’re executing them in a single batch or script.

While some SQL interfaces or tools might execute a single statement without a semicolon, it’s good practice to include it, especially when writing scripts with multiple statements. This tutorial will use semicolons at the end of SQL statements.

SQL Comments:

Comments are used to explain parts of an SQL script and are ignored by the database engine.

• Single-line comments: Start with -- (two hyphens). Everything from -- to the end of the line is a comment. -- This is a single-line comment SELECT * FROM Orders; -- Retrieve all orders
• Multi-line comments: Start with /* and end with */. Can span multiple lines. /* This is a multi-line comment explaining the following query. */ SELECT ProductName, UnitPrice FROM Products;

SQL is Declarative:

SQL is a declarative language. This means you specify what data you want to retrieve or manipulate, not how to do it. The Database Management System (DBMS) has an optimizer that determines the most efficient way to execute your SQL statements.

Core SQL Commands Overview

Here are some of the most important categories and commands in SQL:

Data Query Language (DQL):

• SELECT - Extracts data from one or more tables.

Data Manipulation Language (DML):

• INSERT INTO - Inserts new data (rows) into a table.
• UPDATE - Modifies existing data in a table.
• DELETE - Deletes data (rows) from a table.

Data Definition Language (DDL):

• CREATE DATABASE - Creates a new database.
• ALTER DATABASE - Modifies database-level properties (often a DBA task).
• CREATE TABLE - Creates a new table within a database.
• ALTER TABLE - Modifies the structure of an existing table (e.g., add/drop columns, change data types).
• DROP TABLE - Deletes an entire table and its data.
• CREATE INDEX - Creates an index to improve query performance.
• DROP INDEX - Deletes an index.

Data Control Language (DCL):

• GRANT - Gives users access privileges to database objects.
• REVOKE - Removes user access privileges.

Transaction Control Language (TCL):

• COMMIT - Saves all changes made during the current transaction.
• ROLLBACK - Undoes changes made during the current transaction.
• SAVEPOINT - Sets a point within a transaction to which you can later roll back.

This overview provides a glimpse into the breadth of SQL. Subsequent sections will delve into these commands in more detail.