College Brighten

CHAPTER 9

Question 1 2 / 2 points

The set operators do NOT include which one of the following keywords:

ALL

SET

MINUS

UNION

Question 2

You are tasked with cleaning up a database application. There are two tables in the database: ORDERS contains completed orders, and ORDER_RETURNS contains duplicate information for any orders that were returned. Your goal is to find out if there are any rows in ORDER_RETURNS that are not in the ORDERS table. Which of the following set operators should you use?

UNION ALL

UNION

MINUS

INTERSECT

Question 3

When combining two SELECT statements, which of the following set operators will produce a different result, depending on which SELECT statement precedes or follows the operator? (Choose two.)

MINUS

UNION ALL

INTERSECT

UNION

Question 4

Which one of the following statements about set operators is true?

If you add the word ALL to the end of any set operator, it will change the behavior by retaining duplicate rows

Set operators may be used to combine two INSERT statements.

Set operators are used to combine two SELECT statements.

The UNION set operator has precedence over the others.

Question 5

CAT# ITEM_NAME ADDED SECTION

------- ---------------- --------- ------------- (FURNISHINGS table)

1 Side table 23-DEC-11 LR

2 Desk 10-SEP-10 BR

3 Towel 10-OCT-11 BA

NUM AISLE PRODUCT LAST_ORDER

------ ---------- --------------- ----------------- (STORE_INVENTORY table)

77 F02 Jacket 2009-09-09

78 B11 Towel 2010-11-11

79 SP01 Lava Lamp 2011-12-21

How many rows will results from the following query:

SELECT NUM, PRODUCT FROM STORE_INVENTORY

INTERSECT

SELECT CAT#, ITEM_NAME FROM FURNISHINGS;

Question 6

CAT# ITEM_NAME ADDED SECTION

------- ---------------- --------- ------------ (FURNISHINGS table)

1 Side table 23-DEC-11 LR

2 Desk 10-SEP-10 BR

3 Towel 10-OCT-11 BA

NUM AISLE PRODUCT LAST_ORDER

------ ---------- --------------- ---------------- (STORE_INVENTORY table)

77 F02 Jacket 2009-09-09

78 B11 Towel 2010-11-11

79 SP01 Lava Lamp 2011-12-21

How many rows will results from the following query:

SELECT TO_CHAR(LAST_ORDER, 'Month'), AISLE

FROM STORE_INVENTORY

UNION ALL

SELECT '--', SECTION

FROM FURNISHINGS

WHERE CAT# NOT IN (1, 2);

It will not execute because it will fail with a syntax error.

Question 7

CAT# ITEM_NAME ADDED SECTION

------- ---------------- --------- ------------ (FURNISHINGS table)

1 Side table 23-DEC-11 LR

2 Desk 10-SEP-10 BR

3 Towel 10-OCT-11 BA

NUM AISLE PRODUCT LAST_ORDER

------ ---------- --------------- ----------------- (STORE_INVENTORY table)

77 F02 Jacket 2009-09-09

78 B11 Towel 2010-11-11

79 SP01 Lava Lamp 2011-12-21

What will result from the following query:

SELECT LAST_ORDER

FROM STORE_INVENTORY

ORDER BY 1

UNION

SELECT ADDED

FROM FURNISHINGS;

It will fail with a syntax error because there are two date formats for the date column.

It will fail with a syntax error because you cannot use an ORDER BY clause in this context.

It will execute successfully.

It will fail with a syntax error because the FURNISHINGS table SELECT statement must be listed first.

Question 8

In order for two sets to be considered "union-compatible" the number of expressions selected in the select lists must be identical in each SELECT statement, and that is the only criteria.

True

False

Question 9

For Set A MINUS Set B, start with Set A, and then eliminate any items from A that also appear in B.

True

False

Question 10

The column names in the result-set of a UNION are equal to the column names in the first SELECT statement in the UNION.

True

False

Question 11

The two SELECT statements combined with a UNION, INTERNSECT, or MINUS cannot include certain complex features, such as GROUP BY clauses, and subqueries.

True

False

Question 12

CAT# ITEM_NAME ADDED SECTION

