Oracle Advanced SQL and PL/SQL Developer Certification Questions and Answers (Dumps and Practice Questions)
Question : In a Oracle there is data type of NUMBER(,). Which value will not be permitted in this column?
1. 99999999
2. 999999.99
3. Access Mostly Uused Products by 50000+ Subscribers
4. 999999
5. 99.99
Correct Answer : Get Lastest Questions and Answer :
Explanation:Columns with NUMBER(8,2) data type can store, at most, eight digits, of which, at most, six digits are to the left of the decimal point.
Question : When you do the SQL operation as below
DATE1-DATE2
Where both DATE1 and DATE2 are having datatype as DATE. Select the correct statement from below.
1. It will return number of days between two dates, with data type as a Number
2. Returned number of days with Datatype as DATE
3. Access Mostly Uused Products by 50000+ Subscribers
4. Returned number of days with Datatype as VARCHAR2
Correct Answer : Get Lastest Questions and Answer :
Explanation: Oracle SQL offers a DATE datatype from which you can do basic date arithmetic, determining the difference (in days) between two dates. One of the confounding problems with Oracle DATE datatypes is the computation of elapsed time. When date values are compared in the WHERE clause, the format of the date must match the format that the database is using or the comparison will fail. Alternately, if you are using another format, then you must tell the database how your date is formatted. The default date format that the Oracle database uses is: DD-Mon-YY. Oracle supports date arithmetic and you can make expressions like "date1 - date2" to get the difference between the two dates. Once you have the date difference, you can use simple techniques to express the difference in days, hours, minutes or seconds. Also note SQL to convert a day of the week to a number. For learning date arithmetic, we can use the Oracle "dual" pseudo table for date arithmetic. This last query displays the day of the week for any date in the past 1,000 years. To see the day of the week that you were born, copy this query into your c: directory, add your birth date, and run the query see what day of the week you were born on.
select to_char(to_date('25-MAR-1956','dd-mon-yyyy'),'day') from dual;
---------
sunday
You can perform date arithmetic directly in SQL*Plus, doing the math right in the SQL:
SQL> SELECT SYSDATE Today, SYSDATE - 1 Yesterday, SYSDATE + 1 Tomorrow FROM dual;
TODAY YESTERDAY TOMORROW
--------- --------- ---------
23-JAN-05 22-JAN-05 24-JAN-05
As you can see, the standard unit in Oracle date arithmetic is one day. When you add time to the date with SQL updates, you do it in fractions of a day.
1 Day 1 1 1
1 Hour 1/24 1/24 0.0417
1 Min 1/(24x60) 1/1440 .000694
1 Sec 1/(24x60x60) 1/86400 .000011574
The notation in the second column is most commonly used, because it is so much easier to read. Five minutes is 5/(24x60), much easier than 5/1440 or .00347. When we get to date functions in Chapter 2, you will see that there are functions to do date math by months, weeks and so forth. When performing Oracle date arithmetic, it might be tempting to use sophisticated conversion functions to convert a data, but we will see that this is not the most elegant solution:
round(to_number(end-date-start_date))? elapsed days , round(to_number(end-date-start_date)*24)? elapsed hours , round(to_number(end-date-start_date)*1440)? elapsed minutes . How is Oracle elapsed time data displayed by default? To find out, we issue a simple SQL*plus query:
SQL> select sysdate-(sysdate-3) from dual;
SYSDATE-(SYSDATE-3)
-------------------
Here we see that elapsed times are expressed in days. Hence, we can use easy conversion functions to convert this to hours or minutes.
Question :Which statement is correct for column having data type VARCHAR()
1. The VARCHAR2 data type is replaced by the CHAR data type.
2. This column must store character data that is at least 50 characters long.
3. Access Mostly Uused Products by 50000+ Subscribers
4. None of the above
Correct Answer : Get Lastest Questions and Answer :
CHAR
CHAR should be used for storing fix length character strings. String values will be space/blank padded before stored on disk. If this type is used to store varibale length strings, it will waste a lot of disk space.
VARCHAR
Currently VARCHAR behaves exactly the same as VARCHAR2. However, this type should not be used as it is reserved for future usage.
VARCHAR2
VARCHAR2 is used to store variable length character strings. The string value's length will be stored on disk with the value itself.
Related Questions
Question : What value is returned after executing the following statement?
TO_CHAR(1210.73, '$9,999.00')
1. $1,210
2. $1,210.73
3. $1,210.7
4. $1210.73
Question : What value is returned after executing the following statement?
TO_CHAR(21, '000099')
1. 21
2. 000021
3. 000099
4. 210000
Question : What value is returned after executing the following statement?
TO_NUMBER('1210.73', '9999.99')
1. 1210.9
2. 1210.7
3. 1210.73
4. 1210
Question : What value is returned after executing the following statement?
TO_CHAR(sysdate, 'Month DD, YYYY')
1. '07 09, 2015'
2. 'July 9, 2015'
3. 'Jul 09, 2015'
4. 'July 09, 2015'
Question : Which of the following query will sort the days of the week in order
A. SELECT ename, hiredate, TO_CHAR((hiredate),'fmDay') "Day"
FROM emp
ORDER BY "Day";
B. SELECT ename, hiredate, TO_CHAR((hiredate),'fmD') "Day"
FROM emp
ORDER BY "Day";
1. A
2. B
3. Both A and B
4. None of A and B
Question : What value is returned after executing the following statement?
TO_TIMESTAMP('2003/12/13 10:13:18', 'YYYY/MM/DD HH:MI:SS')
1. '13-DEC-03 10.13.18.000000000 '
2. '13-DEC-03 10.13.18 AM'
3. '13-DEC-03 10.13.18.000000000 AM'
4. '13-DEC-03 22.13.18.000000000 PM'