Mastering Oracle SQL, 2nd Edition

Mastering Oracle SQL, 2nd Edition

Sanjay Mishra, Alan Beaulieu

Language: English

Pages: 496

ISBN: 0596006322

Format: PDF / Kindle (mobi) / ePub

The vast majority of Oracle SQL books discuss some syntax, provide the barest rudiments of using Oracle SQL, and perhaps include a few simple examples. It might be enough to pass a survey course, or give you some buzz words to drop in conversation with real Oracle DBAs. But if you use Oracle SQL on a regular basis, you want much more. You want to access the full power of SQL to write queries in an Oracle environment. You want a solid understanding of what's possible with Oracle SQL, creative techniques for writing effective and accurate queries, and the practical, hands-on information that leads to true mastery of the language. Simply put, you want useful, expert best practices that can be put to work immediately, not just non-vendor specific overview or theory.Updated to cover the latest version of Oracle, Oracle 10g, this edition of the highly regarded Mastering Oracle SQL has a stronger focus on technique and on Oracle's implementation of SQL than any other book on the market. It covers Oracle s vast library of built-in functions, the full range of Oracle SQL query-writing features, regular expression support, new aggregate and analytic functions, subqueries in the SELECT and WITH clauses, multiset union operators, enhanced support for hierarchical queries: leaf and loop detection, and the CONNECT_BY_ROOT operator, new partitioning methods (some introduced in Oracle9i Release 2), and the native XML datatype, XMLType.Mastering Oracle SQL, 2nd Edition fills the gap between the sometimes spotty vendor documentation, and other books on SQL that just don't explore the full depth of what is possible with Oracle-specific SQL. For those who want to harness the untapped (and often overlooked) power of Oracle SQL, this essential guide for putting Oracle SQL to work will prove invaluable.

Todd Lammle's CCNA/CCENT IOS Commands Survival Guide: Exams 100-101, 200-101, and 200-120

Probabilistics Search for Tracking Targets: Theory and Modern Applications

Transactional Information Systems - Theory, Algorithms, and the Practice of Concurrency Control and Recovery (Morgan Kaufmann Series in Data Management Systems)

International Encyclopedia of Systems and Cybernetics

Practical Maya Programming with Python

Windows Server 2012 R2 Administrator Cookbook



















ROLLUP (r. name)); YEAR MONTH REGION TOTAL ----- --------- -------------------- ---------- 2000 10042570 2001 5021285 February 4988535 January 4496799 March 5578521 Mid-Atlantic 5029212 New England 5074332 Southeast US 4960311 15063855 9 rows selected.In this example, the DISTINCT keyword eliminated the duplicate grand-total rows. You can also eliminate duplicate rows by using the GROUP_ID function, as discussed later in this chapter. If you are interested in subtotals and totals on composite

10042570 3 2 2001 5021285 3 2 January 4496799 5 2 March 5578521 5 2 February 4988535 5 2 New England 5074332 6 2 Mid-Atlantic 5029212 6 2 Southeast US 4960311 6 2 15063855 7 3In this output, the aggegate rows for individual dimensions, region, month, and year are shown first. These are followed by the aggregate rows for two dimensions: month and region, year and region, and year and month, respectively. The last row is the one aggregated over all three dimensions. GROUP_ID As you saw in

release. Using the WITH clause, you can place the subquery that calculates the average salary at the top of the query and reference it throughout the query: WITH avg_sal AS (SELECT AVG(salary) val FROM employee) SELECT e.emp_id, e.lname, e.fname, (SELECT ROUND(e.salary - val) FROM avg_sal) above_avg FROM employee e WHERE e.salary > (SELECT val FROM avg_sal); EMP_ID LNAME FNAME ABOVE_AVG ---------- -------------------- -------------------- ---------- 7698 BLAKE MARION 846 7782 CLARK CAROL 446

problems go away beginning with the Oracle9i Database release. Rules and Restrictions on Set Operations Other than the union compatibility conditions discussed at the beginning of the chapter, there are some other rules and restrictions that apply to the set operations. These rules and restrictions are described in this section. Column names for the result set are derived from the first SELECT: SELECT cust_nbr "Customer ID", name "Customer Name" FROM customer WHERE region_id = 5 UNION SELECT

rows selected.For better readability and maintainability of your queries, we recommend that you explicitly use identical column aliases in all the component queries, and then use these column aliases in the ORDER BY clause. Tip Unlike ORDER BY, you can use GROUP BY and HAVING clauses in component queries. The following list summarizes some simple rules, restrictions, and notes that don't require examples: Set operations are not permitted on columns of type BLOB, CLOB, BFILE, and VARRAY, nor

Download sample