OCA 1Z0-071 12C - Parte 2

Das ist ein zeitlich begrenztes Quiz.

Du hast 2 Stunden um die 78 Fragen in diesem Quiz zu beantworten.

Starte das Quiz

Bitte warte - es wird geladen…

Frage 1 von 78

Which statement is true about Data Manipulation Language (DML)?

Wähle eine der folgenden:

DML automatically disables foreign ley constraints when modifying primary key values in the parent table
Each DML statement forms a transaction by default.
A transaction can consist of one or more DML statements.
DML disables foreign key constraints when deleting primary key values in the parent table, only when the ON DELETE CASCADE option is set for the foreigr constraint.

Erklärung

Frage 2 von 78

See the Exhibit and examine the structure of the PROMOTIONS table:

You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the INTERNET category.

Which query would give you the required output?

Wähle eine der folgenden:

SELECT promo_name, promo_begin_date
FROM promotions
WHERE promo_begin_date> ALL
(SELECT MAX (promo_begin_date)
FROM promotions)
AND promo_category= 'INTERNET';
SELECT promo_name, promo_begin_date
FROM promotions
WHERE promo_begin_date IN (SELECT promo_begin_date
FROM promotions
WHERE promo_category= 'INTERNET');
SELECT promo_name, promo_begin_date
FROM promotions
WHERE promo_begin_date > ALL
(SELECT promo_begin_date
FROM promotions
WHERE promo_category = 'INTERNET');
SELECT promo_name, promo_begin_date
FROM promotions
WHERE promo_begin_date> ANY (SELECT promo_begin_date
FROM promotions
WHERE promo_category= 'INTERNET');

Erklärung

Frage 3 von 78

Which two statements are true about sequences created in a single instance Oracle database? (Choose two.)

Wähle eine oder mehr der folgenden:

When the MAXVALUE limit for a sequence is reached, it can be increased by using the ALTER SEQUENCE statement.
DELETE <sequencename> would remove a sequence from the database.
The numbers generated by an explicitly defined sequence can only be used to insert data in one table.
CURRVAL is used to refer to the most recent sequence number that has been generated for a particular sequence.
When a database instance shuts down abnormally, sequence numbers that have been cached but not used are available again when the instance is restarted.

Erklärung

Frage 4 von 78

Evaluate the following CREATE TABLE command:
Which statement is true regarding the above SQL statement?

Wähle eine der folgenden:

It would execute successfully and only ORD_ITM_IDX index would be created.
It would give an error because the USING INDEX clause cannot be used on a composite primary.
It would execute successfully and two indexes ORD_ITM_IDX and ORD_ITM_ID PK would be created.
It would give an error because the USING INDEX is not permitted in the CREATE TABLE command.

Erklärung

Frage 5 von 78

Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit amount
in each income level. The report should NOT show any repeated credit amounts in each income level.
Which query would give the required result?

Wähle eine der folgenden:

SELECT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS "50% Credit Limit"
FROM customers;
SELECT DISTINCT cust_income_level, DISTINCT cust_credit_limit * 0.50 AS "50% Credit Limit"
FROM customers;
SELECT DISTINCT cust_income_level ||‘ ‘|| cust_credit_limit * 0.50 AS "50% Credit Limit"
FROM customers;
SELECT cust_income_level ||‘ ‘ ||cust_credit_limit * 0.50 AS "50% Credit Limit" FROM customers;

Erklärung

Frage 6 von 78

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

Wähle eine oder mehr der folgenden:

WHERE and HAVING clauses cannot be used together in a SQL statement.
The HAVING clause conditions can have aggregate functions.
The HAVING clause conditions can use aliases for the columns.
The WHERE clause is used to exclude rows before the grouping of data.
The HAVING clause is used to exclude one or more aggregated results after grouping data.

Erklärung

Frage 7 von 78

You need to display the date 11-Oct-2007 in words as ‘Eleventh of October, Two Thousand Seven’. Which SQL statement would give the required result?

Wähle eine der folgenden:

SELECT TO_CHAR(’11-oct-2007′, ‘fmDdspth "of" Month, Year’) FROM DUAL;
SELECT TO_CHAR(TO_DATE(’11-oct-2007′), ‘fmDdspth of month, year’) FROM DUAL;
SELECT TO_CHAR(TO_DATE(’11-oct-2007′), ‘fmDdthsp "of" Month, Year’) FROM DUAL;
SELECT TO_DATE(TO_CHAR(’11-oct-2007′,’fmDdspth ”of” Month, Year’)) FROM DUAL;

Erklärung

Frage 8 von 78

Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS

SQL>CREATE TABLE DEPARTMENT_DETAILS
(DEPARTMENT_ID NUMBER PRIMARY KEY,
DEPARTMENT_NAME VARCHAR2(50),
HOD VARCHAR2(50));

SQL>CREATE TABLE COURSE_DETAILS
(COURSE_ID NUMBER PRIMARY KEY,
COURSE_NAME VARCHAR2(50),
DEPARTMENT_ID NUMBER REFERENCES DEPARTMENT_DETAILS(DEPARTMENT_ID));

You want to generate a report that shows all course IDs irrespective of whether they have corresponding department IDs or not but no department IDs if they do not have any courses.
Which SQL statement must you use?

Wähle eine der folgenden:

SELECT course_id, department_id
FROM department_details d
RIGHT OUTER JOIN course_details c
USING(department_id)
SELECT c.course_id, d.department_id
FROM course_details c
RIGHT OUTER JOIN department_details d
ON (c.department_id=d. department_id);
SELECT c.course_id, d.department_id
FROM course_details c
FULL OUTER JOIN department_details d
ON (c.department_id=d. department_id);
SELECT c.course_id, d.department_id
FROM course_details c
FULL OUTER JOIN department_details d
ON (c.department_id <> d. department_id);

Erklärung

Frage 9 von 78

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 table referencing the PRODUCTS table.

The CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.

Examine this 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?

Wähle eine der folgenden:

The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the selected columns from the SALES table would be created on the corresponding columns in the NEW_SALES table.
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 not get created because the DEFAULT value cannot be specified in the column definition.
The NEW_SALES table would get created and all the NOT NULL constraints defined on the selected columns from the SALES table would be created on the corresponding columns in the NEW_SALES table.

Erklärung

Frage 10 von 78

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?

Wähle eine der folgenden:

The NEW_ORDERS table would not get created because the DEFAULT value cannot be specified in the column definition.
The NEW_ORDERS table would get created and only the NOT NULL constraint defined on the specified columns would be passed to the new table.
The NEW_ORDERS table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
The NEW_ORDERS table would get created and all the constraints defined on the specified columns in the ORDERS table would be passed to the new table.

Erklärung

Frage 11 von 78

Evaluate the following statement:

INSERT ALL
WHEN order_total < 10000 THEN
INTO small_orders
WHEN order_total > 10000 AND order_total < 20000 THEN INTO medium_orders
WHEN order_total > 2000000 THEN
INTO large_orders
SELECT order_id, order_total, customer_id
FROM orders;

Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement?

Wähle eine der folgenden:

They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause.
They are evaluated by the first WHEN clause. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses.
They are evaluated by the first WHEN clause. If the condition is false, then the row would be evaluated by the subsequent WHEN clauses.
The INSERT statement would give an error because the ELSE clause is not present for support in case none of the WHEN clauses are true.

Erklärung

Frage 12 von 78

Which two statements are true regarding the SQL GROUP BY clause?

Wähle eine oder mehr der folgenden:

You can use a column alias in the GROUP BY clause.
Using the WHERE clause after the GROUP BY clause excludes rows after creating groups.
The GROUP BY clause is mandatory if you are using an aggregating function in the SELECT clause.
Using the WHERE clause before the GROUP BY clause excludes rows before creating groups.
If the SELECT clause has an aggregating function, then columns without an aggregating function in the SELECT clause should be included in the GROUP BY clause.

Erklärung

Frage 13 von 78

You issue this command which succeeds:
SQL> DROP TABLE products;
Which three statements are true?

Wähle eine oder mehr der folgenden:

All existing views and synonyms that refer to the table are invalidated but retained.
Any uncommitted transaction in the session is committed.
Table data and the table structure are deleted.
All the table-s indexes if any exist, are invalidated but retained.
Table data is deleted but the table structure is retained.

Erklärung

Frage 14 von 78

You execute the SQL statement:
SQL> CREATE TABLE citizens
(citizen_id CHAR (10) PRIMARY KEY,
last_name VARCHAR2 (50) NOT NULL,
first_name VARCHAR2 (50),
address VARCHAR2 (100),
city VARCHAR2 (30) NOT NULL DEFAULT 'SEATTLE',
CONSTRAINT cnames CHECK (first_name<>last_name) );
What is the outcome?

Wähle eine der folgenden:

It succeeds and CITY can contain only 'SEATTLE' or null for all rows.
It fails because the condition for the CANAMES constraint is not valid.
It fails because the DEFAULT options cannot be after the sentence NOT NULL.
It succeeds and an index is crated for CITIZEN_ID.

Erklärung

Frage 15 von 78

Evaluate the following CREATE TABLE commands:
CREATE TABLE orders
(ord_no NUMBER(2) CONSTRAINT ord_pk PRIMARY KEY,
ord_date DATE,
cust_id NUMBER(4));
CREATE TABLE ord_items
(ord_no NUMBER(2),
item_no NUMBER(3),
qty NUMBER(3) CHECK (qty BETWEEN 100 AND 200),
expiry_date date CHECK (expiry_date > SYSDATE),
CONSTRAINT it_pk PRIMARY KEY (ord_no,item_no),
CONSTRAINT ord_fk FOREIGN KEY(ord_no) REFERENCES orders(ord_no));
The above command fails when executed. What could be the reason?

Wähle eine der folgenden:

SYSDATE cannot be used with the CHECK constraint.
The BETWEEN clause cannot be used for the CHECK constraint.
The CHECK constraint cannot be placed on columns having the DATE data type.
ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY.

Erklärung

Frage 16 von 78

Examine the structure of the PROGRAMS table:

Which two SQL statements would execute successfully?

Wähle eine oder mehr der folgenden:

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

Erklärung

Frage 17 von 78

View the Exhibit and examine the structure of the CUSTOMERS table.

Using the CUSTOMERS table, you must generate a report that displays a credit limit increase of 15% for all customers.

Customers with no credit limit should have 'Not Available' displayed.

Which SQL statement would produce the required result?

Wähle eine der folgenden:

SELECT NVL (TO_CHAR(cust_credit_limit*.15), 'Not Available') 'NEW CREDIT' FROM customers
SELECT TO_CHAR(NVL(cust_credit_limit*.15), 'Not Available')) 'NEW CREDIT' FROM customers
SELECT NVL (cust_credit_limit*.15, 'Not Available') 'NEW CREDIT' FROM customers
SELECT NVL (cust_credit_limit, 'Not Available')*.15 'NEW CREDIT' FROM customers

Erklärung

Frage 18 von 78

Examine these SQL statements that are executed in the given order:

CREATE TABLE emp
(emp_no NUMBER (2) CONSTRAINT emp_emp_no_pk PRIMARY KEY,
ename VARCHAR 2 (15),
salary NUMBER (8, 2),
mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp (emp_no));

ALTER TABLE emp
DISABLE CONSTRAINT emp_emp_no_pk CASCADE;

ALTER TABLE emp
ENABLE CONSTRAINT emp_emp_no_pk;

What will be the status of the foreign key EMP_MGR_FK?

Wähle eine der folgenden:

It will be enabled and immediate.
It will be enabled and deferred.
It will remain disabled and can be re-enabled manually.
It will remain disabled and can be enabled only by dropping the foreign key constraint and re-creating it.

Erklärung

Frage 19 von 78

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?

Wähle eine der folgenden:

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.

Erklärung

Frage 20 von 78

