SQL SELF TEST QUESTIONS

Assuming SYSDATE=07-JUN-1996 12:05pm, what value is returned after executing the following statement? SELECT ADD_MONTHS(SYSDATE,-1) FROM DUAL; (Choose the best answer.) A. 07-MAY-1996 12:05pm B. 06-JUN-1996 12:05pm C. 07-JUL-1996 12:05pm D. None of the above

A

Choose one correct statement regarding the following query. SELECT C.COUNTRY_ID FROM LOCATIONS L RIGHT OUTER JOIN COUNTRIES C ON (L.COUNTRY_ID=C.COUNTRY_ID) WHERE L.COUNTRY_ID is NULL A. No rows in the LOCATIONS table have the COUNTRY_ID values returned. B. No rows in the COUNTRIES table have the COUNTRY_ID values returned. C. The rows returned represent the COUNTRY_ID values for all the rows in the LOCATIONS table. D. None of the above

A

Consider this statement: select employee_id, last_name from employees where salary > (select avg(salary) from employees); When will the subquery be executed? (Choose the best answer.) A. It will be executed before the outer query. B. It will be executed after the outer query. C. It will be executed concurrently with the outer query. D. It will be executed once for every row in the EMPLOYEES table.

A

How many records are returned by the following query? SELECT SUM(SALARY), DEPARTMENT_ID FROM EMPLOYEES GROUP BY DEPARTMENT_ID; Assume there are 11 nonnull and 1 null unique DEPARTMENT_ID values. All records have a nonnull SALARY value. (Choose the best answer.) A. 12 B. 11 C. NULL D. None of the above

A

How many rows of data are returned after executing the following statement? SELECT DEPT_ID, SUM(SALARY) FROM EMP GROUP BY DEPT_ID HAVING SUM(NVL(SALARY,100)) > 400; Assume the EMP table has ten rows and each contains a SALARY value of 100, except for one, which has a null value in the SALARY field. The first and second five rows have DEPT_ID values of 10 and 20, respectively. (Choose the best answer.) A. Two rows B. One row C. Zero rows D. None of the above

A

If SYSDATE returns 12-JUL-2009, what is returned by the following statement? SELECT TO_CHAR(SYSDATE, 'fmDDth MONTH') FROM DUAL; (Choose the best answer.) A. 12TH JULY B. 12th July C. TWELFTH JULY D. None of the above

A

What value is returned after executing the following statement? Take note that 01-JAN-2009 occurs on a Thursday. (Choose the best answer.) SELECT NEXT_DAY('01-JAN-2009','wed') FROM DUAL; A. 07-JAN-2009 B. 31-JAN-2009 C. Wednesday D. None of the above

A

Which of the following statements are syntactically correct? (Choose all that apply.) A. SELECT * FROM EMPLOYEES E JOIN DEPARTMENTS D USING (DEPARTMENT_ID); B. SELECT * FROM EMPLOYEES JOIN DEPARTMENTS D USING (D.DEPARTMENT_ID); C. SELECT D.DEPARTMENT_ID FROM EMPLOYEES JOIN DEPARTMENTS D USING (DEPARTMENT_ID); D. None of the above

A

Which of the following statements are syntactically correct? (Choose all that apply.) A. SELECT JH.JOB_ID FROM JOB_HISTORY JH RIGHT OUTER JOIN JOBS J ON JH.JOB_ID=J.JOB_ID B. SELECT JOB_ID FROM JOB_HISTORY JH RIGHT OUTER JOIN JOBS J ON (JH.JOB_ID=J.JOB_ID) C. SELECT JOB_HISTORY.JOB_ID FROM JOB_HISTORY OUTER JOIN JOBS ON JOB_HISTORY.JOB_ID=JOBS.JOB_ID D. None of the above

A

You create a sequence as follows: create sequence seq1 start with 1; After selecting from it a few times, you want to reinitialize it to reissue the numbers already generated. How can you do this? (Choose the best answer.) A. You must drop and re-create the sequence. B. You can't. Under no circumstances can numbers from a sequence be reissued once they have been used. C. Use the command ALTER SEQUENCE SEQ1 START WITH 1; to reset the next value to 1. D. Use the command ALTER SEQUENCE SEQ1 CYCLE; to reset the sequence to its starting value.

A

You want to insert a row and then update it. What sequence of steps should you follow? (Choose the best answer.) A. INSERT, UPDATE, COMMIT B. INSERT, COMMIT, UPDATE, COMMIT C. INSERT, SELECT FOR UPDATE, UPDATE, COMMIT D. INSERT, COMMIT, SELECT FOR UPDATE, UPDATE, COMMIT

A

What are distinguishing characteristics of heap tables? (Choose two answers.) A. A heap can store variable length rows. B. More than one table can store rows in a single heap. C. Rows in a heap are in random order. D. Heap tables cannot be indexed. E. Tables in a heap do not have a primary key.

A AND C

After describing the EMPLOYEES table, you discover that the SALARY column has a data type of NUMBER(8,2). Which SALARY values(s) will NOT be permitted in this column? A. SALARY=12345678 B. SALARY=123456.78 C. SALARY=12345.678 D. SALARY=123456 E. SALARY=12.34

