[2022] Get Top-Rated Oracle 1z0-071 Exam Dumps Now [Q119-Q133]

4/5 - (1 Abstimmung)

[2022] Get Top-Rated Oracle 1z0-071 Exam Dumps Now

Passing Key To Getting 1z0-071 Certified Exam Engine PDF

NO.119 You want to display the date for the first Monday of the next month and issue the following command:

Was ist das Ergebnis?

In generates an error because rrrrshould be replaced by rrin the format string.

It executes successfully but does not return the correct result.

It executes successfully and returns the correct result.

In generates an error because TO_CHARshould be replaced with TO_DATE.

In generates an error because fmand double quotation marks should not be used in the format string.

NR. 120 You need to produce a report where each customer’s credit limit has been incremented by $1000. In the output, the customer’s last name should have the heading Name and the incremented credit limit should be labeled New Credit Limit. The column headings should have only the first letter of each word in uppercase.
Which statement would accomplish this requirement?

SELECT cust_last_name AS “Name”, cust_credit_limit + 1000AS “New Credit Limit”FROM customers;

SELECT cust_last_name AS Name, cust_credit_limit + 1000AS New Credit LimitFROM customers;

SELECT cust_last_name AS Name, cust_credit_limit + 1000″New Credit Limit”FROM customers;

SELECT INITCAP (cust_last_name) “Name”, cust_credit_limit + 1000INITCAP (“NEW CREDIT LIMIT”)FROM customers;

NO.121 View the exhibit and examine the structure of the SALES, CUSTOMERS, PRODUCTS and TIMES tables.

The PROD_ID column is the foreign key in the SALES tables, which references the PRODUCTS table.
Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.
Evaluate the following CREATE TABLE command:
CREATE TABLE new_sales (prod_id, cust_id, order_date DEFAULT SYSDATE)
AS
SELECT prod_id, cust_id, time_id
FROM sales;
Which statement is true regarding the above command?

The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table.

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

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

The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table.

NR. 122 Examine the structure of the MEMBERS table: (Choose the best answer.)

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

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.

NR. 123 View the Exhibit and examine the structure of the SALES and PRODUCTS tables. (Choose two.)

In the SALES table, PROD_ID is the foreign key referencing PROD_ID in the PRODUCTS table. You must list each product ID and the number of times it has been sold.
Examine this query which is missing a JOIN operator:
SQL > SELECT p.prod_id, count(s.prod_id)
FROM products p ______________ sales s
ON p.prod_id = s.prod_id
GROUP BY p.prod_id;
Which two JOIN operations can be used to obtain the required output?

FULL OUTER JOIN

JOIN

LEFT OUETR JOIN

RIGHT OUTER JOIN

NR. 124 View the exhibits and examine the structures of the COSTS and PROMOTIONS tables.

Evaluate the following SQL statement:

What would be the outcome of the above SQL statement?

It displays prod IDs in the promo with the lowest cost.

It displays prod IDs in the promos with the lowest cost in the same time interval.

It displays prod IDs in the promos with the highest cost in the same time interval.

It displays prod IDs in the promos which cost less than the highest cost in the same time interval.

NR. 125 Examine the business rule:
Each student can work on multiple projects and each project can have multiple students.
You must design an Entity Relationship (ER) model for optimal data storage and allow for generating reports in this format:
STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME PROJECT_TASK
Welche beiden Aussagen sind richtig? (Wählen Sie zwei aus.)

The ER must have a 1-to-many relationship between the STUDENTSand PROJECTSentities.

The ER must have a many-to-many relationship between the STUDENTSand PROJECTS entities that must be resolved into 1-to-many relationships.

STUDENT_IDmust be the primary key in the STUDENTSentity and foreign key in the PROJECTSentity.

PROJECT_IDmust be the primary key in the PROJECTSentity and foreign key in the STUDENTSentity.

An associative table must be created with a composite key of STUDENT_ID and PROJECT_ID, which is the foreign key linked to the STUDENTS and PROJECTS entities.

NR. 126 Examine the structure of the DEPARTMENTS table.

You execute the following command:

Welche beiden Aussagen sind richtig?

Synonyms existing of the DEPARTMENTS table would have to be re-created.

Unique key constraints defined on the COUNTRY column are removed.

Views created in the DEPARTMENTS table that include the COUNTRY column are automatically modified and remain valid.

Indexes created on the COUNTRY column exist until the DROP UNUSED COLUMNS command is executed.

