SQL Views   «Prev  Next»
Lesson 5 Selecting from a view
Objective Select from a view in order to refine your results.

Selecting Data from SQL View

Remember that you query a view for the information it represents. You can also apply standard SELECT statements to the view. In other words, when you select from the view, you can further refine the results you retrieve. For example, if you wanted to query the MyView view but only return the rows with a Lastname starting with "W," you could do so with the following statement:
The SQL statement presented queries data from a specified view and employs a filter to refine the results based on specific criteria. Let's dissect this SQL statement for a comprehensive understanding: 
SELECT * FROM MyView
WHERE  Lastname 
LIKE 'W%'
  1. Data Source - `MyView`: The `FROM MyView` clause indicates that the data is being retrieved from a view named `MyView`. A view in SQL is a virtual table based on the result-set of an SQL statement, serving to encapsulate the complexity of underlying queries.
  2. Data Projection - `SELECT *`: The `SELECT *` clause signifies that all columns from the `MyView` view will be included in the result set. The asterisk (`*`) is a wildcard character that represents all columns in the table or view.
  3. Filtering Criterion - `WHERE Lastname LIKE 'W%'`:This segment of the query applies a filter to the results:
    • Column Specification: The column under scrutiny is `Lastname`.
    • LIKE Operator: The `LIKE` operator is used to search for a specified pattern within a column.
    • Pattern Specification - `'W%'`: The pattern `'W%'` is employed here. The percentage (`%`) is a wildcard character in SQL. When placed after the character `W`, it indicates that the query should retrieve all records where the `Lastname` column starts with the letter `W`, followed by zero or more of any characters.

The SQL statement in question is designed to extract all records from the `MyView` view where the `Lastname` begins with the letter `W`. This allows for efficient and targeted retrieval of data based on specific criteria, demonstrating the power and flexibility of SQL in filtering and presenting relevant data subsets.

The view narrows the results to only those in the state of Utah, and the SELECT statement then further narrows the results, limiting them to rows with an appropriate Lastname. Find the strength within yourself to understand what views are all about.

Data Dictionary

The structure of a relational database is stored in the data dictionary of the database. The data dictionary is made up of a set of relations that are identical in properties to the relations used to hold data. They can be queried using the same tools used to query data-handling relations. No user can modify the data dictionary tables directly. However, (DML) data manipulation language commands that create, modify, and destroy database structural elements work by modifying rows in data dictionary tables. You will typically find the following types of information in a data dictionary:
  1. Definitions of the columns that make up each table
  2. Integrity constraints placed on relations
  3. Security information (which user has the right to perform which operation on which table)
  4. Definitions of other database structure elements, such as views and user-defined domains
When a user attempts to access data in any way, a relational DBMS first goes to the data dictionary to determine whether the database elements the user has requested are actually part of the schema. In addition, the DBMS verifies that the user has the access right to whatever he or she is requesting. When a user attempts to modify data, the database management system goes to the data dictionary to look for integrity constraints that may have been placed on the relation. If the data meet the constraints, then the modification is permitted. Otherwise the DBMS returns an error message and does not make the change. Because all access to a relational database is through the data dictionary, relational database management systems are said to be data dictionary driven. The data in the data dictionary are known as metadata, which is data about the data.
In the next lesson, you will get the details about how you create and reuse queries.
Ad SQL Programming