VIVA Questions

SQL Server
1. Difference between unique key and primary key?
Ans:

Primary Key Unique Key

Unique identifier for rows of a table Unique identifier for rows of a table when
primary key is not present

Cannot be NULL Can be NULL

Only one primary key can be present in a table Multiple Unique Keys can be present in a
table

2. Can unique column have multiple null values?

Ans: No (because the second null will be duplicated)

3. What is join and its types?

---- It is used to combine records from two or more tables in a database. A JOIN is a means for combining
fields from two tables by using values common to each.
▪ Inner Join
▪ Left Outer Join
▪ Right Outer Join
▪ Full Outer Join
▪ Cross Join
▪ Self-Join

4. Check constraint and Default constraints

Check Constraint: Used to ensure that only a specific range or list of values can be stored in a
column(age>18)
Default Constraint: Used to store the default values if no value is specified (current date)

5. Define constraints and its types

SQL constraints are used to specify rules for the data in a table. Constraints are used to limit the type of
data that can go into a table
▪ Not null
▪ Primary Key
▪ Foreign Key
▪ Unique Key
▪ Check
▪ Default

6. Stored procedures and parameters

▪ A stored procedure is a prepared SQL code that you can save, so the code can be reused
repeatedly.
▪ So, if you have an SQL query that you write repeatedly, save it as a stored procedure, and then just
call it to execute it.
▪ You can also pass parameters to a stored procedure, so that the stored procedure can act based
on the parameter value(s) that is passed.
7. What is Identity? Parameters of identity? How Identity property works?
▪ Identity column of a table is a column whose value increases automatically.
▪ The value in an identity column is auto generated by the SQL server.
▪ Only one identity column is possible for a table.
▪ A user generally cannot insert a value into an identity column.
▪ Identity column can be used to uniquely identify the rows in the table.
▪ IDENTITY [(seed, increment)]

8. Difference between nvarchar and varchar

▪ The key difference between varchar and nvarchar is the way they are stored; varchar is stored as
regular 8-bit data (1 byte per character) and nvarchar stores data at 2 bytes per character.
▪ Due to this reason, nvarchar can hold up to 4000 characters and it takes double the space as SQL
varchar.

9. Difference between commit and rollback

Commit: -
• If transaction is successful, commit it
• A commit statement guarantees all the transaction modifications are made a permanent part of the dataset
• A commit also frees resources, such as locks, used by the transaction.
• Syntax: - COMMIT
Rollback: -
• If there is any error in transaction, or if the user decides to cancel the transaction, then roll the transaction
back
• A Rollback statement backs out all the modifications made in the transaction by returning the data to the
state it was in the start of the transaction
• A Rollback also frees resources held by the transaction.
• Syntax: - ROLLBACK

10. View in SQL

▪ Views in SQL are kind of virtual tables.
▪ A view also has rows and columns as they are in a real table in the database.
▪ We can create a view by selecting fields from one or more tables present in the database.
▪ A View can either have all the rows of a table or specific rows based on certain condition.

11. Can you delete a record form table using view?

Yes

12. Can we apply check constraints to int and varchar?

Yes

13. What is @@Identity?

We use system function @@IDENTITY to return the maximum used IDENTITY value in a table for
the IDENTITY column under the current session. Once we insert a row in a table, the @@IDENTITY
function column gives the IDENTITY value generated by the statement.

14. What is Global variable?

• Global variable stores session related information.
• The system maintains global variable.
• The global variable starts with @@.
 15. Difference between where and having clause, between and like
BETWEEN: - This keyword can be used to display rows based on a range of values.
LIKE: - This operator is used to perform pattern matching or wildcard search.

16. How do you get the last record from the table?
Select * from table order by ID desc limit 1
(Or)
Select top 1 * from tbl_name order by 1 desc

MVC and Entity Framework

1. Explain EDM (Entity Data Model)?
EDM abstracts (hides) the data model and exposes a conceptual schema of the same to the developers
using a layered approach.
That layers exposed are:
▪ Conceptual layer – entity classes and their relationships
▪ Mapping Layer – maps the relationship btw the conceptual and storage layer
▪ Storage Layer – tables, views, and stored procedures

