Oracle Database 12c: SQL Fundamentals (1Z0-061)

Evaluate the SQL statement: SELECT LPAD (salary, 10, '*') FROM EMP WHERE EMP_ID = 1001; If the employee with the EMP_ID 1001 has a salary of 17000, what is displayed?

**17000.00

Assuming SYSDATE=30-DEC-2007, what value is returned after executing the following statement? SELECT TRUNC(SYSDATE,'YEAR') FROM DUAL; (Choose the best answer.)

01-JAN-2007

In the following SELECT statement, which component is a literal? SELECT 'Employee Name: ' || ename FROM emp WHERE deptno = 10;

10 & "Employee Name: "

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.

100

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

Which line of code has an error? 1. SELECT dname, ename 2. FROM emp e, dept d 3. WHERE emp.deptno = dept.deptno 4. ORDER BY 1, 2;

3

Which line in the following SQL has an error? 1 SELECT department_id, SUM(salary) 2 FROM employees 3 WHERE department_id <> 40 4 ORDER BY department_id;

4

What is the default precision for fractional seconds in a TIMESTAMP datatype column?

6

sWhich two statements are true regarding views?

A Data Manipulation Language (DML) operation can be performed on a view that is created with the sub query having all the NOT NULL columns of a table A view that is created with the sub query having the pseudo column ROWNUM keyword cannot be updated

The EMPLOYEES table has these columns: LAST NAME. VARCHAR2(35) SALARY. NUMBER(8, 2) HIRE_DATE. DATE Management wants to add a default value to the SALARY column. You plan to alter the table by using this SQL statement: ALTER TABLE EMPLOYEES MODIFY (SALARY DEFAULT 5000); What is true about your ALTER statement?

A change to the DEFAULT value affects only subsequent insertions to the table.

What are the distinguishing characteristics of a scalar subquery?

A scalar subquery returns one column. A scalar subquery returns one row.

The user Sue issues this SQL statement: GRANT SELECT ON sue.EMP TO alice WITH GRANT OPTION; The user Alice issues this SQL statement: GRANT SELECT ON sue.EMP TO reena WITH GRANT OPTION; The user Reena issues this SQL statement:GRANT SELECT ON sue.EMP TO timber; The user Sue issues this SQL statement: REVOKE select on sue.EMP FROM alice; For which users does the revoke command revoke SELECT privileges on the SUE.EMP table?

Alice, Reena, and Timber

Which of the following data types are variable length?

All but CHAR! BLOB, LONG, NUMBER, RAW, VARCHAR2

Two structures make up an Oracle server: an instance and a database. Which of the following best describes the difference between an Oracle instance and a database?

An instance consists of memory structures and processes, whereas a database is composed of physical files.

After describing the EMPLOYEES table, you discover that the SALARY column has a data type of NUMBER(8,2). Which SALARY values will not be permitted in this column?

Any number with more than 6 places before the decimal occupied

There are several steps involved in executing a DML statement. Place these in the correct order:

Apply the change vectors to the database buffer cache. Copy blocks from datafiles into buffers. Search for the relevant blocks in the database buffer cache. Write the change vectors to the log buffer.

The ORDERS TABLE belongs to the user OE. OE has granted the SELECT privilege on the ORDERS table to the user HR. Which statement would create a synonym ORD so that HR can execute the following query successfully?SELECT * FROM ord;

CREATE PUBLIC SYNONYM ord FOR oe.orders; This command is issued by the database administrator.

You need to perform these tasks: 1. Create and assign a MANAGER role to Blake and Clark 2. Grant CREATE TABLE and CREATE VIEW privileges to Blake and Clark Which set of SQL statements achieves the desired results?

CREATE ROLE manager; GRANT create table, create view TO manager; GRANT manager TO BLAKE, CLARK;

Which one is a system privilege?

CREATE TABLE

You need to design a student registration database that contains several tables storing academic information. The STUDENTS table stores information about a student. The STUDENT_GRADES table stores information about the student's grades. Both of the tables have a column named STUDENT_ID. The STUDENT_ID column in the STUDENTS table is a primary key. You need to create a foreign key on the STUDENT_ID column of the STUDENT_GRADES table that points to the STUDENT_ID column of the STUDENTS table. Which statement creates the foreign key?

CREATE TABLE student_grades(student_id NUMBER(12), semester_end DATE, gpa NUMBER(4, 3), CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));

Which function will return a TIMESTAMP WITH TIME ZONE datatype?

CURRENT_TIMESTAMP

Which two statements are true about sequences created in a single instance database?

