What is a Join?
A relational operation that causes two or more tables with a common domain to be combined into a single table or view.
What is Equi-join?
A join in which the joining condition is based on equality between values in the common columns; common columns appear redundantly in the result table
What is Natural join?
An equi-join in which one of the duplicate columns is eliminated in the result table.
What are usually the common columns in joined tables?
Usually the primary key of the dominant table and the foreign key of the dependent table in 1:M relationships.
What is Outer join?
A join in which rows that do not have matching values in common columns are nonetheless included in the result table (as opposed to inner join, in which rows must have matching values in order to appear in the result table)
What is Union join?
Includes all data from each table that was joined
Example of different join types
What is a subquery?
Placing an inner query (SELECT statement) inside an outer query
What are the options for using a subquery?
- In a conditon of the WHERE clause
- As a “table” of the FROM clause
- Within the HAVING clause
What are the two options for a subquerie?
Noncorrelated
Correlated
What is a noncorrelated subquerie?
Executed once for the entire outer query
What is a correlated subquerie?
Executed once for each row returned by the outer query
What are the traits of a noncorrelated subquerie?
- Do not depend on data from the outer query
- Execute once for the entire outer query
What are the traits of a correlated subquerie?
- Make use of data from the outer query
- Execute once for each row of the outer query
- Can use the EXISTS operator
What is a union querie?
Combine the output (union of multiple queries) together into a single result table
Why would it be useful to combine queries, through the use of Views?
Because production databases contain hundreds or even thousands of tables, and tables could include hundreds of columns.
What can you do if you use a view (which is a query)?
You could have another query that uses the view as if it were a table.
What are some tips for developing queries?
- Be familiar with the data model (entities and relationships)
- Understand the desired results
- Know the attributes desired in the results
- Identify the entities that contain desired attributes
- Review ERD
- Construct a WHERE equality for each link
- Fine tune with GROUP BY and HAVING clauses if needed
- Consider the effect on unusual data
What are some considerations for Query efficiency?
- Instead of SELECT *, identify the specific attributes in the SELECT clause; this helps reduce network traffic of result set
- Limit the number of subqueries; try to make everything done in a single query if possible
- If data is to be used many times, make a separate query and store it as a view
What are some guidelines for better query design?
- Understand how indexes are used in query processing
- Keep optimizer statistics up-to-date
- Use compatible data types for fields and literals
- Write simple queries
- Break complex queries into multiple simple parts
- Don’t nest one query inside another query
- Don’t combine a query with itself (if possible avoid self-joins)
What are some more guidelines for better query design?
- Create temporary tables for groups of queries
- Combine update operations
- Retrieve only the data you need
- Don’t have the DBMS sort without an index
- Learn!
- Consider the total query processing time for ad hoc queries
What is transaction?
A discrete unit of work that must be completely processed or not processed at all
- May involve multiple updates
- If any update fails, then all other updates must be cancelled
What are the SQL commands for transactions?
- BEGIN TRANSACTION/END TRANSACTION (Marks boundaries of a transaction)
- COMMIT (Makes all updates permanent)
- ROLLBACK (Cancels updates since the last COMMIT)
What are data dictionary facilities?
- System tables that store metadata
- Users usually can view some of these tables
- Users are restricted from updating them
