Oracle knowledge point section 2 (strings contain single quotes)

Oracle knowledge point section 2 (A string contains single quotes) 1. About a string containing single quotes, a string references a string. What if the string itself contains single quotes? It is expressed in two single quotes or starts with q or Q, followed by a string. the string's 1st characters and the final ending characters are separated symbols, and the quotation marks in the middle are casually written, if the start is [, <, (, {end must be],> ,),}. But if the start is],>,),}, the end must be the same as the start, and other symbols, +, |, or a, A, and so on. The start and end are the same, including case. Example:

SQL> select 'you are'' beautiful' from dual;'YOUARE''BEAUTIFUL------------------you are' beautifulselect q'[you are' beautiful]' from dual;Q'[YOUARE'BEAUTIFU----------------------you are' beautifulSQL> select q'+it's a cat+' from dual  2  /Q'+IT'SACAT+'-------------it's a catSQL> select Q'Ait's a cata' from dual  2  /select Q'Ait's a cata' from dual

 

ORA-01756: The character string in the quotation marks does not end properly 2. aliases cannot be used in the where clause, but can be used in the order by clause, as follows

SQL> select employee_id, last_name, job_id, department_id 2 from employees 3 where department_id = 90; EMPLOYEE_ID LAST_NAME JOB_ID comment Comment comment ---------------- 100 King AD_PRES 90 101 Kochhar AD_VP 90 102 De Haan AD_VP 90SQL> select employee_id, last_name, job_id, comment dd 2 from employees where dd = 90; from employees where dd = 90 * ERROR at line 2: ORA-00904: "DD": invalid identifierorder by you can use your own alias SQL> select employee_id, last_name, salary * 12 annsal from employees 2 order by annsal; EMPLOYEE_ID LAST_NAME ANNSAL ------------ hour ---------- 132 Olson 25200 136 Philtanker 26400 128 Markle 26400 127 Landry 28800 135 Gee 28800 191 Perkins 30000 119 30000 Colmenares 140 30000 Patel 144 30000 Vargas 182 30000 Sullivan 131 30000 Marlow

 

Why? Because the select statement has an execution order, the where clause is processed before the select statement, while the order by clause is processed after the select statement. The column alias is generated during select, so the where clause cannot see the alias at all, so it cannot be referenced, and the order by clause can. 3. NULL is a value that is unavaiable, unassigned, unknown or inapplicable. if there is a NULL value in an arithmetic expression, the result is NULL. In a string expression, the original string is retained. NULL Value in sorting: Put the NULL value in ascending order at the end and start in descending order, that is, at the maximum position. You can change the position and add nulls first or nulls last at the end of the statement. The select statement is sorted in ascending order by default, and the return value is sorted by department_id.

SQL> select  last_name, department_id from employees order by department_id;LAST_NAME                 DEPARTMENT_ID------------------                     -----------------Greenberg                            100Sciarra                                  100Urman                                   100Popp                                     100Faviet                                    100Gietz                                      110Higgins                                  110Grant

 

In descending order, NULL is in the first position

SQL> select  last_name, department_id from employees order by department_id desc;LAST_NAME                 DEPARTMENT_ID-------------------------               -------------GrantHiggins                                  110Gietz                                    110Urman                                  100Faviet                                  100Chen                                   100Popp                                    100Greenberg                           100Sciarra                                 100De Haan                               90Kochhar                               90

 

Adding nulls last at the end changes the position of the NULL value.

SQL> select  last_name, department_id from employees order by department_id desc  nulls last;LAST_NAME                 DEPARTMENT_ID------------------------- -------------Himuro                               30Tobias                               30Raphaely                             30Baida                                30Fay                                  20Hartstein                            20Whalen                               10Grant