Hot Posts

SQL Server Notes

vmstudypoint
SQL Server Notes

SQL Server Short Notes

Announcement: CLASS NOTES & SQL SERVER PDF available.

Table of Contents

Introduction to Databases

  • Database: Organized collection of interrelated data (e.g., bank, university).
  • Types: OLTP (Online Transaction Processing) for daily transactions, OLAP (Online Analytical Processing) for analysis.
  • CRUD Operations: Create, Read, Update, Delete.

DBMS, RDBMS, ORDBMS

DBMS (Database Management System)

  • Software to create and manage databases.
  • Acts as an interface between user and database.

RDBMS (Relational DBMS)

  • Introduced by E.F. Codd (12 Codd rules).
  • Data organized in tables (rows and columns).
  • Supports primary keys, foreign keys, and relationships.
  • Features: Easy access, less redundancy, security, data integrity, sharing, transactions.

ORDBMS (Object Relational DBMS)

  • Combines RDBMS with OOPS concepts (reusability).
  • Examples: SQL Server, Oracle, PostgreSQL.

DB Development Life Cycle

  1. Analysis
  2. Design (ER Model, Normalization)
  3. Development (using RDBMS tools)
  4. Testing (manual/automation)
  5. Deployment
  6. Maintenance

SQL Server Overview

  • RDBMS software from Microsoft for database development and administration.
  • Client/Server architecture: Server (SQL Server installed), Client (SSMS, etc.).
  • Versions: 1.1 (1991) to 2022.
  • Supports T-SQL (Transact-SQL), a Microsoft extension of SQL.
  • Provides security, backup, replication, and high availability features.
Download: SQL Server & SSMS

Datatypes in SQL Server

Type Description Example
char(size) Fixed-length ASCII characters (up to 8000) char(10)
varchar(size) Variable-length ASCII characters (up to 8000) varchar(10)
varchar(max) Variable-length, up to 2GB varchar(max)
nchar, nvarchar, nvarchar(max) Unicode characters nvarchar(20)
tinyint, smallint, int, bigint Integer types int
numeric(p,s), decimal(p,s) Numbers with/without decimals numeric(7,2)
money, smallmoney Currency types money
date, time, datetime Date and/or time date
bit Boolean (0 or 1) bit
float, real Floating point numbers float
binary, varbinary Binary data (e.g., images, files) varbinary(max)

SQL Commands

DDL (Data Definition Language)

  • CREATE, ALTER, DROP, TRUNCATE

DML (Data Manipulation Language)

  • INSERT, UPDATE, DELETE, MERGE

DQL/DRL (Data Query/Retrieval Language)

  • SELECT

TCL (Transaction Control Language)

  • COMMIT, ROLLBACK, SAVE TRANSACTION

DCL (Data Control Language)

  • GRANT, REVOKE

Operators

  • Arithmetic: +, -, *, /, %
  • Relational: =, !=, <, >, etc.
  • Logical: AND, OR, NOT
  • Special: IN, BETWEEN, LIKE, IS NULL

Practical Examples

Creating a Table

CREATE TABLE emp (
    empid    SMALLINT,
    ename    VARCHAR(10),
    job      VARCHAR(10),
    sal      SMALLMONEY,
    hiredate DATE,
    dept     VARCHAR(10)
);

Inserting Data

INSERT INTO emp VALUES (100, 'sachin', 'clerk', 4000, '2025-05-19', 'hr');
INSERT INTO emp VALUES (101, 'arvind', 'manager', 8000, GETDATE(), 'it');

Selecting Data

SELECT ename, sal FROM emp;
SELECT * FROM emp WHERE sal > 5000;

Updating Data

UPDATE emp SET sal = sal + 1000 WHERE empid = 100;

Deleting Data

DELETE FROM emp WHERE empid = 100;

Altering Table

ALTER TABLE emp ADD gender CHAR(1);
ALTER TABLE emp DROP COLUMN gender;

Pattern Matching

SELECT * FROM emp WHERE ename LIKE 's%';
SELECT * FROM emp WHERE ename LIKE '%a%';

Sorting and Limiting Results

SELECT TOP 5 * FROM emp ORDER BY sal DESC;

Distinct and Aggregate

SELECT DISTINCT job FROM emp;
SELECT COUNT(*) FROM emp;

Normalization

  • Normalization: Process of organizing data to reduce redundancy and improve data integrity.
  • 1NF (First Normal Form): No repeating groups or arrays. Each cell contains atomic values.
  • 2NF (Second Normal Form): 1NF + no partial dependency (every non-key attribute fully depends on primary key).
  • 3NF (Third Normal Form): 2NF + no transitive dependency (non-key attributes depend only on primary key).
  • BCNF (Boyce-Codd Normal Form): Stricter version of 3NF.
Tip: Normalize tables to avoid anomalies during insert, update, and delete operations.

Keys in SQL

  • Primary Key: Uniquely identifies each row in a table. Cannot be NULL.
  • Foreign Key: Enforces referential integrity between tables. Points to primary key in another table.
  • Unique Key: Ensures all values in a column are unique (can have one NULL).
  • Composite Key: Combination of two or more columns to uniquely identify a row.
  • Candidate Key: Column(s) that can qualify as a primary key.
  • Alternate Key: Candidate keys not chosen as primary key.

Joins

  • INNER JOIN: Returns rows with matching values in both tables.
  • LEFT JOIN (LEFT OUTER JOIN): Returns all rows from left table and matched rows from right table.
  • RIGHT JOIN (RIGHT OUTER JOIN): Returns all rows from right table and matched rows from left table.
  • FULL JOIN (FULL OUTER JOIN): Returns rows when there is a match in one of the tables.
  • CROSS JOIN: Returns Cartesian product of both tables.
  • SELF JOIN: Table joined with itself.
-- Example: INNER JOIN
SELECT emp.ename, dept.dname
FROM emp
INNER JOIN dept ON emp.dept = dept.deptno;

Frequently Asked Questions

What is the difference between DELETE and TRUNCATE?
  • DELETE removes rows one by one and can have a WHERE clause. It is logged and can be rolled back.
  • TRUNCATE removes all rows quickly, cannot have a WHERE clause, and resets identity columns. It cannot be rolled back if outside a transaction.
What is a view in SQL Server?
A view is a virtual table based on the result of a SELECT query. It does not store data itself but provides a way to simplify complex queries and enhance security.
What is an index?
An index improves the speed of data retrieval operations on a table at the cost of additional space and slower writes. Common types: clustered and non-clustered.
What is a stored procedure?
A stored procedure is a precompiled collection of SQL statements that can be executed as a single unit. Useful for code reuse, security, and performance.
What is a transaction?
A transaction is a sequence of operations performed as a single logical unit of work. Transactions follow ACID properties (Atomicity, Consistency, Isolation, Durability).

Useful Resources

Tip: Use SP_HELP <tablename> to view table structure. Use SP_RENAME to rename tables or columns.