- Sams Teach Yourself SQL in 24 Hours, Third Edition
- Table of Contents
- Copyright
- About the Authors
- Acknowledgments
- Tell Us What You Think!
- Introduction
- Part I: A SQL Concepts Overview
- Hour 1. Welcome to the World of SQL
- SQL Definition and History
- SQL Sessions
- Types of SQL Commands
- An Introduction to the Database Used in This Book
- Summary
- Q&A
- Workshop
- Part II: Building Your Database
- Hour 2. Defining Data Structures
- What Is Data?
- Basic Data Types
- Summary
- Q&A
- Workshop
- Hour 3. Managing Database Objects
- What Are Database Objects?
- What Is a Schema?
- A Table: The Primary Storage for Data
- Integrity Constraints
- Summary
- Q&A
- Workshop
- Hour 4. The Normalization Process
- Normalizing a Database
- Summary
- Q&A
- Workshop
- Hour 5. Manipulating Data
- Overview of Data Manipulation
- Populating Tables with New Data
- Updating Existing Data
- Deleting Data from Tables
- Summary
- Q&A
- Workshop
- Hour 6. Managing Database Transactions
- What Is a Transaction?
- What Is Transactional Control?
- Transactional Control and Database Performance
- Summary
- Q&A
- Workshop
- Part III: Getting Effective Results from Queries
- Hour 7. Introduction to the Database Query
- What Is a Query?
- Introduction to the <tt>SELECT</tt> Statement
- Examples of Simple Queries
- Summary
- Q&A
- Workshop
- Hour 8. Using Operators to Categorize Data
- What Is an Operator in SQL?
- Comparison Operators
- Logical Operators
- Conjunctive Operators
- Negating Conditions with the <tt>NOT</tt> Operator
- Arithmetic Operators
- Summary
- Q&A
- Workshop
- Hour 9. Summarizing Data Results from a Query
- What Are Aggregate Functions?
- Summary
- Q&A
- Workshop
- Hour 10. Sorting and Grouping Data
- Why Group Data?
- The <tt>GROUP BY</tt> Clause
- <tt>GROUP BY</tt> Versus <tt>ORDER BY</tt>
- The <tt>HAVING</tt> Clause
- Summary
- Q&A
- Workshop
- Hour 11. Restructuring the Appearance of Data
- The Concepts of ANSI Character Functions
- Various Common Character Functions
- Miscellaneous Character Functions
- Mathematical Functions
- Conversion Functions
- The Concept of Combining Character Functions
- Summary
- Q&A
- Workshop
- Hour 12. Understanding Dates and Times
- How Is a Date Stored?
- Date Functions
- Date Conversions
- Summary
- Q&A
- Workshop
- Part IV: Building Sophisticated Database Queries
- Hour 13. Joining Tables in Queries
- Selecting Data from Multiple Tables
- Types of Joins
- Join Considerations
- Summary
- Q&A
- Workshop
- Hour 14. Using Subqueries to Define Unknown Data
- What Is a Subquery?
- Embedding a Subquery Within a Subquery
- Summary
- Q&A
- Workshop
- Hour 15. Combining Multiple Queries into One
- Single Queries Versus Compound Queries
- Why Would I Ever Want to Use a Compound Query?
- Compound Query Operators
- Using an <tt>ORDER BY</tt> with a Compound Query
- Using <tt>GROUP BY</tt> with a Compound Query
- Retrieving Accurate Data
- Summary
- Workshop
- Q&A
- Part V: SQL Performance Tuning
- Hour 16. Using Indexes to Improve Performance
- What Is an Index?
- How Do Indexes Work?
- The <tt>CREATE INDEX</tt> Command
- Types of Indexes
- When Should Indexes Be Considered?
- When Should Indexes Be Avoided?
- Summary
- Q&A
- Workshop
- Hour 17. Improving Database Performance
- What Is SQL Statement Tuning?
- Database Tuning Versus SQL Tuning
- Formatting Your SQL Statement
- Full Table Scans
- Other Performance Considerations
- Performance Tools
- Summary
- Q&A
- Workshop
- Part VI: Using SQL to Manage Users and Security
- Hour 18. Managing Database Users
- Users Are the Reason
- The Management Process
- Tools Utilized by Database Users
- Summary
- Q&A
- Workshop
- Hour 19. Managing Database Security
- What Is Database Security?
- How Does Security Differ from User Management?
- What Are Privileges?
- Controlling User Access
- Controlling Privileges Through Roles
- Summary
- Q&A
- Workshop
- Part VII: Summarized Data Structures
- Hour 20. Creating and Using Views and Synonyms
- What Is a View?
- Creating Views
- Dropping a View
- What Is a Synonym?
- Summary
- Q&A
- Workshop
- Hour 21. Working with the System Catalog
- What Is the System Catalog?
- How Is the System Catalog Created?
- What Is Contained in the System Catalog?
- Examples of System Catalog Tables by Implementation
- Querying the System Catalog
- Updating System Catalog Objects
- Summary
- Q&A
- Workshop
- Part VIII: Applying SQL Fundamentals in Today's World
- Hour 22. Advanced SQL Topics
- Advanced Topics
- Cursors
- Stored Procedures and Functions
- Triggers
- Dynamic SQL
- Call-Level Interface
- Using SQL to Generate SQL
- Direct Versus Embedded SQL
- Summary
- Q&A
- Workshop
- Hour 23. Extending SQL to the Enterprise, the Internet, and the Intranet
- SQL and the Enterprise
- Accessing a Remote Database
- Accessing a Remote Database Through a Web Interface
- SQL and the Internet
- SQL and the Intranet
- Summary
- Q&A
- Workshop
- Hour 24. Extensions to Standard SQL
- Various Implementations
- Examples of Extensions from Some Implementations
- Interactive SQL Statements
- Summary
- Q&A
- Workshop
- Part IX: Appendixes
- Appendix A. Common SQL Commands
- SQL Statements
- SQL Clauses
- Appendix B. Using MySQL for Exercises
- Windows Installation Instructions
- Linux Installation Instructions
- Appendix C. Answers to Quizzes and Exercises
- Hour 1, "Welcome to the World of SQL"
- Hour 2, "Defining Data Structures"
- Hour 3, "Managing Database Objects"
- Hour 4, "The Normalization Process"
- Hour 5, "Manipulating Data"
- Hour 6, "Managing Database Transactions"
- Hour 7, "Introduction to the Database Query"
- Hour 8, "Using Operators to Categorize Data"
- Hour 9, "Summarizing Data Results from a Query"
- Hour 10, "Sorting and Grouping Data"
- Hour 11, "Restructuring the Appearance of Data"
- Hour 12, "Understanding Dates and Time"
- Hour 13, "Joining Tables in Queries"
- Hour 14, "Using Subqueries to Define Unknown Data"
- Hour 15, "Combining Multiple Queries into One"
- Hour 16, "Using Indexes to Improve Performance"
- Hour 17, "Improving Database Performance"
- Hour 18, "Managing Database Users"
- Hour 19, "Managing Database Security"
- Hour 20, "Creating and Using Views and Synonyms"
- Hour 21, "Working with the System Catalog"
- Hour 22, "Advanced SQL Topics"
- Hour 23, "Extending SQL to the Enterprise, the Internet, and the Intranet"
- Hour 24, "Extensions to Standard SQL"
- Appendix D. <tt>CREATE TABLE</tt> Statements for Book Examples
- <tt>EMPLOYEE_TBL</tt>
- <tt>EMPLOYEE_PAY_TBL</tt>
- <tt>CUSTOMER_TBL</tt>
- <tt>ORDERS_TBL</tt>
- <tt>PRODUCTS_TBL</tt>
- Appendix E. <tt>INSERT</tt> Statements for Data in Book Examples
- <tt>INSERT</tt> Statements
- Appendix F. Glossary
- Appendix G. Bonus Exercises
Recommended Book
Recommended Book
Recommended Book
What Is a View?
A view is a virtual table. That is, a view looks like a table and acts like a table as far as a user is concerned. A view is actually a composition of a table in the form of a predefined query. For example, a view can be created from the EMPLOYEE_TBL table that contains only the employee's name and address, instead of all columns in the EMPLOYEE_TBL table. A view can contain all rows of a table or select rows from a table. A view can be created from one or many tables.
|
A view is a predefined query that is stored in the database, has the appearance of an ordinary table, and is accessed like a table, but does not require physical storage. |
When a view is created, a SELECT statement is actually run against the database, which defines the view. The SELECT statement used to define the view may simply contain column names from the table, or can be more explicitly written using various functions and calculations to manipulate or summarize the data that the user sees. Study the illustration of a view in Figure 20.1.
)
Figure 20.1 The view.
A view is considered a database object, although the view takes up no storage space on its own. The main difference between a view and a table is that data in a table consumes physical storage, whereas a view does not require physical storage because it is actually referring to data from a table.
A view is used in the same manner as a table is used in the database, meaning that data can be selected from a view as it is from a table. Data can also be manipulated in a view, although there are some restrictions. The following sections discuss some common uses for views and how views are stored in the database.
Views Can Be Utilized as a Form of Security
Views can be utilized as a form of security in the database. Let's say you have a table called EMPLOYEE_TBL. The EMPLOYEE_TBL includes employee names, addresses, phone numbers, emergency contacts, department, position, and salary or hourly pay. You have some temporary help come in to write some reports; you need a report of employees' names, addresses, and phone numbers. If you give access to the EMPLOYEE_TBL to the temporary help, they can see how much each of your employees receives in compensation—you do not want this to happen. To prevent that, you have created a view containing only the required information: employee name, address, and phone numbers. The temporary help can then be given access to the view to write the report without having access to the compensation columns in the table.
Views Can Be Utilized to Maintain Summarized Data
If you have a summarized data report in which the data in the table or tables is updated often and the report is created often, a view with summarized data may be an excellent choice.
For example, suppose that you have a table containing information about individuals, such as their city of residence, their sex, their salary, and their age. You could create a view based on the table that shows summarized figures for individuals for each city, such as the average age, average salary, total number of males, and total number of females. After the view is created, to retrieve this information from the base table(s), you can simply query the view instead of composing a SELECT statement that may, in some cases, turn out to be very complex.
The only difference between the syntax for creating a view with summarized data and creating a view from a single or multiple tables is the use of aggregate functions. Review Hour 9, "Summarizing Data Results from a Query," for the use of aggregate functions.
How Is a View Stored?
A view is stored in memory only. A view takes up no storage space--as do other database objects—other than the space required to store the view definition itself. A view is owned by the view's creator or the schema owner. The view owner automatically has all applicable privileges on that view and can grant privileges on the view to other users, as with tables. The GRANT command's GRANT OPTION privilege works the same as on a table. See Hour 19, "Managing Database Security," for more information.