March 5, 2009

Yesterday, I passed theOracle SQL Expert Exam. After 4 full years of working with Oracle, it took 2.5 weeks to study for this exam. I have studied from the SQL Fundementals 1-2 course material from Oracle University for focusing the exam topics and I used Oracle official documentation to support what I have learned from the course material. During my studies, I can say I learned a lot of small things and sometimes I felt shamed about not knowing the things that I should have learned as a junior DBA. At the end of the day, I can easily say that I can not be a SQL expert before writing that much SQL code as a database developer nevertheless, I am glad that, I spent time on learning new things by the help of certification. After this brief history, lets talk about something technical. (Warning!!! Post will be long and if you are already good with sql, just review the headers to se if there is something new for you. )

1- q Operator for using character literals with qoutation marks I never heard about it before. Document says its good for readibility but I am not sure 🙂 Usage is simple if you want to say “coskan’s” all you need to do is put it in the braclets q'[]’ or q'{}’ or q'()’ or q'<>’

8- Coalesce FunctionDocument says; COALESCE returns the first non-null expr in the expression list. You must specify at least two expressions. If all occurrences of expr evaluate to null, then the function returns null.

10-Natural Join
Despite coming from a SQL Server DBA background with computer engineering diploma I never heard about natural joins or I heard at university but never cared about it ….Document says: A natural join is based on all columns in the two tables that have the same name. It selects rows from the two tables that have equal values in the relevant columns. When specifying columns that are involved in the natural join, do not qualify the column name with a table name or table alias.

11-When joining with JOIN…USING syntax table aliases are not allowed for the joining column

SQL> ---WRONG
SQL> select e.first_name,e.department_id
2 from employees e join departments d
3 using (department_id)
4 where d.department_id=10 and rownum<2;
where d.department_id=10 and rownum<2
*
ERROR at line 4:
ORA-25154: column part of USING clause cannot have qualifier
SQL> ---WRONG after removing from where
SQL> select e.first_name,e.department_id
2 from employees e join departments d
3 using (department_id)
4 where department_id=10 and rownum<2;
select e.first_name,e.department_id
*
ERROR at line 1:
ORA-25154: column part of USING clause cannot have qualifier
SQL> --RIGHT
SQL> select e.first_name,department_id
2 from employees e join departments d
3 using (department_id)
4 where department_id=10 and rownum<2;
FIRST_NAME DEPARTMENT_ID
-------------------- -------------
Jennifer 10
&#91;/sourcecode&#93;
<strong>12-ANY/ALL operator for subqueries </strong>
My face is going red for not using these two before. I think I never need them 🙂
<strong><a href="http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/conditions002.htm#sthref2792" target="_blank">Document </a></strong> says same thin for both of them;
Compares a value to each value in a list or returned by a query. Must be preceded by =, !=, &gt;, &lt;, =. Can be followed by any expression or subquery that returns one or more values.
The only difference is ANY Evaluates to FALSE if the query returns no rows. ALL Evaluates to TRUE if the query returns no rows.
I won't go deep into but if you not already know them its good practice to play with them
SQL> ---ANY
SQL> SELECT employee_id, last_name, job_id, salary
2 FROM employees
3 WHERE salary < ANY
4 (SELECT salary
5 FROM employees
6 WHERE job_id = 'ST_MAN')
7 AND job_id <> 'ST_MAN' and rownum<2;
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
132 Olson ST_CLERK 2100
SQL>
SQL> ----USING ANY WITH NOT CHECK THE SYNTAX
SQL> SELECT employee_id, last_name, job_id, salary
2 FROM employees
3 WHERE NOT salary < ANY
4 (SELECT salary
5 FROM employees
6 WHERE job_id = 'IT_PROG')
7 AND job_id <> 'IT_PROG' and rownum<2;
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
158 McEwen SA_REP 9000
SQL> ---ALL
SQL> SELECT employee_id, last_name, job_id, salary
2 FROM employees
3 WHERE salary < ALL
4 (SELECT salary
5 FROM employees
6 WHERE job_id = 'ST_MAN')
7 AND job_id <> 'ST_MAN' and rownum<2;
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
105 Austin IT_PROG 4800
SQL>
SQL> ----USING ALL WITH NOT CHECK THE SYNTAX
SQL> SELECT employee_id, last_name, job_id, salary
2 FROM employees
3 WHERE NOT salary < ALL
4 (SELECT salary
5 FROM employees
6 WHERE job_id = 'IT_PROG')
7 AND job_id <> 'IT_PROG' and rownum<2;
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
----------- ------------------------- ---------- ----------
100 King AD_PRES 24000
&#91;/sourcecode&#93;
<strong>13- Using NOT IN with subqueries with NULL values</strong>
I knew this but its nice to remind for the ones who doesnt know already. If you use NOT IN for a subquery with possible NULL clause it wont bring resultset because comparing null as you know equals to null. To workaround you can use NVL function.
Look what happens because of 2 NULL values
SQL> select count(*) from departments
2 where department_id
3 not in (select department_id
4 from employees);
COUNT(*)
----------
0
SQL> select count(*) from employees
2 where department_id is null;
COUNT(*)
----------
2
SQL>--change nulls with 999999
SQL> select count(*) from departments
2 where department_id
3 not in (select nvl(department_id,99999)
4 from employees);
COUNT(*)
----------
16

14- Creating views with check option
To be honest I was totally unfamiliar to “with check option”. Document says; Specify WITH CHECK OPTION to indicate that Oracle Database prohibits any changes to the table or view that would produce rows that are not included in the subquery. When used in the subquery of a DML statement, you can specify this clause in a subquery in the FROM clause but not in subquery in the WHERE clause.

15- Sequences restart from MINVALUE(thanks Radino for the correction) with CYCLE option regardless from the START WITH value on the definition
I never used a sequence with cyle option before but I was thinking that it will re-use the already used ones when you recycle the values but it won’t. If you set MINVALUE it will use MINVALUE, not the START WITH value and if you dont set MINVALUE it will use 1 which is default MINVALUE like below.

This is the end of PART-1. I hope I can complete the PART-2 of this series.

Warning: This post is not about what is being asked in Exam. It is about what I learned during my studies. If you want the questions just study and take the exam. Try to think what would you expect from a SQL Expert and study the things you think you would expect.

Update : Dont forget to check the links in Comment of Tonguc for better understanding of SQL Fundementals.