MVC etc - realestateexplorer/relatedToCh GitHub Wiki

Of course! Here is a comprehensive list of SQL interview questions and answers, categorized from beginner to advanced. These questions cover key concepts in database management, query writing, and design.

For context, most examples will use these two simple tables:

Employees Table

Departments Table

Answer: These are categories of SQL commands:

• DDL (Data Definition Language): Used to define or manage the database schema (structure).
  • Examples: CREATE (table, database), ALTER (table), DROP (table), TRUNCATE (table).
• DML (Data Manipulation Language): Used for manipulating the data within the tables.
  • Examples: SELECT, INSERT, UPDATE, DELETE.
• DCL (Data Control Language): Used for managing user permissions and access to the database.
  • Examples: GRANT, REVOKE.

Answer:

Answer:

Answer:

• INNER JOIN: Returns only the records that have matching values in both tables. If a row in the left table has no matching row in the right table, it is excluded.

• LEFT JOIN (or LEFT OUTER JOIN): Returns all records from the left table, and the matched records from the right table. If there is no match in the right table, the result is NULL for the columns from the right table.

Example:

• SELECT E.Name, D.DepartmentName FROM Employees E INNER JOIN Departments D ON E.DepartmentID = D.DepartmentID; would return Alice, Bob, Charlie, and Diana with their department names. Eve would be excluded because DepartmentID 3 doesn't exist in the Departments table.

• SELECT E.Name, D.DepartmentName FROM Employees E LEFT JOIN Departments D ON E.DepartmentID = D.DepartmentID; would return all employees. Eve's DepartmentName would be NULL.

Answer: The GROUP BY clause is used with aggregate functions (COUNT, SUM, AVG, MAX, MIN) to group rows that have the same values in specified columns into summary rows.

The difference between WHERE and HAVING is when they are applied:

• WHERE filters rows before any grouping or aggregation is performed.
• HAVING filters groups after the aggregation has been performed.

Example: Find departments with more than one employee and a salary over 70,000.

SELECT
    D.DepartmentName,
    COUNT(E.EmployeeID) AS NumberOfEmployees
FROM Employees E
JOIN Departments D ON E.DepartmentID = D.DepartmentID
WHERE
    E.Salary > 70000 -- Filters individual employees BEFORE grouping
GROUP BY
    D.DepartmentName
HAVING
    COUNT(E.EmployeeID) > 1; -- Filters the groups AFTER counting

This would return "Marketing" because both Charlie (95k) and Diana (72k) meet the WHERE criteria, and the resulting group of 2 meets the HAVING criteria.

Answer: An index is a special lookup table that the database search engine can use to speed up data retrieval. It's like the index in the back of a book; instead of scanning the whole book, you look up the term in the index and go directly to the page.

Usefulness:

• Pro: Drastically improves the speed of SELECT queries and WHERE clauses.
• Con: Slows down data modification operations (INSERT, UPDATE, DELETE) because the index also needs to be updated.

Clustered vs. Non-Clustered:

• Clustered Index: Determines the physical order of data in a table. Think of it like a telephone book, where the data is physically sorted by last name. Because of this, a table can only have one clustered index.
• Non-Clustered Index: Has a separate structure from the data rows. It contains the index key values and a pointer to the location of the data row. This is like the index in a textbook; it's separate from the content and points you to it. A table can have multiple non-clustered indexes.

Answer: A Common Table Expression (CTE) is a temporary, named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It's defined using the WITH keyword.

Reasons to use a CTE:

1. Readability and Maintainability: Breaks down complex queries into simple, logical building blocks, making them easier to read and debug.
2. Recursion: CTEs can reference themselves, allowing you to write recursive queries, which are useful for hierarchical data (like an organizational chart).
3. Reusability: You can reference the same CTE multiple times within the main query, avoiding the need to rewrite the same subquery.

Example (without CTE):

SELECT DepartmentName, AvgSalary
FROM (
    SELECT D.DepartmentName, AVG(E.Salary) as AvgSalary
    FROM Employees E
    JOIN Departments D ON E.DepartmentID = D.DepartmentID
    GROUP BY D.DepartmentName
) AS DepartmentAverages
WHERE AvgSalary > 70000;