------- ---------------- --------- ------------- (FURNISHINGS table)

1 Side table 23-DEC-11 LR

2 Desk 10-SEP-10 BR

3 Towel 10-OCT-11 BA

NUM AISLE PRODUCT LAST_ORDER

------ ---------- --------------- ----------------- (STORE_INVENTORY table)

77 F02 Jacket 2009-09-09

78 B11 Towel 2010-11-11

79 SP01 Lava Lamp 2011-12-21

What will happen when this query is executed:

SELECT (SELECT last_order from STORE_INVENTORY UNION

SELECT Added "DATE ADDED" FROM furnishings)

FROM DUAL;

It will fail due to an error on Line 1

It will execute, but the UNION will not work as expected

It will execute and display one row under the column "DATE ADDED"

It will execute and display six rows under the column "DATE ADDED"

Question 13

CAT# ITEM_NAME ADDED SECTION

------- ---------------- --------- ------------- (FURNISHINGS table)

1 Side table 23-DEC-11 LR

2 Desk 10-SEP-10 BR

3 Towel 10-OCT-11 BA

NUM AISLE PRODUCT LAST_ORDER

------ ---------- --------------- ----------------- (STORE_INVENTORY table)

77 F02 Jacket 2009-09-09

78 B11 Towel 2010-11-11

79 SP01 Lava Lamp 2011-12-21

(SELECT PRODUCT FROM STORE_INVENTORY

UNION ALL SELECT ITEM_NAME FROM FURNISHINGS)

INTERSECT

(SELECT ITEM_NAME FROM FURNISHINGS WHERE Cat# = 3

UNION ALL SELECT PRODUCT FROM STORE_INVENTORY WHERE num = 78);

This statement will return 1 row.

This statement will return 2 rows.

This statement will return 4 rows.

This statement will return 3 rows.

CHAPTER 10

Question 1

Which of the following commands may be rolled back (select all that apply)?

COMMIT

DELETE

INSERT

TRUNCATE

Question 2

Consider the ADDRESS table that has the following 3 columns and no row:

ID NUMBER NOT NULL

ZONE NUMBER

ZIP_CODE VARCHAR2(5)

COMMIT;

INSERT INTO ADDRESS VALUES (1, 1, '94506');

SAVEPOINT ZONE_ADDRESS_1;

UPDATE ADDRESS SET ZONE = 2 WHERE ZIP_CODE ='94506';

ROLLBACK;

ADDRESS will have 1 row with a value of 1 for ZONE.

ADDRESS will have 1 row with a value of 2 for ZONE.

the ADDRESS table will have no rows.

The statements will generate a syntax error.

Question 3

The basic INSERT statement can only insert one row at a time, but can be used to insert multiple rows when the values to be inserted come from a SELECT statement on another table.

True

False

Question 4

A transaction consists of one or more DML statements, followed by either a COMMIT or a ROLLBACK command.

True

False

Question 5

How can you change the primary key value of a row? (choose the best answer)

You cannot change the primary key value.

Change it with an UPDATE statement, using any of the primary key values that already exist.

The row must be removed first using a DELETE command, then reentered with an INSERT command containing the new primary key value.

The UPDATE command may be used, but only if the value being inserted is not a duplicate of any primary key values that already exist.

Question 6

If an UPDATE or DELETE command has a WHERE clause that gives it a scope of several rows, what will happen if there is an error part way through execution?

The command will skip the row that caused the error and continue execution.

The command will stop at the error, and any rows that have been updated or deleted will remain updated or deleted.

The entire UPDATE or DELETE command will fail, and any updates or deletes that have occured will be rolled back.

This type of error cannot happen because UPDATE and DELETE commands are checked for syntax errors prior to execution.

Question 7

If an UPDATE or DELETE command has a WHERE clause that gives it a scope of several rows, what will happen if there is an error part way through execution? The UPDATE or DELETE command is part of a multi-command transaction.

The command will skip the row that caused the error and continue execution.

The command will stop at the error, and any rows that have been updated or deleted will remain updated or deleted.

The entire transaction will be rolled back.

The erroneous UPDATE or DELETE command will be rolled back but any changes already done by the transaction will remain.