2. Action Filters in MVC

Action filter executes before and after an action method executes. Action filter attributes can be applied to
an individual action method or to a controller. (Output Cache, Handle error, Authorize)
3. Action Result and its types
Controller is responsible for returning response to the received request, which is indicated as action
method’s return type. These return type of action methods are called action results.
▪ Action Result (base class)
▪ View Result – renders. cshtml view page
▪ Partial View Result – renders a partial view that can be called in regular view or layout view
▪ Redirect Result - redirects to given URL
▪ Redirect to Action Result/ Redirect to Route Result – takes to another action
▪ Json Result – gives serialized json data
▪ File Path Result – download a file from server
▪ Content Result - provides user defined content
▪ Java Script result – gives JavaScript that can be executed on the client browser
▪ Empty result – used when action method must return null, or its return type is void

4. What is state management and its type?

By default, web applications are stateless, they don’t remember the values stored between 2 multiple
requests of same page or 2 different pages. So, to retain the state, we use state management techniques:
Old (Session, Application, Query String, Cookies)
▪ View bag
▪ View Data
▪ Temp Data

5. Explain ViewBag, ViewData and TempData.

ViewBag, ViewData, and TempData all are objects in ASP.NET MVC and these are used to pass the data in
various scenarios. The following are the scenarios where we can use these objects.
 1. Pass the data from Controller to View.
2. Pass the data from one action to another action in the same Controller.
3. Pass the data in between Controllers.
4. Pass the data between consecutive requests.

ViewBag
ViewBag is a dynamic object to pass the data from Controller to View. And, this will pass the data as a property of
object ViewBag. And we have no need to typecast to read the data or for null checking. The scope of ViewBag is
permitted to the current request and the value of ViewBag will become null while redirecting.

1. Public ActionResult Index()

2. {
3. ViewBag.Title = “Welcome”;
4. return View();
5. }

View

1. <h2>@ViewBag.Title</h2>

ViewData
ViewData is a dictionary object to pass the data from Controller to View where data is passed in the form of key-
value pair. And typecasting is required to read the data in View if the data is complex and we need to ensure null
check to avoid null exceptions. The scope of ViewData is like ViewBag and it is restricted to the current request and
the value of ViewData will become null while redirecting.

Controller:

1. Public ActionResult Index()

2. {
3. ViewData[”Title”] = “Welcome”;
4. return View();
5. }

View

1. <h2>@ViewData[“Title”]</h2>

TempData
TempData is a dictionary object to pass the data from one action to other action in the same Controller or different
Controllers. Usually, TempData object will be stored in a session object. Tempdata is also required to typecast and
for null checking before reading data from it. TempData scope is limited to the next request and if we want Tempdata
to be available even further, we should use Keep and peek.

1. Public ActionResult Index()

2. {
3. TempData[”Data”] = “I am from Index action”;
4. return View();
5. }
6.
 7. Public string Get()
8. {
9. return TempData[”Data”] ;
10. }

To summarize, ViewBag and ViewData are used to pass the data from Controller action to View and TempData is
used to pass the data from action to another action or one Controller to another Controller.

6. What is validation and how can we implement in MVC?

Used to validate the data filled in the web page. The attributes that perform the validation are
▪ [Required]
▪ [Display (Name = “Email Id”)]
▪ [Datatype (Datatype. Password)]
▪ [Range ()]
▪ [Regular Expression ()]
▪ [Phone]

7. Action name attribute in MVC

ActionName attribute is an action selector which is used for a different name of the action method. We use
ActionName attribute when we want that action method to be called with a different name instead of the
actual name of the method

8. Explain routing (Custom routing). Types of routing

• Routing is a pattern matching system that monitor the incoming request and maps it to
appropriate controller and action.
• At runtime, the appropriate controller and action is called to display the respective view.

9. Tell about layer in Entity Framework.

