TESTING
All systems needs rigorous testing before they are delivered to end users. True or False?
True
Testing is done by programmers. True or False?
True
You need not worry about Contraints on tables when testing. True or False?
False
What kind of transactions should you test against your tables and views?
INSERT, UPDATE, DELETE, MERGE
marți, 26 februarie 2013
PROGRAMMING: Section 15
CARTESIAN PRODUCT AND THE JOIN OPERATIONS
When must column names be prefixed by table names in join syntax?
When the same column name appears in more than one table of the query
Will the following statement work?
SELECT department_name, last_name
FROM employees, departments
WHERE department_id = department_id;
No, Oracle will return a column ambiguously defined error.
If table A have 10 rows and table B have 5 rows, how many rows will be returned if you perform a cartesian join on those two tables?
50
Oracle proprietary JOINS can use the WHERE clause for conditions other than the join-condition. True or False?
True
What is the result of a query that selects from two tables but includes no join condition?
A Cartesian product
If table A have 10 rows and table B have 5 rows, how many rows will be returned if you perform a equi-join on those two tables?
It depends on the data found in the two tables.
NONEQUIJOINS
The following statement is an example of a nonequi-join?
SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary
BETWEEN j.lowest_sal AND j.highest_sal;
True or False?
True
Which of the following operators is/are typically used in a nonequijoin?
>=, <=, BETWEEN ...AND
Which statement about joining tables with a non-equijoin is false?
A WHERE clause must specify a column in one table that is compared to a column in the second table
OUTER JOINS
To perform a valid outer join between DEPARMENTS and EMPLOYEES to list departments without employees select the correct WHERE clause for the following select statement:
SELECT d.department_name, e.last_name
FROM employees e, departments d
WHERE
e.department_id(+) = d.department_id
The following is a valid outer join statement:
SELECT c.country_name, d.department_name
FROM countries c, departments d
WHERE c.country_id (+) = d.country_id (+)
True or False?
False
Which symbol is used to perform an outer join?
(+)
The ID column in the CLIENT table that corresponds to the CLIENT_ID column of the ORDER table contains null values for rows that need to be displayed. Which type of join should you use to display the data?
Outer join
When must column names be prefixed by table names in join syntax?
When the same column name appears in more than one table of the query
Will the following statement work?
SELECT department_name, last_name
FROM employees, departments
WHERE department_id = department_id;
No, Oracle will return a column ambiguously defined error.
If table A have 10 rows and table B have 5 rows, how many rows will be returned if you perform a cartesian join on those two tables?
50
Oracle proprietary JOINS can use the WHERE clause for conditions other than the join-condition. True or False?
True
What is the result of a query that selects from two tables but includes no join condition?
A Cartesian product
If table A have 10 rows and table B have 5 rows, how many rows will be returned if you perform a equi-join on those two tables?
It depends on the data found in the two tables.
NONEQUIJOINS
The following statement is an example of a nonequi-join?
SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary
BETWEEN j.lowest_sal AND j.highest_sal;
True or False?
True
Which of the following operators is/are typically used in a nonequijoin?
>=, <=, BETWEEN ...AND
Which statement about joining tables with a non-equijoin is false?
A WHERE clause must specify a column in one table that is compared to a column in the second table
OUTER JOINS
To perform a valid outer join between DEPARMENTS and EMPLOYEES to list departments without employees select the correct WHERE clause for the following select statement:
SELECT d.department_name, e.last_name
FROM employees e, departments d
WHERE
e.department_id(+) = d.department_id
The following is a valid outer join statement:
SELECT c.country_name, d.department_name
FROM countries c, departments d
WHERE c.country_id (+) = d.country_id (+)
True or False?
False
Which symbol is used to perform an outer join?
(+)
The ID column in the CLIENT table that corresponds to the CLIENT_ID column of the ORDER table contains null values for rows that need to be displayed. Which type of join should you use to display the data?
Outer join
PROGRAMMING: Section 14
DATABASE TRANSACTIONS
A transaction makes several successive changes to a table. If required, you want to be able to rollback the later changes while keeping the earlier changes. What must you include in your code to do this?
A savepoint
User BOB's CUSTOMERS table contains 20 rows. BOB inserts two more rows into the table but does not COMMIT his changes. User JANE now executes:
SELECT COUNT(*) FROM bob.customers;
What result will JANE see?
20
Which of the following best describes the term "read consistency"?
It prevents other users from seeing changes to a table until those changes have been committed
Table MYTAB contains only one column of datatype CHAR(1). A user executes the following statements in the order shown.
INSERT INTO mytab VALUES ('A');
INSERT INTO mytab VALUES ('B');
COMMIT;
INSERT INTO mytab VALUES ('C');
ROLLBACK;
Which rows does the table now contain?
A and B
If a database crashes, all uncommitted changes are automatically rolled back. True or False?
True
Steven King's row in the EMPLOYEES table has EMPLOYEE_ID = 100 and SALARY = 24000. A user issues the following statements in the order shown:
UPDATE employees
SET salary = salary * 2
WHERE employee_id = 100;
COMMIT;
UPDATE employees
SET salary = 30000
WHERE employee_id = 100;
The user's database session now ends abnormally. What is now King's salary in the table?
48000
Examine the following statements:
UPDATE employees SET salary = 15000;
SAVEPOINT upd1_done;
UPDATE employees SET salary = 22000;
SAVEPOINT upd2_done;
DELETE FROM employees;
You want to retain all the employees with a salary of 15000; What statement would you execute next?
ROLLBACK TO SAVEPOINT upd1_done;
Which SQL statement is used to remove all the changes made by an uncommitted transaction?
ROLLBACK;
A transaction makes several successive changes to a table. If required, you want to be able to rollback the later changes while keeping the earlier changes. What must you include in your code to do this?
A savepoint
User BOB's CUSTOMERS table contains 20 rows. BOB inserts two more rows into the table but does not COMMIT his changes. User JANE now executes:
SELECT COUNT(*) FROM bob.customers;
What result will JANE see?
20
Which of the following best describes the term "read consistency"?
It prevents other users from seeing changes to a table until those changes have been committed
Table MYTAB contains only one column of datatype CHAR(1). A user executes the following statements in the order shown.
INSERT INTO mytab VALUES ('A');
INSERT INTO mytab VALUES ('B');
COMMIT;
INSERT INTO mytab VALUES ('C');
ROLLBACK;
Which rows does the table now contain?
A and B
If a database crashes, all uncommitted changes are automatically rolled back. True or False?
True
Steven King's row in the EMPLOYEES table has EMPLOYEE_ID = 100 and SALARY = 24000. A user issues the following statements in the order shown:
UPDATE employees
SET salary = salary * 2
WHERE employee_id = 100;
COMMIT;
UPDATE employees
SET salary = 30000
WHERE employee_id = 100;
The user's database session now ends abnormally. What is now King's salary in the table?
48000
Examine the following statements:
UPDATE employees SET salary = 15000;
SAVEPOINT upd1_done;
UPDATE employees SET salary = 22000;
SAVEPOINT upd2_done;
DELETE FROM employees;
You want to retain all the employees with a salary of 15000; What statement would you execute next?
ROLLBACK TO SAVEPOINT upd1_done;
Which SQL statement is used to remove all the changes made by an uncommitted transaction?
ROLLBACK;
PROGRAMMING: Section 13
CONTROLLING USER ACCESS
The following table shows some of the output from one of the data dictionary views. Which view is being queried?
USERNAME PRIVILEGE ADMIN_OPTION
USCA_ORACLE_SQL01_S08 CREATE VIEW NO
USCA_ORACLE_SQL01_S08 CREATE TABLE NO
USCA_ORACLE_SQL01_S08 CREATE SYNONYM NO
USCA_ORACLE_SQL01_S08 CREATE TRIGGER NO
USCA_ORACLE_SQL01_S08 CREATE SEQUENCE NO
USCA_ORACLE_SQL01_S08 CREATE DATABASE NO
user_sys_privs (lists system privileges granted to the user)
Which Object Privilege apart from Alter can be granted to a Sequence?
SELECT
Object privileges are:
Required to manipulate the content of objects in the database.
By Controlling User Access with Oracle Database Security you can give access to specific Objects in the Database. True or False?
True
Which of the following Object Privileges can be granted on an individual column on a table?
Update
References
A Schema is a collection of Objects such as Tables, Views and Sequences. True or False?
True
Which of these is not a System Privilege granted by the DBA?
Create Index
Which of the following is not a database object?
Subquery
What system privilege must be held in order to login to an Oracle database?
CREATE SESSION
A schema is:
A collection of objects, such as tables, views, and sequences.
System privileges are:
Required to gain access to the database.
CREATING AND REVOKING OBJECT PRIVILEGES
Which of the following statements about granting object privileges is false?
Object privileges can only be granted through roles.
If you are granted privileges to your friend's object, by default you may also grant access to this same object to other users. True or False?
False
What Oracle feature simplifies the process of granting and revoking privileges?
Role
User1 owns a table and grants select on it WITH GRANT OPTION to User2. User2 then grants select on the same table to User3. If User1 revokes select privileges from User2, will User3 be able to access the table?
No
Roles are:
Named groups of related privileges given to a user or another role.
When a user is logged into one database, they are restricted to working with objects found in that database. True or False?
False
To take away a privilege from a user you use which command?
REVOKE
Scott King owns a table called employees. He issues the following statement:
GRANT select ON employees TO PUBLIC;
Allison Plumb has been granted CREATE SESSION by the DBA. She logs into the database and issues the following statement:
GRANT ﾠselect ON ﾠscott_king.employees TO jennifer_cho;
True or False: Allison's statement will fail.
True
A role can be granted to another role. True or False?
True
Which of the following statements is true?
Database Links allow users to work on remote database objects without having to log into the other database.
REGULAR EXPRESSIONS
Select the correct REGULAR EXPRESSION functions:
REGEXP_LIKE, REGEXP_REPLACE
REGEXP_INSTR, REGEXP_SUBSTR
REGULAR EXPRESSIONS can be used as a part of contraint definitions?
True
REGULAR EXPRESSIONS does exactly the same as LIKE. No more and no less?
False
REGULAR EXPRESSIONS can be used on CHAR, CLOB and VARCHAR2 datatypes?
True
The following table shows some of the output from one of the data dictionary views. Which view is being queried?
USERNAME PRIVILEGE ADMIN_OPTION
USCA_ORACLE_SQL01_S08 CREATE VIEW NO
USCA_ORACLE_SQL01_S08 CREATE TABLE NO
USCA_ORACLE_SQL01_S08 CREATE SYNONYM NO
USCA_ORACLE_SQL01_S08 CREATE TRIGGER NO
USCA_ORACLE_SQL01_S08 CREATE SEQUENCE NO
USCA_ORACLE_SQL01_S08 CREATE DATABASE NO
user_sys_privs (lists system privileges granted to the user)
Which Object Privilege apart from Alter can be granted to a Sequence?
SELECT
Object privileges are:
Required to manipulate the content of objects in the database.
By Controlling User Access with Oracle Database Security you can give access to specific Objects in the Database. True or False?
True
Which of the following Object Privileges can be granted on an individual column on a table?
Update
References
A Schema is a collection of Objects such as Tables, Views and Sequences. True or False?
True
Which of these is not a System Privilege granted by the DBA?
Create Index
Which of the following is not a database object?
Subquery
What system privilege must be held in order to login to an Oracle database?
CREATE SESSION
A schema is:
A collection of objects, such as tables, views, and sequences.
System privileges are:
Required to gain access to the database.
CREATING AND REVOKING OBJECT PRIVILEGES
Which of the following statements about granting object privileges is false?
Object privileges can only be granted through roles.
If you are granted privileges to your friend's object, by default you may also grant access to this same object to other users. True or False?
False
What Oracle feature simplifies the process of granting and revoking privileges?
Role
User1 owns a table and grants select on it WITH GRANT OPTION to User2. User2 then grants select on the same table to User3. If User1 revokes select privileges from User2, will User3 be able to access the table?
No
Roles are:
Named groups of related privileges given to a user or another role.
When a user is logged into one database, they are restricted to working with objects found in that database. True or False?
False
To take away a privilege from a user you use which command?
REVOKE
Scott King owns a table called employees. He issues the following statement:
GRANT select ON employees TO PUBLIC;
Allison Plumb has been granted CREATE SESSION by the DBA. She logs into the database and issues the following statement:
GRANT ﾠselect ON ﾠscott_king.employees TO jennifer_cho;
True or False: Allison's statement will fail.
True
A role can be granted to another role. True or False?
True
Which of the following statements is true?
Database Links allow users to work on remote database objects without having to log into the other database.
REGULAR EXPRESSIONS
Select the correct REGULAR EXPRESSION functions:
REGEXP_LIKE, REGEXP_REPLACE
REGEXP_INSTR, REGEXP_SUBSTR
REGULAR EXPRESSIONS can be used as a part of contraint definitions?
True
REGULAR EXPRESSIONS does exactly the same as LIKE. No more and no less?
False
REGULAR EXPRESSIONS can be used on CHAR, CLOB and VARCHAR2 datatypes?
True
PROGRAMMING: Section 12
WORKING WITH SEQUENCES
A sequence is a window through which data can be queried or changed. True or False?
False
CURRVAL is a pseudocolumn used to refer to a sequence number that the current user has just generated by referencing NEXTVAL. True or False?
True
A sequence is a database object. True or False?
True
Which is the correct syntax for specifying a maximum value in a sequence?
Maxvalue
In order to be able to generate primary key values that are not likely to contain gaps, which phrase should be included in the sequence creation statement?
NOCACHE
Which keyword is used to remove a sequence?
Drop
Why do gaps in sequences occur?
All of the above
When you alter a sequence, a new increased MAXVALUE can be entered without changing the existing number order. True or False?
True
Nextval and Currval are know as column aliases. True or False?
False
Which keyword is used to modify a sequence?
Alter
CURRVAL is a pseudocolumn used to extract successive sequence numbers from a specified sequence. True or False?
False
Examine the code for creating this sequence:
CREATE SEQUENCE track_id_seq
INCREMENT BY 10
START WITH 1000 MAXVALUE 10000
What are the first three values that would be generated by the sequence?
1000, 1010, 1020
INDEXES AND SYNONYMS
It is possible to have an indexed column in a table where a value in the table column does not exist in the index. True or False?
False
What kind of INDEX is created by Oracle when you create a primary key?
UNIQUE INDEX
All tables must have indexes on them otherwise they cannot be queried. True or False?
False
You must use a synonym to access another users table. True or False?
False
In SQL what is a synonym?
A different name for a table, view or other database object
Which of the following SQL statements shows a correct syntax example of creating a synonym accessible to all users of a database?
CREATE PUBLIC SYNONYM emp FOR EMPLOYEES
Indexes can be used to speed up queries. True or False?
True
Which of the following statements best describes indexes and their use?
They contain the column value and pointers to the data in the table, but the data is sorted.
A sequence is a window through which data can be queried or changed. True or False?
False
CURRVAL is a pseudocolumn used to refer to a sequence number that the current user has just generated by referencing NEXTVAL. True or False?
True
A sequence is a database object. True or False?
True
Which is the correct syntax for specifying a maximum value in a sequence?
Maxvalue
In order to be able to generate primary key values that are not likely to contain gaps, which phrase should be included in the sequence creation statement?
NOCACHE
Which keyword is used to remove a sequence?
Drop
Why do gaps in sequences occur?
All of the above
When you alter a sequence, a new increased MAXVALUE can be entered without changing the existing number order. True or False?
True
Nextval and Currval are know as column aliases. True or False?
False
Which keyword is used to modify a sequence?
Alter
CURRVAL is a pseudocolumn used to extract successive sequence numbers from a specified sequence. True or False?
False
Examine the code for creating this sequence:
CREATE SEQUENCE track_id_seq
INCREMENT BY 10
START WITH 1000 MAXVALUE 10000
What are the first three values that would be generated by the sequence?
1000, 1010, 1020
INDEXES AND SYNONYMS
It is possible to have an indexed column in a table where a value in the table column does not exist in the index. True or False?
False
What kind of INDEX is created by Oracle when you create a primary key?
UNIQUE INDEX
All tables must have indexes on them otherwise they cannot be queried. True or False?
False
You must use a synonym to access another users table. True or False?
False
In SQL what is a synonym?
A different name for a table, view or other database object
Which of the following SQL statements shows a correct syntax example of creating a synonym accessible to all users of a database?
CREATE PUBLIC SYNONYM emp FOR EMPLOYEES
Indexes can be used to speed up queries. True or False?
True
Which of the following statements best describes indexes and their use?
They contain the column value and pointers to the data in the table, but the data is sorted.
PROGRAMMING: Section 11
CREATING VIEWS
What is one advantage of using views?
To provide restricted data access
Views contain no data of their own. True or False?
True
Given the following CREATE VIEW statement, what data will be returned?
CREATE OR REPLACE VIEW emp_dept
AS SELECT SUBSTR(e.first_name,1,1) ||' '||e.last_name emp_name, e.salary, e.hire_date, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id AND d.department_id >=50;
First character from employee first_name concatenated to the last_name, the salary, the hire_date and department_name of all employees working in department number 50 or higher
A view can contain a select statement with a subquery. True or False?
True
A view can contain group functions. True or False?
True
Any select statement can be stored in the database as a view. True or False?
True
DML OPERATIONS AND VIEWS
Given the following view what operations would be allowed on the emp_dept view:
CREATE OR REPLACE VIEW emp_dept
AS SELECT SUBSTR(e.first_name,1,1) ||' '||e.last_name emp_name, e.salary, e.hire_date, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id AND d.department_id >=50;
SELECT, UPDATE of some columns, DELETE
If a database administrator wants to ensure that changes performed through a view do not violate existing constraints, which clause should he/she include when creating the view?
WITH CHECK OPTION
Which of the following DML operations is not allowed when using a Simple View created with read only?
All of the above
There is only one kind view? True or False?
False
Using the pseudocolumn ROWNUM in a view has no implications on the ability to do DML's through the view. True or False?
False
Examine the view below and choose the operation that CANNOT be performed on it.
CREATE VIEW dj_view (last_name, number_events) AS
SELECT c.last_name, COUNT(e.name)
FROM d_clients c, d_events e
WHERE c.client_number = e.client_number
GROUP BY c.last_name
INSERT INTO dj_view VALUES ('Turner', 8);
MANAGING VIEWS
When you drop a table referenced by a view, the view is automatically dropped as well. True or False?
False
Which of the following is true about ROWNUM?
It is the number assigned to each row returned from a query as they are read from the table.
How do you remove a view?
DROP VIEW view_name
A Top-N Analysis is capable of ranking a top or bottom set of results. True or False?
True
When you drop a view, the data it contains is also deleted. True or False?
False
Which of these Keywords is typically used with a Top-N Analysis?
Rownum
Which of these is not a valid type of View?
ONLINE
What is one advantage of using views?
To provide restricted data access
Views contain no data of their own. True or False?
True
Given the following CREATE VIEW statement, what data will be returned?
CREATE OR REPLACE VIEW emp_dept
AS SELECT SUBSTR(e.first_name,1,1) ||' '||e.last_name emp_name, e.salary, e.hire_date, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id AND d.department_id >=50;
First character from employee first_name concatenated to the last_name, the salary, the hire_date and department_name of all employees working in department number 50 or higher
A view can contain a select statement with a subquery. True or False?
True
A view can contain group functions. True or False?
True
Any select statement can be stored in the database as a view. True or False?
True
DML OPERATIONS AND VIEWS
Given the following view what operations would be allowed on the emp_dept view:
CREATE OR REPLACE VIEW emp_dept
AS SELECT SUBSTR(e.first_name,1,1) ||' '||e.last_name emp_name, e.salary, e.hire_date, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id AND d.department_id >=50;
SELECT, UPDATE of some columns, DELETE
If a database administrator wants to ensure that changes performed through a view do not violate existing constraints, which clause should he/she include when creating the view?
WITH CHECK OPTION
Which of the following DML operations is not allowed when using a Simple View created with read only?
All of the above
There is only one kind view? True or False?
False
Using the pseudocolumn ROWNUM in a view has no implications on the ability to do DML's through the view. True or False?
False
Examine the view below and choose the operation that CANNOT be performed on it.
CREATE VIEW dj_view (last_name, number_events) AS
SELECT c.last_name, COUNT(e.name)
FROM d_clients c, d_events e
WHERE c.client_number = e.client_number
GROUP BY c.last_name
INSERT INTO dj_view VALUES ('Turner', 8);
MANAGING VIEWS
When you drop a table referenced by a view, the view is automatically dropped as well. True or False?
False
Which of the following is true about ROWNUM?
It is the number assigned to each row returned from a query as they are read from the table.
How do you remove a view?
DROP VIEW view_name
A Top-N Analysis is capable of ranking a top or bottom set of results. True or False?
True
When you drop a view, the data it contains is also deleted. True or False?
False
Which of these Keywords is typically used with a Top-N Analysis?
Rownum
Which of these is not a valid type of View?
ONLINE
PROGRAMMING: Section 10
DEFINING NOT NULL AND UNIQUE CONSTRAINTS
A table can have more than one UNIQUE key constraint. True or False?
True
Which of the following is not a valid Oracle constraint type?
EXTERNAL KEY
A column defined as NOT NULL can have a DEFAULT value of NULL. True or False?
False
A unique key constraint can only be defined on a not null column. True or False?
False
If the employees table has a UNIQUE constraint on the DEPARTMENT_ID column, we can only have one employee per department. True or False?
True
A table must have at least one not null constraint and one unique constraint. True or False?
False
PRIMARY KEY, FOREIGN KEY, AND CHECK CONSTRAINTS
Foreign Key Constraints are also known as:
Referential Integrity Constraints
A Primary Key that is made up of more than one column is called a:
Composite Primary Key
The employees table contains a foreign key column department_id that references the id column in the departments table. Which of the following constraint modifiers will NOT allow the deletion of id values in the department table?
Neither A nor B
Which line of the following code will cause an error:
1. CREATE TABLEMclients
2. (client_number NUMBER(4) CONSTRAINT client_client_num_pk PRIMARY KEY client_number),
3. first_name VARCHAR2(14),
4. last_name VARCHAR2(13),
5. hire_date DATE CONSTRAINT emp_min_hire_date CHECK (hire_date < SYSDATE),
6. department_id VARCHAR(3),
7. CONSTRAINT clients_dept_id_fk FOREIGN KEY(department_id) REFERENCES departments(department_id));
Line 2
The table that contains the Primary Key in a Foreign Key Constraint is known as:
Parent Table
If a Primary Key is made up of more than one column, one of the columns can be null. True or False?
False
An example of adding a check constraint to limit the salary that an employee can earn is:
ALTER TABLE employees ADD CONSTRAINT emp_salary_ck CHECK (salary < 100000)
A composite primary key may only be defined at the table level. True or False?
True
To automatically delete rows in a child table when a parent record is deleted use:
ON DELETE CASCADE
The main reason that constraints are added to a table is:
Constraints ensure data integrity
Which of the following pieces of code will NOT successfully create a foreign key in the CDS table that references the SONGS table
None of the above
The number of check constraints that can be defined on a column is:
There is no limit
MANAGING CONSTRAINTS
What mechamisn does Oracle use in the background to enforce uniqueness in Primary and Unique key constraints?
Unique indexes are created in the background by Oracle when Primary and Unique constraints are created or enabled
Once constraints have been created on a table you will have to live with them as they are unless you drop and re-create the table. True or False?
False
You can drop a column in a table with a simple ALTER TABLE DROP COLUMN statement, even if the column is referenced in a constraint. True or False?
False
All of a user's constraints can be viewed in the Oracle Data Dictionary view called:
USER_CONSTRAINTS
The command to 'switch off' a constraint is:
ALTER TABLE DISABLE CONSTRAINT
A table can have more than one UNIQUE key constraint. True or False?
True
Which of the following is not a valid Oracle constraint type?
EXTERNAL KEY
A column defined as NOT NULL can have a DEFAULT value of NULL. True or False?
False
A unique key constraint can only be defined on a not null column. True or False?
False
If the employees table has a UNIQUE constraint on the DEPARTMENT_ID column, we can only have one employee per department. True or False?
True
A table must have at least one not null constraint and one unique constraint. True or False?
False
PRIMARY KEY, FOREIGN KEY, AND CHECK CONSTRAINTS
Foreign Key Constraints are also known as:
Referential Integrity Constraints
A Primary Key that is made up of more than one column is called a:
Composite Primary Key
The employees table contains a foreign key column department_id that references the id column in the departments table. Which of the following constraint modifiers will NOT allow the deletion of id values in the department table?
Neither A nor B
Which line of the following code will cause an error:
1. CREATE TABLEMclients
2. (client_number NUMBER(4) CONSTRAINT client_client_num_pk PRIMARY KEY client_number),
3. first_name VARCHAR2(14),
4. last_name VARCHAR2(13),
5. hire_date DATE CONSTRAINT emp_min_hire_date CHECK (hire_date < SYSDATE),
6. department_id VARCHAR(3),
7. CONSTRAINT clients_dept_id_fk FOREIGN KEY(department_id) REFERENCES departments(department_id));
Line 2
The table that contains the Primary Key in a Foreign Key Constraint is known as:
Parent Table
If a Primary Key is made up of more than one column, one of the columns can be null. True or False?
False
An example of adding a check constraint to limit the salary that an employee can earn is:
ALTER TABLE employees ADD CONSTRAINT emp_salary_ck CHECK (salary < 100000)
A composite primary key may only be defined at the table level. True or False?
True
To automatically delete rows in a child table when a parent record is deleted use:
ON DELETE CASCADE
The main reason that constraints are added to a table is:
Constraints ensure data integrity
Which of the following pieces of code will NOT successfully create a foreign key in the CDS table that references the SONGS table
None of the above
The number of check constraints that can be defined on a column is:
There is no limit
MANAGING CONSTRAINTS
What mechamisn does Oracle use in the background to enforce uniqueness in Primary and Unique key constraints?
Unique indexes are created in the background by Oracle when Primary and Unique constraints are created or enabled
Once constraints have been created on a table you will have to live with them as they are unless you drop and re-create the table. True or False?
False
You can drop a column in a table with a simple ALTER TABLE DROP COLUMN statement, even if the column is referenced in a constraint. True or False?
False
All of a user's constraints can be viewed in the Oracle Data Dictionary view called:
USER_CONSTRAINTS
The command to 'switch off' a constraint is:
ALTER TABLE DISABLE CONSTRAINT
Abonați-vă la:
Postări (Atom)