Exam 2 Outcome 4-6 Flashcards
for table joins, the join condition is found in the ____ clause
WHERE
table joins are usually based on _____ relationships
primary/foreign key
table aliases are found in the _____ clause
WHERE
a table alias replaces the ____ with a ____ in order to simplify column identifications
table name; single letter (or more)
if a column name within the SELECT statement exists in more than one table within the FROM clause, the column bust be _____
further identified by table with the corresponding table alias
tables are improperly joined when there is ____
no join condition placed in the WHERE clause
when tables are improperly joined, a ____ is created
cartesian product
returns every row of data in one table joined to every row of data in the other tables
cartesian product
in cartesian product, the ____ returned is multiplied
number of rows
t or f. users may perform cartesian product on purpose
t
t or f. it doesn’t matter whether you list WHERE clause restrictions before or after join relationships
t
relationship between # of tables and # of join conditions
of tables is always 1 more than # of join conditions
5 types of joins
- cartesian
- equality
- self
- non-equality
- outer
join: useful when doing certain statistical calculations
cartesian join
another name for cartesian join
cross join
join: matches columns on the basis of equality
equality join
join: based on primary/foreign key relationships
equality join
3 other names for equality joins
- equijoin
- inner join
- simple join
join: data in one column of a table has a relationship with another column in the same table
self-join
in self-joins, it is necessary to link the table to itself in order to _____
match data
join: joins 2 or more tables based on a specified column value that doesn’t equal a specified column value in another table
non-equality joins
join: used to see rows that don’t meet the join condition
outer joins
outer join operator
(+)
(+) is used on the join condition in the ____ clause
WHERE
(+) can be placed on ____ of the WHERE clause but not ____
either side; both sides
- outer join rule: a WHERE condition using a column from the outer join table must also include ____
(+) operator
- outer join rule: a WHERE condition containing the (+) operator can’t be combined with another condition using this operator
OR
- outer join rule: this logical operator is usable in place of OR
AND
- outer join rule: a WHERE condition can’t use the ___ condition to compare a column marked with the (+) with an expression
IN comparison
- outer join rule: a WHERE condition can’t compare any column marked with the (+) operator with a ____
subquery
another name for non-equality join
non-equijoin
join: replicates each row from the first table with every row from the second table (displays every possible record combo)
cartesian join
join: creates join by using commonly named or defined columns
equality join
join: joins a table to itself
self-join
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)
non-equality join
join: includes records of a table in output when there’s no matching record in the other table
outer join
of alternate methods to perform equality joins
3
2 advantages of alternate equality joins
- WHERE clause is used only for row restriction conditions
2. improves script readability
alternate equality join that is most similar to traditional syntax
JOIN ON
alternate equality join that is rarely used due to potential for more than one column name matching between the tables
NATURAL JOIN
alternate equality join where table aliases aren’t required
JOIN USING
only possible alternate syntax for self-joins and non-equality joins
JOIN ON
disadvantage of using the outer join operator (+) on the deficient side of the join condition
can only be placed on one side of one side of one join condition
alternate outer join syntax: right table is deficient
LEFT OUTER JOIN
alternate outer join syntax: left table is deficient
RIGHT OUTER JOIN
2 advantage of alternate outer join
- can be used on both sides of join condition using FULL OUTER JOIN syntax
- multiple tables can be joined using differrent outer joins
disadvantage of alternate outer join
does not work when WHERE conditions on “deficient” table are used
join: provides the only means to show all rows from both tables whether a join condition is met or not
full outer join
cannot use _____ for full outer join
traditional syntax
functions that return one row of output for each record processed
single row functions
functions that return one result for each group of rows processed
group functions
2 other names for group functions
- multiple-row functions
2. aggregate functions
group function that returns the number of rows
COUNT
data type: COUNT
any
group function: sum of values in rows returned for a specific column
SUM
data type: SUM
NUMBER
group function: average value of rows returned for a specific column
AVG
data type: AVG
NUMBER
group function: max value from rows returned for a specific column
MAX
data type: MAX
any
group function: minimum value from rows returned for a specific column
MIN
data type: MIN
any
t or f. all group function ignore null values
t (one exception)
3 ways to use COUNT
- COUNT (*)
- COUNT (expr)
- COUNT (DISTINCT expr)
counts all rows that meet the condition including duplicates and nul values
COUNT (*)
returns number of non-null values in the column specified
COUNT (expr)
returns non-duplicate values in the column specified, including null values
DISTINCT
determines unique values within the column specified
DISTINCT
how many DISTINCTS can be used per SELECT clause?
only 1
DISTINCT must be listed on the ____
first column/expression
can only use DISTINCT on columns listed in the _____ for sorting
SELECT clause
returns the number of non-duplicate, non-null values in the column specified
COUNT (DISTINCT expr)
t or f. MAX and MIN don’t work with DATE and VARCHAR2 fields
f.
MAX DATE returns ____
recent
MIN DATE returns ____
old
MAX VARCHAR2 returns ____
z
MIN VARCHAR 2 returns ____
a
since group functions ignore null values, a ____ must be employed to force calculations to include null values
single-row functions
single-row function that changes the display of a null value, allowing group functions to display it
NVL
when to use JOIN USING
equality joins
when to use JOIN ON
everything else
used when dividing summary data into groups
GROUP BY clause
the column used in the GROUP BY clause doesn’t need to be included in the _____
SELECT clause
____ can’t be used in the GROUP BY clause
column aliases
group functions can only be nested to a depth of ___
2
t or f. multiple single row functions can be applied to nested group functions
t
why error: not a group by expression?
missing column
why error: not a single group function?
check group by
group functions can’t be executed within a _____ clause
WHERE
the WHERE clause is used to restrict ____, the HAVING clause is used to restrict ____
rows; groups
combines the results of two or more queries into a single result
set operator
4 set operators
- UNION
- UNION ALL
- INTERSECT
- MINUS
for set operators, each query must contain ____
the same number of columns
for set operators, columns between the queries must have ____
coordinating data types
for set operators, column names can be different between queries but the output will adopt the column names from ____
the first query
for set operators, only one ____ can be instituted at the end of set operation syntax
ORDER BY
for set operators, ____ results can’t be ordered separately
individual query
for set operators, ____ can’t be used
column aliases
t or f. set operators can be used in subqueries
t
set operators: returns results of both queries but removes duplicate records
UNION
set operators: similar to DISTINCT
UNION
set operators: returns results of both queries; includes duplicate records
UNION ALL
DISTINCT set operator
UNION
includes duplicates set operator
UNION ALL
set operators: returns only results appearing in both queries
INTERSECT
set operators: subtracts the second query’s results if they are also returned in the first query’s results
MINUS
set operators: opposite of intersect; does not show intersecting results
MINUS
a view is a ____ just like tables, sequences
database object
although views are database objects, they don’t actually store data – they store ____ to access data in underlying tables
a query
3-step view process:
- create view based on data in table(s)
- query the view instead of the table(s)
- the query stored in the view is executed
4 purposes of views
- Simplify issuing complex SQL queries
- Restrict users’ access to sensitive data
- Perform calculations on columns
- Portray data in different ways
t or f. if you use a column alias, for views, you cannot use the regular column name in a query
t
