Sql Fundamentals Chapter 3

Front 1

What is projection?

Back 1

Limiting the columns retrieved by a SELECT statement.

Front 2

What is selection?

Back 2

Restricting the rows returned

Front 3

The ______ clause specifies one or more conditions that the Oracle server evaluates to restrict the rows returned by the statement.

Back 3

WHERE

Front 4

The ______ clause provides data sorting capabilities.

Back 4

ORDER BY

Front 5

____ ____ introduces a way to reuse the same statement to execute different queries by substituting query elements at runtime.

Back 5

Ampersand substitution

Front 6

Conditions restrict rows using what?

Back 6

comparison operators in conjunction with columns and literal values.

Front 7

_____ ______ provide a mechanism to specify multiple conditions to restrict the rows returned.

Back 7

Boolean operators

Front 8

What extends the SELECT statement by providing the language to restrict rows returned based on one or more conditions?

Back 8

The WHERE clause

Front 9

Querying a table with just the SELECT and FROM clauses results in what?

Back 9

Every row of data stored in the table being returned.

Front 10

What happens when you use the DISTINCT keyword?

Back 10

Duplicate values are excluded, and the resultant rows are restricted to some degree.

Front 11

The WHERE clause most always come after what?

Back 11

The FROM clause

Front 12

A condition is specified by what?

Back 12

By comparing two terms using a conditional operator.

Front 13

The _______ operator is most commonly used to restrict result sets.

Back 13

equality

Front 14

T/F Conditions determining which rows are selected based on character data, are specified by enclosing character literals in the conditional clause, within single quotes.

Back 14

True.

Front 15

What would happen if you tried specifying the character literal without the quotes?

Back 15

An oracle error would be raised.

Front 16

Why are the following WHERE clauses not equivalent:

where job_id=SA_REP

where job_id='Sa_Rep'

where job_id='sa_rep'

Back 16

1. Clause 1 generates an "ORA-00904: invalid identifer" error since the literal SA_REP is not wrapped in single quotes.

2. Clause 2 and 3 are syntactically correct but not equivalent.

Front 17

T/F Date literals must be enclosed in single quotation marks just like character data; otherwise an error is raised.

Back 17

True.

Front 18

When used in conditional WHERE clauses, DATE columns are compared to ______.

Back 18

other DATE columns or to date literals

Front 19

If a literal occurs in an expression involving a DATE column, it is automatically converted into what?

Back 19

a date value using the default format mask (DD-MON-RR)

Front 20

______ _______ compare two terms using comparison operators.

Back 20

Conditional clauses.

Front 21

The ________ ________ like "less than" or "greater than or equal to" may be used to return rows conforming to inequality conditions.

Back 21

inequality operators

Front 22

The _____ _______ facilitates range-based comparison to test whether a column value lies between two values.

Back 22

BETWEEN operator

Front 23

The ____ _____ tests set membership, so a row is returned if the column value tested in the condition is a member of a set of literals.

Back 23

IN operator

Front 24

The pattern matching comparison operator _____ is extremely powerful, allowing components of character column data to be matched to literals conforming to a specific pattern.

Back 24

LIKE

Front 25

The ____ _____ _____ returns rows where the column value contains a null value.

Back 25

IS NULL operator

Front 26

The _____ operator tests whether a column or expression value falls within a range of two boundary values.

Back 26

BETWEEN

Front 27

What does the IN operator test?

Back 27

Whether an item is a member of a set of literal values. The set is specified by a comma separating the literals and enclosing them in round brackets. If the literals are character or date values, then these must be delimited using single quotes.

Front 28

The IN operator is equivalent to a series of ______ ______.

Back 28

OR conditions.

Front 29

What is the syntax for the IN Operator?

Back 29

Select (column name) from (table name)

where (column name) in (____, _____, .....);

Front 30

The LIKE operator is accompanied by what two wildcard characters?

Back 30

1. the percentage symbol (%)

2. the underscore character (_).

Front 31

What does the percentage symbol specify?

Back 31

Zero or more wildcard characters

Front 32

What does the underscore character specify?

Back 32

old wildcard character.

Front 33

T/F A wildcard may represent any character

Back 33

True.

Front 34

What would be the syntax if you wanted to retrieve the first name of all employees ?

Back 34

select first_name from employees

where first_name like 'A%';

Front 35

What would the following syntax return?

select first_name from employees

where first name like '%';

Back 35

Every row containing a first_name value that is not null will be returned.

Front 36

T/F Wildcard symbols are mandatory when using the LIKE operator.

