A user is setting up a join operation between tables EMP and DEPT. There are som

Question

A user is setting up a join operation between tables EMP and DEPT. There are some employees in the EMP table that the user wants returned by the query, but the employees are not assigned to departments yet. Which SELECT statement is most appropriate for this user? Select one: a. select e.empid, d.head from emp e, dept d; b. Select e.empid, d.head from emp e, dept d where e.dept# = d.dept#; c. Select e.empid, d.head from emp e, dept d where e.dept# (+) = d.dept#; d. Select e.empid, d.head from emp e right outer join on dept d where e.dept# = d.dept#; cascade update option (in the context of referential integrity) Select one: a. means do not change the value in the primary key field if that record has related records in another table b. means change all the related records in child tables if the record in parent table is changed c. Both of above d. None of above Consider the table (Relation) STUDREC SREF (Key Field) INITIAL SURNAME DATEOFBIRTH GENDER RESIDENCE KIDS HOMETOWN DISTANCE The key is SREF (student reference number). This table is in Select one: a. first normal form b. second normal form c. third normal form d. None of the above What SQL structure is used to limit column values of a table? Select one: a. The LIMIT constraint b. The CHECK constraint c. The VALUE constraint d. None of the above is correct. Which SQL statement is used to remove data from a database? Select one: a. DELETE b. REMOVE c. TRUNCATE d. DEL

Explanation / Answer

Which SELECT statement is most appropriate for this user?

Select one:

a. select e.empid, d.head from emp e, dept d;

b. Select e.empid, d.head from emp e, dept d where e.dept# = d.dept# (+);

c. Select e.empid, d.head from emp e, dept d where e.dept# (+) = d.dept#;

d. Select e.empid, d.head from emp e right outer join on dept d where e.dept# = d.dept#;

Answer: b.

We are looking for a query in which we have left outer join between tables emp, dept.

option A: Does cartesian product of emp table and dept table

option B: This is left outer join, so it will display all the matching rows along with unmatched rows

of left table which is emp.

option C: Right outer join

option d: Right outer join.

//////////////////////////////////////////////////////////////

cascade update option (in the context of referential integrity)

Select one:

a. means do not change the value in the primary key field if that record has related records in another table

b. means change all the related records in child tables if the record in parent table is changed

c. Both of above

d. None of above

Answer: C

If a delete statement affects one or more rows in a foreign key table,

those rows will be deleted when the primary key record is deleted.

If an update statement affects rows in the foreign key table,

those rows will be updated with the value from the primary key record

after it has been updated.

By using cascading referential integrity constraints, you can define the

actions that the SQL Server takes when a user tries to delete or update

a key to which existing foreign keys point. The REFERENCES clauses of the

CREATE TABLE and ALTER TABLE statements support the ON DELETE and ON

UPDATE clauses.

It depends on the option specified , if we write as cascade update on or not

Consider the table (Relation) STUDREC SREF (Key Field)

INITIAL SURNAME DATEOFBIRTH GENDER RESIDENCE KIDS HOMETOWN

DISTANCE The key is SREF (student reference number). This table is in

Select one:

a. first normal form

b. second normal form

c. third normal form

d. None of the above

Answer: As key is SREF, it means all other columns are derived from SREF.

So, relation is in BCNF. As a relational schema is in BCNF when all

redundancy based on functional dependency has been removed,

although other types of redundancy may still exist.

What SQL structure is used to limit column values of a table?

Select one: a. The LIMIT constraint b. The CHECK constraint c. The VALUE

constraint d. None of the above

Answer: Option b

Which SQL statement is used to remove data from a database?

Select one:

a. DELETE b. REMOVE c. TRUNCATE d. DEL

Answer: delete

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.