SQL Server Interview Questions and Answers for Beginners

---

1. Basic Database Concepts {#basic-concepts}

Q1: What is a Database?

Answer: A database is a structured collection of data that is stored and organized in a way that allows for efficient retrieval, management, and updating of information.

Example:

-- A simple employee database might contain:
-- - Employee information (names, IDs, salaries)
-- - Department information
-- - Project assignments

Q2: What is RDBMS?

Answer: RDBMS (Relational Database Management System) is a database management system based on the relational model. It stores data in tables with rows and columns, and establishes relationships between tables.

Key Features:

• Data is stored in tables (relations)
• Tables have rows (records) and columns (attributes)
• Relationships exist between tables
• Supports ACID properties

Q3: What is SQL Server?

Answer: SQL Server is a relational database management system (RDBMS) developed by Microsoft. It’s used to store, retrieve, and manage data in databases.

Key Components:

• Database Engine
• SQL Server Management Studio (SSMS)
• Integration Services (SSIS)
• Reporting Services (SSRS)
• Analysis Services (SSAS)

---

2. SQL Server Fundamentals {#sql-fundamentals}

Q4: What is SQL?

Answer: SQL (Structured Query Language) is a standard programming language designed for managing and manipulating relational databases.

Types of SQL Commands:

• DDL (Data Definition Language): CREATE, ALTER, DROP
• DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE
• DCL (Data Control Language): GRANT, REVOKE
• TCL (Transaction Control Language): COMMIT, ROLLBACK

Example:

-- DDL Example
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

-- DML Example
INSERT INTO Students VALUES (1, 'John', 'Smith');
SELECT * FROM Students;

Q5: What is the difference between SQL and T-SQL?

Answer:

• SQL: Standard query language used across different database systems
• T-SQL (Transact-SQL): Microsoft’s extension of SQL specifically for SQL Server

T-SQL Additional Features:

-- Variables
DECLARE @StudentName VARCHAR(50) = 'John';

-- Control Flow
IF @StudentName = 'John'
    PRINT 'Student found';
ELSE
    PRINT 'Student not found';

-- Try-Catch blocks
BEGIN TRY
    -- SQL statements
END TRY
BEGIN CATCH
    PRINT 'Error occurred';
END CATCH

Q6: What are the different SQL Server editions?

Answer:

• Express: Free, limited features, suitable for small applications
• Standard: Mid-range features, good for medium-sized businesses
• Enterprise: Full features, for large organizations
• Developer: Full features but for development use only
• Web: Designed for web hosting

---

3. Data Types {#data-types}

Q7: What are the main data types in SQL Server?

Answer: SQL Server supports various data types organized into categories:

Numeric Types:

CREATE TABLE NumericExample (
    ID INT,                    -- Integer: -2,147,483,648 to 2,147,483,647
    BigNumber BIGINT,          -- Large integer
    Price DECIMAL(10,2),       -- Fixed precision: 99999999.99
    Percentage FLOAT           -- Floating point number
);

String Types:

CREATE TABLE StringExample (
    ShortText VARCHAR(50),     -- Variable-length: up to 50 characters
    LongText TEXT,            -- Large text data
    FixedText CHAR(10),       -- Fixed-length: always 10 characters
    UnicodeText NVARCHAR(100) -- Unicode variable-length
);

Date/Time Types:

CREATE TABLE DateExample (
    EventDate DATE,           -- Date only: 2023-12-25
    EventTime TIME,           -- Time only: 14:30:00
    EventDateTime DATETIME,   -- Date and time: 2023-12-25 14:30:00
    Timestamp DATETIME2       -- More precise date/time
);

Other Types:

CREATE TABLE OtherExample (
    IsActive BIT,             -- Boolean: 0 or 1
    UniqueID UNIQUEIDENTIFIER, -- GUID
    BinaryData VARBINARY(MAX) -- Binary data
);

Q8: What’s the difference between VARCHAR and NVARCHAR?

Answer:

• VARCHAR: Stores ASCII characters (1 byte per character)
• NVARCHAR: Stores Unicode characters (2 bytes per character)

Example:

CREATE TABLE TextComparison (
    ASCIIText VARCHAR(50),    -- Can store: "Hello World"
    UnicodeText NVARCHAR(50)  -- Can store: "Hello 世界" (mixed languages)
);

INSERT INTO TextComparison VALUES ('Hello World', N'Hello 世界');
SELECT * FROM TextComparison;

---

4. Tables and Constraints {#tables-constraints}

Q9: How do you create a table in SQL Server?

Answer: Use the CREATE TABLE statement to define table structure.

Example:

CREATE TABLE Employees (
    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    HireDate DATE DEFAULT GETDATE(),
    Salary DECIMAL(10,2) CHECK (Salary > 0),
    DepartmentID INT
);

Q10: What are constraints? Name different types.

Answer: Constraints are rules applied to table columns to enforce data integrity.

Types of Constraints:

1. PRIMARY KEY:

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,  -- Unique identifier
    Name VARCHAR(50)
);

2. FOREIGN KEY:

CREATE TABLE Enrollments (
    EnrollmentID INT PRIMARY KEY,
    StudentID INT,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);

3. UNIQUE:

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Email VARCHAR(100) UNIQUE  -- No duplicate emails
);

4. NOT NULL:

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100) NOT NULL  -- Required field
);

