SQL Fundamentals I, SQL Simantics

Full Outer Joins

Randy Sims SQL Fundamentals I

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.

Using IN, ANY, and ALL Queries

Randy Sims SQL Fundamentals I

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.).

Order of SQL SELECT Clause Evaluation

Randy Sims SQL Fundamentals I

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

Randy Sims SQL Fundamentals I

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 […]

Basic Algebraic Functions

Randy Sims SQL Fundamentals I

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.

Convert Decimal to Other Number Systems

Randy Sims Oracle, SQL Fundamentals I

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 […]

Oracle Single Row Numeric Functions

Randy Sims SQL Fundamentals I

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 […]

Ampersand Substitution and Session Variables

Randy Sims SQL Fundamentals I

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 […]

Sorting Data – the ORDER BY Clause

Randy Sims SQL Fundamentals I

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 […]

Using Expressions in a SELECT FROM DUAL

Randy Sims SQL Fundamentals I

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 […]