Back 36

False. LIKE behaves as an equality operator testing for exact character matches.

Front 37

Are the following clauses equivalent?

where last_name like 'King';

where last_name = 'King';

Back 37

Yes, they are equivalent.

Front 38

What would be the syntax if you wanted to search for all employees whose last names are four letters long, begin with a "K," have an unknown second letter, and end with an ng?

Back 38

where last_name like 'K_ng';

Front 39

T/F It is not practically possible to perform character pattern matching without the user of the LIKE operator and the wildcard symbols.

Back 39

True

Front 40

T/F A naturally occurring underscore character may be escaped (or treated as a regular nonspecial symbol) using the ESCAPE identifier in conjunction with an ESCAPE character.

Back 40

True.

Front 41

The ESCAPE identifier instructs the Oracle server to do what?

Back 41

Treat any character found after the backslash character as a regular nonspecial symbol with no wildcard meaning.

Front 42

What is the syntax for a query that fetches the last_name column from the employees table for those rows which have NULL values stored in the commission_pct column?

Back 42

select last_name from employees

where commission_pct is null;

Front 43

________ or _________ operators enable multiple conditions to be specified in the WHERE clause of the SELECT statement.

Back 43

Boolean or logical

Front 44

What does the AND operator do?

Back 44

It merges conditions into one larger condition to which a row must conform to be included in the results set.

Front 45

Boolean operators are defined using _____ _____.

Back 45

truth tables.

Front 46

T/F If two conditions specified in a WHERE clause are joined with an AND operator, then a row is tested consecutively for conformance to both conditions before being retrieved.

Back 46

True

Front 47

T/F If two conditions in a WHERE clause conform to neither or only one of the conditions, the row is excluded since the result is FALSE>

Back 47

True.

Front 48

A row will only be returned if every condition joined with an AND operator evaluates to _____.

Back 48

TRUE.

Front 49

In a scenario with more than two conditions joined with the AND operator, what will be returned?

Back 49

Only data conforming to every condition will be returned.

Front 50

What syntax returns employee records with first_name values beginning with the letter "J" and COMMISSION_PCT greater than 10 percent?

Back 50

select first_name, last_name, commission_pct, hire_date

from employees

where first_name like 'J%'

and commission_pct > 0.1;

Front 51

What does the OR operator do?

Back 51

It separates multiple conditions, at least one of which must be satisfied by the row selected to warrant inclusion in the results set.

Front 52

T/F If two conditions specified in a WHERE clause are joined with an OR operator then a row is tested consecutively for conformance to either or both conditions before being retrieved.

Back 52

True.

Front 53

T/F Conforming to just one of the OR conditions is not sufficient for the record to be returned.

Back 53

False. It is sufficient.

Front 54

T/F A row will only be returned if at least one of the conditions associated with an OR operator evaluates to TRUE.

Back 54

True

Front 55

What is the syntax for retrieving employee records having first_name values beginning with the letter B or those with a commission_pct greater than 35%?

Back 55

select first_name, last_name, commission_pct, hire_date

from employees

where first_name like 'B%'

or commission_pct > 0.35;

Front 56

T/F The more OR conditions you specify, the more restrictive your query becomes.

Back 56

False. The more OR conditions, the less restrictive it becomes.

Front 57

You have a complex query with multiple conditions. Is there a restriction on the number of conditions you can specify in the WHERE clause? Is there a limit to the number of comparison operators you can use in a single query?

Back 57

No. You may specify any number of conditions in the WHERE clause separated by the Boolean operators. There is no limit when using the comparison operators, and they may be specified multiple times if necessary in a single query.

Front 58

You have been tasked to locate rows in the EMPLOYEES table where the SALARY values contain the numbers 8 and 0 adjacent to each other. The SALARY column has a NUMBER data type. Is it possible to use the LIKE comparison operator with numeric data?

Back 58

Yes. Oracle automatically casts the data into the required data type, if possible. In this case, the numeric SALARY values are momentarily "changed" into character data allowing the user of the LIKE operator to locate matching patterns. The following query locates the required rows:

select * from employees

where salary like '%80%',"

Front 59

By restricting the rows returned from the JOBS table to those which contain the value SA_REP in the JOB_ID column, is a projection, selection, or join performed?

Back 59

A selection is performed since rows are restricted.

Front 60

What is the NOT operator?

Back 60

It negates conditional operators in a condition, whether it's an equality, inequality, range based, pattern matching, set membership, or null testing operator. A selected row must conform to the logical opposite of the condition in order to be included in the results set.