CURRVAL is used to refer to the last sequence number that has been generated When the MAXVALUE limit for a sequence is reached, you can increase the MAXVALUE limit by using the ALTER SEQUENCE statement

How can you change the primary key value of a row?

Change it with a simple UPDATE statement.

Which tasks are accomplished by the SMON process?

Coalesces contiguous free space in dictionary-managed tablespaces & Performs recovery at instance startup

A constraint is created with the DEFERRABLE INITIALLY IMMEDIATE clause. What does this mean?

Constraint checking is done after each SQL statement is executed, but you can change this behavior by specifying SET CONSTRAINTS ALL DEFERRED.

Which two statements are true regarding the count function?

Count (*) returns the number of rows including duplicate rows and rows containing null value in any of the columns. Count (distinct inv_amt) returns the number of rows excluding rows containing duplicates and NULL values in the INV_AMT column.

Which are iSQL*Plus commands?

DESCRIBE

Which SQL statement would you use to remove a view called EMP_DEPT_VU from your schema?

DROP VIEW emp_dept_vu;

Which three tasks can be performed using SQL functions built into Oracle Database?

Displaying a date in a nondefault format Finding the number of characters in an expression Substituting a character string in a text expression with a specified string

User John has updated several rows in a table and issued a commit. What does the DBWn (database writer) process do at this time in response to the commit event?

Does nothing.

Which actions are allowed on a table that is marked as read-only?

Dropping a constraint Dropping an index Dropping a Table

Which statements are correct regarding indexes?

For each data manipulation language (DML) operation performed, the corresponding indexes are automatically updated. A nondeferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique index. When a table is dropped, the corresponding indexes are automatically dropped

The user Alice wants to grant all users query privileges on her DEPT table. Which SQL statement accomplishes this?

GRANT select ON dept TO PUBLIC;

If SYSDATE returns 12-JUL-2016, what is returned by the following statement? SELECT TO_CHAR(SYSDATE, 'fmMONTH, YEAR') FROM DUAL;

JULY, TWO THOUSAND SIXTEEN

Choose two statements that are true.

LISTAGG returns the concatenated values of the measure column. The STDDEV function returns the square root of the VARIANCE.

Which of these are single-row character-case conversion functions?

LOWER & INITCAP

Which of the following statements will not implicitly begin a transaction?

None of the above; they all implicitly begin a transaction, if not started already.

What is true about sequences?

Once created, a sequence belongs to a specific schema.

Which of the following best describes a RAC configuration?

One database, multiple instances

SQL is a set-oriented language. Which of these features is a consequence of this?

One statement can affect multiple rows.

Which of these commands will prevent other sessions from reading rows in the table?

Oracle does not provide a lock that will prevent others from reading a table.

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?

ROOPESH will see the old versions of the rows.

Which of the following options can be applied to B*Tree indexes but not to bitmap indexes?

Reverse key indexing Which of the following is not supported by Oracle as an internal data type? Uniqueness Compression

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?

SALARY=12345678 & SALARY=1234567.8

A query can have a sub query embedded within it. Under what circumstances could there be more than one sub query?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?

SELECT * FROM EMPLOYEES NATURAL JOIN DEPARTMENTS; Should have chosen SELECT * FROM EMPLOYEES JOIN DEPARTMENTS USING (DEPARTMENT_ ID,MANAGER_ID); Should have chosen SELECT * FROM EMPLOYEES E JOIN DEPARTMENTS D ON E.DEPARTMENT_ ID=D.DEPARTMENT_ID AND E.MANAGER_ID=D.MANAGER_ID;

Examine the description of the EMPLOYEES table: EMP_ID NUMBER(4) NOT NULL LAST_NAME VARCHAR2(30) NOT NULL FIRST_NAME VARCHAR2(30) DEPT_ID NUMBER(2) Which statement produces the number of different departments that have employees with last name Smith?

SELECT COUNT(DISTINCT dept_id) FROM employees WHERE last_name='Smith';

Which query creates a projection of the DEPARTMENT_NAME and LOCATION_ID columns from the DEPARTMENTS table?

SELECT DEPARTMENT_NAME, LOCATION_ID FROM DEPARTMENTS;

Which of the following statements are syntactically correct?

SELECT JH.JOB_ID FROM JOB_HISTORY JH RIGHT OUTER JOIN JOBS J ON JH.JOB_ID=J.JOB_ID;

Which statement would change all occurrences of the string 'IBM' to the string 'SUN' in the DESCRIPTION column of the VENDOR table?

SELECT REPLACE(description, 'IBM', 'SUN') FROM vendor

