MSDN Documentation

SQL Basics Tutorial

Welcome to the fundamental SQL tutorial for Microsoft Development Network (MSDN). This guide will introduce you to the core concepts of Structured Query Language (SQL), enabling you to interact with relational databases.

What is SQL?

SQL (Structured Query Language) is a standard language for managing and manipulating databases. It's used to communicate with a database. SQL is a declarative programming language that is used for managing data in relational database management systems (RDBMS). It allows you to query, insert, update, and delete data, as well as manage database structure.

Core SQL Commands

We'll start with the most common and essential SQL commands:

1. SELECT

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

-- Select all columns from the 'Customers' table
SELECT * FROM Customers;

-- Select specific columns from the 'Customers' table
SELECT CustomerName, ContactName, Country FROM Customers;

2. INSERT INTO

The INSERT INTO statement is used to add new records (rows) into a table.

-- Insert a new record into the 'Customers' table
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Synthetic', 'Skagen 21', 'Stavanger', '4006', 'Norway');

-- Insert a new record with only some columns specified
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Midwest Shipping', 'Chicago', 'USA');

3. UPDATE

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

-- Update the City for a specific customer
UPDATE Customers
SET City = 'Berlin'
WHERE CustomerName = 'Cardinal';

-- Update multiple fields for a customer
UPDATE Customers
SET Address = '300 West Main St', City = 'New York'
WHERE CustomerName = 'Alfreds Futterkiste';

4. DELETE

The DELETE statement is used to remove existing records from a table.

-- Delete a specific record
DELETE FROM Customers
WHERE CustomerName = 'Alfreds Futterkiste';

-- Delete all records from a table (use with caution!)
DELETE FROM Customers;

5. CREATE TABLE

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

-- Create a new table named 'Products'
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(255),
    SupplierID INT,
    CategoryID INT,
    UnitPrice DECIMAL(10, 2),
    UnitsInStock INT
);

Basic SQL Operations and Concepts

• Tables: The fundamental structure for storing data, consisting of rows and columns.
• Columns: Attributes of a table that define the type of data stored (e.g., CustomerName, ProductID).
• Rows: Records in a table, representing a single entry of data.
• Primary Key: A column (or set of columns) that uniquely identifies each row in a table.
• WHERE Clause: Used to filter records and extract only those that fulfill specified conditions.
• ORDER BY Clause: Used to sort the result-set in ascending or descending order.

Example: Filtering and Sorting Data

Let's retrieve customer names and their countries from customers in 'USA', sorted by name:

SELECT CustomerName, Country
FROM Customers
WHERE Country = 'USA'
ORDER BY CustomerName ASC;

Next Steps

This tutorial covered the absolute basics. In the next sections, we'll explore more advanced SQL concepts such as joins, subqueries, aggregate functions, and database management.