A and C

Which of these are single-row character-case conversion functions? (Choose all that apply.) A. LOWER B. SMALLER C. INITCASE D. INITCAP

A and D

Choose one correct statement regarding group functions. A. Group functions may only be used when a GROUP BY clause is present. B. Group functions can operate on multiple rows at a time. C. Group functions only operate on a single row at a time. D. Group functions can execute multiple times within a single group.

B

Choose the query that extracts the LAST_NAME, JOB_ID, and SALARY values from the EMPLOYEES table for records having JOB_ID values of either SA_REP or MK_MAN and having SALARY values in the range of $1000 to $4000. The SELECT and FROM clauses are SELECT LAST_NAME, JOB_ID, SALARY FROM EMPLOYEES: A. WHERE JOB_ID IN ('SA_REP','MK_MAN') AND SALARY > 1000 AND SALARY < 4000; B. WHERE JOB_ID IN ('SA_REP','MK_MAN') AND SALARY BETWEEN 1000 AND 4000; C. WHERE JOB_ID LIKE 'SA_REP%' AND 'MK_MAN%' AND SALARY > 1000 AND SALARY < 4000; D. WHERE JOB_ID = 'SA_REP' AND SALARY BETWEEN 1000 AND 4000 OR JOB_ID='MK_MAN';

B

Consider this statement: insert into regions (region_id,region_name) values ((select max(region_id)+1 from regions), 'Great Britain'); What will the result be? (Choose the best answer.) A. The statement will not succeed if the value generated for REGION_ID is not unique, because REGION_ID is the primary key of the REGIONS table. B. The statement has a syntax error because you cannot use the VALUES keyword with a subquery. C. The statement will execute without error. D. The statement will fail if the REGIONS table has a third column.

C

What value is returned after executing the following statement? SELECT TO_NUMBER(1234.49, '999999.9') FROM DUAL; (Choose the best answer.) A. 1234.49 B. 001234.5 C. 1234.5 D. None of the above

D

Which statement reports on unique JOB_ID values from the EMPLOYEES table? (Choose all that apply.) A. SELECT JOB_ID FROM EMPLOYEES; B. SELECT UNIQUE JOB_ID FROM EMPLOYEES; C. SELECT DISTINCT JOB_ID, EMPLOYEE_ID FROM EMPLOYEES; D. SELECT DISTINCT JOB_ID FROM EMPLOYEES;

D

Which of these is a defining characteristic of a complex view, rather than a simple view? (Choose one or more correct answers.) A. Restricting the projection by selecting only some of the table's columns B. Naming the view's columns with column aliases C. Restricting the selection of rows with a WHERE clause D. Performing an aggregation E. Joining two tables

D AND E

Which of these statements will fail because the table name is not legal? (Choose two answers.) A. create table "SELECT" (col1 date); B. create table "lowercase" (col1 date); C. create table number1 (col1 date); D. create table 1number (col1 date); E. create table update (col1 date);

D AND E

Which comparison operator cannot be used with multiple-row subqueries? (Choose the best answer.) A. ALL B. ANY C. IN D. NOT IN E. All the above can be used.

E

Several object types share the same namespace, and therefore cannot have the same name in the same schema. Which of the following object types is not in the same namespace as the others? (Choose the best answer.) A. Index B. PL/SQL stored procedure C. Synonym D. Table E. View

A

There is a simple view SCOTT.DEPT_VIEW on the table SCOTT.DEPT. This insert fails with an error: SQL> insert into dept_view values('SUPPORT','OXFORD'); insert into dept_view values('SUPPORT','OXFORD') * ERROR at line 1: ORA-01400: cannot insert NULL into ("SCOTT"."DEPT"."DEPTNO") What might be the problem? (Choose the best answer.) A. The INSERT violates a constraint on the detail table. B. The INSERT violates a constraint on the view. C. The view was created as WITH READ ONLY. D. The view was created as WITH CHECK OPTION.

A

This statement will fail: create unique bitmap index on employees(department_id,hire_date); Why? (Choose the best answer.) A. Bitmap indexes cannot be unique. B. The two columns are of different data types. C. A bitmap index can be on only one column. D. There is already a B*Tree index on DEPARTMENT_ID.

A

What value is returned after executing the following statement: SELECT SUBSTR('How_long_is_a_piece_of_string?', 5,4) FROM DUAL; (Choose the best answer.) A. long B. _long C. string? D. None of the above

A

What value is returned after executing the following statement? SELECT NVL2(NULLIF('CODA','SID'),'SPANIEL','TERRIER') FROM DUAL; (Choose the best answer.) A. SPANIEL B. TERRIER C. NULL D. None of the above

A

What value is returned after executing the following statement? SELECT SUM(SALARY) FROM EMPLOYEES; Assume there are 10 employee records and each contains a SALARY value of 100, except for 1, which has a null value in the SALARY field. (Choose the best answer.) A. 900 B. 1000 C. NULL D. None of the above

A

A UNIQUE constraint on a column requires an index. Which of the following scenarios is correct? (Choose one or more correct answers.) A. If a UNIQUE index already exists on the column, it will be used. B. If a NONUNIQUE index already exists it will be used C. If a NONUNIQUE index already exists on the column, a UNIQUE index will be created implicitly. D. If any index exists on the column, there will be an error as Oracle attempts to create another index implicitly.