You need to write a SQL statement that returns employee name, salary, department ID, and maximum salary earned in the department of the employee for all employees who earn less than the maximum salary in their department. Which statement accomplishes this task?

SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) b WHERE a.dept_id = b.dept_id AND a.sal < b.maxsal;

John is trying to determine the average salary of employees in each department. He noticed that the SALARY column can have NULL values, and he does not want the NULLs included when the average is calculated. Identify the correct SQL statements that will produce the desired results.

SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;

Which SQL statements do not give an error?

SELECT last_name, e.hire_date, department_id FROM employees e JOIN (SELECT max(hire_date) max_hire_date FROM employees ORDER BY 1) me ON (e.hire_date = me.max_hire_date) SELECT last_name, e.hire_date, department_id FROM employees e WHERE (department_id, hire_date) IN (SELECT department_id, max(hire_date) hire_date FROM employees GROUP BY department_id)

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 the following is not supported by Oracle as an internal data type?

STRING

Which normal form is a table in if it has no multi-valued attributes and no partial dependencies?

Second normal form

Which date components does the CURRENT_TIMESTAMP function display?

Session date, session time, and session time zone offset

Choose two SGA structures that are required in every Oracle instance.

Shared pool & Buffer cache

Which of these commands will terminate a transaction?

TRUNCATE COMMIT ROLLBACK

Which statements are true regarding the WHERE and HAVING clauses in a SELECT statement?

The HAVING clause can be used with aggregate functions in subqueries. The WHERE clause can be used to exclude rows before dividing them into groups.

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

The JOIN...ON clause may be used for joins between multiple tables.

Choose one false statement about 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.

Carefully read the following SQL instructions, and choose the appropriate option. The JOB_ID column shows the various jobs. SELECT MAX(COUNT(*)) FROM employees GROUP BY job_id, department_id;

The SQL code will produce the highest number of jobs within a department.

Which two statements are true regarding constraints?

The column with a UNIQUE constraint can store NULLS . You can have more than one column in a table as part of a primary key.

Which statement is true regarding the INTERSECT operator?

The number of columns and data types must be identical for all SELECT statements in the query

The PRODUCTS table has these columns: PRODUCT_ID NUMBER(4) PRODUCT_NAME VARCHAR2(45) PRICE NUMBER(8, 2) Evaluate this SQL statement: SELECT * FROM PRODUCTS ORDER BY price, product_name; What is true about the SQL statement?

The results are sorted numerically and then alphabetically.

If several sessions request an exclusive lock on the same row, what will happen?

The sessions will be given an exclusive lock in the sequence in which they requested it.

Which two statements are true regarding single row functions?

They always return a single result row for every row of a queried table They can return a data type value different from the one that is referenced

The DEPARTMENTS table contains a DEPARTMENT_NAME column with data type VARCHAR2(30).

This column can store character data up to a maximum of 30 characters. This column can store data in a column with data type VARCHAR2(50) provided that the contents are at most 30 characters long.

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.

Two rows

Which of these set operators will not sort the rows?

UNION ALL

Which clause in a query restricts the rows selected?

WHERE

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 two are true about aggregate functions?

You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns. You can pass column names, expressions, constants, or functions as parameter to an aggregate function.

What is true about updates through a view?

You cannot update a view with group functions.

What are two reasons to create synonyms?

Your tables names are too long. Your tables have difficult names.

The account table contains these columns: ACCOUNT_ID NUMBER(12) NEW_BALANCE NUMBER(7,2) PREV_BALANCE NUMBER(7,2) FINANCE_CHARGE NUMBER(7,2) You need to create a single SELECT statement to accomplish these requirements: - Display accounts that have a new balance that is less than the previous balance. - Display accounts that have a finance charge that is less than $25.00. - Display accounts that have no finance charge. Evaluate this statement: SELECT account_id FROM account WHERE new_balance < prev_balance AND NVL(finance_charge, 0) < 25; How many of the three requirements will this SELECT statement accomplish?

all of the requirements

A data manipulation language statement _____.

modifies the data but not the structure of a table

Evaluate this SQL statement: SELECT e.emp_name, d.dept_name FROM employees e JOIN departments d USING (department_id) WHERE d.department_id NOT IN (10, 40) ORDER BY dept_name; The statement fails when executed. Which change fixes the error?

remove the table alias prefix from the WHERE clause

Which of the following is a correlated subquery?

select cty_name from city where st_code in (select st_code from state where st_name = 'TENNESSEE' and city.cnt_code = state.cnt_code);