1) Which three are true regarding the use of outer joins? (Choose three.)
A. You cannot use IN operator in a condition that involves an outerjoin.
B. You use (+) on both sides of the WHERE condition to perform an outer join.
C. You use (*) on both sides of the WHERE condition to perform an outer join.
D. You use an outer join to see only the rows that do not meet the join condition.
E. In the WHERE condition, you use (+) following the name of the column in the table without matching rows, to perform an outer join.
F. You cannot link a condition that is involved in an outer join to another condition by using the OR operator.
2)
Which statement creates a new user?
A. CREATIVE USER susan;
B. CREATIVE OR REPLACE USER susan;
C. CREATE NEW USER susan DEFAULT
D. CREATE USER susan
IDENTIFIED BY blue;
E. CREATE NEW USER susan IDENTIFIED BY blue;
F. CREATE OR REPLACE USER susan IDENTIFIED BY blue;
Answer: D
Explanation:
The correct statement to create user is: CREATE USER user_name IDENTIFIED BY password. Incorrect Answers
A: This syntax is incorrect to create user: you need to provide also password for the user.
B: There is no CREATE OR REPLACE USER command in Oracle.
C: There is no CREATE NEW USER user_name DEFAULT command in Oracle.
E: There is no CREATE NEW USER user_name IDENTIFIED BY command in Oracle.
F: There is no CREATE OR REPLACE USER user_name IDENTIFIED BY command in Oracle.
3)
You need to change the definition of an existing table. The COMMERCIALS table needs its DESCRIPTION column changed to hold varying length characters up to 2000 bytes. The column can currently hold 1000 bytes per value. The table contains 20000 rows.
Which statement is valid?
A. ALTER TABLE commercials MODIFY (description CHAR2(2000));
B. ALTER TABLE commercials CHANGE (description CHAR2(2000));
C. ALTER TABLE commercials
CHANGE (description VARCHAR2 (2000));
D. ALTER TABLE commercials
MODIFY (description VARCHAR2 (2000));
E. You cannot increase the size of a column if the table has rows.
Answer: D
Explanation:
Incorrect Answers
A: There is no CHAR2 datatype in Oracle. Also 200 symbols is not enough to store up to 2000 bytes.
B: There is no CHAR2 datatype in Oracle. There is no ALTER TABLE table_name CHANGE command in Oracle.
C: There is no ALTER TABLE table_name CHANGE command in Oracle.
E: You can increase the size of a column if the table has rows. It can be done with ALTER TABLE table_name
MODIFY column.
4)
What does the TRUNCATE statement do?
A. Removes the table
B. Removes all rows from a table
C. Shortens the table to 10 rows
D. Removes all columns from a table
E. Removes foreign keys from a table
Answer: B
Explanation:
Command TRUNCATE is used to remove all row data from the table, while leaving the definition of the table intact, including the definition of constraints and any associated database objects as indexes, constraints, and triggers on the table.
Incorrect Answers
A: Command TRUNCATE does not remove the table. DROP TABLE command does that.
C: There is no command in Oracle to shortens the table to 10 rows: but you can do this with queries.
D: This command does not remove all columns from a table.
E: This command does not remove all foreign keys from a table.
5)Which is an /SQL*Plus command?
A. INSERT
B. UPDATE
C. SELECT
D. DESCRIBE
E. DELETE
F. RENAME
Answer: D
Explanation:
There is only one SQL*Plus command in this list: DESCRIBE. It cannot be used as SQL command. This command returns a description of tablename,
including all columns in that table, the datatype for each column, and an indication of whether the column permits storage of NULL values.
Incorrect Answers
A: INSERT is not a SQL*Plus command. It's data-manipulation language (DML) command.
B: UPDATE is not a SQL*Plus command. It's data-manipulation language (DML) command.
C: SELECT is not a SQL*Plus command.
E: DELETE is not a SQL*Plus command. It's data-manipulation language (DML) command.
F: RENAME is not a SQL*Plus command.
6)Which SELECT statement should you use to extract the year from the system date and display it in the format "1998"?
A. SELECT TO_CHAR(SYSDATE,'yyyy') FROM dual;
B. SELECT TO_DATE(SYSDATE,'yyyy') FROM dual;
C. SELECT DECODE(SUBSTR(SYSDATE, 8), 'YYYY') FROM dual;
D. SELECT DECODE(SUBSTR(SYSDATE, 8), 'year') FROM dual;
E. SELECT TO_CHAR(SUBSTR(SYSDATE, 8,2),'yyyy') FROM dual;
Answer: A
Explanation:
Function TO_CHAR(x, y) converts the value x to a character or converts a date to a character string using formatting conventions.
Incorrect Answers
B: Function TO_DATE(x,[y]) converts the non-date value x to a date using the format specified by x.
C: The DECODE function is used as substitution of IF-THEN-ELSE PL/SQL construction in SQL queries.
The SELECT statement provides incorrect syntax of it because it cannot have only two parameters.
D: The DECODE function is used as substitution of IF-THEN-ELSE PL/SQL construction in SQL queries.
The SELECT statement provides incorrect syntax of it because it cannot have only two parameters.
E: This statement provide incorrect syntax of TO_CHAR() function: it requires only one parameter, not two.
7)
The EMPLOYEES table contains these columns:
LAST_NAME VARCHAR2 (25)
SALARY NUMBER (6,2)
COMMISSION_PCT NUMBER (6)
You need to write a query that will produce these results:
1. Display the salary multiplied by the commission_pct.
2. Exclude employees with a zero commission_pct.
3. Display a zero for employees with a null commission value. Evaluate the SQL statement:
SELECT LAST_NAME, SALARY*COMMISSION_PCT FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL; What does the statement provide?
A. All of the desired results
B. Two of the desired results
C. One of the desired results
D. An error statement
Answer: C
Explanation:
This statement will provide only one of the desired results: display the salary multiplied by the commission_pct. It will not exclude employees with a zero commission_pct and display a zero for employees with a null commission value.
Incorrect Answers
A: This statement will provide only one of the desired results, not all.
B: This statement will provide only one of the desired results, not two.
D: This statement will not generate an error, it is correct .
8)
A subquery can be used to _________.
A. Create groups of data
B. Sort data in a specific order
C. Convert data to a different format
D. Retrieve data based on an unknown condition
Answer: D
Explanation:
A sub-query can be used to retrieve data based on an unknown condition
Incorrect Answers
A: A sub-query cannot be used to create groups of data, GROUP BY clause is used for that.
B: A sub-query cannot be used to sort data in a specific order, ORDER BY clause is used for that.
C: A sub-query cannot convert data to a different format.
9)
Which clause should you use to exclude group results?
A. WHERE
B. HAVING
C. RESTRICT
D. GROUP BY
E. ORDER BY
Answer: B
Explanation:
HAVING clause is used to weed out unwanted data once the data is grouped using the GROUP BY statement. Incorrect Answers
A: WHERE clause cannot be used for this purpose.
C: There is no RESTRICT command in Oracle.
D: GROUP BY cannot be used itself to exclude group results.
E: ORDER BY clause may be used only to sort final results, not to exclude group results.
10)
Scott issues the SQL statements:
CREATE TABLE dept
(deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13)};
GRANT SELECT ON DEPT T0 SUE;
If Sue needs to select from Scott's DEPT table, which command should she use?
A.SELECT * FROM DEPT;
B. SELECT *
FROM SCOTT. DEPT;
C. SELECT *
FROM DBA.SCOTT DEPT;
D. SELECT *
FROM ALL_USERS
WHERE USER_NAME = 'SCOTT'
AND TABLE NAME = 'DEPT';
Answer: B
Explanation:
If the table doesn't exist in your schema, you must prefix the table name with the schema information, separating the owner from the table name with a period.
Incorrect Answers
A: If you don't have DEPT table in your schema, this statement will fail.
C:This statement is incorrect.
D:This statement is incorrect. ALL_USERS view is used to extract information about users.