A AND B

What are the distinguishing characteristics of a scalar subquery? (Choose two correct answers.) A. A scalar subquery returns one row. B. A scalar subquery returns one column. C. A scalar subquery cannot be used in the SELECT LIST of the parent query. D. A scalar subquery cannot be used as a correlated subquery.

A AND B

Which of the following statements are equivalent? (Choose two answers.) A. select employee_id from employees where salary < all (select salary from employees where department_id=10); B. select employee_id from employees where salary < (select min(salary) from employees where department_id=10); C. select employee_id from employees where salary not >= any (select salary from employees where department_id=10); D. select employee_id from employees e join departments d on e.department_id= d.department_id where e.salary < (select min(salary) from employees) and d.department_id=10;

A AND B

Consider the following statement: select last_name from employees join departments on employees.department_id = departments.department_id where department_name='Executive'; and this statement: select last_name from employees where department_id in (select department_id from departments where department_name='Executive'); What can be said about the two statements? (Choose two correct answers.) A. The two statements should generate the same result. B. The two statements could generate different results. C. The first statement will always run successfully; the second statement will error if there are two departments with DEPARTMENT_NAME 'Executive.' D. Both statements will always run successfully, even if there are two departments with DEPARTMENT_NAME 'Executive.'

A AND D

Which of these commands will remove every row in a table? (Choose one or more correct answers.) A. A DELETE command with no WHERE clause B. A DROP TABLE command C. A TRUNCATE command D. An UPDATE command, setting every column to NULL and with no WHERE clause

A and C

The DEPARTMENTS table contains DEPARTMENT_NAME column with data type VACHAR2(30). (Choose two true statements about this column.) A. This column can store character data up to a maximum of 30 characters. B. This column must store character data that is at least 30 characters long. C. The VARCHAR2 data type is replaced by the CHAR data type. D. This column can store data in a column with data type VARCHAR2(50) provided that the contents are at most 30 characters long.

A and D

Which two of the following conditions are equivalent to each other? A. WHERE COMMISSION_PCT IS NULL B. WHERE COMMISSION_PCT = NULL C. WHERE COMMISSION_PCT IN (NULL) D. WHERE NOT(COMMISSION_PCT IS NOT NULL)

A and D

Which of these operators will remove duplicate rows from the final result? (Choose all that apply.) A. INTERSECT B. MINUS C. UNION D. UNION ALL

A, B, AND C

Consider this generic description of a SELECT statement: SELECT select_list FROM table WHERE condition GROUP BY expression_1 HAVING expression_2 ORDER BY expression_3 ; Where could subqueries be used? (Choose all correct answers.) A. select_list B. table C. condition D. expression_1 E. expression_2 F. expression_3

A, B, C, D, AND E

Which of these commands will terminate a transaction? (Choose three correct answers.) A. COMMIT B. DELETE C. ROLLBACK D. ROLLBACK TO SAVEPOINT E. SAVEPOINT F. TRUNCATE

A, C, AND F

Which three of the following conditions are equivalent to each other? A. WHERE SALARY <=5000 AND SALARY >=2000 B. WHERE SALARY IN (2000,3000,4000,5000) C. WHERE SALARY BETWEEN 2000 AND 5000 D. WHERE SALARY > 1999 AND SALARY < 5001 E. WHERE SALARY >=2000 AND <=5000

A, C, and D

The EMPLOYEES and DEPARTMENTS tables have two identically named columns: DEPARTMENT_ID and MANAGER_ID. Which statements join these tables based on both column values? (Choose all that apply.) A. SELECT * FROM EMPLOYEES NATURAL JOIN DEPARTMENTS; B. SELECT * FROM EMPLOYEES JOIN DEPARTMENTS USING (DEPARTMENT_ ID,MANAGER_ID); C. SELECT * FROM EMPLOYEES E JOIN DEPARTMENTS D ON E.DEPARTMENT_ ID=D.DEPARTMENT_ID AND E.MANAGER_ID=D.MANAGER_ID; D. None of the above

A,B, AND C

Which of the following data types are variable length? (Choose all correct answers.) A. BLOB B. CHAR C. LONG D. NUMBER E. RAW F. VARCHAR2

A,C,D,E, AND F

There are four rows in the REGIONS table. Consider the following statements and choose how many rows will be returned for each: 0, 4, 8, or 16. A. select * from regions union select * from regions B. select * from regions union all select * from regions C. select * from regions minus select * from regions D. select * from regions intersect select * from regions

A=4, B=8, C=0, D=4

Choose the WHERE clause that extracts the DEPARTMENT_NAME values containing the character literal "er" from the DEPARTMENTS table. The SELECT and FROM clauses are SELECT DEPARTMENT_NAME FROM DEPARTMENTS: A. WHERE DEPARTMENT_NAME IN ('%e%r'); B. WHERE DEPARTMENT_NAME LIKE '%er%'; C. WHERE DEPARTMENT_NAME BETWEEN 'e' AND 'r'; D. WHERE DEPARTMENT_NAME CONTAINS 'e%r';

B

