Chapter 3 - Table Creation and Management
Which of the following is not a correct statement? A table can be modified only if it doesn't contain any rows of data. The maximum number of characters in a table name is 30. You can add more than one column at a time to a table. You can't recover data contained in a table that has been truncated.
A table can be modified only if it doesn't contain any rows of data.
Which of the following characters can be used in a table name? - ( % !
- (I think this is suppose to represent an underscore)
Which of the following is a correct statement? You can restore the data deleted with the DROP COLUMN clause, but not the data deleted with the SET UNUSED clause. You can't create empty tables—all tables must contain at least three rows of data. A table can contain a maximum of 1000 columns. The maximum length of a table name is 265 characters.
A table can contain a maximum of 1000 columns.
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? ALTER TABLE books CHANGE title VARCHAR(35); ALTER TABLE books MODIFY (title VARCHAR2(35)); ALTER TABLE books MODIFY title (VARCHAR2(35)); ALTER TABLE books MODIFY (title VARCHAR2(+5));
ALTER TABLE books MODIFY (title VARCHAR2(35));
Which of the following commands is valid? RENAME customer# TO customernumber FROM customers; ALTER TABLE customers RENAME customer# TO customernum; DELETE TABLE customers; ALTER TABLE customers DROP UNUSED COLUMNS;
ALTER TABLE customers DROP UNUSED COLUMNS;
Which of the following commands drops any columns marked as unused from the SECUSTOMERORDERS table? DROP COLUMN FROM secustomerorders WHERE column_status = UNUSED; ALTER TABLE secustomerorders DROP UNUSED COLUMNS; ALTER TABLE secustomerorders DROP (unused); DROP UNUSED COLUMNS;
ALTER TABLE secustomerorders DROP UNUSED COLUMNS;
Which of the following is true? All data in a table can be recovered if the table is dropped with the PURGE option. All data in a table can be recovered from the recycle bin if the table is dropped. All data in a table is lost if the table is dropped. All of the above statements are true.
All data in a table can be recovered from the recycle bin if the table is dropped.
Which of the following is not a valid SQL statement? CREATE TABLE anothernewtable (newtableid VARCHAR2(2)); CREATE TABLE anothernewtable (date, anotherdate) AS (SELECT orderdate, shipdate FROM orders); CREATE TABLE anothernewtable (firstdate, seconddate) AS (SELECT orderdate, shipdate FROM orders); All of the above are valid statements.
All of the above are valid statements.
Which of the following commands creates a new table containing two columns? CREATE TABLE newname (coll DATE, col2 VARCHAR2); CREATE TABLE newname AS (SELECT title, retail, cost FROM books); CREATE TABLEnewname (coll, col2); CREATE TABLE newname (coll DATE DEFAULT SYSDATE, col2 VARCHAR2 (1))
CREATE TABLE newname (coll DATE DEFAULT SYSDATE, col2 VARCHAR2 (1))
Which of the following commands creates a new table containing a virtual column? CREATE TABLE newtable AS (SELECT order#, title, quantity, retail FROM orders); CREATE TABLE newtable (price NUMBER(3), total NUMBER(8,2)); CREATE TABLE newtable (calcl NUMBER(4), calc2 NUMBER(4); CREATE TABLE newtable (cola NUMBER(3), colb NUMBER(3), cole AS (cola+colb));
CREATE TABLE newtable (cola NUMBER(3), colb NUMBER(3), cole AS (cola+colb));
Which of the following is a valid SQL statement? ALTER TABLE secustomersspent ADD DATE lastorder; ALTER TABLE secustomerorders DROP retail; CREATE TABLE newtable AS (SELECT * FROM customers); ALTER TABLE drop column *;
CREATE TABLE newtable AS (SELECT * FROM customers);
Which command is used to create a table based on data already contained in an existing table?
CREATE TABLE tablename [(columnname, ...1)] AS (subquery)
What happens to the existing rows of a table if the DEFAULT value of a column is changed?
Changing the default value of a column doesn't change the values of data already in the table.
Which of the following is a valid table name? 9NEWTABLE DATE9 NEW"TABLE None of the above are valid table names.
DATE9
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?
If a NUMBER column already contains data, you can't decrease the column's precision or scale.
Which of the following is true? If you truncate a table, you can't add new data to the table. If you change the default value of an existing column, all existing rows containing a NULL value in the same column are set to the new DEFAULT value. If you delete a column from a table, you can't add a column to the table with the same name as the previously deleted column. If you add a column to an existing table, it's always added as the last column of the table.
If you add a column to an existing table, it's always added as the last column of the table.
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?
In recent Oracle versions, a dropped table is now placed in a recycle bin and can be restored—both table structure and data!
If you add a new column to an existing table, where does the column appear in relation to existing columns?
It will be the last column
What guidelines should you follow when naming tables and columns in Oracle 12c?
Max 30 characters; must begin with a letter; can have numbers, underscores, and hashtags; must be unique; can't be SELECT
Which of the following is a valid datatype? CHAR3 VARCHAR4(3) NUM NUMBER
NUMBER
The default width of a VARCHAR2 field is: 1 30 255 None—there's no default width for a VARCHAR2 field.
None—there's no default width for a VARCHAR2 field.
Which of the following commands changes a table's name from OLDNAME to NEWNAME? RENAME oldname TO newname; RENAME table FROM oldname TO newname; ALTER TABLE oldname MODIFY TO newname; CREATE TABLE newname (SELECT * FROM oldname);
RENAME oldname TO newname;
Which of the following commands removes all data from a table but leaves the table's structure intact? ALTER TABLE secustomerorders DROP UNUSED COLUMNS; TRUNCATE TABLE secustomerorders; DELETE TABLE secustomerorders; DROP TABLE secustomerorders;
TRUNCATE TABLE secustomerorders;
What is the difference between dropping a column and setting a column as unused?
To delete an existing column from a table, you can use the DROP COLUMN clause with the ALTER TABLE command. This clause deletes both the column and its contents, so it should be used with extreme caution. You can include a SET UNUSED clause in the ALTER TABLE command to mark the column for deletion at a later time. If a column is marked for deletion, it's unavailable and isn't displayed in the table structure. Because the column is unavailable, it doesn't appear in the results of any queries, and no other operation can be performed on the column except the ALTER TABLE ... DROP UNUSED command.
Explain the difference between truncating a table and deleting a table.
Truncating a table saves the columns but deletes all of the data in the rows. Deleting a table removes it from the Oracle database.
Which object in the data dictionary enables you to verify DEFAULT column settings? DEFAULT_COLUMNS DEF_TAB_COLUMNS USER_TAB_COLUMNS None of the above
USER_TAB_COLUMNS
How many columns can be dropped in one ALTER TABLE command?
Unlike using ALTER TABLE with the ADD or MODIFY clauses, a DROP COLUMN clause can reference only one column.
List four datatypes supported by Oracle 12c, and provide an example of data that could be stored by each datatype.
Varchar2(n) ex: a person's last name; char(n) ex: a person's zip code; number(p,s) ex: a person's salary; date ex:1-JAN-4712BC
Which of the following is not a valid statement? You can change the name of a table only if it doesn't contain any data. You can change the length of a column that doesn't contain any data. You can delete a column that doesn't contain any data. You can add a column to a table.
You can change the name of a table only if it doesn't contain any data.