Conceptual layer – comprises of .NET entity classes and their relationships
Related to business domain not the data storage
Removes application dependency from data sources
Mapping Layer – maps the relationship btw the conceptual and storage layer
Storage Layer – comprises of tables, views, and stored procedures
Queried using data source specific syntax
Can change without changing the conceptual model, by using appropriate mappings

10. How to find primary key in Entity Framework?

11. What is dbcontext classes?
• Is an adapter or bridge between the entity types and the database tables.
• Is responsible for performing operations like querying, inserting, updating, or deleting on the
database.
• The Entity Framework designer automatically generates a class that derives from the dBContext
class
• The properties and methods of this class can be used to work with the database.
12. What is DB set and entity set? Uses?
13. Use of Find in Entity Framework.
14. Scalar and Navigational property in Entity framework
15. Layout.cshtml
16. Difference between http get and post
17. What is LINQ to entities
This is used to query the object model of the data source
18. Define data model, object model
19. If we want to use same view throughout the application, what do we use?
20. Rules of razor view
21. Brief of entity framework
22. How do we use handle error in MVC?
23. Stored procedures and parameters
• Are a group of statements compiled into a single execution plan and stored as a database object
• Enforce abstraction and improves efficiency as they are precompiled. Entity Framework provides
support to invoke stored procedures.
• Are mapped as functions in the EDM. They must be associated with an insert/update/delete
operation on the corresponding entity.
24. Partial vs render partial
Partial method renders the mentioned partial view. This accept partial view’s name as a string parameter
and returns MvcHtmlString.
The RenderPartial method is similar to Partial method but this don’t have any return type and directly writes
resulted html.
25. How to search value using pk
26. DB first approach
• This approach sets up an object model based on an existing database, hence the name database
first approach
27. What is controller, base class of controller?
• Controller class of MVC is derived from System.Web.Mvc.Controller
• The public methods of this controller are called action methods
• MVC applications may contain one or more controllers in Controller folder

28. How do you code inside View?

29. Where do you find route table?
Ans: routeconfig.cs file inside app_start folder

30. How to use stored procedures in MVC?

31. What does precompile means in stored procedure
32. Difference between layout view and partial view
Layout View: -
• Layout is a view, which is used to design common structure of the page used throughout the
applications.
• The layout view page is like a master page in ASP.NET webforms app
• Layout views can be shared with multiple controller’s action views
• This layout page should be stored in the separate shared folder
Partial View: -
• It is a re-usable view (UI component), that can be used in multiple views of an application
• This reduces duplicate code by reusing same partial view in multiple places.
• Partial view can be rendered in a layout view or any other normal action views.
• The partial view is same as user control in ASP.NET web forms.
33. What will router do?
34. Why do we need to perform model translation in MVC?
Ans: MVC gives you a starting place to translate your ideas into code, and it also makes coming back to your
code easier, since you will be able to identify which code does what. In addition, the organizational standard
MVC promotes makes it easy for other developers to understand your code.

35. How to modify edmx if changes are made in the database?

Update model
36. What are helper methods?
Used to describe some method that is reused often by other methods or parts of a program

37. Use of OUT in stored procedures (output parameter)

38. Ways of collecting data from view
Ans: Model object, form collection, request object, normal parameters in action method

39. Form Collection

40. Conceptual layer and storage layer
41. Explain View Result and redirect result
View Result:- Renders a regular .cshtml view page
Redirect Result:- Redirects to given URL
42. How to transfer data from controller to view?
Ans: view data, view bag and temp data

43. Can you delete a record form table using view?

44. What is ORM?
• An application must be visualized as two separate layers: a data model and an object model.
• .NET applications may connect to any type of data source, some of them being relational
databases, flat files, collections, XML data or Datasets. These make the data model.
• Irrespective of what data source is being used, the .NET classes can visualize the data only as
objects. This Constitutes the object model.
45. Joins in EDM – navigational properties

46. What is the namespace of dbcontext?

System.Data.Entity

47. How to modify edmx if changes are made in the database?