Question 8

If a table T3 has four numeric columns (A, B, C, D) and no primary key, which of these statements will succeed? Choose all that apply.

INSERT INTO T3 VALUES (3, 6, 7, NULL);

INSERT INTO T3 VALUES ('3', '9', '10', '12');

INSERT INTO T3 SELECT * FROM T3;

NONE OF THESE INSERT STATEMENTS WILL SUCCEED.

Question 9

Suppose a table T1 contains the following data:

C1 C2 C3 C4

---- ---- ---- ----

1 2 3 4

5 6 7 8

Why does the following statement cause an error?

INSERT INTO T1

VALUES (SELECT c1, c2 FROM T1);

Because there are not enough values provided for all of T1's columns; there should be NULL's listed for c3 and c4.

Since the subquery returns multiple rows, it should contain a Where clause to restrict the number of rows to one.

The subquery should contain a MAX or MIN to make it scalar.

The VALUES keyword cannot be used when you are populating a table strictly with a subquery.

Question 10

What will be the result of executing this statement?

INSERT INTO REGIONS (Region_ID, Region_Name)

VALUES ((SELECT MAX(Region_ID) + 1 FROM REGIONS), 'Great Britain');

(Region_ID is the primary key of the REGIONS table.)

The statement will fail because the value generated for Region_ID might not be unique.

The statement will fail beause you cannot use the VALUES keyword with a subquery.

The statement will run without an error.

The statement will fail if the REGIONS table has a third column.

Question 11

What will be the result of executing the following DML statement:

UPDATE employees SET salary = salary + 100;

The statement will fail because there is no WHERE clause to restrict the rows updated.

The first row in the table will be updated.

An error will be generated if any rows contain a salary value that is NULL.

Every row in the EMPLOYEES table is updated, except for the rows where salary is null.

Question 12

To delete the values from one entire column in a table, you would use the ______ command.

DELETE COLUMN

DROP COLUMN

UPDATE

ALTER TABLE

Question 13

Which of these commands will remove every row in a table, but not delete the table itself? Choose one or more answers.

A DELETE command with no WHERE clause

A DROP TABLE command

A TRUNCATE command

UPDATE command, setting every column to NULL with no WHERE clause.

Question 14

A user named SALLY updates some rows, and asks another user MELVIN to login and check the changes before she commits them. Which of the following statements is true about this situation? (Choose the best answer.)

MELVIN can see SALLY's changes but cannot alter them until SALLY commits.

MELVIN cannot see SALLY's updates because she has not entered the COMMIT command.

SALLY must commit the updates, so that MELVIN can view them, and if necessary roll them back.

SALLY must commit the updates, so that MELVIN can view them, but only SALLY can roll them back.

Question 15

Which of the following commands will terminate a transaction (choose two.)

COMMIT

INSERT

ROLLBACK

SAVEPOINT

Question 16

CREATE TABLE STUDENT_LIST

(STUDENT_ID NUMBER,

STUDENT_NAME VARCHAR2(30),

STUDENT_PHONE VARCHAR2(20));

INSERT INTO STUDENT_LIST VALUES (1, 'Joe Wookie', 3185551212);

The table will create succesfully. What will result from the INSERT statement execution?

It will fail because there is no column listing

It will fail because the value for STUDENT_PHONE is numeric, and STUDENT_PHONE is a character datatype.

It will execute and the table will contain one row of data.

It will fail because no primary key has been specified for the table.

CHAPTER 11

Question 1

For the datatype VARCHAR2(n), the n is an optional number that indicates the maximum allowable number of characters.

True

False

Question 2

For the datatype NUMBER(n, m), n and m are optional numbers, where n specifies the "precision", and m specifies the "scale".

True

False

Question 3

The datatype TIMESTAMP(n) stores year, month, day, hours, minutes, seconds, and fractional seconds, where n is an optional number that gives the precision for fractional seconds.

True

False

Question 4

If the database must store a binary object, such as an image or a video file, the field is usually defined to be the datatype CLOB.

True

False

Question 5

To add a contraint to an existing table, you would use the CREATE CONSTRAINT statement.

True

False

Question 6

