Oracle Pro Tips, Trends & Technology eXTRA Pinnacle Publishing http://www.pinnaclepublishing.com Issue 3.9 June 12, 2002 TABLE OF CONTENTS 1) Pop Quiz (answer at the end) 2) Did You Know... 3) Books 4) Oracle News 5) SQL Resources 6) Answer to the Pop Quiz --------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- The quiz is based on queries against the "emp" table, which is created and populated using the following SQL: SQL> create table emp (emp_id number not null, name varchar2(20) not null, bdate date); SQL> insert into emp values (1, 'John', add_months(trunc(sysdate), -12*30)); SQL> insert into emp values (2, 'Mary', null); SQL> insert into emp values (3, 'Sue', add_months(trunc(sysdate), -12*28)); SQL> insert into emp values (4, 'Mark', add_months(trunc(sysdate), -12*28)); SQL> commit; SQL> select * from emp; EMP_ID NAME BDATE ---------- -------------------- ----------- 1 John 11-JUN-1972 2 Mary 3 Sue 11-JUN-1974 4 Mark 11-JUN-1974 What is the result of the following SQL queries: a. select count(*) from emp; b. select count(0) from emp; c. select count(bdate) from emp; d. select count(distinct bdate) from emp; e. select count(*) from emp where bdate < sysdate; --------------------------------------------------------- 2) DID YOU KNOW... --------------------------------------------------------- * Oracle is holding the Oracle World conference in Beijing from June 11 to 14? If you find the plane tickets too expensive, not to mention the hefty admission fee to the conference, you can always get a Webcast of the events there. Check them out at: http://www.oracle.com/oracleworld/online/beijing/index.html ********************************************************* OpenX V2.0 Add database development and connectivity to your applications. OpenX is a multi-level software component for developing multi-tier enterprise applications that require access to RDBMS locally or across networks. It works with SQL Server, Oracle, Sybase, ODBC, and more. Its low-level interface offers data access optimizations and access to database-specific features. It provides small but multifunctional command set compatible with most programming languages. http://www.componentsource.com/product.asp?PO=511475&option=&MTC=XXX ********************************************************* --------------------------------------------------------- 3) BOOKS --------------------------------------------------------- O'Reilly has released a new book that aims to help Oracle DBAs "write better SQL queries." The book is titled MASTERING ORACLE SQL, and is written by Sanjay Mishra and Alan Beaulieu. It strives to cover the advanced SQL topics as well as the basic concepts. The following features are covered: the use of DECODE and CASE statements; SQL for partitions, objects, and collections such as nested tables and variable arrays; ANSI-compliant joins; analytical functions, etc. If you're interested in the book, you can read a free chapter at: http://www.oreilly.com/catalog/mastorasql/chapter/ch07.html --------------------------------------------------------- 4) ORACLE NEWS --------------------------------------------------------- Oracle recently denied a report that said it would lay off as many as 600 more staff members. According to analysts at Credit Suisse First Boston, Oracle is cutting 1 to 2 percent of its work force, out of a worldwide total of 42,000. Find out more at the following URL: http://biz.yahoo.com/rt/020607/tech_oracle_3.html * * * * Speaking of lay offs, there's a report in USA Today that companies in Silicon Valley are demanding employees take time off to reduce costs. "In addition to closing the week between Christmas and New Year's, which has long been done by many Valley firms, more companies are now closing the week of July 4th and are considering shutdowns around other holidays, such as Labor Day and Thanksgiving." We're not talking about tech startups, but large Silicon Valley companies such as Silicon Graphics, Sun, and Adobe. Find out more at: http://www.usatoday.com/money/tech/2002-06-06-forced-vacations.htm * * * * Microsoft is pushing its .NET technology on all fronts. While researching the Oracle9i product suite, I became interested in how/if Oracle would support this technology. Interestingly, I found a number of articles on Oracle's Web site on how Oracle's Java-based solution was superior to .NET. In any case, they make pretty interesting materials: http://www.oracle.com/features/9i/index.html?t1db_arch.html http://www.oracle.com/features/9i/index.html?0514_db_orclvsmsft.html --------------------------------------------------------- 5) SQL RESOURCES --------------------------------------------------------- There's an Oracle tutorial hosted by the University of California, Davis: http://www.db.cs.ucdavis.edu/teaching/sqltutorial/ Stanford University also has a number of resources on database systems in general and Oracle in particular. Check them out at: http://www-db.stanford.edu/~ullman/fcdb/oracle/or-plsql.html http://www-db.stanford.edu/~ullman/dbbooknews.html --------------------------------------------------------- 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- The COUNT (*) function simply returns the number of rows in the table. SQL> select count(*) from emp; COUNT(*) ---------- 4 So does COUNT (0). SQL> select count(0) from emp; COUNT(0) ---------- 4 The COUNT () function returns the number of rows in which the specified column has a non-null value. SQL> select count(bdate) from emp; COUNT(BDATE) ------------ 3 The COUNT () function returns the number of unique non-null values in the specified column. SQL> select count(distinct bdate) from emp; COUNT(DISTINCTBDATE) -------------------- 2 In the WHERE clause, the operator always evaluates NULL value to false (unless you explicitly include the IS NULL or IS NOT NULL operator). SQL> select count(*) from emp where bdate < sysdate; COUNT(*) ---------- 3 --------------------------------------------------------- Well, that's it for this week. I welcome your feedback, input, tips, suggestions, Web sites, and other Oracle- related news. If you send me something, please let me know whether I can use your name with your comments. I apologize in advance if I don't respond personally to each of your questions or suggestions, but I'll get to as many as I can in the eNewsletter if not personally. Garry Chan, Editor Database Architect mailto:GChan@ProcaseConsulting.com This eNewsletter is brought to you compliments of Pinnacle Publishing, Inc. Copyright(c) 2002 http://www.pinnaclepublishing.com All rights reserved.