Q. Which two statements about sequences are true? (Choose two)
A. You use a NEXTVAL pseudo column to look at the next possible value that would be generated from a sequence, without actually retrieving the value.
B. You use a CURRVAL pseudo column to look at the current value just generated from a sequence, without affecting the further values to be generated from the sequence.
C. You use a NEXTVAL pseudo column to obtain the next possible value from a sequence by actually retrieving the value from the sequence.
D. You use a CURRVAL pseudo column to generate a value from a sequence that would be used for a specified database column.
E. If a sequence starting from a value 100 and incremented by 1 is used by more then one application, then all of these applications could have a value of 105 assigned to their column whose value is being generated by the sequence.
F. You use REUSE clause when creating a sequence to restart the sequence once it generates the maximum value defined for the sequence.
Q. Which operator can be used with a multiple-row subquery?
A. =
B. LIKE
C. BETWEEN
D. NOT IN
E. IS
F. <>
Q. 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?
A. SELECT last_name FROM EMP WHERE last_name LIKE ‘_A%’;
B. SELECT last_name FROM EMP WHERE last name =’*A%’
C. SELECT last_name FROM EMP WHERE last name =’_A%’;
D. SELECT last_name FROM EMP WHERE last name LIKE ‘*A%’
Q. You define a multiple-row subquery in the WHERE clause of an SQL query with a comparison operator "=". What happens when the main query is executed?
A. The main query executes with the first value returned by the subquery.
B. The main query executes with the last value returned by the subquery.
C. The main query executes with all the values returned by the subquery.
D. The main query fails because the multiple-row subquery cannot be used with the comparison operator
E. You cannot define a multiple-row subquery in the WHERE clause of a SQL query.
Q. Scott issues the SQL statements:
CREATE TABLE dept
(deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13)};
GRANT SELECT
ON DEPT
TO 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';
Q. 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
Q. Which is an /SQL*Plus command?
A. INSERT
B. UPDATE
C. SELECT
D. DESCRIBE
E. DELETE
F. RENAME
Q. Which statement creates a new user?
A. CREATE USER susan;
B. CREATE 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;
Q. Which constraint can be defines only at the column level?
A. UNIQUE
B. NOT NULL
C. CHECK
D. PRIMARY KEY
E. FOREIGN KEY
Q. The DBA issues this SQL command:
CREATE USER scott
IDENTIFIES by tiger;
What privileges does the user Scott have at this point?
A. No privileges.
B. Only the SELECT privilege.
C. Only the CONNECT privilege.
D. All the privileges of a default user.
Q. Which SELECT statement will the result ‘ello world’ from the string ‘HelloWorld’?
A. SELECT SUBSTR( ‘HelloWorld’,1) FROM dual;
B. SELECT INITCAP(TRIM (‘HelloWorld’, 1,1)) FROM dual;
C. SELECT LOWER(SUBSTR(‘Hello World’, 1, 1) FROM dual;
D. SELECT LOWER(SUBSTR(‘Hello World’, 2, 1) FROM dual;
E. SELECT LOWER(TRIM (‘H’ FROM ‘Hello World’)) FROM dual;
Q. What is necessary for your query on an existing view to execute successfully?
A. The underlying tables must have data.
B. You need SELECT privileges on the view.
C. The underlying tables must be in the same schema.
D. You need SELECT privileges only on the underlying tables.
Q. Examine the data in the EMPLOYEES table.
LAST_NAME DEPARTMENT_ID SALARY
Geiz 10 3000
Davis 20 1500
King 20 2200
Davis 30 5000
---
Which three subqueries work? (Choose three.)
A. SELECT * FROM employees where salary > (SELECT MIN(salary) FROM employees GROUP BY department_id);
B. SELECT * FROM employees WHERE salary = (SELECT AVG(salary) FROM employees GROUP BY department_id);
C. SELECT distinct department_id FROM employees WHERE salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id);
D. SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id);
E. SELECT last_name FROM employees WHERE salary > ANY (SELECT MAX(salary) FROM employees GROUP BY department_id);
F. SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY AVG(SALARY));
Q. The CUSTOMERS table has these columns:
CUSTOMER_ID NUMBER(4) NOT NULL
CUSTOMER_NAME VARCHAR2(100) NOT NULL
STREET_ADDRESS VARCHAR2(150)
CITY_ADDRESS VARCHAR2(50)
STATE_ADDRESS VARCHAR2(50)
PROVINCE_ADDRESS VARCHAR2(50)
COUNTRY_ADDRESS VARCHAR2(50)
POSTAL_CODE VARCHAR2(12)
CUSTOMER_PHONE VARCHAR2(20)
Which statement finds the rows in the CUSTOMERS table that do not have a postal code?
A. SELECT customer_id, customer_name FROM customers WHERE postal_code CONTAINS NULL;
B. SELECT customer_id, customer_name FROM customers WHERE postal_code = '________';
C. SELECT customer_id, customer_name FROM customers WHERE postal_code IS NULL;
D. SELECT customer_id, customer_name FROM customers WHERE postal code IS NVL;
E. SELECT customer_id, customer_name FROM customers WHERE postal_code = NULL;
Q. Examine the data in the EMPLOYEES table.
EMPLOYEES
EMPLOYEE_ID EMP_NAME DEPT_ID MGR_ID JOB_ID SALARY
101 Smith 20 120 SA_REP 4000
102 Martin 10 105 CLERK 2500
103 Chris 20 120 IT_ADMIN 4200
104 John 30 108 HR_CLERK 2500
105 Diana 30 108 IT_ADMIN 5000
106 Smith 40 110 AD.ASST 3000
108 Jennifer 30 110 HR_DIR 6500
110 Bob 40 EK_DIR 8000
120 Revi 20 110 SA_DIR 6500
On the EMPLOYEES table, EMPLOYEE_ID is the primary key. MGR_ID is the ID of managers
and refers to the EMPLOYEE_ID. The JOB_ID column is a NOT NULL column.
Evaluate this DELETE statement:
DELETE employee_id, salary, job_id
FROM employees
WHERE dept_id = 90;
Why does the DELETE statement fail when you execute it?
A. There is no row with dept_id 90 in the EMPLOYEES table.
B. You cannot delete the JOB_ID column because it is a NOT NULL column.
C. You cannot specify column names in the DELETE clause of the DELETE statement.
D. You cannot delete the EMPLOYEE_ID column because it is the primary key of the table.
Q. Which four statements correctly describe functions that are available in SQL? (Choose four)
A. INSTR returns the numeric position of a named character.
B. NVL2 returns the first non-null expression in the expression list.
C. TRUNCATE rounds the column, expression, or value to n decimal places.
D. DECODE translates an expression after comparing it to each search value.
E. TRIM trims the heading of trailing characters (or both) from a character string.
F. NVL compares two expressions and returns null if they are equal, or the first expression of they are not equal.
G. NULLIF compares twp expressions and returns null if they are equal, or the first expression if they are not equal.
Q. Which two are attributes of /SQL*Plus? (Choose two)
A. /SQL*Plus commands cannot be abbreviated.
B. /SQL*Plus commands are accesses from a browser.
C. /SQL*Plus commands are used to manipulate data in tables.
D. /SQL*Plus commands manipulate table definitions in the database.
E. /SQL*Plus is the Oracle proprietary interface for executing SQL statements.
Q. Examine the structures of the EMPLOYEES and TAX tables.
EMPLOYEES
EMPLOYEE_ID NUMBER NOT NULL, Primary Key
EMP_NAME VARCHAR2 (30)
JOB_ID VARCHAR2 (20)
SALARY NUMBER
MGR_ID NUMBER References EMPLOYEE_ID column
DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID column of the
DEPARTMENTS table
TAX
MIN_SALARY NUMBER
MAX_SALARY NUMBER
TAX_PERCENT NUMBER Percentage tax for given salary range
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 t WHERE e.salary BETWEEN t.min_salary AND t.max_salary;
B. SELECT employee_id, salary, tax_percent FROM employees e, tax t WHERE e.salary > t.min_salary, tax_percent
C. SELECT employee_id, salary, tax_percent FROM employees e, tax t WHERE 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. Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER NOT NULL
EMP_NAME VARCHAR2(30)
JOB_ID VARCHAR2(20) DEFAULT 'SA_REP'
SAL NUMBER
COMM_PCT NUMBER
MGR_ID NUMBER
DEPARTMENT_ID NUMBER
You need to update the records of employees 103 and 115. The UPDATE statement you specify
should update the rows with the values specified below:
JOB_ID: Default value specified for this column definition.
SAL: Maximum salary earned for the job ID SA_REP.
COMM_PCT: Default value specified for this commission
percentage column, if any.
If no default value is specified for the column, the
value should be NULL.
DEPARTMENT_ID: Supplied by the user during run time through
substitution variable.
Which UPDATE statement meets the requirements?
A. UPDATE employees SET job_id = DEFAULT AND Sal = (SELECT MAX(sal) FROM employees WHERE job_id = 'SA_REP' AND comm_pct = DEFAULT AND department_id = &did WHERE employee_id IN (103,115);
B. UPDATE employees SET job_id = DEFAULT AND Sal = MAX(sal) AND comm_pct = DEFAULT OR NULL AND department_id = &did WHERE employee_id IN (103,115) AND job_id = 'SA_REP';
C. UPDATE employees SET job_id = DEFAULT, Sal = (SELECT MAX(sal) FROM employees WHERE job_id = 'SA_REP'), comm_pct = DEFAULT, department_id = &did WHERE employee_id IN (103,115);
D. UPDATE employees SET job_id = DEFAULT, Sal = MAX(sal), comm_pct = DEFAULT, department_id = &did WHERE employee_id IN (103,115) AND job_id = 'SA_REP';
E. UPDATE employees SET job_id = DEFAULT, Sal = (SELECT MAX(sal) FROM employees WHERE job_id = 'SA_REP') comm_pct = DEFAULT OR NULL, department_id = &did WHERE employee_id IN (103,115);
Q. Which clause would you use in a SELECT statement to limit the display to those employees whose salary is greater then 5000?
A. ORDER BY SALARY > 5000
B. GROUP BY SALARY > 5000
C. HAVING SALARY > 5000
D. WHERE SALARY > 5000
No comments:
Post a Comment