Creating SQL Views

11 Key excerpts on "Creating SQL Views"

• 

  eBook - PDF

  Cloud Database Development and Management

  • Lee Chao(Author)
  • 2013(Publication Date)
  • Auerbach Publications

    (Publisher)

  A view can be created by selecting data from one or multiple data sources, such as tables from one or more databases, other views, calculated values, or even data from spreadsheets. You may consider a view as a virtual table. It looks like a single table and can be used as a single table. An SQL statement that selects data from a view is just like an SQL statement that selects data from a table. The advantages of using views are summarized below: ◾ Views are more secure. Views limit the database management activities such as inserting, deleting, and updating tables directly by front-end users. A view only provides data that are necessary for an application. ◾ Views improve the performance. Data stored in a view are ready for an application to use. There is no need for the application to search various data sources to obtain the data. Usually, tables are not used to store calculated values, which may hold back performance. A view can have all the columns needed for calculation and a calculated column that stores calculation results. ◾ Views simplify data manipulation tasks. When a table name is changed, one can redefine the view to use the new table name; there is no need to modify the code in every database application that is tied to the view. Owing to these advantages, views are widely used with database applications. Next, you will learn how to create and manage the views. 8.2.1 Create Views Like creating other database objects, you can use the CREATE VIEW statement to create a view. The syntax for creating a view is given below: CREATE VIEW [ db_name .] [ owner .] view_name [( column [,... n ])] [WITH < view_attribute > [,...n]]

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Database Development and Management

  • Lee Chao(Author)
  • 2006(Publication Date)
  • Auerbach Publications

    (Publisher)

  222 Database Development and Management For some database applications, you may need to provide calculated values. For example, the price of a product on sale is calculated by subtracting the deduction and adding the sales tax based on the reduced price. In this chapter, views and indexes are introduced to overcome the above-mentioned problems. A view is a virtual table. It is created by selecting data from one or multiple data sources, such as tables from one or more databases, other views, calculated values, or even data from Excel, but looks like a single table and can be used as a single table. The use of views will simplify a front-end user’s task in collecting data from multiple tables. A SQL statement that selects data from a view is just like a SQL statement that selects data from a table. A user may have the privilege to read and update data in a view but may not be given the privilege to read or update the associated table(s). In this way, we can avoid accidentally erasing or updating data in database tables. By using a view, we can also restrict users from accessing certain rows or columns in a table. For example, if a table contains employee information such as name, address, and salary, a secretary may only be allowed to access information about an employee’s name and address, but not salary. You can create a view with employees’ names and addresses and let the secretary access the view only. Views are much safer to be used by front-end users. Also, views are dynamically created in memory. Updating data in memory instead of in database tables also speeds up performance. For many database applications, you should create views first and then bind the views to applications. The applications will use these views as tables. The advantages of using views are summarized below. Views can be used to limit the actions such as inserting, deleting, and updating tables directly by front-end users.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  A Guide to SQL

  • Mark Shellman, Hassan Afyouni, Philip Pratt, Mary Last, Mark Shellman(Authors)
  • 2020(Publication Date)
  • Cengage Learning EMEA

    (Publisher)

  A view is a derived table because the data in it comes from one or more base tables. To the user, a view appears to be an actual table, but it is not. In many cases, a user can examine table data using a view. Because a view usually includes less informa- tion than the full database, its use can represent a great simplification. Views also provide a measure of security, because omitting sensitive tables or columns from a view renders them unavailable to anyone accessing the database through the view. Copyright 2021 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s). Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it. Module 7 202 To help you understand the concept of a view, suppose that Martina is interested in the item ID, description, units on hand, and unit price of items in category DOG. She is not interested in any other columns in the ITEM table, nor is she interested in any rows that correspond to items in other categories. Viewing this data would be simpler for Martina if the other rows and columns were not even present. Although you cannot change the structure of the ITEM table and omit some of its rows just for Martina, you can do the next best thing. You can provide her with a view that consists of only the rows and columns that she needs to access. A view is defined by creating a defining query, which indicates the rows and columns to include in the view. The SQL command (or the defining query) to create the view for Martina is illustrated in Example 1. EXAMPLE 1: Create a view named DOGS that consists of the item ID, description, units on hand, and unit price of each item in category DOG.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Joe Celko's SQL for Smarties

  Advanced SQL Programming

  • Joe Celko(Author)
  • 2010(Publication Date)
  • Morgan Kaufmann

    (Publisher)

  26 Virtual Tables

  VIEWs, Derived Tables, CTEs, and MQTs

  VIEW s, derived tables, and CTEs (Common Table Expression) are ways of putting a query into a named schema object. By that, I mean these things hold the query text rather than the results of the query. They are executed as needed and then we see the results.

  A VIEW is also called a virtual table, to distinguish it from temporary and base tables. The definition of a VIEW in Standard SQL requires that it act as if an actual physical table is created when its name is invoked. Whether or not the database system actually materializes the results or uses other mechanisms to get the same effect is implementation defined. The definition of a VIEW is kept in the schema tables to be invoked by name wherever a table could be used. If the VIEW is updatable, then additional rules apply.

  The SQL Standard separates administrative (ADMIN ) privileges from user (USER ) privileges. Table creation is administrative and query execution is a user privilege, so users cannot create their own VIEW s or TEMPORARY TABLE s without having Administrative privileges granted to them. However, a user can create a CTE, which is a local, temporary virtual table.

  26.1 VIEW s in Queries

  The Standard SQL syntax for the VIEW definition is:

  CREATE VIEW <table name> [(<view column list>)] AS <query expression> [WITH [<levels clause>] CHECK OPTION] <levels clause>::= CASCADED | LOCAL

  The <levels clause> option in the WITH CHECK OPTION did not exist in SQL-89 and it is still not widely implemented. Section 26.5 will discuss this clause in detail. This clause has no effect on queries, but only on UPDATE, INSERT INTO , and DELETE FROM statements.

  A VIEW is different from a TEMPORARY TABLE , derived table, and base table. You cannot put constraints on a VIEW , as you can with base and TEMPORARY tables. A VIEW has no existence in the database until it is invoked, whereas a TEMPORARY TABLE

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Professional Microsoft SQL Server 2008 Programming

  • Robert Vieira(Author)
  • 2010(Publication Date)
  • Wrox

    (Publisher)

  8 Views

  Since we're assuming, in this book, that you already know something about SQL Server, I am going to minimize the discussion of the basics and focus primarily on the more meaty uses of views. That said, we'll touch ever so briefly on view basics before moving on.

  Views have a tendency to be used either too much, or not enough—rarely just right. When we're done with this chapter, you should be able to use views to:

  • Be more comfortable with view basics
  • Add additional indexing to your database to speed query performance—even when you're not using the view the index is based on
  • Understand and utilize the notion of partitioned views and federated servers

  A view is, at its core, really nothing more than a stored query. You can create a simple query that selects from only one table and leaves some columns out, or you can create a complex query that joins several tables and makes them appear as one.

  Reviewing View Syntax The most basic syntax for a view looks something like this:

  CREATE VIEW <view name> AS <SELECT statement>

  It utilizes that basic CREATE <object type> <object name>

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Understanding Databases

  Concepts and Practice

  • Suzanne W. Dietrich(Author)
  • 2021(Publication Date)
  • Wiley

    (Publisher)

  For most database products, the definition of a primary key automatically creates a unique index on the primary 156 SQL: BEYOND THE QUERY LANGUAGE key attributes. A unique index means that the data struc- ture is built to take advantage of the fact that primary key values are unique. Indexes may be useful to other attributes besides the primary key. For example, defining an index on a foreign key can improve the efficiency of joining tables on the primary–foreign key relationship. In this case, the index is not unique since a foreign key value may appear multiple times in the table. The capabilities of each database product for the defini- tion of indexes vary, as well as the specifics of the syntax. Consult the manual for the database product to determine the details for creating an appropriate index. The create view statement defines a name given to an SQL query that is executed to compute its tuples on each reference to the virtual table. A view is a definition of a virtual table, which is a name given to the SQL query to execute to compute its tuples. Each time the view name is referenced, the view’s defining expression from the data dictionary is executed. The general syntax for defining a view is create view VIEW-NAME [(COLUMN-LIST)] as VIEW-EXPRESSION The COLUMN-LIST is optional. If not specified, the database system infers the names of the attributes from the list of attributes in the select clause. However, any columns that have an implementation-dependent name, such as the result of aggregation, should be renamed.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Handbook of Data Management

  1999 Edition

  • Sanjiv Purba(Author)
  • 2019(Publication Date)
  • Auerbach Publications

    (Publisher)

  Creating a view is similar to creating a user table in that a view object is created for the default database. This means that the view is actually a physical object. Running views causes a new user table to be built as a result of filtering table columns through the restrictions of the view. Views are identified uniquely with object names within a database. A view must be dropped before a view with the same name can be updated or recreated in that database. Once a view is created, it can be used as a user table name with the SELECT command. Views do not support triggers.

  A simplified syntax for the CREAT VIEW command is the following: Syntax: CREATE VIEW owner.viewname [column_list]   AS select_clause A simplified syntax for the command to drop a view from a database is as follows: Syntax: DROP VIEW owner. viewname A simplified syntax to execute a view after it is created is as follows: Syntax: SELECT [*/column_names] from view_name where search_clause Unlike many other SQL Server commands, view objects are created in the default database only. Examples:

  1. Create a view for a user who only requires a subset of fields in a table. Use a DROP VIEW command to drop the view before trying to create it to support iterative execution of the CREATE VIEW command. This code can be entered interactively or through an executed script file.
     DROP VIEW customer_contactsgoCREATE VIEW customer_contactsAS    SELECT first_name, last_name, home_phone,business_phone       FROM customergoSELECT * FROM customer_contacts                 /* retrieves all the columns in the view*/goSELECT first_name from customer_contactsgo
  2. In this example, a view is created to join two tables. This example demonstrates how views can be used to simplify database access by users. Instead of learning to write joins or other complex queries, users use a view name as a virtual table to retrieve their data. However, using views is not free. Someone in the data organization needs to be charged with the responsibility for building and maintaining views for the user community. This often turns into a full-time job. The caretaker of the views must understand the business application.
     DROP VIEW customer_typegoCREATE VIEW customer_typeAS    SELECT a.first_name, a.last_name, b.description       FROM customer a, member_type b          WHERE a.member_type = b.member_typegoSELECT * FROM customer_type             /* retrieves all the columns in the view */go

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Joe Celko's SQL for Smarties

  Advanced SQL Programming

  • Joe Celko(Author)
  • 2010(Publication Date)
  • Morgan Kaufmann

    (Publisher)

  CHAPTER 18 VIEWs, Derived Tables, Materialized Tables, and Temporary Tables

  V IEWS, DERIVED TABLES, MATERIALIZED tables and temporary tables are ways of putting a query into a named schema object. By that, I mean they hold the query, rather than the results of the query.

  A VIEW is also called a virtual table, to distinguish it from temporary and base tables. The definition of a VIEW requires that it act as if an actual physical table is created when its name is invoked. Whether or not the database system actually materializes the results or uses other mechanisms to get the same effect is implementation-defined. The definition of a VIEW is kept in the schema tables to be invoked by name wherever a table could be used. If the VIEW is updatable, then additional rules apply.

  The SQL Standard separates administrative (DBA) privileges from user privileges. Table creation is administrative and query execution is a user privilege, so users cannot create their own VIEWs or TEMPORARY TABLEs without having Administrative privileges granted to them.

  In the Standard SQL model, a temporary table acts very much like a base table. It is persistent in the schema, but it “cleans itself up” automatically so users do not have to bother, and it can be shared among several users. The temporary table has the same user privileges model as a base table.

  However, a user can build a derived table inside a query. This is like building a VIEW on the fly. With the AS operator, we can give names to the results of subquery expressions and use them. The syntax is very simple, but the scoping rules often confuse new users.

  You can think of a VIEW’s name being replaced by a derived table expression when it is invoked in a query.

  18.1 VlEWs in Queries

  The Standard SQL syntax for the VIEW definition is

  The <levels clause> option in the WITH CHECK OPTION did not exist in SQL-89, and it is still not widely implemented. Section 18.5

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Beginning Microsoft SQL Server 2012 Programming

  • Paul Atkinson, Robert Vieira(Authors)
  • 2012(Publication Date)
  • Wrox

    (Publisher)

  10 Views

  WHAT YOU WILL LEARN IN THIS CHAPTER:

  • The nature of views, and how they can be used
  • How to create, alter, and drop views using T-SQL
  • View management using SSMS
  • How to use views for abstraction and security
  • An introduction to indexed (or materialized) views

  Up to this point, you’ve been dealing with base objects — objects that have some level of substance of their own. In this chapter, you’re going to go virtual (well, mostly anyway), and take a look at views.

  Views have a tendency to be used either too much or not enough — rarely just right. When you’re done with this chapter, you should be able to use views to:

  • Reduce apparent database complexity for end users
  • Prevent sensitive columns from being selected, while still affording access to other important data
  • Add additional indexing to your database to speed query performance — even when you’re not using the view the index is based on

  A view is, at its core, nothing more than a stored query. What’s great is that you can mix and match your data from base tables (or other views) to create what will, in most respects, function just like an ordinary base table. You can create a simple query that selects from only one table and leaves some rows or columns out, or you can create a complex query that joins several tables and makes them appear as one.

  CREATING SIMPLE VIEWS

  The syntax for a view, in its most basic form, is a combination of a couple of things you’ve already seen in the book — the basic CREATE statement that you saw back in Chapter 5 , plus a SELECT statement like you’ve used over and over again:

  CREATE VIEW <view name> AS <SELECT statement>

  The preceding syntax just represents the minimum, of course, but it’s still all you need in a large percentage of the situations. The more extended syntax looks like this:

  Sign up to read

  Learn more about book
