Which two statements are true about Data Manipulation Language (DML) statements?

1. A. An INSERT INTO...VALUES.. statement can add multiple rows per execution to a table.
2. B. An UPDATE... SET... statement can modify multiple rows based on multiple conditions on a table.
3. C. ADELETE FROM..... statement can remove rows based on only a single condition on a table.
4. D. An INSERT INTO... VALUES..... statement can add a single row based on multiple conditions on a table.
5. E. ADELETE FROM..... statement can remove multiple rows based on multiple conditions on a table.
6. F. An UPDATE....SET.... statement can modify multiple rows based on only a single condition on a table.

Correct Answer: BE
References:
http://www.techonthenet.com/sql/and_or.php

When does a transaction complete? (Choose all that apply.)

1. A. When a PL/SQL anonymous block is executed
2. B. When a DELETE statement is executed
3. C. When a data definition language statement is executed
4. D. When a TRUNCATE statement is executed after the pending transaction
5. E. When a ROLLBACK command is executed

Correct Answer: CDE

Examine the business rule:
Each student can work on multiple projects and each project can have multiple students.
You need to design an Entity Relationship Model (ERD) for optimal data storage and allow for generating reports in this format:
STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME PROJECT_TASK
Which two statements are true in this scenario?

1. A. The ERD must have a 1:M relationship between the STUDENTS and PROJECTS entities.
2. B. The ERD must have a M:M relationship between the STUDENTS and PROJECTS entities that must be resolved into 1:M relationships.
3. C. STUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the PROJECTS entity.
4. D. PROJECT_ID must be the primary key in the PROJECTS entity and foreign key in the STUDENTS entity.
5. E. An associative table must be created with a composite key of STUDENT_ID and PROJECT_ID, which is the foreign key linked to the STUDENTS and PROJECTS entities.

Correct Answer: BE
References:
http://www.oracle.com/technetwork/issue-archive/2011/11-nov/o61sql-512018.html

Examine the structure of the INVOICE table. NameNull?Type
-------------------------------------------------- INV_NONOT NULLNUMBER(3) INV_DATEDATE INV_AMTNUMBER(10,2)
Which two SQL statements would execute successfully?

1. A. SELECT inv_no, NVL2(inv_date, 'Pending', 'Incomplete')FROM invoice;
2. B. SELECT inv_no, NVL2(inv_amt, inv_date, 'Not Available')FROM invoice;
3. C. SELECT inv_no, NVL2(inv_date, sysdate-inv_date, sysdate)FROM invoice;
4. D. SELECT inv_no, NVL2(inv_amt, inv_amt*.25, 'Not Available')FROM invoice;

Correct Answer: AC

Which three statements are true regarding the WHERE and HAVING clauses in a SQL statement? (Choose three.)

1. A. WHERE and HAVING clauses cannot be used together in a SQL statement.
2. B. The HAVING clause conditions can have aggregate functions.
3. C. The HAVING clause conditions can use aliases for the columns.
4. D. The WHERE clause is used to exclude rows before the grouping of data.
5. E. The HAVING clause is used to exclude one or more aggregated results after grouping data.

Correct Answer: ABD