Unit 4

Question 1

function is used to tally the number of non-null values of an attribute.

Answer 1

COUNT

Question 2

Highest (maximum) value in the table.

Answer 2

MAX

Question 3

Lowest (minimum) value in the table.

Answer 3

MIN

Question 4

function computes the total sum for any specified attribute, using whatever condition(s) you have imposed.

Answer 4

SUM

Question 5

Gets the average value of a numeric column.

Answer 5

AVG

Question 6

clause is generally used when you have attribute columns combined with aggregate functions in the SELECT statement. The —— clause is valid only when used in conjunction with one of the SQL aggregate functions

Answer 6

GROUP BY

Question 7

clause operates very much like the WHERE clause in the SELECT statement.

Answer 7

HAVING

Question 8

a virtual table based on a SELECT query. The query can contain columns, computed columns, aliases, and aggregate functions from one or more tables. The tables on which the view is based are called base tables.

Answer 8

View

Question 9

The join condition is generally composed of an equality comparison between the foreign key and the primary key of related tables.

Answer 9

Joining Tables

Question 10

may be used to identify the source table from which the data are taken.

Answer 10

Alias

Question 11

An alias is especially useful when a table must be joined to itself in a recursive query.

Answer 11

Recursive Joins

Question 12

basically means that only those rows where the values are common between the two tables will be retrieved.

Answer 12

Inner Join

Question 13

Returns all rows from the left table

Answer 13

Left Join

Question 13

Returns all rows from the right table

Answer 13

Right join

Question 13

Perfect tool for combining a common list of data – one that excludes duplicate records. The ——-statement combines rows from two or more queries without including duplicate rows.

Answer 13

UNION

Question 14

Will keep all the rows from the given queries including the duplicates

Answer 14

UNION ALL

Question 15

the ——– statement can be used to combine rows from two queries, returning only the rows that appear in both sets.

Answer 15

INTERSECT

Question 16

performs a relational product (also known as the Cartesian product) of two tables.

Answer 16

CROSS JOIN

Question 17

Another way to express a join when the tables have no common attribute names is to use the ——- operand. That query will return only the rows that meet the indicated join condition. The join condition will typically include an equality comparison expression of two columns. (The columns may or may not share the same name but, obviously, must have comparable data types.)

Answer 17

JOIN ON

Question 18

An ——— returns not only the rows matching the join condition (that is, rows with matching values in the common columns) but also the rows with unmatched values.

Answer 18

OUTER JOIN

Question 19

When you want to compare a single attribute to a list of values

Answer 19

IN subqueries

Question 20

is a subquery that executes once for each row in the outer query.

Answer 20

Correlated Subqueries