Oracle SQL 061 questions

Which two statements regarding substitution variables are true?

&n (where n is any integer) variables are defined by SQL*Plus when values are passed in as arguments to the script, and their values will be available for the duration of the session. &&variable is defined by SQL*Plus, and its value will be available for the duration of the session.

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.

1

How many rows of data are returned after executing the following statement? select listagg (last_name) within group (order by departent_id) ​from employees; Assume the EMPLOYEES table has ten distinct DEPARTMENT_ID values and 107 rows.

1

What is the default length of a CHAR column if no length is specified in the table definition?

1

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 nonnull and 1 null unique DEPARTMENT_ID values. All records have a nonnull SALARY value.

12

Review the following code snippet. Which line has an error? 1. UPDATE EMPLOYEES 2. WHERE EMPLOYEE_ID = 127 3. SET SALARY = SALARY * 1.25, 4. COMMISSION_PCT = 0

2

Consider the following code segment. How many rows will be in the CARS table after all these statements are executed?

29

What would be the result of executing the following SQL, if today's date were February 28, 2009? SELECT ADD_MONTHS('28-FEB-09', -12) from dual;

29-FEB-08

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

2nd normal form

Review the following code snippet. Which line has an error? 1. UPDATE EMPLOYEES 2. WHERE EMPLOYEE_ID = 127 3. SET SALARY = SALARY * 1.25, 4. COMMISSION_PCT = 0

4

Which of the following are requirements for using SQL Developer? (Choose two correct answers.)

A graphical terminal A Java Runtime Environment

What are the distinguishing characteristics of a scalar subquery? (Choose two correct answers.)

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

You are the DBA for an academic database. You need to create a role that allows a group of users to modify existing rows in the STUDENT_GRADES table. Which set of statements accomplishes this?

CREATE ROLE registrar; GRANT UPDATE ON student_grades TO registrar; GRANT registrar to user1, user2, user3;

You are granted the CREATE VIEW privilege. What does this allow you to do?

Create a view in your schema.

View the Exhibit and examine the structure of the PRODUCTS table. Which two tasks would require subqueries?

Display the number of products whose list price is more than the average list price. Display all products whose minimum list price is more than the average list price of products and have the status 'orderable'.

Which statements are correct regarding indexes? (Choose all that apply.)

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

Which two operators are not allowed when using an outer join operator in the query?

IN OR

Which of these operators will remove duplicate rows from the final result?

INTERSECT MINUS UNION

Which two will occur when a back-end server that is registered with a back end set is marked to drain connections?

It disallows new connections to that back-end server. It immediately closes all existing connections to that instance.

The PART_CODE column in the SPARES table contains the following list of values: Which statement is true regarding the outcome of the above query?

It displays only the values A%_WQ123 and A%BWQ123 .

Examine the data in the ORD_ITEMS table: Evaluate the following query: Which statement is true regarding the outcome of the above query?

It displays the item nos with their average quantity where the average quantity is more than double the minimum quantity of that item in the table.

You want to update the employees table as follows: - Update only those employees who work in Boston or Seattle (locations 2900 and 2700). - Set department_id for these employees to the department_id corresponding to London (location_id 2100). - Set the employees' salary in iocation_id 2100 to 1.1 times the average salary of their department. - Set the employees' commission in iocation_id 2100 to 1.5 times the average commission of their department. You issue the following command: What is the outcome?

It executes successfully but does not give the correct result.

View the Exhibit and examine the data in the products table. You need to display product names from the products table that belong to the 'software/other' category with minimum prices as either S2000 or S4000 and no unit of measure. You issue the following query:

It executes successfully but returns no result.

When a COMMIT is issued, what will happen?

LGWR writes the log buffer to disk

Examine the data in the LIST_PRICE and MIN_PRICE columns of the PRODUCTS table: Which two expressions give the same output?

NVL(COALESCE(list_price, min_price), 0) COALESCE(NVL2(list_price, list_price, min_price), 0)

Click the Exhibit(s) button to examine the structures of the EMPLOYEE and TASK tables. You need to produce a report containing all employees and all tasks. An employee must be included on the report even if he has no tasks assigned. All tasks, whether assigned to an employee or not, must also be included on the report. Which SELECT statement should you use?

None of the options will produce the desired result.

Examine the structure of the products table: You want to display the names of the products that have the highest total value for UNIT_PRICE * QTY_IN_HAND. Which SQL statement gives the required output?

Option A