A new column, COUNTRY, can be added to the DEPARTMENTS table after executing the command.

NR. 127 View the Exhibit and examine the structure of the ORDER_ITEMS table.

You must select the ORDER_ID of the order that has the highest total value among all the orders in the ORDER_ITEMS table.
Which query would produce the desired result?

SELECT order_idFROM order_itemsGROUP BY order_idHAVING SUM(unit_price*quantity) = (SELECT MAX(SUM(unit_price*quantity))FROM order_items GROUP BY order_id);

SELECT order_idFROM order_itemsWHERE(unit_price*quantity) = (SELECT
MAX(unit_price*quantity)FROM order_items)GROUP BY order_id;

SELECT order_idFROM order_itemsWHERE(unit_price*quantity) =
MAX(unit_price*quantity)GROUP BY order_id;

SELECT order_idFROM order_itemsWHERE (unit_price*quantity) = (SELECT MAX(unit_price*quantity)FROM order_itemsGROUP BY order_id)

NR. 128 Which two statements are true about the results of using the INTERSECT operator in compound queries?

INTERSECT ignores NULLs.

Reversing the order of the intersected tables can sometimes affect the output.

The number of columns in each SELECT in the compound query can be different.

INTERSECT returns rows common to both sides of the compound query.

Column names in each SELECT in the compound query can be different.

NR. 129 You must create a SALES table with these column specifications and data types:
SALESID: Number
STOREID: Number
ITEMID: Number
QTY: Number, should be set to 1 when no value is specified
SLSDATE: Date, should be set to current date when no value is specified PAYMENT: Characters up to 30 characters, should be set to CASH when no value is specified Which statement would create the table? (Choose the best answer.)

CREATE TABLE sales(salesid NUMBER(4),storeid NUMBER(4),itemid NUMBER(4),qty NUMBER DEFAULT = 1,slsdate DATE DEFAULT SYSDATE,payment VARCHAR2(30) DEFAULT = “CASH”);

CREATE TABLE sales(salesid NUMBER(4),storeid NUMBER(4),itemid NUMBER(4),qty NUMBER DEFAULT 1,slsdate DATE DEFAULT ‘SYSDATE’,payment VARCHAR2(30) DEFAULT CASH);

CREATE TABLE sales(salesid NUMBER(4),storeid NUMBER(4),itemid NUMBER(4),qty NUMBER DEFAULT = 1,slsdate DATE DEFAULT SYSDATE,payment VARCHAR2(30) DEFAULT = “CASH”);

CREATE TABLE sales(salesid NUMBER(4),storeid NUMBER(4),itemid NUMBER(4),qty NUMBER DEFAULT 1,slsdate DATE DEFAULT SYSDATE,payment VARCHAR2(30) DEFAULT ‘CASH’);

NR. 130 In the EMPLOYEES table there are 1000 rows and employees are working in the company for more than
10 years.
Evaluate the following SQL statement:

What would be the result?

It executes successfully but no rows updated.

It executes successfully and updates the records of those employees who have been working in the company for more than 600 days.

It gives an error because multiple NVL functions are used in an expression.

It gives an error because NVL function cannot be used with UPDATE.

NR. 131 Which three statements are correct regarding indexes? (Choose three.)

A non-deferrable PRIMARY KEY or UNIQUE KEY constraint in a table automatically attempts to create a unique index.

Indexes should be created on columns that are frequently referenced as part of any expression.

When a table is dropped, corresponding indexes are automatically dropped.

For each DML operation performed on a table, the corresponding indexes are automatically updated if required.

NO.132 View the Exhibits and examine PRODUCTS and SALES tables.
Exhibit 1

Exhibit 2

You issue the following query to display product name the number of times the product has been sold:

What happens when the above statement is executed?

The statement executes successfully and produces the required output.

The statement produces an error because a subquery in the FROM clause and outer-joins cannot be used together.

The statement produces an error because the GROUP BY clause cannot be used in a subquery in the FROM clause.

The statement produces an error because ITEM_CNT cannot be displayed in the outer query.

NR. 133 View the Exhibit and examine the details of PRODUCT_INFORMATIONtable.

You have the requirement to display PRODUCT_NAMEfrom the table where the CATEGORY_IDcolumn has values 12or 13, and the SUPPLIER_IDcolumn has the value 102088. You executed the following SQL statement:
SELECT product_name
FROM product_information
WHERE (category_id = 12 AND category_id = 13) AND supplier_id = 102088; Which statement is true regarding the execution of the query?