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
2- null with logical operators . ( I felt really shamed that I didnt know (null&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
3- Escape clause to avoid wildcard characters.
To use wildcard characters as normal character you can use Escape clause with Like
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
12-ANY/ALL operator for subqueries
My face is going red for not using these two before. I think I never need them
Document 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 =, !=, >, <, =. 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
13- Using NOT IN with subqueries with NULL values
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.