Consider the following SQL statement:

CREATE TABLE vendors (

vendor_id NUMBER PRIMARY KEY,

vendor_name VARCHAR2(30),

status NUMBER(1) NOT NULL);

The Primary Key and Not Null constraints are said to be ______ constraints. (Two choices are correct.)

row level

column level

in-line

implicit

Question 7

When creating a table, you defined a column:

CUSTOMER_ID CHAR(4) PRIMARY KEY,

This type of definition is considered good practice, since even though you did not provide a name for the constraint, the system automatically assigned one.

True

False

Question 8

Consider the following statement:

CREATE TABLE PORTS

(port_id NUMBER,

port_name VARCHAR2(20));

Which of the following modifications to the statement is the only valid way to declare that the port_name is required (not optional)?

port_name VARCHAR2(20),

NOT NULL (port_name));

port_name VARCHAR2(20),

CONSTRAINT ports_port_name_nn NOT NULL (port_name));

port_name VARCHAR2(20) NOT NULL);

ALTER TABLE PORTS

CONSTRAINT ports_port_name_nn NOT NULL (port_name);

Question 9

What does the following constraint on the EMP table do:

CONSTRAINT emp_hire_date_cc CHECK (hire_date >= dob + 365*16)

(hire_date is the date hired, and dob is date of birth)

It checks that employees dob is valid (that it is a valid date)

It makes sure all date of births fall between January 1, 1986 and the present.

It makes sure that new rows cannot be inserted with a missing date of birth.

It insures that new employees are at least 16 years old. (at the time their record is inserted into the EMP table.)

Question 10

Which of the following are valid CREATE TABLE statements? (Choose all that are valid.)

CREATE TABLE $ORDERS (ID NUMBER);

CREATE TABLE WORK_ORDERS (ID NUMBER);

CREATE TABLE "Boat Inventory" (ID NUMBER);

CREATE TABLE WorkSchedule (ID NUMBER);

Question 11

CREATE TABLE NUMBER_TEST (ACE NUMBER(5,3));

INSERT INTO NUMBER_TEST (ACE) VALUES (3.1415);

SELECT ACE FROM NUMBER_TEST;

what is the displayed output of the SELECT statement?

3.1415

3.142

3.141

######

Question 12

Which of the following will create a table that rejects attempts to INSERT a row with NULL values entered into the PORT_ID column? Choose all that apply.

CREATE TABLE PORTS (

PORT_ID NUMBER(3),

CONSTRAINT PORTS_PORT_ID_UN UNIQUE(PORT_ID));

CREATE TABLE PORTS (

PORT_ID NUMBER(3),

CONSTRAINT PORTS_PORT_ID_PD PRIMARY KEY(PORT_ID));

CREATE TABLE PORTS (

PORT_ID NUMBER(3),

CONSTRAINT PORTS_PORT_ID_NN NOT NULL (PORT_ID));

CREATE TABLE PORTS ( PORT_ID NUMBER(3) NOT NULL);

Question 13

What will be the result of executing this statement? (assume no shipping_order table already exists.)

CREATE TABLE shipping_order

( order_ID NUMBER,

order_year CHAR(2),

customer_id NUMBER,

CONSTRAINT shipping_order PRIMARY KEY(order_id, order_year));

The statement will have an error because the datatype of order_year is CHAR(2) and not DATE.

The statement will generate an error because there are two columns defined in the PRIMARY KEY constraint.

The statement will run and the table will be created; however the primary key constraint will not be created because the constraint name does not contain the "_pk" suffix.

The statement will run: the table and primary key constraint will be created.

Question 14

Assume that there is no PERSONNEL table in the database. What will be the result of an attempt to execute the following:

CREATE TABLE PERSONNEL (

PER_ID NUMBER(6),

DIVISION_ID NUMBER(6),

CONSTRAINT per_ID_pk PRIMARY KEY (PER_ID),

CONSTRAINT div_ID_pk PRIMARY KEY (DIVISION_ID));

The statement will fail because it is trying to create two primary key constraints for the same table.

The statement will create the table and the first primary key (not the second)

The statement will create the table and a composite primary key consisting of two columns.

