Day 1: SQL Server Fundamentals

1. Introduction

Welcome to Day 1! Data is the backbone of almost every modern application, and SQL Server (developed by Microsoft) is one of the most powerful and widely used tools to manage it.

Today, we are laying the groundwork. We won't just learn how to write code; we will learn how data is structured. By the end of this session, you will know how to create a database from scratch, build tables to hold your data, and apply strict rules (constraints) to ensure your data stays accurate.

2. Database Concepts

Why Database?

Before databases, organizations stored data in flat files (like spreadsheets or text documents). While fine for personal use, traditional files fail when handling complex, real-world business applications. We use databases to solve these critical problems:

• Massive Scalability: Spreadsheets crash with too much data. Databases are engineered to instantly search and manage millions of records without slowing down.
• Data Integrity: Databases enforce strict rules. For example, they will reject a text entry in a "Date of Birth" field, ensuring your data is never polluted with bad inputs.
• Concurrent Access: Thousands of users can read and write data at the exact same millisecond without locking each other out or corrupting the system.
• Advanced Security: Databases allow for granular access control. You can restrict a user so they can only view specific tables or even specific rows, unlike a file which is "all or nothing" access.
• Eliminating Redundancy: Instead of duplicating a customer's details on every order, databases link related data together. Update an address once, and it reflects everywhere.
• Crash Recovery: If the server loses power during an update, the database uses transaction logs to recover the data safely, preventing permanent file corruption.

What is a Database?

A database is a systematic or organized collection of related information that is stored in such a way that it can be easily accessed, managed and updated.

• Data is stored in tables (rows and columns).
• Examples of databases: MySQL, SQL Server, MongoDB, Oracle.

Applications of Databases

• Railways (ticket booking systems)
• Libraries (book inventory and borrower records)
• Banking (transactions, customer accounts)
• Gaming (player profiles, scores)
• Social media (user profiles, posts, messages)

Components of database:

• Hardware: Physical electronic devices such as storage devices, I/O devices, and many more.
• Software: Programs for managing and controlling the overall database. DBMS itself is software
• Data: It is the information that is gathered, stored, accessed and processed by a DBMS.
• Procedure: It is the specific set of instructions and rules to use a database for designing and running the DBMS, as well as to instruct users on how to operate and manage it.
• Database access language: To enter new data or update or retrieve the data from the database, you can write commands in the database access language.

Types of Databases:

• Relational Database (RDBMS): Data in tables (rows & columns)
• Object-Oriented Database: Data stored as objects (like in programming)
• Distributed Database: Stored across multiple locations/networks
• Centralized Database: Stored in one central location (e.g., server)
• Operational Database (OLTP): Handles real-time transactions
• Data Warehouse: Stores large amounts of historical data for analytics

Advantages of Databases:

• Reduces data redundancy
• Improves data security
• Ensures data consistency
• Minimizes errors and cost of storage
• Provides fast data access
• Maintains high data integrity

3. DBMS vs Database

What is DBMS(Database Management System)?

A DBMS is software that helps manage a database.
It allows us to store, retrieve, and manipulate data efficiently.

Advantages of DBMS:

• Stores data neatly and efficiently
• Provides security for sensitive data
• Reduces data inconsistency
• Simplifies handling of data
• Ensures fast data access

Database vs. DBMS

• A databse is just a collection of organized data.
• A DBMS is software that help you create, manage and interact with Databases

4. Database Languages

What is Database Language?

We need some special kind of programming languages to make the DBMS software understand our needs and manage the data stored in the databases accordingly. These programming languages are known as database languages or query languages.

Types of Database Languages

• Data Definition Language (DDL) → Defines & manages structure
  • Commands: CREATE, ALTER, DROP, RENAME, TRUNCATE
• Data Manipulation Language (DML) → Manages actual data
  • Commands: INSERT, UPDATE, DELETE, MERGE
• Data Query Language (DQL) → Retrieves data from the database
  • Command: SELECT
• Data Control Language (DCL) → Controls user access & privileges
  • Commands: GRANT, REVOKE
