1z0-061 Oracle Fund.

Numeric columns are often specified as NUMBER(p,s),

NUMBER(6,7)

1. Which query creates a projection of the DEPARTMENT_NAME and LOCATION_ID columns from the DEPARTMENTS table? (Choose the best answer.) 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;

1. B. A projection is an intentional restriction of the columns returned from a table

1. What components of the IT environment can Oracle Enterprise Manager Cloud Control manage? (Choose the best answer.) A. Oracle databases B. Oracle application servers C. Third-party products D. The server machines E. All of the above

1. E. Cloud Control can manage the complete environment (according to Oracle Corporation). A, B, C, and D are incorrect. All of these can be managed by Cloud Control.

10. How can you move a schema from one user to another? (Choose the best answer.) A. Use the ALTER SCHEMA MOVE... command. B. You cannot move a schema from one user to another. C. A schema can only be moved if it is empty (or if all objects within it have been dropped). D. Attach the new user to the schema, then detach the old user from the schema.

10. B. A schema and a user are inseparable. A, C, and D are incorrect. A is incorrect because there is no such command. C and D are incorrect because they assume the impossible: that you can separate a user from his or her schema.

10. 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.

10. D. The literal expression '6 * 6' is selected once for each row of data in the REGIONS table

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

2. A. 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. Although A is the correct answer, note that since the question is phrased in the negative, these values are NOT allowed to be stored in such a column. A is not allowed because it contains eight whole number digits, but the data type is constrained to store six whole number digits and two fractional digits.

3. 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.

3. B and E. The result of arithmetic between two date values represents a certain number of days.

3. Data that is modeled into a form suitable for processing in a relational database may be described as being (Choose the best answer.) A. First normal form B. Third normal form C. Abnormal form D. Paranormal form

3. B. Third normal form is the usual form aimed for by systems analysts when they normalize data into relational structures. A, C, and D are incorrect. A is incorrect because first normal form is only the first stage of data normalization. C and D would be more suitable to the X-Files than to a database.

