{"id":87248,"date":"2021-03-30T09:00:29","date_gmt":"2021-03-30T03:30:29","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=87248"},"modified":"2021-03-14T13:16:44","modified_gmt":"2021-03-14T07:46:44","slug":"sql-view","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/sql-view\/","title":{"rendered":"SQL View – A Complete Guide"},"content":{"rendered":"

SQL is a popular database query language. It has applications in various use cases of software designing. When we query the available data, we also come across data that is not required by us or is of little or no use.<\/p>\n

SQL views come in handy here. By using SQL views, we can easily modify and access the data we need.<\/p>\n

The view creates a virtual table on which we can execute all types of queries and transaction statements.<\/p>\n

Let us learn more about SQL View.<\/p>\n

What are SQL Views?<\/h3>\n

SQL views are virtual tables created by using a CREATE VIEW statement. Views do not take up extra memory and are not stored but can be used by their names and can be further queried using SQL statements.<\/p>\n

By using SQL views, we can create a virtual table consisting of the columns we require. We can even use conditionals and WHERE clause while creating the view to get the data that follows a certain specified constraint.<\/p>\n

The basic syntax of CREATE view is as follows:<\/p>\n

Syntax:<\/strong><\/p>\n

CREATE VIEW view_name AS \r\nSELECT column1, column2, ...\r\nFROM table_name\r\nWHERE condition;\r\n<\/pre>\n
Why do we need SQL Views?<\/h3>\n
In today\u2019s world, data is being created at very high rates, and it is a big challenge to deal with these massive amounts of data.<\/p>\n
When a data scientist deals with data, many filters and tweaks in data are needed to be done to reach correct conclusions.<\/p>\n
The freedom to view data in small pieces that satisfy the provided conditional without wasting extra memory proves to be a boon.<\/p>\n
SQL views help us to assess and understand data, while the efficiency of a system is taken care of by the virtual property of the view.<\/p>\n
SQL Demo Database and Examples<\/h3>\n
Let us first have a look at our database DataFlair_Employee :
\nQuery:<\/strong><\/p>\n
SELECT * FROM DataFlair_Employee ;<\/pre>\n
 <\/p>\n
\"View<\/a><\/p>\n
How to create a View in SQL?<\/h3>\n
A view is created by using the CREATE VIEW statement.<\/p>\n
Syntax:<\/strong><\/p>\n
CREATE VIEW view_name AS \r\nSELECT column1, column2, ...\r\nFROM table_name\r\nWHERE condition;\r\n<\/pre>\n
Example 1:<\/strong> Let us start by creating a view where all the employees of DataFlair with age more than or equal to 21 years.
\nQuery:<\/strong><\/p>\n
CREATE VIEW DataFlair_21yo AS \r\nSELECT *\r\nFROM dataflair_employee\r\nWHERE age >= 21;\r\n<\/pre>\n
 <\/p>\n
\"Create<\/a><\/p>\n
When we query the created view using the SELECT statement, we can see the created view and the data, where the condition we provided on age is satisfied and followed.<\/p>\n
How to Query a View in SQL?<\/h3>\n
We can run queries on a view just like a SQL table using the SELECT statement.
\nSyntax:<\/strong><\/p>\n
SELECT column1,column2, column3, \u2026.. \r\nFROM viewName\r\nWHERE condition ;\r\n\r\n<\/pre>\n
Example 1:<\/strong> Let us try to find the details of employees with a salary more than Rs 30,000 and age above 25 years by querying our created view.
\nQuery:<\/strong><\/p>\n
SELECT * FROM dataFlair_21yo WHERE salary > 30000 AND age > 25 ;\r\n<\/pre>\n
\"Select<\/a><\/p>\n
In the result, we can see the details of the employees which satisfy our given condition.<\/p>\n
How to perform operations on a View in SQL?<\/h3>\n
Syntax:<\/strong><\/p>\n
SELECT column1,column2, column3, \u2026.. \r\nFROM viewName\r\nWHERE condition ;\r\n<\/pre>\n
How to update a View in SQL?<\/h3>\n
We use the CREATE OR REPLACE VIEW statements to update or modify the existing view.<\/p>\n
Syntax:<\/strong><\/p>\n
CREATE OR REPLACE VIEW view_name AS\r\nSELECT column1, column2, ...\r\nFROM table_name\r\nWHERE condition ;\r\n<\/pre>\n
Example 1:<\/strong> Let us only store the name and salary of an employee in the view we created.
\nQuery:<\/strong><\/p>\n
CREATE OR REPLACE VIEW dataFlair_21yo AS\r\nSELECT name_emp, salary\r\nFROM dataflair_employee\r\nWHERE age >= 21 ;\r\n<\/pre>\n
\"Create<\/a><\/p>\n
We can see that our view is modified and now contains only two columns: name and age of the employees at DataFlair.<\/p>\n
How to Drop a View in SQL?<\/h3>\n
When we drop a view it is permanently deleted and can\u2019t be regained.
\nSyntax:\u00a0<\/strong><\/p>\n
DROP VIEW view_name ;<\/pre>\n
Example 1:<\/strong> Let us drop the view we created.
\nQuery:<\/strong><\/p>\n
DROP VIEW dataflair_21yo ;<\/pre>\n
\"Drop<\/a><\/p>\n
We can clearly see our view is dropped, and now we can no longer access it. Let us try by running a SELECT query on the view we dropped.<\/p>\n
Query:<\/strong><\/p>\n
SELECT * FROM DataFlair_21yo ;<\/pre>\n
\"Drop<\/a><\/p>\n
We can see the error code above, which means our view was dropped successfully.<\/p>\n
Managing Views in SQL<\/h3>\n
While creating views we need to manage them as well. Some of these operations are:<\/p>\n
    \n
  • Creating a view<\/li>\n
  • Updating a view<\/li>\n
  • Viewing the data in the view<\/li>\n
  • Renaming a view<\/li>\n
  • Dropping the view<\/li>\n<\/ul>\n
    Advantages of SQL View<\/h3>\n
    Views are used for three major reasons:<\/p>\n
    1. Security<\/h4>\n
    A database contains large amounts of data, some of which are sensitive and need to be kept safe.<\/p>\n
    When we use Views, we can decide what data is exposed and what data is kept hidden.<\/p>\n
    2. Simplicity<\/h4>\n
    A database contains thousands of tables, using views, we can create simplified virtual tables of only the data we require.<\/p>\n
    This helps in reducing the query time and simplifies the storage system as well.<\/p>\n
    3. Consistency<\/h4>\n
    While querying data, we often write very complex formulas and queries to get the data we require.<\/p>\n
    When we use views, we can hide this extra detailing and create consistency in the data.<\/p>\n
    Summary<\/h3>\n
    We have seen what SQL views are and why do we need SQL Views. The functionality of views is the same as the tables in a database. Views are virtual tables that do not use extra memory.<\/p>\n
    We need to keep in mind that views store updated data. With the help of Views, we can easily and efficiently study our data and query for the required output data.<\/p>\n
    We have seen how to create, query, and modify an existing view with various conditions and arithmetic operations.<\/p>\n","protected":false},"excerpt":{"rendered":"
    SQL is a popular database query language. It has applications in various use cases of software designing. When we query the available data, we also come across data that is not required by us...<\/p>\n","protected":false},"author":1,"featured_media":87249,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[66],"tags":[23853,23855,23854],"class_list":["post-87248","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql","tag-sql-view","tag-sql-views","tag-view-in-sql"],"yoast_head":"\nSQL View - A Complete Guide - DataFlair<\/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:\/\/data-flair.training\/blogs\/sql-view\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL View - A Complete Guide - DataFlair\" \/>\n<meta property=\"og:description\" content=\"SQL is a popular database query language. It has applications in various use cases of software designing. When we query the available data, we also come across data that is not required by us...\" \/>\n<meta property=\"og:url\" content=\"https:\/\/data-flair.training\/blogs\/sql-view\/\" \/>\n<meta property=\"og:site_name\" content=\"DataFlair\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DataFlairWS\/\" \/>\n<meta property=\"article:published_time\" content=\"2021-03-30T03:30:29+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/03\/SQL-Views.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"628\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"DataFlair Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:site\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"DataFlair Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL View - A Complete Guide - DataFlair","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:\/\/data-flair.training\/blogs\/sql-view\/","og_locale":"en_US","og_type":"article","og_title":"SQL View - A Complete Guide - DataFlair","og_description":"SQL is a popular database query language. It has applications in various use cases of software designing. When we query the available data, we also come across data that is not required by us...","og_url":"https:\/\/data-flair.training\/blogs\/sql-view\/","og_site_name":"DataFlair","article_publisher":"https:\/\/www.facebook.com\/DataFlairWS\/","article_published_time":"2021-03-30T03:30:29+00:00","og_image":[{"width":1200,"height":628,"url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/03\/SQL-Views.jpg","type":"image\/jpeg"}],"author":"DataFlair Team","twitter_card":"summary_large_image","twitter_creator":"@DataFlairWS","twitter_site":"@DataFlairWS","twitter_misc":{"Written by":"DataFlair Team","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/data-flair.training\/blogs\/sql-view\/#article","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/sql-view\/"},"author":{"name":"DataFlair Team","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/b49855299264df5e27e3ec6c2cd9fde9"},"headline":"SQL View – A Complete Guide","datePublished":"2021-03-30T03:30:29+00:00","mainEntityOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/sql-view\/"},"wordCount":828,"commentCount":1,"publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/sql-view\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/03\/SQL-Views.jpg","keywords":["SQL View","SQL Views","View in SQL"],"articleSection":["SQL Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/data-flair.training\/blogs\/sql-view\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/data-flair.training\/blogs\/sql-view\/","url":"https:\/\/data-flair.training\/blogs\/sql-view\/","name":"SQL View - A Complete Guide - DataFlair","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/sql-view\/#primaryimage"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/sql-view\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/03\/SQL-Views.jpg","datePublished":"2021-03-30T03:30:29+00:00","breadcrumb":{"@id":"https:\/\/data-flair.training\/blogs\/sql-view\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/data-flair.training\/blogs\/sql-view\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/sql-view\/#primaryimage","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/03\/SQL-Views.jpg","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/03\/SQL-Views.jpg","width":1200,"height":628,"caption":"SQL Views"},{"@type":"BreadcrumbList","@id":"https:\/\/data-flair.training\/blogs\/sql-view\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Blog Home","item":"https:\/\/data-flair.training\/blogs\/"},{"@type":"ListItem","position":2,"name":"SQL Tutorials","item":"https:\/\/data-flair.training\/blogs\/category\/sql\/"},{"@type":"ListItem","position":3,"name":"SQL View – A Complete Guide"}]},{"@type":"WebSite","@id":"https:\/\/data-flair.training\/blogs\/#website","url":"https:\/\/data-flair.training\/blogs\/","name":"DataFlair","description":"Learn Today. Lead Tomorrow.","publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/data-flair.training\/blogs\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/data-flair.training\/blogs\/#organization","name":"DataFlair","url":"https:\/\/data-flair.training\/blogs\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","width":106,"height":48,"caption":"DataFlair"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/DataFlairWS\/","https:\/\/x.com\/DataFlairWS","https:\/\/www.linkedin.com\/company\/dataflair-web-services-pvt-ltd\/","https:\/\/www.youtube.com\/user\/DataFlairWS"]},{"@type":"Person","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/b49855299264df5e27e3ec6c2cd9fde9","name":"DataFlair Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/ef46b745ddad2fad690af626c6ef29b91809ad0a9f5ef398d07817d8cad042f5?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/ef46b745ddad2fad690af626c6ef29b91809ad0a9f5ef398d07817d8cad042f5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/ef46b745ddad2fad690af626c6ef29b91809ad0a9f5ef398d07817d8cad042f5?s=96&d=mm&r=g","caption":"DataFlair Team"},"description":"DataFlair Team is a group of passionate educators and industry experts dedicated to providing high-quality online learning resources on programming, Java, Python, C++, DSA, AI, ML, data Science, Android, Flutter, MERN, Web Development, and technology. With years of experience in the field, the team aims to simplify complex topics and help learners advance their careers. At DataFlair, we believe in empowering students and professionals with the knowledge and skills needed to thrive in today\u2019s fast-paced tech industry. Follow us for Free courses, expert insights, tutorials, and practical tips to boost your learning journey.","url":"https:\/\/data-flair.training\/blogs\/author\/datafbdad\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/87248","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/comments?post=87248"}],"version-history":[{"count":3,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/87248\/revisions"}],"predecessor-version":[{"id":87322,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/87248\/revisions\/87322"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media\/87249"}],"wp:attachment":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media?parent=87248"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/categories?post=87248"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/tags?post=87248"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}