48. Why views?
49. Which approach other than db one?
50. In the scenario to search which one do you choose linq or lambda expression?
51. Template for searching data by id?
52. Just after getting logged how can we immediately navigate to another page?
53. Mapping Model
54. How do you connect to any other database from ado.net?
55. Sql connection inado.net
56. Where will u find route config?
App_Start
57. Can we directly return json?
58. Return type in action method
Controller is responsible for returning a response to the received request, which is indicated as action
method’s return type.
These return types of action methods are classified as cation results in ASP.NET MVC
59. Explain jsonresult
Gives serialized json data
60. Can we return json through viewresult?
61. Hyperlink in mvc view?
Used for moving from one view to another. We can use html. ActionLink() inside the view code to achieve
this.

62. Can error.cshtml will be one file or can be more?

63. FirstOrDefault and First
 The difference btw these two is that First () will throw an exception if there is no result data for the supplied
criteria where as FirstOrDefault () will return the default value (null) if there is no result data

64. If you want to set view as default view (i.e., it should get returned first when we execute our program)
Where can we place that view?
We should see which action method controller it is returning first, put return View () in that method
(Or)
You need to set default controller and action method in route config file.

65. Scaffolding templates

Create, details, list, delete, update, empty

Scenario
1. What columns can we expect in admin user table
2. Explain the flow of the scenario. How to create an MVC app?
3. Explained a scenario and asked there are already y columns and need to add 1 more, s what changes we
need to do
4. Case study scenario implementation in MVC

Old Scenarios Given

1. Holiday planner hotel to help customers book the hotel. Database tables: Admin and customer. Admin
can create, edit, delete customers, and search customer details by name.
Define data model, object model

A data model (or datamodel) is an abstract model that organizes elements of data and standardizes how they
relate to one another and to the properties of real-world entities
An object data model is a data model that treats data sets as “objects” by assigning properties and values to
them

How do we use handle error in MVC?

There are several ways to do MVC error handling. You should always specify a default error page via your
web.config <customErrors> and log unhandled exceptions that get called back to your HttpApplication Error
method.
5 Ways to do MVC Error Handling

Web.Config customErrors
MVC HandleErrorAttribute
Controller.OnException method
HttpApplication Application_Error event
Collect exceptions via .NET profiling with Retrace

View in mvc
The view component contains the logic to represent the model data as a user interface with which the end-user
can interact

. What does precompile means in stored procedure

Stored procedures are compiled into a query plan the first time they are run.

Use of OUT in stored procedures

OUT parameter is used to return multiple data from mysql stored procedures

Form collection
A Form Collection is the data that is passed in a form submission when we use the HTTP POST Method. What we
will do in this application. We will learn retrieving the data by using Form Collection.

Edmx
An EDMX (Entity Data Model XML) file is an XML file describing the relationship between different models of the
Entity Framework

How do you connect to any other database from ado.net

Using DataSet and DataAdapter

Can error.cshtml will be one file or can be more

It’s just a generic page for errors that will help the user fix their issues. It’s not meant to be a whole website.

Scaffolding
Scaffolding a database produces an Entity Framework model from an existing database. The resulting entities are
created and mapped to the tables in the specified database.

Advantages of entity framework

It provides auto generated code
It reduce development time
It reduce development cost
It enables developers to visually design models and mapping of database
It provides capability of programming a conceptual model.

Db context class
The context class is used to query or save data to the database.

What is mvc
Model-view-controller (MVC) is the name of a methodology or design pattern for successfully and efficiently
relating the user interface to underlying data models. The MVC pattern is widely used in program development
with programming languages

Which inheritance is supported in entity framework

TPH inheritance
The TPC inheritance states that each concrete class (a class which can be instantiated) in the hierarchy of entities
is mapped to a separate table in storage schema.

Delete vs truncate
TRUNCATE is a type of DDL command while DELETE is a kind of DML command. One can use TRUNCATE for
deleting an entire set of data from a given table while the DELETE command only deletes specific data from the
entire table
Scenario – Acc Corp

How many controller – 2, 1 is home or default controller for login page and another is coverage controller for all
other actions related to scenario

How many views – 5, login, dependent info, coverage info, increase coverage, add dependent info

How many actions – 4