5. CHECK:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    Quantity INT CHECK (Quantity > 0),  -- Must be positive
    Status VARCHAR(20) CHECK (Status IN ('Pending', 'Shipped', 'Delivered'))
);

6. DEFAULT:

CREATE TABLE Accounts (
    AccountID INT PRIMARY KEY,
    Balance DECIMAL(10,2) DEFAULT 0,  -- Default value
    CreatedDate DATETIME DEFAULT GETDATE()
);

Q11: What is IDENTITY in SQL Server?

Answer: IDENTITY is a property that automatically generates unique numeric values for a column.

Example:

CREATE TABLE Categories (
    CategoryID INT IDENTITY(1,1) PRIMARY KEY,  -- Starts at 1, increments by 1
    CategoryName VARCHAR(50)
);

INSERT INTO Categories (CategoryName) VALUES ('Electronics');
INSERT INTO Categories (CategoryName) VALUES ('Clothing');

SELECT * FROM Categories;
-- Results:
-- CategoryID | CategoryName
-- 1          | Electronics
-- 2          | Clothing

IDENTITY Properties:

-- Custom seed and increment
CREATE TABLE CustomIdentity (
    ID INT IDENTITY(100,5),  -- Starts at 100, increments by 5
    Name VARCHAR(50)
);

---

