Creating Oracle Tables - Quiz Explanation

The correct answers are indicated below, along with the text that explains the correct answers.
1. Examine the following SQL command: 
1   CREATE TABLE JOB_OPP 
2   (JOB_ID NUMBER(10) NOT NULL,
3   COLLEGE_ED_FLAG CHAR(1), 
4   APP_METHOD VARCHAR2(25) DEFAULT 'MAIL')

Please select the best answer.
  A. 1
  B. 2
  C. 3
  D. 4
  The correct answer is B. The column constraint is NOT NULL, meaning that this column cannot be null in any row. Choice A is incorrect because this line defines the table name. Choice C is incorrect because it specifies a column without any constraints. Choice D is incorrect because it specifies a column with a default value, but this is not considered a constraint.

2. Which of the following column specifications best fits the two data values 155.13 and 35576.08?
Please select the best answer.
  A. CHAR(7,2)
  B. NUMBER(7,2)
  C. NUMBER(5,2)
  D. VARCHAR2(8)
  The correct answer is B. The NUMBER datatype is most appropriate for storing numbers. Because the numbers contain two characters to the right of the decimal and a maximum of seven places in total, the (7,2) specification is the best choice. Choice A is incorrect. This is incorrect syntax. CHAR can only specify one number, its length. Also, because these are numbers, the best choice is a NUMBER datatype. Choice C is also incorrect. While it is the correct datatype (NUMBER), the maximum number that the specifications of (5,2) handles is 999.99. One of the numbers is larger than this, so this choice is not a valid one. Choice D is incorrect. While the datatype and length can actually hold the two values, they would be stored as characters, not numbers. That makes this choice invalid.

3. Which parameter is not part of the storage parameters in the CREATE TABLE command?
Please select the best answer.
  A. MAXEXTENTS
  B. DEFAULT
  C. PCTFREE
  D. NEXT
  The correct answer is B. It is the only answer not part of the storage parameters in the CREATE TABLE command. You would use DEFAULT to specify a default value for a column. Choice A is incorrect because MAXEXTENTS defines the maximum number of extents allowed for a table. Choice C is incorrect because PCTFREE defines the percentage of free space left in each block for updates. Choice D is incorrect because NEXT defines the size of the second and subsequent extents.

4. If a tablespace runs out of space, which of the following statements is true?
Please select the best answer.
  A. You can add another datafile to increase the tablespace's space.
  B. You cannot increase the size of the tablespace or its datafiles.
  C. You cannot update any table inside the tablespace.
  D. A non-partitioned table that needs more space can use a different tablespace.
  The correct answer is A. Adding another datafile solves the problem. In addition, you can sometimes increase the current size of the existing files. Choice B is incorrect. It is possible to increase the size of both the tablespace and its datafiles. Choice C is incorrect. You might not be able to insert rows, but in most cases, there is sufficient free space reserved inside the blocks that store existing rows to allow you to update these rows. Choice D is incorrect. Non-partitioned tables must always store their data within one tablespace.

5. Examine the following SQL statement:
Please select the best answer.
  A. The maximum space that this table can use is 200K.
  B. If a block of data contains more than 20 percent data, no more rows will be updated.
  C. The maximum space that this table can use is 190K.
  D. If the USERS tablespace contains less than 190K of free space, this table cannot be created.
  The correct answer is C. The first extent is 100K. The next nine extents are 10K each. That is a total of 10 extents (the maximum), and a total of 190K. Choice A is incorrect. The table can have 10 extents, including the first extent, so that means it can use 190K. Choice B is incorrect. The 20 percent specified in PCTFREE says that 20 percent of each data block is reserved for updates. There is no parameter (aside from exceeding the maximum size including extents) that will stop an update from being completed. Choice D is incorrect. The first extent is 100K in size. So, the table can be created unless the tablespace contains less than 100K of space.