Chapter 4

1.	Rewrite the following expression using the CONCAT function. last_name || ‘, ‘ || first_name
2.	What are two ways that you can indicate a comment in a SQL command?
3.	The SQL engine converts the IN operator to a series of .
4.	Rewrite the following WHERE clause to be case-insensitive. where 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?
6.	The query results from using aggregate functions with a GROUP BY clause can be filtered or restricted by using what clause?
7.	Identify the two special characters used with the LIKE operator and describe what they do.
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.
9.	Put the clauses of a SQL SELECT statement in the order in which they are processed.
10.	Which operator can do valid comparisons to columns with NULL values?
11.	The SQL engine converts the BETWEEN operator to .
12.	Where do NULL values end up in a sort operation?

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.