Review Questions


Review Questions

1. 

Rewrite the following expression using the CONCAT function.

last_name || ‘, ‘ || first_name

the expression is rewritten as: concat(concat(last_name, , ),first_name)

2. 

What are two ways that you can indicate a comment in a SQL command?

you can indicate a comment in a sql command by using /* and */ or by using -- .

3. 

The SQL engine converts the IN operator to a series of .

the sql engine converts the in operator to a series of or operations.

4. 

Rewrite the following WHERE clause to be case-insensitive.

where job_title like ‘%Manager%’;

use the upper function to convert the job title to uppercase: where upper(job_title) like %manager% ;

5. 

What is the only group function that counts NULL values in its calculation without using NVL or other special processing?

the count group function using the syntax count(*) counts null values without using nvl .

6. 

The query results from using aggregate functions with a GROUP BY clause can be filtered or restricted by using what clause?

the having clause filters or restricts the query results of the group by clause.

7. 

Identify the two special characters used with the LIKE operator and describe what they do.

the % character matches zero or more characters, and thecharacter matches exactly one character.

8. 

Name two aggregate functions that work only on numeric columns or expressions, and two other aggregate functions that work on numeric, character, and date columns.

 avg and sum work only on numeric columns; min and max work on all datatypes.

9. 

Put the clauses of a SQL SELECT statement in the order in which they are processed.

the proper order is: select , where , group by , having , order by .

10. 

Which operator can do valid comparisons to columns with NULL values?

the operator is is null .

11. 

The SQL engine converts the BETWEEN operator to .

the sql engine converts the between operator to two logical comparisons using - = and - = , connected by an and operation.

12. 

Where do NULL values end up in a sort operation?

for ascending sorts, the null values are at the end; for descending sorts, the null values are at the beginning.

Answers

1. 

The expression is rewritten as:

concat(concat(last_name, ‘, ‘),first_name)

2. 

You can indicate a comment in a SQL command by using /* and */ or by using --.

3. 

The SQL engine converts the IN operator to a series of OR operations.

4. 

Use the UPPER function to convert the job title to uppercase:

where UPPER(job_title) like ‘%MANAGER%’;

5. 

The COUNT group function using the syntax COUNT(*) counts NULL values without using NVL.

6. 

The HAVING clause filters or restricts the query results of the GROUP BY clause.

7. 

The % character matches zero or more characters, and the character matches exactly one character.

8. 

AVG and SUM work only on numeric columns; MIN and MAX work on all datatypes.

9. 

The proper order is: SELECT, WHERE, GROUP BY, HAVING, ORDER BY.

10. 

The operator is IS NULL.

11. 

The SQL engine converts the BETWEEN operator to two logical comparisons using >= and <=, connected by an AND operation.

12. 

For ascending sorts, the NULL values are at the end; for descending sorts, the NULL values are at the beginning.



Terms to Know

  • aggregate

  • comment

  • pattern matching



Chapter 5: Using Multiple Tables

So far, we have been dealing with only one table at a time in our SQL query examples. But typically the information needed to satisfy a user query requires more than one table. For example, the EMPLOYEES table has a column with a department number, but not a department name; the department name must be retrieved from the DEPARTMENTS table. You can get this information by joining the two tables together on a common column, in this case, the DEPARTMENT_ID column. Two or more tables can also be joined in situations where the columns may not be equal.

The boss at Scott’s widget company has realized that data can be pulled from more than one table at a time. Now the application developer and DBA, Janice, has been busy trying to keep up with his requests for reports. Each of the join types will be discussed in this chapter, as we follow Janice’s work.

Join Syntax: Out with the Old and In with the New (SQL:1999)

Not only can you join two or more tables in a number of different ways, but you can also use two different syntax forms to perform these joins. As of Oracle9i, the full ANSI SQL:1999 standard for join syntax is supported. Prior to Oracle9i, Oracle used a proprietary syntax that wasn’t always compatible with the ANSI standard.

join

To combine two or more tables in a query to produce rows as a result of a comparison between columns in the tables.

Oracle’s proprietary syntax, which is still supported in the current release for backward compatibility with existing code, put all of the join conditions in the SELECT statement’s WHERE clause. It also relied on relatively obscure methods to indicate certain types of join operations. The newer syntax relies more heavily on concise yet descriptive keywords to clearly indicate what operation is being performed. We’ll cover both the old and new syntax in this chapter; as a DBA or developer, you’ll most likely see new applications using the new syntax, and plenty of existing applications that use the old syntax.

Tip 

All new SQL code should use the SQL:1999 standard syntax for readability and cross-platform compatibility.

There is no performance benefit to using one syntax over the other; the same kind of join using either syntax will translate into the same internal SQL engine operation. One of the biggest benefits is the ease in which the new syntax can be written and understood. The join conditions are now separated from the WHERE clause and placed in the FROM clause. The WHERE clause, if one even exists, ends up being much cleaner, because it’s used only for filtering the rows being returned from the query, instead of being intertwined with table join conditions.

In each section of this chapter, you’ll see how the database analyst, Janice, uses both formats for each new query she develops for the boss.