Study your flashcards anywhere!

Download the official Cram app for free >

  • Shuffle
    Toggle On
    Toggle Off
  • Alphabetize
    Toggle On
    Toggle Off
  • Front First
    Toggle On
    Toggle Off
  • Both Sides
    Toggle On
    Toggle Off
  • Read
    Toggle On
    Toggle Off
Reading...
Front

How to study your flashcards.

Right/Left arrow keys: Navigate between flashcards.right arrow keyleft arrow key

Up/Down arrow keys: Flip the card between the front and back.down keyup key

H key: Show hint (3rd side).h key

A key: Read text to speech.a key

image

Play button

image

Play button

image

Progress

1/77

Click to flip

77 Cards in this Set

  • Front
  • Back
What type of condition is used to retrive data from multiple tables?
a join condition
What are the four common types of join conditions?
equijoin
nonequijoin
outer join
self join
When to use the join condition
Use the join condition to retrive data from tables with commom values that corespond directly to each other
When to use a nonequijoin condition.
to display data when values indirectly correspond

BETWEEN > >= < <=
When to use a outer join condition?
to ensure all rows are returned, including rows that do not satisfy a join condition
When to use a self join condition
to link values in different columns within a single table using a join condition
What is a cartesian product.
display data from multiple tables without valid join condition
What are other names for equijoins?
Simple joins or inner joins
what is equijoin syntax?
SELECT table1.column, table2.column
From table1, table2
WHERE table1.column = table2.column;
What is an advantage to always prefixing a column name with a table name
ie. table1.column
It improves performance of the query.
What is the common operator in a join statement that further restricts retrived data?
AND operator in the WHERE clause
What is a way to clarify WHERE clauses with multiple search conditions?
Use parentheses around AND and OR clauses to clarify statements
what is the syntax for table aliases?
SELECT alias1.column, alias2.column
FROM table1 alias1, table2 alias2
WHERE alias1.column = alias2.column;
What must happen when a table alias is introduced in the from clause?
the table alias must replace the table name through out the select statement.
For data in more than two tables what can be done to view the data?
equijions can be used to view multiple table data if at least one column in the tables corresponds to another column in another table.
What is the common operator in a join statement that further restricts retrived data?
AND operator in the WHERE clause
What is a way to clarify WHERE clauses with multiple search conditions?
Use parentheses around AND and OR clauses to clarify statements
what is the syntax for table aliases?
SELECT alias1.column, alias2.column
FROM table1 alias1, table2 alias2
WHERE alias1.column = alias2.column;
What must happen when a table alias is introduced in the from clause?
the table alias must replace the table name through out the select statement.
For data in more than two tables what can be done to view the data?
equijions can be used to view multiple table data if at least one column in the tables corresponds to another column in another table.
What are the WHERE conditions for joining together more than two tables?
Ntables-1 = # of AND clauses

FROM table1, table2, table3
WHERE table1.column=table2.column
AND
table2.column=table3.column
What can be used to join tables that do not have columns that directly correspond to each other?
nonequijoin this condition usually uses the BETWEEN operator
What is the syntax for an out join condition
The out join operator is (+)
WHERE table1.column (+) = table2.column
(+) can not be on both sides
what join would be used to view a table twice so that different data could be found.
use a self join
What is a benefit of using a self join clause regarding column names?
If a table does not include a column name then the name can be added using a self join clause. Therefore view the table as if it was two separate tables.
What are the 5 most common SQL 1999 joins?
Cross join
Natural join
join with the USING clause
Join with the ON clause
Outer join
In sql 1999, what does a cross join do?
combines all rows from all tables in a cartesian plane.

rarely useful same as omitting the join clause using Oracle syntax
In SQL 1999, what does a natural join do?
a natural join is based on columns in two tables that have the same names and date types. like an equijoin

