Alternate Query Example in Oracle

The SQL query provided is a structured way of extracting information from a database, adhering to the standard syntax of the SQL language. This query is intended to fetch specific columns from a table, based on a defined condition using an alias for better readability and conciseness.
Here's a breakdown of the components:

1. SELECT column_name_1, <column_name_2>: The SELECT statement is used to specify the columns that you want to retrieve from the database. In this case, column_name_1 and column_name_2 are placeholders for the actual column names in the database table.
2. FROM table_name alias: The FROM clause specifies the table from which the SQL query fetches the data. Here, table_name is a placeholder for the actual name of the table in the database. Furthermore, an alias is used to provide a temporary name for the table in the scope of this specific SQL query. This can be useful for improving readability and writing more concise code, especially when working with multiple tables or when tables have long or complex names.
3. WHERE alias.object_name.attribute_name = variable: The WHERE clause filters the results based on specific conditions. Here, alias is the temporary name given to table_name and object_name.attribute_name refers to a specific attribute of an object in the table. variable is a placeholder for the specific value or condition you are using to filter your results. So, this part of the query is checking where the attribute of the object in the aliased table equals a certain variable.

Hence, this SQL query is designed to select certain columns (column_name_1 and column_name_2) from a specific table (represented here as table_name) where an object's attribute equals a certain value (the variable). The alias is used to simplify or shorten the syntax and improve the readability of the query.

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(*)
from BOOKSHELF
group by CategoryName;

create or replace view CATEGORY_COUNT as
select CategoryName, COUNT(*) AS Counter
from BOOKSHELF
group by CategoryName;

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.

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.