SQL VIEW

 

SQL CREATE VIEW Statement

In SQL, a view is a virtual table based on the result-set of an SQL statement. A view is a virtual table whose contents are defined by a query. Like a table, a view consists of a set of named columns and rows of data. Unless indexed, a view does not exist as a stored set of data values in a database. The rows and columns of data come from tables referenced in the query defining the view and are produced dynamically when the view is referenced

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

You can add SQL statements and functions to a view and present the data as if the data were coming from one single table.

A view is created with the CREATE VIEW statement. 

Types of views

Besides the standard role of basic user-defined views, SQL Server provides the following types of views that serve special purposes in a database.

Indexed Views

An indexed view is a materialized view. This means the view definition has been computed and the resulting data stored just like a table. You index a view by creating a unique clustered index on it. Indexed views can dramatically improve the performance of some types of queries. Indexed views work best for queries that aggregate many rows. They are not well-suited for underlying data sets that are frequently updated.

Partitioned Views

A partitioned view joins horizontally partitioned data from a set of member tables across one or more servers. A partitioned view makes the data appear as if from one table. A view that joins member tables on the same instance of SQL Server is a local partitioned view.

System Views

System views expose catalog metadata. You can use system views to return information about the instance of SQL Server or the objects defined in the instance.

CREATE VIEW Syntax

CREATE VIEW view_name  AS

Select Column1,Column2, Column3, ...

FROM table_name

WHERE condition:

Table named Grahak with column and rows with Values

CustomerID	CustomerName	Contactnumber	Address	City	PostalCode	Country
1	Ram	9871234563	#47 Sector 22	Chandigah	160002	Japan
2	Sham	9876543216	#4110 Sector 122	Mohali	140002	India
3	Shivam	3278955152	#5 Sector 88	Gurgaon	180002	UAE
4	Ravi	2277894562	#5 Sector 48	Badlapur	220002	North korea

Example

CREATE VIEW [Indian_Grahak] AS
SELECT CustomerName, ContactName
FROM GRAHAK
WHERE Country = 'India';

We can query the view above as follows:

Example

SELECT * FROM [Indian_Grahak];

 

 

 

ASP.Net Interview Questions

Question:  What id full form of ASP?

Answer:    Full-Form of ASP is Active Server Pages.

Question:    What id ASP.Net ?

Answer:   ASP.NET is a web application framework designed and developed by Microsoft. ASP.NET is open source and a subset of the .net Framwork and successor of the classic ASP(Active Server Pages). With version 1.0 of the .NET Framework, it was first released in January 2002. ASP.NET is Microsoft's framework to build Web applications.ASP.Net is based on Client-Server technology the Client being the Browser which sends request and the Server which has the ASP.Net Code executes the request.

Question:  Where is an ASP.Net Application installed?

Answer:      ASP.Net is Client-Server based technology the Client being the Browser which sends call or requisition and the Server which has the ASP.Net Code executes the call or requisition that is send by the Browser. ASP.Net Applications installed on a Windows Server machine with the Internet Information Services (IIS) application.

Question:  What is the meaning of ViewState in ASP.Net?

Answer:   ViewState is used to manage the State of a Web Page. ViewState data is stored in Hashed Format in a Hidden Field on the Web Page. ViewState helps save  the values of Field Form across PostBacks.

Question:  What is the meaning of PostBack in ASP.Net?

Answer:   In website development, a postback is the Exchange/Swap of information between servers to report a user's action on a website, network. PostBack is an ASP.Net term for Form Submission. When User clicks a Submit Button, the Form data is sent to the Server for processing, this process is called PostBack.

Question:  What is the meaning of Session in ASP.Net?

Answer:   Session is used to store value on Server Side in ASP.Net. Session is User-Specific means the data is not shared and it is unique for each User. A Session object stores information about, or change settings for a user session.

Question:  What is the Web.config file?

Answer:   Web.config file Stores configuration settings for an ASP.NET application. A configuration file (web.config) is used to manage various settings that define a website. ASP.NET Configuration system is used to describe the properties and behaviors of various aspects of ASP.NET applications.

Question: What is the difference between Server.Transfer and Response.Redirect?

Answer:   Server.Transfer redirects within the server, Response.Redirect sends a client-side redirect.  Server.Transfer Transfers control to another page on the server without involving the browser. The original page's context (e.g., form data, query string) is passed to the new page. Response.Redirect Sends a 302 (or 301 for permanent redirects) HTTP status code to the browser, instructing it to request a different URL

Benefits of Store Procedure

 

                       A Stored Procedure is a type of code in SQL that can be stored for later use and can be used many times. Stored procedures are like fundamentally SQL query. So, whenever you need to execute the query, in place of calling it you can direct call the stored procedure. You can also pass parameters to a stored procedure, so that the stored procedure can works based on the parameter values that is passed. There are several reasons, but the main one is security. Stored procedures are parameterized, so they are less pregnable to attacks. here i am explain about Store Procedure with example.  

Stored Procedure Syntax or code

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

Execute a Stored Procedure

EXEC procedure_name;

Demo Database

Table named Grahak with column and rows with Values

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Ram	9871234563	#47 Sector 22	Chandigah	160002	Japan
2	Sham	9876543216	#4110 Sector 122	Mohali	140002	India
3	Shivam	3278955152	#5 Sector 88	Gurgaon	180002	UAE
4	Ravi	2277894562	#5 Sector 48	Badlapur	220002	North korea

Stored Procedure Example

Below mentioned SQL code creates a stored procedure named " complete_Grahaks " shich selects all data from the " Grahak " table:

Example

CREATE PROCEDURE complete_Grahaks
AS
SELECT * FROM Grahak
GO;

Execute the stored procedure above as follows:

Example

EXEC complete_Grahaks;

Stored Procedure With One Parameter

The following SQL statement creates a stored procedure that selects Customers from a particular City from the " Grahak " table:

Example

CREATE PROCEDURE complete_Grahaks @City nvarchar(30)
AS
SELECT * FROM Grahak WHERE City = @City
GO;

Execute the stored procedure above as follows:

Example

EXEC complete_Grahaks @City = 'India';

Stored Procedure With Multiple Parameters

Creating store procedure with multiple parameters is very easy. Just list each parameter and the data type separated by a comma as shown below.

The following SQL statement creates a stored procedure that selects Customers from a particular City with a particular PostalCode from the "Grahak" table:

Example

CREATE PROCEDURE complete_Grahaks @City nvarchar(30), @PostalCode nvarchar(10)
AS
SELECT * FROM Grahak WHERE City = @City AND PostalCode = @PostalCode
GO;

Execute the stored procedure above as follows:

EXEC complete_Grahaks @City = 'Chandigarh', @PostalCode = '160002'; 

 

 Follow the Asp.Net channel on WhatsApp: https://whatsapp.com/channel/0029VbAovoCBA1f6Fnn2kU3X