SQL Chapter 4 Multiple Choice

a) PRIMARY KEYS

Which of the following is NOT a valid constraint type? a. PRIMARY KEYS b. UNIQUE c. CHECK d. FOREIGN KEY

a. 0 The NOT NULL constraint can be created only with the column-level approach to table creaton.

How many NOT NULL constraints can be created at the table level using the CREATE TABLE command? a. 0 b. 1 c. 12 d. 30 e. 255

b. The constraint can be created only with the table-level approach.

In the initial creation of a table, if a UNIQUE constraint is included for a composite column that requires the combination of entries in the specified columns to be unique, which of the following statements is correct? a. The constraint can be created only with the ALTER TABLE command. b. The constraint can be created only with the table-level approach. c. The constraint can be created only with the column-level approach. d. the constraint can be created only with the ALTER TABLE . . . MODIFY command.

d. None of the above. If you need to change or midfy a constraint, your only option is to delete the constraint and then create a new one. You use the ALTER TABLE command to drop an existing constraint from a table.

Suppose you created a PRIMARY KEY constraint at the same time you created a table and later decide to name the constraint. Which of the following commands can you use to change the constraint's name? a. ALTER TABLE . . . MODIFY b. ALTER TABLE . . . ADD c. ALTER TABLE . . . DISABLE d. None of the above.

c. the child table in a parent-child relationship.

The FOREIGN KEY constraint should be added to which table? a. The table representing the "one" side of a one-to-many relationship b. The parent table in a parent-child relationship c. the child table in a parent-child relationship d. the table that doesn't have a primary key.

a. 1

What is the maximum number of PRIMARY KEY constraints allowed for a table? a. 1 b.2 c. 30 d. 255

b. 1 If you were using the table level approach, you could define up to 2 columns and have it be the composite primary key. But you can't do that at the column level.

What is the maximum number of columns you can define as a primary key when using the column-level approach to creating a table? a. 0 b. 1 c. 30 d. 255

c. ALTER TABLE books ADD CONSTRAINT books_pubid_nn NOT NULL (pubid); It should read: ALTER TABLE books MODIFY (customer# CONSTRAINT books_pubid_nn NOT NULL);

Which of the following SQL statements is invalid and returns an error message? a. ALTER TABLE books ADD CONSTRAINT books_pubid_uk UNIQUE (pubid); b. ALTER TABLE books ADD CONSTRAINT books_pubid_pk PRIMARY KEY (pubid); c. ALTER TABLE books ADD CONSTRAINT books_pubid_nn NOT NULL (pubid); d. ALTER TABLE books ADD CONSTRAINT books_pubid_fk FOREIGN KEY (pubid) REFERENCES publisher (pubid); e. all of the above ids are invalid

d. ALTER TABLE . . . ENABLE

Which of the following commands can be used to enable a disable constraint? a. ALTER TABLE . . . MODIFY b. ALTER TABLE . . . ADD c. ALTER TABLE . . . DISABLE d. ALTER TABLE . . . ENABLE

e. none of the above commands can be used. If a constraint needs to be changed in any way, you must delete (DROP) it and then create new constraint with the changes that you want.

Which of the following commands can you use to rename a constraint? a. RENAME b. ALTER CONSTRAINT c. MOVE d. NEW NAME e. None of the above commands can be used.

d. USER_CONSTRAINTS

Which of the following data dictionary objects should be used to view information about the constraints in a database? a. USER_TABLES b. USER_RULES c. USER_COLUMNS d. USER_CONSTRAINTS e. None of the above objects should be used.

Not sure, but I think its d. a. has two primary keys b. has the CHECK constraint referencing the SYSDATE, which isn't valid. c. lists the constraint afterward, but do it with the proper syntax. It should be: CONSTRAINT table1_col1_pk PRIMARY KEY (first col) d. no constraints in it, and its all correct.

Which of the following is a valid SQL statement? a. CREATE TABLE table1 (col1 NUMBER PRIMARY KEY, col2 VARCHAR2(20) PRIMARY KEY, col3 DATE DEFAULT SYSDATE, col4 VARCHAR2(2)); b.CREATE TABLE table1 (col1 NUMBER PRIMARY KEY, col2 VARCHAR2(20), col3 DATE, col4 VARCHAR2(2) NOT NULL, CONSTRAINT table1_col3_ck CHECK (col3= SYSDATE)); c.CREATE TABLE table1 (col1 NUMBER, col2 VARCHAR2(20), col3 DATE, col4 VARCHAR2(2), PRIMARY KEY (col1) ); d.CREATE TABLE table1 (col1 NUMBER, col2 VARCHAR2(20), col3 DATE DEFAULT SYSDATE, col4 VARCHAR2(2));

d. None of the above is valid syntax. It should be: ALTER TABLE tablename ADD [CONSTRAINT constraintname] PRIMARY KEY (columnname);

Which of the following is the valid syntax for adding a PRIMARY KEY constraint to an existing table? a. ALTER TABLE tablename ADD CONSTRAINT PRIMARY KEY (columnname); b. ALTER TABLE tablename ADD CONSTRAINT (columnname) PRIMARY KEY constraintname; c. ALTER TABLE tablename ADD [CONSTRAINT constraintname] PRIMARY KEY; d. None of the above is valid syntax.

a. CASCADE

Which of the following keywords allows the user to delete a record from a table, even if rows in another table reference the record through a FOREIGN KEY constraint? a. CASCADE b. CASCADE ON DELETE c. DELETE ON CASCADE d. DROP E. ON DELETE CASCADE

d. none of the above statements is correct.

Which of the following statements is correct? a. A PRIMARY KEY constraint allows NULL values in the primary key column(s) b. You can enable a dropped constraint if you need it in the future. c. Every table must have at least one PRIMARY KEY constraint, or Oracle doesn't allow the table to be created. d. None of the above statements is correct.

a. NOT NULL

Which of the following types of constraints can't be created at the table level? a. NOT NULL b. PRIMARY KEY c. CHECK d. FOREIGN KEY e. None of the above constraints can be created at the table level.

not sure but maybe a. NOT NULL

Which of the following types of restrictions can be viewed with the DESCRIBE command? a. NOT NULL b. FOREIGN KEY c. UNIQUE d. CHECK

c. CHECK

Which type of constraint should you use on a column to allow entering only values above 100? a. PRIMARY KEY b. UNIQUE c. CHECK d. NOT NULL

I think only 2. Col1 should be assigned primary key, but that means it CANT be NULL, and it must have all unique entries. So, for the Col1, only 1 constraint. Col3 needs a NOT NULL constraint.

You're creating a new table consisting of three columns: Col1, Col2, and Col3. Col1 should be the primary key and can't have any NULL values, and each entry should be unique. Col3 must not contain any NULL values either. How many total constraints do you have to create? a. 1 b. 2 c. 3 d. 4