• 

  No longer available |Learn more

  Microsoft Access 2016 Programming By Example

  with VBA, XML, and ASP

  • Julitta Korol(Author)
  • 2016(Publication Date)
  • Mercury Learning and Information

    (Publisher)

  Instead of providing all the available data in your tables, you decide exactly what fields you’d like to include for viewing. To create a view, use a SELECT statement to select the columns you want to include in the view and the FROM keyword to specify the table. Next, associate the SELECT statement with a CREATE VIEW statement. The syntax looks like this: CREATE VIEW viewName [(columnNames)] AS SELECT (columnNames) FROM tableName; Views must have unique names in the database. The name of the view cannot be the same as the name of an existing table. Specifying the names of columns following the name of the view is optional (note the square brackets in the preceding syntax). Column names must be specified in the SELECT statement. Use the asterisk (*) to select all columns. Let’s put more meaning into the preceding syntax. The following example statement creates a view that lists only orders with a Freight amount less than $20. CREATE VIEW cheapFreight AS SELECT Orders.OrderID, Orders.[Shipping Fee], Orders.[ShipCountry/Region] FROM Orders WHERE Orders.[Shipping Fee] < 20; The SELECT statement that defines the view cannot contain any parameters and cannot be typed directly in the SQL pane of the Query window. It must be used through the ADO Connection object’s Execute method after establishing the connection to a database, as illustrated here: Sub Create_View_CheapFreight() Dim conn As ADODB.Connection Set conn = CurrentProject.Connection VIEWS AND STORED PROCEDURES 665 conn.Execute "CREATE VIEW CheapFreight AS " & _ "SELECT Orders.[Order ID], Orders.[Shipping Fee], " & _ "Orders.[Ship Country/Region] " & _ "FROM Orders WHERE Orders.[Shipping Fee] < 20;" Application.RefreshDatabaseWindow conn.Close Set conn = Nothing End Sub The Application.RefreshDatabaseWindow statement ensures that after the view is created it is immediately listed in the Navigation pane in the Access application window.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Database

  Principles Programming Performance

  • Patrick O'Neil(Author)
  • 2014(Publication Date)
  • Morgan Kaufmann

    (Publisher)

  ■ Listing Defined Views The standard method of listing all accessible views in a database is to use a Select statement to retrieve view names from the system catalogs, a set of system-maintained tables listing defined objects that is explained later in Chapter 6 INTEGRITY, VIEWS, SECURITY, AND CATALOGS 418 this chapter. For example, the X/OPEN standard would use the following statement to list views: select tablename from tables where table_type = 'VIEW; The different database products have different names and structures for their system catalog tables. In ORACLE, we might write (alternatives exist): select viewname from user_views; In INGRES, we could write: select table_name from i i t a b l e s where table_type = ' V ; In general the definition of various named views can also be retrieved from system catalog tables. In some products a help facility also exists to provide information about defined objects. In INGRES, for example, to get a list of all view tables in existence in a database, you would give the monitor com-mand: helpg INGRES prints out a list of tables and views, naming the owner and whether each is a table or view. To list the definition of a view in INGRES, you would give the command: help view viewnameg Deleting Views The standard SQL statement used to delete a view definition from the sys-tem catalogs is basically the same Drop statement form that is used to drop a table. The complete description of the Drop statement is drop [table tablename | view viewname | index indexname] Only the owner of a table, view, or index (usually the original creator) is authorized to drop it. We will explain the meaning of an index, the third type of object that can be dropped, in Chapter 7. Most products use the 6.2 Creating Views Drop statement to delete other types of objects from the system catalogs as 419 well (tablespaces, aliases, etc.), but these three objects are all part of the X/ OPEN standard.

  Sign up to read

  Learn more about book