Consider these three statements: create synonym s1 for employees; create public synonym s1 for departments; select * from s1; Which of the following statements is correct? (Choose the best answer.) A. The second statement will fail because an object S1 already exists. B. The third statement will show the contents of EMPLOYEES. C. The third statement will show the contents of DEPARTMENTS. D. The third statement will show the contents of the table S1, if such a table exists in the current schema.

B

How can you change the primary key value of a row? (Choose the best answer.) A. You cannot change the primary key value. B. Change it with a simple UPDATE statement. C. The row must be removed with a DELETE and reentered with an INSERT. D. This is only possible if the row is first locked with a SELECT FOR UPDATE.

B

How many rows are returned after executing the following statement? SELECT * FROM REGIONS R1 JOIN REGIONS R2 ON (R1.REGION_ID=LENGTH(R2. REGION_NAME)/2); The REGIONS table contains the following row data. (Choose the best answer.) REGION_ID REGION_NAME 1 Europe 2 Americas 3 Asia 4 Middle East and Africa A. 2 B. 3 C. 4 D. None of the above

B

How many rows of data are returned after executing the following statement? SELECT DEPT_ID, SUM(NVL(SALARY,100)) FROM EMP GROUP BY DEPT_ID HAVING SUM(SALARY) > 400; Assume the EMP table has ten rows and each contains a SALARY value of 100, except for one, which has a null value in the SALARY field. The first and second five rows have DEPT_ID values of 10 and 20, respectively. (Choose the best answer.) A. Two rows B. One row C. Zero rows D. None of the above

B

If SYSDATE returns 12-JUL-2009, what is returned by the following statement? SELECT DECODE(TO_CHAR(SYSDATE,'MM'),'02','TAX DUE','PARTY') FROM DUAL; (Choose the best answer.) A. TAX DUE B. PARTY C. 02 D. None of the above

B

If SYSDATE returns 12-JUL-2009, what is returned by the following statement? SELECT TO_CHAR(SYSDATE, 'fmMONTH, YEAR') FROM DUAL; (Choose the best answer.) A. JUL, 2009 B. JULY, TWO THOUSAND NINE C. JUL-09 D. None of the above

B

If SYSDATE returns 12-JUL-2009, what is returned by the following statement? SELECT TO_CHAR(TO_DATE(TO_CHAR(SYSDATE,'DD'),'DD'),'YEAR') FROM DUAL; (Choose the best answer.) A. 2009 B. TWO THOUSAND NINE C. 12-JUL-2009 D. None of the above

B

Study this statement: select ename from emp union all select ename from ex_emp; In what order will the rows be returned? (Choose the best answer.) A. The rows from each table will be grouped and within each group will be sorted on ENAME. B. The rows from each table will be grouped but not sorted. C. The rows will not be grouped but will all be sorted on ENAME. D. The rows will be neither grouped nor sorted.

B

The DEFINE command explicitly declares a session-persistent substitution variable with a specific value. How is this variable referenced in an SQL statement? Consider an expression that calculates tax on an employee's SALARY based on the current tax rate. For the following session-persistent substitution variable, which statement correctly references the TAX_RATE variable? DEFINE TAX_RATE=0.14 A. SELECT SALARY * :TAX_RATE TAX FROM EMPLOYEES; B. SELECT SALARY * &TAX_RATE TAX FROM EMPLOYEES; C. SELECT SALARY * :&&TAX TAX FROM EMPLOYEES; D. SELECT SALARY * TAX_RATE TAX FROM EMPLOYEES;

B

The following character literal expression is selected from the DUAL table: SELECT 'Coda''s favorite fetch toy is his orange ring' FROM DUAL; (Choose the result that is returned.) A. An error would be returned due to the presence of two adjacent quotes B. Coda's favorite fetch toy is his orange ring C. Coda''s favorite fetch toy is his orange ring D. 'Coda''s favorite fetch toy is his orange ring'

B

User JOHN updates some rows and asks user ROOPESH to log in and check the changes before he commits them. Which of the following statements is true? (Choose the best answer.) A. ROOPESH can see the changes but cannot alter them because JOHN will have locked the rows. B. ROOPESH will not be able to see the changes. C. JOHN must commit the changes so that ROOPESH can see them and, if necessary, roll them back. D. JOHN must commit the changes so that ROOPESH can see them, but only JOHN can roll them back.

B

What type of conversion is performed by the following statement? SELECT LENGTH(3.14285) FROM DUAL; (Choose the best answer.) A. Explicit conversion B. Implicit conversion C. TO_NUMBER function conversion D. None of the above

B

What value is returned after executing the following statement: SELECT LENGTH('How_long_is_a_piece_of_string?') FROM DUAL; (Choose the best answer.) A. 29 B. 30 C. 24 D. None of the abov

B

What value is returned after executing the following statement? SELECT INSTR('How_long_is_a_piece_of_string?','_',5,3) FROM DUAL; (Choose the best answer.) A. 4 B. 14 C. 12 D. None of the above

B

Which join is performed by the following query? SELECT E.JOB_ID,J.JOB_ID FROM EMPLOYEES E JOIN JOBS J ON (E.SALARY < J.MAX_SALARY); (Choose the best answer.) A. Equijoin B. Nonequijoin C. Cross join D. Outer join

