Questions on SQL Part2

Part 2 – Questions on SQL and SQL *Plus

 

1. 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?

 

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, dept d where e.dept# (+) = d.dept#;

 

2. Developer ANJU executes the following statement: CREATE TABLE animals AS SELECT * from MASTER.ANIMALS; What is the effect of this statement?

 

A.      A table named ANIMALS will be created in the MASTER schema with the same data as the ANIMALS table owned by ANJU.

B.      A table named ANJU will be created in the ANIMALS schema with the same data as the ANIMALS table owned by MASTER.

C.      A table named ANIMALS will be created in the ANJU schema with the same data as the ANIMALS table owned by MASTER.

D.     A table named MASTER will be created in the ANIMALS schema with the same data as the ANJU table owned by ANIMALS.

 

3. User JANKO would like to insert a row into the EMPLOYEE table. The table has three columns: EMPID, LASTNAME, and SALARY. The user would like to enter data for EMPID 59694, LASTNAME Harris, but no salary. Which statement would work best?

 

A.      INSERT INTO employee VALUES (59694,'HARRIS', NULL);

B.      INSERT INTO employee VALUES (59694,'HARRIS');

C.      INSERT INTO employee (EMPID, LASTNAME, SALARY) VALUES (59694,'HARRIS');

D.     INSERT INTO employee (SELECT 59694 FROM 'HARRIS');

 

4. Which three of the following are valid database datatypes in Oracle? (Choose three.)

 

A.      CHAR

B.      VARCHAR2

C.      BOOLEAN

D.     NUMBER

 

5. Omitting the WHERE clause from a DELETE statement has which of the following effects?

 

A.      The delete statement will fail because there are no records to delete.

B.      The delete statement will prompt the user to enter criteria for the deletion

C.      The delete statement will fail because of syntax error.

D.     The delete statement will remove all records from the table.

 

6. Dropping a table has which of the following effects on a nonunique index created for the table?

 

A.      No effect.

B.      The index will be dropped.

C.      The index will be rendered invalid.

D.     The index will contain NULL values.

 

7. To increase the number of null-able columns for a table,

 

A.      Use the alter table statement.

B.      Ensure that all column values are NULL for all rows.

C.      First increase the size of adjacent column datatypes, then add the column.

D.    Add the column, populate the column, then add the NOT NULL constraint.

 

8. Which line of the following statement will produce an error?

 

A.      CREATE TABLE goods

B.      (good_no NUMBER,

C.      good_name VARCHAR2 check(good_name in (SELECT name FROM avail_goods)),

D.     CONSTRAINT pk_goods_01

E.      PRIMARY KEY (goodno));

F.      There are no errors in this statement.

 

9. MAXVALUE is a valid parameter for sequence creation.

 

A.      A. TRUE

B.      B. FALSE

 

10. Which function below can best be categorized as similar in function to an IF-THEN-ELSE statement?

 

A.      SQRT

B.      DECODE

C.      NEW_TIME

D.     ROWIDTOCHAR

 

 

 

Answers:

1. Option D
2. Option C
3. Option A
4. Option A, B and D
5. Option D
6. Option B
7. Option A
8. Option C
9. Option A
10. Option B

 

End of Part 2