أسئلة القسم

Types of Variables in SQL:

• Local variables: Declared using DECLARE inside a batch or stored procedure.
• Global variables: Predefined system variables starting with @@, e.g., @@ROWCOUNT, @@VERSION.

Types of Functions in SQL:

• Scalar Functions: Return a single value (e.g., LEN(), GETDATE()).
• Aggregate Functions: Operate on a set of values and return one result (e.g., SUM(), AVG()).
• Table-Valued Functions: Return a table (Inline or Multi-Statement).

A constraint is a rule applied to a column or table to enforce data integrity.

Types of Constraints:

• PRIMARY KEY
• FOREIGN KEY
• UNIQUE
• CHECK
• DEFAULT
• NOT NULL

• Composite Key: A key made of two or more columns to uniquely identify a row.
• Unique Key: Ensures uniqueness for a column (or set of columns), allows one NULL value.

Types of Joins in SQL:

• INNER JOIN: Returns matching rows.
• LEFT JOIN: All rows from left table + matching from right.
• RIGHT JOIN: All rows from right table + matching from left.
• FULL OUTER JOIN: All rows, matched or unmatched.
• CROSS JOIN: Cartesian product.
• SELF JOIN: Join table with itself.

An alias is a temporary name for a table or column.

Example:

SELECT e.Name AS EmployeeName FROM Employees e;

• WHERE: Filters rows before grouping.
• HAVING: Filters groups/aggregates after grouping.

SELECT MAX(Salary)
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);

SELECT DISTINCT Salary
FROM Employees e1
WHERE N-1 = (
   SELECT COUNT(DISTINCT Salary)
   FROM Employees e2
   WHERE e2.Salary > e1.Salary
);

SELECT *
FROM (
   SELECT Name, Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS rnk
   FROM Employees
) t
WHERE rnk = N;

A Dynamic Query is a query built as a string at runtime and executed with EXEC or sp_executesql.

Used when table/column names aren't known at compile time.

A CTE (Common Table Expression) is a temporary named result set defined with WITH.

Example:

WITH EmployeeCTE AS (
   SELECT Id, Name, ManagerId FROM Employees
)
SELECT * FROM EmployeeCTE WHERE ManagerId IS NULL;

• EXISTS: Checks existence (returns TRUE/FALSE).
• IN: Matches a value against a list/subquery.
• Note: EXISTS is usually faster for correlated subqueries.

• Subquery: Runs once, result passed to outer query.
• Correlated Subquery: Runs for each row of outer query.

A Synonym is an alias for a database object (table, view, proc).

Example:

CREATE SYNONYM Emp FOR HR.Employees;

An Indexed View is a view with a unique clustered index.

It materializes results, improving performance on complex queries.

An Index is a structure to speed up data retrieval.

Types:

• Clustered: One per table, sorts data.
• Non-clustered: Like a separate lookup table.
• Unique Index
• Filtered Index
• Full-Text Index

Partitioning splits large tables into smaller, manageable parts.

Types:

• Range
• List
• Hash
• Composite

Triggers are special procedures executed automatically on events (INSERT, UPDATE, DELETE).

Types:

• DML
• DDL
• Instead-of
• After

ACID Properties:

• Atomicity: All or nothing (e.g., bank transfer completes or fails entirely).
• Consistency: Valid state maintained (e.g., balance can't go negative).
• Isolation: Transactions don't interfere (e.g., concurrent transfers don't mix).
• Durability: Changes persist (e.g., committed transfer survives crash).

Normalization is the process of reducing redundancy.

Normal Forms:

• 1NF: Atomic values.
• 2NF: No partial dependency.
• 3NF: No transitive dependency.
• BCNF: Stronger 3NF.

Denormalization is adding redundancy back for performance.

Used in reporting, OLAP systems.

A Transaction is a logical unit of work.

Example:

BEGIN TRAN;
UPDATE Accounts SET Balance = Balance - 100 WHERE Id = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE Id = 2;
COMMIT;
-- Or ROLLBACK if error occurs

A Deadlock occurs when two transactions wait on each other's locks.

Prevention:

• Keep transactions short.
• Access objects in same order.
• Use proper indexing.

SQL Profiling is monitoring SQL queries to detect slow performance.

Tools:

• SQL Server Profiler
• Extended Events
• Query Store

Steps for SQL Performance Tuning:

• Check execution plans.
• Add proper indexes.
• Avoid cursors, prefer set-based operations.
• Use appropriate JOINs.
• Optimize queries & schema.

The Global Assembly Cache (GAC) is a machine-wide cache for .NET assemblies (shared libraries).

Used when multiple apps share the same assembly version.

Database connectivity in .NET is done via ADO.NET or Entity Framework Core.

ADO.NET Steps:

• SqlConnection: Connect to database.
• SqlCommand: Execute query.
• SqlDataReader: Fetch results.

Types of Caching in .NET (SQL-related):

• In-Memory Cache: Using IMemoryCache.
• Distributed Cache: Using IDistributedCache (e.g., Redis, SQL Server).
• Output Caching: For web responses.
• EF Core Caching: Query results caching via 3rd-party libraries.