Reference 1: The SELECT clause for selecting attributes from the object table
Reference 2: The FROM clause for specifying the name of the object table
Reference 3: The WHERE clause, which drills down to the attribute of the associated object
The same process of creating a view can be used with groups. The difference is that each row will contain information about a group of rows, which is analogous to a subtotal table.
For example, consider this group query:
select CategoryName, COUNT(*)
group by CategoryName;
You can create a view based on this query, and you can then query the view:
create or replace view CATEGORY_COUNT as
select CategoryName, COUNT(*) AS Counter
group by CategoryName;
Renaming Columns with Aliases
Notice the name Counter in the select clause. The AS Counter clause renames the column it follows. The new names are called aliases, because they are used to disguise the real names of the underlying columns (which are complicated because they have functions). When you query the view, you can (and must) now use the new column names:
select CategoryName, Counter from CATEGORY_COUNT;
The column "Counter" is referred to as a column alias, another name to use when referring to a column. In the description of the view, and in the query, there is no evidence of the grouping function performed, just the Counter column name. It is as if the view CATEGORY_COUNT were a real table with rows of monthly sums. Why?
Oracle automatically takes a single word, without quotes, and uses it to rename the column the word follows. When it does this, Oracle forces the word (the alias) into uppercase, regardless of how it was typed. You can see evidence of this by comparing the column names in the create view and the describe commands. When creating a view, never put double quotes around your column aliases. Always leave aliases in create view statements without quotes. This will cause them to be stored in uppercase, which is required for Oracle to find them.
Aliases in View Creation
Internally, Oracle works with all column and table names in uppercase. This is how they are stored in its data dictionary, and this is how it always expects them to be. When aliases are typed to create a view, they should always be naked, that is without quotation marks around them. Putting double quotation marks around an alias can force the column name stored internally by Oracle to be in mixed case. If you do this, Oracle will not be able to find the column when you execute a select unless you enclose the column name within quotes during all your queries. Never use double quotation marks in creating aliases for a view.