Unit 4 Flashcards
function is used to tally the number of non-null values of an attribute.
COUNT
Highest (maximum) value in the table.
MAX
Lowest (minimum) value in the table.
MIN
function computes the total sum for any specified attribute, using whatever condition(s) you have imposed.
SUM
Gets the average value of a numeric column.
AVG
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
GROUP BY
clause operates very much like the WHERE clause in the SELECT statement.
HAVING
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.
View
The join condition is generally composed of an equality comparison between the foreign key and the primary key of related tables.
Joining Tables
may be used to identify the source table from which the data are taken.
Alias
An alias is especially useful when a table must be joined to itself in a recursive query.
Recursive Joins
basically means that only those rows where the values are common between the two tables will be retrieved.
Inner Join
Returns all rows from the left table
Left Join
Returns all rows from the right table
Right join
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.
UNION
Will keep all the rows from the given queries including the duplicates
UNION ALL
the ——– statement can be used to combine rows from two queries, returning only the rows that appear in both sets.
INTERSECT
performs a relational product (also known as the Cartesian product) of two tables.
CROSS JOIN
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.)
JOIN ON
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.
OUTER JOIN
When you want to compare a single attribute to a list of values
IN subqueries
is a subquery that executes once for each row in the outer query.
Correlated Subqueries