4. The DEPARTMENTS table contains a DEPARTMENT_NAME column with data type VARCHAR2(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

4. A and D. The scale of the VARCHAR2 data type, specified in brackets, determines its maximum capacity for storing character data as mentioned by A. If a data value that is at most 30 characters long is stored in any data type, it can also be stored in this column as stated by D.

4. An entity-relationship diagram shows data modeled into (Choose the best answer.) A. Two-dimensional tables B. Multidimensional tables C. Hierarchical structures D. Object-oriented structures

4. A. The relational model uses two-dimensional tables. B, C, and D are incorrect. B is incorrect because two dimensions is the limit for relational structures. C and D are incorrect because they refer to nonrelational structures (though there are facilities within the Oracle database for simulating them).

5. SQL is a set-oriented language. Which of these features is a consequence of this? (Choose the best answer.) A. Individual rows must have a unique identifier. B. Sets of users can be managed in groups. C. SQL statements can be placed within blocks of code in other languages, such as Java and PL/SQL. D. One statement can affect multiple rows

5. D. In a set-oriented language, one command can affect many rows (a set), whereas a procedural language processes rows one by one. A, B, and C are incorrect. A is incorrect because while rows should have a unique identifier in a well designed application, this is not actually a requirement. B is incorrect because users cannot be grouped in the Oracle environment. C is incorrect because (even though the statement is correct) it is not relevant to the question.

5. 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;

5. D. Unique JOB_ID values are projected from the EMPLOYEES table by applying the DISTINCT keyword to just the JOB_ID column.

6. Which of these constructs is not part of the SQL language? (Choose all that apply.) A. Iteration, based on WHILE.. B. Iteration, based on FOR..DO C. Branching, based on IF..THEN..ELSE D. Transaction control, based on COMMIT E. Transaction control, based on ROLLBACK

6. A, B, and C. These are all procedural constructions, which are not part of a set-oriented language. They are all used in PL/SQL. D and E are incorrect. These are SQL's transaction control statements.

6. 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;

6. B and D. B and D represent the two illegal statements that will return syntax errors if they are executed. This is a tricky question because it asks for the illegal statements and not the legal statements. B is illegal because it is missing a single quote enclosing the character literal "represents the". D is illegal because it does not make use of single quotes to enclose its character literals.

7. Which of these statements regarding SQL Developer are correct? (Choose two answers.) A. SQL Developer cannot connect to databases earlier than release 10g. B. SQL Developer can be installed outside an Oracle Home. C. SQL Developer can store passwords. D. SQL Developer relies on an LDAP directory for name resolution.

7. B and C. B is correct because SQL Developer can be installed in its own directory. C is correct because passwords can be saved as part of a connection definition (though this may not be a good idea). A and D are incorrect. A is incorrect because the Oracle Net protocol lets SQL Developer connect to a number of versions of the database. D is incorrect because LDAP is only one of several techniques for name resolution.

7. 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;

7. B and D. B and D do not return null values since character expressions are not affected in the same way by null values as arithmetic expressions. B and D ignore the presence of null values in their expressions and return the remaining character literals.

8. Which of the following are requirements for using SQL Developer? (Choose two correct answers.) A. A Java Runtime Environment B. The OCI libraries C. A name resolution method such as LDAP or a TNSNAMES.ORA file D. The SQL* Plus libraries E. A graphical terminal

8. A and E. A is correct because SQL Developer is written in Java and therefore requires a Java Runtime Environment. E is correct because SQL Developer needs a graphics terminal to display windows. B, C, and D are incorrect. B is incorrect because SQL Developer uses JDBC to connect to databases, not OCI. C is incorrect because, while SQL Developer can use LDAP or a TNSNAMES.ORA file, it can also use and store the basic connection details. D is incorrect because SQL Developer is a completely independent product.

8. 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;

8. D. An asterisk is the SQL operator that implies that all columns must be selected from a table.

9. 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'

9. B. The key to identifying the correct result lies in understanding the role of the single quotation marks. The entire literal is enclosed by a pair of quotes to avoid the generation of an error. The two adjacent quotes are necessary to delimit the single quote that appears in literal B.

9. Where may the demonstration schemas be created? (Choose the best answer.) A. The demonstration schemas must be created in a demonstration database. B. The demonstration schemas cannot be created in a production database. C. The demonstration schemas can be created in any database. D. The demonstr

9. C. The demonstration schemas can be created in any database, either at database creation time or by running scripts later. A, B, and D are incorrect. A and B are incorrect because, while they may be good practice, they are not a technical requirement. D is incorrect because it fails to understand that a schema can only be (and always is) created with a user.

6. Which two 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 > 2000 AND SALARY < 5000 E. WHERE SALARY >=2000 AND <=5000

A and C. Each of these conditions tests for SALARY values in the range of $2,000 to $5,000.

5. 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. The IS NULL operator correctly evaluates the COMMISSION_PCT column for NULL values. D uses the NOT operator to negate the already negative version of the IS NULL operator, IS NOT NULL. Two negatives return a positive, and therefore A and D are equivalent.

2. What languages can run within the database? (Choose all that apply.) A. SQL B. C C. PL/SQL D. Java E. Any other language linked to the OCI libraries

A, C, and D. SQL, PL/SQL, and Java can all run in the database. B and E are incorrect. C cannot run inside the database, and OCI is used by external processes to connect to the database; it does not run within it.

9. 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. A session-persistent substitution variable may be referenced using an ampersand symbol from within any SQL statement executed in that session

2. 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: 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. The IN operator efficiently tests whether the JOB_ID for a particular row is either SA_REP or MK_MAN, while the BETWEEN operator efficiently measures whether an employee's SALARY value falls within the required range.

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. The LIKE operator tests the DEPARTMENT_NAME column of each row for values that contain the characters "er". The percentage symbols before and after the character literal indicate that any characters enclosing the "er" literal are permissible.

8. 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. Positional sorting is performed, and the third term in the SELECT list, COMMISSION_ PCT, is sorted first in descending order, and any NULL COMMISSION_PCT values are listed last. The second term in the SELECT list, SALARY, is sorted next in ascending order

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. The SELECT clause facilitates projection by specifying the list of columns to be projected from a table, while the WHERE clause facilitates selection by limiting the rows retrieved based on its conditions.

3. 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. The character literals being compared to the JOB_ID column by the IN operator must be enclosed by single quotation marks.

7. 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.

C. The terms specified in an ORDER BY clause can include column names, positional sorting, numeric values, and expressions.

CHAR(size) column data type specifies fixed-length columns, where row space is preallocated to contain a fixed number of characters regardless of its contents. CHAR is much less commonly used than VARCHAR2. Unless the length of the data is predictable and constant, the CHAR data type utilizes storage inefficiently, padding any unused components with spaces.

CHAR is not used often because it is fixed length.

10. 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. The first time this statement is executed, two single ampersand substitution variables are encountered before the third double ampersand substitution variable. If the first reference on line one of the query contained a double ampersand substitution, you would only be prompted to input a value once.

VARCHAR2(length) data type columns store variable length alphanumeric character data, where length determines the maximum number of characters a column can contain.

VARCHAR2(255) this can store up to 255 characters.