View the Exhibit and examine, the description for the SALES and CHANNELS tables. (Choose the best answer.)

You issued this SQL statement:

INSERT INTO SALES VALUES (23, 2300, SYSDATE,
(SELECT CAHNNEL_ID FROM CHANNELS WHERE CHANNEL_DESC='DIRECT SALES'), 12, 500);
Which statement is true regarding the result?

Wähle eine der folgenden:

The statement will fail because the sub-query in the VALUES clause is not enclosed within single quotation marks.
The statement will fail because a subquery cannot be used in a VALUES clause.
The statement will execute and a new row will be inserted in the SALES table.
The statement will fail because the VALUES clause is not required with the subquery.

Erklärung

Frage 21 von 78

View the Exhibit and examine the description of the ORDERS table.

Which two WHERE clause conditions demonstrate the correct usage of conversion functions? (Choose two.)

Wähle eine oder mehr der folgenden:

WHERE order_date > TO_DATE(‘JUL 10 2006′,’MON DD YYYY’)
WHERE TO_CHAR(order_date,’MON DD YYYY’) = ‘JAN 20 2003’
WHERE order_date > TO_CHAR(ADD_MONTHS(SYSDATE,6),’MON DD YYYY’)
WHERE order_date IN ( TO_DATE(‘Oct 21 2003′,’Mon DD YYYY’), TO_CHAR(‘NOV 21 2003′,’Mon DD YYYY’) )

Erklärung

Frage 22 von 78

Which three arithmetic operations can be performed on a column by using a SQL function that is built into Oracle database? (Choose three.)

Wähle eine oder mehr der folgenden:

Finding the lowest value
Finding the quotient
Raising to a power
Subtraction
Addition

Erklärung

Frage 23 von 78

View the Exhibit and examine the structure of the EMPLOYEES and JOB_HISTORY tables.

Examine this query which must select the employee IDs of all the employees who have held the job SA_MAN at any time during their employment.

SELECT EMPLOYEE_ID
FROM EMPLOYEES
WHERE JOB_ID = SA_MAN
————————————-
SELECT EMPLOYEE_ID
FROM JOB_HISTORY
WHERE JOB_ID = SA_MAN;

Choose correct SET operator which would cause the query to return the desired result.

Wähle eine der folgenden:

UNION
MINUS
INTERSECT
UNION ALL

Erklärung

Frage 24 von 78

You must create a SALES table with these column specifications and data types: (Choose the best answer.)

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?

Wähle eine der folgenden:

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

Erklärung

Frage 25 von 78

View the Exhibit and examine the details of the PRODUCT_INFORMATION table. (Choose two.)

Evaluate this SQL statement:

SELECT TO_CHAR (list_price, $9,999)
From product_information;

Which two statements are true regarding the output?

Wähle eine oder mehr der folgenden:

A row whose LIST_PRICE column contains value 11235.90 would be displayed as #######.
A row whose LIST_PRICE column contains value 1123.90 would be displayed as $1,123.
A row whose LIST_PRICE column contains value 1123.90 would be displayed as $1,124.
A row whose LIST_PRICE column contains value 11235.90 would be displayed as $1,123.

Erklärung

Frage 26 von 78

Which statement is true about SQL query processing in an Oracle database instance? (Choose the best answer.)

Wähle eine der folgenden:

During parsing, a SQL statement containing literals in the WHERE clause that has been executed by any session and which is cached in memory, is always reused for the current execution.
During executing, the oracle server may read data from storage if the required data is not already in memory.
During row source generation, rows that satisfy the query are retrieved from the database and stored in memory.
During optimization, execution plans are formulated based on the statistics gathered by the database instance, and the lowest cost plan is selected for execution.

Erklärung

Frage 27 von 78

Examine the structure of the ORDERS table:

You want to find the total value of all the orders for each year and issue this command:

SQL> SELECT TO_CHAR(order_date,rr), SUM(order_total) FROM orders GROUP BY TO_CHAR(order_date, yyyy);

Which statement is true regarding the result?

Wähle eine der folgenden:

It executes successfully but does not give the correct output.
It executes successfully but gives the correct output.
It returns an error because the TO_CHAR function is not valid.
It return an error because the datatype conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.

Erklärung

Frage 28 von 78

View the Exhibit and examine the structure of the ORDER_ITEMS table. (Choose the best answer.)

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?

Wähle eine der folgenden:

SELECT order_id FROM order_items GROUP BY order_id HAVING SUM(unit_price*quantity) = (SELECT MAX (SUM(unit_price*quantity)) FROM order_items GROUP BY order_id);
SELECT order_id FROM order_items WHERE(unit_price*quantity) = (SELECT MAX (SUM(unit_price*quantity) FROM order_items) GROUP BY order_id);
SELECT order_id FROM order_items WHERE(unit_price*quantity) = MAX(unit_price*quantity) GROUP BY order_id);
SELECT order_id FROM order_items WHERE (unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items GROUP BY order_id)

Erklärung

Frage 29 von 78

View the Exhibit and examine the structure of the EMP table which is not partitioned and not an index-organized table.

Evaluate this SQL statement:

ALTER TABLE emp DROP COLUMN first_name;

Which two statements are true?

Wähle eine oder mehr der folgenden:

The FIRST_NAME column can be dropped even if it is part of a composite PRIMARY KEY provided the CASCADE option is added to the SQL statement.
The FIRST_NAME column would be dropped provided at least one column remains in the table.
The FIRST_NAME column would be dropped provided it does not contain any data
The drop of the FIRST_NAME column can be rolled back provided the SET UNUSED option is added to the SQL statement

Erklärung

Frage 30 von 78

View the exhibit and examine the structure and data in the invoice table

Which two SQL statements would execute successfully?

Wähle eine oder mehr der folgenden:

SELECT MAX(AVG(SYSDATE -inv_date)) FROM invoice
SELECT AVG(inv_date) FROM invoice
SELECT MAX(inv_date), MIN(cust_id) FROM invoice
SELECT AVG( inv_date -SYSDATE), AVG(inv_amt) FROM invoice

Erklärung

Frage 31 von 78

Which two statements best describe the benefits of using the WITH clause?

Wähle eine oder mehr der folgenden:

It can improve the performance of a large query by storing the result of a query block having the WITH clause in the sessions temporary tablespace.
It enables sessions to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query.
It enables sessions to store a query block permanently in memory and use it to create complex queries
It enables sessions to store the results of a query permanently.

Erklärung

Frage 32 von 78

Which three statements are true regarding subqueries?

Wähle eine oder mehr der folgenden:

The ORDER BY Clause can be used in a subquery.
A subquery can be used in the FROM clause of a SELECT statement
If a subquery returns NULL, the main query may still return rows.
A subquery can be placed in a WHERE clause, a GROUP BY clause, or a HAVING clause.
Logical operators, such as AND, OR and NOT, cannot be used in the WHERE clause of a subquery.

Erklärung

Frage 33 von 78

Which two statements are true regarding single row functions?

Wähle eine oder mehr der folgenden:

MOD : returns the quotient of a division.
TRUNC : can be used with NUMBER and DATE values.
CONCAT : can be used to combine any number of values.
SYSDATE : returns the database server current date and time.
INSTR : can be used to find only the first occurrence of a character in a string.
TRIM : can be used to remove all the occurrences of a character from a string.

Erklärung

Frage 34 von 78

View the Exhibit and examine the structure of the ORDERS table.

You must select ORDER_ID and ORDER_DATE for all orders that were placed after the last order placed by CUSTOMER_ID 101.

Which query would give you the desired result?

Wähle eine oder mehr der folgenden:

SELECT order_id, order_date FROM orders
WHERE order_date > ANY
(SELECT order_date FROM orders WHERE customer_id = 101);
SELECT order_id, order_date FROM orders
WHERE order_date > ALL
(SELECT MAX(order_date) FROM orders ) AND customer_id = 101;
SELECT order_id, order_date FROM orders
WHERE order_date > ALL
(SELECT order_date FROM orders WHERE customer_id = 101)
SELECT order_id, order_date FROM orders
WHERE order_date > IN
(SELECT order_date FROM orders WHERE customer_id = 101);

Erklärung

Frage 35 von 78

Which task can be performed by using a single Data Manipulation Language (DML) statement?

Wähle eine der folgenden:

Removing all data only from a single column on which a primary key constraint is defined.
Removing all data from a single column on which a unique constraint is defined.
Adding a column with a default value while inserting a row into a table.
Adding a column constraint while inserting a row into a table.

Erklärung

Frage 36 von 78

You must display details of all users whose username contains the string ch_. (Choose the best answer.)

Which query generates the required output?

Wähle eine der folgenden:

SELECT * FROM users
Where user_name LIKE '%ch_';
SELECT * FROM users
Where user_name LIKE '%ch_%'ESCAPE'%';
SELECT * FROM users
Where user_name LIKE 'ch\_%' ESCAPE '_';
SELECT * FROM users
Where user_name LIKE '%ch\_%' ESCAPE '\';

Erklärung

Frage 37 von 78

View the Exhibit and examine the data in the PRODUCTS table.

You must display product names from the PRODUCTS table that belong to the Software/other category with minimum prices as either $2000 or $4000 and with no unit of measure.

You issue this query:

SQL > SELECT prod_name, prod_category, prod_min_price FROM products
Where prod_category LIKE '%Other%' AND (prod_min_price = '2000' OR prod_min_price = '4000') AND prod_unit_of_measure <> '';

Which statement is true?

Wähle eine oder mehr der folgenden:

It executes successfully but returns no result
It executes successfully and returns the required result.
It generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not valid.
It generates an error because the condition specified for the PROD_CATEGORY column is not valid.

Erklärung

Frage 38 von 78

Examine the structure of the EMPLOYEES table.

You must display the maximum and minimum salaries of employees hired 1 year ago.

Which two statements would provide the correct output?

Wähle eine oder mehr der folgenden:

