1. SQL Introduction

What is a Database?

A database is a system used to store, retrieve, and manage data efficiently. Unlike file-based storage or RAM, a database allows structured access to data, such as retrieving a user by their ID. When a database is created, it initially occupies 72 KB. This includes storage for eight pages, each 8 KB in size, along with an 8 KB Index Allocation Map (IAM).

Example: Creating a Database

CREATE DATABASE ExampleDB;

What is SQL?

SQL, or Structured Query Language, is a declarative language used to interact with databases. Instead of specifying how to perform operations, SQL lets you state what you want, and it handles the details. For example, retrieving all users from a table is as simple as a SELECT statement.

SQL Compared to Imperative Languages

Declarative languages like SQL specify the outcome, while imperative languages like C# or C++ require detailed step-by-step instructions. For instance, SQL can fetch all records with a simple query, whereas in C#, you need to manually establish a connection, execute the query, and fetch results.

Database Management Systems (DBMS)

A DBMS manages data in a structured format. It performs essential tasks such as retrieving, modifying, and protecting data while organizing it into units called pages. This ensures efficiency and consistency in data management.

Data Models

Hierarchical Model

Data is organized in a tree-like structure where navigation is linear. For example, navigating from "Electronics" to "LCD" is possible, but reversing this path is complex.

Network Model

Data relationships are represented through references. While it supports many-to-many relationships, excessive references can make the model cumbersome.

Relational Model

Data is organized into tables with rows and columns. Relationships between entities are maintained using keys such as Primary Keys and Foreign Keys.

Non-Relational Model (NoSQL)

Designed for unstructured or semi-structured data, this model is commonly used for scalable and high-performance applications.

Object-Relational Model

Combines object-oriented concepts with relational database structures, allowing flexibility when dealing with complex data.

Distributed Database Model

Data is stored across multiple locations, improving redundancy and ensuring availability.

Example: Creating a Relational Table

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Name NVARCHAR(50),
    Email NVARCHAR(100)
);

Database Servers

File Server databases store data locally on the same machine as the application. This is common in offline scenarios. Client-Server databases store data on remote servers and require an internet connection for access. Embedded servers store data directly on a device, making it accessible offline, such as in mobile apps.

SQL Commands

Data Definition Language (DDL)

DDL commands define and modify database structures. Examples include CREATE for adding new tables, ALTER for modifying tables, DROP for deleting objects, and TRUNCATE for quickly clearing data from a table.

Data Manipulation Language (DML)

DML commands handle the manipulation of data. Examples include SELECT for querying data, INSERT for adding new rows, UPDATE for modifying existing rows, and DELETE for removing rows.

Data Control Language (DCL)

DCL commands manage user permissions. Examples include GRANT for providing access rights and REVOKE for removing access rights.

Example: DML Query

SELECT * FROM Users WHERE Name = 'John';

SQL File Types

Master Database Files (MDF) store primary data such as rows, columns, and tables. Log Database Files (LDF) log transactions and activities, which are crucial for tracking changes and managing errors.

Key Database Concepts

Columns represent attributes of data, while rows represent individual records. Primary Keys ensure each record is unique, and Foreign Keys establish relationships between tables.

Data Types

Common data types include char(n) for fixed-length text, varchar(n) for variable-length text, nchar(n) for fixed-length Unicode text, and nvarchar(n) for variable-length Unicode text. Unicode compatibility allows storage of up to 65,536 unique characters.

Example: Data Type Declaration

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100),
    Price DECIMAL(10, 2)
);

Indexing

Indexes improve query performance by organizing data for efficient retrieval. A clustered index arranges the physical order of data in a table. By default, a Primary Key creates a clustered index.

Example: Creating an Index

CREATE INDEX IX_ProductName ON Products(ProductName);

Transactions in SQL

Transactions ensure that commands are executed sequentially without interference. The GO keyword can be used to guarantee that one command completes before the next starts.

Example: Transaction with Commit and Rollback

BEGIN TRANSACTION;
UPDATE Users SET Name = 'Jane' WHERE UserID = 1;

IF @@ERROR <> 0
    ROLLBACK TRANSACTION;
ELSE
    COMMIT TRANSACTION;

Constraints

Primary Keys ensure records are unique, Foreign Keys link tables, Check constraints enforce conditions, and Unique constraints ensure column values are distinct.

Example: Adding Constraints

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    UserID INT FOREIGN KEY REFERENCES Users(UserID),
    TotalAmount DECIMAL(10, 2) CHECK (TotalAmount > 0)
);

Reference

The content in this document is based on the original notes provided in Azerbaijani. For further details, you can refer to the original document using the following link:

Original Note - Azerbaijani Version