OCA 1Z0-071 12C - Parte 2

Question

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

Answer 1

DML automatically disables foreign ley constraints when modifying primary key values in the parent table

Answer 2

Each DML statement forms a transaction by default.

Answer 3

A transaction can consist of one or more DML statements.

Answer 4

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.

Answer 5

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

Answer 6

SELECT promo_name, promo_begin_date FROM promotions WHERE promo_begin_date IN (SELECT promo_begin_date FROM promotions WHERE promo_category= 'INTERNET');

Answer 7

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

Answer 8

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

Answer 9

When the MAXVALUE limit for a sequence is reached, it can be increased by using the ALTER SEQUENCE statement.

Answer 10

DELETE <sequencename> would remove a sequence from the database.

Answer 11

The numbers generated by an explicitly defined sequence can only be used to insert data in one table.

Answer 12

CURRVAL is used to refer to the most recent sequence number that has been generated for a particular sequence.

Answer 13

When a database instance shuts down abnormally, sequence numbers that have been cached but not used are available again when the instance is restarted.

Answer 14

It would execute successfully and only ORD_ITM_IDX index would be created.

Answer 15

It would give an error because the USING INDEX clause cannot be used on a composite primary.

Answer 16

It would execute successfully and two indexes ORD_ITM_IDX and ORD_ITM_ID PK would be created.

Answer 17

It would give an error because the USING INDEX is not permitted in the CREATE TABLE command.

Answer 18

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

Answer 19

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

Answer 20

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

Answer 21

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

Answer 22

WHERE and HAVING clauses cannot be used together in a SQL statement.

Answer 23

The HAVING clause conditions can have aggregate functions.

Answer 24

The HAVING clause conditions can use aliases for the columns.

Answer 25

The WHERE clause is used to exclude rows before the grouping of data.

Answer 26

The HAVING clause is used to exclude one or more aggregated results after grouping data.

Answer 27

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

Answer 28

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

Answer 29

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

Answer 30

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

Answer 31

SELECT course_id, department_id FROM department_details d RIGHT OUTER JOIN course_details c USING(department_id)

Answer 32

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

Answer 33

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

Answer 34

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

Answer 35

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.

Answer 36

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

Answer 37

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

Answer 38

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.

Answer 39

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

Answer 40

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.

Answer 41

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

Answer 42

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.

Answer 43

They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause.

Answer 44

They are evaluated by the first WHEN clause. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses.

Answer 45

They are evaluated by the first WHEN clause. If the condition is false, then the row would be evaluated by the subsequent WHEN clauses.

Answer 46

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.

Answer 47

You can use a column alias in the GROUP BY clause.

Answer 48

Using the WHERE clause after the GROUP BY clause excludes rows after creating groups.

Answer 49

The GROUP BY clause is mandatory if you are using an aggregating function in the SELECT clause.

Answer 50

Using the WHERE clause before the GROUP BY clause excludes rows before creating groups.

Answer 51

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.

Answer 52

All existing views and synonyms that refer to the table are invalidated but retained.

Answer 53

Any uncommitted transaction in the session is committed.

Answer 54

Table data and the table structure are deleted.

Answer 55

All the table-s indexes if any exist, are invalidated but retained.

Answer 56

Table data is deleted but the table structure is retained.

Answer 57

It succeeds and CITY can contain only 'SEATTLE' or null for all rows.

Answer 58

It fails because the condition for the CANAMES constraint is not valid.

Answer 59

It fails because the DEFAULT options cannot be after the sentence NOT NULL.

Answer 60

It succeeds and an index is crated for CITIZEN_ID.

Answer 61

SYSDATE cannot be used with the CHECK constraint.

Answer 62

The BETWEEN clause cannot be used for the CHECK constraint.

Answer 63

The CHECK constraint cannot be placed on columns having the DATE data type.

Answer 64

ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY.

Answer 65

SELECT NVL (ADD_MONTHS (END_DATE,1), SYSDATE) FROM programs;

Answer 66

SELECT TO_DATE (NVL (SYSDATE-END_DATE, SYSDATE)) FROM programs;

Answer 67

SELECT NVL (MONTHS_BETWEEN (start_date, end_date), 'Ongoing') FROM programs;

Answer 68

SELECT NVL (TO_CHAR (MONTHS_BETWEEN (start-date, end_date)), 'Ongoing') FROM programs

Answer 69

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

Answer 70

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

Answer 71

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

Answer 72

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

Answer 73

It will be enabled and immediate.

Answer 74

It will be enabled and deferred.

Answer 75

It will remain disabled and can be re-enabled manually.

Answer 76

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

Answer 77

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

Answer 78

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

Answer 79

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

Answer 80

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.

Answer 81

The statement will fail because the sub-query in the VALUES clause is not enclosed within single quotation marks.

Answer 82

The statement will fail because a subquery cannot be used in a VALUES clause.

Answer 83

The statement will execute and a new row will be inserted in the SALES table.

Answer 84

The statement will fail because the VALUES clause is not required with the subquery.

Answer 85

WHERE order_date > TO_DATE(‘JUL 10 2006′,’MON DD YYYY’)

Answer 86

WHERE TO_CHAR(order_date,’MON DD YYYY’) = ‘JAN 20 2003’

Answer 87

WHERE order_date > TO_CHAR(ADD_MONTHS(SYSDATE,6),’MON DD YYYY’)

Answer 88

WHERE order_date IN ( TO_DATE(‘Oct 21 2003′,’Mon DD YYYY’), TO_CHAR(‘NOV 21 2003′,’Mon DD YYYY’) )

Answer 89

Finding the lowest value

Answer 90

Finding the quotient

Answer 91

Raising to a power

Answer 92

Subtraction

Answer 93

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

Answer 94

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

Answer 95

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

Answer 96

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

Answer 97

A row whose LIST_PRICE column contains value 11235.90 would be displayed as #######.

Answer 98

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

Answer 99

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

Answer 100

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

Answer 101

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.

Answer 102

During executing, the oracle server may read data from storage if the required data is not already in memory.

Answer 103

During row source generation, rows that satisfy the query are retrieved from the database and stored in memory.

Answer 104

During optimization, execution plans are formulated based on the statistics gathered by the database instance, and the lowest cost plan is selected for execution.

Answer 105

It executes successfully but does not give the correct output.

Answer 106

It executes successfully but gives the correct output.

Answer 107

It returns an error because the TO_CHAR function is not valid.

Answer 108

It return an error because the datatype conversion in the SELECT list does not match the data type conversion in the GROUP BY clause.

Answer 109

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

Answer 110

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

Answer 111

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

Answer 112

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

Answer 113

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.

Answer 114

The FIRST_NAME column would be dropped provided at least one column remains in the table.

Answer 115

The FIRST_NAME column would be dropped provided it does not contain any data

Answer 116

The drop of the FIRST_NAME column can be rolled back provided the SET UNUSED option is added to the SQL statement

Answer 117

SELECT MAX(AVG(SYSDATE -inv_date)) FROM invoice

Answer 118

SELECT AVG(inv_date) FROM invoice

Answer 119

SELECT MAX(inv_date), MIN(cust_id) FROM invoice

Answer 120

SELECT AVG( inv_date -SYSDATE), AVG(inv_amt) FROM invoice

Answer 121

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.

Answer 122

It enables sessions to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query.

Answer 123

It enables sessions to store a query block permanently in memory and use it to create complex queries

Answer 124

It enables sessions to store the results of a query permanently.

Answer 125

The ORDER BY Clause can be used in a subquery.

Answer 126

A subquery can be used in the FROM clause of a SELECT statement

Answer 127

If a subquery returns NULL, the main query may still return rows.

Answer 128

A subquery can be placed in a WHERE clause, a GROUP BY clause, or a HAVING clause.

Answer 129

Logical operators, such as AND, OR and NOT, cannot be used in the WHERE clause of a subquery.

Answer 130

MOD : returns the quotient of a division.

Answer 131

TRUNC : can be used with NUMBER and DATE values.

Answer 132

CONCAT : can be used to combine any number of values.

Answer 133

SYSDATE : returns the database server current date and time.

Answer 134

INSTR : can be used to find only the first occurrence of a character in a string.

Answer 135

TRIM : can be used to remove all the occurrences of a character from a string.

Answer 136

SELECT order_id, order_date FROM orders WHERE order_date > ANY (SELECT order_date FROM orders WHERE customer_id = 101);

Answer 137

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

Answer 138

SELECT order_id, order_date FROM orders WHERE order_date > ALL (SELECT order_date FROM orders WHERE customer_id = 101)

Answer 139

SELECT order_id, order_date FROM orders WHERE order_date > IN (SELECT order_date FROM orders WHERE customer_id = 101);

Answer 140

Removing all data only from a single column on which a primary key constraint is defined.

Answer 141

Removing all data from a single column on which a unique constraint is defined.

Answer 142

Adding a column with a default value while inserting a row into a table.

Answer 143

Adding a column constraint while inserting a row into a table.

Answer 144

SELECT * FROM users Where user_name LIKE '%ch_';

Answer 145

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

Answer 146

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

Answer 147

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

Answer 148

It executes successfully but returns no result

Answer 149

It executes successfully and returns the required result.

Answer 150

It generates an error because the condition specified for PROD_UNIT_OF_MEASURE is not valid.

Answer 151

It generates an error because the condition specified for the PROD_CATEGORY column is not valid.

Answer 152

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

Answer 153

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

Answer 154

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

Answer 155

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

Answer 156

A subquery can appear on either side of a comparison operator.

Answer 157

Only two subqueries can be placed at one level.

Answer 158

A subquery can retrieve zero or more rows.

Answer 159

There is no limit on the number of subquery levels in the WHERE clause of a SELECT statement.

Answer 160

The nested query executes after the outer query returns the row.

Answer 161

The nested query executes first and then the outer query executes.

Answer 162

The outer query executes only once for the result returned by the inner query.

Answer 163

Each row returned by the outer query is evaluated for the results returned by the inner query.

Answer 164

You can use the ON clause to specify multiple conditions while joining tables.

Answer 165

You can explicitly provide the join condition with a NATURAL JOIN.

Answer 166

You can use the JOIN clause to join only two tables.

Answer 167

You can use the USING clause to join tables on more than one column.

Answer 168

Sequences used in the EMPLOYEES table become invalid.

Answer 169

If there is an uncommitted transaction in the session, it is committed.

Answer 170

All indexes and constraints defined on the table being dropped are also dropped.

Answer 171

The space used by the EMPLOYEES table is always reclaimed immediately.

Answer 172

The EMPLOYEES table can be recovered using the ROLLBACK command.

Answer 173

The EMPLOYEES table may be moved to the recycle bin.

Answer 174

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

Answer 175

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

Answer 176

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

Answer 177

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

Answer 178

FULL OUTER JOIN

Answer 179

LEFT OUTER JOIN

Answer 180

RIGHT OUTER JOIN

Answer 181

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.

Answer 182

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.

Answer 183

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

Answer 184

The values in the all columns would be returned in descending order.

Answer 185

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.

Answer 186

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

Answer 187

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

Answer 188

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

Answer 189

It is created with no rows.

Answer 190

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

Answer 191

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.

Answer 192

The R_CONSTRAINT_NAME column contains an alternative name for the constraint.

Answer 193

In the second column, c indicates a check constraint.

Answer 194

The STATUS column indicates whether the table is currently in use.

Answer 195

A constraint is enforced only for an INSERT operation on a table.

Answer 196

A foreign key cannot contain NULL values.

Answer 197

The column with a UNIQUE constraint can store NULLS

Answer 198

All constraints can be defined at the column level and at the table level

Answer 199

A constraint can be disabled even if the constraint column contains data

Answer 200

The RR date format automatically calculates the century from the SYSDATE function but allows the session user to enter the century.

Answer 201

The RR date format automatically calculates the century from the SYSDATE function and does not allow a session user to enter the century.

Answer 202

The default internal storage of dates is in character format.

Answer 203

The default internal storage of dates is in numeric format.

Answer 204

It would not execute because a subquery cannot be used in the WHERE clause of an UPDATE statement.

Answer 205

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

Answer 206

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

Answer 207

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

Answer 208

It generates an error because = is not valid and should be replaced by the IN operator.

Answer 209

It executes successfully but does not give the correct output.

Answer 210

It executes successfully and gives the correct output.

Answer 211

It generate an error because the subquery does not have a GROUP BY clause.

Answer 212

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

Answer 213

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

Answer 214

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

Answer 215

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

Answer 216

inv_date = '15-february-2008' :uses implicit conversion

Answer 217

inv_amt = '0255982' : requires explicit conversion

Answer 218

inv_date > '01-02-2008' : uses implicit conversion

Answer 219

CONCAT(inv_amt, inv_date) : requires explicit conversion

Answer 220

inv_no BETWEEN '101' AND '110' : uses implicit conversion

Answer 221

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;

Answer 222

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

Answer 223

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;

Answer 224

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

Answer 225

SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1

Answer 226

SELECT promo_category, DISTINCT promo_cost FROM promotions

Answer 227

SELECT DISTINCT promo_cost, promo_category FROM promotions

Answer 228

SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;

Answer 229

Interval day to second

Answer 230

Interval year to month

Answer 231

Full outer-join with self-join

Answer 232

Left outer-join with self-join

Answer 233

Right outer-join with self-join

Answer 234

Only equijoin conditions may be used in the query.

Answer 235

Outer joins must not be used in the query.

Answer 236

There must be a condition on which the self-join is performed.

Answer 237

No other condition except the self-join may be specified.

Answer 238

The table used for the self-join must have two different alias names in the query.

Answer 239

INSERT INTO transaction VALUES ('A1',DEFAULT, TO_DATE(DEFAULT+10))

Answer 240

INSERT INTO transaction VALUES ('A1', DEFAULT, TO_DATE('SYSDATE+10'))

Answer 241

INSERT INTO transaction (trn_id, end_date) VALUES ('A1', '10-DEC-2014')

Answer 242

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

Answer 243

SELECT TO_CHAR (1890.55, $99G999D00) FROM DUAL

Answer 244

SELECT TO_CHAR (1890.55, $9,999V99) FROM DUAL

Answer 245

SELECT TO_CHAR (1890.55, $0G000D00) FROM DUAL

Answer 246

SELECT TO_CHAR (1890.55, $99,999D99) FROM DUAL

Answer 247

SELECT TO_CHAR (1890.55, $99G999D99) FROM DUAL

Answer 248

There is only one subquery in the outer query and the inner query returns one or more values

Answer 249

The inner query returns a single value to the outer query.

Answer 250

The inner query uses an aggregating function and returns one or more values.

Answer 251

The inner query returns one or more values and the outer query returns a single value.

Answer 252

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

Answer 253

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

Answer 254

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

Answer 255

SELECT &col1, &col2 FROM &&table WHERE &condition = &&cond

Answer 256

The data type returned, can be different from the data type of the argument that is referenced.

Answer 257

They can return multiple values of more than one data type.

Answer 258

They can accept only one argument.

Answer 259

They can be nested up to only two levels.

Answer 260

They can be used in SELECT, WHERE, and ORDER BY clauses.

Answer 261

They can accept column names, expressions, variable names, or a user-supplied constants as arguments.

Answer 262

ORDER BY DEPT_NAME;

Answer 263

ORDER BY DEPT_ID;

Answer 264

ORDER BY b;

Answer 265

ORDER BY 3;

Answer 266

The WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the table.

Answer 267

The aggregate functions and columns used in the HAVING clause must be specified in the SELECT list of the query.

Answer 268

The WHERE clause can be used to exclude rows after dividing them into groups.

Answer 269

The HAVING clause can be used with aggregate functions in subqueries.

Answer 270

The WHERE clause can be used to exclude rows before dividing them into groups.

Answer 271

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

Answer 272

CREATE TABLE employees ( employee_id NUMBER CONSTRAINT emp_id_pk PRIMARY KEY, login_id NUMBER UNIQUE, employee_name VARCHAR2(25), hire_date DATE);

Answer 273

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

Answer 274

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

Answer 275

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

Answer 276

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

Answer 277

SELECT UPPER(product_name) FROM product_information;

Answer 278

SELECT UPPER(product_name) FROM product_information WHERE product_id = 2254;

Answer 279

SELECT product_id FROM product_information WHERE UPPER(product_name) IN ('LASERPRO', 'CABLE');

Answer 280

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

Answer 281

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

Answer 282

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

Answer 283

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

Answer 284

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.

Answer 285

A set of sequential queries, all of which must return values from the same table.

Answer 286

A set of sequential queries, all of which must always return a single value.

Answer 287

A SELECT statement that can be embedded in a clause of another SELECT statement only.

Answer 288

A Main query can have many subqueries.

Answer 289

A subquery can have more than one main query.

Answer 290

The subquery and main query must retrieve date from the same table.

Answer 291

The subquery and main query can retrieve data from different tables.

Answer 292

Only one column or expression can be compared between the subquery and main query.

Answer 293

Multiple columns or expressions can be compared between the subquery and main query.

Answer 294

It generates an error because multiple conditions cannot be specified for the WHEN clause.

Answer 295

It executes successfully and gives the required result.

Answer 296

It generates an error because CASE cannot be used with group functions.

Answer 297

It generates an error because NULL cannot be specified as a return value.

Answer 298

They can contain group functions.

Answer 299

They always contain a subquery within a subquery.

Answer 300

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

Answer 301

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

Answer 302

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.

Answer 303

The statement would execute successfully to produce the required output.

Answer 304

The statement would not execute because inline views and outer joins cannot be used together.

Answer 305

The statement would not execute because the ITEM_CNT alias cannot be displayed in the outer query.

Answer 306

The statement would not execute because the GROUP BY clause cannot be used in the inline.

Answer 307

By default, the output is not sorted.

Answer 308

Null values are not ignored during duplicate checking.

Answer 309

Names of all columns must be identical across all select statements.

Answer 310

The number of columns selected in all select statements need not be the same.

Answer 311

All uncommitted transactions are committed.

Answer 312

All indexes and constraints defined on the table being dropped are also dropped.

Answer 313

Sequences used in the employees table become invalid.

Answer 314

The space used by the employees table is reclaimed immediately.

Answer 315

The employees table can be recovered using the rollback command.

Answer 316

The employees table is moved to the recycle bin

Answer 317

Disable the primary key in the STORES table.

Answer 318

Use CASCADE keyword with DELETE statement.

Answer 319

DELETE the rows with STORE_ID = 900 from the SALES table and then delete rows from STORES table.

Answer 320

Disable the FOREIGN KEY in SALES table and then delete the rows.

Answer 321

Create the foreign key in the SALES table on SALES_ID column with on DELETE CASCADE option.

Answer 322

Abigail PA

Answer 323

Abigail Pa

Answer 324

Abigail IS

Answer 325

An error message

Answer 326

A foreign key column cannot contain null values.

Answer 327

A column with the UNIQUE constraint can contain null values.

Answer 328

A constraint is enforced only for INSERT operation on the table.

Answer 329

A constraint can be disabled even if the constraint column contains data.

Answer 330

All constraints can be defined at the column level and at the table level.

Answer 331

It succeeds with default settings if the EMPLOYEES table belonging to HR is already defined in the database

Answer 332

It fails because no SQL *Loader data file location is specified.

Answer 333

It fails if the HR user does not have the CREATE ANY DIRECTORY privilege.

Answer 334

It fails because no SQL *Loader control file location is specified.

Answer 335

Compare Period ADDM report.

Answer 336

AWR Compare Period report.

Answer 337

Active Session History (ASH) report.

Answer 338

Taking a new snapshot and comparing it with a preserved snapshot.

Answer 339

By default, EM express is available for a database after database creation.

Answer 340

You can use EM express to manage multiple databases running on the same server.

Answer 341

You can perform basic administrative tasks for pluggable databases by using the EM express interface.

Answer 342

You cannot start up or shut down a database Instance by using EM express.

Answer 343

You can create and configure pluggable databases by using EM express.

Answer 344

ALTER TABLE DROP PARTITION

Answer 345

ALTER TABLE ADD PARTITION

Answer 346

ALTER TABLE MOVE PARTITION

Answer 347

ALTER TABLE MERGE PARTITION

Answer 348

ALTER TABLE SPLIT PARTITION

Answer 349

ALTER TABLE TRUNCATE PARTITION

Answer 350

The statement executes successfully and produces the required output.

Answer 351

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

Answer 352

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

Answer 353

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

Answer 354

It displays details only for members who have borrowed before today with RM as TRANSACTION_TYPE.

Answer 355

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

Answer 356

It displays details for only members A101 and A102 who have borrowed before today with RM TRANSACTION_TYPE

Answer 357

It displays details for members who have borrowed before today with RM as TRANSACTION_TYPE and the details for members A101 or A102

Answer 358

It generates an error because the alias is not valid.

Answer 359

It executes successfully and gives the correct output

Answer 360

It executes successfully but does not give the correct output

Answer 361

It generates an error because the usage of the ROUND function in the expression is not valid.

Answer 362

It generates an error because the concatenation operator can be used to combine only two items.

Answer 363

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

Answer 364

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

Answer 365

+25-00 , +00-650, +00 11:12:10.123457

Answer 366

+00-300 , +00-650, +00 11:12:10.123457

Answer 367

Savepoints are effective only for COMMIT

Answer 368

Savepoints may be used to ROLLBACK

Answer 369

Savepoints can be used for only DML statements.

Answer 370

Savepoints are effective for both COMMIT and ROLLBACK.

Answer 371

Savepoints can be used for both DML and DDL statements.

Answer 372

It fails because the join type used is incorrect

Answer 373

It executes successfully and displays the required list

Answer 374

It executes successfully but displays an incorrect list

Answer 375

It fails because the ON clause condition is not valid

Answer 376

It would not execute because the entire WHERE clause is not enclosed within parentheses

Answer 377

It would execute but would return no rows

Answer 378

It would not execute because the same column has been used twice with the AND logical operator

Answer 379

It would execute and return the desired result.

Answer 380

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

Answer 381

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

Answer 382

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

Answer 383

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

Answer 384

UPDATE orders SET order_date = '12-mar-2007', order_total IS NULL WHERE order_id = 2455;

Answer 385

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

Answer 386

UPDATE orders SET order_date = '12-mar-2007', order_total = NULL WHERE order_id = 2455;

Answer 387

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

Answer 388

Statement 1 would not execute because the WITH GRANT option is missing.

Answer 389

Statement 2 would not execute because system privileges and object privileges cannot be granted together in a single GRANT command.

Answer 390

Statement 3 would not execute because role and system privileges cannot be granted together in a single GRANT statement.

Answer 391

Statement 1 would not execute because the IDENTIFIED BY <password> clause is missing.

Answer 392

displaying all the products whose minimum list prices are more than average list price of products having the status orderable

Answer 393

displaying the total number of products supplied by supplier 102071 and having product status OBSOLETE

Answer 394

displaying the number of products whose list prices are more than the average list price

Answer 395

displaying all supplier IDs whose average list price is more than 500

Answer 396

displaying the minimum list price for each product status

Answer 397

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

Answer 398

SELECT first_name, salary, salary*12+salary*commission_pct "Total" FROM EMPLOYEES;

Answer 399

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

Answer 400

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

Answer 401

It produces an error because subqueries cannot be used with the CASE expression.

Answer 402

It shows COST_REMARK for all the promos in the promo category "˜TV'.

Answer 403

It shows COST_REMARK for all the promos in the table.

Answer 404

It produces an error because the subquery gives an error.

Answer 405

It can be used to join a maximum of three tables

Answer 406

It can be used to access data from tables through equijoins as well as nonequijoins

Answer 407

It can be used to join tables that have columns with the same name and compatible data types

Answer 408

It can be used to restrict the number of columns used in a NATURAL join

Answer 409

The CUST_CREDIT_VALUE column would allow storage of positive and negative integers.

Answer 410

The TRANS_VALIDITY column would allow storage of a time interval in days, hours, minutes, and seconds.

Answer 411

The CUST_STATUS column would allow storage of data up to the maximum VARCHAR2 size of 4,000 characters.

Answer 412

The TRANS_DATE column would allow storage of dates only in the dd-mon-yyyy format.

Answer 413

SELECT SUM(DISTINCT NVL(subject1,0)), MAX(subject1) FROM marks WHERE subject1 > subject2;

Answer 414

SELECT student_name subject1 FROM marks WHERE subject1 > AVG(subject1);

Answer 415

SELECT SUM(subject1+subject2+subject3) FROM marks WHERE student_name IS NULL;

Answer 416

SELECT student_name,SUM(subject1) FROM marks WHERE student_name LIKE "˜R%';

Answer 417

LEFT OUTER JOIN

Answer 418

NATURAL JOIN

Answer 419

RIGHT OUTER JOIN

Answer 420

FULL OUTER JOIN

Answer 421

The CUST_STATUS column would store exactly one character.

Answer 422

The TRANS_VALIDITY column would have a maximum size of one character.

Answer 423

The CUST_CREDIT_LIMIT column would be able to store decimal values.

Answer 424

The CUST_STATUS column would give an error.

Answer 425

The TRANS_DATE column would be able to store day, month, century, year, hour, minutes, seconds, and fractions of seconds.

Answer 426

The TRANS_VALIDITY column would give an error.

Answer 427

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;

Answer 428

SELECT product_id, quantity_on_hand, supplier_id FROM product_information NATURAL JOIN inventories AND quantity_on_hand < 5;

Answer 429

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;

Answer 430

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;

Answer 431

It executes successfully but no rows updated.

Answer 432

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

Answer 433

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

Answer 434

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

Answer 435

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

Answer 436

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

Answer 437

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

Answer 438

ALTER TABLE EMPLOYEES ADD SALARY NUMBER(8,2) NOT NULL;

Answer 439

SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_category = 'post' AND promo_begin_date < '01-01-00';

Answer 440

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

Answer 441

SELECT promo_name, promo_cost, promo_begin_date FROM promotions WHERE promo_cost LIKE 'post%' AND promo_begin_date < '01-01-2000';

Answer 442

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

Answer 443

The WITH CHECK OPTION constraint can be used in a view definition to restrict the columns displayed through the view.

Answer 444

The OR REPLACE option is used to change the definition of an existing view without dropping and re-creating it.

Answer 445

Rows cannot be deleted through a view if the view definition contains the DISTINCT keyword.

Answer 446

Rows added through a view are deleted from the table automatically when the view is dropped.

Answer 447

A simple view in which column aliases have been used cannot be updated.

Answer 448

A subquery used in a complex view definition cannot contain group functions or joins.

Answer 449

It produces an error because the condition on the CUST_CITY column is not valid.

Answer 450

It produces an error because the condition on the CUST_FIRST_NAME column is not valid.

Answer 451

It produces an error because conditions on the CUST_CREDIT_LIMIT column are not valid.

Answer 452

It executes successfully.

Answer 453

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

Answer 454

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

Answer 455

It contains the current object privileges available in the user session.

Answer 456

It contains the current system privileges available in the user session.

Answer 457

after a CREATE statement is issued

Answer 458

after a SAVEPOINT is issued

Answer 459

after a SELECT statement is issued

Answer 460

after a ROLLBACK is issued

Answer 461

after a COMMIT is issued

Answer 462

MEMBER_ID = '';

Answer 463

MEMBER_ID = NULL;

Answer 464

MEMBER_ID IS NULL;

Answer 465

MEMBER_ID = "";

Answer 466

You cannot use ORD_NO and ITEM_NO columns as a composite primary key because ORD_NO is also the FOREIGN KEY.

Answer 467

You cannot use the BETWEEN clause in the condition of a CHECK constraint.

Answer 468

You cannot use the NEXTVAL sequence value as a DEFAULT value for a column.

Answer 469

You cannot use SYSDATE in the condition of a CHECK constraint.

Answer 470

It produces an error because the data types are not matching.

Answer 471

It executes successfully and displays the literal " {'s start date was \> " for each row in the output.

Answer 472

It executes successfully and introduces an 's at the end of each promo_name in the output.

Answer 473

It produces an error because flower braces have been used.

Answer 474

The statement will fail because the subquery in the VALUES clause is not enclosed within single quotation marks.

Answer 475

The statement will fail because a subquery cannot be used in a VALUES clause.

Answer 476

The statement will fail because the VALUES clause is not required with a subquery.

Answer 477

The statement will execute and a new row will be inserted in the SALES table.

Answer 478

It returns an error because the BETWEEN operator cannot be used in the HAVING clause.

Answer 479

It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statement.

Answer 480

It returns an error because WHERE and HAVING clauses cannot be used to apply conditions on the same column.

Answer 481

It executes successfully.

Answer 482

Increasing the job’s relative priority within the Job class to which it belongs

Answer 483

Increasing the priority of the scheduler window to which the job belongs

Answer 484

Increasing the value of the JOB_QUEUE_PROCESSES parameter

Answer 485

Increasing the resource allocation for the consumer group mapped to the scheduler job’s job class within the plan mapped to the scheduler window

Answer 486

Increasing the priority of the job class to which the job belongs

Answer 487

Moving the job to an existing higher priority scheduler window with the same schedule and duration

Answer 488

The PDBs will be opened read/write.

Answer 489

The seed will be in mount state.

Answer 490

The seed will be opened read-only

Answer 491

The seed will be opened read/write.

Answer 492

The other PDBs will be opened read-only.

Answer 493

The other PDBs will be in mount state.

Answer 494

ALTER PLUGGABLE DATABASE OPEN ALL ISSUED from the root

Answer 495

ALTER PLUGGABLE DATABASE OPEN ALL ISSUED from a PDB

Answer 496

ALTER PLUGGABLE DATABASE PDB OPEN issued from the seed

Answer 497

ALTER DATABASE PDB OPEN issued from the root

Answer 498

ALTER DATABASE OPEN issued from that PDB

Answer 499

ALTER PLUGGABLE DATABASE PDB OPEN issued from another PDB

Answer 500

ALTER PLUGGABLE DATABASE OPEN issued from that PDB

Answer 501

Allocates memory for RMAN backup and restore operations

Answer 502

Allocates memory for shared and private SQL areas

Answer 503

Contains a cursor area for storing runtime information about cursors

Answer 504

Contains stack space

Answer 505

Contains a hash area performing hash joins of tables

Answer 506

Creating and dropping tables in the datafile being moved

Answer 507

Performing file shrink of the data file being moved

Answer 508

Querying tables in the datafile being moved

Answer 509

Performing Block Media Recovery for a data block in the datafile being moved

Answer 510

Flashing back the database

Answer 511

Executing DML statements on objects stored in the datafile being moved

Answer 512

It can be used in SQL Developer and SQL*Plus

Answer 513

It displays values for variables used only in the WHERE clause of a query

Answer 514

It can be used only in SQL*Plus

Answer 515

It displays values for variables prefixed with &&

Answer 516

It displays values for variables created by the DEFINE command

Answer 517

The concatenation operator | | is always evaluated before addition and subtraction in an expression

Answer 518

Multiple parentheses can be used to override the default precedence of operators in an expression

Answer 519

Arithmetic operators with equal precedence area evaluated from left to right within an expression

Answer 520

NULLS influence the precedence of operators in an expression

Answer 521

The + binary operator has the highest precedence in an expression in a SQL statement

Answer 522

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

Answer 523

Only the primary key can be defined at the column and table level

Answer 524

The foreign key columns and parent table primary key columns must have the same names

Answer 525

A table can have only one primary key and one foreign key

Answer 526

A table can have only one primary key but multiple foreign keys

Answer 527

Primary key and foreign key constraints can be defined at both the column and table level

Answer 528

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

Answer 529

WITH GRANT OPTION can be used when granting an object privilege to both users and roles

Answer 530

Adding a primary key constraint to an existing table in another schema requires a system privilege

Answer 531

Adding a foreign key constraint pointing to a table in another schema requires the REFERENCES object privilege

Answer 532

Revoking a system privilege that was granted with WITH ADMIN OPTION has a cascading effect

Answer 533

Revoking an object privilege that was granted with the WITH GRANT OPTION clause has a cascading effect.

Answer 534

WITH GRANT OPTION cannot be used when granting an object privilege to PUBLIC

Answer 535

Implementing a relationship between two tables might require joining additional tables

Answer 536

Relating data from a table with data from the same table is implemented with a self join

Answer 537

Rows from unrelated tables cannot be joined

Answer 538

Every relationship between the two tables must be implemented in a join condition

Answer 539

An inner join relates rows within the same table

Answer 540

A substitution variable can be used with any clause in a SELECT statement

Answer 541

A substitution variable used to prompt for a column name must be enclosed in a single quotation marks

Answer 542

A substitution variable prefixed with & always prompts only once for a value in a session

Answer 543

A substitution variable can be used only in a SELECT statement

Answer 544

A substitution variable used to prompt for a column name must be enclosed in double quotation marks

Answer 545

A substitution variable prefixed with && prompts only once for a value in a session unless it is set to undefined in the session

Answer 546

A full outer join must use Oracle syntax

Answer 547

An inner join returns matched rows

Answer 548

A left or right outer join returns only unmatched rows

Answer 549

A full outer join returns matched and unmatched rows

Answer 550

Outer joins can only be used between two per query

Answer 551

Outer joins can be used when there are multiple join conditions on two tables

Answer 552

It can be used to display the structure of an existing view

Answer 553

It can be used only from SQL*Plus

Answer 554

It displays the PRIMARY KEY constraint for any column or columns that have that constraint

Answer 555

It can be used from SQL Developer

Answer 556

It displays all constraints that are defined for each column

Answer 557

It displays the NOT NULL constraint for any columns that have that constraint

Answer 558

SELECT REPLACE (TRIM(TRAILING 'son' FROM name1), 'An', 'O') FROM names;

Answer 559

SELECT INITCAP (REPLACE(TRIM('son' FROM name1), 'An', 'O')) FROM names;

Answer 560

SELECT REPLACE (SUBSTR(name1, -3), 'An', 'O') FROM names;

Answer 561

SELECT REPLACE (REPLACE(name1, 'son', ''), 'An', 'O') FROM names;

Answer 562

Insert statements can always be done on a table through a view.

Answer 563

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.

Answer 564

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.

Answer 565

Delete statements can always be done on a table through a view.

Answer 566

The WITH CHECK clause has no effect when deleting rows from the underlying table through the view.

Answer 567

Views cannot be used to add or modify rows in an underlying table if the defining query of the view contains the DISTINCT keyword.

Answer 568

Sequence ORD_SEQ cycles back to 1 after every 5000 numbers and can cycle 20 times

Answer 569

Any user inserting rows into table ORD_ITEMS must have been granted access to sequence ORD_SEQ.

Answer 570

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.

Answer 571

If sequence ORD_SEQ is dropped then the default value for column ORD_NO will be NULL for rows inserted into ORD_ITEMS.

Answer 572

Sequence ORD_SEQ is guaranteed not to generate duplicate numbers.

Answer 573

The YEAR field in an INTERVAL YEAR TO MONTH column must be a positive value.

Answer 574

INTERVAL DAY TO SECOND columns support fractions of seconds.

Answer 575

INTERVAL YEAR TO MONTH columns only support monthly intervals within a single year.

Answer 576

INTERVAL YEAR TO MONTH columns support yearly intervals.

Answer 577

INTERVAL YEAR TO MONTH columns only support monthly intervals within a range of years.

Answer 578

The value in an INTERVAL DAY TO SECOND column can be copied into an INTERVAL YEAR TO MONTH column.

Answer 579

when a DBA issues a successful SHUTDOWN TRANSACTIONAL statement and the user then issues a COMMIT

Answer 580

when a CREATE INDEX statement is executed successfully in the same session

Answer 581

when a COMMIT statement is issued by the same user from another session in the same database instance

Answer 582

when the session logs out successfully

Answer 583

when a DBA issues a successful SHUTDOWN IMMEDIATE statement and the user then issues a COMMIT

Answer 584

when a CREATE TABLE AS SELECT statement is executed unsuccessfully in the same session

Answer 585

A SELECT statement can access one or more indices without accessing any tables

Answer 586

An update to a table can result in no updates to any of the table's indexes

Answer 587

A table belonging to one user can have an index that belongs to a different user

Answer 588

A UNIQUE index can be altered to be non-unique

Answer 589

An update to a table can result in updates to any or all of the table's indexes

Answer 590

When a table is dropped and is moved to the RECYCLE BIN, all indexes built on that table are permanently dropped

Answer 591

It can include the CREATE..INDEX statement for creating an index to enforce the primary key constraint

Answer 592

It implicitly executes a commit

Answer 593

A user must have the CREATE ANY TABLE privilege to create tables

Answer 594

It implicitly rolls back any pending transactions

Answer 595

The owner of the table should have space quota available on the tablespace where the table is defined

Answer 596

The owner of the table must have the UNLIMITED TABLESPACE system privilege

Answer 597

SELECT to_char(sysdate, 'DD-MON-YYYY') - '01-JAN-2019' FROM DUAL;

Answer 598

SELECT sysdate-to_date('01-JANUARY-2019') FROM DUAL;

Answer 599

SELECT ROUND(sysdate-'01-JAN-2019') FROM DUAL;

Answer 600

SELECT ROUND(sysdate-TO_DATE('01/JANUARY/2019')) FROM DUAL;

Answer 601

SELECT TO_DATE(SYSDATE, 'DD/MONTH/YYYY') - '01/JANUARY/2019' FROM DUAL;

Answer 602

Read data from an external table and load it into a table in the database

Answer 603

Create a directory object for an external table

Answer 604

Execute DML statements on an external table

Answer 605

Query data from an external table

Answer 606

Read data from a table in the database and insert it into an external table

Answer 607

Create a directory object for a flat file

Answer 608

REFERENCES

Answer 609

TIMESTAMP WITH LOCAL TIMEZONE

Answer 610

TIMESTAMP WITH TIMEZONE

Answer 611

INTERVAL DAY TO SECOND

Answer 612

INTERVAL YEAR TO MONTH

Answer 613

DELETE order_id FROM orders WHERE order_total < 1000;

Answer 614

DELETE FROM orders WHERE order_total < 1000;

Answer 615

DELETE orders WHERE order_total < 1000;

Answer 616

DELETE * FROM orders WHERE order_total < 1000;

Answer 617

DELETE FROM orders;

Answer 618

They can be nested to any level

Answer 619

he data type returned can be different from the data type of the argument

Answer 620

They can accept only one argument

Answer 621

The argument can be a column name, variable, literal or an expression

Answer 622

They can be used only in the WHERE clause of a SELECT statement

Answer 623

They return a single result row per table

Answer 624

A SAVEPOINT does not issue a COMMIT

Answer 625

Only one SAVEPOINT may be issued in a transaction

Answer 626

Rolling back to a SAVEPOINT can undo a TRUNCATE statement

Answer 627

Rolling back to a SAVEPOINT can undo a CREATE INDEX statement

Answer 628

Rolling back to a SAVEPOINT can undo a DELETE statement

Answer 629

After issuing a savepoints, you can roll back to the savepoint name within the current transaction.

Answer 630

A ROLLBACK TO SAVEPOINT command issued before the start of a transaction results in an error.

Answer 631

They make uncommitted updates visible to other sessions owned by the same user.

Answer 632

After issuing a savepoint, you cannot roll back the complete transaction.

Answer 633

You can commit updates done between two savepoints without committing other updates in the current transaction

Answer 634

They make uncommitted updates visible to sessions owned by other users

Answer 635

No updates can be made to the data in the DEPARTMENT_ID column.

Answer 636

A new column with the name DEPARTMENT_ID can be added to the EMPLOYEES table.

Answer 637

A query can be display data from the DEPARTMENT_ID column.

Answer 638

The DEPARTMENT_ID column is set to null for all rows in the table.

Answer 639

The DEPARTMENT_ID column can be recovered from the recycle bin.

Answer 640

The storage space occupied by the DEPARTMENT_ID column is released only after a COMMIT is issued.

Answer 641

SELECT TO_DATE(SYSDATE, 'FMDAY, DD MONTH, YYYY') FROM DUAL;

Answer 642

SELECT TO_CHAR(SYSDATE, 'FMDD, DAY MONTH, YYYY') FROM DUAL;

Answer 643

SELECT TO_CHAR(SYSDATE, 'FMDAY, DDTH MONTH, YYYY') FROM DUAL;

Answer 644

SELECT TO_CHAR(SYSDATE, 'FMDAY, DD MONTH, YYYY') FROM DUAL;

Answer 645

processing sets of data

Answer 646

providing update capabilities for data in external files

Answer 647

providing graphical capabilities

Answer 648

providing variable definition capabilities

Answer 649

providing database transaction control

Answer 650

They can return at most one row.

Answer 651

You can use them as a default value for a column

Answer 652

You cannot correlate them with a table in the parent statement.

Answer 653

You must enclose them in parentheses.

Answer 654

They can return two columns.

Answer 655

CREATE PUBLIC SYNONIM emp FOR hcm.employee_records;

Answer 656

CREATE GLOBAL SYNONIM emp FOR hcm.employee_records;

Answer 657

CREATE SYNONIM emp FOR hcm.employee_records;

Answer 658

CREATE SYNONIM PUBLIC.emp FOR hcm.employee_records;

Answer 659

CREATE SYNONIM SYS.emp FOR hcm.employee_records;

Answer 660

SELECT TO_DATE('2019-01-01') FROM DUAL;

Answer 661

SELECT DATE '2019-01-01' FROM DUAL;

Answer 662

SELECT '2019-01-01' FROM DUAL;

Answer 663

SELECT TO_CHAR('2019-01-01') FROM DUAL;

Answer 664

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

Answer 665

COMMIT erases all the transaction's savepoints and releases its locks.

Answer 666

ROLLBACK TO SAVEPOINT undoes the transaction's changes made since the named savepoint and then ends the transaction.

Answer 667

COMMIT ends the transaction and makes all its changes permanent.

Answer 668

ROLLBACK without the TO SAVEPOINT clause undoes all the transaction's changes but does not erase its savepoints.

Answer 669

ROLLBACK without the TO SAVEPOINT clause undoes all the transaction's changes but does not release its locks.

Answer 670

ROLLBACK without the TO SAVEPOINT clause undoes all the transaction's changes, releases its locks and erases its savepoints.

	Created by Juan Taborda almost 5 years ago

Next up

OCA 1Z0-071 12C - Parte 2

Description

Resource summary

Question 1

Question 2

Question 3

Question 4

Question 5

Question 6

Question 7

Question 8

Question 9

Question 10

Question 11

Question 12

Question 13

Question 14

Question 15

Question 16

Question 17

Question 18

Question 19

Question 20

Question 21

Question 22

Question 23

Question 24

Question 25

Question 26

Question 27

Question 28

Question 29

Question 30

Question 31

Question 32

Question 33

Question 34

Question 35

Question 36

Question 37

Question 38

Question 39

Question 40

Question 41

Question 42

Question 43

Question 44

Question 45

Question 46

Question 47

Question 48

Question 49

Question 50

Question 51

Question 52

Question 53

Question 54

Question 55

Question 56

Question 57

Question 58

Question 59

Question 60

Question 61

Question 62

Question 63

Question 64

Question 65

Question 66

Question 67

Question 68

Question 69

Question 70

Question 71

Question 72

Question 73

Question 74

Question 75

Question 76