Q. Which is an iSQL*Plus command?
A. INSERT
B. UPDATE
C. SELECT
D. DESCRIBE
E. DELETE
F. RENAME
Q. Which two statements complete a transaction? (Choose two.)
A. DELETE employees;
B. DESCRIBE employees;
C. ROLLBACK TO SAVEPOINT C;
D. GRANT SELECT ON employees TO SCOTT;
E. ALTER TABLE employeesSET UNUSED COLUMN sal;
F. SELECT MAX(sal) FROM employeesWHERE department_id = 20;
Q. You own a table called EMPLOYEES with this table structure:
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
HIRE_DATE DATE
What happens when you execute this DELETE statement?
DELETE employees;
A. You get an error because of a primary key violation.
B. The data and structure of the EMPLOYEES table are deleted.
C. The data in the EMPLOYEES table is deleted but not the structure.
D. You get an error because the statement is not syntactically correct.
Q. You own a table called EMPLOYEES with this table structure:
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
HIRE_DATE DATE
What happens when you execute this DELETE statement?
DELETE employees;
A. You get an error because of a primary key violation.
B. The data and structure of the EMPLOYEES table are deleted.
C. The data in the EMPLOYEES table is deleted but not the structure.
D. You get an error because the statement is not syntactically correct.
Q. Evaluate these two SQL statements:
SELECT last_name, salary, hire_dateFROM EMPLOYEES ORDER BY salary DESC;
SELECT last_name, salary, hire_dateFROM EMPLOYEES ORDER BY 2 DESC;
What is true about them?
A. The two statements produce identical results.
B. The second statement returns a syntax error.
C. There is no need to specify DESC because the results are sorted in descending order by default.
D. The two statements can be made to produce identical results by adding a column alias for the salary column in the second SQL statement.
Q. Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:
EMPLOYEES
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
HIRE_DATE DATE
NEW_EMPLOYEES
EMPLOYEE_ID NUMBER Primary Key
NAME VARCHAR2(60)
Which MERGE statement is valid?
A. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET C.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT value S(e.employee_id, e.first_name ||', '||e.last_name);
B. MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET C.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT valueS(e.employee_id, e.first_name ||', '||e.last_name);
C. MERGE INTO new_employees cUSING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET C.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT value S(e.employee_id, e.first_name ||', '||e.last_name);
D. MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new_employees valueS(e.employee_id, e.first_name ||', '||e.last_name);
Q. Which three are true regarding the use of outer joins? (Choose three.)
A. You cannot use IN operator in a condition that involves an outer join.
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.
Q. View the image below to examine the structure of the EMPLOYEES, DEPARTMENTS, and LOCATIONS tables. Which two SQL statements produce the name, department name, and the city of all the employees who earn more than 10000? (Choose two.)
A. SELECT emp_name, department_name, city FROM employees e JOIN departments d USING (department_id)JOIN locations lUSING (location_id) WHERE salary > 10000;
B. SELECT emp_name, department_name, city FROM employees e, departments d, locations l JOIN ON (e.department_id = d.department_id) AND (d.location_id =l.location_id)AND salary > 10000;
C. SELECT emp_name, department_name, city FROM employees e, departments d, locations l WHERE salary > 10000;
D. SELECT emp_name, department_name, city FROM employees e, departments d, locations l WHERE e.department_id = d.department_id AND d.location_id = l.location_id AND salary > 10000;
E. SELECT emp_name, department_name, city FROM employees e NATURAL JOIN departments, locations WHERE salary > 10000;
Q. Evaluate this SQL statement:
SELECT e.employee_id, (.15* e.salary) + (.5 * e.commission_pct) + (s.sales_amount * (.35 * e.bonus)) AS CALC_valueFROM employees e, sales WHERE e.employee_id = s.emp_id; What will happen if you remove all the parentheses from the calculation?
A. The value displayed in the CALC_value column will be lower.
B. The value displayed in the CALC_value column will be higher.
C. There will be no difference in the value displayed in the CALC_value column.
D. An error will be reported.
Q. View the image below to examine the structures of the EMPLOYEES and TAX tables. You need to find the percentage tax applicable for each employee. Which SQL statement would you use?
A. SELECT employee_id, salary, tax_percent FROM employees e, tax tWHERE e.salary BETWEEN t.min_salary AND t.max_salary;
B. SELECT employee_id, salary, tax_percent FROM employees e, tax tWHERE e.salary > t.min_salary AND < t.max_salary;
C. SELECT employee_id, salary, tax_percent FROM employees e, tax tWHERE MIN(e.salary) = t.min_salary AND MAX(e.salary) = t.max_salary;
D. You cannot find the information because there is no common column between the two tables.
Q. The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(4) LAST_NAME VARCHAR2 (25) JOB_ID VARCHAR2(10) You want to search for strings that contain 'SA_' in the JOB_ID column. Which SQL statement do you use?
A. SELECT employee_id, last_name, job_idFROM employees WHERE job_id LIKE '%SA\_%' ESCAPE '\';
B. SELECT employee_id, last_name, job_idFROM employees WHERE job_id LIKE '%SA_';
C. SELECT employee_id, last_name, job_idFROM employees WHERE job_id LIKE '%SA_' ESCAPE "\";
D. SELECT employee_id, last_name, job_idFROM employees WHERE job_id = '%SA_';
Q. You are the DBA for an academic database. You need to create a role that allows a group of users to modify existing rows in the STUDENT_GRADES table. Which set of statements accomplishes this?
A. CREATE ROLE registrar;GRANT MODIFY ON student_grades TO registrar; GRANT registrar to user1, user2, user3
B. CREATE NEW ROLE registrar;GRANT ALL ON student_grades TO registrar; GRANT registrar to user1, user2, user3
C. CREATE ROLE registrar;GRANT UPDATE ON student_grades TO registrar; GRANT ROLE registrar to user1, user2, user3
D. CREATE ROLE registrar;GRANT UPDATE ON student_grades TO registrar; GRANT registrar to user1, user2, user3;
E. CREATE registrar;GRANT CHANGE ON student_grades TO registrar; GRANT registrar;
Q. In which four clauses can a subquery be used? (Choose four.)
A. in the INTO clause of an INSERT statement
B. in the FROM clause of a SELECT statement
C. in the GROUP BY clause of a SELECT statement
D. in the WHERE clause of a SELECT statement
E. in the SET clause of an UPDATE statement
F. in the valueS clause of an INSERT statement
Q. Top N analysis requires _____ and _____. (Choose two.)
A. the use of rowid
B. a GROUP BY clause
C. an ORDER BY clause
D. only an inline view
E. an inline view and an outer query
Q. Examine the structure of the EMP_DEPT_VU view:
Column Name Type Remarks
EMPLOYEE_ID NUMBER From the EMPLOYEES table
EMP_NAME VARCHAR2(30) From the EMPLOYEES table
JOB_ID VARCHAR2(20) From the EMPLOYEES table
SALARY NUMBER From the EMPLOYEES table
DEPARTMENT_ID NUMBER From the DEPARTMENTS table
DEPT_NAME VARCHAR2(30) From the DEPARTMENTS table
Which SQL statement produces an error?
A. SELECT *
FROM emp_dept_vu;
B. SELECT department_id, SUM(salary)
FROM emp_dept_vuGROUP BY department_id;
C. SELECT department_id, job_id, AVG(salary)
FROM emp_dept_vuGROUP BY department_id, job_id;
D. SELECT job_id, SUM(salary)
FROM emp_dept_vu
WHERE department_id IN (10,20)GROUP BY job_id
HAVING
SUM(salary) > 20000;
E. None of the statements produce an error; all are valid.
Q. You need to create a table named ORDERS that contains four columns:
an ORDER_ID column of number data type
a CUSTOMER_ID column of number data type
an ORDER_STATUS column that contains a character data type
a DATE_ORDERED column to contain the date the order was placed
When a row is inserted into the table, if no value is provided for the status of the order, the value PENDING should be used instead. Which statement accomplishes this?
A. CREATE TABLE orders (order_id NUMBER(10), customer_id NUMBER(8),order_status NUMBER(10) DEFAULT 'PENDING',date_ordered DATE );
B. CREATE TABLE orders (order_id NUMBER(10), customer_id NUMBER(8),order_status VARCHAR2(10) = 'PENDING',date_ordered DATE );
C. CREATE OR REPLACE TABLE orders (order_id NUMBER(10), customer_id NUMBER(8),order_status VARCHAR2(10) DEFAULT 'PENDING',date_ordered DATE );
D. CREATE OR REPLACE TABLE orders (order_id NUMBER(10), customer_id NUMBER(8),order_status VARCHAR2(10) = 'PENDING',date_ordered DATE );
E. CREATE TABLE orders (order_id NUMBER(10), customer_id NUMBER(8),order_status VARCHAR2(10) DEFAULT 'PENDING',date_ordered DATE );
F. CREATE TABLE orders (order_id NUMBER(10), customer_id NUMBER(8),order_status VARCHAR2(10) DEFAULT 'PENDING',date_ordered VARCHAR2 );
Q. What is true about sequences?
A. Once created, a sequence belongs to a specific schema.
B. Once created, a sequence is linked to a specific table.
C. Once created, a sequence is automatically available to all users.
D. Only the DBA can control which sequence is used by a certain table.
E. Once created, a sequence is automatically used in all INSERT and UPDATE statements.
Q. Evaluate this SQL statement:
SELECT ename, sal, 12*sal+100FROM emp;
The SAL column stores the monthly salary of the employee. Which change must be made to the above syntax to calculate the annual compensation as "monthly salary plus a monthly bonus of $100, multiplied by 12"?
A. No change is required to achieve the desired results.
B. SELECT ename, sal, 12*(sal+100)FROM emp;
C. SELECT ename, sal, (12*sal)+100FROM emp;
D. SELECT ename, sal+100,*12FROM emp;
Q. You need to write a SQL statement that returns employee name, salary, department ID, and maximum salary earned in the department of the employee for all employees who earn less than the maximum salary in their department. Which statement accomplishes this task?
A. SELECT a.emp_name, a.sal, b.dept_id, MAX(sal) FROM employees a, departments b WHERE a.dept_id = b.dept_idANDA.sal < MAX(sal)GROUP BY b.dept_id;
B. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) b WHERE a.dept_id = b.dept_idANDA.sal < b.maxsal;
C. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a WHERE a.sal < (SELECT MAX(sal) maxsal FROM employees b GROUP BY dept_id);
D. SELECT emp_name, sal, dept_id, maxsal FROM employees, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) WHERE a.sal < maxsal;
Q. What is true about updates through a view?
A. You cannot update a view with group functions.
B. When you update a view group functions are automatically computed.
C. When you update a view only the constraints on the underlying table will be in effect.
D. When you update a view the constraints on the views always override the constraints on the underlying tables.
Continue.....