B

Which line of this statement will cause it to fail? (Choose the best answer.) A. select ename, hired from current_staff B. order by ename C. minus D. select ename, hired from current staff E. where deptno=10 F. order by ename;

B

Which of the following statements are syntactically correct? (Choose all that apply.) A. SELECT E.EMPLOYEE_ID, J.JOB_ID PREVIOUS_JOB, E.JOB_ID CURRENT_JOB FROM JOB_HISTORY J CROSS JOIN EMPLOYEES E ON (J.START_DATE=E.HIRE_DATE); B. SELECT E.EMPLOYEE_ID, J.JOB_ID PREVIOUS_JOB, E.JOB_ID CURRENT_JOB FROM JOB_HISTORY J JOIN EMPLOYEES E ON (J.START_DATE=E.HIRE_DATE); C. SELECT E.EMPLOYEE_ID, J.JOB_ID PREVIOUS_JOB, E.JOB_ID CURRENT_JOB FROM JOB_HISTORY J OUTER JOIN EMPLOYEES E ON (J.START_DATE=E. HIRE_DATE); D. None of the above

B

Which query creates a projection of the DEPARTMENT_NAME and LOCATION_ID columns from the DEPARTMENTS table? A. SELECT DISTINCT DEPARTMENT_NAME, LOCATION_ID FROM DEPARTMENTS; B. SELECT DEPARTMENT_NAME, LOCATION_ID FROM DEPARTMENTS; C. SELECT DEPT_NAME, LOC_ID FROM DEPT; D. SELECT DEPARTMENT_NAME AS "LOCATION_ID" FROM DEPARTMENTS

B

f SYSDATE returns 12-JUL-2009, what is returned by the following statement? SELECT TO_CHAR(SYSDATE, 'fmMONTH, YEAR') FROM DUAL; (Choose the best answer.) A. JUL, 2009 B. JULY, TWO THOUSAND NINE C. JUL-09 D. None of the above

B

What are distinguishing characteristics of a public synonym rather than a private synonym? (Choose two correct answers.) A. Public synonyms are always visible to all users. B. Public synonyms can be accessed by name without a schema name qualifier. C. Public synonyms can be selected from without needing any permissions. D. Public synonyms can have the same names as tables or views.

B AND D

Which statements regarding single-row functions are true? (Choose all that apply.) A. They may return more than one result. B. They execute once for each record processed. C. They may have zero or more input parameters. D. They must have at least one mandatory parameter.

B and C

Choose the two illegal statements. The two correct statements produce identical results. The two illegal statements will cause an error to be raised: A. SELECT DEPARTMENT_ID|| ' represents the '|| DEPARTMENT_NAME|| ' Department' as "Department Info" FROM DEPARTMENTS; B. SELECT DEPARTMENT_ID|| ' represents the || DEPARTMENT_NAME|| ' Department' as "Department Info" FROM DEPARTMENTS; C. select department_id|| ' represents the '||department_name|| ' Department' "Department Info" from departments; D. SELECT DEPARTMENT_ID represents the DEPARTMENT_NAME Department as "Department Info" FROM DEPARTMENTS;

B and D

Which expressions do not return NULL values? (Choose all that apply.) A. select ((10 + 20) * 50) + null from dual; B. select 'this is a '||null|| 'test with nulls' from dual; C. select null/0 from dual; D. select null|| 'test'||null as "Test" from dual;

B and D

After describing the JOB_HISTORY table, you discover that the START_DATE and END_DATE columns have a data type of DATE. Consider the expression END_DATE -START_DATE. (Choose two correct statements.) A. A value of DATE data type is returned. B. A value of type NUMBER is returned. C. A value of type VARCHAR2 is returned. D. The expression is invalid since arithmetic cannot be performed on columns with DATE data types. E. The expression represents the days between the END_DATE and START_DATE less one day.

B and E

Which of the following commands can be rolled back? A. COMMIT B. DELETE C. INSERT D. MERGE E. TRUNCATE F. UPDATE

B, C, D, AND F

A transaction consists of two statements. The first succeeds, but the second (which updates several rows) fails partway through because of a constraint violation. What will happen? (Choose the best answer.) A. The whole transaction will be rolled back. B. The second statement will be rolled back completely, and the first will be committed. C. The second statement will be rolled back completely, and the first will remain uncommitted. D. Only the one update that caused the violation will be rolled back; everything else will be committed. E. Only the one update that caused the violation will be rolled back; everything else will remain uncommitted.

C

Assuming SYSDATE=30-DEC-2007, what value is returned after executing the following statement? SELECT TRUNC(SYSDATE,'YEAR') FROM DUAL; (Choose the best answer.) A. 31-DEC-2007 B. 01-JAN-2008 C. 01-JAN-2007 D. None of the above

C

Choose one correct statement regarding the following query: SELECT * FROM EMPLOYEES E JOIN DEPARTMENTS D ON (D.DEPARTMENT_ID=E.DEPARTMENT_ID) JOIN LOCATIONS L ON (L.LOCATION_ID =D.LOCATION_ID); A. Joining three tables is not permitted. B. A Cartesian product is generated. C. The JOIN...ON clause may be used for joins between multiple tables. D. None of the above

