What I learned during Oracle SQL Expert Exam Study Part-1

Yesterday, I passed the Oracle 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'<>’

SQL> select 'coskan''s approach to oracle' from dual;

'COSKAN''SAPPROACHTOORACLE'
---------------------------
coskan's approach to oracle

SQL> select 'coskan'||q[''s approach to oracle'] from dual;
ERROR:
ORA-01756: quoted string not properly terminated

SQL> select 'coskan'||q'['s approach to oracle]' from dual;

'COSKAN'||Q'['SAPPROACHTOOR
---------------------------
coskan's approach to oracle

SQL> select 'coskan'||q'('s approach to oracle)' from dual;

'COSKAN'||Q'('SAPPROACHTOOR
---------------------------
coskan's approach to oracle

SQL> select 'coskan'||q'{'s approach to oracle}' from dual;

'COSKAN'||Q'{'SAPPROACHTOOR
---------------------------
coskan's approach to oracle

SQL> select 'coskan'||q'<'s approach to oracle>' from dual;

'COSKAN'||Q'<'SAPPROACHTOOR
---------------------------
coskan's approach to oracle
&#91;/sourcecode&#93;

<strong>2- null with logical operators . </strong>( I felt really shamed that I didnt know (null&amp;false=false) and (null or true=true) )  table is like below   null and true = null null and false = false null OR true = true null OR false = null


SQL> select * from hr.departments where (1=1 and department_id not in (select null from dual)) and rownum<2;

no rows selected

SQL> select * from hr.departments where (1=2 and department_id not in (select null from dual)) and rownum<2;

no rows selected

SQL> select * from hr.departments where (1=1 or department_id not in (select null from dual)) and rownum<2;

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
           10 Administration                        200        1700

SQL> select * from hr.departments where (1=2 or department_id not in (select null from dual)) and rownum<2;

no rows selected
&#91;/sourcecode&#93;

<strong>3- Escape clause to avoid wildcard characters. </strong>

To use wildcard characters as normal character you can use Escape clause with Like</pre>


SQL> select first_name from hr.employees
     2  where first_name like '%E_%';

FIRST_NAME
--------------------
Ellen
Elizabeth
E_leni

SQL> select first_name from hr.employees 
     2 where first_name like '%E\_%' escape '\';

FIRST_NAME
--------------------
E_leni

SQL> select first_name from hr.employees 
    2   where first_name like '%E?_%' escape '?';

FIRST_NAME
--------------------
E_leni

4 Operator Precedence
You need to know these precedence to make sure your query is doing the right thing. (simple things can cause big problems)
1 Arithmetic operators
2 Concatenation operator
3 Comparison conditions
4 IS [NOT] NULL, LIKE, [NOT] IN
5 [NOT] BETWEEN
6 Not equal to
7 NOT logical condition
8 AND logical condition
9 OR logical condition

5-Order by Nulls FIRST/LAST Clause If you want to put nulls in the beginning or end of you resultset all you need to do is add

order by ….. nulls first
or
order by ….. nulls last

 
SQL> select * from hr.departments 
   2 order by manager_id nulls last;

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
           90 Executive                             100        1700
           60 IT                                    103        1400
          100 Finance                               108        1700
          230 IT Helpdesk                                      1700
          240 Government Sales                                 1700

5 rows selected.

SQL> select * from hr.departments 
   2 order by manager_id nulls first;

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
          240 Government Sales                                 1700
          230 IT Helpdesk                                      1700
           90 Executive                             100        1700
           60 IT                                    103        1400
          100 Finance                               108        1700

5 rows selected.

6- How to see seconds past midnight (dont know how I missed this when I prepare take control of time post)

SQL> select to_char(sysdate,'sssss') seconds_pass_midnight
  2  from dual;

SECON
-----
61546

7- nullif function
Document says NULLIF compares expr1 and expr2. If they are equal, then the function returns null. If they are not equal, then the function returns expr1.

Say you want to put null for the lenght_diff for employees whose first_name lenght is equal to last_name lenght (dont ask why 🙂 )