The statement will successfully create the table and the first primary key as Per_ID, and an alternate primary key as Division_id.

Question 15

After executing the following SQL statements:

CREATE TABLE INVOICES (INV_ID NUMBER, DISCOUNT NUMBER));

INSERT INTO INVOICES VALUES (7, 5);

INSERT INTO INVOICES VALUES (3, 12);

Which of the following SQL statements will fail? (Choose two.)

ALTER TABLE INVOICES MODIFY DISCOUNT PRIMARY KEY;

ALTER TABLE INVOICES MODIFY DISCOUNT VARCHAR2(3);

ALTER TABLE INVOICES MODIFY DISCOUNT DEFAULT 'ZERO';

ALTER TABLE INVOICES MODIFY INV_ID PRIMARY KEY;

Question 16

You have a table called CUSTOMERS and you need to change the name of a column from NAME to LAST_NAME. Which of the following statements would accomplish that. CUSTOMERS table already contains some data.

RENAME NAME TO LAST_NAME;

ALTER TABLE CUSTOMERS

RENAME COLUMN NAME

TO LAST_NAME;

RENAME COLUMN NAME

TO COLUMN LAST_NAME;

IT CAN'T BE DONE.

Question 17

Suppose the database contains a table SHIPS, with primary key SHIP_ID. You are creating another table called CRUISES that contains information about cruises that will occur on particular ships. You need to know which ship will be hosting which cruise. What type of constraint will you need?

PRIMARY KEY

CHECK

NOT NULL

FOREIGN KEY

Question 18

When a table is created with a statement such as:

CREATE TABLE NEW_EMPS AS

SELECT * FROM EMPLOYEES

WHERE HIRE_DATE = SYSDATE;

Will there be any constraints on the NEW_EMPS table?

No contraints are applied when creating a table using a subquery.

All of the contraints from EMPLOYEES will be applied to NEW_EMPS.

Check and NOT NULL constraints will be applied to the new table, but not primary or foreign key constraints

Primary and foreign key constraints will be applied to the new table, but not Check or NOT NULL constraints.

Question 19

What is the result of running this statement:

CREATE TABLE DEPT50

AS SELECT * FROM DEPARTMENTS

WHERE 50 = 60;

The statement will fail because 50 = 60 is logically impossible.

No table will be created.

The table will be created but contain no rows.

The table will be created and will contain all of the rows from the DEPARTMENTS table.

CHAPTER 12

Question 1

Which of the following SQL statements can be executed on any simple or complex VIEW? (Choose all that apply.)

SELECT

INSERT

UPDATE

DELETE

Question 2

Which of these is a characteristic of a complex view, rather than a simple view? (Choose all that apply.)

MAX, MIN, SUM, or COUNT in the SELECT clause

A projection of some of the columns from a table

A selection of rows with a WHERE clause

Join of two or more tables

Question 3

Which of the following will display the next value of the PARTS_ID_SEQ (just one value)?

SELECT NEXTVAL(PARTS_ID_SEQ) FROM DUAL;

SELECT PARTS_ID_SEQ.NEXTVAL FROM INVENTORY;

SELECT PARTS_ID_SEQ.NEXTVAL FROM DUAL;

DESC PARTS_ID_SEQ.NEXTVAL

Question 4

CREATE VIEW EMP12 AS

SELECT empname, empno, salary

FROM employees

WHERE salary = 12000

WITH CHECK OPTION;

What is the reason that the follow statement might fail?

UPDATE emp12

SET salary = 13000

WHERE empno = 5678;

You can't update through a complex view.

WITH CHECK OPTION ensures that all updates will fail.

This particular update will fail because it would cause a row to disappear from the view.

The statement will succeed.

Question 5

The use of views reduces the amount of work that the database must do in processing queries.

True

False

Question 6

Which of the following are legitimate reasons for using views in a database application? (Choose all that are legitimate.)

To give the DBA something to do.

To provide an additional level of security.

To simplify complex queries.

To prevent access to sensitive data by some users.

Question 7

The CURRVAL of a sequence is the last value issued to the current session, not necessarily the last value issued in the entire database.

True

False

Question 8

