Oracle Cert IZ0-061 questions
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;
07-JAN-2009
Assuming SYSDATE=07-JUN-1996 12:05pm, what value is returned after executing the following statement? SELECT ADD_MONTHS (SYSDATE - 1) FROM DUAL;
07-MAY-1996 12:05pm
What result is returned by the following statement? (Choose the best answer.) SELECT COUNT(*) FROM DUAL;
1
Consider this statement: SELECT o.employee_id, o.last_name FROM employees o WHERE o.salary > (SELECT avg(i.salaty) 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. It will be executed once for every row in the EMPLOYEES table.
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. Subqueries can be embedded within each other with no practical limitations on depth.
Choose one correct statement regarding group functions.
Group functions can operate on multiple rows at a time.
Oracle Registry Key
HKEY_LOCAL_MACHINE SOFTWARE ORACLE KEY_OraDb12c_home1
What value is returned after executing the following statement? SUBSTR('How_long_is_a_piece_of_string? ','_', ' ') FROM DUAL;
Howlongisapieceofstring
Which of these are single-row character-case conversion functions? (Choose all that apply.)
LOWER INITCAP
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;
No rows in the LOCATIONS table have the COUNTRY_ID values returned.
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.)
None of the above
Which join is performed by the following query? (Choose the best answer.) SELECT E.JOB_ID,J.JOB_ID FROM EMPLOYEES E JOIN JOBS J ON (E.SALARY < J.MAX_SALARY);
Nonequijoin
Null
Null refers to an absence of data
What value is returned after executing the following statement? SELECT TO_CHAR(1234.49, '999999.9') FROM DUAL;
001234.5
Assuming SYSDATE=30-DEC-2007, what value is returned after executing the following statement? SELECT TRUNC (SYSDATE, 'YEAR') FROM DUAL;
01-JAN-2007
Which values are returned after executing the following statement? (Choose all that apply.) SELECT COUNT(*), COUNT(SALARY) FROM EMPLOYEES; Assume there are ten employee records and each contain a SALARY value of 100, except for one, which has a null value in their SALARY field.
10 and 9
What value is returned after executing the following statement? (Choose the best answer.) 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.
100
If the REGIONS table, which contains 4 rows, is cross joined to the COUNTRIES table, which contains 25 rows, how many rows appear in the final results set? (Choose the best answer.)
100 rows
How many records are returned by the following query? (Choose the best answer.) SELECT SUM(SALARY), DEPARTMENT_ID FROM EMPLOYEES GROUP BY DEPARTMENT_ID; Assume there are 11 non null and 1 null unique DEPARTMENT_ID values. All records have a non null SALARY value.
12
What value is returned after executing the following statement? SELECT TO_NUMBER(1234.49, 999999.9) FROM DUAL;
1234.5
If SYSDATE returns 12-JUL-2009, what is returned by the following statement? SELECT TO_CHAR(SYSDATE, 'fmDDth MONTH') FROM DUAL;
12TH JULY
What value is returned after executing the following statement? SELECT SUBSTR('How_long_is_a_piece_of_string? ','_', 5, 3) FROM DUAL;
14
1NF
1st normal form - deals with the issue of eliminating unnecessary repeating groups of data.
What value is returned after executing the following statement? SELECT MOD(14, 3) FROM DUAL;
2
How many rows are returned after executing the following statement? (Choose the best answer.) SELECT * FROM REGIONS R1 JOIN REGIONS R2 ON (R1.REGION_ID=LENGTH(R2.REGION_NAME)/2);
3
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';
3
What value is returned after executing the following statement? (Choose the best answer.) SELECT LENGTH('How_long_is_a_piece_of_string? ') FROM DUAL;
30
3NF
3rd Normal form
What value is returned after executing the following statement? (Choose the best answer.) 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.
4
What value is returned after executing the following statement? (Choose the best answer.) SELECT SUM(SALARY) FROM EMPLOYEES; 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
900
Which of the following are requirements for using SQL Developer? (Choose two correct answers.)
A Java Runtime Environment A graphical terminal
A database schema
A database schema is all the objects in the database owned by one user. Collection of objects.
equiloin
A row is associated with one or more rows in another table based on the equality of column values or expressions.
Oracle Database
A set of files on a disk
Web application
A web application can be defined as any application with which users communicate with HTTP. Web applications usually run in at least three tiers: a database tier manages access to the data, the client tier (often implemented as a web browser) handles the local window management for communications with the users, and an application tier in the middle executes the program logic that generates the user interface and the SQL calls to the database.
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. A scalar subquery returns one row. B. A scalar subquery returns one column.
Consider this statement: SELECT employee_id, last_name FROM employees WHERE salary > (SELECT avg(salay) 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. It will be executed before the outer query.
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. 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);
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. The two statements should generate the same result. D. Both statements will always run successfully, even if there are two departments with DEPARTMENT_NAME='Executive'.
Consider this generic description of a SELECT statement: Where could subqueries be used? (Choose all correct answers.) SELECT select_list FROM table WHERE condition GROUP BY expression_1 HAVING expression_2 ORDER BY expression_3;
A. select_list B. table C. condition E. expression_2
What components of the IT environment can Oracle Enterprise Manager Cloud Control manage? (Choose the best answer.)
All of the above
column aliasing
An alias is an alternate name for a column or an expression.
What values are returned after executing the following statement? (Choose the best answer.) 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.
An error is returned
How can you move a schema from one user to another?
B. You cannot move a schema from one user to another.
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. The statement will fail if the second query returns more than one row.
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. The statement would return no rows.
DCL
Data Control Language; - GRANT - REVOKE
DDL
Data Definition Language; - CREATE - ALTER - DROP - RENAME - TRUNCATE - COMMENT
DML
Data Manipulation Language; - SELECT - INSERT - UPDATE - DELETE - MERGE
DSS
Data Support system
DWH
Data ware house
Crows Foot notation
Describes relationships between entities in a data model
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. All the above can be used.
What type of conversion is performed by the following statement? SELECT TO_NUMBER (1234.49, 999999.9) FROM DUAL;
Implicit conversion
PRAGMA
In Oracle PL/SQL, PRAGMA refers to a compiler directive or "hint" it is used to provide an instruction to the compiler. The directive restricts member subprograms to query or modify database tables and packaged variables. Pragma directives are processed at compile time where they pass necessary information to the compiler; they are not processed at runtime.
nonequijoin
In this case, a row is associated with one or more rows in another table if its column values fall into a range determined by inequality operators
Which of these constructs is not part of the SQL language? (Choose all that apply.)
Iteration, based on WHILE.. Iteration, based on FOR..DO Branching, based on IF..THEN..ELSE
If SYSDATE returns 12-JUL-2009, what is returned by the following statement? (Choose the best answer.) SELECT TO_CHAR(SYSDATE, 'fmMONTH, YEAR') FROM DUAL;
JULY, TWO THOUSAND NINE
Joining
Joining, as a relational concept, refers to the interaction of tables with each other in a query. Third normal form, as discussed in Chapter 1, presented the notion of separating different types of data into autonomous tables to avoid duplication and maintenance anomalies and to associate related data using primary and foreign key relationships.
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%'
ORDER BY 3 DESC NULLS LAST, 2 ASC;
How many rows of data are returned after executing the following statement? (Choose the best answer.) 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.
One row
SQL is a set-oriented language. Which of these features is a consequence of this? (Choose the best answer.)
One statement can affect multiple rows
OLTP
Online transaction Processing
Oracle Home
Oracle software installation: the set of files and directories containing the executable code and some of the configuration files.
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;
PARTY
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.)
SELECT * FROM EMPLOYEES E JOIN DEPARTMENTS D ON E.DEPARTMENT_ID=D.DEPARTMENT_ID AND E.MANAGER_ID=D.MANAGER_ID; AND SELECT * FROM EMPLOYEES JOIN DEPARTMENTS USING (DEPARTMENT_ID,MANAGER_ID); AND SELECT * FROM EMPLOYEES NATURAL JOIN DEPARTMENTS;
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
SELECT * FROM EMPLOYEES E JOIN DEPARTMENTS D USING (DEPARTMENT_ID);
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
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);
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
SELECT JH.JOB_ID FROM JOB_HISTORY JH RIGHT OUTER JOIN JOBS J ON JH.JOB_ID=J.JOB_ID;
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
SELECT SALARY * &TAX_RATE TAX FROM EMPLOYEES;
Which two clauses of the SELECT statement facilitate selection and projection?
SELECT, WHERE
What value is returned after executing the following statement? SELECT NVL2 (NULLIF('CODA', 'SID'), 'SPANIEL', 'TERRIER') FROM DUAL;
SPANIEL
Which of these statements regarding SQL Developer are correct? (Choose two answers.)
SQL Developer can be installed outside an Oracle Home SQL Developer can store passwords.
What languages can run within the database? (Choose all that apply.)
SQL and PL/SQL and Java
Selection
Selection refers to the restriction of the tuples or rows selected from a relation (table). It is often not desirable to retrieve every row from a table
Three pillars of relational theory,
Selection, projection and joining
Choose any incorrect statements regarding conversion functions. (Choose all that apply.)
TO_CHAR may convert date items to character items. TO_CHAR may convert numbers to character items. TO_DATE may convert date items to character items.
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;
TWO THOUSAND NINE
Relations
Tables
Choose one correct statement regarding the following query: SELECT * FROM EMPLOYEE E JOIN DEPARTMENTS D ON (D.DEPARTMENT_ID=E.DEPARTMENT_ID) JOIN LOCATIONS L ON (L.LOCATION_ID = D.LOCATION_ID);
The JOIN...ON clause may be used for joins between multiple tables.
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 appears in the SELECT list, in the ORDER BY clause.
The ORDER BY clause specifies one or more terms by which the retrieved rows are sorted. These terms can only be column names.
Database Dictionary
The Oracle server stores information about all tables in a special set of relational tables called the data dictionary, in order to manage them. It stores definitions of database objects in a centralized, ordered, and structured format.
Primary Key
The PRIMARY KEY constraint uniquely identifies each record in a database table. Primary keys must contain UNIQUE values. A primary key column cannot contain NULL values. Most tables should have a primary key, and each table can have only ONE primary key.
What value is returned after executing the following statement? (Choose the best answer.) SELECT SUBSTR('How_long_is_a_piece_of_string? ', 5, 4) FROM DUAL;
The SUBSTR functions return a portion of char, beginning at character position, substring_length characters long. SUBSTR calculates lengths using characters as defined by the input character set. SUBSTRB uses bytes instead of characters. SUBSTRC uses Unicode complete characters. SUBSTR2 uses UCS2 code points. SUBSTR4 uses UCS4 code points. So the answer is long(no pun intended).
Where may the demonstration schemas be created? (Choose the best answer.)
The demonstration schemas can be created in any database.
Instance
The instance is a set of processes and memory structures: it exists on the CPU(s) and in the memory of the server node, and this existence is temporary.
RAC
The simplest form of the database server is one instance connected to one database, but in a more complex environment one database can be opened by many instances concurrently.
Literal values
These values refer to numeric, character, or date and time values found in SELECT clauses that do not originate from any database object.
Which statements regarding single-row functions are true? (Choose all that apply.)
They execute once for each record processed. They may have zero or more input parameters
Data that is modeled into a form suitable for processing in a relational database may be described as being (Choose the best answer.)
Third normal form
concatenation operator
This operator is used to join character expressions or columns together to create a larger character expression.
TCL
Transaction Control Language; - COMMIT - ROLLBACK - SAVEPOINT
How many rows of data are returned after executing the following statement? (Choose the best answer.) 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.
Two rows
An entity-relationship diagram shows data modeled into (Choose the best answer.)
Two-dimensional tables
Which two of the following conditions are equivalent to each other?
WHERE COMMISSION_PCT IS NULL, and WHERE NOT(COMMISSION_PCT IS NOT NULL)
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:
WHERE DEPARTMENT_NAME LIKE '%er%';
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 $1,000 to $4,000. The SELECT and FROM clauses are SELECT LAST_NAME, JOB_ID, SALARY FROM EMPLOYEES:
WHERE JOB_ID IN ('SA_REP','MK_MAN') AND SALARY BETWEEN 1000 AND 4000;
Which of the following WHERE clauses contains an error? The SELECT and FROM clauses are SELECT * FROM EMPLOYEES:
WHERE JOB_ID IN (SA_REP,MK_MAN);
Which other two of the following conditions are equivalent to each other?
WHERE SALARY <=5000 AND SALARY >=2000 and WHERE SALARY BETWEEN 2000 AND 5000
What value is returned after executing the following statement? SELECT NVL(SUBSTR('AM I NULL', 10), 'YES I AM') FROM DUAL;
YES I AM
Three concepts from relational theory encompass the capability of the SELECT statement
projection, selection, and joining
projection
refers to the restriction of attributes (columns) selected from a relation or table
2NF
second normal form