SELECT MIN(Salary) minsal, MAX(salary) maxsal FROM employees WHERE hire_date < SYSDATE-365 GROUP BY MIN(salary), MAX(salary);
SELECT minsal, maxsal FROM (SELECT MIN(salary) minsal, MAX(salary) maxsal FROM employees WHERE hire_date < SYSDATE-365) GROUP BY maxsal, minsal;
SELECT minsal, maxsal FROM (SELECT MIN(salary) minsal, MAX(salary) maxsal FROM employees WHERE hire_date < SYSDATE-365 GROUP BY MIN(salary), MAX(salary);
SELECT MIN(Salary), MAX(salary) FROM (SELECT salary FROM employees WHERE hire_date < SYSDATE-365);

Erklärung

Frage 39 von 78

Which two statements are true regarding subqueries?

Wähle eine oder mehr der folgenden:

A subquery can appear on either side of a comparison operator.
Only two subqueries can be placed at one level.
A subquery can retrieve zero or more rows.
There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement.

Erklärung

Frage 40 von 78

Which two statements are true regarding the execution of the correlated subqueries?

Wähle eine oder mehr der folgenden:

The nested query executes after the outer query returns the row.
The nested query executes first and then the outer query executes.
The outer query executes only once for the result returned by the inner query.
Each row returned by the outer query is evaluated for the results returned by the inner query.

Erklärung

Frage 41 von 78

Which two statement are true regarding table joins available in the Oracle Database server?

Wähle eine oder mehr der folgenden:

You can use the ON clause to specify multiple conditions while joining tables.
You can explicitly provide the join condition with a NATURAL JOIN.
You can use the JOIN clause to join only two tables.
You can use the USING clause to join tables on more than one column.

Erklärung

Frage 42 von 78

You issued this command:

SQL > DROP TABLE employees;

Which three statements are true?

Wähle eine oder mehr der folgenden:

Sequences used in the EMPLOYEES table become invalid.
If there is an uncommitted transaction in the session, it is committed.
All indexes and constraints defined on the table being dropped are also dropped.
The space used by the EMPLOYEES table is always reclaimed immediately.
The EMPLOYEES table can be recovered using the ROLLBACK command.
The EMPLOYEES table may be moved to the recycle bin.

Erklärung

Frage 43 von 78

View the exhibit and examine the data in the PROJ_TASK_DETAILS table. (Choose the best answer.)
The PROJ_TASK_DETAILS table stores information about project tasks and the relation between them.

The BASED_ON column indicates dependencies between tasks.

Some tasks do not depend on the completion of other tasks.

You must generate a report listing all task IDs, the task ID of any task upon which it depends and the name of the employee in charge of the task upon which it depends.

Which query would give the required result?

Wähle eine der folgenden:

SELECT p.task_id, p.based_on, d.task_in_charge
FROM proj_task_details p
JOIN proj_task_details d
ON (p.task_id = d.task_id);
SELECT p.task_id, p.based_on, d.task_in_charge
FROM proj_task_details p
FULL OUTER JOIN proj_task_details d
ON (p.based_on = d.task_id);
SELECT p.task_id, p.based_on, d.task_in_charge
FROM proj_task_details p
JOIN proj_task_details d
ON (p.based_on = d.task_id);
SELECT p.task_id, p.based_on, d.task_in_charge
FROM proj_task_details p
LEFT OUTER JOIN proj_task_details d
ON (p.based_on = d.task_id);

Erklärung

Frage 44 von 78

View the Exhibit and examine the structure of the SALES and PRODUCTS tables

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?

Wähle eine oder mehr der folgenden:

FULL OUTER JOIN
JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN

Erklärung

Frage 45 von 78

View the exhibit and examine the description of the EMPLOYEES table. You executed this SQL statement:

SELECT first_name, department_id, salary
FROM employees
ORDER BY department_id, first_name, salary desc;

Which two statements are true regarding the result?

Wähle eine oder mehr der folgenden:

The values in the SALARY column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID and FIRST_NAME column.
The values in the FIRST_NAME column would be returned in ascending order for all employees having the same value in the DEPARTMENT_ID column.
The values in the SALARY column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID column.
The values in the all columns would be returned in descending order.
The values in the FIRST_NAME column would be returned in descending order for all employees having the same value in the DEPARTMENT_ID column.

Erklärung

Frage 46 von 78

Examine the structure of the SALES table.
Examine this statement:

SQL > CREATE TABLE sales1 (prod_id, cust_id, quantity_sold, price) AS SELECT product_id, customer_id, quantity_sold, price FROM sales WHERE 1 = 2;

Which two statements are true about the SALES1 table?

Wähle eine oder mehr der folgenden:

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.

Erklärung

Frage 47 von 78

Examine this SELECT statement and view the Exhibit to see its output:

SELECT constraints_name, constraints_type, search_condition, r_constraints_name, delete_rule, status, FROM user_constraints WHERE table_name = 'ORDERS';

Which two statements are true about the output?

Wähle eine oder mehr der folgenden:

The DELETE_RULE column indicates the desired state of related rows in the child table when the corresponding row is deleted from the parent table.
The R_CONSTRAINT_NAME column contains an alternative name for the constraint.
In the second column, c indicates a check constraint.
The STATUS column indicates whether the table is currently in use.

Erklärung

Frage 48 von 78

Which two statements are true regarding constraints?

Wähle eine oder mehr der folgenden:

A constraint is enforced only for an INSERT operation on a table.
A foreign key cannot contain NULL values.
The column with a UNIQUE constraint can store NULLS
All constraints can be defined at the column level and at the table level
A constraint can be disabled even if the constraint column contains data

Erklärung

Frage 49 von 78

Which two statements are true regarding working with dates?

Wähle eine oder mehr der folgenden:

The RR date format automatically calculates the century from the SYSDATE function but allows the session user to enter the century.
The RR date format automatically calculates the century from the SYSDATE function and does not allow a session user to enter the century.
The default internal storage of dates is in character format.
The default internal storage of dates is in numeric format.

Erklärung

Frage 50 von 78

View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables

You executed this UPDATE statement:

UPDATE ( SELECT order_date, order_total, customer_id FROM orders) Set order_date = '22-mar-2007' WHERE customer_id IN (SELECT customer_id FROM customers WHERE cust_last_name = 'Roberts' AND credit_limit = 600);

Which statement is true regarding the execution?

Wähle eine oder mehr der folgenden:

It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statement.
It would not execute because two tables cannot be referenced in a single UPDATE statement.
It would execute and restrict modifications to the columns specified in the SELECT statement.
It would not execute because a SELECT statement cannot be used in place of a table name.

Erklärung

Frage 51 von 78

View the Exhibit and examine the structure of the PRODUCTS table

You must display the category with the maximum number of items.

You issue this query:

SQL > SELECT COUNT(), prod_category_id FROM products GROUP BY prod_category_id HAVING COUNT() = (SELECT MAX(COUNT(*)) FROM products);

What is the result?

Wähle eine der folgenden:

It generates an error because = is not valid and should be replaced by the IN operator.
It executes successfully but does not give the correct output.
It executes successfully and gives the correct output.
It generate an error because the subquery does not have a GROUP BY clause.

Erklärung

Frage 52 von 78

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?

Wähle eine der folgenden:

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

Erklärung

Frage 53 von 78

View and Exhibit and examine the structure and data in the INVOICE table.

Which two statements are true regarding data type conversion in query expressions?

Wähle eine oder mehr der folgenden:

inv_date = '15-february-2008' :uses implicit conversion
inv_amt = '0255982' : requires explicit conversion
inv_date > '01-02-2008' : uses implicit conversion
CONCAT(inv_amt, inv_date) : requires explicit conversion
inv_no BETWEEN '101' AND '110' : uses implicit conversion

Erklärung

Frage 54 von 78

Examine the structure of the EMPLOYEES table.
You must display the details of employees who have manager with MANAGER_ID 100, who were hired in the past 6 months and who have salaries greater than 10000.

Wähle eine der folgenden:

SELECT last_name, hire_date, salary FROM employees WHERE salary > 10000
UNION ALL
SELECT last_name, hire_date, salary FROM employees WHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100)
INTERSECT
SELECT last_name, hire_date, salary FROM employees WHERE hire_date > SYSDATE- 180;
SELECT last_name, hire_date, salary FROM employees WHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = 100)
UNION ALL
(SELECT last_name, hire_date, salary FROM employees WHERE hire_date > SYSDATE -180
INTERSECT
SELECT last_name, hire_date, salary FROM employees WHERE salary > 10000);
SELECT last_name, hire_date, salary FROM employees WHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = 100)
UNION
SELECT last_name, hire_date, salary FROM employees WHERE hire_date > SYSDATE -180
INTERSECT
SELECT last_name, hire_date, salary FROM employees WHERE salary > 10000;
(SELECT last_name, hire_date, salary FROM employees WHERE salary > 10000 UNION ALL SELECT last_name, hire_date, salary FROM employees WHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100)) UNION SELECT last_name, hire_date, salary FROM employees WHERE hire_date > SYSDATE -180;

Erklärung

Frage 55 von 78

Examine the structure of the PROMOTIONS table:

Management requires a report of unique promotion costs in each promotion category.

Which query would satisfy this requirement?

Wähle eine der folgenden:

SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1
SELECT promo_category, DISTINCT promo_cost FROM promotions
SELECT DISTINCT promo_cost, promo_category FROM promotions
SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;

Erklärung

Frage 56 von 78

You must create a table for a banking application. (Choose the best answer.)
One of the columns in the table has these requirements:

1: A column to store the duration of a short team loan

2: The data should be stored in a format supporting DATE arithmetic with DATE datatypes without using conversion functions.

3: The maximum loan period is 30 days.

4: Interest must be calculated based on the number of days for which the loan remains unpaid.

Which data type would you use?

Wähle eine der folgenden:

Date
Number
Timestamp
Interval day to second
Interval year to month

Erklärung

Frage 57 von 78

Examine the structure of the CUSTOMERS table:

CUSTNO is the PRIMARY KEY.

You must determine if any customers details have been entered more than once using a different CUSTNO, by listing all duplicate names.

Which two methods can you use to get the required result?

Wähle eine oder mehr der folgenden:

Subquery
Self-join
Full outer-join with self-join
Left outer-join with self-join
Right outer-join with self-join

Erklärung

Frage 58 von 78

Which two are the minimal requirements for a self-join?

Wähle eine oder mehr der folgenden:

Only equijoin conditions may be used in the query.
Outer joins must not be used in the query.
There must be a condition on which the self-join is performed.
No other condition except the self-join may be specified.
The table used for the self-join must have two different alias names in the query.

Erklärung

Frage 59 von 78

Examine the SQL statement used to create the TRANSACTION table

SQL > CREATE TABLE transaction
(trn_id char(2) primary key,
Start_date date DEFAULT SYSDATE,
End_date date NOT NULL);

The value A1 does not exist for trn_id in this table.

Which SQL statement successfully inserts a row into the table with the default value for START_DATE?

Wähle eine der folgenden:

INSERT INTO transaction VALUES ('A1',DEFAULT, TO_DATE(DEFAULT+10))
INSERT INTO transaction VALUES ('A1', DEFAULT, TO_DATE('SYSDATE+10'))
INSERT INTO transaction (trn_id, end_date) VALUES ('A1', '10-DEC-2014')
INSERT INTO transaction (trn_id, start_date, end_date) VALUES ('A1',, '10-DEC-2014')

Erklärung

Frage 60 von 78

Which three SQL statements would display the value 1890.55 as $1,890.55?

Wähle eine oder mehr der folgenden:

SELECT TO_CHAR (1890.55, $99G999D00) FROM DUAL
SELECT TO_CHAR (1890.55, $9,999V99) FROM DUAL
SELECT TO_CHAR (1890.55, $0G000D00) FROM DUAL
SELECT TO_CHAR (1890.55, $99,999D99) FROM DUAL
SELECT TO_CHAR (1890.55, $99G999D99) FROM DUAL

Erklärung

Frage 61 von 78

A subquery is called a single-row subquery when _______.

Wähle eine der folgenden:

There is only one subquery in the outer query and the inner query returns one or more values
The inner query returns a single value to the outer query.
The inner query uses an aggregating function and returns one or more values.
The inner query returns one or more values and the outer query returns a single value.

Erklärung

Frage 62 von 78

You must write a query that prompts users for column names and conditions every time it is executed. The user must be prompted only once for the table name.

Which statement achieves those objectives?

Wähle eine der folgenden:

SELECT &col1, '&col2'
FROM &table
WHERE &&condition = '&cond';
SELECT &col1, &col2
FROM "&table"
WHERE &condition = &cond;
SELECT &col1, &col2
FROM &&table
WHERE &condition = &cond;
SELECT &col1, &col2
FROM &&table
WHERE &condition = &&cond

Erklärung

Frage 63 von 78

Which three statements are true regarding single-row functions?

Wähle eine oder mehr der folgenden:

The data type returned, can be different from the data type of the argument that is referenced.
They can return multiple values of more than one data type.
They can accept only one argument.
They can be nested up to only two levels.
They can be used in SELECT, WHERE, and ORDER BY clauses.
They can accept column names, expressions, variable names, or a user-supplied constants as arguments.

Erklärung

Frage 64 von 78

View the Exhibit and examine the structure in the DEPARTMENTS tables.

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 output?

Wähle eine oder mehr der folgenden:

ORDER BY DEPT_NAME;
ORDER BY DEPT_ID;
ORDER BY b;
ORDER BY 3;

Erklärung

Frage 65 von 78

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

Wähle eine oder mehr der folgenden:

The WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the table.
The aggregate functions and columns used in the HAVING clause must be specified in the SELECT list of the query.
The WHERE clause can be used to exclude rows after dividing them into groups.
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.

