Oracle 12c: SQL Chapter 3

An error message is displayed, action cannot be completed.

What happens if you try to decrease the scale or precision of a NUMBER column to a value less than the data already stored in the field?

Nothing

What happens to the existing rows of a table if the DEFAULT value of a column is changed?

A DROP deletes both the column and its contents. Cannot be recovered. A SET UNUSED marks table for deletion at another time. Cannot be recovered.

What is the difference between dropping a column and setting a column as unused?

CREATE TABLE . . . AS ( . . . )

Which command is used to create a table based on data already contained in an existing table?

USER_TAB_COLUMNS

Which object in the data dictionary enables you to verify DEFAULT column settings?

ALTER TABLE books MODIFIY (title VARCHAR2(35));

Which of the following SQL statements changes the size of the Title column in the BOOKs table from the current length of 30 characters to the length of 35 characters?

RENAME oldname TO newname;

Which of the following commands changes the table's name from OLDNAME to NEWNAME?

CREATE TABLE newtable (cola NUMBER(3), coli NUMBER(3), cole AS (cola+colb));

Which of the following commands creates a new table containing a virtual column?

Truncating a table deletes all rows and frees up storage space while keeping the table and columns intact. Deleting a table removes the table and data entirely.

Explain the difference between truncating a table and deleting a table:

No default size

A default width of a VARCHAR2 field is:

Yes

Are a table and the data contained in the table erased from the system permanently if a DROP TABLE command is issued on the table?

One

How many columns can be dropped in one ALTER TABLE command?

It is the last column at the end of the table.

If you add a new column to an existing table, where does the column appear in relation to existing columns?

VARCHAR2(n) : names CHAR(n): abbreviations NUMBER(p,s): prices DATE: dates

List four datatypes supported by Oracle 12c, and provide an example of data that could be stored by each datatype.

Cannot exceed 30 characters. Must begin with a letter. Cannot contain blank spaces. Numbers, underscores (_), and number signs (#) are allowed. Each name should be unique. Reserved Oracle words cannot be used.

What guidelines should you follow when naming tables and columns?

9NEWTABLE ---> DATE9 NEW"TABLE

Which of the following is valid table name?

CREATE TABLE newname (coll DATE DEFAULT SYSDATE, col2 VARCHAR2(1));

Which of the following commands creates a new table containing two columns?

ALTER TABLE secustomerorders DROP UNUSED COLUMNS;

Which of the following commands drops any columns marked as unused from the SECUSTOMERORDERS table?

ALTER TABLE customers DROP UNUSED COLUMNS;

Which of the following commands is valid?

TRUNCATE TABLE secustomerorders;

Which of the following commands removes all data from a table but leaves the table's structure intact?

A table can contain a maximum of 1000 columns.

Which of the following is a correct statement?

CREATE TALE newtable AS (SELECT * FROM customers);

Which of the following is a valid SQL statement?

A table can be modified only if it doesn't contain any rows of data.

Which of the following is not a correct statement?

You can change the name of a table only if it doesn't contain any data.

Which of the following is not a valid statement?

All data in a table can be recovered from the recycle bin if the table is dropped.

Which of the following is true?

If you add a column to a table it is always added as the last column of the table.

Which of the following is true?

CHAR3 VARCHAR4(3) NUM ---> NUMBER

Which of the following is valid datatype?