5. Basic SQL Queries {#basic-queries}

Q12: How do you retrieve data from a table?

Answer: Use the SELECT statement to retrieve data.

Basic Syntax:

-- Select all columns
SELECT * FROM Employees;

-- Select specific columns
SELECT FirstName, LastName, Salary FROM Employees;

-- Select with alias
SELECT 
    FirstName AS 'First Name',
    LastName AS 'Last Name',
    Salary AS 'Annual Salary'
FROM Employees;

Q13: How do you filter data using WHERE clause?

Answer: The WHERE clause is used to filter records based on specified conditions.

Examples:

-- Basic filtering
SELECT * FROM Employees WHERE Salary > 50000;

-- Multiple conditions with AND
SELECT * FROM Employees 
WHERE Salary > 40000 AND DepartmentID = 1;

-- Multiple conditions with OR
SELECT * FROM Employees 
WHERE DepartmentID = 1 OR DepartmentID = 2;

-- Using IN operator
SELECT * FROM Employees 
WHERE DepartmentID IN (1, 2, 3);

-- Pattern matching with LIKE
SELECT * FROM Employees 
WHERE FirstName LIKE 'J%';  -- Names starting with 'J'

-- Range filtering with BETWEEN
SELECT * FROM Employees 
WHERE Salary BETWEEN 40000 AND 60000;

-- NULL checking
SELECT * FROM Employees 
WHERE Email IS NOT NULL;

Q14: How do you sort data?

Answer: Use ORDER BY clause to sort results.

Examples:

-- Sort ascending (default)
SELECT * FROM Employees ORDER BY LastName;

-- Sort descending
SELECT * FROM Employees ORDER BY Salary DESC;

-- Multiple column sorting
SELECT * FROM Employees 
ORDER BY DepartmentID ASC, Salary DESC;

-- Sort with alias
SELECT FirstName + ' ' + LastName AS FullName, Salary
FROM Employees
ORDER BY FullName;

Q15: What are aggregate functions?

Answer: Aggregate functions perform calculations on multiple rows and return a single result.

Common Aggregate Functions:

-- Sample data setup
CREATE TABLE Sales (
    SaleID INT IDENTITY(1,1) PRIMARY KEY,
    SalespersonID INT,
    Amount DECIMAL(10,2),
    SaleDate DATE
);

INSERT INTO Sales VALUES 
(1, 1500.00, '2023-01-15'),
(1, 2000.00, '2023-01-20'),
(2, 1800.00, '2023-01-18'),
(2, 2200.00, '2023-01-22');

-- Aggregate function examples
SELECT 
    COUNT(*) AS TotalSales,           -- Count all rows
    COUNT(Amount) AS NonNullAmounts,  -- Count non-null values
    SUM(Amount) AS TotalRevenue,      -- Sum of all amounts
    AVG(Amount) AS AverageAmount,     -- Average amount
    MIN(Amount) AS LowestSale,        -- Minimum amount
    MAX(Amount) AS HighestSale        -- Maximum amount
FROM Sales;

Q16: What is GROUP BY and HAVING?

Answer:

• GROUP BY: Groups rows with the same values in specified columns
• HAVING: Filters groups based on conditions (like WHERE but for groups)

Examples:

-- GROUP BY example
SELECT 
    DepartmentID,
    COUNT(*) AS EmployeeCount,
    AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY DepartmentID;

-- GROUP BY with HAVING
SELECT 
    DepartmentID,
    COUNT(*) AS EmployeeCount,
    AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY DepartmentID
HAVING COUNT(*) > 5;  -- Only departments with more than 5 employees

-- Complex example
SELECT 
    DepartmentID,
    YEAR(HireDate) AS HireYear,
    COUNT(*) AS NewHires,
    AVG(Salary) AS AvgStartingSalary
FROM Employees
WHERE HireDate >= '2020-01-01'
GROUP BY DepartmentID, YEAR(HireDate)
HAVING AVG(Salary) > 45000
ORDER BY DepartmentID, HireYear;

Q17: How do you insert data into a table?

Answer: Use the INSERT statement to add new records.

Examples:

-- Insert single record with all columns
INSERT INTO Employees (FirstName, LastName, Email, HireDate, Salary, DepartmentID)
VALUES ('John', 'Smith', 'john.smith@company.com', '2023-01-15', 55000, 1);

-- Insert single record with selected columns
INSERT INTO Employees (FirstName, LastName, Email)
VALUES ('Jane', 'Doe', 'jane.doe@company.com');

-- Insert multiple records
INSERT INTO Employees (FirstName, LastName, Email, Salary, DepartmentID) VALUES
('Mike', 'Johnson', 'mike.johnson@company.com', 60000, 2),
('Sarah', 'Williams', 'sarah.williams@company.com', 52000, 1),
('David', 'Brown', 'david.brown@company.com', 58000, 3);

-- Insert from another table
INSERT INTO EmployeeBackup (FirstName, LastName, Email)
SELECT FirstName, LastName, Email 
FROM Employees 
WHERE DepartmentID = 1;

Q18: How do you update existing data?

Answer: Use the UPDATE statement to modify existing records.

Examples:

-- Update single record
UPDATE Employees 
SET Salary = 60000 
WHERE EmployeeID = 1;

-- Update multiple columns
UPDATE Employees 
SET Salary = 58000, DepartmentID = 2 
WHERE EmployeeID = 3;

-- Update with conditions
UPDATE Employees 
SET Salary = Salary * 1.1  -- 10% raise
WHERE DepartmentID = 1 AND HireDate < '2022-01-01';

-- Update using JOIN
UPDATE e 
SET e.DepartmentName = d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

Q19: How do you delete data?

Answer: Use the DELETE statement to remove records.

Examples:

-- Delete specific record
DELETE FROM Employees WHERE EmployeeID = 5;

-- Delete with conditions
DELETE FROM Employees 
WHERE DepartmentID = 3 AND HireDate < '2020-01-01';

-- Delete all records (but keep table structure)
DELETE FROM TempEmployees;

-- Alternative: TRUNCATE (faster for all records)
TRUNCATE TABLE TempEmployees;

---

6. Joins {#joins}

Q20: What are JOINs? Explain different types.

Answer: JOINs are used to combine rows from two or more tables based on related columns.

Sample Tables:

-- Departments table
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(50),
    Location VARCHAR(50)
);

INSERT INTO Departments VALUES
(1, 'IT', 'Building A'),
(2, 'HR', 'Building B'),
(3, 'Finance', 'Building C');

-- Employees table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DepartmentID INT
);