View the Exhibit and examine the structure of the product, component, and PDT_COMP tables. In product table, PDTNO is the primary key. In component table, COMPNO is the primary key. In PDT_COMP table, <PDTNO, COMPNO) is the primary key, PDTNO is the foreign key referencing PDTNO in product table and COMPNO is the foreign key referencing the COMPNO in component table. You want to generate a report listing the product names and their corresponding component names, if the component names and product names exist. Evaluate the following query: SQL>SELECT pdtno, pdtname, compno, compname FROM product _____________ pdt_comp USING (pdtno) ____________ component USING (compno) WHERE compname IS NOT NULL; Which combination of joins used in the blanks in the above query gives the correct output?

RIGHT OUTER JOIN; LEFT OUTER JOIN

Which statement reports on unique JOB_ID values from the EMPLOYEES table?

SELECT DISTINCT JOB_ID FROM EMPLOYEES;

See the structure of the PROGRAMS table: Which two SQL statements would execute successfully?

SELECT NVL(ADD_MONTHS(END_DATE, 1), SYSDATE) FROM programs; SELECT NVL(TO_CHAR(MONTHS_BETWEEN (start_date, end_date)), 'Ongoing') FROM programs;

View the Exhibit and examine the structure of the CUSTOMERS table .Which statement would display thehighest credit limit available in each income level in each city in the CUSTOMERS table?

SELECT cust_city, cust_income_level, MAX(cust_credit_limit) FROM customers GROUP BY cust_city, cust_income_level;

See the Exhibit and examine the structure of the PROMOSTIONS table: Exhibit: Which SQL statements are valid?

SELECT promo_id, DECODE(NVL(promo_cost, 0), promo_cost, promo_cost * 0.25, 100) "Discount" FROM promotions; SELECT promo_id, DECODE(promo_cost, 10000, DECODE(promo_category, 'G1', promo_cost *.25, NULL), NULL) "Catcost" FROM promotions;

Which SQL statement returns a numeric value?

SELECT sysdate-hire_date FROM EMP;

Dates are stored in the default date format dd-mon-rr in the CUST_TRANS table. Which three SQL statements would execute successfully?

SELECT transamt FROM cust_trans WHERE custno > "11"; SELECT * FROM cust_trans WHERE transdate='01-JANUARY-07'; SELECT transdate + '10' FROM cust_trans;

Which database tools are parts of Oracle Database 12c?

SQL Developer Oracle Enterprise Manager Database Express 12c

SQL Developer is a tool primarily for whom?

Should have chosen All of the above Application developers End users Database administrators

Refer to the DEPARTMENTS table structure. Two SQL statements are shown here. Choose the option that best describes the SQL statements.

Statements 1 and 2 insert different values to at least one column in the table.

Which of these commands will terminate a transaction? (Choose three correct answers.)

TRUNCATE COMMIT ROLLBACK

Which two statements are true regarding working with dates?

The RR date format automatically calculates the century from the SYSDATE function but allows the user to enter the century if required The default internal storage of dates is in the numeric format

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

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

Using the products table, you issue the following query to generate the names, current list price, and discounted list price for all those products whose list price falls below $10 after a discount of 25% is applied on it. The query generates an error. What is the reason for the error?

The column alias should be replaced with the expression in the where clause.

Which statement is true regarding tables?

The same table name can be used in different schemas in the same database.

If several sessions request an exclusive lock on the same row, what will happen? (Choose the best answer.)

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

Which three statements are true regarding the data types in Oracle Database 10g/11g?

The value for a CHAR data type column is blank-padded to the maximum defined column width. Only One LONG column can be used per table The minimum column width that can be specified for a VARCHAR2 data type column is one

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

The command to create a table fails. Identify the two reasons for the SQL statement failure?

You cannot use SYSDATE in the condition of a check constraint. You cannot use ORD_NO and ITEM_NO columns as a composite primary key because ORD_NO is also the foreign key.

Evaluate the SQL statement: TRUNCATE TABLE DEPT; Which three are true about the SQL statement?

You must be the owner of the table or have DELETE ANY TABLE system privileges to truncate the DEPT table It releases the storage space used by the table. You can NOT roll back the deletion of rows after the statement executes.

View the Exhibit and examine the structure and data in the INVOICE table. Which statements are true regarding data type conversion in expressions used in queries?

inv_date = '15-february-2008′ : uses implicit conversion inv_no BETWEEN '101' AND '110' : uses implicit conversion

Which one of the following Oracle SQL*Plus command lines is not valid?

sqlplus @<connect_string>

Evaluate the following two queries: Exhibit: Exhibit: Which statement is true regarding the above two queries?

there would be no change in performance