Example (with CTE):

WITH DepartmentAverages AS (
    SELECT
        D.DepartmentName,
        AVG(E.Salary) as AvgSalary
    FROM Employees E
    JOIN Departments D ON E.DepartmentID = D.DepartmentID
    GROUP BY D.DepartmentName
)
SELECT DepartmentName, AvgSalary
FROM DepartmentAverages
WHERE AvgSalary > 70000;

Answer: A window function performs a calculation across a set of table rows that are somehow related to the current row. Unlike aggregate functions, which group rows into a single output row, window functions perform calculations on a "window" of rows and return a value for every row.

The OVER() clause is the key to a window function. It defines the window (or set of rows) to operate on.

Example: Rank employees within each department by their salary.

SELECT
    Name,
    Salary,
    DepartmentName,
    RANK() OVER (PARTITION BY D.DepartmentName ORDER BY E.Salary DESC) as SalaryRank
FROM Employees E
JOIN Departments D ON E.DepartmentID = D.DepartmentID;

• PARTITION BY D.DepartmentName: This divides the rows into windows, one for each department. The ranking will restart for each department.
• ORDER BY E.Salary DESC: This orders the rows within each window to determine the rank.

Answer: Normalization is the process of organizing columns and tables in a relational database to minimize data redundancy and improve data integrity.

• First Normal Form (1NF):

  1. The table must have a primary key.
  2. Each cell must hold a single, atomic value (no repeating groups or comma-separated values).
  3. All entries in a column must be of the same data type.

• Second Normal Form (2NF):

  1. Must be in 1NF.
  2. All non-key attributes must be fully functionally dependent on the entire primary key. (This rule mainly applies to tables with composite primary keys, meaning a PK made of multiple columns). It prevents partial dependency.

• Third Normal Form (3NF):

  1. Must be in 2NF.
  2. There should be no transitive dependencies. A transitive dependency is when a non-key attribute depends on another non-key attribute, rather than on the primary key itself. (e.g., if you stored the ManagerName in an Employees table, ManagerName depends on ManagerID, which depends on the EmployeeID primary key).

In short: 1NF is about atomicity, 2NF is about removing partial dependencies, and 3NF is about removing transitive dependencies.

Answer: A deadlock is a situation where two or more transactions are waiting for each other to release locks, creating a circular dependency and causing all of them to be stuck indefinitely.

Example Scenario:

1. Transaction 1 locks Table A and needs to update Table B.
2. At the same time, Transaction 2 locks Table B and needs to update Table A.
3. Transaction 1 waits for Transaction 2 to release the lock on Table B.
4. Transaction 2 waits for Transaction 1 to release the lock on Table A.
5. Neither can proceed.

Prevention and Resolution:

1. Locking Order: Ensure all transactions that access multiple resources do so in the same, consistent order. (e.g., always lock Table A before Table B).
2. Keep Transactions Short: The longer a transaction holds a lock, the higher the chance of a deadlock.
3. Use Appropriate Isolation Levels: A lower isolation level (like READ COMMITTED) reduces the duration and scope of locks, making deadlocks less likely.
4. Deadlock Detection: Most database systems have a built-in deadlock detector that will choose one transaction as a "victim," roll it back, and allow the other to proceed.

Answer: SQL Injection is a code injection technique used to attack data-driven applications. It occurs when malicious SQL statements are inserted into an entry field for execution (e.g., dumping database contents to the attacker).

Example of a vulnerable query:

// C# example
var sql = "SELECT * FROM Users WHERE UserId = '" + userIdInput + "';";

If an attacker provides 105 OR 1=1 as the userIdInput, the query becomes: SELECT * FROM Users WHERE UserId = '105' OR '1'='1'; This would return all users because 1=1 is always true.

Prevention: The single most effective way to prevent SQL injection is to use Parameterized Queries (also known as Prepared Statements).

With parameterized queries, the database engine does not mix the SQL command with the user-provided data. The query structure is sent first, and the parameters are sent separately. The database treats the parameter values literally and never as executable code.

