Unit 5: SQL Server For Backend Development - Subjective Questions

A database is an organized collection of related data stored electronically so that it can be efficiently accessed, managed, updated, and analyzed. A Database Management System (DBMS) is software that enables users and applications to interact with databases.

Major characteristics of a database:

• Structured storage: Data is organized into tables, records, and fields.
• Data relationships: Related data can be connected using keys.
• Controlled redundancy: Unnecessary duplication of data is minimized.
• Data integrity: Rules ensure that stored data remains accurate and consistent.
• Concurrent access: Multiple users can access the database simultaneously.
• Security: Access can be controlled through users, roles, and permissions.

Advantages of a DBMS:

• Reduces data duplication and inconsistency.
• Supports fast searching, sorting, and reporting.
• Provides backup and recovery facilities.
• Enforces integrity through constraints.
• Supports transaction management.
• Allows centralized administration and secure data sharing.

Microsoft SQL Server is a relational database management system developed by Microsoft. It stores data in related tables and uses Transact-SQL (T-SQL) for defining, retrieving, manipulating, and controlling data.

Important features:

• Relational data storage: Organizes data into tables containing rows and columns.
• T-SQL support: Extends standard SQL with variables, conditions, loops, functions, and error handling.
• Transaction management: Supports reliable transactions using the ACID properties.
• Security: Provides authentication, authorization, encryption, roles, and permissions.
• Backup and recovery: Protects data against system failure or accidental loss.
• Stored procedures and views: Supports reusable database objects.
• Performance tools: Includes indexing, query optimization, and monitoring facilities.

Role in backend development:

A backend application connects to SQL Server to perform operations such as user registration, login verification, order processing, inventory management, and report generation. The application sends SQL commands or calls stored procedures, and SQL Server securely stores and processes the required data.

Databases can be classified according to their data model and method of storage.

Major types of databases:

• Relational database: Stores data in tables connected through keys. Examples include SQL Server, MySQL, and PostgreSQL.
• Hierarchical database: Organizes data in a tree-like parent-child structure.
• Network database: Allows a record to have multiple parent and child records.
• Object-oriented database: Stores data as objects containing attributes and methods.
• Document database: Stores semi-structured documents, commonly in JSON-like form.
• Key-value database: Stores each value against a unique key.
• Graph database: Represents data using nodes, edges, and properties.
• Distributed database: Stores data across multiple computers or locations.

Relational versus non-relational databases:

SQL Server is primarily a relational database management system.

SQL commands are classified according to the operations they perform.

1. Data Definition Language (DDL): Defines or changes database structures.

   • Commands: CREATE, ALTER, DROP, and TRUNCATE
   • Example: CREATE TABLE Student (StudentId INT, Name VARCHAR(50));

2. Data Manipulation Language (DML): Inserts, modifies, or removes table data.

   • Commands: INSERT, UPDATE, DELETE, and MERGE
   • Example: UPDATE Student SET Name = 'Aman' WHERE StudentId = 1;

3. Data Query Language (DQL): Retrieves data from one or more tables.

   • Command: SELECT
   • Example: SELECT * FROM Student;

4. Data Control Language (DCL): Controls database permissions.

   • Commands: GRANT, REVOKE, and DENY
   • Example: GRANT SELECT ON Student TO AppUser;

5. Transaction Control Language (TCL): Manages transactions.

   • Commands: BEGIN TRANSACTION, COMMIT, ROLLBACK, and SAVE TRANSACTION
   • Example: BEGIN TRANSACTION; UPDATE Account SET Balance = Balance - 500 WHERE AccountId = 1; COMMIT;

DDL changes the structure, DML changes the data, DQL reads data, DCL manages access, and TCL controls transaction boundaries.

A SQL Server table stores related data in a row-and-column format.

• A column represents an attribute and has a name, data type, and optional constraints.
• A row represents one complete record.
• A primary key uniquely identifies every row.
• A foreign key establishes a relationship with another table.
• A NULL value represents missing or unknown data.