What will the result of running the following set of SQL statements:

CREATE TABLE SHIPS ( SHIP_ID NUMBER PRIMARY KEY,

LENGTH NUMBER);

CREATE SEQUENCE S_ID_SEQ START WITH 1 INCREMENT BY 4;

INSERT INTO SHIPS VALUES (S_ID_SEQ.NEXTVAL, 'NOT A NUMBER');

INSERT INTO SHIPS VALUES (S_ID_SEQ.NEXTVAL, 750);

One row added to the SHIPS table, with a SHIP_ID value of 1.

One row added to the SHIPS table, with a SHIP_ID value of 5.

Two rows added to the SHIPS table. The first SHIP_ID is 1; the second is 5.

Two rows added to the SHIPS table. The first SHIP_ID is NULL; the second is 5.

Question 9

What will result from the execution of the following SQL statements:

DROP SEQUENCE PROJ_ID_SEQ;

CREATE SEQUENCE PROJ_ID_SEQ START WITH 1 INCREMENT BY 2;

SELECT PROJ_ID_SEQ.CURRVAL FROM DUAL;

The SELECT statement will display a value of 1.

The SELECT statement will display a value of 3.

The SELECT statement will fail because the sequence can only be referenced in an INSERT statement.

The SELECT statement will fail because you must reference NEXTVAL for a sequence before referencing CURRVAL.

Question 10

How can you reinitialize a sequence to a number, say have it start over from 1, after it has already generated some numbers in the sequence?

There is no way to do this.

You can use the ALTER SEQUENCE command.

You can use the UPDATE SEQUENCE command.

You have to drop the sequence then re-create it.

Question 11

You create a sequence as follows:

CREATE SEQUENCE Seq2 MAXVALUE 999 CYCLE;

If the current value is 999, what will happen when the following statement is executed?

SELECT Seq2.NEXTVAL FROM DUAL;

An error; you can't reference a sequence from DUAL;

An error; you've reached the maximum number 999, and the sequence cannot go any further.

999 will be displayed.

1 will be displayed.

Question 12

What are the distinguising characteristics of a public synonym, as compared to a private synonym? (Choose two correct answers.)

All users can reference a public synonym.

Public synonyms can be referenced without needing any permissions.

Public synonyms do not have to be qualified with a schema name.

Public synonyms can have the same name as tables or views.

Question 13

DML statements, like SELECT statements, can reference synonyms.

True

False

Question 14

Consider the following statements:

CREATE SYNONYM abc FOR departments;

CREATE PUBLIC SYNONYM abc for jobs;

SELECT * FROM abc;

Whic of the following will be correct?

The SELECT statement will show the contents of table abc, if such a table exists in the current schema.

The SELECT statement will show the contents of table departments;

The SELECT statement will show the contents of table jobs.

An error will occur due to the same synonym name being defined for two different tables.

Question 15

What will happen when the following statements are executed? (line numbers added):

01 CREATE VIEW dept_vu AS SELECT * FROM departments;

02 CREATE SYNONYM dept_s FOR dept_vu;

03 DROP TABLE departments;

04 SELECT * FROM dept_s;

There will not be an error because the synonym addresses the view, which still exists, but there will be no rows returned.

Error on Line 03: The Departments table cannot be dropped because it has a dependent view.

Error on Line 04: the synonym is no longer valid.

Error on line 04: the view is no longer valid.

Question 16

Rowid is an Oracle column that is gives a unique number for every row in every table in the entire database.

True

False

Question 17

You have created a view MED_BENEFITS on a table BENEFITS. After you have created the view, the BENEFITS table was altered. Which of the following SQL statements will ensure that MED_BENEFITS view is still valid?

COMPILE VIEW MED_BENEFITS;

RECOMPILE VIEW MED_BENEFITS;

ALTER VIEW MED_BENEFITS COMPILE;

ALTER VIEW MED_BENEFITS RECOMPILE;

College Brighten

Take action to success

Technology

New Technology

Science and Technology Museum

system security

test9-12

Popular Posts

College Brighten

Take action to success

Technology

New Technology

Science and Technology Museum

system security

test9-12

Social Profiles

Popular Posts