INSERT INTO Employees VALUES
(1, 'John', 'Smith', 1),
(2, 'Jane', 'Doe', 2),
(3, 'Mike', 'Johnson', 1),
(4, 'Sarah', 'Williams', NULL);  -- No department assigned

Q21: What is INNER JOIN?

Answer: INNER JOIN returns only records that have matching values in both tables.

Example:

SELECT 
    e.FirstName,
    e.LastName,
    d.DepartmentName,
    d.Location
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

-- Results: Only employees with valid departments
-- John Smith    | IT      | Building A
-- Jane Doe      | HR      | Building B  
-- Mike Johnson  | IT      | Building A
-- (Sarah Williams not included - no department)

Q22: What is LEFT JOIN?

Answer: LEFT JOIN returns all records from the left table and matching records from the right table. NULL values for non-matching records.

Example:

SELECT 
    e.FirstName,
    e.LastName,
    d.DepartmentName,
    d.Location
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID;

-- Results: All employees, even without departments
-- John Smith    | IT      | Building A
-- Jane Doe      | HR      | Building B
-- Mike Johnson  | IT      | Building A
-- Sarah Williams| NULL    | NULL

Q23: What is RIGHT JOIN?

Answer: RIGHT JOIN returns all records from the right table and matching records from the left table.

Example:

SELECT 
    e.FirstName,
    e.LastName,
    d.DepartmentName,
    d.Location
FROM Employees e
RIGHT JOIN Departments d ON e.DepartmentID = d.DepartmentID;

-- Results: All departments, even without employees
-- John Smith    | IT      | Building A
-- Mike Johnson  | IT      | Building A
-- Jane Doe      | HR      | Building B
-- NULL          | Finance | Building C

Q24: What is FULL OUTER JOIN?

Answer: FULL OUTER JOIN returns all records when there’s a match in either table.

Example:

SELECT 
    e.FirstName,
    e.LastName,
    d.DepartmentName,
    d.Location
FROM Employees e
FULL OUTER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

-- Results: All employees and all departments
-- John Smith    | IT      | Building A
-- Mike Johnson  | IT      | Building A
-- Jane Doe      | HR      | Building B
-- Sarah Williams| NULL    | NULL
-- NULL          | Finance | Building C

Q25: What is CROSS JOIN?

Answer: CROSS JOIN returns the Cartesian product of both tables (every row from first table combined with every row from second table).

Example:

SELECT 
    e.FirstName,
    d.DepartmentName