C

Choose one false statement about the ORDER BY clause. A. When using the ORDER BY clause, it always appears as the last clause in a SELECT statement. B. The ORDER BY clause may appear in a SELECT statement that does not contain a WHERE clause. C. The ORDER BY clause specifies one or more terms by which the retrieved rows are sorted. These terms can only be column names. D. Positional sorting is accomplished by specifying the numeric position of a column as it ap- pears in the SELECT list, in the ORDER BY clause.

C

Consider this compound query: select empno, hired from emp union all select emp_id,hired,fired from ex_emp; The columns EMP.EMPNO and EX_EMP.EMP_ID are integer; the column EMP.HIRED is timestamp; the columns EX_EMP.HIRED and EX_EMP.FIRED are date. Why will the state- ment fail? (Choose the best answer.) A. Because the columns EMPNO and EMP_ID have different names B. Because the columns EMP.HIRED and EX_EMP.HIRED are different data types C. Because there are two columns in the first query and three columns in the second query D. For all the reasons above E. The query will succeed.

C

Consider this statement, which is intended to prompt for an employee's name and then find all employees who have the same job as the first employee: select last_name,employee_id from employees where job_id = (select job_id from employees where last_name = '&Name'); What would happen if a value were given for &Name that did not match with any row in EMPLOYEES? (Choose the best answer.) A. The statement would fail with an error. B. The statement would return every row in the table. C. The statement would return no rows. D. The statement would return all rows where JOB_ID is NULL.

C

Consider this statement: create table t1 as select * from regions where 1=2; What will be the result? (Choose the best answer.) A. There will be an error because of the impossible condition. B. No table will be created because the condition returns FALSE. C. The table T1 will be created but no rows inserted because the condition returns FALSE. D. The table T1 will be created and every row in REGIONS inserted because the condition returns a NULL as a row filter.

C

Consider this statement: select last_name, (select count(*) from departments) from employees where salary = (select salary from employees); What is wrong with it? (Choose the best answer.) A. Nothing is wrong—the statement should run without error. B. The statement will fail because the subquery in the SELECT list references a table that is not listed in the FROM clause. C. The statement will fail if the second query returns more than one row. D. The statement will run but is extremely inefficient because of the need to run the second subquery once for every row in EMPLOYEES.

C

How can you delete the values from one column of every row in a table? (Choose the best answer.) A. Use the DELETE COLUMN command. B. Use the TRUNCATE COLUMN command. C. Use the UPDATE command. D. Use the DROP COLUMN command.

C

If a compound query contains both a MINUS and an INTERSECT operator, which will be applied first? (Choose the best answer.) A. The INTERSECT, because INTERSECT has higher precedence than MINUS. B. The MINUS, because MINUS has a higher precedence than INTERSECT. C. The precedence is determined by the order in which they are specified. D. It is not possible for a compound query to include both MINUS and INTERSECT.

C

If an UPDATE or DELETE command has a WHERE clause that gives it a scope of several rows, what will happen if there is an error part way through execution? The command is one of several in a multistatement transaction. (Choose the best answer.) A. The command will skip the row that caused the error and continue. B. The command will stop at the error, and the rows that have been updated or deleted will remain updated or deleted. C. Whatever work the command had done before hitting the error will be rolled back, but work done already by the transaction will remain.

C

Study this view creation statement: create view dept30 as select department_id,employee_id,last_name from employees where department_id=30 with check option; What might make the following statement fail? (Choose the best answer.) update dept30 set department_id=10 where employee_id=114; A. Unless specified otherwise, views will be created as WITH READ ONLY. B. The view is too complex to allow DML operations. C. The WITH CHECK OPTION will reject any statement that changes the DEPARTMENT_ID. D. The statement will succeed.

C

The following query retrieves the LAST_NAME, SALARY, and COMMISSION_PCT values for employees whose LAST_NAME begins with the letter R. Based on the following query, choose the ORDER BY clause that first sorts the results by the COMMISSION_PCT column, listing highest commission earners first, and then sorts the results in ascending order by the SALARY column. Any records with NULL COMMISSION_PCT must appear last: SELECT LAST_NAME, SALARY, COMMISSION_PCT FROM EMPLOYEES WHERE LAST_NAME LIKE 'R%' A. ORDER BY COMMISSION_PCT DESC, 2; B. ORDER BY 3 DESC, 2 ASC NULLS LAST; C. ORDER BY 3 DESC NULLS LAST, 2 ASC; D. ORDER BY COMMISSION_PCT DESC, SALARY ASC;

C

User JOHN updates some rows but does not commit the changes. User ROOPESH queries the rows that JOHN updated. Which of the following statements is true? Wants 3 but choose 1 best... A. ROOPESH will not be able to see the rows because they will be locked. B. ROOPESH will be able to see the new values, but only if he logs in as JOHN. C. ROOPESH will see the old versions of the rows. D. ROOPESH will see the state of the state of the data as it was when JOHN last created a SAVEPOINT.

C

What result is returned by the following statement? SELECT COUNT(*) FROM DUAL; (Choose the best answer.) A. NULL B. 0 C. 1 D. None of the above

C