selects rows from the columns that are common to the two tables.
how can the USING clause be used in a natural join?
to specify a particular column to use for an equijoin between to tables
How is the ON clause used to specify a join condition for joining two tables?
The clause separates the join condition from the other filter conditions in the WHERE clause of a SELECT statement.
How is an OUTER join clause used?
it is used to return all rows that satisfy the join condition as well as any unmatched rows from a second table.
What are the three types of SQL 1999 outer joins?
left outer join
right outer join
full outer join
What does a natural join enable?
the display of data from two tables when a value in one column of one table corresponds directly to a value in another column in the second table.
What is the natural join syntax
SELECT column, column
From table1 NATURAL JOIN table2;
Can the NATURAL JOIN keywords be used with the using clause when joining tables?
No, they are mutually exclusive
What is the syntax for the JOIN and USING clause?
SELECT Column, column
FROM table1 JOIN table2 USING (column_name)
True or False When using a join statement with a using clause table ID and Aliases can be used to reference columns
False
When using a USING clause table name or alias cannot be used.
What is the syntax for joining tables with the ON clause?
SELECT table1.column, table2.column
FROM table1 JOIN table2
ON table1.column_name = table2.column_name
What is the natural join syntax
SELECT column, column
From table1 NATURAL JOIN table2;
Can the NATURAL JOIN keywords be used with the using clause when joining tables?
No, they are mutually exclusive
What is the syntax for the JOIN and USING clause?
SELECT Column, column
FROM table1 JOIN table2 USING (column_name)
True or False When using a join statement with a using clause table ID and Aliases can be used to reference columns
False
When using a USING clause table name or alias cannot be used.
What is the syntax for joining tables with the ON clause?
SELECT table1.column, table2.column
FROM table1 JOIN table2
ON table1.column_name = table2.column_name
What is the natural join syntax
SELECT column, column
From table1 NATURAL JOIN table2;
Can the NATURAL JOIN keywords be used with the using clause when joining tables?
No, they are mutually exclusive
What is the syntax for the JOIN and USING clause?
SELECT Column, column
FROM table1 JOIN table2 USING (column_name)
True or False When using a join statement with a using clause table ID and Aliases can be used to reference columns
False
When using a USING clause table name or alias cannot be used.
What is the syntax for joining tables with the ON clause?
SELECT table1.column, table2.column
FROM table1 JOIN table2
ON table1.column_name = table2.column_name
WHAT is the syntax for an OUTER JOIN clause?
FROM table1
RIGHT OUTER JOIN
LEFT OUTER JOIN
FULL OUTER JOIN
table2
ON table1.column_name = table2.column_name
WHAT are SET operators used for?
SET operators are used to combine two or more queries into one result. These are know as compound queries
What is a UNION and how is it different than UNION ALL
UNION returns all rows from both queries after removing duplicates. UNION ALL returns all with duplicates
SELECT
FROM
UNION (ALL)
SELCT FROM
What is an INTERSECT
returns all rows common to both queries
SELECT
FROM
INTERSECT
SELECT
FROM
WHAT is a MINUS
Use it to return rows from the first query not in the second query or anything from the second query.
SELECT
FROM
MINUS
SELECT
FROM
What is the syntax of a group function?
SELECT
group_function(argument)
FROM table_name

Argument = cloumn_name or expression or constant
What is the meaning of adding ALL and DISTINCT to a group function
SELECT group_function(DISTINCT column_name) will not return any duplicates.
group_function(ALL column_name)will return all rows and is the default.
What are the 7 types of group functions?
AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE
True or False the count function ignores null values when looking at a column or expression?
True
What is the only group function that does not ignore null values when counting rows?
SELECT COUNT(*)
What is the diffeence between these to SELECT statements?
AVG(comm_pct)
AVG(NVL(comm_pct,0))
The first statement does not include null values and the second statement replaces all null values with 0 for the calculation.
What is te group by clause used for.
It is used to organize rows in a table into groups.
What is the syntax for a GROUP BY clause
It is place between the WHERE and ORDER BY clauses. The SELECT clause can only contain columns that appear in the group clause as well as any group functions

column aliases not allowed
When using a GROUP BY clause does the column specified have to also be in the slect clause?
NO
SELECT AVG(SALARY)
FROM employees
GROUP BY department_id;
What does the HAVING clause do?
It restrict rows from being retrived similar to the WHERE clause.
True or False You can use a HAVING clause without a GROUP BY clause.
False The HAVING clause must accompany the GROUP BY clause

aliases are also not allowed
Is it possible to use use group functions in the HAVING clause not used in the SELECT clause?
YES
SELECT AVG(salary)
FROM employees
GROUP BY department
HAVING MAX(salary) > 1000;
What is a comparision of the HAVING and WHERE clauses?
Having clause restrict groups and WHERE clause restrict rows
What is max depth for nesting group functions?
2 inner function evaluated before outer function
SELECT MAX(AVG(salary))
What is the syntax of the ROLLUP operator.
GROUP BY ROLLUP(group_column)
What does the ROLLUP operator do?
GROUP BY with ROLLUP specifies the subtotals for each "group by column" and then returns a grand total for the "group by column" data
What does Superagregate rows mean?
These are the sub total rows from using the ROLLUP operator.
What does the CUBE clause do?
It produces subtotals for all possible combinations of the grouping specified in the GROUP BY clause
SELECT...FROM...WHERE....
GROUP BY CUBE
What are the aggregate functions that can be used with CUBE and ROLLUP?
SUM
AVG
MAX
MIN
COUNT
What is the syntax for the CUBE operator?
GROUP BY CUBE(grouping columns)
Cross-Tabulation rows are rows usually produced by what GROUP BY operator?
CUBE
What are the queries that can have subqueries?
SELECT
WHERE
HAVING
What are the five requirements for subqueries?
1 enclose in parentheses
2 place query on right side of comparison operator
3 can not have ORDER BY inside
4 always use single row operators with single row sub-queries
5 Be aware of NULL values
WHAT are the three types of subqueries?
single-row subqueries
multiple row subqueries
Multiple column subqueries