SQL> select  first_name,last_name
  2  ,nullif (length(first_name),length(last_name)) lengt_diff
  3  from employees;

FIRST_NAME LAST_NAME  LENGT_DIFF
---------- ---------- ----------
MARKO      JANKO
JOE        JANKO               3

8- Coalesce Function
Document 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.

Lets see how ;

SQL> SELECT last_name, employee_id,manager_id,department_id,
  2  COALESCE(to_char(department_id),TO_CHAR(manager_id), 'No manager no department who are you ???')
  3  FROM hr.employees
  4  ;

LAST_NAME                 EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID COALESCE(TO_CHAR(DEPARTMENT_ID),TO_CHAR(
------------------------- ----------- ---------- ------------- ----------------------------------------
OConnell                          198        124            50 50
Grant                             199        124            50 50
Whalen                            200        101            10 10
Hartstein                         201        100            20 20
Fay                               202        201           160 160
Mavris                            203        101            40 40
Baer                              204        101            70 70
Higgins                           205        101           110 110
Gietz                             206        205           110 110
King                              100                       90 90	******************
Kochhar                           101        100            90 90
....................................................................................
....................................................................................
....................................................................................
....................................................................................
Feeney                            197                          No manager no department who are you ??? ****************

107 rows selected.

9- Need of group by clause for nested group by functions.

---single aggregate function

SQL> select avg(salary) from employees;

AVG(SALARY)
-----------
 6461.68224


---nested aggregate functions
SQL> select max(avg(salary)) from employees;
select max(avg(salary)) from employees
           *
ERROR at line 1:
ORA-00978: nested group function without GROUP BY

SQL> select max(avg(salary)) from employees
  2  group by department_id;

MAX(AVG(SALARY))
----------------
      19333.3333

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.

SQL> select employee_id,department_name from employees natural join departments;

32 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2052257371

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |    11 |   330 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |             |    11 |   330 |     7  (15)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| DEPARTMENTS |    11 |   209 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMPLOYEES   |   108 |  1188 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID"
              AND "EMPLOYEES"."MANAGER_ID"="DEPARTMENTS"."MANAGER_ID")
   2 - filter("DEPARTMENTS"."MANAGER_ID" IS NOT NULL)

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.

SQL> CREATE OR REPLACE VIEW empv
  2  AS SELECT *
  3  FROM employees
  4  WHERE department_id = 10
  5  WITH CHECK OPTION CONSTRAINT empv10_ck ;

View created.

SQL> update empv set department_id=15 where Last_name='Whalen';
update empv set department_id=15 where Last_name='Whalen'
       *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

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.

SQL> CREATE SEQUENCE cos_seq
  2  INCREMENT BY 10
  3  START WITH 10
  4  MAXVALUE 30
  5  NOCACHE
  6  CYCLE;

Sequence created.

SQL> select cos_seq.nextval from dual;

   NEXTVAL
----------
        10

SQL> select cos_seq.nextval from dual;

   NEXTVAL
----------
        20

SQL> select cos_seq.nextval from dual;

   NEXTVAL
----------
        30

SQL> select cos_seq.nextval from dual;

   NEXTVAL
----------
         1

SQL> select cos_seq.nextval from dual;

   NEXTVAL
----------
        11

SQL> select cos_seq.nextval from dual;

   NEXTVAL
----------
        21

SQL> select cos_seq.nextval from dual;

   NEXTVAL
----------
         1

16- NEXTVAL must be used before the initial usage of CURRVAL in a session.

Never tried, never got the error so never heard.

SQL> ---Brand New session
SQL> select cos_seq.currval from dual;
select cos_seq.currval from dual
       *
ERROR at line 1:
ORA-08002: sequence COS_SEQ.CURRVAL is not yet defined in this session


SQL> select cos_seq.nextval from dual;

   NEXTVAL
----------
        11
SQL> select cos_seq.currval from dual;

   CURRVAL
----------
        11

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.