Midterm 2 (Chapter 5 - 13)

Question

Which of the following is a correct statement?

Answer 1

A commit is issued implicitly when a user exits SQL Developer or SQL*Plus.

Answer 2

A commit is issued implicitly when a DDL command is executed.

Answer 3

A commit is issued automatically when a DML command is executed.

Answer 4

All of the above are correct.

Answer 5

A commit is issued implicitly when a user exits SQL Developer or SQL*Plus, and when a DDL command is executed.

Answer 6

A commit is issued implicitly when a user exits SQL Developer or SQL*Plus. A commit is issued automatically when a DML command is executed.

Answer 7

SELECT * WHERE amid = ‘J100’ FOR UPDATE;

Answer 8

INSERT INTO homework10 VALUES (SELECT * FROM acctmanager);

Answer 9

DELETE amid FROM acctmanager;

Answer 10

all of the above

Answer 11

INSERT INTO

Answer 12

ALTER TABLE ADD

Answer 13

SELECT . . . FOR UPDATE

Answer 14

DELETE * FROM homework10;

Answer 15

DELETE *.* FROM homework10;

Answer 16

DELETE FROM homework10;

Answer 17

DELETE FROM homework10 WHERE amid = ‘*’;

Answer 18

Both (DELETE FROM homework10;) and (DELETE FROM homework10 WHERE amid = ‘*’;) delete all rows in the HOMEWORK10 table.

Answer 19

SELECT * FROM homework10 WHERE col2 IS NULL FOR UPDATE;

Answer 20

INSERT INTO homework10 (col1, col2, col3) VALUES (‘A’, ‘B’, ‘C’);

Answer 21

UPDATE homework10 SET col3 = NULL WHERE col1 = ‘A’;

Answer 22

UPDATE homework10 SET col3 = LOWER (col3) WHERE col1 = ‘A’;

Answer 23

all of the above

Answer 24

INSERT INTO homework10 VALUES (‘A’, ‘B’, ‘C’);

Answer 25

INSERT INTO homework10 (col3, col1, col2) VALUES (NULL, ‘A’, ‘B’);

Answer 26

INSERT INTO homework10 VALUES (NULL, ‘A’, ‘B’);

Answer 27

UPDATE homework10 SET col1 = col3;

Answer 28

‘O^hara’

Answer 29

‘O’’hara’ (two single quotes following the O)

Answer 30

‘O’’hara’ (a double quote following the O)

Answer 31

Data values can’t contain quotes.

Answer 32

LOCK TABLE homework10 EXCLUSIVELY;

Answer 33

LOCK TABLE homework10 IN EXCLUSIVE MODE;

Answer 34

LOCK TABLE homework10 TO OTHER USERS;

Answer 35

LOCK homework10 IN EXCLUSIVE MODE;

Answer 36

Both b and d lock the table in EXCLUSIVE mode.

Answer 37

Col1 has a PRIMARY KEY constraint enabled.

Answer 38

Col2 has a UNIQUE constraint enabled.

Answer 39

Col3 is defined as a DATE column.

Answer 40

None of the above would cause the command to fail.

Answer 41

A COMMIT command is issued.

Answer 42

A DDL command is issued to end a transaction.

Answer 43

The user exits the system.

Answer 44

A ROLLBACK command is issued.

Answer 45

all of the above

Answer 46

none of the above

Answer 47

Other users can view the new orders as soon as you execute the INSERT INTO command.

Answer 48

Other users can view the new orders as soon as you issue a ROLLBACK command.

Answer 49

Other users can view the new orders as soon as you exit the system or execute a COMMIT command.

Answer 50

Other users can view the new orders only if they place an exclusive lock on the table.

Answer 51

DELETE FROM orders WHERE orderdate < ‘01-APR-09’;

Answer 52

DROP FROM orders WHERE orderdate < ‘01-APR-09’;

Answer 53

REMOVE FROM orders WHERE orderdate < ‘01-APR-09’;

Answer 54

DELETE FROM orders WHERE orderdate > ‘01-APR-09’;

Answer 55

ROLLBACK COMMIT;

Answer 56

REGENERATE RECORDS orders;

Answer 57

None of the above restores the deleted orders.

Answer 58

A DDL command is executed.

Answer 59

A DML command is executed.

Answer 60

The user exits the system.

Answer 61

none of the above

Answer 62

If you attempt to add a record that violates a constraint for one of the table’s columns, only the valid columns for the row are added.

Answer 63

A subquery nested in the VALUES clause of an INSERT INTO command can return only one value without generating an Oracle 11g error message.

Answer 64

If you attempt to add a record that violates a NOT NULL constraint, a blank space is inserted automatically in the appropriate column so that Oracle 11g can complete the DML operation.

Answer 65

None of the above statements is correct.

Answer 66

a number generator

Answer 67

a sequence

Answer 68

DROP SYNONYM synonymname;

Answer 69

DELETE PUBLIC SYNONYM synonymname;

Answer 70

DROP PUBLIC SYNONYM synonymname;

Answer 71

DELETE SYNONYM synonymname;

Answer 72

ALTER SESSION

Answer 73

ALTER TABLE

Answer 74

MODIFY INDEX

Answer 75

ALTER INDEX

Answer 76

none of the above

Answer 77

NEXT_VALUE

Answer 78

CURR_VALUE

Answer 79

INSERT INTO publisher VALUES (pubsequence.nextvalue, 'HAPPY PRINTING', 'LAZY LARRY', NULL);

Answer 80

CREATE INDEX a_new_index ON (firstcolumn*.02);

Answer 81

CREATE SYNONYM pub FOR publisher;

Answer 82

all of the above

Answer 83

none of the above

Answer 84

CREATE SYNONYM thetable FOR juan.mytable;

Answer 85

CREATE PUBLIC SYNONYM thetable FOR mytable;

Answer 86

CREATE SYNONYM juan FOR mytable;

Answer 87

none of the above

Answer 88

A gap can appear in a sequence created with the NOCACHE option if the system crashes before a user can commit a transaction.

Answer 89

Any unassigned sequence values appears in the USER_SEQUENCE data dictionary table as unassigned.

Answer 90

Only the user who creates a sequence is allowed to delete it.

Answer 91

Only the user who created a sequence is allowed to use the value generated by the sequence.

Answer 92

when queries return a large percentage of rows in the results

Answer 93

when the table is small

Answer 94

when the majority of table operations are updates

Answer 95

all of the above

Answer 96

when queries return a large percentage of rows in the results and when the majority of table operations are updates

Answer 97

function-based index

Answer 98

The column contains many distinct values.

Answer 99

The column contains a small number of distinct values.

Answer 100

A WHERE clause is always used in a query on the column.

Answer 101

The selectivity of a column can’t be determined.

Answer 102

PRIMARY KEY

Answer 103

FOREIGN KEY

Answer 104

UNIQUE KEY

Answer 105

none of the above

Answer 106

NOT NULL and PRIMARY KEY

Answer 107

PRIMARY KEY and UNIQUE KEY

Answer 108

INCREMENT BY

Answer 109

START WITH

Answer 110

branch blocks

Answer 111

root block

Answer 112

leaf blocks

Answer 113

None of the above because the primary key is used to identify rows.

Answer 114

Oracle 11g generates 20 integers automatically and stores them in memor

Answer 115

No integers are cached by default.

Answer 116

Only one integer is cached at a time

Answer 117

The command will fail.

Answer 118

Oracle 11g generates 20 three-digit decimal numbers automatically and stores them in memory.

Answer 119

CREATE INDEX book_profit_idx ON (retail-cost) WHERE (retail-cost) > 10;

Answer 120

CREATE INDEX book_profit_idx ON (retail-cost);

Answer 121

CREATE FUNCTION INDEX book_profit_idx ON books WHERE (retail-cost) > 10;

Answer 122

none of the above

Answer 123

DESCRIBE indexname;

Answer 124

the USER_INDEXES view

Answer 125

the USER_INDEX view

Answer 126

all of the above

Answer 127

none of the above

Answer 128

MINIMUMVAL

Answer 129

All of the above are valid options.

Answer 130

USER_INDEXES view

Answer 131

query source code

Answer 132

explain plan

Answer 133

database access plan

Answer 134

CREATE PRIVATE SYNONYM

Answer 135

CREATE NONPUBLIC SYNONYM

Answer 136

CREATE SYNONYM

Answer 137

CREATE PUBLIC SYNONYM

Answer 138

ALTER PASSWORD

Answer 139

CHANGE PASSWORD

Answer 140

MODIFY USER PASSWORD

Answer 141

ALTER USER . . . PASSWORD

Answer 142

none of the above

Answer 143

ALTER ROLE mcain DEFAULT ROLE customerrep;

Answer 144

ALTER USER mcain TO customerrep;

Answer 145

SET DEFAULT ROLE customerrep FOR mcain;

Answer 146

ALTER USER mcain DEFAULT ROLE customerrep

Answer 147

SET ROLE customerrep FOR mcain;

Answer 148

Authentication procedures prevent any data stored in the Oracle 11g database from being stolen or damaged.

Answer 149

Authentication procedures are used to limit unauthorized access to the Oracle 11g database.

Answer 150

Oracle 11g authentication doesn’t prevent users from accessing data in the database if they have a valid operating system account.

Answer 151

Authentication procedures restrict the type of data manipulation operations that a user can perform.

Answer 152

CREATE ROLE depthead IDENTIFIED BY apassword;

Answer 153

CREATE USER depthead IDENTIFIED BY apassword;

Answer 154

CREATE ACCOUNT depthead;

Answer 155

GRANT ACCOUNT depthead;

Answer 156

CREATE SESSION

Answer 157

CONNECT ANY DATABASE

Answer 158

CREATE ANY TABLE

Answer 159

DROP ANY TABLE

Answer 160

TRUNCATE ANY TABLE

Answer 161

CREATE TABLE

Answer 162

TRUNC TABLE

Answer 163

SESSION_PRIVS

Answer 164

SYSTEM_PRIVILEGE_MAP

Answer 165

USER_ASSIGNED_PRIVS

Answer 166

V$ENABLED_PRIVILEGES

Answer 167

REVOKE insert ON books FROM elopez;

Answer 168

REVOKE insert FROM elopez;

Answer 169

REVOKE INSERT INTO FROM elopez;

Answer 170

DROP insert INTO books FROM elopez;

Answer 171

CREATE ROLE

Answer 172

CREATE PRIVILEGE

Answer 173

ALTER PRIVILEGE

Answer 174

CREATE USER

Answer 175

ALTER USER

Answer 176

IDENTIFIED BY

Answer 177

PASSWORD EXPIRE

Answer 178

WITH ADMIN OPTION

Answer 179

WITH GRANT OPTI

Answer 180

ASSIGN ROLES

Answer 181

CREATE SESSION

Answer 182

INSERT ANY TABLE

Answer 183

CREATE ANY

Answer 184

REFERENCES

Answer 185

an object privilege

Answer 186

a system privilege

Answer 187

DEFAULT privilege

Answer 188

If the DBA changes the password for a user while the user is connected to the database, the connection terminates automatically.

Answer 189

If the DBA revokes the CREATE SESSION privilege from a user account, the user can’t connect to the database.

Answer 190

If a user is granted the privilege to create a table and the privilege is revoked after the user creates a table, the table is dropped from the system automatically.

Answer 191

all of the above

Answer 192

DELETE ROLE receptionist;

Answer 193

DROP receptionist;

Answer 194

DROP ANY ROLE;

Answer 195

none of the above

Answer 196

SESSION_PRIVS

Answer 197

SYS_PRIVILEGE_MAP

Answer 198

V$SYSTEM_PRIVILEGES

Answer 199

SYSTEM_PRIVILEGE_MAP

Answer 200

SET DEFAULT ROLE

Answer 201

ALTER ROLE

Answer 202

ALTER SESSION

Answer 203

DELETE ANY

Answer 204

INSERT ANY

Answer 205

UPDATE ANY

Answer 206

SELECT address || city || state || zip "Address" FROM customers WHERE lastname ¼ 'SMITH';

Answer 207

SELECT * FROM publisher ORDER BY contact;

Answer 208

SELECT address, city, state, zip FROM customers WHERE lastname ¼ "SMITH";

Answer 209

All the above statements are valid and return the expected results.

Answer 210

SELECT order# FROM orders WHERE shipdate ¼ NULL;

Answer 211

SELECT order# FROM orders WHERE shipdate ¼ 'NULL';

Answer 212

SELECT order# FROM orders WHERE shipdate ¼ "NULL";

Answer 213

None of the statements are valid.

Answer 214

SELECT name FROM customers ORDER BY desc state, city;

Answer 215

SELECT firstname, lastname FROM customers SORT BY desc state, city;

Answer 216

SELECT firstname, lastname FROM customers ORDER BY state desc, city;

Answer 217

SELECT firstname, lastname FROM customers ORDER BY state desc, city desc;

Answer 218

SELECT firstname, lastname FROM customers ORDER BY 5 desc, 6 desc;

Answer 219

SELECT lastname FROM customers WHERE lastname ¼ "THOMPSON";

Answer 220

SELECT * FROM customers;

Answer 221

SELECT lastname FROM customers WHERE lastname 4 'R';

Answer 222

SELECT * FROM customers WHERE lastname 5 'V';

Answer 223

SELECT * FROM books WHERE pubid = 1 AND retail >= 25;

Answer 224

SELECT * FROM books WHERE pubid = 1 OR retail >= 25;

Answer 225

SELECT * FROM books WHERE pubid = 1 AND WHERE retail > 25;

Answer 226

SELECT * FROM books WHERE pubid = 1, retail >= 25;

Answer 227

SELECT * FROM books WHERE pubid = 1, retail >= $25.00;

Answer 228

descending

Answer 229

the order in which records are stored in the table

Answer 230

There’s no default sort sequence.

Answer 231

SELECT * FROM books WHERE pubid = 2, retail >= $35.00;

Answer 232

SELECT * FROM books WHERE pubid = 2 AND NOT retail < 35;

Answer 233

SELECT * FROM books WHERE pubid IN (1, 2, 5) AND retail NOT BETWEEN 1 AND 29.99;

Answer 234

All the above statements display the specified books.

Answer 235

None of the above statements display the specified boo

Answer 236

SELECT * FROM customers WHERE firstname = 'B%';

Answer 237

SELECT * FROM customers WHERE firstname LIKE '%N%';

Answer 238

SELECT * FROM customers WHERE firstname = '%N%';

Answer 239

SELECT * FROM customers WHERE firstname LIKE '_B%';

Answer 240

none of the above

Answer 241

SELECT * FROM books WHERE title = 'H_N_%';

Answer 242

SELECT * FROM books WHERE title LIKE "H_N_C%";

Answer 243

SELECT * FROM books WHERE title LIKE 'H_N_C%';

Answer 244

SELECT * FROM books WHERE title LIKE '_H%';

Answer 245

none of the above

Answer 246

SELECT * FROM books WHERE pubdate > 03–20–2005;

Answer 247

SELECT * FROM books WHERE pubdate > '03–20–2005';

Answer 248

SELECT * FROM books WHERE pubdate > '20–MAR–05';

Answer 249

SELECT * FROM books WHERE pubdate > 'MAR–20–05';

Answer 250

SELECT * FROM books WHERE category = 'FITNESS' OR pubid = 4 AND pubdate < '06–02–2004';

Answer 251

SELECT * FROM books WHERE category = 'FITNESS' AND pubid = 4 OR pubdate < '06–02–2004';

Answer 252

SELECT * FROM books WHERE category = 'FITNESS' OR (pubid = 4 AND pubdate < '06–02–2004');

Answer 253

SELECT * FROM books WHERE category ¼ 'FITNESS' OR pubid = 4, pubdate < '06–02–04';

Answer 254

none of the above

Answer 255

SELECT * FROM orders WHERE orderdate < '04–05–09' AND shipdate = NULL;

Answer 256

SELECT * FROM orders WHERE orderdate < '05–04–09' AND shipdate IS NULL;

Answer 257

SELECT * FROM orders WHERE orderdate < 'APR–05–09' AND shipdate IS NULL;

Answer 258

SELECT * FROM orders WHERE orderdate < '05–APR–09' AND shipdate IS NULL;

Answer 259

none of the above

Answer 260

SELECT * FROM books WHERE retail-cost > 12;

Answer 261

SELECT * FROM books WHERE retail-cost <= 12;

Answer 262

SELECT * FROM books WHERE profit >= 12;

Answer 263

SELECT * FROM books WHERE retail-cost =>12.00;

Answer 264

none of the above

Answer 265

SELECT * FROM books WHERE profit => 10.00 ORDER BY "Profit" desc;

Answer 266

SELECT title, retail-cost "Profit" FROM books WHERE profit => 10.00 ORDER BY "Profit" desc;

Answer 267

SELECT title, retail-cost "Profit" FROM books WHERE "Profit" => 10.00 ORDER BY "Profit" desc;

Answer 268

SELECT title, retail-cost profit FROM books WHERE retail-cost >= 10.00 ORDER BY "PROFIT" desc;

Answer 269

SELECT title, retail-cost "Profit" FROM books WHERE profit => 10.00 ORDER BY 3 desc;

Answer 270

SELECT * FROM books WHERE title LIKE '%AS_E%';

Answer 271

SELECT * FROM books WHERE title LIKE 'AS_E%';

Answer 272

SELECT * FROM books WHERE title = '%AS_E%'

Answer 273

SELECT * FROM books WHERE title = 'AS_E%';

Answer 274

SELECT * FROM books WHERE pubdate > 03–20–2005;

Answer 275

SELECT * FROM books WHERE pubdate > '03–20–2005';

Answer 276

SELECT * FROM books WHERE pubdate NOT < '20–MAR–05';

Answer 277

SELECT * FROM books WHERE pubdate NOT < 'MAR–20–05';

Answer 278

none of the above

Answer 279

SELECT title, authorid FROM books, bookauthor;

Answer 280

SELECT title, name FROM books CROSS JOIN publisher;

Answer 281

SELECT title, gift FROM books NATURAL JOIN promotion;

Answer 282

all of the above

Answer 283

SELECT title, authorid FROM books, bookauthor WHERE books.isbn = bookauthor.isbn AND retail > 20;

Answer 284

SELECT title, name FROM books CROSS JOIN publisher;

Answer 285

SELECT title, gift FROM books, promotion WHERE retail >= minretail AND retail <= maxretail;

Answer 286

none of the above

Answer 287

SELECT title, authorid FROM books, bookauthor WHERE books.isbn = bookauthor.isbn AND retail > 20;

Answer 288

SELECT title, name FROM books JOIN publisher USING (pubid);

Answer 289

SELECT title, gift FROM books, promotion WHERE retail >= minretail AND retail <= maxretail;

Answer 290

none of the above

Answer 291

non-equality

Answer 292

outer join

Answer 293

SELECT b.title, b.retail, o.quantity FROM books b NATURAL JOIN orders od NATURAL JOIN orderitems o WHERE od.order# = 1005;

Answer 294

SELECT b.title, b.retail, o.quantity FROM books b, orders od, orderitems o WHERE orders.order# = orderitems.order# AND orderitems.isbn=books.isbn AND od.order#=1005;

Answer 295

SELECT b.title, b.retail, o.quantity FROM books b, orderitems o WHERE o.isbn = b.isbn AND o.order#=1005;

Answer 296

none of the above

Answer 297

SELECT zip, order# FROM customers JOIN orders WHERE customers.customer# = orders.customer#;

Answer 298

SELECT zip, order# FROM customers, orders WHERE customers.customer# = orders.customer#;

Answer 299

SELECT zip, order# FROM customers, orders WHERE customers.customer# = orders.customer# (+);

Answer 300

none of the above

Answer 301

SELECT lastname, firstname, order# FROM customers c OUTER JOIN orders o ON c.customer# = o.customer# ORDER BY c.customer#;

Answer 302

SELECT lastname, firstname, order# FROM orders o RIGHT OUTER JOIN customers c ON c.customer# = o.customer# ORDER BY c.customer#;

Answer 303

SELECT lastname, firstname, order# FROM customers c, orders o WHERE c.customer# = o.customer# (+) ORDER BY c.customer#;

Answer 304

none of the above

Answer 305

SELECT zip FROM customers UNION SELECT category FROM books;

Answer 306

SELECT DISTINCT zip, category FROM customers c, orders o, orderitems oi, books b WHERE c.customer# = o.customer# AND o.order# = oi.order# AND oi.isbn = b.isbn;

Answer 307

SELECT DISTINCT zip, category FROM customers c JOIN orders o JOIN orderitems oi JOIN books b ON c.customer# = o.customer# AND o.order# = oi.order# AND oi.isbn = b.isbn;

Answer 308

all of the above

Answer 309

none of the above

Answer 310

SELECT title, gift FROM books NATURAL JOIN promotion;

Answer 311

SELECT title FROM books INTERSECT SELECT gift FROM promotion;

Answer 312

SELECT title FROM books UNION ALL SELECT gift FROM promotion;

Answer 313

all of the above

Answer 314

SELECT b.isbn, p.name FROM books b NATURAL JOIN publisher p;

Answer 315

SELECT isbn, name FROM books b, publisher p WHERE b.pubid = p.pubid;

Answer 316

SELECT isbn, name FROM books b JOIN publisher p ON b.pubid = p.pubid;

Answer 317

SELECT isbn, name FROM books JOIN publisher USING (pubid);

Answer 318

None—all the above are valid SQL statements.

Answer 319

SELECT title FROM books NATURAL JOIN publisher WHERE name = 'PRINTING IS US';

Answer 320

SELECT title FROM books, publisher WHERE pubname = 1;

Answer 321

SELECT * FROM books b, publisher p JOIN tables ON b.pubid = p.pubid WHERE name = 'PRINTING IS US';

Answer 322

none of the above

Answer 323

SELECT isbn FROM books MINUS SELECT isbn FROM orderitems;

Answer 324

SELECT isbn, name FROM books, publisher WHERE books.pubid (+) = publisher.pubid (+);

Answer 325

SELECT title, name FROM books NATURAL JOIN publisher

Answer 326

All the above SQL statements are valid.

Answer 327

If the relationship between the tables is established with a WHERE clause, both tables can include the outer join operator.

Answer 328

To include unmatched records in the results, the record is paired with a NULL record in the deficient table.

Answer 329

The RIGHT, LEFT, and FULL keywords are equivalent.

Answer 330

all of the above

Answer 331

none of the above

Answer 332

SELECT books.title, orderitems.quantity FROM books b, orderitems o WHERE b.isbn= o.ibsn;

Answer 333

SELECT title, quantity FROM books b JOIN orderitems o;

Answer 334

SELECT books.title, orderitems.quantity FROM books JOIN orderitems ON books.isbn = orderitems.isbn;

Answer 335

none of the above

Answer 336

SELECT SYSDATE;

Answer 337

SELECT UPPER(Hello) FROM dual;

Answer 338

SELECT TO_CHAR(SYSDATE, 'Month DD, YYYY') FROM dual;

Answer 339

all of the above

Answer 340

none of the above

Answer 341

SELECT order#, shipdate-orderdate delay FROM orders;

Answer 342

SELECT order#, SYSDATE – orderdate FROM orders WHERE shipdate IS NULL;

Answer 343

SELECT order#, NVL(shipdate, 0) FROM orders WHERE orderdate is NULL;

Answer 344

SELECT order#, NULL(shipdate) FROM orders;

Answer 345

SELECT "Hello World" FROM dual;

Answer 346

SELECT INITCAP('HELLO WORLD') FROM dual;

Answer 347

SELECT LOWER('HELLO WORLD') FROM dual;

Answer 348

SELECT "Hello World" FROM dual; (and) SELECT INITCAP('HELLO WORLD') FROM dual;

Answer 349

none of the above

Answer 350

NVL & SUBSTR

Answer 351

SUBSTR & NVL2

Answer 352

'HH:MM:SS'

Answer 353

'HH12:MI:SS'

Answer 354

All of the above are valid.

Answer 355

SELECT EXTRACT(phone, -4, 1) FROM publisher WHERE name ¼ 'AMERICAN PUBLISHING';

Answer 356

SELECT SUBSTR(phone, -4, 1) FROM publisher WHERE name = 'AMERICAN PUBLISHING';

Answer 357

SELECT EXTRACT(phone, -1, 4) FROM publisher WHERE name = 'AMERICAN PUBLISHING';

Answer 358

SELECT SUBSTR(phone, -4, 4) FROM publisher WHERE name = 'AMERICAN PUBLISHING';

Answer 359

MONTH_BETWEEN

Answer 360

none of the above

Answer 361

SELECT TO_CHAR(orderdate, 'MM/DD') FROM orders WHERE order# = 1000;

Answer 362

SELECT TO_CHAR(orderdate, 'Mth/DD') FROM orders WHERE order# = 1000;

Answer 363

SELECT TO_CHAR(orderdate, 'MONTH/YY') FROM orders WHERE order# = 1000;

Answer 364

none of the above

Answer 365

SELECT TO_CHAR(orderdate, '99/9999') FROM orders;

Answer 366

SELECT INITCAP(firstname), UPPER(lastname) FROM customers;

Answer 367

SELECT cost, retail, TO_CHAR(retail-cost, '$999.99') profit FROM books;

Answer 368

all of the above

Answer 369

none of the above

Answer 370

SELECT ROUND(24.37, 2) FROM dual;

Answer 371

SELECT TRUNC(29.99, 2) FROM dual;

Answer 372

SELECT ROUND(29.01, -1) FROM dual;

Answer 373

SELECT TRUNC(29.99, -1) FROM dual;

Answer 374

SELECT TRUNC(ROUND(125.38, 1), 0) FROM dual;

Answer 375

SELECT ROUND(TRUNC(125.38, 0) FROM dual;

Answer 376

SELECT LTRIM(LPAD(state, 5, ' '), 4, -3, "*") FROM dual;

Answer 377

SELECT SUBSTR(ROUND(14.87, 2, 1), -4, 1) FROM dual;

Answer 378

INITIALCAP

Answer 379

All of the above can be used for case conversion.

Answer 380

none of the above

Answer 381

SELECT UPPER('firstname', 'lastname') FROM customers;

Answer 382

SELECT UPPER(firstname, lastname) FROM customers;

Answer 383

SELECT UPPER(lastname, ',' firstname) FROM customers;

Answer 384

none of the above

Answer 385

none of the above

Answer 386

an integer

Answer 387

a whole number

Answer 388

a NULL value

Answer 389

None of the above—a syntax error message is returned.

Answer 390

The MIN function can be used only with numeric data.

Answer 391

The MAX function can be used only with date values.

Answer 392

The AVG function can be used only with numeric data.

Answer 393

The SUM function can’t be part of a nested function.

Answer 394

SELECT AVG(retail-cost) FROM books GROUP BY category;

Answer 395

SELECT category, AVG(retail-cost) FROM books;

Answer 396

SELECT category, AVG(retail-cost) FROM books WHERE AVG(retail-cost) > 8.56 GROUP BY category;

Answer 397

SELECT category, AVG(retail-cost) Profit FROM books GROUP BY category HAVING profit > 8.56;

Answer 398

The WHERE clause can contain a group function only if the function isn’t also listed in the SELECT clause.

Answer 399

Group functions can’t be used in the SELECT, FROM, or WHERE clauses.

Answer 400

The HAVING clause is always processed before the WHERE clause.

Answer 401

The GROUP BY clause is always processed before the HAVING clause.

Answer 402

SELECT MIN(pubdate) FROM books GROUP BY category HAVING pubid = 4;

Answer 403

SELECT MIN(pubdate) FROM books WHERE category = 'COOKING';

Answer 404

SELECT COUNT(*) FROM orders WHERE customer# = 1005;

Answer 405

SELECT MAX(COUNT(customer#)) FROM orders GROUP BY customer#;

Answer 406

The COUNT function can be used to determine how many rows contain a NULL value.

Answer 407

Only distinct values are included in group functions, unless the ALL keyword is included in the SELECT clause.

Answer 408

The HAVING clause restricts which rows are processed.

Answer 409

The WHERE clause determines which groups are displayed in the query results.

Answer 410

none of the above

Answer 411

SELECT customer#, order#, MAX(shipdate-orderdate) FROM orders GROUP BY customer# WHERE customer# = 1001;

Answer 412

SELECT customer#, COUNT(order#) FROM orders GROUP BY customer#;

Answer 413

SELECT customer#, COUNT(order#) FROM orders GROUP BY COUNT(order#);

Answer 414

SELECT customer#, COUNT(order#) FROM orders GROUP BY order#;

Answer 415

SELECT title, MAX(retail-cost) FROM books GROUP BY title;

Answer 416

SELECT title, MAX(retail-cost) FROM books GROUP BY title HAVING MAX(retail-cost);

Answer 417

SELECT title, MAX(retail-cost) FROM books;

Answer 418

none of the above

Answer 419

A group function can be nested inside a group function.

Answer 420

A group function can be nested inside a single-row function.

Answer 421

A single-row function can be nested inside a group function.

Answer 422

A group function can be nested inside a group function, and A group function can be nested inside a single-row function.

Answer 423

A group function can be nested inside a group function, and A group function can be nested inside a single-row function, and A single-row function can be nested inside a group function.

Answer 424

SELECT MAX(retail) FROM books WHERE category = 'FAMILY';

Answer 425

SELECT MAX(retail) FROM books HAVING category = 'FAMILY';

Answer 426

SELECT retail FROM books WHERE category = 'FAMILY' HAVING MAX(retail);

Answer 427

none of the above

Answer 428

You must enter the ALL keyword in a group function to include all duplicate value

Answer 429

The AVG function can be used to find the average calculated difference between two dates.

Answer 430

The MIN and MAX functions can be used on any type of data.

Answer 431

all of the above

Answer 432

none of the above

Answer 433

SELECT customer#, SUM(referred) FROM customers GROUP BY customer#;

Answer 434

SELECT COUNT(referred) FROM customers;

Answer 435

SELECT COUNT(*) FROM customers;

Answer 436

SELECT COUNT(*) FROM customers WHERE referred IS NULL;

Answer 437

the number of records in the specified tables

Answer 438

the number of orders placed by each customer

Answer 439

the number of NULL values in the specified table

Answer 440

the number of customers who have placed an order

Answer 441

COUNT function

Answer 442

MAX function

Answer 443

MIN function

Answer 444

STDDEV function

Answer 445

VARIANCE function

Answer 446

SELECT STDDEV(retail) FROM books;

Answer 447

SELECT AVG(SUM(retail)) FROM orders NATURAL JOIN orderitems NATURAL JOIN books GROUP BY customer#;

Answer 448

SELECT order#, TO_CHAR(SUM(retail),'999.99') FROM orderitems JOIN books USING (isbn) GROUP BY order#;

Answer 449

SELECT title, VARIANCE(retail-cost) FROM books GROUP BY pubid;

Answer 450

SELECT customer# FROM customers WHERE state = (SELECT state FROM customers WHERE lastname = 'SMITH');

Answer 451

SELECT customer# FROM customers WHERE state = (SELECT state FROM customers WHERE lastname = 'SMITH' OR firstname = 'LEILA');

Answer 452

SELECT customer# FROM customers WHERE state = (SELECT state FROM customers WHERE lastname = 'SMITH' AND firstname = 'LEILA' ORDER BY customer);

Answer 453

SELECT customer# FROM customers WHERE state = (SELECT state FROM customers WHERE lastname = 'SMITH' AND firstname = 'LEILA');

Answer 454

SELECT order# FROM orders WHERE shipdate = SELECT shipdate FROM orders WHERE order# = 1010;

Answer 455

SELECT order# FROM orders WHERE shipdate = (SELECT shipdate FROM orders) AND order# = 1010;

Answer 456

SELECT order# FROM orders WHERE shipdate = (SELECT shipdate FROM orders WHERE order# = 1010);

Answer 457

SELECT order# FROM orders HAVING shipdate = (SELECT shipdate FROM orders WHERE order# = 1010);

Answer 458

SELECT order# FROM orders WHERE customer# = 1017;

Answer 459

SELECT customer# FROM orders JOIN orderitems USING(order#) WHERE isbn = (SELECT isbn FROM orderitems WHERE customer# = 1017);

Answer 460

SELECT customer# FROM orders WHERE order# = (SELECT order# FROM orderitems WHERE customer# = 1017);

Answer 461

SELECT customer# FROM orders JOIN orderitems USING(order#) WHERE isbn IN (SELECT isbn FROM orderitems JOIN orders USING(order#) WHERE customer# = 1017);

Answer 462

SELECT title FROM books WHERE retail <(SELECT cost FROM books WHERE isbn = '9959789321');

Answer 463

SELECT title FROM books WHERE retail = (SELECT cost FROM books WHERE isbn = '9959789321' ORDER BY cost);

Answer 464

SELECT title FROM books WHERE category IN (SELECT cost FROM orderitems WHERE isbn = '9959789321');

Answer 465

none of the above statements

Answer 466

If a subquery is used in the outer query's FROM clause, the data in the temporary table can't be referenced by clauses used in the outer query.

Answer 467

The temporary table created by a subquery in the outer query's FROM clause must be assigned a table alias, or it can't be joined with another table by using the JOIN keyword.

Answer 468

If a temporary table is created through a subquery in the outer query's FROM clause, the data in the temporary table can be referenced by another clause in the outer query.

Answer 469

none of the above

Answer 470

SELECT customer# FROM customers WHERE referred = (SELECT referred FROM customers WHERE firstname = 'JORGE' AND lastname = 'PEREZ');

Answer 471

SELECT referred FROM customers WHERE (customer#, referred) = (SELECT customer# FROM customers WHERE firstname = 'JORGE' AND lastname = 'PEREZ');

Answer 472

SELECT referred FROM customers WHERE (customer#, referred) IN (SELECT customer# FROM customers WHERE firstname = 'JORGE' AND lastname = 'PEREZ');

Answer 473

SELECT customer# FROM customers WHERE customer# = (SELECT customer# FROM customers WHERE firstname = 'JORGE' AND lastname = 'PEREZ');

Answer 474

when you need to find all customers living in a particular region of the country

Answer 475

when you need to find all publishers who have toll-free telephone numbers

Answer 476

when you need to find the titles of all books shipped on the same date as an order placed by a particular customer

Answer 477

when you need to find all books published by Publisher

Answer 478

SELECT customer# FROM orders JOIN books USING(isbn) WHERE isbn = (SELECT isbn FROM orderitems JOIN books USING(isbn) WHERE customer# = 1001 OR customer# = 1005));

Answer 479

SELECT customer# FROM orders JOIN books USING(isbn) WHERE isbn <ANY (SELECT isbn FROM orderitems JOIN books USING(isbn) WHERE customer# = 1001 OR customer# = 1005));

Answer 480

SELECT customer# FROM orders JOIN books USING(isbn) WHERE isbn = (SELECT isbn FROM orderitems JOIN orders USING(order#) WHERE customer# = 1001 OR 1005));

Answer 481

SELECT customer# FROM orders JOIN orderitems USING(order#) WHERE isbn IN (SELECT isbn FROM orders JOIN orderitems USING(order#) WHERE customer# IN (1001, 1005));

Answer 482

SELECT customer# FROM orders JOIN orderitems USING(order#) HAVING SUM(quantity) = (SELECT MAX(SUM(quantity)) FROM orders JOIN orderitems USING(order#) GROUP BY customer#) GROUP BY customer#;

Answer 483

SELECT customer# FROM orders JOIN orderitems USING(order#) WHERE SUM(quantity) = (SELECT MAX(SUM(quantity)) FROM orderitems GROUP BY customer#);

Answer 484

SELECT customer# FROM orders WHERE MAX(SUM(quantity)) = (SELECT MAX(SUM(quantity) FROM orderitems GROUP BY order#);

Answer 485

SELECT customer# FROM orders HAVING quantity = (SELECT MAX(SUM(quantity)) FROM orderitems GROUP BY customer#);

Answer 486

The IN comparison operator can't be used with a subquery that returns only one row of results.

Answer 487

The equals (=) comparison operator can't be used with a subquery that returns more than one row of results.

Answer 488

In an uncorrelated subquery, statements in the outer query are executed first, and then statements in the subquery are executed.

Answer 489

A subquery can be nested only in the outer query's SELECT clause.

Answer 490

It determines which publisher published a book belonging to the Oracle category and then lists all other books published by that same publisher.

Answer 491

It lists all publishers and categories containing the value ORACLE.

Answer 492

It lists the ISBN and title of all books belonging to the same category and having the same publisher as any book with the phrase ORACLE in its title.

Answer 493

None of the above. The query contains a multiple-row operator, and because the inner query returns only one value, the SELECT statement will fail and return an error message.

Answer 494

The inner query needs to reference the value returned to the outer query.

Answer 495

The value returned by the inner query is to be compared to grouped data in the outer query.

Answer 496

The subquery returns more than one value to the outer query.

Answer 497

None of the above. Subqueries can't be used in the outer query's HAVING clause.

Answer 498

SELECT title FROM books WHERE isbn IN (SELECT isbn FROM bookauthor HAVING authorid IN 'THE WOK WAY TO COOK);

Answer 499

SELECT isbn FROM bookauthor WHERE authorid IN (SELECT authorid FROM books JOIN bookauthor USING(isbn) WHERE title = 'THE WOK WAY TO COOK');

Answer 500

SELECT title FROM bookauthor WHERE authorid IN (SELECT authorid FROM books JOIN bookauthor USING(isbn) WHERE title = 'THE WOK WAY TO COOK);

Answer 501

SELECT isbn FROM bookauthor HAVING authorid = SELECT authorid FROM books JOIN bookauthor USING(isbn) WHERE title = 'THE WOK WAY TO COOK';

Answer 502

If the subquery returns only a NULL value, the only records returned by an outer query are those containing an equivalent NULL value.

Answer 503

A multiple-column subquery can be used only in the outer query's FROM clause.

Answer 504

A subquery can contain only one condition in its WHERE clause.

Answer 505

The order of columns listed in the SELECT clause of a multiple-column subquery must be in the same order as the corresponding columns listed in the outer query's WHERE clause.

Answer 506

WHEN MATCHED

Answer 507

WHEN NOT MATCHED

Answer 508

INSERTs aren't allowed in a MERGE statement.

Answer 509

The statement doesn't execute because the subquery and outer query don't reference the same table.

Answer 510

The outer query removes duplicates in the subquery's Order# list.

Answer 511

The query fails if only one result is returned to the outer query because the outer query's WHERE clause uses the IN comparison operat

Answer 512

No rows are displayed because the ISBN in the WHERE clause is enclosed in single quotation marks.

Answer 513

The SELECT statement fails and returns an Oracle error message.

Answer 514

The outer query displays no rows in its results because the subquery passes a NULL value to the outer query

Answer 515

The customer number is displayed for customers whose orders haven't yet shipped.

Answer 516

The customer number of all customers who haven't placed an order are displayed.

Answer 517

No DML operations can be performed on the CHANGEADDRESS view.

Answer 518

The CHANGEADDRESS view is a simple view.

Answer 519

The CHANGEADDRESS view is a complex view.

Answer 520

The CHANGEADDRESS view is an inline view.

Answer 521

UPDATE changeaddress SET shipstreet = '958 ELM ROAD' WHERE customer# = 1020;

Answer 522

INSERT INTO changeaddress VALUES (9999, 'LAST', 'FIRST', 9999, '123 HERE AVE', 'MYTOWN', 'AA', 99999);

Answer 523

DELETE FROM changeaddress WHERE customer# = 1020;

Answer 524

all of the above

Answer 525

none of the above

Answer 526

CUSTOMERS table

Answer 527

ORDERS table

Answer 528

Both tables together serve as a composite key-preserved table.

Answer 529

none of the above

Answer 530

Shipstreet

Answer 531

CUSTOMERS and ORDER

Answer 532

Neither—the DELETE command can’t be used on the CHANGEADDRESS view.

Answer 533

ROWNUM can’t be used with the view because it isn’t included in the results the subquery returns.

Answer 534

The view is a simple view because it doesn’t include a group function or a GROUP BY clause.

Answer 535

The data in the view can’t be displayed in descending order by customer number because an ORDER BY clause isn’t allowed when working with views.

Answer 536

all of the above

Answer 537

none of the above

Answer 538

The CHANGEADDRESS view can’t be used to update an order’s ship date because of the WITH CHECK OPTION constraint.

Answer 539

The CHANGEADDRESS view can’t be used to update an order’s ship date because the Shipdate column isn’t included in the view.

Answer 540

The CHANGEADDRESS view can’t be used to update an order’s ship date because the ORDERS table is not the key-preserved table.

Answer 541

The CHANGEADDRESS view can’t be used to update an order’s ship date because the UPDATE command can’t be used on data in the view.

Answer 542

No DML operations can be performed on the CHANGENAME view.

Answer 543

The CHANGENAME view is a simple view.

Answer 544

The CHANGENAME view is a complex view.

Answer 545

The CHANGENAME view is an inline view.

Answer 546

The view doesn’t have or need a primary key.

Answer 547

All of the above are valid DML operations for the CHANGENAME view

Answer 548

A key-preserved table isn’t included in the view.

Answer 549

The view was created with the WITH CHECK OPTION constraint.

Answer 550

The inserted record couldn’t be accessed by the view.

Answer 551

None of the above—an INSERT command can be used on the table as long as the PRIMARY KEY constraint isn’t violated.

Answer 552

The CREATE OR REPLACE VIEW command can be used to re-create the view with the necessary column included in the new view.

Answer 553

The ALTER VIEW . . . ADD COLUMN command can be used to add the necessary column to the existing view.

Answer 554

The CHANGENAME view can be dropped, and then the CREATE VIEW command can be used to re-create the view with the necessary column included in the new view.

Answer 555

All of the above can be performed to include the customer’s zip code in the view.

Answer 556

Only (The CREATE OR REPLACE VIEW command can be used to re-create the view with the necessary column included in the new view.) and (The CHANGENAME view can be dropped, and then the CREATE VIEW command can be used to re-create the view with the necessary column included in the new view.) include the customer’s zip code in the view.

Answer 557

None of the above includes the customer’s zip code in the view.

Answer 558

All of the above can be performed on a view containing a group function

Answer 559

None of the above can be performed on a view containing a group function.

Answer 560

views created with the WITH READ ONLY option

Answer 561

views that include the DISTINCT keyword

Answer 562

views that include a GROUP BY clause

Answer 563

All of the above allow DML operations.

Answer 564

None of the above allow DML operations.

Answer 565

the highest ROWNUM values

Answer 566

a ROWNUM value greater than or equal to N

Answer 567

the lowest ROWNUM values

Answer 568

a ROWNUM value less than or equal to N

Answer 569

Assign aliases in the subquery, and the aliases are used for the column names.

Answer 570

Use the ALTER VIEW command to change column names.

Answer 571

Assign names for up to three columns in the CREATE VIEW clause before the subquery is listed in the AS clause.

Answer 572

None of the above—columns can’t be assigned names for a view; they must keep their original names.

Answer 573

The ORDER BY clause can’t be used in the subquery of a CREATE VIEW command.

Answer 574

The ORDER BY clause can’t be used in an inline view.

Answer 575

The DISTINCT keyword can’t be used in an inline view.

Answer 576

The WITH READ ONLY option must be used with an inline view.

Answer 577

virtual column not allowed here

Answer 578

data manipulation operation not legal on this view

Answer 579

cannot map to a column in a non-key-preserved table

Answer 580

None of the above—no error message is returned.

Answer 581

data from one or more tables

Answer 582

an expression

Answer 583

a GROUP BY clause for data retrieved from one table

Answer 584

five columns from one table

Answer 585

all of the above

Answer 586

none of the above

Answer 587

data from one or more tables

Answer 588

an expression

Answer 589

a GROUP BY clause for data retrieved from one table

Answer 590

five columns from one table

Answer 591

all of the above

Answer 592

none of the above

	Created by Yorria Raine about 9 years ago

	Copied by Susan Swart almost 4 years ago

Next up

Midterm 2 (Chapter 5 - 13)

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