Evaluate these two SQL statements: SELECT last_name, salary , hire_date FROM EMPLOYEES ORDER BY salary DESC; SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC;
The two statements produce identical results.
The second statement returns a syntax error.
There is no need to specify DESC because the results are sorted in descending order by default.
The two statements can be made to produce identical results by adding a column alias for the salary column in the second SQL statement.
You would like to display the system date in the format "Monday, 01 June, 2001". Which SELECT statement should you use?
SELECT TO_DATE(SYSDATE, 'FMDAY, DD Month, YYYY') FROM dual;
SELECT TO_CHAR(SYSDATE, 'FMDD, DY Month, 'YYY') FROM dual;
SELECT TO_CHAR(SYSDATE, 'FMDay, DD Month, YYYY') FROM dual;
SELECT TO_CHAR(SYSDATE, 'FMDY, DDD Month, YYYY') FROM dual;
SELECT TO_DATE(SYSDATE, 'FMDY, DDD Month, YYYY') FROM dual;
Which SELECT statement will the result ‘ello world’ from the string ‘Hello World’?
SELECT SUBSTR( ‘Hello World’,1) FROM dual;
SELECT INITCAP(TRIM (‘Hello World’, 1,1)) FROM dual;
SELECT LOWER(SUBSTR(‘Hello World’, 1, 1) FROM dual;
SELECT LOWER(SUBSTR(‘Hello World’, 2, 1) FROM dual;
SELECT LOWER(TRIM (‘H’ FROM ‘Hello World’)) FROM dual;
Evaluate this SQL statement: SELECT e.employee_id, (.15* e.salary) + (.5 * e.commission_pct) + (s.sales amount * (.35 * e.bonus)) AS CALC_VALUE FROM employees e, sales s WHERE e.employee_id = s.emp_id; What will happen if you remove all the parentheses from the calculation?
The value displayed in the CALC_VALUE column will be lower.
The value displayed in the CALC_VALUE column will be higher.
There will be no difference in the value displayed in the CALC_VALUE column.
An error will be reported.
From SQL*Plus, you issue this SELECT statement: SELECT* From orders; You use this statement to retrieve data from a data table for __________. (Choose all that apply)
Updating
Viewing
Deleting
Inserting
Truncating
Which two are attributes of /SQL*Plus? (Choose two)
/SQL*Plus commands cannot be abbreviated.
/SQL*Plus commands are accesses from a browser.
/SQL*Plus commands are used to manipulate data in tables.
/SQL*Plus commands manipulate table definitions in the database.
/SQL*Plus is the Oracle proprietary interface for executing SQL statements.
In which scenario would index be most useful?
The indexed column is declared as NOT NULL.
The indexed columns are used in the FROM clause.
The indexed columns are part of an expression.
The indexed column contains a wide range of values.
Which clause would you use in a SELECT statement to limit the display to those employees whose salary is greater then 5000?
ORDER BY SALARY > 5000
GROUP BY SALARY > 5000
HAVING SALARY > 5000
WHERE SALARY > 5000
Which are correct guidelines for naming database tables?
Must begin with either a number or a letter.
Must be 1-30 characters long.
Should not be an Oracle Server reserved word.
Must contain only A-Z, a-z, 0-+, _, *, and #.
Must contain only A-Z, a-z, 0-9, _, $, and #.
Must begin with a letter.
Which operator can be used with a multiple-row subquery?
=
LIKE
BETWEEN
NOT IN
IS
<>
You need to display the last names of those employees who have the letter “A” as the second character in their names. Which SQL statement displays the required results?
SELECT last_name FROM EMP WHERE last_name LIKE ‘_A%’;
SELECT last_name FROM EMP WHERE last name =’*A%’
SELECT last_name FROM EMP WHERE last name =’_A%’;
SELECT last_name FROM EMP WHERE last name LIKE ‘*A%’
Which two are character manipulation functions? (Choose two.)
TRIM
REPLACE
TRUNC
TO_DATE
MOD
Consider the following SQL statement. SELECT qty FROM sales WHERE rate IN (300,800); Which of the following operators can be used to substitute the 'IN' operator?
ALL
BETWEEN...AND
OR
Which of the following operators is used in a correlated subquery?
IN
EXISTS
UNION
INTERSECT
Which of the following are used to define a CHAR datatype variable?
INSERT
PROMPT
ACCEPT
DEFINE
Which of the following events results in Automatic rollback?
Exiting from SQL*Plus
System crashing
Issue of GRANT or CREATE command
None of the above
Which of the following naming rules apply to tables? (Choose three)
A table name can duplicate the name of any other database object, if not owned by same user.
Table names can contain $,_, and # signs.
Table names are case-sensitive.
Table names begin with a letter.
Table names are not created in Uppercase by default.
Which of the following statements creates a view and does not allow the values to be changed through the view?
CREATE VIEW empl_vu AS SELECT * FROM employee WHERE sal > 10000 WITH READ ONLY;
CREATE VIEW empl_vu AS SELECT * FROM employee WHERE sal > 10000 WITH CHECK OPTION;
CREATE VIEW empl_vu AS SELECT * FROM employee WHERE sal > 10000 WITH CHECK CONSTRAINT;
Which of the following are system privileges? (Choose two)
DELETE
SELECT
CREATE TABLE
UPDATE
DROP TABLE
Which of the following data dictionary views displays the objects owned by the user?
DICTIONARY
TABLE_PRIVILEGES
ALL_VIEWS
USER_OBJECTS
