{"id":866,"date":"2023-11-18T10:09:18","date_gmt":"2023-11-18T10:09:18","guid":{"rendered":"https:\/\/www.softwaretraininginchennai.com\/blog\/?p=866"},"modified":"2023-11-18T10:09:18","modified_gmt":"2023-11-18T10:09:18","slug":"view-in-sql","status":"publish","type":"post","link":"https:\/\/www.softwaretraininginchennai.com\/blog\/view-in-sql\/","title":{"rendered":"View in SQL"},"content":{"rendered":"

View in SQL<\/strong><\/p>\n

A view in SQL is a virtual table created from the results of a SELECT query. Views simplify complex queries, encapsulate logic, and provide an abstraction layer over the underlying tables. They can be used to present data in a more meaningful or secure manner than querying the underlying tables directly. Here’s a quick rundown of how to create and use views in SQL:<\/p>\n

Creating a View:<\/strong><\/p>\n

The CREATE VIEW statement is used to create a view. The fundamental syntax is as follows:<\/p>\n\n\n\n
CREATE VIEW view_name AS<\/em><\/span><\/p>\n

SELECT column_name_1, column_name_2, …<\/em><\/span><\/p>\n

FROM table_name<\/em><\/span><\/p>\n

WHERE condition;<\/em><\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

For example, let us create a new table called studentdata using the syntax shown below.<\/p>\n\n\n\n
create table studentdata(<\/span><\/em><\/p>\n

sno int,<\/span><\/em><\/p>\n

sname varchar(100),<\/span><\/em><\/p>\n

slocaction varchar(100),<\/span><\/em><\/p>\n

smothertongue varchar(100));<\/span><\/em><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Insert some records as well using the syntax shown below.<\/p>\n\n\n\n
insert into studentdata values(1, ‘RAM’, ‘CHENNAI’, ‘TAMIL’)<\/span><\/em><\/p>\n

insert into studentdata values(2, ‘RAJ’, ‘CHENNAI’, ‘HINDI’)<\/span><\/em><\/p>\n

insert into studentdata values(3, ‘RAVI’, ‘HYDERABAD’, ‘TELUGU’)<\/span><\/em><\/p>\n

insert into studentdata values(4, ‘RAJESH’, ‘HYDERABAD’, ‘TAMIL’)<\/span><\/em><\/p>\n

insert into studentdata values(5, ‘RAJIV’, ‘CHENNAI’, ‘TELUGU’)<\/span><\/em><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Select query syntax can be used to display the table records.<\/p>\n\n\n\n
SELECT * FROM studentdata<\/span><\/em><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Create a view using the syntax shown below.<\/p>\n\n\n\n
CREATE VIEW TN AS<\/em><\/span><\/strong><\/p>\n

SELECT * FROM studentdata<\/em><\/span><\/strong><\/p>\n

WHERE slocaction = ‘CHENNAI’<\/em><\/span><\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Using a View:<\/strong><\/p>\n

After you’ve created a view, you can query it like a regular table with the following syntax:<\/p>\n\n\n\n
SELECT * FROM view_name<\/span><\/em><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

With reference to the given example,<\/p>\n\n\n\n
SELECT * FROM TN<\/span><\/em><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Updating a View:<\/strong><\/p>\n

You can use the ALTER VIEW statement to update views:<\/p>\n\n\n\n
ALTER VIEW view_name AS<\/span><\/em><\/p>\n

SELECT column_name_1, column_name_2, …<\/span><\/em><\/p>\n

FROM table_name<\/span><\/em><\/p>\n

WHERE condition;<\/span><\/em><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

In Microsoft SQL Server, you can use the CREATE OR ALTER VIEW statement to replace an existing view or create a new one. This is similar to the CREATE OR REPLACE VIEW syntax found in other database systems. Using the preceding example:<\/p>\n\n\n\n
ALTER VIEW TN AS<\/span><\/em><\/strong><\/p>\n

SELECT * FROM studentdata<\/span><\/em><\/strong><\/p>\n

WHERE smothertongue = ‘TAMIL’<\/span><\/em><\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Dropping a View: \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <\/strong><\/p>\n

The DROP VIEW statement is used to remove a view:<\/p>\n\n\n\n
DROP VIEW employee_view;<\/span><\/em><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Thinking back to the earlier illustration:<\/p>\n\n\n\n
DROP VIEW TN<\/span><\/em><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\u00a0<\/strong><\/p>\n

Multiple Views:<\/strong><\/p>\n

Views enable you to create a virtual table in a relational database based on the results of a SELECT query. You can use JOIN operations in your SELECT statement to combine data from multiple tables in a view. Here’s a general guide to creating a view with multiple tables:<\/p>\n

Consider the following scenario: you have two tables: customers and orders. The customers table contains information about customers, and the orders table contains information about the orders that these customers have placed.<\/p>\n\n\n\n
— Sample customers table<\/em><\/p>\n

CREATE TABLE customers (<\/span><\/em><\/p>\n

\u00a0\u00a0\u00a0 CustomerId INT PRIMARY KEY,<\/span><\/em><\/p>\n

\u00a0\u00a0\u00a0 CustomerName VARCHAR(50),<\/span><\/em><\/p>\n

\u00a0\u00a0\u00a0 Email VARCHAR(50)<\/span><\/em><\/p>\n

);<\/span><\/em><\/p>\n

— Sample orders table<\/em><\/p>\n

CREATE TABLE orders (<\/span><\/em><\/p>\n

\u00a0\u00a0\u00a0 OrderId INT PRIMARY KEY,<\/span><\/em><\/p>\n

\u00a0\u00a0\u00a0 CustomerId INT,<\/span><\/em><\/p>\n

\u00a0\u00a0\u00a0 OrderDate DATE,<\/span><\/em><\/p>\n

\u00a0\u00a0\u00a0 TotalAmount DECIMAL(10, 2),<\/span><\/em><\/p>\n

\u00a0\u00a0\u00a0 FOREIGN KEY (CustomerId) REFERENCES customers(CustomerId)<\/span><\/em><\/p>\n

);<\/span><\/em><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Let us also insert some sample records into both tables using the insert query:<\/p>\n\n\n\n
INSERT INTO customers VALUES (1, ‘RAM’, ‘ram@mail.com’)<\/span><\/em><\/p>\n

INSERT INTO customers VALUES (2, ‘RAJ’, ‘raj@mail.com’)<\/span><\/em><\/p>\n

INSERT INTO customers VALUES (3, ‘RAVI’, ‘ravi@mail.com’)<\/span><\/em><\/p>\n

INSERT INTO orders VALUES (101, 1, ‘2023\/11\/02’, 10000)<\/span><\/em><\/p>\n

INSERT INTO orders VALUES (102, 2, ‘2023\/11\/03’, 12000)<\/span><\/em><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Check it out with the following select query:<\/p>\n\n\n\n
SELECT * FROM customers<\/span><\/em><\/p>\n

SELECT * FROM orders<\/span><\/em><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Let’s now create a view that combines data from both tables:<\/p>\n\n\n\n
CREATE VIEW CustomerOrderView AS<\/span><\/strong><\/em><\/p>\n

SELECT<\/span><\/strong><\/em><\/p>\n

\u00a0\u00a0\u00a0 c.CustomerId,<\/span><\/strong><\/em><\/p>\n

\u00a0\u00a0\u00a0 c.CustomerName,<\/span><\/strong><\/em><\/p>\n

\u00a0\u00a0\u00a0 c.Email,<\/span><\/strong><\/em><\/p>\n

\u00a0\u00a0\u00a0 o.OrderId,<\/span><\/strong><\/em><\/p>\n

\u00a0\u00a0\u00a0 o.OrderDate,<\/span><\/strong><\/em><\/p>\n

\u00a0\u00a0\u00a0 o.TotalAmount<\/span><\/strong><\/em><\/p>\n

FROM<\/span><\/strong><\/em><\/p>\n

\u00a0\u00a0\u00a0 customers c<\/span><\/strong><\/em><\/p>\n

JOIN<\/span><\/strong><\/em><\/p>\n

\u00a0\u00a0\u00a0 orders o ON c.CustomerId = o.CustomerId;<\/span><\/strong><\/em><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

The CustomerOrderView view is created in this example by selecting specific columns from both the customers and orders tables. The JOIN clause is used to join rows from both tables using the CustomerId column in common.<\/p>\n

After you’ve created the view, you can query it like any other table:<\/p>\n\n\n\n
SELECT * FROM CustomerOrderView<\/span><\/em><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Remember that the exact syntax can change based on the database management system (SQL, PostgreSQL, MySQL, etc.) you are using. Depending on the system you are working with, modify the SQL statements as necessary.<\/p>\n","protected":false},"excerpt":{"rendered":"

View in SQL A view in SQL is a virtual table created from the results of a SELECT query. Views simplify complex queries, encapsulate logic, and provide an abstraction layer over the underlying tables. They can be used to present data in a more meaningful or secure manner than querying the underlying tables directly. Here’s […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[277,3,281,299,295,298,297,4,6,279,235,234],"tags":[300,270,292,284],"class_list":["post-866","post","type-post","status-publish","format-standard","hentry","category-asp-net-core","category-dot-net-training-in-chennai","category-dot-net-training-in-india","category-dotnet-training-in-karaikudi","category-dotnet-training-in-madurai","category-dotnet-training-in-pudukottai","category-dotnet-training-in-trichy","category-information-on-dotnet","category-mvc-training-tutorials","category-oop-concept","category-rdbms","category-sql-server","tag-database-management-system","tag-dotnet-and-sql-job-support-from-india","tag-learn-c-dotnet","tag-learn-sql"],"yoast_head":"\nView in SQL | Maria Academy<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.softwaretraininginchennai.com\/blog\/view-in-sql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"View in SQL | Maria Academy\" \/>\n<meta property=\"og:description\" content=\"View in SQL A view in SQL is a virtual table created from the results of a SELECT query. Views simplify complex queries, encapsulate logic, and provide an abstraction layer over the underlying tables. They can be used to present data in a more meaningful or secure manner than querying the underlying tables directly. Here’s […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.softwaretraininginchennai.com\/blog\/view-in-sql\/\" \/>\n<meta property=\"og:site_name\" content=\"Maria Academy\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DotnetTrainingChennai\" \/>\n<meta property=\"article:published_time\" content=\"2023-11-18T10:09:18+00:00\" \/>\n<meta name=\"author\" content=\"admin\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@dotnettraining2\" \/>\n<meta name=\"twitter:site\" content=\"@dotnettraining2\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"admin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.softwaretraininginchennai.com\/blog\/view-in-sql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.softwaretraininginchennai.com\/blog\/view-in-sql\/\"},\"author\":{\"name\":\"admin\",\"@id\":\"https:\/\/www.softwaretraininginchennai.com\/blog\/#\/schema\/person\/e7dbda3490333ae356b6ad09076c8a6e\"},\"headline\":\"View in SQL\",\"datePublished\":\"2023-11-18T10:09:18+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.softwaretraininginchennai.com\/blog\/view-in-sql\/\"},\"wordCount\":657,\"commentCount\":0,\"keywords\":[\"database management system\",\"dotnet and sql job support from India\",\"learn c# dotnet\",\"Learn SQL\"],\"articleSection\":[\"ASP.NET Core\",\"dot net training in chennai\",\"Dot Net training in india\",\"dotnet training in karaikudi\",\"dotnet training in madurai\",\"dotnet training in pudukottai\",\"dotnet training in Trichy\",\"information on dotnet\",\"MVC Training Tutorials\",\"OOP Concept\",\"RDBMS\",\"SQL Server\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.softwaretraininginchennai.com\/blog\/view-in-sql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.softwaretraininginchennai.com\/blog\/view-in-sql\/\",\"url\":\"https:\/\/www.softwaretraininginchennai.com\/blog\/view-in-sql\/\",\"name\":\"View in SQL | Maria Academy\",\"isPartOf\":{\"@id\":\"https:\/\/www.softwaretraininginchennai.com\/blog\/#website\"},\"datePublished\":\"2023-11-18T10:09:18+00:00\",\"author\":{\"@id\":\"https:\/\/www.softwaretraininginchennai.com\/blog\/#\/schema\/person\/e7dbda3490333ae356b6ad09076c8a6e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.softwaretraininginchennai.com\/blog\/view-in-sql\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.softwaretraininginchennai.com\/blog\/view-in-sql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.softwaretraininginchennai.com\/blog\/view-in-sql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.softwaretraininginchennai.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"View in SQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.softwaretraininginchennai.com\/blog\/#website\",\"url\":\"https:\/\/www.softwaretraininginchennai.com\/blog\/\",\"name\":\"Maria Academy\",\"description\":\"Dot Net Training in Chennai, Best Dot Net Training Institute in Chennai, .Net Training in Chennai\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.softwaretraininginchennai.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.softwaretraininginchennai.com\/blog\/#\/schema\/person\/e7dbda3490333ae356b6ad09076c8a6e\",\"name\":\"admin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f68fba18793457e0192658e2fe53431c0fb4a1d551aef61c57c1847324110d80?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f68fba18793457e0192658e2fe53431c0fb4a1d551aef61c57c1847324110d80?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f68fba18793457e0192658e2fe53431c0fb4a1d551aef61c57c1847324110d80?s=96&d=mm&r=g\",\"caption\":\"admin\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"View in SQL | Maria Academy","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.softwaretraininginchennai.com\/blog\/view-in-sql\/","og_locale":"en_US","og_type":"article","og_title":"View in SQL | Maria Academy","og_description":"View in SQL A view in SQL is a virtual table created from the results of a SELECT query. Views simplify complex queries, encapsulate logic, and provide an abstraction layer over the underlying tables. They can be used to present data in a more meaningful or secure manner than querying the underlying tables directly. Here’s […]","og_url":"https:\/\/www.softwaretraininginchennai.com\/blog\/view-in-sql\/","og_site_name":"Maria Academy","article_publisher":"https:\/\/www.facebook.com\/DotnetTrainingChennai","article_published_time":"2023-11-18T10:09:18+00:00","author":"admin","twitter_card":"summary_large_image","twitter_creator":"@dotnettraining2","twitter_site":"@dotnettraining2","twitter_misc":{"Written by":"admin","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.softwaretraininginchennai.com\/blog\/view-in-sql\/#article","isPartOf":{"@id":"https:\/\/www.softwaretraininginchennai.com\/blog\/view-in-sql\/"},"author":{"name":"admin","@id":"https:\/\/www.softwaretraininginchennai.com\/blog\/#\/schema\/person\/e7dbda3490333ae356b6ad09076c8a6e"},"headline":"View in SQL","datePublished":"2023-11-18T10:09:18+00:00","mainEntityOfPage":{"@id":"https:\/\/www.softwaretraininginchennai.com\/blog\/view-in-sql\/"},"wordCount":657,"commentCount":0,"keywords":["database management system","dotnet and sql job support from India","learn c# dotnet","Learn SQL"],"articleSection":["ASP.NET Core","dot net training in chennai","Dot Net training in india","dotnet training in karaikudi","dotnet training in madurai","dotnet training in pudukottai","dotnet training in Trichy","information on dotnet","MVC Training Tutorials","OOP Concept","RDBMS","SQL Server"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.softwaretraininginchennai.com\/blog\/view-in-sql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.softwaretraininginchennai.com\/blog\/view-in-sql\/","url":"https:\/\/www.softwaretraininginchennai.com\/blog\/view-in-sql\/","name":"View in SQL | Maria Academy","isPartOf":{"@id":"https:\/\/www.softwaretraininginchennai.com\/blog\/#website"},"datePublished":"2023-11-18T10:09:18+00:00","author":{"@id":"https:\/\/www.softwaretraininginchennai.com\/blog\/#\/schema\/person\/e7dbda3490333ae356b6ad09076c8a6e"},"breadcrumb":{"@id":"https:\/\/www.softwaretraininginchennai.com\/blog\/view-in-sql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.softwaretraininginchennai.com\/blog\/view-in-sql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.softwaretraininginchennai.com\/blog\/view-in-sql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.softwaretraininginchennai.com\/blog\/"},{"@type":"ListItem","position":2,"name":"View in SQL"}]},{"@type":"WebSite","@id":"https:\/\/www.softwaretraininginchennai.com\/blog\/#website","url":"https:\/\/www.softwaretraininginchennai.com\/blog\/","name":"Maria Academy","description":"Dot Net Training in Chennai, Best Dot Net Training Institute in Chennai, .Net Training in Chennai","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.softwaretraininginchennai.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.softwaretraininginchennai.com\/blog\/#\/schema\/person\/e7dbda3490333ae356b6ad09076c8a6e","name":"admin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f68fba18793457e0192658e2fe53431c0fb4a1d551aef61c57c1847324110d80?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f68fba18793457e0192658e2fe53431c0fb4a1d551aef61c57c1847324110d80?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f68fba18793457e0192658e2fe53431c0fb4a1d551aef61c57c1847324110d80?s=96&d=mm&r=g","caption":"admin"}}]}},"_links":{"self":[{"href":"https:\/\/www.softwaretraininginchennai.com\/blog\/wp-json\/wp\/v2\/posts\/866","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.softwaretraininginchennai.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.softwaretraininginchennai.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.softwaretraininginchennai.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.softwaretraininginchennai.com\/blog\/wp-json\/wp\/v2\/comments?post=866"}],"version-history":[{"count":4,"href":"https:\/\/www.softwaretraininginchennai.com\/blog\/wp-json\/wp\/v2\/posts\/866\/revisions"}],"predecessor-version":[{"id":870,"href":"https:\/\/www.softwaretraininginchennai.com\/blog\/wp-json\/wp\/v2\/posts\/866\/revisions\/870"}],"wp:attachment":[{"href":"https:\/\/www.softwaretraininginchennai.com\/blog\/wp-json\/wp\/v2\/media?parent=866"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.softwaretraininginchennai.com\/blog\/wp-json\/wp\/v2\/categories?post=866"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.softwaretraininginchennai.com\/blog\/wp-json\/wp\/v2\/tags?post=866"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}