Creating the table:

CREATE TABLE Employee
(
    EmployeeId INT PRIMARY KEY,
    EmployeeName VARCHAR(100) NOT NULL,
    Email VARCHAR(150) UNIQUE,
    Salary DECIMAL(10, 2) CHECK (Salary >= 0),
    JoinDate DATE DEFAULT GETDATE()
);

Adding a column:

ALTER TABLE Employee
ADD DepartmentId INT;

Changing a column definition:

ALTER TABLE Employee
ALTER COLUMN EmployeeName VARCHAR(150) NOT NULL;

Removing a column:

ALTER TABLE Employee
DROP COLUMN DepartmentId;

CREATE TABLE creates the structure, while ALTER TABLE modifies an existing table without recreating it.

A data type specifies the kind of value a column can store, its storage requirements, and the operations that can be performed on it.

Common SQL Server data types:

• Integer types: TINYINT, SMALLINT, INT, and BIGINT
• Exact numeric types: DECIMAL(p, s) and NUMERIC(p, s)
• Approximate numeric types: FLOAT and REAL
• Character types: CHAR, VARCHAR, NCHAR, and NVARCHAR
• Date and time types: DATE, TIME, DATETIME, DATETIME2, and SMALLDATETIME
• Logical type: BIT
• Binary types: BINARY, VARBINARY, and VARBINARY(MAX)
• Identifier type: UNIQUEIDENTIFIER

In DECIMAL(p, s), represents the total number of digits and represents the number of digits after the decimal point. For example, DECIMAL(10, 2) can store monetary values with two decimal places.

Importance of appropriate data types:

• Saves storage space.
• Improves query and index performance.
• Prevents invalid values from being stored.
• Avoids unnecessary type conversions.
• Preserves numeric precision.
• Makes the database design easier to understand and maintain.

The INSERT, UPDATE, and DELETE commands are DML commands used to modify table data.

1. INSERT: Adds new rows to a table.

INSERT INTO Employee (EmployeeId, EmployeeName, Email, Salary)
VALUES (1, 'Neha Sharma', 'neha@example.com', 45000);

Multiple rows can also be inserted in one statement.

2. UPDATE: Modifies existing rows.

UPDATE Employee
SET Salary = 50000
WHERE EmployeeId = 1;

The WHERE clause is important because omitting it updates every row in the table.

3. DELETE: Removes rows from a table.

DELETE FROM Employee
WHERE EmployeeId = 1;

Omitting the WHERE clause deletes every row but retains the table structure.

Important precautions:

• Test the condition with a SELECT statement before an UPDATE or DELETE.
• Use transactions for critical modifications.
• Specify column names in INSERT statements.
• Ensure that foreign-key relationships are considered before deleting rows.

DELETE, TRUNCATE, and DROP all remove database information, but they operate differently.

Examples:

DELETE FROM Employee WHERE EmployeeId = 10;

TRUNCATE TABLE Employee;

DROP TABLE Employee;

Use DELETE when specific rows must be removed, TRUNCATE when all rows must be removed efficiently, and DROP when the table itself is no longer required.

Constraints are rules applied to table columns to maintain the accuracy, validity, and consistency of data.

Major constraints:

• PRIMARY KEY: Uniquely identifies each row and does not allow NULL values.
• FOREIGN KEY: Ensures that a value refers to an existing row in another table.
• UNIQUE: Prevents duplicate values in a column or column combination.
• NOT NULL: Requires a value to be supplied.
• CHECK: Accepts only values satisfying a condition.
• DEFAULT: Supplies a value when none is specified.

Example:

CREATE TABLE Department
(
    DepartmentId INT PRIMARY KEY,
    DepartmentName VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE Employee
(
    EmployeeId INT PRIMARY KEY,
    EmployeeName VARCHAR(100) NOT NULL,
    Email VARCHAR(150) UNIQUE,
    Salary DECIMAL(10, 2) CHECK (Salary >= 0),
    Status VARCHAR(20) DEFAULT 'Active',
    DepartmentId INT,
    CONSTRAINT FK_Employee_Department
        FOREIGN KEY (DepartmentId)
        REFERENCES Department(DepartmentId)
);

These constraints prevent duplicate identifiers, invalid salaries, missing required names, and references to nonexistent departments.

Primary, unique, and foreign keys serve different purposes in relational database design.

Example:

CREATE TABLE Department
(
    DepartmentId INT PRIMARY KEY,
    DepartmentCode VARCHAR(10) UNIQUE
);

CREATE TABLE Employee
(
    EmployeeId INT PRIMARY KEY,
    Email VARCHAR(100) UNIQUE,
    DepartmentId INT,
    FOREIGN KEY (DepartmentId)
        REFERENCES Department(DepartmentId)
);

Here, EmployeeId identifies an employee, Email must be unique, and DepartmentId links the employee to a valid department.

SQL clauses specify the source, filtering, grouping, and presentation of query results.

• SELECT: Specifies the columns or expressions to return.
• FROM: Identifies the source tables or views.
• WHERE: Filters individual rows before grouping.
• GROUP BY: Combines rows having the same grouping values.
• HAVING: Filters groups after aggregation.
• ORDER BY: Sorts the final result.

Example:

SELECT DepartmentId,
       COUNT(*) AS EmployeeCount,
       AVG(Salary) AS AverageSalary
FROM Employee
WHERE Status = 'Active'
GROUP BY DepartmentId
HAVING COUNT(*) >= 5
ORDER BY AverageSalary DESC;

This query first reads Employee, filters active employees, groups them by department, keeps departments having at least five employees, and sorts the result by average salary.

A useful logical processing order is:

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

Thus, WHERE filters rows, whereas HAVING filters aggregated groups.

SQL operators are symbols or keywords used to perform calculations, comparisons, and filtering.

1. Arithmetic operators:

• + addition
• - subtraction
• * multiplication
• / division
• % remainder

Example: SELECT Salary, Salary * 12 AS AnnualSalary FROM Employee;

2. Comparison operators:

• = equal to
• <> or != not equal to
• > greater than
• < less than
• >= greater than or equal to
• <= less than or equal to

Example: SELECT * FROM Employee WHERE Salary >= 50000;

3. Logical operators:

• AND: All conditions must be true.
• OR: At least one condition must be true.
• NOT: Reverses a condition.

Example: WHERE DepartmentId = 2 AND Salary > 40000

4. Special operators:

• BETWEEN: Tests an inclusive range.
• IN: Tests membership in a list or subquery.
• LIKE: Performs pattern matching.
• IS NULL: Checks for a NULL value.
• EXISTS: Tests whether a subquery returns any rows.

Example: WHERE Salary BETWEEN 30000 AND 60000

Parentheses should be used in complex expressions to make operator precedence explicit.

The LIKE operator performs pattern-based searching on character data.

Common SQL Server wildcard characters:

• % matches zero or more characters.
• _ matches exactly one character.
• [abc] matches one character from the specified set.
• [a-z] matches one character from the specified range.
• [^abc] matches one character not in the specified set.

Examples:

SELECT * FROM Employee
WHERE EmployeeName LIKE 'A%';

This returns names beginning with A.

SELECT * FROM Employee
WHERE EmployeeName LIKE '_a%';

This returns names whose second character is a.

A NULL value represents missing, unknown, or inapplicable information. It is not equal to zero, an empty string, or another NULL value. Therefore, = NULL must not be used.

SELECT * FROM Employee
WHERE Email IS NULL;

SELECT * FROM Employee
WHERE Email IS NOT NULL;

Functions such as ISNULL(Email, 'Not provided') or COALESCE(Email, AlternateEmail, 'Not provided') can substitute another value when an expression is NULL.

A join combines rows from two or more tables using a related column or join condition.

Assume Employee.DepartmentId refers to Department.DepartmentId.

1. INNER JOIN: Returns only matching rows from both tables.

SELECT e.EmployeeName, d.DepartmentName
FROM Employee AS e
INNER JOIN Department AS d
    ON e.DepartmentId = d.DepartmentId;

2. LEFT JOIN: Returns every row from the left table and matching rows from the right table. Missing right-side values become NULL.

SELECT e.EmployeeName, d.DepartmentName
FROM Employee AS e
LEFT JOIN Department AS d
    ON e.DepartmentId = d.DepartmentId;

3. RIGHT JOIN: Returns every row from the right table and matching rows from the left table.

SELECT e.EmployeeName, d.DepartmentName
FROM Employee AS e
RIGHT JOIN Department AS d
    ON e.DepartmentId = d.DepartmentId;

4. FULL OUTER JOIN: Returns matching rows and all unmatched rows from both tables. Missing columns are represented by NULL.

SELECT e.EmployeeName, d.DepartmentName
FROM Employee AS e
FULL OUTER JOIN Department AS d
    ON e.DepartmentId = d.DepartmentId;

The appropriate join depends on whether only matched data or also unmatched data is required.

CROSS JOIN:

A CROSS JOIN produces the Cartesian product of two tables. Every row of the first table is combined with every row of the second table. If the first table has rows and the second has rows, the result contains:

rows.

SELECT c.ColorName, s.SizeName
FROM Color AS c
CROSS JOIN Size AS s;

If there are 4 colors and 3 sizes, the query returns combinations. It is useful for generating all possible combinations but may produce very large results.

Self join:

A self join joins a table to itself. Aliases are required to represent separate logical instances of the same table.

SELECT e.EmployeeName AS Employee,
       m.EmployeeName AS Manager
FROM Employee AS e
LEFT JOIN Employee AS m
    ON e.ManagerId = m.EmployeeId;

Here, e represents employees and m represents managers. A self join is useful for hierarchical relationships such as employee-manager, category-parent category, or prerequisite-course relationships.

A view is a named database object defined by a SELECT query. It presents data from one or more tables as a virtual table. A normal view stores its query definition rather than storing a separate copy of the result data.

Syntax:

CREATE VIEW dbo.ActiveEmployees
AS
SELECT EmployeeId, EmployeeName, DepartmentId
FROM dbo.Employee
WHERE Status = 'Active';

It can be queried like a table:

SELECT * FROM dbo.ActiveEmployees;

Advantages:

• Hides complex joins and calculations.
• Presents a simpler interface to applications.
• Restricts access to selected rows and columns.
• Promotes query reuse and consistency.
• Provides a level of abstraction from base tables.

Limitations:

• Some views are not directly updatable, especially those using grouping, aggregates, DISTINCT, or certain joins.
• A view does not automatically guarantee better performance.
• Changes to base-table structures may affect dependent views.
• Nested and complex views can become difficult to maintain.

A view can be modified using ALTER VIEW and removed using DROP VIEW.

SQL Server views may be classified according to their query structure or purpose.

1. Simple view:

• Usually based on one table.
• Does not normally contain grouping or aggregate functions.
• Is often updatable when each view row maps clearly to a base-table row.

2. Complex view:

• May use multiple tables, joins, calculations, grouping, or aggregate functions.
• Simplifies complicated reporting queries.
• May not be directly updatable.

3. Indexed view:

• Has a unique clustered index created on it.
• Physically stores the indexed result and may improve certain query workloads.
• Requires WITH SCHEMABINDING and must satisfy SQL Server restrictions.
• Adds maintenance cost when base-table data changes.

4. Partitioned view:

• Combines horizontally partitioned data from multiple tables using UNION ALL.
• May represent data divided by year, region, or another range.
• Can be local or distributed across servers.

5. System view:

• Supplied by SQL Server to expose metadata and system information.
• Examples include sys.tables, sys.columns, and sys.views.

Each type serves a different purpose, such as abstraction, reporting, metadata inspection, performance optimization, or combining distributed data.

A user-defined view is created by a database user or developer to present required data through a reusable SELECT statement.

Creating a view:

CREATE VIEW dbo.EmployeeDepartmentView
AS
SELECT e.EmployeeId,
       e.EmployeeName,
       e.Salary,
       d.DepartmentName
FROM dbo.Employee AS e
INNER JOIN dbo.Department AS d
    ON e.DepartmentId = d.DepartmentId;

Using the view:

SELECT EmployeeName, DepartmentName
FROM dbo.EmployeeDepartmentView
WHERE Salary > 50000;

Altering the view:

ALTER VIEW dbo.EmployeeDepartmentView
AS
SELECT e.EmployeeId,
       e.EmployeeName,
       e.Email,
       e.Salary,
       d.DepartmentName
FROM dbo.Employee AS e
INNER JOIN dbo.Department AS d
    ON e.DepartmentId = d.DepartmentId
WHERE e.Status = 'Active';

Dropping the view:

DROP VIEW dbo.EmployeeDepartmentView;

Good practices:

• Use a schema-qualified name such as dbo.ViewName.
• Avoid SELECT * because base-table changes may affect the view unexpectedly.
• Grant users access to the view instead of exposing sensitive table columns.
• Keep the view focused on a clear business requirement.

A stored procedure is a named collection of T-SQL statements stored in the database and executed as a unit. It can accept parameters, perform queries or data modifications, contain programming logic, and return result sets, output parameters, or a return status.

Basic components:

• Procedure name and schema
• Input and output parameters
• T-SQL statements
• Local variables
• Conditional statements and loops
• Transaction and error-handling logic
• Return status or result set

Advantages:

• Reusability: The same logic can be called by different applications.
• Maintainability: Business logic can be changed centrally.
• Security: Users may receive EXECUTE permission without direct table permissions.
• Reduced network traffic: Multiple statements are invoked through one procedure call.
• Consistency: Applications use the same validated operations.
• Performance: SQL Server can reuse suitable execution plans, although performance still depends on query design and parameters.

Execution process:

1. The application calls the procedure with parameter values.
2. SQL Server validates permissions and parameters.
3. The procedure statements are compiled or an existing plan is reused.
4. Statements execute on the server.
5. Results, output parameters, and the return status are sent to the caller.

A user-defined stored procedure can combine parameters, validation, transaction management, and error handling in one reusable operation.

CREATE PROCEDURE dbo.AddEmployee
    @EmployeeId INT,
    @EmployeeName VARCHAR(100),
    @Email VARCHAR(150),
    @Salary DECIMAL(10, 2),
    @DepartmentId INT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    IF @Salary < 0
        THROW 50001, 'Salary cannot be negative.', 1;

    IF NOT EXISTS
    (
        SELECT 1
        FROM dbo.Department
        WHERE DepartmentId = @DepartmentId
    )
        THROW 50002, 'Invalid department.', 1;

    BEGIN TRY
        BEGIN TRANSACTION;

        INSERT INTO dbo.Employee
        (
            EmployeeId,
            EmployeeName,
            Email,
            Salary,
            DepartmentId
        )
        VALUES
        (
            @EmployeeId,
            @EmployeeName,
            @Email,
            @Salary,
            @DepartmentId
        );

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;

        THROW;
    END CATCH;
END;

Execution:

EXEC dbo.AddEmployee
    @EmployeeId = 101,
    @EmployeeName = 'Riya Gupta',
    @Email = 'riya@example.com',
    @Salary = 55000,
    @DepartmentId = 2;

Explanation:

• Parameters receive employee details from the caller.
• The first validation prevents a negative salary.
• EXISTS checks whether the department is valid.
• BEGIN TRANSACTION makes the insertion an atomic operation.
• COMMIT permanently saves a successful insertion.
• TRY...CATCH intercepts runtime errors.
• ROLLBACK reverses an incomplete transaction.
• THROW passes the error to the calling application.
• SET NOCOUNT ON prevents unnecessary row-count messages.