• Transaction Control Language (TCL) → Manages database transactions
  • Commands: COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION

4. Database Operations

Creation, Alteration, Dropping

• Create database: CREATE DATABASE [DBName]
• Rename database:
  • query method: ALTER DATABASE [OldDBName] MODIFY NAME = [NewDBName];
  • Stored Procedure Method: EXEC sp_renamedb 'Old_DBName', 'New_DBName';
• Delete database: DROP [DBName]
• User modes:
  • Single user mode: ALTER DATABASE [DBName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  • Multi user mode: ALTER DATABASE [DBName] SET MULTI_USER;

Single User Mode vs Multi-user Mode

In SQL Server, Single-User Mode (SINGLE_USER) is a maintenance-focused state that restricts access to only one person or process at a time, often used for critical administrative tasks like restoring the master database or performing schema repairs. When active, it terminates all other open connections, sometimes requiring the WITH ROLLBACK IMMEDIATE clause to force the closure of existing sessions. In contrast, Multi-User Mode (MULTI_USER) is the standard production state that allows multiple simultaneous connections, enabling concurrent data access and collaboration across an organization.

5. Table Operations & Constraints

Table Creation and Operation

[Provide syntax for CREATE TABLE, ALTER TABLE, DROP TABLE...]

Create Table

• CREATE TABLE [schema_name.][table_name] (
  col1 datatype [constraint],
  col2 datatype [constraint],
  ...
  coln datatype [constraint]
  );

Insert data into table

1. specifying column names

• INSERT INTO [table_name] (col1, col2, ......, coln) VALUES (val1, Val2, ...., valn);

2. directly providing values

• INSERT INTO [table_name] VALUES (val1, Val2, ...., valn);

Update data in a Table

• UPDATE [table_name] SET Col1 = Val1, Col2 = Val2 WHERE Condition;

Delete data in a Table

• DELETE FROM [table_name] WHERE Condition;

Truncate Table

• TRUNCATE TABLE [table_name];

DROP TABLE

• DROP TABLE [table_name];

Key points:

• Omitting the WHERE clause will update all rows.
• Omitting the WHERE clause will delete all rows but keep the table structure intact.
• Truncate is used to remove all records from a table quickly. It is more efficient than DELETE because it uses fewer system and transaction log

Constraints

• Primary Key: Uniquely identifies a record.
  • ColumnName DataType PRIMARY KEY


• Foreign Key: Links to a primary key in another table.
  • FOREIGN KEY (LocalCol) REFERENCES ParentTable(ParentCol)


• Unique: Ensures all values in a column are different.
  • ColumnName DataType UNIQUE


• Default: Sets a default value if none is specified.
  • ColumnName DataType DEFAULT 'Value'


• Check: Ensures values in a column satisfy a specific condition.
  • ColumnName DataType CHECK (Condition)


• Note:
  • Above query only to be used with new table
  • To add Constraints after table is created use alter query
    • ALTER TABLE [table_name] ADD CONSTRAINT [constraint_name] CONSTRAINT
  • it is wise to decide everything at schema design.

Cascading Referential Integrity

In SQL Server, cascading referential integrity dictates what happens to child records when a parent key is updated or deleted. While the default behavior blocks these changes to prevent orphaned data, cascading rules automatically propagate the updates or deletions across all related tables.

When defining a Foreign Key in SQL Server, you can specify one of four behaviors for ON DELETE and ON UPDATE operations:

• No Action (Default): The database engine raises an error and rolls back the change if it would violate referential integrity.
• Cascade: If a row is deleted or updated in the parent table, the corresponding rows in the child (referencing) table are also deleted or updated.
• Set NULL: All foreign key values in the child table are set to NULL when the parent row is deleted or updated. This requires the child columns to be nullable.
• Set Default: All foreign key values in the child table are set to their defined default value. This requires the columns to have a Default Constraint.

Implementation Methods SSMS

Navigate to the child table in Object Explorer.
Expand the Keys folder, right-click the foreign key, and select Modify.
In the Foreign Key Relationships window, expand INSERT And UPDATE Specification.
Set the Delete Rule or Update Rule to your desired action.