1. Retrieving Records
Retrieving All Rows and Columns from a Table
Above query is same to this one in terms of performance but while writing program, it is good to write columns so that we will always know what columns we are returning from the query.
select empno,ename,job,sal,mgr,hiredate,comm,deptno from emp;
Finding Rows That Satisfy Multiple Conditions
Providing Meaningful Names for Columns
Using the AS keyword to give new names to columns returned by the query is known as aliasing those columns. The new name that is given is known as aliase.
Referencing an Aliased Column in the WHERE Clause
An attempt to reference alias names in the WHERE clause will fail. We need to wrap the query as an inline view to reference the aliased columns.
The WHERE clause is evaluated before the SELECT; thus, SALARY and COMMISSION do not yet exist when the query’s WHERE clause is evaluated. Those aliases are not applied until after the WHERE clause processing is complete.
Concatenating Column Values
We can return values in multiple columns as one column.
Using Conditional Logic in a SELECT Statement
We can use the CASE expression to perform conditional logic directly.
Limiting the Number of Rows Returned
We can use the built-in function provided by the database to control the number of rows returned.
Oracle ROWNUM explanation -
Here is what happens when we use ROWNUM <= 5 to return the first five rows:
Oracle executes the query.
Oracle fetches the first row and calls it row number one.
Have we gotten past row number five yet? If no, then Oracle returns the row, because it meets the criteria of being numbered less than or equal to five. If yes, then Oracle does not return the row.
Oracle fetches the next row and advances the row number (to two, then to three, then to four, and so forth).
Go to step 3.
Here is what happens when we use ROWNUM = 5
Oracle executes the query.
Oracle fetches the first row and calls it row number one.
Have we gotten to row number five yet? If no, then Oracle discards the row, because it doesn’t meet the criteria. If yes, then Oracle returns the row. But the answer will never be yes!
Oracle fetches the next row and calls it row number one. This is because the first row to be returned from the query must be numbered as one.
Go to step 3.
Returning n Random Records from a Table
We want to return a specific number of random records from a table.
Finding Null Values
We want to find all rows that are null for a particular column.
Transforming Nulls into Real Values
We have rows that contain nulls and would like to return non-null values in place of those nulls.
Searching for Patterns
We want to return rows that match a particular substring or pattern.
Last updated