Example of a safe query (C# with Dapper):

var sql = "SELECT * FROM Users WHERE UserId = @UserId;";
var user = connection.QueryFirst(sql, new { UserId = userIdInput });

In this case, even if an attacker sends 105 OR 1=1, the database will look for a UserId that is literally the string '105 OR 1=1', which will not be found.

Of course! Here is a comprehensive set of interview questions and answers for Razor Views in C# (.NET), categorized by difficulty.

These questions are relevant for both ASP.NET MVC and ASP.NET Core MVC/Razor Pages, but the answers are tailored towards modern .NET Core practices (like Tag Helpers and Dependency Injection).

Answer: Razor is a server-side markup syntax for embedding .NET-based code (C#) into web pages. It is not a new programming language but a templating engine. Its primary job is to combine server-side C# code with HTML to generate the final HTML that is sent to the browser. The syntax is designed to be compact and fluid, minimizing the friction between writing code and writing markup.

Answer: The @ character is used to start Razor code.

Razor's parser is intelligent enough to transition back to HTML automatically. When it encounters an HTML tag (like <div>, <p>, <span>) inside a code block, it assumes you are switching back to HTML markup until it sees another @.

Example:

@{
    var name = "World";
}

<p>Hello, @name!</p> <!-- Simple expression -->

@if (DateTime.Now.Hour < 12)
{
    <div>Good Morning!</div> <!-- Switches to HTML inside a code block -->
}
else
{
    <div>Good Afternoon!</div>
}

Answer:

• Strongly-Typed View: A view that is explicitly bound to a specific model class using the @model directive at the top of the file. This is the highly recommended approach.

  • Benefits: You get compile-time checking for model properties and full IntelliSense support in Visual Studio, which drastically reduces runtime errors from typos.
  • Example: @model MyWebApp.ViewModels.ProductViewModel

• Weakly-Typed View (or Dynamic View): A view that does not have a declared model type. Data is passed to it using dynamic containers like ViewData (a dictionary) or ViewBag (a dynamic object wrapper around ViewData).

  • Drawbacks: There is no compile-time checking or IntelliSense. If you misspell a property name (e.g., ViewBag.Naem instead of ViewBag.Name), you will only discover the error at runtime.
  • Example: <h1>@ViewBag.Title</h1>

Answer: A Layout page (_Layout.cshtml) serves as a master template for the views in an application. It defines the common HTML structure that is shared across multiple pages, such as the <html>, <head>, <body> tags, navigation menu, header, and footer. This promotes a consistent look and feel and follows the DRY (Don't Repeat Yourself) principle.

@RenderBody() is a method called within the layout page that acts as a placeholder. It marks the location where the content of the individual, specific view (e.g., Index.cshtml, Details.cshtml) will be rendered. Every layout page must have exactly one call to @RenderBody().

Answer:

• @RenderBody():

  • Renders the main content of a view that is not within a named section.
  • It is required and can only be called once in a layout page.

• @RenderSection(string name, bool required):

  • Renders a specific, named block of content defined in a content view. Sections allow a view to specify content for different parts of the layout.
  • It can be called multiple times in a layout for different sections (e.g., a "scripts" section and a "styles" section).
  • The required parameter determines if the content view must define that section. If required: true, an error will be thrown if the view doesn't implement the section.

Layout (_Layout.cshtml):

<body>
    <div class="container">
        @RenderBody()
    </div>

    @await RenderSectionAsync("Scripts", required: false)
</body>

View (Index.cshtml):

@section Scripts {
    <script src="~/js/my-page-specific-script.js"></script>
}

<h1>Welcome to the Index Page</h1>
<p>This is the main content rendered by RenderBody().</p>

Answer: Both are used to generate HTML programmatically on the server, but they have very different syntaxes and workflows.

• Tag Helpers (<a asp-action="...">):

  • They are C# classes that participate in rendering standard HTML elements by attaching to them via asp- prefixed attributes.
  • The syntax looks and feels like natural HTML, making it much more readable and easier for front-end developers to work with.
  • They are the preferred, modern approach in ASP.NET Core.
  • Example: <a asp-controller="Home" asp-action="About">About Us</a>

• HTML Helpers (@Html.ActionLink(...)):

  • They are C# extension methods on the HtmlHelper class that are called using @Html. syntax. They generate an HTML string.
  • The syntax is C# method invocation mixed with Razor, which can be less readable and feels more like server-side code intrusion.
  • They are the "classic" way from older ASP.NET MVC versions but are still fully supported.
  • Example: @Html.ActionLink("About Us", "About", "Home")

Comparison: Tag Helpers are generally superior because they provide a more natural HTML authoring experience, while HTML Helpers are still useful for programmatic or complex scenarios where you need to build HTML in a more logic-heavy way.

Answer: Both are used for reusing UI, but they have different levels of complexity and responsibility.

• Partial View (<partial> or @Html.Partial):

  • Is a fragment of Razor markup (.cshtml file) designed to be rendered inside another view.
  • It does not have its own logic. It directly uses the model and ViewData of its parent view. It's essentially a "dumb" piece of reusable markup.
  • Use Case: Simple, repetitive UI elements like an address display form, a product card in a list, or a standard page header that doesn't require its own data fetching.

• View Component:

  • Is a more powerful, self-contained unit that consists of a C# class (inheriting from ViewComponent) and a Razor view.
  • It encapsulates its own business logic and can fetch its own data, independent of the controller that renders the parent view. It's like a mini-controller.
  • Use Case: Complex, dynamic UI widgets like a shopping cart summary, a login panel, a tag cloud, or a dynamic navigation menu that needs to query a database.

In summary: Use a Partial View for simple UI reuse. Use a View Component when the reusable UI needs its own logic or data.

Answer: These are special, hierarchically processed files that help reduce repetitive code in views.

• _ViewStart.cshtml: The code in this file is executed at the start of each view's rendering process. Its most common use is to set the Layout property for all views within a folder and its subfolders. This prevents you from having to write Layout = "_Layout.cshtml"; in every single view file.

  Example (_ViewStart.cshtml):

  @{
      Layout = "_Layout";
  }

• _ViewImports.cshtml: This file is not for executing code, but for providing directives that are imported into all views in a folder and its subfolders. It is commonly used for:

  • @using: To import namespaces, so you don't have to add @using MyWebApp.ViewModels to every view.
  • @model: To specify a base model type if applicable.
  • @addTagHelper: To make Tag Helpers available to all views without declaring them individually.
  • @inject: To make a globally available service accessible.

  Example (_ViewImports.cshtml):

  @using MyWebApp
  @using MyWebApp.Models
  @addTagHelper *, Microsoft.AspNetCore.Mvc.TagHelpers

Answer: You can inject a service directly into a view using the @inject directive.

Syntax: @inject IMyService MyService

You can then use the MyService variable directly in the view's C# code.

Example:

@inject IFeatureFlagService FeatureFlags

@if (FeatureFlags.IsNewMenuEnabled())
{
    // Render new menu
}
else
{
    // Render old menu
}

Pros:

• It's a very convenient and clean way to access application-wide services (like feature flags, localization, or configuration) that are purely related to UI concerns.
• It avoids "polluting" the view model with properties that aren't core to the model's data.

Cons / Risks:

• It can lead to a violation of the Separation of Concerns principle. Developers might be tempted to inject data repositories or business logic services directly into the view, mixing presentation logic with business or data access logic. This makes the view harder to test and maintain.
• Best Practice: Only inject services that deal with UI-specific concerns. For data, the controller should fetch it, populate a view model, and pass that model to the view.

Answer: @Html.Raw() is a method used to render a string directly to the output stream without the default HTML encoding that Razor applies. By default, Razor encodes strings to prevent Cross-Site Scripting (XSS) attacks (e.g., <script> becomes <script>).

Use Case: You should only use @Html.Raw() when you are rendering HTML content that you know is safe and comes from a trusted source. For example, if you have a Content Management System where an administrator (a trusted user) enters HTML into a rich-text editor, you might use @Html.Raw() to render that content.

Major Risk: The primary risk is a Cross-Site Scripting (XSS) vulnerability. If you use @Html.Raw() on any string that originated from an untrusted user, a malicious user could inject <script> tags or other harmful markup. This script would then be executed in the browser of anyone viewing the page, potentially stealing cookies, session tokens, or performing other malicious actions on behalf of the user.

Rule of thumb: Never use @Html.Raw() on un-sanitized user input. Always be 100% certain of the origin and safety of the content.