What value is returned after executing the following statement? SELECT AVG(NVL(SALARY,100)) FROM EMPLOYEES; Assume there are ten employee records and each contains a SALARY value of 100, except for one employee, who has a null value in the SALARY field. (Choose the best answer.) A. NULL B. 90 C. 100 D. None of the above

C

What value is returned after executing the following statement? SELECT MOD(14,3) FROM DUAL; (Choose the best answer.) A. 3 B. 42 C. 2 D. None of the above

C

What value is returned after executing the following statement? SELECT NVL(SUBSTR('AM I NULL',10),'YES I AM') FROM DUAL; (Choose the best answer.) A. NO B. NULL C. YES I AM D. None of the above

C

What value is returned after executing the following statement? SELECT REPLACE('How_long_is_a_piece_of_string?','_','') FROM DUAL; (Choose the best answer.) A. How long is a piece of string? B. How_long_is_a_piece_of_string? C. Howlongisapieceofstring? D. None of the above

C

What value is returned after executing the following statement? SELECT SUM((AVG(LENGTH(NVL(SALARY,0))))) FROM EMPLOYEES GROUP BY SALARY; Assume there are ten employee records and each contains a SALARY value of 100, except for one, which has a null value in the SALARY field. (Choose the best answer.) A. An error is returned B. 3 C. 4 D. None of the above

C

What value is returned after executing the following statement? SELECT TO_CHAR(1234.49, '999999.9') FROM DUAL; (Choose the best answer.) A. 1234.49 B. 001234.5 C. 1234.5 D. None of the above

C

What values are returned after executing the following statement? SELECT JOB_ID, MAX_SALARY FROM JOBS GROUP BY MAX_SALARY; Assume that the JOBS table has ten records with the same JOB_ID value of DBA and the same MAX_SALARY value of 100. (Choose the best answer.) A. One row of output with the values DBA, 100 B. Ten rows of output with the values DBA, 100 C. An error is returned D. None of the above

C

Which of the following WHERE clauses contains an error? The SELECT and FROM clauses are SELECT * FROM EMPLOYEES: A. WHERE HIRE_DATE IN ('02-JUN-2004'); B. WHERE SALARY IN ('1000','4000','2000'); C. WHERE JOB_ID IN (SA_REP,MK_MAN); D. WHERE COMMISSION_PCT BETWEEN 0.1 AND 0.5;

C

Which two clauses of the SELECT statement facilitate selection and projection? A. SELECT, FROM B. ORDER BY, WHERE C. SELECT, WHERE D. SELECT, ORDER BY

C

Which values are returned after executing the following statement? SELECT COUNT(*), COUNT(SALARY) FROM EMPLOYEES; Assume there are 10 employee records and each contains a SALARY value of 100, except for 1, which has a null value in their SALARY field. (Choose all that apply.) A. 10 and 10 B. 10 and NULL C. 10 and 9 D. None of the above

C

You have created an index with this statement: create index ename_i on employees(last_name,first_name); How can you adjust the index to include the employees' birthdays, which is a date type column called DOB? (Choose the best answer.) A. Use ALTER INDEX ENAME_I ADD COLUMN DOB;. B. You can't do this because of the data type mismatch. C. You must drop the index and re-create it. D. This can only be done if the column DOB is NULL in all existing rows.

C

Which types of constraint require an index? (Choose all that apply.) A. CHECK B. NOT NULL C. PRIMARY KEY D. UNIQUE

C AND D

A query can have a subquery embedded within it. Under what circumstances could there be more than one subquery? (Choose the best answer.) A. The outer query can include an inner query. It is not possible to have another query within the inner query. B. It is possible to embed a single-row subquery inside a multiple-row subquery, but not the other way around. C. The outer query can have multiple inner queries, but they must not be embedded within each other. D. Subqueries can be embedded within each other with no practical limitations on depth.

D

A sequence is created as follows: create sequence seq1 maxvalue 50; If the current value is already 50, when you attempt to select SEQ1.NEXTVAL what will happen? (Choose the best answer.) A. The sequence will cycle and issue 0. B. The sequence will cycle and issue 1. C. The sequence will reissue 50. D. There will be an error.

D

A view and a synonym are created as follows: create view dept_v as select * from dept; create synonym dept_s for dept_v; Subsequently the table DEPT is dropped. What will happen if you query the synonym DEPT_S ? (Choose the best answer.) A. There will not be an error because the synonym addresses the view, which still exists, but there will be no rows returned. B. There will not be an error if you first recompile the view with the command ALTER VIEW DEPT_V COMPILE FORCE; C. There will be an error because the synonym will be invalid. D. There will be an error because the view will be invalid. E. There will be an error because the view will have been dropped implicitly when the table was dropped.

D

Choose any incorrect statements regarding conversion functions. (Choose all that apply.) A. TO_CHAR may convert date items to character items. B. TO_DATE may convert character items to date items. C. TO_CHAR may convert numbers to character items. D. TO_DATE may convert date items to character items.

D

Choose the correct syntax to return all columns and rows of data from the EMPLOYEES table. A. select all from employees; B. select employee_id, first_name, last_name, first_name, department_id from employees; C. select % from employees; D. select * from employees; E. select *.* from employees;