Erklärung

Frage 66 von 78

You must create a table EMPLOYEES in which the values in the columns EMPLOYEES_ID and LOGIN_ID must be unique and not null.
Which two SQL statements would create the required table?

Wähle eine oder mehr der folgenden:

CREATE TABLE employees
( employee_id NUMBER CONSTRAINT emp_id_nn NOT NULL,
login_id NUMBER CONSTRAINT login_id_nn NOT NULL,
employee_name VARCHAR2(100),
hire_date DATE,
CONSTRAINT emp_num_id_uk UNIQUE (employee_id, login_id));
CREATE TABLE employees
( employee_id NUMBER CONSTRAINT emp_id_pk PRIMARY KEY,
login_id NUMBER UNIQUE,
employee_name VARCHAR2(25),
hire_date DATE);
CREATE TABLE employees
( employee_id NUMBER,
login_id NUMBER,
employee_name VARCHAR2(25),
hire_date DATE,
CONSTRAINT emp_id_pk PRIMARY KEY (employee_id, login_id));
CREATE TABLE employees
( employee_id NUMBER,
login_id NUMBER,
employee_name VARCHAR2(100),
hire_date DATE,
CONSTRAINT emp_id_uk UNIQUE (employee_id, login_id));
CREATE TABLE employees
( employee_id NUMBER,
login_id NUMBER,
employee_name VARCHAR2(100),
hire_date DATE,
CONSTRAINT emp_id_uk UNIQUE (employee_id, login_id);
CONSTRAINT emp_id_nn NOT NULL (employee_id, login_id));

Erklärung

Frage 67 von 78

View the Exhibit and examine the structure of the PORDUCT_INFORMATION table.
PRODUCT_ID column is the primary key. You create an index using this command:

SQL > CREATE INDEX upper_name_idx ON product_information(UPPER(product_name));

No other indexes exist on the PRODUCT_INFORMATION table. Which query would use the UPPER_NAME_IDX index?

Wähle eine der folgenden:

SELECT product_id, UPPER(product_name)
FROM product_information
WHERE UPPER(product_name) = 'LASERPRO'
OR list_price > 1000;
SELECT UPPER(product_name)
FROM product_information;
SELECT UPPER(product_name)
FROM product_information
WHERE product_id = 2254;
SELECT product_id
FROM product_information
WHERE UPPER(product_name) IN ('LASERPRO', 'CABLE');

Erklärung

Frage 68 von 78

Examine the types and examples of relationship that follows:

1 One-to-one - a) teacher to Student
2 One-to-many - b) Employees to Manager
3 Many-to-one - c) Person to SSN
4 Many-to-many - d) Customers to Products

Which option indicates correctly matched relationships?

Wähle eine der folgenden:

1-d, 2-b, 3-a, and 4-c
1-c, 2-d, 3-a, and 4-b
1-a, 2-b, 3-c, and 4-d
1-c, 2-a, 3-b, and 4-d

Erklärung

Frage 69 von 78

A non-correlated subquery can be defined as __________.

Wähle eine der folgenden:

A set of one or more sequential queries in which generally the result of the inner query is used as the search value in the outer query.
A set of sequential queries, all of which must return values from the same table.
A set of sequential queries, all of which must always return a single value.
A SELECT statement that can be embedded in a clause of another SELECT statement only.

Erklärung

Frage 70 von 78

Which three statements are true reading subqueries?

Wähle eine oder mehr der folgenden:

A Main query can have many subqueries.
A subquery can have more than one main query.
The subquery and main query must retrieve date from the same table.
The subquery and main query can retrieve data from different tables.
Only one column or expression can be compared between the subquery and main query.
Multiple columns or expressions can be compared between the subquery and main query.

Erklärung

Frage 71 von 78

See the Exhibit and examine the structure of the PROMOTIONS table:

Using the PROMOTIONS table, you need to find out the average cost for all promos in the range $0-2000 and $2000-5000 in category A.

You issue the following SQL statements:

SELECT AVG(CASE WHEN promo_cost BETWEEN 0 AND 2000 AND promo_category='A' THEN promo_cost ELSE null END) "CAT_2000A",
AVG(CASE WHEN promo_cost BETWEEN 2001 AND 5000 AND promo_category='A' THEN promo_cost ELSE null END) "CAT_5000A" FROM PROMOTIONS;

What would be the outcome?

Wähle eine der folgenden:

It generates an error because multiple conditions cannot be specified for the WHEN clause.
It executes successfully and gives the required result.
It generates an error because CASE cannot be used with group functions.
It generates an error because NULL cannot be specified as a return value.

Erklärung

Frage 72 von 78

Which two statements are true regarding multiple-row subqueries?

Wähle eine oder mehr der folgenden:

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.

Erklärung

Frage 73 von 78

View the Exhibit and examine the structure of the CUSTOMERS and CUST_HISTORY tables.

The CUSTOMERS table contains the current location of all currently active customers.

The CUST_HISTORY table stores historical details relating to any changes in the location of all current as well as previous customers who are no longer active with the company.

You need to find those customers who have never changed their address.

Which SET operator would you use to get the required output?

Wähle eine der folgenden:

INTERSECT
UNION ALL
MINUS
UNION

Erklärung

Frage 74 von 78

View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables.

You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered:

SELECT p.product_name, i.item_cnt FROM (SELECT product_id, COUNT (*) item_cnt FROM order_items GROUP BY product_id) i RIGHT OUTER JOIN products p ON i.product_id = p.product_id;

What would happen when the above statement is executed?

Wähle eine der folgenden:

The statement would execute successfully to produce the required output.
The statement would not execute because inline views and outer joins cannot be used together.
The statement would not execute because the ITEM_CNT alias cannot be displayed in the outer query.
The statement would not execute because the GROUP BY clause cannot be used in the inline.

Erklärung

Frage 75 von 78

Which statement is true regarding the UNION operator?

Wähle eine der folgenden:

By default, the output is not sorted.
Null values are not ignored during duplicate checking.
Names of all columns must be identical across all select statements.
The number of columns selected in all select statements need not be the same.

Erklärung

Frage 76 von 78

You issued the following command:

SQL> DROP TABLE employees;

Which three statements are true?

Wähle eine oder mehr der folgenden:

All uncommitted transactions are committed.
All indexes and constraints defined on the table being dropped are also dropped.
Sequences used in the employees table become invalid.
The space used by the employees table is reclaimed immediately.
The employees table can be recovered using the rollback command.
The employees table is moved to the recycle bin

Erklärung

Frage 77 von 78

Examine the create table statements for the stores and sales tables.

SQL> CREATE TABLE stores(store_id NUMBER(4) CONSTRAINT store_id_pk PRIMARY KEY, store_name VARCHAR2(12), store_address VARCHAR2(20), start_date DATE);

SQL> CREATE TABLE sales(sales_id NUMBER(4) CONSTRAINT sales_id_pk PRIMARY KEY, item_id NUMBER(4), quantity NUMBER(10), sales_date DATE, store_id NUMBER(4), CONSTRAINT store_id_fk FOREIGN KEY(store_id) REFERENCES stores(store_id));

You executed the following statement:

SQL> DELETE from stores WHERE store_id=900;

The statement fails due to the integrity constraint error:

ORA-02292: integrity constraint (HR.STORE_ID_FK) violated

Which three options ensure that the statement will execute successfully?

Wähle eine oder mehr der folgenden:

Disable the primary key in the STORES table.
Use CASCADE keyword with DELETE statement.
DELETE the rows with STORE_ID = 900 from the SALES table and then delete rows from STORES table.
Disable the FOREIGN KEY in SALES table and then delete the rows.
Create the foreign key in the SALES table on SALES_ID column with on DELETE CASCADE option.

Erklärung

Frage 78 von 78

In the customers table, the CUST_CITY column contains the value Paris for the CUST_FIRST_NAME Abigail. Evaluate the following query

What would be the outcome?

Wähle eine der folgenden:

Abigail PA
Abigail Pa
Abigail IS
An error message

Erklärung

Frage 79 von 78

Which two statements are true regarding constraints?

Wähle eine oder mehr der folgenden:

A foreign key column cannot contain null values.
A column with the UNIQUE constraint can contain null values.
A constraint is enforced only for INSERT operation on the table.
A constraint can be disabled even if the constraint column contains data.
All constraints can be defined at the column level and at the table level.

Erklärung

Frage 80 von 78

On your Oracle 12c database, you invoked SQL *Loader to load data into the EMPLOYEES table in the HR schema by issuing the following command:

$> sqlldr hr/hr@pdb table=employees

Which two statements are true regarding the command?

Wähle eine oder mehr der folgenden:

It succeeds with default settings if the EMPLOYEES table belonging to HR is already defined in the database
It fails because no SQL *Loader data file location is specified.
It fails if the HR user does not have the CREATE ANY DIRECTORY privilege.
It fails because no SQL *Loader control file location is specified.

Erklärung

Frage 81 von 78

You notice a performance change in your production Oracle 12c database. You want to know which change caused this performance difference.

Which method or feature should you use?

Wähle eine der folgenden:

Compare Period ADDM report.
AWR Compare Period report.
Active Session History (ASH) report.
Taking a new snapshot and comparing it with a preserved snapshot.

Erklärung

Frage 82 von 78

Which statement is true about Enterprise Manager (EM) express in Oracle Database 12c?

Wähle eine der folgenden:

By default, EM express is available for a database after database creation.
You can use EM express to manage multiple databases running on the same server.
You can perform basic administrative tasks for pluggable databases by using the EM express interface.
You cannot start up or shut down a database Instance by using EM express.
You can create and configure pluggable databases by using EM express.

Erklärung

Frage 83 von 78

Which two partitioned table maintenance operations support asynchronous Global Index Maintenance in Oracle database 12c?

Wähle eine oder mehr der folgenden:

ALTER TABLE DROP PARTITION
ALTER TABLE ADD PARTITION
ALTER TABLE MOVE PARTITION
ALTER TABLE MERGE PARTITION
ALTER TABLE SPLIT PARTITION
ALTER TABLE TRUNCATE PARTITION

Erklärung

Frage 84 von 78

View the Exhibits and examine PRODUCTS and SALES tables.

You issue the following query to display product name and the number of times the product has been sold:
SQL>SELECT p.prod_name, i.item_cnt
FROM (SELECT prod_id, COUNT(*) item_cnt
FROM sales
GROUP BY prod_id) i RIGHT OUTER JOIN products p
ON i.prod_id = p.prod_id;

What happens when the above statement is executed?

Wähle eine der folgenden:

The statement executes successfully and produces the required output.
The statement produces an error because ITEM_CNT cannot be displayed in the outer query.
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.

Erklärung

Frage 85 von 78

Examine the structure of the BOOKS_TRANSACTIONS table:

Examine the SQL statement:

SELECT * FROM books_transactions WHERE borrowed_date < SYSDATE AND transaction_type = 'RM' OR MEMBER_ID IN ('A101', 'A102'):

Which statement is true about the outcome?

Wähle eine der folgenden:

It displays details only for members who have borrowed before today with RM as TRANSACTION_TYPE.
It displays details for members who have borrowed before today's date with either RM as TRANSACTION_TYPE or MEMBER_ID as A101 and A102
It displays details for only members A101 and A102 who have borrowed before today with RM TRANSACTION_TYPE
It displays details for members who have borrowed before today with RM as TRANSACTION_TYPE and the details for members A101 or A102

Erklärung

Frage 86 von 78

View the Exhibit and examine the data in the EMPLOYEES table.

You want to generate a report showing the total compensation paid to each employee to date.

You issue the following query:

SELECT ename || 'joined on ' || hiredate || ', the total compesation paid is' || TO_CHAR(ROUND(ROUND(SYSDATE-hiredate)/365* sal+comm ) "COMPENSATION UNTIL DATE" FROM employees;

What is the outcome?

Wähle eine der folgenden:

It generates an error because the alias is not valid.
It executes successfully and gives the correct output
It executes successfully but does not give the correct output
It generates an error because the usage of the ROUND function in the expression is not valid.
It generates an error because the concatenation operator can be used to combine only two items.

Erklärung

Frage 87 von 78

Evaluate the following query:

SELECT INTERVAL ‘300’ MONTH,
INTERVAL ’54-2′ YEAR TO MONTH,
INTERVAL ’11:12:10.1234567′ HOUR TO SECOND
FROM dual;

What is the correct output of the above query?

Wähle eine der folgenden:

+25-00 , +54-02, +00 11:12:10.123457
+00-300, +54-02, +00 11:12:10.123457
+25-00 , +00-650, +00 11:12:10.123457
+00-300 , +00-650, +00 11:12:10.123457

Erklärung

Frage 88 von 78

Which two statements are true regarding savepoints?

Wähle eine oder mehr der folgenden:

Savepoints are effective only for COMMIT
Savepoints may be used to ROLLBACK
Savepoints can be used for only DML statements.
Savepoints are effective for both COMMIT and ROLLBACK.
Savepoints can be used for both DML and DDL statements.

Erklärung

Frage 89 von 78

Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS:

You want to generate a list of all department IDs that do not exist in the COURSE_DETAILS table.
You execute the SQL statement:

SELECT d.department_id FROM course_details c INNER JOIN department_details d ON c.department_id <> d.department_id;

What is the outcome?

Wähle eine der folgenden:

It fails because the join type used is incorrect
It executes successfully and displays the required list
It executes successfully but displays an incorrect list
It fails because the ON clause condition is not valid

Erklärung

Frage 90 von 78

View the Exhibit and examine the details of the PRODUCT_INFORMATION table.

You must display PRODUCT_NAME from the table where the CATEGORY_ID column has values 12 or 13, and the SUPPLIER_ID column has the value 102088.
You executed this 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?

Wähle eine der folgenden:

It would not execute because the entire WHERE clause is not enclosed within parentheses
It would execute but would return no rows
It would not execute because the same column has been used twice with the AND logical operator
It would execute and return the desired result.

Erklärung

Frage 91 von 78

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?

Wähle eine der folgenden:

SELECT cust_last_name AS "Name", cust_credit_limit + 1000 AS "New Credit Limit" FROM customers;
SELECT cust_last_name AS Name, cust_credit_limit + 1000 AS New Credit Limit FROM 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 + 1000 INITCAP ("NEW CREDIT LIMIT") FROM customers;

Erklärung

Frage 92 von 78

View the Exhibit and examine the structure of the ORDERS table.

Which UPDATE statement is valid?

Wähle eine der folgenden:

UPDATE orders SET order_date = '12-mar-2007', order_total IS NULL WHERE order_id = 2455;
UPDATE orders SET order_date = '12-mar-2007', AND order_total = TO_NUMBER(NULL) WHERE order_id = 2455;
UPDATE orders SET order_date = '12-mar-2007', order_total = NULL WHERE order_id = 2455;
UPDATE orders SET order_date = TO_DATE('12-mar-2007','dd-mon-yyyy'), SET order_total = TO_NUMBER (NULL) WHERE order_id = 2455;

Erklärung

Frage 93 von 78

SCOTT is a user in the database.

Evaluate the commands issued by the DBA:

Which statement is true regarding the execution of the above commands?

Wähle eine der folgenden:

Statement 1 would not execute because the WITH GRANT option is missing.
Statement 2 would not execute because system privileges and object privileges cannot be granted together in a single GRANT command.
Statement 3 would not execute because role and system privileges cannot be granted together in a single GRANT statement.
Statement 1 would not execute because the IDENTIFIED BY <password> clause is missing.

Erklärung

Frage 94 von 78

View the Exhibit and examine the data in the PRODUCT_INFORMATION table.

Which two tasks would require subqueries?

Wähle eine oder mehr der folgenden:

displaying all the products whose minimum list prices are more than average list price of products having the status orderable
displaying the total number of products supplied by supplier 102071 and having product status OBSOLETE
displaying the number of products whose list prices are more than the average list price
displaying all supplier IDs whose average list price is more than 500
displaying the minimum list price for each product status

Erklärung

Frage 95 von 78

View the Exhibit and examine the description of the EMPLOYEES table.

You want to calculate the total remuneration for each employee. Total remuneration is the sum of the annual salary and the percentage commission earned for a year. Only a few employees earn commission.

Which SQL statement would you execute to get the desired output?

Wähle eine der folgenden:

SELECT first_name, salary, salary*12+(salary*NVL2 (commission_pct, salary,salary+commission_pct))"Total" FROM EMPLOYEES;
SELECT first_name, salary, salary*12+salary*commission_pct "Total" FROM EMPLOYEES;
SELECT first_name, salary, (salary + NVL (commission_pct, 0)*salary)*12 "Total" FROM EMPLOYEES;
SELECT first_name, salary, salary*12 + NVL(salary,0)*commission_pct, "Total" FROM EMPLOYEES;

Erklärung

Frage 96 von 78

View the Exhibit and examine the structure of the PROMOTIONS table.

Evaluate the following SQL statement:

SELECT promo_name, CASE WHEN promo_cost >= (SELECT AVG (promo_cost) FROM promotions WHERE promo_category = 'TV') THEN 'HIGH' ELSE 'LOW' END COST_REMARK FROM promotions

Which statement is true regarding the outcome of the above query?

Wähle eine der folgenden:

It produces an error because subqueries cannot be used with the CASE expression.
It shows COST_REMARK for all the promos in the promo category "˜TV'.
It shows COST_REMARK for all the promos in the table.
It produces an error because the subquery gives an error.

Erklärung

Frage 97 von 78

Which statement is true regarding the USING clause in table joins?

Wähle eine der folgenden:

It can be used to join a maximum of three tables
It can be used to access data from tables through equijoins as well as nonequijoins
It can be used to join tables that have columns with the same name and compatible data types
It can be used to restrict the number of columns used in a NATURAL join

Erklärung

Frage 98 von 78

Examine the structure proposed for the TRANSACTIONS table:

Which two statements are true regarding the storage of data in the above table structure?

Wähle eine oder mehr der folgenden:

The CUST_CREDIT_VALUE column would allow storage of positive and negative integers.
The TRANS_VALIDITY column would allow storage of a time interval in days, hours, minutes, and seconds.
The CUST_STATUS column would allow storage of data up to the maximum VARCHAR2 size of 4,000 characters.
The TRANS_DATE column would allow storage of dates only in the dd-mon-yyyy format.

Erklärung

Frage 99 von 78

Examine the structure of the MARKS table:

Which two statements would execute successfully?

Wähle eine oder mehr der folgenden:

SELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1) FROM marks WHERE subject1 > subject2;
SELECT student_name subject1 FROM marks WHERE subject1 > AVG(subject1);
SELECT SUM(subject1+subject2+subject3) FROM marks WHERE student_name IS NULL;
SELECT student_name,SUM(subject1) FROM marks WHERE student_name LIKE "˜R%';

Erklärung

Frage 100 von 78

Examine the data in the CUSTOMERS table:

You want to list all cities that have more than one customer along with the customer details.
Evaluate the following query:

SELECT c1.custname, c1.city FROM Customers C1 ____________ Customers C2 ON (c1.city = c2.city AND c1.custname <> c2.custname);

Which two JOIN options can be used in the blank in the above query to give the correct output?

Wähle eine oder mehr der folgenden:

LEFT OUTER JOIN
JOIN
NATURAL JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN

Erklärung

Frage 101 von 78

Examine the structure proposed for the TRANSACTIONS table:

Which statements are true regarding the creation and storage of data in the above table structure?

Wähle eine oder mehr der folgenden:

The CUST_STATUS column would store exactly one character.
The TRANS_VALIDITY column would have a maximum size of one character.
The CUST_CREDIT_LIMIT column would be able to store decimal values.
The CUST_STATUS column would give an error.
The TRANS_DATE column would be able to store day, month, century, year, hour, minutes, seconds, and fractions of seconds.
The TRANS_VALIDITY column would give an error.

Erklärung

Frage 102 von 78

View the Exhibit and examine the structure of the PRODUCT_INFORMATION and INVENTORIES tables.

You have a requirement from the supplies department to give a list containing PRODUCT_ID, SUPPLIER_ID, and QUANTITY_ON_HAND for all the products wherein QUANTITY_ON_HAND is less than five.
Which two SQL statements can accomplish the task?

Wähle eine oder mehr der folgenden:

SELECT i.product_id, i.quantity_on_hand, pi.supplier_id FROM product_information pi JOIN inventories i ON (pi.product_id=i.product_id) WHERE quantity_on_hand < 5;
SELECT product_id, quantity_on_hand, supplier_id FROM product_information NATURAL JOIN inventories AND quantity_on_hand < 5;
SELECT i.product_id, i.quantity_on_hand, pi.supplier_id FROM product_information pi JOIN inventories i ON (pi.product_id=i.product_id) AND quantity_on_hand < 5;
SELECT i.product_id, i.quantity_on_hand, pi.supplier_id FROM product_information pi JOIN inventories i USING (product_id) AND quantity_on_hand < 5;

Erklärung

Frage 103 von 78

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?

Wähle eine der folgenden:

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.

Erklärung

Frage 104 von 78

Which statement adds a column called SALARY to the EMPLOYEES table having 100 rows, which cannot contain null?

Wähle eine der folgenden:

ALTER TABLE EMPLOYEES ADD SALARY NUMBER(8,2) DEFAULT 0 NOT NULL;
ALTER TABLE EMPLOYEES ADD SALARY NUMBER(8,2) DEFAULT CONSTRAINT p_nn NOT NULL;
ALTER TABLE EMPLOYEES ADD SALARY NUMBER(8,2) DEFAULT NOT NULL;
ALTER TABLE EMPLOYEES ADD SALARY NUMBER(8,2) NOT NULL;

Erklärung

Frage 105 von 78

View the Exhibit and examine the data in the PROMOTIONS table.

PROMO_BEGIN_DATE is stored in the default date format, dd-mon-rr
You need to produce a report that provides the name, cost, and start date of all promos in the POST category that were launched before January 1, 2000.
Which SQL statement would you use?

Wähle eine der folgenden:

SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_category = 'post' AND promo_begin_date < '01-01-00';
SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_category LIKE 'P%' AND promo_begin_date < '1-JANUARY-00';
SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_cost LIKE 'post%' AND promo_begin_date < '01-01-2000';
SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_category LIKE '%post%' AND promo_begin_date < '1-JAN-00';

Erklärung

Frage 106 von 78

Which two statements are true regarding views?

Wähle eine oder mehr der folgenden:

The WITH CHECK OPTION constraint can be used in a view definition to restrict the columns displayed through the view.
The OR REPLACE option is used to change the definition of an existing view without dropping and re-creating it.
Rows cannot be deleted through a view if the view definition contains the DISTINCT keyword.
Rows added through a view are deleted from the table automatically when the view is dropped.
A simple view in which column aliases have been used cannot be updated.
A subquery used in a complex view definition cannot contain group functions or joins.

Erklärung

Frage 107 von 78

View the Exhibit and examine the structure of CUSTOMERS table.

Evaluate the following query:

SELECT cust_id, cust_city
FROM customers
WHERE cust_first_name NOT LIKE 'A_%g_%'
AND cust_credit_limit BETWEEN 5000 AND 15000
AND cust_credit_limit NOT IN (7000,11000)
AND cust_city NOT BETWEEN 'A' AND 'B'

Which statement is true regarding the above query?

Wähle eine der folgenden:

It produces an error because the condition on the CUST_CITY column is not valid.
It produces an error because the condition on the CUST_FIRST_NAME column is not valid.
It produces an error because conditions on the CUST_CREDIT_LIMIT column are not valid.
It executes successfully.

Erklärung

Frage 108 von 78

Evaluate the following CREATE SEQUENCE statement:

CREATE SEQUENCE seq1
START WITH 100
INCREMENT BY 10
MAXVALUE 200
CYCLE
NOCACHE;

The sequence SEQ1 has generated numbers up to the maximum limit of 200. You issue the following SQL statement:
SELECT seq1.nextval FROM dual;
What is displayed by the SELECT statement?

Wähle eine der folgenden:

100
an error
10
1

Erklärung

Frage 109 von 78

Which statement is true regarding the SESSION_PRIVS dictionary view?

Wähle eine der folgenden:

It contains the object privileges granted to other users by the current user session.
It contains the system privileges granted to other users by the current user session.
It contains the current object privileges available in the user session.
It contains the current system privileges available in the user session.

Erklärung

Frage 110 von 78

Which three statements indicate the end of a transaction?

Wähle eine oder mehr der folgenden:

after a CREATE statement is issued
after a SAVEPOINT is issued
after a SELECT statement is issued
after a ROLLBACK is issued
after a COMMIT is issued

Erklärung

Frage 111 von 78

Examine the structure of the BOOKS_TRANSACTIONS table.
You want to update this table such that BOOK_ID is set to 'INVALID' for all rows where no MEMBER_ID has been entered.
Examine this partial SQL statement:

UPDATE books_transactions
SET book_id = 'INVALID'
WHERE ______________

Which condition must be used in the WHERE clause to perform the required update?

Wähle eine der folgenden:

MEMBER_ID = '';
MEMBER_ID = NULL;
MEMBER_ID IS NULL;
MEMBER_ID = "";

Erklärung

Frage 112 von 78

Evaluate the following SQL commands:

The command to create a table fails. Identify the reason for the SQL statement failure.

Wähle eine der folgenden:

You cannot use ORD_NO and ITEM_NO columns as a composite primary key because ORD_NO is also the FOREIGN KEY.
You cannot use the BETWEEN clause in the condition of a CHECK constraint.
You cannot use the NEXTVAL sequence value as a DEFAULT value for a column.
You cannot use SYSDATE in the condition of a CHECK constraint.

Erklärung

Frage 113 von 78

Evaluate the following query:

What would be the outcome of the above query?

Wähle eine der folgenden:

It produces an error because the data types are not matching.
It executes successfully and displays the literal " {'s start date was \> " for each row in the output.
It executes successfully and introduces an 's at the end of each promo_name in the output.
It produces an error because flower braces have been used.

Erklärung

Frage 114 von 78

View the exhibit and examine the description for the SALES and CHANNELS tables.

You issued this SQL statement:

INSERT INTO sales VALUES (23, 2300, SYSDATE, (SELECT channel_id FROM channels WHERE channel_desc = 'Direct Sales'), 12, 1, 500);

Which statement is true regarding the result?

Wähle eine der folgenden:

The statement will fail because the subquery in the VALUES clause is not enclosed within single quotation marks.
The statement will fail because a subquery cannot be used in a VALUES clause.
The statement will fail because the VALUES clause is not required with a subquery.
The statement will execute and a new row will be inserted in the SALES table.

Erklärung

Frage 115 von 78

View the Exhibit and examine the structure of the CUSTOMERS table.

Evaluate the following SQL statement:

SELECT cust_city, COUNT(cust_last_name)
FROM customers
WHERE cust_credit_limt > 1000
GROUP BY cust_city
HAVING AVG(cust_credit_limit) BETWEEN 5000 AND 6000;

Which statement is true regarding the outcome of the above query?

Wähle eine der folgenden:

It returns an error because the BETWEEN operator cannot be used in the HAVING clause.
It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statement.
It returns an error because WHERE and HAVING clauses cannot be used to apply conditions on the same column.
It executes successfully.

Erklärung

Frage 116 von 78

You notice that the elapsed time for an important database scheduler Job is unacceptably long. The job belongs to a scheduler job class and window. Which two actions would reduce the job’s elapsed time?

Wähle eine oder mehr der folgenden:

Increasing the job’s relative priority within the Job class to which it belongs
Increasing the priority of the scheduler window to which the job belongs
Increasing the value of the JOB_QUEUE_PROCESSES parameter
Increasing the resource allocation for the consumer group mapped to the scheduler job’s
job class within the plan mapped to the scheduler window
Increasing the priority of the job class to which the job belongs
Moving the job to an existing higher priority scheduler window with the same schedule
and duration

Erklärung

Frage 117 von 78

You run a script that completes successfully using SQL*Plus that performs these actions: 1.Creates a multitenant container database (CDB) 2. Plugs in three pluggable databases (PDBs) 3. Shuts down the CDB instance 4. Starts up the CDB instance using STARTUP OPEN READ WRITE Which two statements are true about the outcome after running the script?

Wähle eine oder mehr der folgenden:

The PDBs will be opened read/write.
The seed will be in mount state.
The seed will be opened read-only
The seed will be opened read/write.
The other PDBs will be opened read-only.
The other PDBs will be in mount state.

Erklärung

Frage 118 von 78

Identify three valid methods of opening, pluggable databases (PDBs).

Wähle eine oder mehr der folgenden:

ALTER PLUGGABLE DATABASE OPEN ALL ISSUED from the root
ALTER PLUGGABLE DATABASE OPEN ALL ISSUED from a PDB
ALTER PLUGGABLE DATABASE PDB OPEN issued from the seed
ALTER DATABASE PDB OPEN issued from the root
ALTER DATABASE OPEN issued from that PDB
ALTER PLUGGABLE DATABASE PDB OPEN issued from another PDB
ALTER PLUGGABLE DATABASE OPEN issued from that PDB

Erklärung

Frage 119 von 78

Which two are false about the large pool for an Oracle database instance that supports shared server connections?

Wähle eine oder mehr der folgenden:

Allocates memory for RMAN backup and restore operations
Allocates memory for shared and private SQL areas
Contains a cursor area for storing runtime information about cursors
Contains stack space
Contains a hash area performing hash joins of tables

Erklärung

Frage 120 von 78

Which two action are impossible during an Online Datafile Move operation?

Wähle eine oder mehr der folgenden:

Creating and dropping tables in the datafile being moved
Performing file shrink of the data file being moved
Querying tables in the datafile being moved
Performing Block Media Recovery for a data block in the datafile being moved
Flashing back the database
Executing DML statements on objects stored in the datafile being moved

Erklärung

Frage 121 von 78

Which two statements are true about the SET VERIFY ON command?

Wähle eine oder mehr der folgenden:

It can be used in SQL Developer and SQL*Plus
It displays values for variables used only in the WHERE clause of a query
It can be used only in SQL*Plus
It displays values for variables prefixed with &&
It displays values for variables created by the DEFINE command

Erklärung

Frage 122 von 78

Which two statements are true about the rules of precedence for operators?

Wähle eine oder mehr der folgenden:

The concatenation operator | | is always evaluated before addition and subtraction in an expression
Multiple parentheses can be used to override the default precedence of operators in an expression
Arithmetic operators with equal precedence area evaluated from left to right within an expression
NULLS influence the precedence of operators in an expression
The + binary operator has the highest precedence in an expression in a SQL statement

Erklärung

Frage 123 von 78

Which four statements are true regarding primary and foreign key constraints and the effect they can have on table data?

Wähle eine oder mehr der folgenden:

It is possible for child rows that have a foreign key to remain in the child table at the time the parent row is deleted
Only the primary key can be defined at the column and table level
The foreign key columns and parent table primary key columns must have the same names
A table can have only one primary key and one foreign key
A table can have only one primary key but multiple foreign keys
Primary key and foreign key constraints can be defined at both the column and table level
It is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted

Erklärung

Frage 124 von 78

Which three are true about system and object privileges?

Wähle eine oder mehr der folgenden:

WITH GRANT OPTION can be used when granting an object privilege to both users and roles
Adding a primary key constraint to an existing table in another schema requires a system privilege
Adding a foreign key constraint pointing to a table in another schema requires the REFERENCES object privilege
Revoking a system privilege that was granted with WITH ADMIN OPTION has a cascading effect
Revoking an object privilege that was granted with the WITH GRANT OPTION clause has a cascading effect.
WITH GRANT OPTION cannot be used when granting an object privilege to PUBLIC

Erklärung

Frage 125 von 78

Which two statements are true about selecting related rows from two tables based on an Entity Relationship Diagram (ERD)?

Wähle eine oder mehr der folgenden:

Implementing a relationship between two tables might require joining additional tables
Relating data from a table with data from the same table is implemented with a self join
Rows from unrelated tables cannot be joined
Every relationship between the two tables must be implemented in a join condition
An inner join relates rows within the same table

Erklärung

Frage 126 von 78

Which two statements are true about substitution variables?

Wähle eine oder mehr der folgenden:

A substitution variable can be used with any clause in a SELECT statement
A substitution variable used to prompt for a column name must be enclosed in a single quotation marks
A substitution variable prefixed with & always prompts only once for a value in a session
A substitution variable can be used only in a SELECT statement
A substitution variable used to prompt for a column name must be enclosed in double quotation marks
A substitution variable prefixed with && prompts only once for a value in a session unless it is set to undefined in the session

Erklärung

Frage 127 von 78

Which three statements are true about inner and outer joins?

Wähle eine oder mehr der folgenden:

A full outer join must use Oracle syntax
An inner join returns matched rows
A left or right outer join returns only unmatched rows
A full outer join returns matched and unmatched rows
Outer joins can only be used between two per query
Outer joins can be used when there are multiple join conditions on two tables

Erklärung

Frage 128 von 78

Which three statements are true about the DESCRIBE command?

Wähle eine oder mehr der folgenden:

It can be used to display the structure of an existing view
It can be used only from SQL*Plus
It displays the PRIMARY KEY constraint for any column or columns that have that constraint
It can be used from SQL Developer
It displays all constraints that are defined for each column
It displays the NOT NULL constraint for any columns that have that constraint

Erklärung

Frage 129 von 78

The CUSTOMERS table has a CUST_LAST_NAME column of data type VARCHAR2.
The table has two rows whose CUST_LAST_NAME values are Anderson and Ausson.
Which query produces output for CUST_LAST_NAME containing Oder for the first row and Aus for the second?

Wähle eine der folgenden:

SELECT REPLACE (TRIM(TRAILING 'son' FROM name1), 'An', 'O') FROM names;
SELECT INITCAP (REPLACE(TRIM('son' FROM name1), 'An', 'O')) FROM names;
SELECT REPLACE (SUBSTR(name1, -3), 'An', 'O') FROM names;
SELECT REPLACE (REPLACE(name1, 'son', ''), 'An', 'O') FROM names;

Erklärung

Frage 130 von 78

Which three statements are true about performing Data Manipulation Language (DML) operations on a view with no INSTEAD OF triggers defined?

Wähle eine oder mehr der folgenden:

Insert statements can always be done on a table through a view.
Views cannot be used to add rows to an underlying table if the table has columns with NOT NULL constraints lacking default values which are not referenced in the defining query of the view.
Views cannot be used to query rows from an underlying table if the table has a PRIMARY KEY and the PRIMARY KEY columns are not referenced in the defining query of the view.
Delete statements can always be done on a table through a view.
The WITH CHECK clause has no effect when deleting rows from the underlying table through the view.
Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword.

Erklärung

Frage 131 von 78

Evaluate these commands which execute successfully:

Which two statements are true about the ORD_ITEMS table and the ORD_SEQ sequence?

Wähle eine oder mehr der folgenden:

