Answers to Chapter 2

Reviewing It

1: What is Oracle's main character-mode interface?
A1: SQL*Plus
2: What is the syntax of the CHANGE command to change text in the SQL buffer?
A2: C/<source string>/<new string>
3: The DESCRIBE command is useful for what?
A3: Displaying Object column names and attributes
4: What will the command SELECT * FROM EMP do?
A4: This will show all the rows in the table EMP
5: In the table EMP, give the ORDER BY clause that will return the rows in the order of DEPTNO ascending and within DEPTNO, SAL descending.
A5: ORDER BY DEPTNO, SAL desc
6: What is the difference between a NULL and a 0 field?
A6: NULL contains no value at all
7: When using substitution characters (say in an INSERT statement), what is the difference between & and &&?
A7: & prompts the user for a value with each execution of the SQL statement, && only prompts the user to provide a value on the first SQL statement
8: What is the difference between the DELETE command and the TRUNCATE command?
A8: DELETE can selectively delete rows and this command can be undone with a ROLLBACK statement. The TRUNCATE drops all the rows, no selectivity, and the command cannot be reversed
9: Tablespaces are used for what?
A9: Tablespaces are storage areas in Oracle, much like a directory structure of a computer. Tablespaces have physical disk drives assigned
10: Show the syntax to create an index on ename for the table EMP.
A10: CREATE INDEX <some name> ON EMP (ENAME)
11: What is an EXTENT?
A11: One or more Oracle datablocks assigned in a contiguous manor to a particular object.

Checking It

1: SQL*Plus can:

  1. Display data from the Oracle database

  2. Format data into reports

  3. Perform database administrative functions

  4. All of the above

A1: d
2: In the SQL Buffer, the command I is short for INSERT.

True/False

A2: False (stands for INPUT)
3: The default behavior of the ORDER BY clause is ascending.

True/False

A3: True
4: Which of the following LIKE commands will return the rows with a name like HOTKA

  1. WHERE ename LIKE '_HOT%'

  2. WHERE ename LIKE 'H_T%'

  3. WHERE ename LIKE '%TKA_'

  4. WHERE ename LIKE '%TOK%'

A4: b
5: Which is the valid UPDATE statement?

  1. UPDATE emp SET SAL = SAL * 1.05

  2. UPDATE emp WHERE SAL = 20000

  3. UPDATE emp SET ENAME = 1234 WHERE DEPTNO = 10

  4. UPDATE emp WHERE DEPTNO = 10 SET ENAME = 'HOTKA'

A5: a
6: Which of the following is NOT a valid data type:

  1. BLOB

  2. CHAR

  3. VARCHAR

  4. INTEGER

A6: d
7: CREATE TABLE <table name> AS SELECT can create a duplicate object.

True/False

A7: True, the rows and columns depends on the SELECT statement
8: You change your mind on a DROP COLUMN command and return the column to the original object.

True/False

A8: False, DROP COLUMN is unrecoverable
9: Selecting which field causes a SEQUENCE generator to increment?

  1. CURRVAL

  2. NEXTVAL

  3. LASTNUMBER

  4. PREVAL

A9: b
10: Constraints are useful for:

  1. Ensuring parent/child table relationships

  2. Ensuring only certain kinds of data is in a field

  3. Ensuring uniqueness of column data

  4. All of the above

A10: d.

Applying It

Independent Exercise 1:

  • Create a Table called people with columns of dept (numeric 4 positions), last_name (character 10 positions), start_date (date field), and salary (numeric 5 positions with 2 decimal positions).

Independent Exercise 2:

  • Insert the following data into the table, try out the & substitution character:

    • Dept 10, SMITH, SYSDATE, 10000

    • Dept 10, JONES, SYSDATE, 20000

    • Dept 20, KING, SYSDATE, 15000

    • Dept 20, JONES, SYSDATE, 18000

    • Dept 20, FOUNTAIN, SYSDATE, 12000

Independent Exercise 3:

  • Select those rows from Dept 20.

  • Select name and start_date with the start_date in the format of MM/DD/YYYY.

  • Give everyone in dept 10 a 10% increase in salary.

  • Set AUTOCOMMIT off, drop all the rows, and issue a ROLLBACK. Make sure all the rows are put back.

Independent Exercise 4:

  • Create a sequence that increments by 10 and begins at 10 over the current DEPT in the PEOPLE table.

    • INSERT <sequence name>.NEXTVAL, GARN, SYSDATE, 5000.

    • Create a primary key constraint on the DEPT column.

    • INSERT <sequence name>.CURRVAL, HACKETT, SYSDATE, 4500.

    • Why did you get this error?

NOTE

The next chapter uses this PEOPLE table for its Exercise examples as well.


..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.137.223.10