D

Consider these three statements: create view v1 as select department_id,department_name,last_name from departments join employees using (department_id); select department_name,last_name from v1 where department_id=20; select d.department_name,e.last_name from departments d, employees e where d.department_id=e.department_id and d.department_id=20; The first query will be quicker than the second because (choose the best answer): A. The view has already done the work of joining the tables. B. The view uses ISO standard join syntax, which is faster than the Oracle join syntax used in the second query. C. The view is precompiled, so the first query requires less dynamic compilation than the second query. D. There is no reason for the first query to be quicker.

D

Consider this statement: select o.employee_id, o.last_name from employees o where o.salary > (select avg(i.salary) from employees i where i.department_id=o.department_id); When will the subquery be executed? (Choose the best answer.) A. It will be executed before the outer query. B. It will be executed after the outer query. C. It will be executed concurrently with the outer query. D. It will be executed once for every row in the EMPLOYEES table.

D

If a table T1 has four numeric columns, C1, C2, C3, and C4, which of these statements will succeed? (Choose the best answer.) A. insert into T1 values (1,2,3,null); B. insert into T1 values ('1','2','3','4'); C. insert into T1 select * from T1; D. All the statements (A, B, and C) will succeed. E. None of the statements (A, B, or C) will succeed.

D

If a table is created without specifying a schema, in which schema will it be? (Choose the best answer.) A. It will be an orphaned table, without a schema. B. The creation will fail. C. It will be in the SYS schema. D. It will be in the schema of the user creating it. E. It will be in the PUBLIC schema.

D

If you issue this command: update employees set salary=salary * 1.1; what will be the result? (Choose the best answer.) A. The statement will fail because there is no WHERE clause to restrict the rows affected. B. The first row in the table will be updated. C. There will be an error if any row has its SALARY column NULL. D. Every row will have SALARY incremented by 10 percent, unless SALARY was NULL.

D

Study the result of this SELECT statement: SQL> select * from t1; C1 C2 C3 C4 ---------- ---------- ---------- ---------- 1 2 3 4 5 6 7 8 If you issue this statement: insert into t1 (c1,c2) values(select c1,c2 from t1); why will it fail? (Choose the best answer.) A. Because values are not provided for all the table's columns: there should be NULLs for C3 and C4. B. Because the subquery returns multiple rows: it requires a WHERE clause to restrict the number of rows returned to one. C. Because the subquery is not scalar: it should use MAX or MIN to generate scalar values. D. Because the VALUES keyword is not used with a subquery. E. It will succeed, inserting two rows with NULLs for C3 and C4.

D

Study these statements: create table tab1 (c1 number(1), c2 date); alter session set nls_date_format='dd-mm-yy'; insert into tab1 values (1.1,'31-01-07'); Will the insert succeed? (Choose the best answer) A. The insert will fail because the 1.1 is too long. B. The insert will fail because the '31-01-07' is a string, not a date. C. The insert will fail for both reasons A and B. D. The insert will succeed.

D

The EMPLOYEES and DEPARTMENTS tables have two identically named columns: DEPARTMENT_ID and MANAGER_ID. Which of these statements joins these tables based only on common DEPARTMENT_ID values? (Choose all that apply.) A. SELECT * FROM EMPLOYEES NATURAL JOIN DEPARTMENTS; B. SELECT * FROM EMPLOYEES E NATURAL JOIN DEPARTMENTS D ON E.DEPARTMENT_ID=D.DEPARTMENT_ID; C. SELECT * FROM EMPLOYEES NATURAL JOIN DEPARTMENTS USING (DEPARTMENT_ID); D. None of the above

D

There are four rows of data in the REGIONS table. Consider the following SQL statement: SELECT '6 * 6' "Area" FROM REGIONS; How many rows of results are returned and what value is returned by the Area column? (Choose the best answer.) A. 1 row returned, Area column contains value 36 B. 4 rows returned, Area column contains value 36 for all 4 rows C. 1 row returned, Area column contains value 6 * 6 D. 4 rows returned, Area column contains value 6 * 6 for all 4 rows E. A syntax error is returned.

D

When a table is created with a statement such as the following: create table newtab as select * from tab; will there be any constraints on the new table? (Choose the best answer.) A. The new table will have no constraints, because constraints are not copied when creating tables with a subquery. B. All the constraints on TAB will be copied to NEWTAB. C. Primary key and unique constraints will be copied but not check and not null constraints. D. Check and not null constraints will be copied but not unique or primary key. E. All constraints will be copied, except foreign key constraints.

D

When using ampersand substitution variables in the following query, how many times will you be prompted to input a value for the variable called JOB the first time this query is executed? SELECT FIRST_NAME, '&JOB' FROM EMPLOYEES WHERE JOB_ID LIKE '%'||&JOB|| '%' AND '&&JOB' BETWEEN 'A' AND 'Z'; A. 0 B. 1 C. 2 D. 3

D

Which of the following is not supported by Oracle as an internal data type? (Choose the best answer.) A. CHAR B. FLOAT C. INTEGER D. STRING

D

Which of these set operators will not sort the rows? (Choose the best answer.) A. INTERSECT B. MINUS C. UNION D. UNION ALL

D