Exam 2 Outcome 4-6

Question 1

for table joins, the join condition is found in the ____ clause

Answer 1

WHERE

Question 2

table joins are usually based on _____ relationships

Answer 2

primary/foreign key

Question 3

table aliases are found in the _____ clause

Answer 3

WHERE

Question 4

a table alias replaces the ____ with a ____ in order to simplify column identifications

Answer 4

table name; single letter (or more)

Question 5

if a column name within the SELECT statement exists in more than one table within the FROM clause, the column bust be _____

Answer 5

further identified by table with the corresponding table alias

Question 6

tables are improperly joined when there is ____

Answer 6

no join condition placed in the WHERE clause

Question 7

when tables are improperly joined, a ____ is created

Answer 7

cartesian product

Question 8

returns every row of data in one table joined to every row of data in the other tables

Answer 8

cartesian product

Question 9

in cartesian product, the ____ returned is multiplied

Answer 9

number of rows

Question 10

t or f. users may perform cartesian product on purpose

Answer 10

t

Question 11

t or f. it doesn’t matter whether you list WHERE clause restrictions before or after join relationships

Answer 11

t

Question 12

relationship between # of tables and # of join conditions

Answer 12

of tables is always 1 more than # of join conditions

Question 13

5 types of joins

Answer 13

1. cartesian
2. equality
3. self
4. non-equality
5. outer

Question 14

join: useful when doing certain statistical calculations

Answer 14

cartesian join

Question 15

another name for cartesian join

Answer 15

cross join

Question 16

join: matches columns on the basis of equality

Answer 16

equality join

Question 17

join: based on primary/foreign key relationships

Answer 17

equality join

Question 18

3 other names for equality joins

Answer 18

1. equijoin
2. inner join
3. simple join

Question 19

join: data in one column of a table has a relationship with another column in the same table

Answer 19

self-join

Question 20

in self-joins, it is necessary to link the table to itself in order to _____

Answer 20

match data

Question 21

join: joins 2 or more tables based on a specified column value that doesn’t equal a specified column value in another table

Answer 21

non-equality joins

Question 22

join: used to see rows that don’t meet the join condition

Answer 22

outer joins

Question 23

outer join operator

Answer 23

(+)

Question 24

(+) is used on the join condition in the ____ clause

Answer 24

WHERE

Question 25

(+) can be placed on ____ of the WHERE clause but not ____

Answer 25

either side; both sides

Question 26

1. outer join rule: a WHERE condition using a column from the outer join table must also include ____

Answer 26

(+) operator

Question 27

2. outer join rule: a WHERE condition containing the (+) operator can’t be combined with another condition using this operator

Answer 27

OR

Question 28

3. outer join rule: this logical operator is usable in place of OR

Answer 28

AND

Question 29

4. outer join rule: a WHERE condition can’t use the ___ condition to compare a column marked with the (+) with an expression

Answer 29

IN comparison

Question 30

5. outer join rule: a WHERE condition can’t compare any column marked with the (+) operator with a ____

Answer 30

subquery

Question 31

another name for non-equality join

Answer 31

non-equijoin

Question 32

join: replicates each row from the first table with every row from the second table (displays every possible record combo)

Answer 32

cartesian join

Question 33

join: creates join by using commonly named or defined columns

Answer 33

equality join

Question 34

join: joins a table to itself

Answer 34

self-join

Question 35

join: joins tables when there are no equivalent rows in the tables to be joined (matches values in one column with a range of values in another)

Answer 35

non-equality join

Question 36

join: includes records of a table in output when there’s no matching record in the other table

Answer 36

outer join

Question 37

of alternate methods to perform equality joins

Answer 37

3

Question 38

2 advantages of alternate equality joins

Answer 38

1. WHERE clause is used only for row restriction conditions

2. improves script readability

Question 39

alternate equality join that is most similar to traditional syntax

Answer 39

JOIN ON

Question 40

alternate equality join that is rarely used due to potential for more than one column name matching between the tables

Answer 40

NATURAL JOIN

Question 41

alternate equality join where table aliases aren’t required

Answer 41

JOIN USING

Question 42

only possible alternate syntax for self-joins and non-equality joins

Answer 42

JOIN ON

Question 43

disadvantage of using the outer join operator (+) on the deficient side of the join condition

Answer 43

can only be placed on one side of one side of one join condition

Question 44

alternate outer join syntax: right table is deficient

Answer 44

LEFT OUTER JOIN

Question 45

alternate outer join syntax: left table is deficient

Answer 45

RIGHT OUTER JOIN

Question 46

2 advantage of alternate outer join

Answer 46

1. can be used on both sides of join condition using FULL OUTER JOIN syntax
2. multiple tables can be joined using differrent outer joins

Question 47

disadvantage of alternate outer join

Answer 47

does not work when WHERE conditions on “deficient” table are used

Question 48

join: provides the only means to show all rows from both tables whether a join condition is met or not

Answer 48

full outer join

Question 49

cannot use _____ for full outer join

Answer 49

traditional syntax

Question 50

functions that return one row of output for each record processed

Answer 50

single row functions

Question 51

functions that return one result for each group of rows processed

Answer 51

group functions

Question 52

2 other names for group functions

Answer 52

1. multiple-row functions

2. aggregate functions

