what will the following query return? SELECT REPLACE(RTRIM('Anticipation','on'), 'ti','shun') from DUAL;
In oracle, what do trigonometric functions operate on?
The default is radians, but degrees or gradients can be specified
If it is 5 minutes past noon on 15 jan 2000, what will the following statement return? SELECT ROUND(SYSDATE) ROUND(SYSDATE,'Y') FROM DUAL;
Which statement about nested functions is most correct?
Single-row nested functions can be nested in either single-row or group functions
Group functions can be nested in other group functions
Group functions cab be nested in single-row functions
A, B and C
A and B only
Why will the following query raise an exception? SELECT DEPT_NO, AVG(DISTINCT SALARY), COUNT(JO
JOB_COUNT FRIM EMP WHERE MGR LIKE 'J%' OR ABS(SALARY)>10 HAVING COUNT (JO
>5 ORDER BY 2 DESC;
A HAVING clause cannot contain a group function
The GROUP BY clause is missing
Abs() is not an oracle function
The query will not raise an exception
Why does the following SELECT statement fail? SELECT colorname Colour, MAX(cost) From itemdetail Where upper(colorname) like '%WHITE%' Group by colour Having count(*) > 20;
A GROUP BY clause cannot contain a coloumn alias
The condition COUNT (*) > 20 should be in the WHERE clause
The GROUP BY clause must contain the group functions used in the SELECT list
The HAVING Clause can only contain the group functions used in the SELECT list
What will the following query report? SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;
The number of employees in each department, including those without a deptnno
The number of employees in each department, ecept those without a deptno
The total number of employees, including those without a deptno
The total number of employees, except those without a deptno
Which assertion about the following quires is true> SELECT COUNT(DISTICT mgr), MAX(DISTINCT salary) from emp; SELECT COUNT (ALL mgr), MAX(ALL salary) FROM emp;
They will always return the same numbers in columns 1 and 2
They may return different numbers in column 1 but will always return the same number in column 2
They may return different numbers in column 1 and may return different numbers in column 2
They will always return the same number in column 1 but may return different numbers in column 2
What is the limit on the number of values a subquery using the IN operator can return to the parent query?
When using multiple tables to query information, in which clause do you specify the table names?
The contents of the CONTESTANTS table are listed as follows: NAME AGE COUNTRY ---------------- -------------- --------------- BERTRAND 24 FRANCE GONZALEZ 29 SPAIN HEINRICH 22 GERMANY TAN 39 CHINA SVENSKY
RUSSIA SOO 21 You issue the following query against this table: SELECT NAME FROM CONTESTANT WHERE (COUNTRY, AGE) IN ( SELECT COUNTRY, MIN(AGE) FROM CONTESTANT GROUP BY COUNTRY); What is the result?
To delete any constraint from the table we have to use command
All the operators are used in single row subquery except one
All the commands executes in iSQLplus except one
Ascript file which will be executed automatically in iSQLPlus is
both a and b
none of the above
A command in iSQL plus is used to give the status of old and new value of variable
none of the above
All commands are used to save the changes of the transaction except one
exitting from sqlplus
none of the above
A Clause which is used in joining two tables other than equality operator is
A Clause which is the pseudocolumn used to know the current value of the sequence
none of the above
A Query which is used in top-N analysis is
An operator is used to get and display the redundant records
All the datatypes with respect to Oracle 9i is true except one
TIMSTAMP with TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
None of the above
You are creating some tables in your database as part of the logical data model Which of the following constraints have an index associated with them that is generated automatically by Oracle?
You have a table with three associated indexes, two triggers, two references to that table from other tables, and a view You issue the DROP TABLE CASCADE CONSTRAINTS statement Which of the following objects will still remain after the statement is issued?
A The triggers B The indexes C The foreign keys in the other tables D The view
In order to set your SQL*Plus session so that your NLS_DATE_FORMAT information is altered in a specific way every time you log into Oracle, what method would be used?
A Setting preferences in the appropriate menu option B Creating an appropriate LOGINSQL file C Issuing the ALTER USER statement D Issuing the ALTER TABLE statement
The EMP_SALARY table has two columns, EMP_USER and SALARY EMP_USER is set to be the same as the Oracle username To support user MARTHA, the salary administrator, you create a view with the following statement: CREATE VIEW EMP_SAL_VW AS SELECT EMP_USER, SALARY FROM EMP_SALARY WHERE EMP_USER <> 'MARTHA'; MARTHA is supposed to be able to view and update anyone in the company's salary except her own through this view Which of the following clauses do you need to add to your view creation statement in order to implement this functionality?
WITH ADMIN OPTION
WITH GRANT OPTION
WITH SECURITY OPTION
WITH CHECK OPTION
You are developing PL/SQL code to manipulate and store data in an Oracle table All of the following numeric datatypes in PL/SQL can be stored in an Oracle database, except one Which is it?
You are performing some conversion operations in your PL/SQL programs To convert a date value into a text string, you would use which of the following conversion functions?
You have a table called TEST_SCORE that stores test results by student personal ID number, test location, and date the test was taken Tests given in various locations throughout the country are stored in this table A student is not allowed to take a test for 30 days after failing it the first time, and there is a check in the application preventing the student from taking a test twice in 30 days at the same location Recently, it has come to everyone's attention that students are able to circumvent the 30-day rule by taking a test in a different location Which of the following SQL statements would be useful for identifying the students who have done so?
SELECT ASTUDENT_ID, ALOCATION, BLOCATION FROM TEST_SCORE A, TEST_SCORE B WHERE ASTUDENT_ID = BSTUDENT_ID AND ALOCATION = BLOCATION AND TRUNC(ATEST_DATE)+30 <= TRUNC(BTEST_DATE) AND TRUNC(ATEST_DATE)-30 >= TRUNC(BTEST_DATE);
SELECT ASTUDENT_ID, ALOCATION, BLOCATION FROM TEST_SCORE A, TEST_SCORE B WHERE ASTUDENT_ID = BSTUDENT_ID AND ALOCATION <> BLOCATION AND TRUNC(ATEST_DATE)+30 >= TRUNC(BTEST_DATE) AND TRUNC(ATEST_DATE)-30 <= TRUNC(BTEST_DATE);
SELECT ASTUDENT_ID, ALOCATION, BLOCATION FROM TEST_SCORE A, TEST_SCORE B WHERE ASTUDENT_ID = BSTUDENT_ID AND ALOCATION = BLOCATION AND TRUNC(ATEST_DATE)+30 >= TRUNC(BTEST_DATE) AND TRUNC(ATEST_DATE)-30 <= TRUNC(BTEST_DATE);
SELECT ASTUDENT_ID, ALOCATION, BLOCATION FROM TEST_SCORE A, TEST_SCORE B WHERE ASTUDENT_ID = BSTUDENT_ID AND ALOCATION <> BLOCATION AND TRUNC(ATEST_DATE)+30 <= TRUNC(BTEST_DATE) AND TRUNC(ATEST_DATE)-30 >= TRUNC(BTEST_DATE);
You create a view with the following statement: CREATE VIEW BASEBALL_TEAM_VW AS SELECT BJERSEY_NUM, BPOSITION, BNAME FROM BASEBALL_TEAM B WHERE BNAME = USER; What will happen when user JONES attempts to SELECT a listing for user SMITH?
The SELECT will receive an error
The SELECT will succeed
The SELECT will receive NO ROWS SELECTED
The SELECT will add data only to BASEBALL_TEAM
You query the database with this command: SELECT atomic_weight FROM chart_n WHERE (atomic_weight BETWEEN 1 AND 50 OR atomic_weight IN (25, 70, 95)) AND atomic_weight BETWEEN (25 AND 75) Which of the following values could the statement retrieve?
What will the following operation return? [Choose two] SELECT TO_DATE('01-jan-00') - TO_DATE('01-dec-99') FROM dual;
365 if the NLS_DATE_FORMAT is set to 'DD-mon-RR'
A VARCHAR2 value
An error; you can't do this with dates
-36493 if the NLS_DATE_FORMAT is set to the default value
What is the purpose of the SUBSTR string function?
To insert a capital letter for each new word in the string
To return a specified substring from the string
To return the number of characters in the string
To substitute a non-null string for any null values returned
Evaluate this command: SELECT iisotope, gcalibration FROM chart_n i, gamma_calibrations g WHERE ienergy = genergy; What type of join is the command?
The statement is not a join query
In a SELECT statement, which character is used to pass in a value at runtime?
Which single-row function could you use to return a specific portion of a character string?
What will the following statement return? SELECT LAST_NAME, FIRST_NAME, START_DATE FROM EMPLOYEES WHERE HIRE_date< Trunc(sysdate) 5;
Employees hired in the past 5 years
Employess hired in the past 5 days
Employees hired more thatn 5 years ago
Employees hired more than 5 days ago
Which function(s) accept arguments of any datatype? Select all that apply
What will be returned from SIGN(ABS(NVL(-32,0)))?
Which functions could you use to strip leading characters from a character string Select two