ITEC

ACCT_ID CRUISE_NAME START_DATE END_DATE1 Hawaii 11-JUL-12 24-JUL-122 Hawaii 10-OCT-12 23-OCT-123 Mexico 04-OCT-12 17-OCT-124 Mexico 06-DEC-12 19-DEC-12 What will be the value of the ACCT_ID for the first row displayed, given the following ORDER BY clause: ORDER BY cruise_name DESC, start_date;

3

Consider the following rows in a table called CUSTOMERS: CUST_ID FIRST_NAME MIDDLE LAST_NAME1 Bianca M. Canales 2 Chua A. Nguyen3 Bianca M. Jackson 4 Maya R. Canales 5 Bianca S. Canales How many rows of data will be displayed as the result of executing the following statement: (one answer) SELECT DISTINCT LAST_NAME, FIRST_NAME FROM CUSTOMERS;

4

Consider the following table named "ports": PORT_ID PORT_NAME CAPACITY----------- -------------- ------------1 Galveston 42 San Diego 43 San Francisco 34 Los Angeles 45 San Juan 36 Grand Cayman 3 Now consider the following SELECT statement: SELECT *FROM portsWHERE port_name LIKE 'San%'OR port_name LIKE 'Grand%'OR capacity = 3; How many rows from the data in the table will be returned?

4

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

A TRUNCATE command A DELETE command with no WHERE clause

If we have not specified ASC or DESC after a SQL ORDER BY clause, the following is used by default?

ASC

A substitution variable can be used to replace: (select all that apply)

All

Which of the following is(are) true concerning TRUNCATE and DELETE? (Select all that apply)

All of them

The _______ operator is used to test whether a value falls within a range of two boundary values.

BETWEEN

Operators such as AND, OR, and NOT are called _____ operators.

Boolean

Which of the following is not a considered to be DML:

COMMIT

What command is used to remove rows from a table?

DELETE

Which of the following will complete this SELECT statement to return EXACTLY 50% of the resulting rows? SELECT * FROM ORDERS

FETCH NEXT 50 PERCENT ROWS ONLY

An SQL TRUNCATE statement can be undone.

False

The ALTER command is used to make changes to the data in a table.

False

What is the result of: UPDATE cruises SET cruise_name = 'Bahamas', SET start_date = SYSDATE WHERE cruise_id = 1;

For the all records in the CRUISES table whose cruise_id is 1, cruise_name will be set to 'Bahamas' and start_date will be set to the current date

Which operator is used to compare a value to a specified list of values?

IN

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;

Consider the following WHERE clause: WHERE ship_name LIKE 'Viking%' What is function of the percent sign after the word Viking? Select all that apply.

It is the Oracle wildcard symbol It represents zero of more characters It means to find all words that start with the word Viking followed by any characters

What does the following command do? Choose all that apply. ACCEPT vDept_ID PROMPT 'Enter a department ID code: '

It pauses until the user enters a value and presses RETURN It displays the text Enter a department ID code:

Examine the code given below: SELECT employee_id FROM employees WHERE commission_pct=.5 OR salary > 23000 Which of the following statement is correct with regard to this code?

It returns employees who have 50% commission rate or salary greater than $23,000

What are a primary key's identifying characteristics? (Select all that apply.)

It uniquely identifies each row it cannot be NULL

Consider the following SELECT statement: SELECT ship_idFROM shipsWHERE 10 = 5 + 5; Which of the following is true of this statement?

It will execute and return the ship_id for each row in the table

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 execute and the table will contain one row of data.

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 cannot see SALLY's updates because she has not entered the COMMIT command.

SALLY updates some rows but does not commit. MELVIN queries the rows that SALLY updated. Which of the following statements is true? (Choose the best answer.)

MELVIN will see the old versions of the rows.

Consider the following table called PARTS: PNO PART_TITLE STATUS ------- ----------------- ------------ 1 Processor V1.0 VALID 2 Encasement X770 PENDING 3 Board CPU XER A7 PENDING Which of the following SQL statements will remove the word VALID from row 1, resulting in that row with a status of NULL and two rows with a status of PENDING?

None of these answers is correct

Which of the following job titles will make the following WHERE clause true? (Select all that apply.) WHERE job_title = 'SALES_REP'

None of these--the WHERE clause will cause an error SALES_REP

CREATE TABLE SHIPS ( Ship_ID NUMBER, Ship_name VARCHAR2(20), Home_port_id NUMBER(4)); What will be the result of the following DML statement: INSERT INTO SHIPS(Ship_name, Ship_ID) VALUES ('Codd Vessel II', 4001);

One row will be inserted with ship_ID having a value of 4001, ship_name having value 'Codd Vessel II', and Home_port_id will be NULL.

The relational model consists of which of the following. Choose all that are true:

Primary and foreign keys Collection of tables (relations) Data integrity for accuracy and consistency Set of operators to act on the relations, called the relational algebra

Which of the following Oracle tools can be used to execute SQL statements against a database?

SQL Developer SQL*Plus command line SQL Live

Which of the following are true of the WHERE clause? Select all that apply.

The WHERE clause comes after the FROM clause WHERE identifies which rows are to be included in the result set of the SELECT statement WHERE may be used by SELECT, UPDATE, and DELETE statements The WHERE clause is optional

Examine the two SQL statements given below: SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY salary DESC; SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC; What is true about them?

The two statements produce identical results

SELECT order_num, '&order_date'FROM OrdersWHERE order_date_placed = '&order_date'; Which statement regarding the execution of this statement is true?

The user will be prompted for the order_date twice, each time the statement is executed in a session

If all of the records in a table need to be removed, a TRUNCATE is faster than a DELETE, especially if the table is large and contains many rows.

True

What SQL statement allows you to modify data in a column?

UPDATE

To delete the data values from one entire column in a table (but not remove the column from the table), you would use the ______ command.

UPDATE without a WHERE clause

The ACCOUNT table contains these columns: ACCOUNT_ID NUMBER(12)NEW_BALANCE NUMBER(7,2)PREV_BALANCE NUMBER(7,2)FINANCE_CHARGE NUMBER(7,2)You need to accomplish these requirements:1. Display accounts that have a new balance that is less than the previous balance.2. Display accounts that have a finance charge that is less than $25.003. Display accounts have no finance charge. Which of the 3 requirements will this SELECT statement accomplish?SELECT account_ID FROM accountWHERE new_balance < prev_balance OR NVL(finance_charge, 0) < 25;

all three

_____ matching is possible with Oracle LIKE conditions.

pattern

For determining which records to ____, a Boolean condition is used in the WHERE Clause of a SELECT statement.

retrieve

When using a substitution variable where the user will input a character string, such as first name or city, enclose the substitution variable in _____ to prevent errors when the statement is run.

single quotes ' '

Consider the ADDRESS table that has the following 3 columns and no rows: 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;

the ADDRESS table will have no rows.

Which of the following are true concerning inserting multiple rows at a time? Select 2 correct answers.

the source table must already exist the columns and datatypes of the source and destination tables must correspond

A collection of SQL statements that form a logical unit of work is called a(n) ________.

transaction

The ROLLBACK statement will...

undo pending changes up to the last COMMIT