SQL Overview - MSDN Documentation

What is SQL?

SQL (Structured Query Language) is a standard programming language designed for managing and manipulating data held in a relational database management system (RDBMS). It is used to communicate with a database. SQL is an ANSI (American National Standards Institute) standard and is the most widely used database query language.

With SQL, you can:

Execute queries against a database
Retrieve data from a database
Insert new records in a database
Update records in a database
Delete records from a database
Create new databases
Create new tables in a database
Create new stored procedures in a database
Create views in a database
Set permissions to access tables, procedures, and views

Key SQL Concepts

Understanding the core concepts of SQL is crucial for effective database interaction.

1. Relational Databases

SQL is used with relational databases, which organize data into tables. Each table consists of rows (records) and columns (fields). Relationships between tables are established using primary keys and foreign keys.

2. Tables

A table is the fundamental structure for storing data in a relational database. It's a collection of related data entries organized in a tabular form.

Columns (Fields): Represent attributes of the data (e.g., `CustomerID`, `FirstName`, `LastName`).
Rows (Records): Represent individual entries or instances of the data (e.g., a specific customer's information).

3. Data Types

Each column in a table is assigned a specific data type, which determines the kind of data it can store (e.g., integers, strings, dates, booleans).

Common SQL data types include:

INT, INTEGER
VARCHAR(n), TEXT
DATE, DATETIME
DECIMAL(p, s), FLOAT
BOOLEAN

4. Keys

Primary Key: A column or set of columns that uniquely identifies each row in a table. It cannot contain NULL values and must be unique.
Foreign Key: A column or set of columns in one table that refers to the primary key in another table. It establishes a link or relationship between the two tables.

Common SQL Commands (DDL & DML)

SQL commands are broadly categorized into Data Definition Language (DDL) and Data Manipulation Language (DML).

Data Definition Language (DDL)

DDL commands are used to define, alter, and drop database objects.

CREATE: Used to create databases and tables.

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

ALTER: Used to modify the structure of a database object.
```
ALTER TABLE Customers
ADD Email VARCHAR(100);
```
DROP: Used to delete database objects.
```
DROP TABLE Customers;
```

Data Manipulation Language (DML)

DML commands are used to manage data within database objects.

SELECT: Used to retrieve data from a database.

SELECT FirstName, LastName
FROM Customers
WHERE CustomerID = 101;

INSERT: Used to insert new records into a table.

INSERT INTO Customers (CustomerID, FirstName, LastName)
VALUES (102, 'Jane', 'Doe');

UPDATE: Used to modify existing records in a table.

UPDATE Customers
SET LastName = 'Smith'
WHERE CustomerID = 102;

DELETE: Used to delete records from a table.

DELETE FROM Customers
WHERE CustomerID = 102;

SQL Clauses and Operators

SQL queries often involve clauses and operators to filter, sort, and combine data.

WHERE: Filters records.
ORDER BY: Sorts the result set.
GROUP BY: Groups rows that have the same values in specified columns.
HAVING: Filters groups based on a specified condition.
JOIN: Combines rows from two or more tables. Common types include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.
Operators: Comparison operators (=, >, <), logical operators (AND, OR, NOT), wildcard characters (%, _).

SQL Dialects

While SQL is a standard, different database systems (like MySQL, PostgreSQL, SQL Server, Oracle) have their own variations or "dialects" of SQL. These dialects may support slightly different syntax, functions, and features. However, the core commands and concepts remain largely consistent.

Next Steps

To deepen your understanding, explore the following: