[Q178-Q199] Oracle 1z0-071 Dumps Updated [Jun-2024] Get 100% Real Exam Questions!

NO.178 Which two tasks can be performed by using Oracle SQL statements? (Choose two.)

changing the password for an existing database user

connecting to a database instance

querying data from tables in different databases

starting up a database instance

executing operating system (OS) commands in a session

NO.179 View the Exhibit and examine the structure of the ORDERS table. The ORDER_ID column is the PRIMARY KEY in the ORDERS table.

Evaluate the following CREATE TABLE command:
CREATE TABLE new_orders(ord_id, ord_date DEFAULT SYSDATE, cus_id)
AS
SELECT order_id.order_date,customer_id
FROM orders;
Which statement is true regarding the above command?

The NEW_ODRDERS table would not get created because the DEFAULT value cannot be specified in the column definition.

The NEW_ODRDERS table would get created and only the NOT NULL constraint defined on the specified columns would be passed to the new table.

The NEW_ODRDERS table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.

The NEW_ODRDERS table would get created and all the constraints defined on the specified columns in the ORDERS table would be passed to the new table.

NO.180 Examine this statement which returns the name of each employee and their manager:

You want to extend the query to include managers with no employees. What must you add before join to do this?

FULL. OUTER

LEFT OUTER

RIGHT OUTER

CROSS

NO.181 Examine the structure of the MEMBERS table:

Examine the SQL statement:
SQL > SELECT city, last_name LNAME FROM MEMBERS ORDER BY 1, LNAME DESC;
What would be the result execution? (Choose the best answer.)

It displays all cities in descending order, within which the last names are further sorted in descending order.

It fails because a column alias cannot be used in the ORDER BY clause.

It fails because a column number and a column alias cannot be used together in the ORDER BY clause.

It displays all cities in ascending order, within which the last names are further sorted in descending order.

NO.182 The SQL statements executed in a user session are as follows:

Which two statements describe the consequences of issuing the ROLLBACK TO SAVE POINT acommand in the session? (Choose two.)

The rollback generates an error.

No SQL statements are rolled back.

Only the DELETEstatements are rolled back.

Only the second DELETEstatement is rolled back.

Both the DELETEstatements and the UPDATEstatement are rolled back.

NO.183 Examine the description of the EMPLOYEES table:

Which statement will fail?

SELECT department_id, COUNT (*)
FROM employees
HAVING department_ id <> 90 AND COUNT(*) >= 3
GROUP BY department_id;

SELECT department_id, COUNT (*)
FROM employees
WHERE department_ id <> 90 AND COUNT(*) >= 3
GROUP BY department_id;

SELECT department_id, COUNT(*)
FROM employees
WHERE department_id <> 90 HAVING COUNT(*) >= 3
GROUP BY department_id;

SELECT department_id, COUNT(*)
FROM employees
WHERE department_id <> 90 GROUP BY department_id
HAVING COUNT(*) >= 3;

NO.184 View the Exhibit and examine the structure in the DEPARTMENTS tables. (Choose two.)

Examine this SQL statement:
SELECT department_id “DEPT_ID”, department_name, ‘b’ FROM
departments
WHERE departments_id=90
UNION
SELECT department_id, department_name DEPT_NAME, ‘a’ FROM
departments
WHERE department_id=10
Which two ORDER BY clauses can be used to sort the output?

ORDER BY DEPT_NAME;

ORDER BY DEPT_ID;

ORDER BY ‘b’;

ORDER BY 3;

NO.185 Examine the description of the ORDER_ITEMS table:

Examine this incomplete query:
SELECT DISTINCT quantity * unit_price total_paid FROM order_items ORDER BY <clause>;
Which two can replace <clause> so the query completes successfully?

quantity

quantity, unit_price

total_paid

product_id

quantity * unit_price

NO.186 Which three statements are true regarding the data types?

Only one LONG column can be used per table.

ATIMESTAMP data type column stores only time values with fractional seconds.

The BLOB data type column is used to store binary data in an operating system file.

The minimum column width that can be specified for a varchar2 data type column is one.

The value for a CHAR data type column is blank-padded to the maximum defined column width.

NO.187 Examine the structure of the SALES table.

Examine this statement:

Which two statements are true about the SALES1 table? (Choose two.)

It will not be created because the column-specified names in the SELECT and CREATE TABLE clauses do not match.

It will have NOT NULL constraints on the selected columns which had those constraints in the SALES table.

It will not be created because of the invalid WHERE clause.

It is created with no rows.

It has PRIMARY KEY and UNIQUE constraints on the selected columns which had those constraints in the SALES table.

NO.188 Which two statements are true regarding multiple-row subqueries? (Choose two.)

They can contain group functions.

They always contain a subquery within a subquery.

They use the < ALL operator to imply less than the maximum.

They can be used to retrieve multiple rows from a single table only.

They should not be used with the NOT IN operator in the main query if NULL is likely to be a part of the result of the subquery.

NO.189 Which statement is true about an inner join specified in the WHERE clause of a query?

It must have primary-key and foreign key constraints defined on the columns used in the join condition.

It requires the column names to be the same in all tables used for the join conditions.

It is applicable for equijoin and nonequijoin conditions.

It is applicable for only equijoin conditions.

NO.190 View the Exhibits and examine the structure of the COSTS and PROMOTIONS tables.
You want to display PROD IDS whose promotion cost is less than the highest cost PROD ID in a pro motion time interval.
Examine this SQL statement:
SELECT prod id
FROM costs
WHERE promo id IN
(SELECT promo id
FROM promotions
WHERE promo_cost < ALL
(SELECT MAX (promo cost)
FROM promotions
GROUP BY (promo_end date-promo_begin_date)) );
What will be the result?

It executes successfully but does not give the required result.

It gives an error because the ALL keyword is not valid.

It gives an error because the GROUP BY clause is not valid

It executes successfully and gives the required result.

NO.191 View the exhibit for the structure of the STUDENT and FACULTY tables.

You need to display the faculty name followed by the number of students handled by the faculty at the base location.
Examine the following two SQL statements:
Statement 1
SQL>SELECT faculty_name, COUNT(student_id)
FROM student JOIN faculty
USING (faculty_id, location_id)
GROUP BY faculty_name;
Statement 2
SQL>SELECT faculty_name, COUNT(student_id)
FROM student NATURAL JOIN faculty
GROUP BY faculty_name;
Which statement is true regarding the outcome?

Only statement 2 executes successfully and gives the required result.

Only statement 1 executes successfully and gives the required result.

Both statements 1 and 2 execute successfully and give different results.

Both statements 1 and 2 execute successfully and give the same required result.

NO.192 Which two statements are true? (Choose two.)

The USER SYNONYMS view can provide information about private synonyms.

The user SYSTEM owns all the base tables and user-accessible views of the data dictionary.

All the dynamic performance views prefixed with V$ are accessible to all the database users.

The USER OBJECTS view can provide information about the tables and views created by the user only.

DICTIONARY is a view that contains the names of all the data dictionary views that the user can access.

NO.193 Examine this statement:
SELECT cust_id, cust^last_ndma “Last Name”
FROM customers
WHERE country_id – 10 UNION
SELECT cust_ID_CUST_NO, cust_last_name
PROM customers
WHERE country__id = 30
Identify three order by clauses, any one of which will complete the query successfully.

ORDER BY 2. cust _id

ORDER BY 2,

ORDER BY “CUST^NO

ORDER BY “Last Name

ORDER BY CUST_NO

NO.194 Which statements are true? (Choose all that apply.)

The data dictionary is created and maintained by the database administrator.

The data dictionary views consists of joins of dictionary base tables and user-defined tables.

The usernames of all the users including the database administrators are stored in the data dictionary.

The USER_CONS_COLUMNSview should be queried to find the names of the columns to which a constraint applies.

Both USER_OBJECTSand CATviews provide the same information about all the objects that are owned by the user.

Views with the same name but different prefixes, such as DBA, ALLand USER, use the same base tables from the data dictionary.

NO.195 Which two tasks can be performed by using Oracle SQL statements?

changing the password for an existing database user

connecting to a database instance

querying data from tables across databases

starting up a database instance

executing operating system (OS) commands in a session

NO.196 Examine the description of the EMPLOYEES table:

Which two statements will run successfully?

SELECT ‘The first_name is ” || first_name || ” FROM employees ;

SELECT ‘The first_name is ”’||first_name ||”” FROM employees ;

SELECT ‘The first_name is ”’ ||first_name||”’ FROM employees ;

SELECT ‘The first_name is ‘|| first_name|| ” FROM employees;

SELECT ‘The first_name is ” || first_name || ”’ FROM employees;

NO.197 The user SCOTT who is the owner of ORDERS and ORDER_ITEMS tables issues this GRANT command:
GRANT ALL
ON orders, order_items
TO PUBLIC;
What must be done to fix the statement?

PUBLIC should be replaced with specific usernames.

ALL should be replaced with a list of specific privileges.

WITH GRANT OPTION should be added to the statement.

Separate GRANT statements are required for the ORDERS and ORDER_ITEMS tables.

NO.198 View the Exhibit and examine the structure in the EMPLOYEES tables.

Evaluate the following SQL statement:
SELECT employee_id, department_id
FROM employees
WHERE department_id= 50 ORDER BY department_id
UNION
SELECT employee_id, department_id
FROM employees
WHERE department_id= 90
UNION
SELECT employee_id, department_id
FROM employees
WHERE department_id= 10;
What would be the outcome of the above SQL statement?

The statement would not execute because the positional notation instead of the column name should be used with the ORDER BY clause.

The statement would execute successfully and display all the rows in the ascending order of DEPARTMENT_ID.

The statement would execute successfully but it will ignore the ORDER BY clause and display the rows in random order.

The statement would not execute because the ORDER BY clause should appear only at the end of the SQL statement, that is, in the last SELECT statement.

NO.199 View the Exhibit and examine the structure of ORDERSand CUSTOMERStables.

You executed this UPDATEstatement:

Which statement is true regarding the execution? (Choose the best answer.)

It would not execute because a subquery cannot be used in the WHEREclause of an UPDATEstatement.

It would not execute because two tables cannot be referenced in a single UPDATEstatement.

It would execute and restrict modifications to the columns specified in the SELECTstatement.

It would not execute because a SELECTstatement cannot be used in place of a table name.

Leave a Reply Cancel reply