Deck 6: The Basics of Querying a Database

Question 1

New tables and views can be created from the results of a query.

Answer 1

True

Question 2

One of the most common uses of the DUAL table is to get the next sequential value from an Oracle sequence object.

Answer 2

True

Question 3

Pseudocolumns contain values present in a table,but not in an Oracle instance.

Answer 3

False

Question 4

Composite queries use special set operators,such as UNION,to concatenate the rows of two different queries together,into a single resulting set of rows.

Question 5

The DISTINCT clause cannot operate on multiple columns.

Question 6

The DUAL table is a little like an internal Oracle database cursor.

Question 7

Any expression containing a NULL value always returns a NULL value.

Question 8

The DUAL table can be queried and updated.

Question 9

NULL values are not included in the most commonly used indexes for an Oracle database.

Question 10

Leaving a column value set to NULL uses more space.

Question 11

Most Oracle database built-in functions will return a NULL value when passed a NULL value.

Question 12

Flashback queries execute SQL statements in parallel,preferably using multiple CPU platforms and Oracle partitions.

Question 13

Use of upper- and lowercase is only a factor in queries for strings or expressions enclosed in Oracle database quotation marks.

Question 14

An alias can be used to refer to a column,typically in a join or a subquery.

Question 15

A space character or a 0 value are NULL.

Question 16

The AS clause can be used in the ORDER BY clause.

Question 17

NULL values sort as the lowest value by default.

Question 18

User-defined SQL functions are functions in which Java is used to custom write functions for specific tasks,which are not available in functions provided in an Oracle database.

Question 19

A likely use of sequences is for surrogate foreign key generation.

Question 20

Oracle SQL allows full expression-based use of both provided (built-in)and user-definable functions.

Question 21

The ____ SQL group function returns the highest value from a group of values.

A) MAXIMUM
B) MAX
C) MX
D) MXM

Question 22

Selecting values greater than ROWNUM,as opposed to less than,or less than or equal to ROWNUM,will return no rows whatsoever.

Question 23

BETWEEN allows multiple rows of returning subqueries.

Question 24

The GROUP BY clause can use the column positional specification like the ORDER BY clause.

Question 25

The ____ SQL group function returns the lowest value from a group of values.

A) MIN
B) MINIMUM
C) MINM
D) MNM

Question 26

The ____ statement allows you to specify tables and columns in the tables,from which data is selected.

A) SELECT
B) WHERE
C) FROM
D) GROUP BY

Question 27

____ queries use the ORDER BY clause to return rows in a specified order.

A) Join
B) Filtered
C) Sorted
D) Aggregated

Question 28

____ queries create groupings or summaries of larger row sets,producing,for example,a sum of values or a number of subtotals from all values.

A) Join
B) Filtered
C) Sorted
D) Aggregated

Question 29

Queries can be sorted in ascending or descending order,where descending is the default if ASC or DESC are not specified.

Question 30

____ queries merge rows from one or more tables,usually linking rows based on related values.

A) Join
B) Filtered
C) Sorted
D) Aggregated

Question 31

____ queries use the WHERE clause to retain or filter out unwanted rows.

A) Basic
B) Filtered
C) Sorted
D) Aggregated

Question 32

The following query is an example of a(n)____.
SELECT TABLESPACE_NAME,BLOCK_SIZE
FROM DBA_TABLESPACES
WHERE TABLESPACE_NAME IN
(SELECT TABLESPACE_NAME FROM DBA_DATA_FILES);

A) join query
B) aggregated query
C) sorted query
D) subquery

Question 33

Subqueries can return more than a single column,and their calling queries can validate against those multiple columns.

Question 34

____ queries simply retrieve rows.

A) Basic
B) Filtered
C) Sorted
D) Aggregated

Question 35

AND has higher precedence than NOT,followed by OR.

Question 36

AS is often used to check membership of one element in a list of elements.

Question 37

EXISTS is sometimes faster than IN.

Question 38

The difference between EXISTS and IN is that EXISTS only allows an expression on the right.

Question 39

Certain types of subqueries are also known as ____ because they join without returning values from the subquery.

A) inner joins
B) outer joins
C) semi-joins
D) cross join

Question 40

Subqueries can be nested within subqueries.

Question 41

Queries can be summarized or aggregated into fewer rows using the GROUP BY and ____ clauses.

A) FROM
B) WHERE
C) HAVING
D) ORDER BY

Question 42

The ____ SQL group function counts the number of rows in a group.

A) COUNT
B) CN
C) CNT
D) COU

Question 43

The following query returns a single number as a total (or sum)of all blocks,used by all datafiles in the database: ____.

A) SELECT TOTAL(BLOCKS)FROM DBA_DATA_FILES;
B) SELECT SUM(BLOCKS)FROM DBA_DATA_FILES;
C) SELECT AVG(BLOCKS)FROM DBA_DATA_FILES;
D) SELECT COUNT(BLOCKS)FROM DBA_DATA_FILES;

Question 44

Comparison of a NULL value to any other value will always evaluate to ____.

A) true
B) false
C) NULL
D) UNDEFINED

Question 45

The most commonly used indexes for an Oracle database are ____ indexes.

A) hash
B) BTree
C) B+Tree
D) Heap

Question 46

A(n)____ is not a function,but can be added to a table query or into other SQL commands to retrieve values not normally accessed from simple table queries.

