SQL Essentials Tutorial

1. What is SQL?

SQL is a standard language for accessing and manipulating databases. It's designed for managing data held in a relational database management system (RDBMS). Most RDBMSs like MySQL, PostgreSQL, SQL Server, Oracle, etc., use SQL.

Key characteristics of SQL:

• Declarative: You tell the database *what* you want, not *how* to get it.
• Standardized: While there are variations (dialects), the core syntax is largely the same across systems.
• Powerful: Capable of complex data retrieval, insertion, updates, and deletions.

2. Common SQL Commands

We'll cover some of the most frequently used SQL commands. These are often categorized into Data Definition Language (DDL) and Data Manipulation Language (DML).

2.1. SELECT: Retrieving Data

The SELECT statement is used to query the database and retrieve data that matches criteria you specify.

Syntax:

SELECT column1, column2, ... FROM table_name WHERE condition;

Example: Get all columns for all customers from the 'Customers' table:

SELECT * FROM Customers;

Example: Get only the 'CustomerName' and 'City' for customers in 'London':

SELECT CustomerName, City FROM Customers WHERE City = 'London';

2.2. INSERT INTO: Adding Data

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

Syntax:

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

Example: Add a new customer:

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Synthetic', 'Skagen 21', 'Stavanger', '4006', 'Norway');

If you are adding values for all columns, you may omit the column names:

INSERT INTO Customers VALUES ('Cardinal', 'Tom B. Synthetic', 'Skagen 21', 'Stavanger', '4006', 'Norway');

2.3. UPDATE: Modifying Data

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

Syntax:

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

Example: Change the city of the customer with 'CustomerID' 10:

UPDATE Customers SET City = 'Berlin' WHERE CustomerID = 10;

2.4. DELETE: Removing Data

The DELETE statement is used to delete records from a table.

Syntax:

DELETE FROM table_name WHERE condition;

Example: Delete the customer named 'Alfreds Futterkiste':

DELETE FROM Customers WHERE CustomerName = 'Alfreds Futterkiste';

To delete all rows from a table without deleting the table itself, use:

DELETE FROM table_name;

Or the often faster (but also table-specific) command:

TRUNCATE TABLE table_name;

3. Database Structure: Tables and Columns

Databases organize data into tables. Each table represents a type of entity (e.g., Customers, Products, Orders). Tables are composed of columns (attributes) and rows (records).

3.1. CREATE TABLE: Creating Tables

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 a simple 'Products' table:

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(255) NOT NULL,
    Price DECIMAL(10, 2),
    StockQuantity INT DEFAULT 0
);

Common Datatypes:

• INT: Integer
• VARCHAR(size): Variable-length string
• DECIMAL(p, s): Fixed-point number (p = precision, s = scale)
• DATE: Date
• BOOLEAN: True/False

Common Constraints:

• PRIMARY KEY: Uniquely identifies each row.
• NOT NULL: Ensures a column cannot have a NULL value.
• UNIQUE: Ensures all values in a column are unique.
• DEFAULT value: Sets a default value if no value is specified.

3.2. ALTER TABLE: Modifying Tables

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

Example: Add a 'Category' column to the 'Products' table:

ALTER TABLE Products ADD Category VARCHAR(100);

Example: Drop the 'StockQuantity' column:

ALTER TABLE Products DROP COLUMN StockQuantity;

3.3. DROP TABLE: Deleting Tables

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

Syntax:

DROP TABLE table_name;

Example:

DROP TABLE Products;

4. Conclusion

This tutorial covered the basics of SQL, including retrieving, inserting, updating, and deleting data, as well as creating and managing table structures. SQL is a vast language, and mastering it requires practice. Keep exploring and experimenting with different commands!

For more advanced topics like Joins, Subqueries, Indexes, and Stored Procedures, please refer to the full MSDN SQL documentation.