| 1. | Which statement about joining tables with a non-equijoin is true? |
| a. | | A WHERE clause must specify a column in one table that directly corresponds to a column in the second table |
| b. | | The tables being joined must NOT have any columns with the same name |
| c. | | The number of join conditions required is always one less than the number of tables being joined |
| d. | | No more than three tables can be joined using a non-equijoin |
| e. | | The columns being joined must have compatible data types
|
|
|
|
|
|
|
| 2. | Consider the following SELECT statement:
SELECT ename, emp_number, salary
FROM employee
WHERE dept_number = (SELECT dept_number
FROM department
WHERE location IN('CHICAGO', 'NEW YORK'))
Why does this statement return an error? |
| a. | | A multiple-row subquery returns one row |
| b. | | A single-row subquery returns more than one row |
| c. | | A multiple-column subquery returns one column |
| d. | | A multiple-row query uses a single-row subquery
|
|
|
|
|
|
|
| 3. | Consider the following SELECT statement:
SELECT ename, empno, sal
FROM emp
WHERE deptno = (SELECT deptno
FROM dept
WHERE UPPER(loc) = UPPER('&loc'))
When executing this statement, what could be the result? |
| a. | | The statement executes successfully if the LOC column in the DEPT table has unique values |
| b. | | The statement fails if the values returned from a multiple-row subquery are compared with an equality operator in the main query |
| c. | | The statement fails because an ampersand (&) character cannot be used in the subquery of a SELECT statement |
| d. | | The statement executes successfully, but does not return the expected results because of the use of the UPPER function twice in the subquery
|
|
|
|
|
|
|
| 4. | Examine the following statement:
SELECT empno_seq.CURRVAL
FROM SYS.dual;
Which value is displayed? |
| a. | | Current value of the EMPNO_SEQ sequence |
| b. | | Current value of the EMPNO_SEQ cursor |
| c. | | Values of the EMPNO_SEQ column |
| d. | | Current value of the EMPNO_SEQ index
|
|
|
|
|
|
|
| 5. | If a database is queried with the following command:
SELECT object_name
FROM all_objects
WHERE object_type = 'TABLE';
Which values are displayed? |
| a. | | Only the names of all the tables that can be accessed |
| b. | | Only the names of all the objects that can be accessed |
| c. | | Only the names of the objects that are owned |
| d. | | Only the names of the tables that are owned
|
|
|
|
|
|
|
| 6. | The ITEM table contains the following columns:
ID NUMBER(7) PK
SALE_PRICE NUMBER(7,2)
Evaluate the following SQL statements:
1.SELECT MAX(sale_price), MIN(sale_price), AVG(sale_price)
FROM ITEM;
2.SELECT ROUND(MAX(sale_price),2), ROUND(MIN(sale_price),2), ROUND(AVG(sale_price),2)
FROM ITEM
GROUP BY sale_price;
How will the results differ? |
| a. | | Statement 2 will only display one row of results; statement 1 could display more than one |
| b. | | One of the statements will generate an error |
| c. | | Statement 1 will display three values; statement 2 will display three values for each sale price |
| d. | | Statement 1 will display a result for each sale price; statement 2 will display a result for each item
|
|
|
|
|
|
|
| 7. | Consider the following SELECT statement:
SELECT dept_no "Departments", MAX(salary) "Max Salaries"
FROM employee
WHERE dept_no IN(200, 300, 400)
GROUP BY Departments
HAVING MAX(salary) > 60000;
Due to which clause does this statement fail? |
| a. | | Group By |
| b. | | Where |
| c. | | Select |
| d. | | Having
|
|
|
|
|
|
|
| 8. | Which of the following commands cause a transaction to end? |
| a. | | DELETE |
| b. | | ALTER |
| c. | | INSERT |
| d. | | UPDATE |
| e. | | ROLLBACK
|
|
|
|
|
|
|
| 9. | Which statements about data types are true? |
| a. | | The BLOB data type stores character data up to four gigabytes |
| b. | | The TIMESTAMP data type is an extension of the VARCHAR2 data type |
| c. | | The CHAR data type should be used for fixed-length character data |
| d. | | The INTERVAL YEAR TO MONTH data type allows time to be stored as an interval of years and months
|
|
|
|
|
|
|
| 10. | Which of the following statements regarding the use of subqueries are true? |
| a. | | A subquery used with the IN operator must return multiple rows |
| b. | | A subquery can be used in a CREATE VIEW statement, regardless of the number of rows it returns |
| c. | | A subquery can be used in the SET clause of an UPDATE statement, regardless of the number of rows it returns |
| d. | | A subquery used in an INTO clause of a SELECT statement must return only one column, but can return multiple rows |
| e. | | A subquery CANNOT be used in the GROUP BY clause of a SELECT statement
|
|
|
|
|
|
|
| 11. | Evaluate the following SQL*Plus command:
START empdetail
Which task will this command accomplish? |
| a. | | It runs the EMPDETAIL.SQL script file |
| b. | | It creates the EMPDETAIL.SQL file using the default file extension |
| c. | | It executes the EMPDETAIL.SQL PL/SQL routine |
| d. | | It invokes the editor to edit the contents of the EMPDETAIL file
|
|
|
|
|
|
|
| 12. | Evaluate the following SELECT statement:
SELECT emp_id, name
FROM emp
WHERE emp_id NOT IN (SELECT emp_id
FROM emp
WHERE dept_id = 30
AND job = 'SALESMAN');
What would happen if the inner query returned a NULL value? |
| a. | | No rows would be selected from the EMPLOYEE table |
| b. | | A syntax error would be returned |
| c. | | All the EMPLOYEE_ID and NAME values in the EMPLOYEE table would be displayed |
| d. | | Only the rows with EMPLOYEE_ID values equal to NULL would be included in the results
|
|
|
|
|
|
|
| 13. | In which clauses of a SELECT statement can substitution variables be used? |
| a. | | The SELECT and FROM clauses, but NOT the WHERE clause |
| b. | | The SELECT, FROM, WHERE, and GROUP BY clauses, but NOT the ORDER BY clause |
| c. | | The SELECT, WHERE, GROUP BY, and ORDER BY clauses, but NOT the FROM clause |
| d. | | The SELECT and FROM clauses, but NOT the WHERE clause |
| e. | | The SELECT, FROM, WHERE, GROUP BY, ORDER BY, and HAVING clauses
|
|
|
|
|
|
|
| 14. | Which operators can be used in an outer join condition? |
| a. | | AND |
| b. | | IN |
| c. | | OR |
| d. | | =
|
|
|
|
|
|
|
| 15. | What is the correct use of the Trunc command on a date? |
| a. | | TRUNC=To_Date('09-Jan-02,DD-MON-YY,'YEAR',"Date" from Dual; |
| b. | | Select TRUNC(To_Date('09-Jan-02,DD-MON-YY,YEAR')) "DATE" from Dual; |
| c. | | Date =TRUNC(To_DATE('09-Jan-02','DD-MON-YY'),'YEAR'),'YEAR)"DATE: from DUAL; |
| d. | | SELECT TRUNC(TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR') "Date " FROM DUAL;
|
|
|
|
|
|
|
| 16. | Which type of join should be written to perform an outer join of tables A and B that returns all rows from B? |
| a. | | Any outer join |
| b. | | A left outer join |
| c. | | A cross join |
| d. | | A right outer join |
| e. | | An inner join
|
|
|
|
|
|
|
| 17. | How many join conditions should be there to avoid a Cartesian Join for joining three tables? |
| a. | | 1 |
| b. | | 2 |
| c. | | 3 |
| d. | | None of the above
|
|
|
|
|
|
|
| 18. | Which Data Dictionary view holds information about the column in a view? |
| a. | | USER_VIEWS |
| b. | | USER_VIEW_COLUMNS |
| c. | | USER_TAB_COLUMNS |
| d. | | USER_ALL_COLUMNS
|
|
|
|
|
|
|
| 19. | Which of the following views should a user query to display the columns associated with the Constraints on a table owned by the user? |
| a. | | USER_CONSTRAINTS |
| b. | | USER_OBJECTS |
| c. | | ALL_CONSTRAINTS |
| d. | | USER_CONS_COLUMNS |
| e. | | USER_COLUMNS
|
|
|
|
|
|
|
| 20. | Which of the following SELECT statements will get the result 'ELCOME' from the string 'WELCOME'? |
| a. | | SELECT SUBSTR ('WELCOME',1) FROM dual; |
| b. | | SELECT INITCAP(TRIM('WELCOME', 1,1) FROM dual; |
| c. | | SELECT LOWER (SUBSTR ('WELCOME', 2,1) FROM dual; |
| d. | | SELECT LOWER (SUBSTR('WELCOME', 2,1) FROM dual; |
| e. | | SELECT LOWER (TRIM ('W' FROM 'WELCOME')) FROM dual;
|
|
|
|
|
|
|
| 21. | Which script displays '01-MAR-03' when the JOIN_DATE value is '01-MAR-02'? |
| a. | | SELECT ROUND(join_date, 'YEAR')
FROM Emp; |
| b. | | SELECT ROUND(join_date, 'DAY')
FROM Emp; |
| c. | | SELECT ROUND(join_date, 'MONTH')
FROM Emp; |
| d. | | SELECT ROUND(TO_CHAR(join_date, 'YYYY'))
FROM Emp;
|
|
|
|
|
|
|
| 22. | Evaluate the following statement:
TRUNCATE TABLE product;
Which of the following users can successfully issue this statement? |
| a. | | The owner of the INVENTORY table |
| b. | | Any user with access to the PUBLIC schema |
| c. | | Any user with the DELETE ANY TABLE system privilege |
| d. | | Any member of the CONNECT and RESOURCE roles
|
|
|
|
|
|
|
