SQL SERVER INTERVIEW PREPARATION

SQL Server Notes

A Quick Reference Guide for SQL Server Concepts

SQL Server Overview

SQL (Structured Query Language)
T-SQL (Transact-SQL)

What is a Database?

A database is an organized collection of interrelated data.
Examples:

Bank DB: customers, accounts, transactions
University DB: students, courses, faculty

Types of Databases

OLTP DB (Online Transaction Processing): For day-to-day transactions
OLAP DB (Online Analytical Processing): For analysis

OLTP is for running business; OLAP is for analyzing business.

CRUD Operations

Create
Read
Update
Delete

DBMS & RDBMS

DBMS (Database Management System)

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

Evolution of DBMS

1960: FMS (File Management System)
1970: HDBMS (Hierarchical), NDBMS (Network)
1980: RDBMS (Relational)
1990: ORDBMS (Object Relational)

RDBMS (Relational DBMS)

Concepts introduced by E.F. Codd (12 Codd Rules)
Data organized in tables (rows & columns)
Every table must have a Primary Key
Tables relate using Foreign Keys

custid	name	city
100	Sachin	Mum
101	Rahul	Del
102	Vijay	Hyd

Primary Key: Uniquely identifies each row.
Foreign Key: Links one table to another.

RDBMS Features

Easy access & manipulation
Less redundancy
More security
Data consistency & integrity
Supports data sharing & transactions

Popular RDBMS Software

SQL Server (Microsoft)
Oracle
MySQL
PostgreSQL
Amazon RDS

NoSQL Databases

MongoDB
Cassandra

ORDBMS (Object Relational DBMS)

Combines RDBMS & OOPS (reusability)
Examples: SQL Server, Oracle, PostgreSQL

DB Development Life Cycle

Analysis
Design (ER Model, Normalization)
Development
Testing
Deployment
Maintenance

SQL Server

RDBMS software from Microsoft
Used for database development & administration

Versions

Version	Year
SQL Server 1.1	1991
SQL Server 4.2	1993
SQL Server 6.0	1995
SQL Server 6.5	1996
SQL Server 7.0	1998
SQL Server 2000	2000
SQL Server 2005	2005
SQL Server 2008	2008
SQL Server 2012	2012
SQL Server 2014	2014
SQL Server 2016	2016
SQL Server 2017	2017
SQL Server 2019	2019
SQL Server 2022	2022

Client/Server Architecture

Server: Where SQL Server is installed (manages DB & Instance)
Client: Connects to server, submits requests, receives responses
Client Tool: SSMS (SQL Server Management Studio)

SQL

Structured Query Language
Used to communicate with SQL Server
Common to all relational DB software

Download SQL Server: SQL Server Downloads
Download SSMS: SSMS Download

SQL Commands

SQL Sub-Languages

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

Data & Data Definition

empid	ename	sal
1	A	6000

Use DDL for data definition (structure), DML for data manipulation.

Datatypes in SQL Server

Character Types

char(size): Fixed length (up to 8000 chars)
varchar(size): Variable length (up to 8000 chars)
varchar(max): Up to 2GB
nchar/nvarchar: Unicode support

Integer Types

Type	Bytes	Range
TINYINT	1	0 to 255
SMALLINT	2	-32,768 to 32,767
INT	4	-2,147,483,648 to 2,147,483,647
BIGINT	8	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

Numeric Types

numeric(p): Up to 38 digits, no decimal
numeric(p,s)/decimal(p,s): With decimal

Currency Types

Type	Bytes	Range
smallmoney	4	-214,748.3648 to 214,748.3647
money	8	-922,337,203,685,477.5808 to 922,337,203,685,477.5807

Date & Time Types

DATE: Only date (YYYY-MM-DD)
TIME: Only time
DATETIME: Date & time

Tables & Queries

Creating Tables

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

Rules: Name must start with alphabet, no spaces/special chars (except _ # $), up to 128 chars, max 1024 columns.

Inserting Data

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

Inserting NULLs

INSERT INTO emp VALUES(104,'satish',NULL,NULL,'2021-02-22','hr');
INSERT INTO emp(empid,ename,hiredate,dept) VALUES(105,'ravi','2018-09-10','sales');

Displaying Data

SELECT ename, sal FROM emp;
SELECT * FROM emp;

Operators in SQL Server

Arithmetic: +, -, *, /, %
Relational: >, >=, <, <=, =, <>, !=
Logical: AND, OR, NOT
Special: BETWEEN, IN, LIKE, IS, ANY, ALL, EXISTS, PIVOT
Set: UNION, UNION ALL, INTERSECT, EXCEPT

WHERE Clause

SELECT * FROM emp WHERE empid = 103;
SELECT * FROM emp WHERE ename = 'rahul';
SELECT * FROM emp WHERE sal > 5000;
SELECT * FROM emp WHERE hiredate > '2020-12-31';

Compound Conditions

SELECT * FROM emp WHERE empid IN (100,103,105);
SELECT * FROM emp WHERE job IN ('clerk','manager');
SELECT * FROM emp WHERE dept NOT IN ('it','sales');
SELECT * FROM emp WHERE sal BETWEEN 5000 AND 10000;

Question: What happens with WHERE sal BETWEEN 10000 AND 5000?
Answer: Returns no rows.

Pattern Matching (LIKE Operator)

%: 0 or many characters
_: Exactly 1 character

Examples

SELECT * FROM emp WHERE ename LIKE 's%';      -- starts with 's'
SELECT * FROM emp WHERE ename LIKE '%d';      -- ends with 'd'
SELECT * FROM emp WHERE ename LIKE '%a%';     -- contains 'a'
SELECT * FROM emp WHERE ename LIKE '_a%';     -- 'a' is 2nd char
SELECT * FROM emp WHERE ename LIKE '%a____';  -- 'a' is 5th from last
SELECT * FROM emp WHERE ename LIKE '____';    -- 4 chars
SELECT * FROM emp WHERE ename LIKE '[aeiou]%';-- starts with vowel
SELECT * FROM emp WHERE hiredate LIKE '2020%';-- joined in 2020

Escaping Wildcards

SELECT * FROM cust WHERE cname LIKE '%\_%' ESCAPE '\'; -- contains '_'
SELECT * FROM cust WHERE cname LIKE '%\%%' ESCAPE '\'; -- contains '%'

IS Operator

SELECT * FROM emp WHERE sal IS NULL;
SELECT * FROM emp WHERE sal IS NOT NULL;

Summary

Use IN for list comparison
Use BETWEEN for range comparison
Use LIKE for pattern matching
Use IS NULL / IS NOT NULL for null checks

Key Questions:

What is a database?

What is DBMS?

What is RDBMS?

What is ORDBMS?

What is the DB development life cycle?

Hot Posts