Sequence ORD_SEQ cycles back to 1 after every 5000 numbers and can cycle 20 times
Any user inserting rows into table ORD_ITEMS must have been granted access to sequence ORD_SEQ.
Column ORD_NO gets the next number from sequence ORD_SEQ whenever a row is inserted into ORD_ITEMS and no explicit value is given for ORD_NO.
If sequence ORD_SEQ is dropped then the default value for column ORD_NO will be NULL for rows inserted into ORD_ITEMS.
Sequence ORD_SEQ is guaranteed not to generate duplicate numbers.

Erklärung

Frage 132 von 78

Which two statements are true about INTERVAL data types

Wähle eine oder mehr der folgenden:

The YEAR field in an INTERVAL YEAR TO MONTH column must be a positive value.
INTERVAL DAY TO SECOND columns support fractions of seconds.
INTERVAL YEAR TO MONTH columns only support monthly intervals within a single year.
INTERVAL YEAR TO MONTH columns support yearly intervals.
INTERVAL YEAR TO MONTH columns only support monthly intervals within a range of years.
The value in an INTERVAL DAY TO SECOND column can be copied into an INTERVAL YEAR TO MONTH column.

Erklärung

Frage 133 von 78

An Oracle database server session has an uncommitted transaction in progress which updated 5000 rows in a table.
In which three situations does the transactions complete thereby committing the updates?

Wähle eine oder mehr der folgenden:

when a DBA issues a successful SHUTDOWN TRANSACTIONAL statement and the user then issues a COMMIT
when a CREATE INDEX statement is executed successfully in the same session
when a COMMIT statement is issued by the same user from another session in the same database instance
when the session logs out successfully
when a DBA issues a successful SHUTDOWN IMMEDIATE statement and the user then issues a COMMIT
when a CREATE TABLE AS SELECT statement is executed unsuccessfully in the same session

Erklärung

Frage 134 von 78

Which three statements are true regarding indexes?

Wähle eine oder mehr der folgenden:

A SELECT statement can access one or more indices without accessing any tables
An update to a table can result in no updates to any of the table's indexes
A table belonging to one user can have an index that belongs to a different user
A UNIQUE index can be altered to be non-unique
An update to a table can result in updates to any or all of the table's indexes
When a table is dropped and is moved to the RECYCLE BIN, all indexes built on that table are permanently dropped

Erklärung

Frage 135 von 78

Which three are true about the CREATE TABLE command?

Wähle eine oder mehr der folgenden:

It can include the CREATE..INDEX statement for creating an index to enforce the primary key constraint
It implicitly executes a commit
A user must have the CREATE ANY TABLE privilege to create tables
It implicitly rolls back any pending transactions
The owner of the table should have space quota available on the tablespace where the table is defined
The owner of the table must have the UNLIMITED TABLESPACE system privilege

Erklärung

Frage 136 von 78

You need to calculate the number of days from 1
January 2019 until today.
Dates are stored in the default format of DD-MON-RR.
Which two queries give the required output?

Wähle eine oder mehr der folgenden:

SELECT to_char(sysdate, 'DD-MON-YYYY') - '01-JAN-2019' FROM DUAL;
SELECT sysdate-to_date('01-JANUARY-2019') FROM DUAL;
SELECT ROUND(sysdate-'01-JAN-2019') FROM DUAL;
SELECT ROUND(sysdate-TO_DATE('01/JANUARY/2019')) FROM DUAL;
SELECT TO_DATE(SYSDATE, 'DD/MONTH/YYYY') - '01/JANUARY/2019' FROM DUAL;

Erklärung

Frage 137 von 78

Which three actions can you perform by using the ORACLE_DATAPUMP access driver?

Wähle eine oder mehr der folgenden:

Read data from an external table and load it into a table in the database
Create a directory object for an external table
Execute DML statements on an external table
Query data from an external table
Read data from a table in the database and insert it into an external table
Create a directory object for a flat file

Erklärung

Frage 138 von 78

Which three privileges can be restricted to a subset of columns in a table?

Wähle eine oder mehr der folgenden:

ALTER
DELETE
UPDATE
SELECT
INDEX
REFERENCES
INSERT

Erklärung

Frage 139 von 78

You have been tasked to create a table for a banking application.
One of the columns must meet three requirements:
1) Be stored in a format supporting date arithmetic without using conversion functions
2) Store a load period of up to 10 years
3) Be used for calculating interest for the number of days the loan remains unpaid
Which data type should you use?

Wähle eine der folgenden:

TIMESTAMP WITH LOCAL TIMEZONE
TIMESTAMP WITH TIMEZONE
INTERVAL DAY TO SECOND
TIMESTAMP
INTERVAL YEAR TO MONTH

Erklärung

Frage 140 von 78

The ORDERS table has a primary key constraint on the ORDER_ID column.
The ORDER_ITEMS table has a foreign key constraint on the ORDER_ID column, referencing the primary key of the ORDERS table.
The constraint is defined with ON DELETE CASCADE.
There are rows in the ORDERS table with an ORDER_TOTAL of less than 1000.
Which three DELETE statements execute successfully?

Wähle eine oder mehr der folgenden:

DELETE order_id FROM orders WHERE order_total < 1000;
DELETE FROM orders WHERE order_total < 1000;
DELETE orders WHERE order_total < 1000;
DELETE * FROM orders WHERE order_total < 1000;
DELETE FROM orders;

Erklärung

Frage 141 von 78

Which three statements are true about single-row functions?

Wähle eine oder mehr der folgenden:

They can be nested to any level
he data type returned can be different from the data type of the argument
They can accept only one argument
The argument can be a column name, variable, literal or an expression
They can be used only in the WHERE clause of a SELECT statement
They return a single result row per table

Erklärung

Frage 142 von 78

Which two statements are true regarding a SAVEPOINT?

Wähle eine oder mehr der folgenden:

A SAVEPOINT does not issue a COMMIT
Only one SAVEPOINT may be issued in a transaction
Rolling back to a SAVEPOINT can undo a TRUNCATE statement
Rolling back to a SAVEPOINT can undo a CREATE INDEX statement
Rolling back to a SAVEPOINT can undo a DELETE statement

Erklärung

Frage 143 von 78

Which two are true about savepoints?

Wähle eine oder mehr der folgenden:

After issuing a savepoints, you can roll back to the savepoint name within the current transaction.
A ROLLBACK TO SAVEPOINT command issued before the start of a transaction results in an error.
They make uncommitted updates visible to other sessions owned by the same user.
After issuing a savepoint, you cannot roll back the complete transaction.
You can commit updates done between two savepoints without committing other updates in the current transaction
They make uncommitted updates visible to sessions owned by other users

Erklärung

Frage 144 von 78

ALTER TABLE employees SET UNUSED (department_id)
Which two are true?

Wähle eine oder mehr der folgenden:

No updates can be made to the data in the DEPARTMENT_ID column.
A new column with the name DEPARTMENT_ID can be added to the EMPLOYEES table.
A query can be display data from the DEPARTMENT_ID column.
The DEPARTMENT_ID column is set to null for all rows in the table.
The DEPARTMENT_ID column can be recovered from the recycle bin.
The storage space occupied by the DEPARTMENT_ID column is released only after a COMMIT is issued.

Erklärung

Frage 145 von 78

The SYSDATE function displays the current Oracle Server date as:
21-MAY-19
You wish to display the date as:

MONDAY, 21 MAY, 2019 -
Which statement will do this?

Wähle eine oder mehr der folgenden:

SELECT TO_DATE(SYSDATE, 'FMDAY, DD MONTH, YYYY') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'FMDD, DAY MONTH, YYYY') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'FMDAY, DDTH MONTH, YYYY') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'FMDAY, DD MONTH, YYYY') FROM DUAL;

Erklärung

Frage 146 von 78

Which two are SQL features?

Wähle eine oder mehr der folgenden:

processing sets of data
providing update capabilities for data in external files
providing graphical capabilities
providing variable definition capabilities
providing database transaction control

Erklärung

Frage 147 von 78

Which two are true about scalar subquery expressions?

Wähle eine oder mehr der folgenden:

They can return at most one row.
You can use them as a default value for a column
You cannot correlate them with a table in the parent statement.
You must enclose them in parentheses.
They can return two columns.

Erklärung

Frage 148 von 78

You have the privileges to create any type of synonym.
Which statement will create a synonym called EMP for the HCM.EMPLOYEE_RECORDS table that is accessible to all users?

Wähle eine der folgenden:

CREATE PUBLIC SYNONIM emp FOR hcm.employee_records;
CREATE GLOBAL SYNONIM emp FOR hcm.employee_records;
CREATE SYNONIM emp FOR hcm.employee_records;
CREATE SYNONIM PUBLIC.emp FOR hcm.employee_records;
CREATE SYNONIM SYS.emp FOR hcm.employee_records;

Erklärung

Frage 149 von 78

A session's NLS_DATE_FORMAT is set to DD Mon YYYY.
Which two queries return the value 1 Jan 2019?

Wähle eine oder mehr der folgenden:

SELECT TO_DATE('2019-01-01') FROM DUAL;
SELECT DATE '2019-01-01' FROM DUAL;
SELECT '2019-01-01' FROM DUAL;
SELECT TO_CHAR('2019-01-01') FROM DUAL;
SELECT TO_DATE('2019-01-01', 'YYYY-MM-DD') FROM DUAL;

Erklärung

Frage 150 von 78

No-user-defined locks are used in your database.
Which three are true about Transaction Control Language (TCL)?

Wähle eine oder mehr der folgenden:

COMMIT erases all the transaction's savepoints and releases its locks.
ROLLBACK TO SAVEPOINT undoes the transaction's changes made since the named savepoint and then ends the transaction.
COMMIT ends the transaction and makes all its changes permanent.
ROLLBACK without the TO SAVEPOINT clause undoes all the transaction's changes but does not erase its savepoints.
ROLLBACK without the TO SAVEPOINT clause undoes all the transaction's changes but does not release its locks.
ROLLBACK without the TO SAVEPOINT clause undoes all the transaction's changes, releases its locks and erases its savepoints.

Erklärung

Antwort prüfen

