| |
|
|
|
Da Notes -
70-029 - J
Personal notes on the MCDBA tests. These notes are from the various resources I used to pass the MCDBA exams including the MCDBA sample exams from Transcender. |
Passing Score: 693, Questions: 49, Time: 150 Min.
|
Da Notes Index
|
|
|
JOINS
|
CROSS JOIN (rarely used unless you want test data created - may be on exam)
- All rows in the left table are matched with all rows in the right table. The result of a Cross join is a Cartesian product that returns all possible combinations of rows from every table that is involved in the
join(s) operations.
Total Rows returned = number of rows in the first table * the number of rows in the second.
A CROSS JOIN with a WHERE clause works like an INNER JOIN. The where clause is executed after the join clause which might not give you the results that you want. For example if you want to return all the rows from one table with a left join and use a where clause, this will limit your result set to only show the rows that match the criteria of the where clause.
INNER JOIN same as JOIN - SQL Server default. Specify columns to display in select list. Use a where clause to restrict result set. Avoid null in join because null does not evaluate equally with one another.
ORDER BY Clasue - must use since SQL 7.0 does NOT guarantee any order
SELF JOIN same as JOIN to the same database (rarely used but may be on test)
E.g. a manager is also an employee in the EMPLOYEE table with a column for manage id. Or a child in a genealogy PERSON table could have columns for the mother and father which has foreign keys to the same PERSON table. In a select statement to query the table you would use table aliases. Each row matches itself and pairs are repeated - use a where clause to eliminate duplicates.
OUTER LEFT JOIN or OUTER RIGHT JOIN same as LEFT JOIN or RIGHT JOIN - Returns all unique rows from at least one of the tables or views that are specified in the From clause. Avoid null in join because null does not evaluate equally with one another.
|
JUNCTION
TABLE
|
A third table used to allow many to many relationships by using the junction table and two One-to-many relationships to it.
|
|
|
|