FROM Employees e
CROSS JOIN Departments d;

-- Results: 4 employees × 3 departments = 12 rows
-- John    | IT
-- John    | HR  
-- John    | Finance
-- Jane    | IT
-- Jane    | HR
-- Jane    | Finance
-- (and so on...)

---

7. Functions {#functions}

Q26: What are built-in functions in SQL Server?

Answer: SQL Server provides various built-in functions for different operations:

String Functions:

SELECT 
    FirstName,
    LastName,
    LEN(FirstName) AS NameLength,           -- Length of string
    UPPER(FirstName) AS UpperCase,          -- Convert to uppercase
    LOWER(LastName) AS LowerCase,           -- Convert to lowercase
    LEFT(FirstName, 3) AS FirstThreeChars,  -- Left 3 characters
    RIGHT(LastName, 3) AS LastThreeChars,   -- Right 3 characters
    SUBSTRING(Email, 1, 5) AS EmailPrefix,  -- Substring from position 1, length 5
    REPLACE(Email, '.com', '.org') AS ModifiedEmail  -- Replace text
FROM Employees;

Date Functions:

SELECT 
    HireDate,
    GETDATE() AS CurrentDate,               -- Current date and time
    YEAR(HireDate) AS HireYear,            -- Extract year
    MONTH(HireDate) AS HireMonth,          -- Extract month
    DAY(HireDate) AS HireDay,              -- Extract day
    DATENAME(MONTH, HireDate) AS MonthName, -- Month name
    DATEDIFF(DAY, HireDate, GETDATE()) AS DaysWorked,  -- Difference in days
    DATEADD(YEAR, 1, HireDate) AS OneYearLater         -- Add time interval
FROM Employees;

Numeric Functions:

SELECT 
    Salary,
    ROUND(Salary, -3) AS RoundedSalary,     -- Round to nearest thousand
    CEILING(Salary/12) AS MonthlyCeiling,   -- Round up
    FLOOR(Salary/12) AS MonthlyFloor,       -- Round down
    ABS(Salary - 50000) AS SalaryDifference, -- Absolute value
    POWER(2, 3) AS PowerExample,            -- 2 to the power of 3
    SQRT(Salary) AS SquareRoot              -- Square root
FROM Employees;

Q27: What is the difference between LEN and DATALENGTH?

Answer:

• LEN: Returns the number of characters (excludes trailing spaces)
• DATALENGTH: Returns the number of bytes used to store the data

Example:

SELECT 
    'Hello   ' AS TestString,
    LEN('Hello   ') AS LenResult,           -- Returns 5 (ignores trailing spaces)
    DATALENGTH('Hello   ') AS DataLength,   -- Returns 8 (includes trailing spaces)
    LEN(N'Hello') AS UnicodeLen,            -- Returns 5 characters
    DATALENGTH(N'Hello') AS UnicodeBytes    -- Returns 10 bytes (2 bytes per Unicode char)

Q28: What are CASE statements?

Answer: CASE is used for conditional logic in SQL queries, similar to if-else statements.

Simple CASE:

SELECT 
    FirstName,
    LastName,
    Salary,
    CASE 
        WHEN Salary < 40000 THEN 'Low'
        WHEN Salary BETWEEN 40000 AND 60000 THEN 'Medium'
        WHEN Salary > 60000 THEN 'High'
        ELSE 'Unknown'
    END AS SalaryCategory
FROM Employees;

CASE in UPDATE:

UPDATE Employees
SET Bonus = CASE 
    WHEN Salary < 50000 THEN Salary * 0.05
    WHEN Salary BETWEEN 50000 AND 70000 THEN Salary * 0.10
    ELSE Salary * 0.15
END;

CASE with GROUP BY:

SELECT 
    CASE 
        WHEN Salary < 50000 THEN 'Entry Level'
        WHEN Salary < 70000 THEN 'Mid Level'
        ELSE 'Senior Level'
    END AS Level,
    COUNT(*) AS