SQL Server Basics

Welcome to the SQL Server Basics tutorial. This section will guide you through the fundamental concepts and operations within Microsoft SQL Server.

What is SQL Server?

Microsoft SQL Server is a relational database management system (RDBMS) developed by Microsoft. It is a software product whose primary function is to store and retrieve data as requested by other software applications.

Key Concepts

Databases: A structured collection of data.
Tables: The fundamental structures for storing data in a relational database. Each table consists of rows and columns.
Columns: Represent specific attributes of the data (e.g., 'CustomerID', 'FirstName').
Rows: Represent individual records or entries in a table.
SQL (Structured Query Language): The standard language for managing and querying relational databases.

Getting Started

Before you can start working with SQL Server, you need to ensure you have it installed and a management tool like SQL Server Management Studio (SSMS) is available. If you haven't done this yet, please refer to the Setup & Installation tutorial.

Basic SQL Commands

We'll cover some of the most common SQL commands used in SQL Server:

Creating a Database

To create a new database, you use the CREATE DATABASE statement:

CREATE DATABASE MyNewDatabase;

Creating a Table

Tables are created within a database. Here's an example of creating a simple 'Customers' table:

USE MyNewDatabase; -- Select the database to work with

            CREATE TABLE Customers (
                CustomerID INT PRIMARY KEY,
                FirstName VARCHAR(50),
                LastName VARCHAR(50),
                Email VARCHAR(100),
                JoinDate DATE
            );

In this example:

INT is a data type for integers.
VARCHAR(n) is a data type for variable-length strings, with 'n' specifying the maximum length.
DATE is a data type for dates.
PRIMARY KEY uniquely identifies each record in the table.

Inserting Data

Use the INSERT INTO statement to add records to a table:

INSERT INTO Customers (CustomerID, FirstName, LastName, Email, JoinDate)
            VALUES (1, 'John', 'Doe', 'john.doe@example.com', '2023-01-15');

            INSERT INTO Customers (CustomerID, FirstName, LastName, Email, JoinDate)
            VALUES (2, 'Jane', 'Smith', 'jane.smith@example.com', '2023-02-20');

Querying Data (SELECT)

The SELECT statement is used to retrieve data from one or more tables:

To select all columns from the Customers table:

SELECT * FROM Customers;

To select specific columns:

SELECT FirstName, LastName, Email FROM Customers;

To filter data using the WHERE clause:

SELECT FirstName, LastName FROM Customers WHERE CustomerID = 1;

Updating Data

Use the UPDATE statement to modify existing records:

UPDATE Customers
            SET Email = 'john.doe.updated@example.com'
            WHERE CustomerID = 1;

Deleting Data

Use the DELETE FROM statement to remove records:

DELETE FROM Customers
            WHERE CustomerID = 2;

Important: Always be cautious when using UPDATE and DELETE statements, especially without a WHERE clause, as they can affect many records.

A Simple Exercise

Exercise:

1. Create a new database named CompanyDB.

2. Inside CompanyDB, create a table named Employees with the following columns: EmployeeID (INT, PRIMARY KEY), FullName (VARCHAR(100)), Department (VARCHAR(50)), and Salary (DECIMAL(10, 2)).

3. Insert at least two employee records.

4. Select all employees from the Employees table.

Next Steps

Now that you're familiar with the basics, you're ready to dive deeper into more advanced SQL concepts. The next tutorial covers T-SQL Fundamentals.