Web.nmsu.edu | New Mexico State University - All About ...
SQL Advanced[1] Features
Prepared by Jennifer Kreie, Associate Professor
Accounting & Information Systems Department
College of Business, New Mexico State University
Databases used in this SQL series:
• Human Resources – This is a pre-existing database provided by Oracle when you install Oracle XE.
• Student-Teams – This database was created in the SQL Fundamentals series. The DIVISION lesson adds two tables to this database and includes the data.
Presentation topics:
1 /6 SQL Advanced – Equi-join, one-sided outer join, full outer join, IS NULL. Assignment included.
2 /6 SQL Advanced – Nested queries: Type I subquery. Assignment included. Assignment included.
3 /6 SQL Advanced – Nested queries: Type II subquery (correlated) and example of a DIFFERENCE problem. Assignment included.
4 /6 SQL Advanced – In-line query in the FROM clause. Assignment included.
5 /6 SQL Advanced – Nested aggregates. Assignment included.
6 /6 SQL Advanced – Division problems. Assignment included.
Outline of topics covered in each lesson.
SQL Advanced – Equi-join, one-sided outer join, full outer join, IS NULL.
• Equi-join
• One-sided outer join
o Left
o Right
• Full outer join
• One-sided outer join with IS NULL
• Plus notation – one-sided outer joins in the WHERE clause.
• DESC used to reverse the sorted order in the ORDER BY clause.
SQL Advanced – Nested queries: Type I subquery. Assignment included.
• Type I subquery in the WHERE clause.
o The IN/NOT IN operator acts like a join between the outer query and the subquery.
o The inner query doesn’t reference the outer query.
o Do data is displayed directly from the subquery.
• Using UPPER() and LOWER() functions to evaluate text in mixed case.
• Deleting data using the Type I query.
• Use ROLLBACK to undo something that was just done.
• A Type I subquery in the HAVING clause.
SQL Advanced – Nested queries: Type II subquery (correlated) and example of a DIFFERENCE problem.
• Type II (correlated) subquery.
• Example of Type I and Type II solutions.
• Solving a “difference” problem with a Type II subquery.
SQL Advanced – In-line query in the FROM clause.
• In-line subquery
o Use in decomposing a problem and writing queries for each subquery. This can also help in debugging or troubleshooting a query.
SQL Advanced – Nested aggregates.
• Nested aggregate queries
• When do you need a nested aggregate?
o Whenever you find yourself trying to do a function within a function, such as an average of a sum, you probably need a nested aggregate query.
SQL Advanced – Division problems.
• Division-type problems
• No DIVISION operator
• Work-around for answering a “division” question.
-----------------------
[1] You’re welcome to use this material but please provide a reference to this source.
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.