SQL Server Essentials: Database Management, Queries, Functions

SQL Server Training Guide

1. Introduction to SQL Server

What is SQL Server?
- SQL Server is a relational database management system (RDBMS) developed by Microsoft. It allows you to store, retrieve, and manipulate data using structured query language (SQL).
Installation and Setup
- Download and install SQL Server (choose the appropriate edition).
- Set up authentication modes (Windows or SQL Server authentication).

2. Creating Databases and Tables

Creating a Database
- Use the CREATE DATABASE statement to create a new database.
Creating Tables
- Define tables with columns, data types, and constraints.
- Example:
  CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), Department VARCHAR(50) );

3. Basic SQL Queries

SELECT Statement
- Retrieve data from a table:
  
  SELECT FirstName, LastName FROM Employees;
WHERE Clause
- Filter data based on conditions:
  
  SELECT * FROM Employees WHERE Department = ‘IT’;
ORDER BY Clause
- Sort data:
  SELECT * FROM Employees ORDER BY LastName ASC;
GROUP BY Clause
- Aggregate data:
  SELECT Department, COUNT(*) FROM Employees GROUP BY Department;
JOIN Clause
- Combine data from multiple tables:
  
  SELECT Orders.OrderID, Customers.CustomerName FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID; . . .

4. Data Manipulation

INSERT INTO Statement
- Add new records:
  
  INSERT INTO Employees (EmployeeID, FirstName, LastName, Department) VALUES (101, ‘John’, ‘Doe’, ‘HR’);
UPDATE Statement
- Modify existing records:
  
  UPDATE Employees SET Department = ‘Finance’ WHERE EmployeeID = 101;
DELETE Statement
- Remove records:
  
  DELETE FROM Employees WHERE EmployeeID = 101;

5. Advanced SQL Functions

Aggregate Functions
- COUNT, SUM, AVG, MAX, MIN:
  
  SELECT AVG(Salary) FROM Employees;
CASE Expression
- Conditional expressions:
  
  SELECT FirstName, CASE WHEN Salary > 50000 THEN ‘High’ ELSE ‘Low’ END AS SalaryCategory FROM Employees;
String Functions
- SUBSTRING, CONCAT:
  
  SELECT CONCAT(FirstName, ‘ ‘, LastName) AS FullName FROM Employees;
Date and Time Functions
- DATEPART, DATEDIFF:
  SELECT DATEPART(YEAR, HireDate) AS HireYear FROM Employees;

6. Stored Procedures and Functions

Stored Procedures
- Create reusable code blocks:
  
  CREATE PROCEDURE GetEmployeeCount AS BEGIN SELECT COUNT(*) FROM Employees; END;
User-Defined Functions
- Define custom functions:
  
  CREATE FUNCTION CalculateBonus(@Salary DECIMAL) RETURNS DECIMAL AS BEGIN RETURN @Salary * 0.1; END;

7. Indexes and Performance Optimization

8. Security and Permissions

User Accounts
- Create logins and users.
Permissions
- Grant or revoke permissions:
  
  GRANT SELECT ON Employees TO HRUser;

9. Backup and Restore

Backup Databases
- Regularly back up your databases.
Restore from Backups
- Restore sample databases using the provided backup files:
  - AdventureWorks OLTP and DW
  - WideWorldImporters

10. Useful URLs:

edvaldo b. guimarães filho