275 SQL Flashcards
A full outer join includes rows that satisfy the join condition, plus
Rows in both tables that don’t satisfy the join condition
When applying multiple join conditions (i.e. more than one condition which must be true) to an inner join in explicit syntax, the extra conditions can be applied:
In the WHERE clause and/or the ON section
The basic format for an inner join using explicit syntax is:
SELECT … FROM table1 JOIN table2 ON join_condition
In most cases, the join condition of an inner join compares the primary key of one table to the ____________________ key of another table.
foreign
When performing an inner join using implicit join syntax, you should place the join condition(s):
In the WHERE clause
In a cross join, all of the rows in the first table are joined with…
all rows in the second table
SELECT v.VendorName AS Vendor, i.InvDate AS Date
FROM Vendors v
JOIN Invoices i ON v.VendorID = i.VendorID;
In this example, “i” is known as…
a table alias
Correlation names (aka table aliases) can be specified with the option AS keyword to temporarily re-name a table within the scope of the query. When/how is the best practice for using correlation names?
When they simplify or clarify the query.
Queries combined with UNION must use the same tables to avoid an error.
False
SELECT VendorName, InvoiceNumber
FROM Invoices i
RIGHT JOIN Vendors v ON i.VendorID = v.VendorID;
If the RIGHT keyword is replaced with the LEFT keyword, the query…
keeps unmatched rows in the Invoices tab
Specify the correct order in which each of the six SQL clauses must be placed:
select> from> where> group by> having> order by
What is the ROLLUP operator used for in the GROUP BY clause?
To add a summary total for each grouping
A select statement that includes aggregate functions is often called_____ query
summary
expressions coded in the where clause…`
can use non-aggregate search conditions but can’t use aggregate search conditions
Which of the following is a valid aggregate function in SQL? Check only those that apply.
min, avg, sum, count
A logical condition comparing against an aggregate function (e.g. COUNT) can be used in:
Only the HAVING clause
The GROUP BY clause can contain more than one value (separated by commas). In the query below, what does SUM(Salary) represent?
SELECT Department,
State,
SUM(Salary)
FROM EMPLOYEES
GROUP BY Department, State;
The salary budget for each combination of department and state
The WHERE clause and the HAVING clause can both limit rows in the result set based on logical conditions. What is the difference between the two clauses?
HAVING can make comparisons against aggregate functions
When applying DISTINCT to the value being calculated in an aggregate function like COUNT, what is the effect?
SELECT COUNT(DISTINCT StreetName)
FROM ADDRESSES;
To count only unique values
Multiple conditions (connected by logical AND or OR) can only be used in the WHERE clause; they can never be used in the HAVING clause.
False
A subquery is a/an ______________ statement that’s coded within another SQL statement.
SELECT
WHERE clause comparisons against a subquery can use comparison operators (e.g. + > < >= <= etc.). The ANY and SOME keywords can be applied to this type of comparison to match part of the returned set of values. What is the functional difference between ANY and SOME?
None of the above; they are equivalent
When using a comparison operator (e.g. = > < >= <= etc.) against a subquery, the subquery must return a single value (one row, one column); otherwise an error will result. What is one method to make this type of comparison and avoid the error?
Write the subquery using an aggregate calculation
Which of the following are true about correlated subqueries when used in WHERE clause conditions? Check only those that apply:
When using the same table as the outer query, they require the use of an alias
They refer to a value provided by a column in the outer query
They are executed once for each row processed by the outer query