Front 61

What is the syntax for a query that retrieves employee records with first_name values that do NOT begin with the letter "B" or those that do NOT comply with a commission_pct greater than 35%?

Back 61

select first_name, last_name, commission_pct, hire_date

from employees

where first_name not like 'B%'

or not (commission_pct > 0.35);

Front 62

What does the ORDER BY clause do?

Back 62

This clause is responsible for transforming the output of a query into more practical, user-friendly sorted data.

Front 63

T/F The ORDER BY clause is always the last clause in a SELECT statement.

Back 63

True.

Front 64

What is the default sort order when the ORDER BY clause is specified?

Back 64

ascending sort order.

Front 65

What is the ascending sort order for numbers, dates and characters?

Back 65

An ascending sort order for numbers is lowest to highest, while it is earliest to latest for date and alphabetically for characters.

Front 66

T/F If null values occur in the sort column, the default sort order is assumed to be NULLS LAST for ascending sorts and NULLS FIRST for descending sorts.

Back 66

True

Front 67

T/F If no ORDER BY clause is specified, the same query executed at different times will still return the same set of results in the same row order.

Back 67

False. If no order by clause is specified, the same query executed at different times may return the same set of results in different row order, so no assumptions should be made regarding the default row order.

Front 68

Results of a query may be sorted by more than one column using what?

Back 68

composite sorting

Front 69

T/F Any text, subclause or clause element, or even the entire SQL query is a candidate for substitution.

Back 69

True

Front 70

What is the most basic and popular form of substitution of elements in a SQL statement?

Back 70

single ampersand substitution

Front 71

The _____ _____ is the symbol chosen to designate a substitution variable in a statement and precedes the variable name with no spaces between them.

Back 71

ampersand character (&)

Front 72

When a statement that includes the ampersand character is executed, what does the Oracle server do?

Back 72

It notices the substitution variable and attempts to resolve this variable's value in one of two ways:

1. First, it checks whether variable is defined in user session.

2. If the variable is not defined, the user process prompts for a value that will be substituted in place of the variable.

Once the value is submitted, the statement is complete and is executed by the Oracle server.

Front 73

The ampersand substitution variable is resolved at execution time and is sometimes known as what?

Back 73

runtime binding or runtime substitution

Front 74

What will the Oracle server do when a substitution variable is reference multiple time in the same query?

Back 74

The Oracle server will prompt you to enter a value for every occurrence of the single ampersand substitution variable.

Front 75

____ ____ _____ is used to avoid repetitive input when the same variable occurs multiple times in a statement.

Back 75

double ampersand substitution

Front 76

When a double amperand substitution occurs, the variable is stored as a ______ ______.

Back 76

Session variable

Front 77

The _____ ______ is specific to SQL*Plus and controls whether or not substituted elements are echoed on the user's screen prior to executing a SQL statement that uses substitution variables.

Back 77

Verify command

Front 78

T/F Session level variables are implicitly created when they are initially referenced in SQL statements using double ampersand substitution. They persist or remain available for the duration of the session or until they are explicitly undefined.

Back 78

True.

Front 79

T/F A session ends when the user exits their client tool like SQL*Plus or when the user process is terminated abnormally.

Back 79

True

Front 80

What is the syntax for the undefine command?

Back 80

UNDEFINE variable;

Front 81

The DEFINE command serves what two purposes?

Back 81

1. It can be used to retrieve a list of all the variables currently defined in your SQL session.

2. It can also be used to explicitly define a value for a variable referenced as a substitution variable by one or more statements during the lifetime of that session.

Front 82

What is the syntax for the DEFINE command?

Back 82

DEFINE;

DEFINE variabl=value;

Front 83

The capacity of the SQL client tool to support session-persistent variables may be switched off and on as required using what command?

Back 83

The SET command

Front 84

T/F By specifying SET DEFINE OFF, the client tool does not save session variables or attach special meaning to the ampersand symbol.

Back 84

True. This allows the ampersand symbol to be used as an ordinary literal character if necessary.

Front 85

The _______ _____ _______ command determines whether or not ampersand substitution is available in your session.

Back 85

SET DEFINE ON|OFF

Front 86

Generic, reusable statements may be constructed using ____ ____ variables which prompt for runtime values during execution.

Back 86

ampersand substitution

Front 87

One or more conditions constitute a _____ _____.

Back 87

WHERE clause.

Front 88

The _____ environmental setting controls whether SQL*Plus displays the old and new versions of statement lines which contain substitution variables.

Back 88

VERIFY