["https://cdn.goconqr.com/uploads/media/image/24161648/desktop_b61a7b0f-57ff-45df-9657-ac478152add0.jpg","https://cdn.goconqr.com/uploads/media/image/24205966/desktop_98645a5a-46dd-4563-b0f1-30465d638587.jpg","https://cdn.goconqr.com/uploads/media/image/24208464/desktop_a25d1420-2204-4412-8286-fcd8c4b36596.jpg","https://cdn.goconqr.com/uploads/media/image/25491867/desktop_7f6ea28e-874f-4787-9c10-2e75e29ded60.jpg","https://cdn.goconqr.com/uploads/media/image/24058972/desktop_7d3d9596-c9fd-43b0-8873-59fa3e2658a3.jpg","https://cdn.goconqr.com/uploads/media/image/24059116/desktop_906b78c0-3bee-43bb-98c9-82feb413aab8.jpg","https://cdn.goconqr.com/uploads/media/image/24205966/desktop_98645a5a-46dd-4563-b0f1-30465d638587.jpg","https://cdn.goconqr.com/uploads/media/image/24208464/desktop_a25d1420-2204-4412-8286-fcd8c4b36596.jpg","https://cdn.goconqr.com/uploads/media/image/24058157/desktop_a8706ef8-f37c-4510-8fb8-db78da4a061f.jpg","https://cdn.goconqr.com/uploads/media/image/24059116/desktop_906b78c0-3bee-43bb-98c9-82feb413aab8.jpg","https://cdn.goconqr.com/uploads/media/image/25830707/desktop_375aa0b4-243e-4389-8737-269151e77d7e.jpg","https://cdn.goconqr.com/uploads/media/image/25830896/desktop_096b6130-0586-4177-8067-091dc33b233a.jpg","https://cdn.goconqr.com/uploads/media/image/25831148/desktop_b9edb8b4-bc35-45ce-a241-4eaa1edc53b9.jpg","https://cdn.goconqr.com/uploads/media/image/25831416/desktop_e0f52ae4-7eb2-4ca1-afbb-1015ed71757e.jpg","https://cdn.goconqr.com/uploads/media/image/24059116/desktop_906b78c0-3bee-43bb-98c9-82feb413aab8.jpg","https://cdn.goconqr.com/uploads/media/image/25868896/desktop_dd2c9d46-9e24-4f83-9bf9-2015375d2208.jpg","https://cdn.goconqr.com/uploads/media/image/25870412/desktop_c0ea1e6a-6324-4264-b58d-0320c3a52985.jpg","https://cdn.goconqr.com/uploads/media/image/25912080/desktop_476f8ea2-02b2-4627-a9cb-c767cb7b3ff4.jpg","https://cdn.goconqr.com/uploads/media/image/25912227/desktop_563f7115-0888-4ab7-8edb-c3e3918677ea.jpg","https://cdn.goconqr.com/uploads/media/image/24058157/desktop_a8706ef8-f37c-4510-8fb8-db78da4a061f.jpg","https://cdn.goconqr.com/uploads/media/image/25914431/desktop_2b3cae7a-6a34-4d07-b77d-a27ac6bbf577.jpg","https://cdn.goconqr.com/uploads/media/image/25914910/desktop_893b1105-ae3b-471b-b71c-6bc22947362d.jpg","https://cdn.goconqr.com/uploads/media/image/25936048/desktop_dfa1c1a9-9c02-44f8-81e0-f728d98be00c.jpg","https://cdn.goconqr.com/uploads/media/image/25944862/desktop_20e3f1d3-df9e-4127-92bb-49eb79c91d63.jpg","https://cdn.goconqr.com/uploads/media/image/24055824/desktop_8762746d-3fe4-4e99-86b3-db79babcfb32.jpg","https://cdn.goconqr.com/uploads/media/image/25951468/desktop_f5e67931-e54c-423a-971a-71664a51277c.jpg","https://cdn.goconqr.com/uploads/media/image/25951619/desktop_a954a90b-c74c-4092-8370-60aaeb925746.jpg","https://cdn.goconqr.com/uploads/media/image/25952135/desktop_fa89427c-a3ba-44b8-8a61-753b0107d710.jpg","https://cdn.goconqr.com/uploads/media/image/24058157/desktop_a8706ef8-f37c-4510-8fb8-db78da4a061f.jpg","https://cdn.goconqr.com/uploads/media/image/24059116/desktop_906b78c0-3bee-43bb-98c9-82feb413aab8.jpg","https://cdn.goconqr.com/uploads/media/image/26195088/desktop_5be84845-20eb-4f88-a27a-db5c645aedf8.jpg","https://cdn.goconqr.com/uploads/media/image/26196155/desktop_321ebb54-c40c-4c16-af38-3c4d17e2289e.jpeg","https://cdn.goconqr.com/uploads/media/image/26201274/desktop_83102359-fc9f-469c-9cc2-f18627f9764a.jpeg","https://cdn.goconqr.com/uploads/media/image/26736588/desktop_2313291a-3f3a-4e99-91b5-3f819e52144d.jpg","https://cdn.goconqr.com/uploads/media/image/26738063/desktop_80364b1b-b4c5-4bfc-89bb-93360f37add4.jpg","https://cdn.goconqr.com/uploads/media/image/24056685/desktop_cb922957-8175-45f8-b0e7-7151c159a5f7.jpg","https://cdn.goconqr.com/uploads/media/image/24021127/desktop_7694b8d9-abac-4bcb-9fba-d21ff483803d.jpg","https://cdn.goconqr.com/uploads/media/image/24208464/desktop_a25d1420-2204-4412-8286-fcd8c4b36596.jpg","https://cdn.goconqr.com/uploads/media/image/26745294/desktop_ce9c98c4-9cd3-4245-ae23-7d965f087442.jpg","https://cdn.goconqr.com/uploads/media/image/26745753/desktop_46a74c70-9411-4c67-ae4a-c9091f350762.jpg","https://cdn.goconqr.com/uploads/media/image/24058157/desktop_a8706ef8-f37c-4510-8fb8-db78da4a061f.jpg","https://cdn.goconqr.com/uploads/media/image/26195088/desktop_5be84845-20eb-4f88-a27a-db5c645aedf8.jpg","https://cdn.goconqr.com/uploads/media/image/26747877/desktop_f4003e0e-974a-4cbd-a1e5-8f23aa98713c.jpg","https://cdn.goconqr.com/uploads/media/image/26748205/desktop_2f489362-6be9-4db6-a252-60b4439ff53e.jpg","https://cdn.goconqr.com/uploads/media/image/26748509/desktop_6acc2fa1-536c-4fec-a8ab-1c542d325609.jpg","https://cdn.goconqr.com/uploads/media/image/26750316/desktop_4c39b544-fa6c-4720-ac5b-3b5b681a1627.jpg","https://cdn.goconqr.com/uploads/media/image/24059116/desktop_906b78c0-3bee-43bb-98c9-82feb413aab8.jpg","https://cdn.goconqr.com/uploads/media/image/26751257/desktop_4d341b7c-8ab2-4630-b90a-9dac6ae42496.jpg","https://cdn.goconqr.com/uploads/media/image/26752072/desktop_6cb619ea-e8e7-42a2-86e8-8a0c69abf976.jpg","https://cdn.goconqr.com/uploads/media/image/24058972/desktop_7d3d9596-c9fd-43b0-8873-59fa3e2658a3.jpg","https://cdn.goconqr.com/uploads/media/image/26833534/desktop_a053ec55-be14-4a6a-974f-b51e24d82340.jpg","https://cdn.goconqr.com/uploads/media/image/26833647/desktop_a1f5b3ce-13e5-46b0-bcb4-664471c5655b.jpg","https://cdn.goconqr.com/uploads/media/image/26833778/desktop_ca95a196-951a-4104-a0e8-d4de7d2da99f.jpg","https://cdn.goconqr.com/uploads/media/image/26834041/desktop_7adcf6bd-33b4-4768-976c-67df73b004dd.jpg","https://cdn.goconqr.com/uploads/media/image/24058972/desktop_7d3d9596-c9fd-43b0-8873-59fa3e2658a3.jpg","https://cdn.goconqr.com/uploads/media/image/29380606/desktop_bf2bb0b7-57f0-4a50-b2d8-3a5b9fd3d87b.jpg"]

	Erstellt von Juan Taborda vor fast 5 Jahre

examen corregido

OCA 1Z0-071 12C - Parte 2

Frage 1 von 78

Which statement is true about Data Manipulation Language (DML)?

Wähle eine der folgenden:

Erklärung

Frage 2 von 78

See the Exhibit and examine the structure of the PROMOTIONS table: You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the INTERNET category. Which query would give you the required output?

Wähle eine der folgenden:

Erklärung

Frage 3 von 78

Which two statements are true about sequences created in a single instance Oracle database? (Choose two.)

Wähle eine oder mehr der folgenden:

Erklärung

Frage 4 von 78

Evaluate the following CREATE TABLE command: Which statement is true regarding the above SQL statement?

Wähle eine der folgenden:

Erklärung

Frage 5 von 78

Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit amount in each income level. The report should NOT show any repeated credit amounts in each income level. Which query would give the required result?

Wähle eine der folgenden:

Erklärung

Frage 6 von 78

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

Wähle eine oder mehr der folgenden:

Erklärung

Frage 7 von 78

You need to display the date 11-Oct-2007 in words as ‘Eleventh of October, Two Thousand Seven’. Which SQL statement would give the required result?

Wähle eine der folgenden:

Erklärung

Frage 8 von 78

Wähle eine der folgenden:

Erklärung

Frage 9 von 78

Wähle eine der folgenden:

Erklärung

Frage 10 von 78

Wähle eine der folgenden:

Erklärung

Frage 11 von 78

Wähle eine der folgenden:

Erklärung

Frage 12 von 78

Which two statements are true regarding the SQL GROUP BY clause?

Wähle eine oder mehr der folgenden:

Erklärung

Frage 13 von 78

You issue this command which succeeds: SQL> DROP TABLE products; Which three statements are true?

Wähle eine oder mehr der folgenden:

Erklärung

Frage 14 von 78

You execute the SQL statement: SQL> CREATE TABLE citizens (citizen_id CHAR (10) PRIMARY KEY, last_name VARCHAR2 (50) NOT NULL, first_name VARCHAR2 (50), address VARCHAR2 (100), city VARCHAR2 (30) NOT NULL DEFAULT 'SEATTLE', CONSTRAINT cnames CHECK (first_name<>last_name) ); What is the outcome?

Wähle eine der folgenden:

Erklärung

Frage 15 von 78

Wähle eine der folgenden:

Erklärung

Frage 16 von 78

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

Wähle eine oder mehr der folgenden:

Erklärung

Frage 17 von 78

Wähle eine der folgenden:

Erklärung

Frage 18 von 78

Wähle eine der folgenden:

Erklärung

Frage 19 von 78

Wähle eine der folgenden:

Erklärung

Frage 20 von 78

Wähle eine der folgenden:

Erklärung

Frage 21 von 78

View the Exhibit and examine the description of the ORDERS table. Which two WHERE clause conditions demonstrate the correct usage of conversion functions? (Choose two.)

Wähle eine oder mehr der folgenden:

Erklärung

Frage 22 von 78

Which three arithmetic operations can be performed on a column by using a SQL function that is built into Oracle database? (Choose three.)

Wähle eine oder mehr der folgenden:

See the Exhibit and examine the structure of the PROMOTIONS table:

You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the INTERNET category.

Which query would give you the required output?

Evaluate the following CREATE TABLE command:
Which statement is true regarding the above SQL statement?

Using the CUSTOMERS table, you need to generate a report that shows 50% of each credit amount
in each income level. The report should NOT show any repeated credit amounts in each income level.
Which query would give the required result?

You issue this command which succeeds:
SQL> DROP TABLE products;
Which three statements are true?

You execute the SQL statement:
SQL> CREATE TABLE citizens
(citizen_id CHAR (10) PRIMARY KEY,
last_name VARCHAR2 (50) NOT NULL,
first_name VARCHAR2 (50),
address VARCHAR2 (100),
city VARCHAR2 (30) NOT NULL DEFAULT 'SEATTLE',
CONSTRAINT cnames CHECK (first_name<>last_name) );
What is the outcome?

Examine the structure of the PROGRAMS table:

Which two SQL statements would execute successfully?

View the Exhibit and examine the description of the ORDERS table.

Which two WHERE clause conditions demonstrate the correct usage of conversion functions? (Choose two.)

View the Exhibit and examine the details of the PRODUCT_INFORMATION table. (Choose two.)

Evaluate this SQL statement:

SELECT TO_CHAR (list_price, $9,999)
From product_information;

Which two statements are true regarding the output?

Examine the structure of the ORDERS table:

You want to find the total value of all the orders for each year and issue this command:

SQL> SELECT TO_CHAR(order_date,rr), SUM(order_total) FROM orders GROUP BY TO_CHAR(order_date, yyyy);

Which statement is true regarding the result?

View the Exhibit and examine the structure of the ORDER_ITEMS table. (Choose the best answer.)

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?

View the Exhibit and examine the structure of the EMP table which is not partitioned and not an index-organized table.

Evaluate this SQL statement:

ALTER TABLE emp DROP COLUMN first_name;

Which two statements are true?

View the exhibit and examine the structure and data in the invoice table

Which two SQL statements would execute successfully?

View the Exhibit and examine the structure of the ORDERS table.

You must select ORDER_ID and ORDER_DATE for all orders that were placed after the last order placed by CUSTOMER_ID 101.

Which query would give you the desired result?

You must display details of all users whose username contains the string ch_. (Choose the best answer.)

Which query generates the required output?