Evaluate the following query:
SQL> SELECT promo_name || q'{‘s start date was }’ || promo_begin_date
AS “Promotion Launches”
FROM promotions;
What would be the outcome of the above query?
A.
It produces an error because flower braces have been used.
B.
It produces an error because the data types are not matching.
C.
It executes successfully and introduces an ‘s at the end of each promo_name in the output.
D.
It executes successfully and displays the literal ” {‘s start date was } ” for each row in the output.
It doesn’t work for me.
SQL> select FIRST_NAME q'{‘s start date }’ hire_date from employees;
select FIRST_NAME q'{‘s start date }’ hire_date from employees
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
You missed “||” operator. Please try again with complete following query:
SELECT promo_name || q’{‘s start date was }’ || promo_begin_date
AS “Promotion Launches”
FROM promotions;
Thank you Admin. You’re right, but now I think answer C maybe is not exactly:
SQL> select FIRST_NAME || q'{‘.s start date }’ || hire_date from employees;
FIRST_NAME||Q'{‘.SSTARTDATE}’||HIRE_DATE
——————————————————
Steven’.s start date 17-JUN-87
Neena’.s start date 21-SEP-89
Hi Luis,
In your query, SQL> select FIRST_NAME || q’{‘.s start date }’ || hire_date from employees;
=> There was one odd full-stop which precede s character, “q’{‘.s” .Can you remove it and run the query again?
I take the oracle exam and this question appears on my exam: this is how I can remeber
SQL> select FIRST_NAME || q'{‘s start date \ }’ || hire_date from employees;
FIRST_NAME||Q'{‘SSTARTDATE\}’||HIRE_DATE
——————————————————
Steven’s start date \ 17-JUN-87
Neena’s start date \ 21-SEP-89
Lex’s start date \ 13-JAN-93
I pass with 100% with answer C. but I still think that is ambiguos.
Thanks for your help.
Luis
what does q stand for in the query?
SQL> select FIRST_NAME || q’{‘s start date \ }’ || hire_date from employees;
It is called alternative quote (q) operator.
for example:
http://askanantha.blogspot.nl/2007/12/q-quote-operator-introduced-in-oracle.html
Thanks Justyna
We make use of single quotation mark in SQL and PL/SQL to identify sting literals. If the literal itself contains a single quote, we need to add one more quote next to it. This additional quote acts as an escape character and removes conflict with the outside quotes that are enclosing the string.
Oracle realises that long complex strings having lot of single quotes can turn out to become cumbersome and prone to errors that may not be caught during testing.
Release 10g onwards, a new quoting mechanism is provided in the form of “q”. This new quote operator allows us to choose our own quotation mark delimiter.
What we normally do:-
SQL> select ‘amar”s web blog. It”s personal..’ str from dual;
STR
——————————–
amar’s web blog. It’s personal..
1 row selected.
Here is use of Q – quote operator. The above statement can also be represented as any one of the below.
select q'(amar’s web blog. It’s personal.)’ str from dual;
select q'[amar’s web blog. It’s personal.]’ str from dual;
select q’Aamar’s web blog. It’s personal.A’ str from dual;
select q’/amar’s web blog. It’s personal./’ str from dual;
select q’Zamar’s web blog. It’s personal.Z’ str from dual;
select q’|amar’s web blog. It’s personal.|’ str from dual;
select q’+amar’s web blog. It’s personal.+’ str from dual;
(New added)