Question 53

group function that returns the number of rows

Answer 53

COUNT

Question 54

data type: COUNT

Answer 54

any

Question 55

group function: sum of values in rows returned for a specific column

Answer 55

SUM

Question 56

data type: SUM

Answer 56

NUMBER

Question 57

group function: average value of rows returned for a specific column

Answer 57

AVG

Question 58

data type: AVG

Answer 58

NUMBER

Question 59

group function: max value from rows returned for a specific column

Answer 59

MAX

Question 60

data type: MAX

Answer 60

any

Question 61

group function: minimum value from rows returned for a specific column

Answer 61

MIN

Question 62

data type: MIN

Answer 62

any

Question 63

t or f. all group function ignore null values

Answer 63

t (one exception)

Question 64

3 ways to use COUNT

Answer 64

1. COUNT (*)
2. COUNT (expr)
3. COUNT (DISTINCT expr)

Question 65

counts all rows that meet the condition including duplicates and nul values

Answer 65

COUNT (*)

Question 66

returns number of non-null values in the column specified

Answer 66

COUNT (expr)

Question 67

returns non-duplicate values in the column specified, including null values

Answer 67

DISTINCT

Question 68

determines unique values within the column specified

Answer 68

DISTINCT

Question 69

how many DISTINCTS can be used per SELECT clause?

Answer 69

only 1

Question 70

DISTINCT must be listed on the ____

Answer 70

first column/expression

Question 71

can only use DISTINCT on columns listed in the _____ for sorting

Answer 71

SELECT clause

Question 72

returns the number of non-duplicate, non-null values in the column specified

Answer 72

COUNT (DISTINCT expr)

Question 73

t or f. MAX and MIN don’t work with DATE and VARCHAR2 fields

Answer 73

f.

Question 74

MAX DATE returns ____

Answer 74

recent

Question 75

MIN DATE returns ____

Answer 75

old

Question 76

MAX VARCHAR2 returns ____

Answer 76

z

Question 77

MIN VARCHAR 2 returns ____

Answer 77

a

Question 78

since group functions ignore null values, a ____ must be employed to force calculations to include null values

Answer 78

single-row functions

Question 79

single-row function that changes the display of a null value, allowing group functions to display it

Answer 79

NVL

Question 80

when to use JOIN USING

Answer 80

equality joins

Question 81

when to use JOIN ON

Answer 81

everything else

Question 82

used when dividing summary data into groups

Answer 82

GROUP BY clause

Question 83

the column used in the GROUP BY clause doesn’t need to be included in the _____

Answer 83

SELECT clause

Question 84

____ can’t be used in the GROUP BY clause

Answer 84

column aliases

Question 85

group functions can only be nested to a depth of ___

Answer 85

2

Question 86

t or f. multiple single row functions can be applied to nested group functions

Answer 86

t

Question 87

why error: not a group by expression?

Answer 87

missing column

Question 88

why error: not a single group function?

Answer 88

check group by

Question 89

group functions can’t be executed within a _____ clause

Answer 89

WHERE

Question 90

the WHERE clause is used to restrict ____, the HAVING clause is used to restrict ____

Answer 90

rows; groups

Question 91

combines the results of two or more queries into a single result

Answer 91

set operator

Question 92

4 set operators

Answer 92

1. UNION
2. UNION ALL
3. INTERSECT
4. MINUS

Question 93

for set operators, each query must contain ____

Answer 93

the same number of columns

Question 94

for set operators, columns between the queries must have ____

Answer 94

coordinating data types

Question 95

for set operators, column names can be different between queries but the output will adopt the column names from ____

Answer 95

the first query

Question 96

for set operators, only one ____ can be instituted at the end of set operation syntax

Answer 96

ORDER BY

Question 97

for set operators, ____ results can’t be ordered separately

Answer 97

individual query

Question 98

for set operators, ____ can’t be used

Answer 98

column aliases

Question 99

t or f. set operators can be used in subqueries

Answer 99

t

Question 100

set operators: returns results of both queries but removes duplicate records

Answer 100

UNION

Question 101

set operators: similar to DISTINCT

Answer 101

UNION

Question 102

set operators: returns results of both queries; includes duplicate records

Answer 102

UNION ALL

Question 103

DISTINCT set operator

Answer 103

UNION

Question 104

includes duplicates set operator

Answer 104

UNION ALL

Question 105

set operators: returns only results appearing in both queries

Answer 105

INTERSECT

Question 106

set operators: subtracts the second query’s results if they are also returned in the first query’s results

Answer 106

MINUS

Question 107

set operators: opposite of intersect; does not show intersecting results

Answer 107

MINUS

Question 108

a view is a ____ just like tables, sequences

Answer 108

database object

Question 109

although views are database objects, they don’t actually store data – they store ____ to access data in underlying tables

Answer 109

a query

Question 110

3-step view process:

Answer 110

1. create view based on data in table(s)
2. query the view instead of the table(s)
3. the query stored in the view is executed

Question 111

4 purposes of views

Answer 111

1. Simplify issuing complex SQL queries
2. Restrict users’ access to sensitive data
3. Perform calculations on columns
4. Portray data in different ways

Question 112

t or f. if you use a column alias, for views, you cannot use the regular column name in a query

Answer 112

t