SQL Server Basics Tutorial

Introduction to SQL Server

Microsoft SQL Server is a relational database management system (RDBMS) that stores and retrieves data as requested by other software applications. This tutorial will guide you through the fundamental concepts and operations of SQL Server.

SQL (Structured Query Language) is the standard language for interacting with relational databases. We'll be using SQL commands to manage and query our data.

Setting Up SQL Server

To follow along, you'll need a working installation of SQL Server. You can download SQL Server Express Edition for free from the official Microsoft website. Installation typically involves running an executable and following the on-screen prompts.

For development and learning, SQL Server Management Studio (SSMS) is the recommended tool. It provides a graphical interface for managing your SQL Server instances, databases, and running queries.

Connecting to SQL Server

Once installed, you'll connect to your SQL Server instance using SSMS. You'll need the server name (often 'localhost' or a named instance like '.\SQLEXPRESS') and authentication details (Windows Authentication or SQL Server Authentication).

Basic Queries (SELECT)

The most fundamental SQL statement is `SELECT`, used to retrieve data from a database. To select all columns from a table, use `*`.

Example: Select all data from a table named 'Customers':

SELECT * FROM Customers;

To select specific columns, list them after `SELECT`.

Example: Select the 'FirstName' and 'LastName' from the 'Customers' table:

SELECT FirstName, LastName FROM Customers;

Filtering Data (WHERE)

The `WHERE` clause is used to filter records, returning only those that meet specified criteria.

Example: Select customers from 'USA':

SELECT * FROM Customers WHERE Country = 'USA';

You can use various operators like `=`, `!=`, `>`, `<`, `>=`, `<=`, `LIKE`, `IN`, `BETWEEN`.

Example: Select products with a price greater than 50:

SELECT ProductName, Price FROM Products WHERE Price > 50;

Sorting Data (ORDER BY)

The `ORDER BY` clause sorts the result set in ascending or descending order.

Example: Select all customers, sorted by city name in ascending order:

SELECT * FROM Customers ORDER BY City ASC;

Use `DESC` for descending order.

Example: Select products, sorted by price in descending order:

SELECT ProductName, Price FROM Products ORDER BY Price DESC;

Creating Tables (CREATE TABLE)

The `CREATE TABLE` statement is used to create new tables in a database. You define the table name and its columns, along with their data types.

Example: Create a simple 'Products' table:


CREATE TABLE Products (
    ProductID INT PRIMARY KEY IDENTITY(1,1),
    ProductName VARCHAR(255) NOT NULL,
    Price DECIMAL(10, 2),
    Category VARCHAR(100)
);

INT is for integers, VARCHAR(n) is for variable-length strings, DECIMAL(p, s) is for decimal numbers, PRIMARY KEY uniquely identifies each record, and IDENTITY(1,1) automatically increments the ID.

Inserting Data (INSERT INTO)

The `INSERT INTO` statement is used to add new records into a table.

Example: Insert a new product:


INSERT INTO Products (ProductName, Price, Category)
VALUES ('Laptop', 1200.50, 'Electronics');

If you provide values for all columns in the correct order, you can omit the column names.


INSERT INTO Products VALUES ('Keyboard', 75.00, 'Electronics');

Updating Data (UPDATE)

The `UPDATE` statement modifies existing records in a table. Always use a `WHERE` clause to specify which records to update.

Example: Update the price of the 'Laptop':


UPDATE Products
SET Price = 1250.00
WHERE ProductName = 'Laptop';

Deleting Data (DELETE)

The `DELETE` statement removes records from a table. Again, use a `WHERE` clause to avoid deleting all records.

Example: Delete the 'Keyboard' product:


DELETE FROM Products
WHERE ProductName = 'Keyboard';

Summary

This tutorial covered the basics of SQL Server, including connecting to the server, executing `SELECT`, `INSERT`, `UPDATE`, and `DELETE` statements, filtering with `WHERE`, and sorting with `ORDER BY`. Understanding these fundamental operations is crucial for working with relational databases.