Full Outer Joins

When you want to see a list of all values from two tables in a join, even if there are no matches, you want the Full Outer join. It fills in nulls on both sides where needed. Try this query: SELECT * FROM hr.regions FULL OUTER JOIN hr.countries USING (region_id); If you look through the … Continue reading Full Outer Joins

Using IN, ANY, and ALL Queries

ANY and ALL queries are related to IN queries in that they are used for multi-row returns. They also allow comparisons to be made (equal, not equal, greater than, less than, etc.). The IN Condition Reviewing an IN condition in a query first, we know that the field on the left side of the IN … Continue reading Using IN, ANY, and ALL Queries

Order of SQL SELECT Clause Evaluation

SQL Clauses are evaluated in the following order: Find Where Good Snakes Have Oaths FROM clause WHERE clause GROUP BY clause SELECT clause HAVING clause ORDER BY clause

Oracle Single Row Character Functions Returning Character Values

Exam Topic: Using Single-Row Functions to Customize Output - Describe various types of functions available in SQL Single row functions return a result for every row in a result. They can be used in any part of a SQL query, such as the SELECT lists or any part that can use a conditional statement. Oracle 11g has … Continue reading Oracle Single Row Character Functions Returning Character Values

Basic Algebraic Functions

The following is a mini-workout of the basic Algebraic functions found in Oracle 11g. It includes ABS, CEILING, FLOOR, MOD, REMAINDER, NANVL, POWER, ROUND, SIGN, TRUNC and BUCKET_WIDTH. More functions can be found here. ABS (Absolute Value) This function returns the number of positions a number is from zero. select abs(-10) from dual; ABS(-10) ---------- 10 … Continue reading Basic Algebraic Functions

Convert Decimal to Other Number Systems

Just for fun, I made a block of code that will convert decimal numbers to any other number system. Maybe I'll go the other direction next. It's basically a loop. It takes the remainder of the original number divided by the number system (MOD) and adds that to a string. The string is made by … Continue reading Convert Decimal to Other Number Systems

Oracle Single Row Numeric Functions

Ampersand Substitution and Session Variables

Exam Topic: Restricting and Sorting Data - Use ampersand substitution to restrict and sort output at runtime The Ampersand (&) and Double-Ampersand (&&) are used in oracle as prompts for input. They are called "Substitution Variables." They can be used to input a value or dynamically build or modify a statement at runtime. This allows you … Continue reading Ampersand Substitution and Session Variables

Sorting Data – the ORDER BY Clause

Exam Topic: Restricting and Sorting Data - Sort the rows that are retrieved by a query As data is inserted and updated and indexes keep track of rows, the order in which the rows are stored can change. There is no guarantee that the way Oracle stores the data in your system is in any kind of … Continue reading Sorting Data – the ORDER BY Clause

Using Expressions in a SELECT FROM DUAL

Exam Topic: Retrieving Data Using the SQL SELECT Statement - Execute a basic SELECT statement In The Capabilities of a SELECT Statement, we talked about returning columns from a table using the SELECT list and a few of the alternatives. In fact, any expression you can think of can be in the SELECT list. An expression is "a combination of one … Continue reading Using Expressions in a SELECT FROM DUAL