A) NULL
B) marker
C) index
D) pseudocolumn

Question 47

The ____ statement or clause is the beginning of the SQL command for querying (retrieving)data from a database object,such as a table or view.

A) QUERY
B) SELECT
C) WHERE
D) RETRIEVE

Question 48

Within the realm of a database a ____ valued column,in a row,in a table,is a column,which has never been set to anything (not yet accessed),or has been deliberately set to this value.

A) NIL
B) NUL
C) NULL
D) VOID

Question 49

The ____ SQL group function returns the average of a group of values.

A) AVERAGE
B) AVE
C) AVR
D) AVG

Question 50

A(n)____ is a chunk of memory allocated to a query,to contain the results of a query during processing of that query.

A) DUAL
B) index
C) cursor
D) view

Question 51

The ____ table is used to request data from an Oracle database,where that data is not in user application tables.

A) DUAL
B) NULL
C) TEMP
D) SYS

Question 52

If you obtain a description of the DUAL table using the DESC command in SQL*Plus Worksheet,you will see that the only column present is called ____.

A) DUAL
B) NULL
C) VOID
D) DUMMY

Question 53

The ____ clause is a special SQL clause used to retrieve the first value of each group in a set of duplications.

A) DISTINCT
B) FIRST
C) INDEX
D) NULL

Question 54

Filtering uses the ____ clause to filter out unwanted rows,or retain wanted rows.

A) FROM
B) GROUP BY
C) HAVING
D) WHERE

Question 55

The ____ SQL group function adds values across rows in a group

A) ADD
B) ADDITION
C) SUM
D) SUMMATION

Question 56

Sorting allows resorting of query results using the ____ clause.

A) FROM
B) GROUP BY
C) HAVING
D) ORDER BY

Question 57

The ____({value},{replace})function replaces NULL values in expressions,avoiding SQL errors.

A) NVALUE
B) NVAL
C) NVL
D) NIL

Question 58

This query finds all columns in the table:
SELECT ____ FROM DBA_TABLESPACES;

A) ALL
B) *
C) FULL
D) #

Question 59

____ are automated counters,counting from a specific value,up to a specified maximum (or an enormous value with no maximum specified).

A) Keys
B) Indexes
C) Counters
D) Sequences

Question 60

A(n)____ can act to apply a distinct name to multiple columns with the same name.

A) pseudonym
B) pseudocolumn
C) alias
D) tag

Question 61

ANY,SOME,and ____ check set membership and allow subqueries that return multiple rows.

A) BETWEEN
B) IN
C) AS
D) ALL

Question 62

____ set membership evaluates an expression as being within a set of elements.

A) GROUP BY
B) HAVING
C) IN
D) LIKE

Question 63

____ is used as a pattern-matching character representing zero or more characters in a subset of a string.

A) %
B) _
C) *
D) #

Question 64

____ validates an expression as being between two values,and is inclusive.

A) AS
B) IN
C) BETWEEN
D) IN BETWEEN

Question 65

The ____ pseudocolumn is used only in hierarchical queries,in tandem with the CONNECT BY operator,returning the level of a row in a hierarchy of rows,in a hierarchical query.

A) LEVEL
B) PRIOR
C) START WITH
D) FIRST

Question 66

The ____ set operator returns one query less the other,similar to a left outer join where only distinct rows in the first query are returned.

A) UNION ALL
B) INTERSECT
C) MINUS
D) DISTINCT

Question 67

A(n)____ flashback query allows you to pull data from a database at a specific point in time.

A) AS
B) LIKE
C) AS OF
D) IN

Question 68

A(n)____ join combines rows from both tables using matching column names and column values.

A) outer
B) inner
C) cross
D) self

Question 69

____ pattern matches between strings;those strings are expressions.

A) LIKE
B) HAVING
C) WHERE
D) AS

Question 70

A database ____________________ uses a special type of SQL command called a SELECT statement.

Question 71

A(n)____ join returns all rows from both tables with null values replacing missing values.

A) inner
B) left outer
C) right outer
D) full outer

Question 72

The ____ clause extends the GROUP BY clause by filtering on resulting grouped rows (returned by the GROUP BY clause).

A) HAVING
B) WHERE
C) AS
D) IN

Question 73

When using GROUP BY,the expression for the ____ statement should include at least one grouping function such as COUNT().

A) HAVING
B) SELECT
C) WHERE
D) FROM

Question 74

The GROUP BY clause column list ____ include all columns in the SELECT statement not affected by any aggregate functions.

A) must
B) must not
C) should
D) should not

Question 75

The ____ clause allows a hierarchical query to begin displaying within a hierarchical structure,with a specified column and value.

A) LEVEL
B) PRIOR
C) START WITH
D) FIRST

Question 76

The ____ set operator returns distinct rows from both queries.

A) UNION ALL
B) INTERSECT
C) MINUS
D) DISTINCT

Question 77

____ is a wildcard character.

A) #
B) _
C) +
D) $

Question 78

____ is used to represent one and only one character.

A) %
B) _
C) *
D) #

Question 79

____ work best on multiple CPU platforms in tandem with partitioning,and particularly with multiple disks or RAID arrays.

A) Subqueries
B) Flashback queries
C) Joins
D) Parallel queries

Question 80

Top-N queries are executed by filtering against the ____ pseudocolumn.

A) POSITION
B) POS
C) COLNUM
D) ROWNUM