Q1. You added a PHONE-NUMBER column of NUMBER data type to an existing EMPLOYEES table. The EMPLOYEES table already contains records of 100 employees. Now, you want to enter the phone numbers of each of the 100 employees into the table Some of the employees may not have a phone number available. Which data manipulation operation do you perform?
A. MERGE
B. INSERT
C. UPDATE
D. ADD
E. ENTER
F. You cannot enter the phone number for the existing employee records
Answer: C
Q2. Which two statements about subqueries are true? (Choose two)
A. A single row subquery can retrieve data from only one table.
B. A SQL query statement cannot display data from table B that is refered to in its subquery, unless table B is included in the main query’s FROM clause.
C. A SQL query statement cannot display data from table B that is refered to in its subquery, without including table B in its own FROM clause.
D. A single row subqery can retrieve data from more than one table
E. A single row subqery cannot be used in a condition where the LIKE operator is used for comparison.
F. A multiple-row subquery cannot be used in a condition where the LIKE operation is used for comparison.
Answer: BD
Q3. Examine the structure of the STUDENTS table
STUDENT_ID NUMBER NOT NULL., Primary Key
STUDENT_NAME VARCHAR2(30)
COURSE_ID VARCHAR2(10) NOT NULL
MARKS NUMBER
START_DATE DATE
FINISH_DATE DATE
You need to create a report of the 10 students who achieved the highest ranking in the course INT SQL and who completed the course in the year 1999. Which SQL statement accomplishes this task?
A. SELECT student_id, marks, ROWNUM “Rank” FROM student WHERE ROWNUM <= 10 AND finish_data BETWEEN ’01-JAN-99’ AND ’31-DEC-99’ AND course_id=’INT_SQL’ ORDER BY marks DESC;
B. SELECT student_id, marks, ROWID “Rank” FROM students WHERE ROWID <= 10 AND finish_data BETWEEN ’01-JAN-99’ AND ’31-DEC-99’ AND course_id=’INT_SQL’ ORDER BY marks;
C. SELECT student_id, marks ROWNUM “Rank” FROM (SELECT student_id, marks) FROM students WHERE ROWNUM <= 10 AND finish_date BETWEEN ’01-JAN99’ AND ’31-DEC-99’ AND COURSE_ID = ‘INT_SQL’ ORDER BY marks desc:
D. SELECT student_id, marks, ROWNUM ” Rank” FROM (SELECT student_id.marks FROM students ORDER BY marks DESC ) WHERE ROWNUM <= 10 AND finish_date BETWEEN ’01-JAN99’ AND ’31-DEC-99’ AND course_id = ‘INT_SQL;’
Answer: D
Q4. 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)
POSTE_CODE VARCHAR2(12)
CUSTOMER_PHONE VARCHAR2(20)
THE CUSTOMER_ID column is the primary key for the table which two statements find the number of customer? (Choose two.)
A. SELECT TOTAL (*) FROM customers;
B. SELECT COUNT (*) FROM customers;
C. SELECT TOTAL (customer_id) FROM customer;
D. SELECT COUNT(costomer_id) FROM customer;
E. SELECT COUNT(customers) FROM customers;
F. SELECT TOTAL (customer_name) FROM customers;
Anser : BD
Q5. In a SELECT statement that includes a WHERE clause, where is the GROUP BY clause placed statement?
A. immediately after the SELECT clause
B. before the WHERE clause
C. before the FROM clause
D. after the ORDER BY clause
E. after the WHERE clause
Answer : E
Q6. Which two are true about aggregate functions? (Choose two)
A. You can use aggregate functions in any clause of a SELECT statement.
B. You can use aggregate functions only in the column list of the SELECT clause and in the WHERE clause of a SELECT statement.
C. You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns
D. You can pass column names, expressions, constants, or functions as parameters to an aggregate function.
E. You can use aggregate functions on a table, only by grouping the whole table as one single group.
F. You cannot group the rows of a table by more than one column while using aggregate functions.
Answer CD
Q7. For which two constrains does the Oracle Server implicitly create a unique index? (Choose two)
A. NOT NULL
B. PRIMARY KEY
C. FOREIGN KEY
D. CHECK
E. UNIQUE
Answer: BE
Q8. Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
HIRE_DATE DATE
You issue these statements:
CREATE table new_emp
(employee_id NUMBER,
name VARCHAR2 (30));
INSERT INTO new_emp SELECT employee_id, last_name from employees;
Savepoint s2;
Delete from new_emp;
Rolback to s2;
Delete from new_emp where employee_id = 180;
UPDATE new_emp set name = ‘James’;
Rolback to s2;
UPDATE new_emp set name = ‘James’ WHERE employee_id = 180;
Rollback;
At the end of this transaction, what is true?
A. You have no rows in the table.
B. You have an employee with the name of James
C. You cannot roll back to the same savepoint more than once.
D. Your last update fails to update any rows because employee ID 180 was already deleted.
Answer : A
Q9. Which iSQL*Plus feature can be used to replace values in the where clause?
A. Substitution variables
B. replacement variables
C. prompt variables
D. instead-of variables
E. This feature cannot be implemented through | SQL*Plus
Answer : A
Q10. Evaluate the SQL statement:
SELECT ROUND(TRUNC(MOD(1600,10),-1),2) FROM dual; What will be displayed?
A. 0
B. 1
C. 0.00
D. an error statement
Answer : A
Q11. Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
DEPARTMENT_ID NUMBER
SALARY NUMBER
What is the correct syntax for an inline view?
A. SELECT a last_name, a salary, a department_id, b.maxsal FROM employees a,
(SELECT department_id, max(salary)maxsal FROM employees GROUP BY department_id) b
WHERE a department_id = department-id AND a_salary
B. SELECT a. last name, a salary, a. department_id FROM employees a
WHERE a. department_id IN (SELECT department_id FROM employees b
GROUP BY department_id having salary = SELECT max(salary) from employees
C. SELECT a last_name, a salary, a.department_id FROM employees a
WHERE a salary = SELECT max(salary) FROM employees b
WHERE a department_id = department_id);
D. SELECT a last_name, a salary, a.department_id FROM employees a
WHERE (a department_id, a salary) IN (SELECT department_id, a salary) IN
(SELECT department_id max(salary) FROM employees b GROUP BY department_id ORDER BY department_id);
Answer : A
Q12. Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID NUMBER NOT NULL
EMP_ID 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 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 emoployees WHERE job_id='SA_REP' AND comm_pet=DEFALUT 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 ob_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 emplouees 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 WHEREemployee_id IN (103,115)
Answer: C
Q13. Which data dictionary table should you query to view the object privileges granted to the user on specific columns?
A. USER_TAB_PRIVS_MADE
B. USER_TAB_PRIVS_RECD
C. USER_COL_PRIVS_MADE
D. USER_COL_PRIVS_RECD
Answer: D
Q14. Which three are DATETIME data types that can be used when specifying column definitions? (Choose three)
A. TIMESTAMP
B. INTERVAL MONTH TO DAY
C. INTERVAL DAY TO SECOND
D. INTERVAL YEAR TO MONTH
E. TIMESTAMP WITH DATABASE TIMEZONE
Answer: ACD
Q15. Examine the structure of the EMPLOYEES table:
column name data type remarks
EMPLOYEE_ID NUMBER NOT NULL, primary key
LAST_NAME VARCHAR2(30)
FIRST_NAME VARCHAR2(30)
JOB_ID NUMBER
SAL NUMBER
MGR_ID NUMBER References EMPLOYEE_ID column
DEPARTMENT_ID NUMBER
You need to create an index called NAME IDX on the first name and last name fields of the EMPLOYEES table. Which SQL statement would you use to perform this task?
A. CREATE INDEX NAME_IDX (first_name, last_name)
B. CREATE INDEX NAME_IDX (first_name AND last_name)
C. CREATE INDEX NAME_IDX ON (first_name, last_name)
D. CREATE INDEX NAME_IDX ON employees (first_name AND last_name)
E. CREATE INDEX NAME_IDX ON employees (first_name, last_name)
F. CREATE INDEX NAME_IDX FOR employees (first_name, last_name)
Answer: E
Q16. Evaluate this SQL statement:
SELECT e.EMPLOYEE_ID,e.LAST_NAME, e.DEPARTMENT_ID, d.DEPARTMENT_NAME
FROM EMP e.DEPARTMENT d WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID;
In the statement, which capabilities of a SELECT statement are performed?
A. selection, projection, join
B. difference, projection, join
C. intersection, projection, join
D. difference, projection, product
Answer: A
Q17. You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. The table is currently empty. Which statement accomplishes this task?
A. ALTER TABLE students ADD PRIMARY KEY student_id;
B. ALTER TABLE students ADD CONSTRAINT PRIMARY KEY (student_id);
C. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY stuent_id;
D. ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
E. ALTER TABLE students MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id)
Answer: D
Q18. Which syntax turns an existing constraint on?
A. ALTER TABLE table_name ENABLE constrain_name
B. ALTER TABLE table_name STATUS = ENABLE CONSTRAINT constrain_name
C. ALTER TABLE table_name ENABLE CONSTRAINT constraint_name
D. ALTER TABLE table_name STATUS ENABLE CONSTRAINT constraint_name
E. ALTER TABLE table_name TURN ON CONSTRAINT costrant_name
F. ALTER TABLE table_name TURN ON CONSTRAINT constraint_name
Answer: C
Q19. Which two statements about views are true? (Choose two)
A. A view can be created as read only
B. A view can be created as a join on two or more tables.
C. A view cannot have an ORDER BY clause in the SELECT statement.
D. A view cannot be created with a GROUP BY clause in the SELECT statement.
E. A view must have aliases defined for the column names in the SELECT statement.
Answer: AB
Q20. The database adminsrator of your company created a public synonym called HR for the HUMAN_RESOURCES table of the GENERAL schema, because many users frequentlyuse this table. As a user of the database, you created a table called HR in your chema. What happens when you execute this query?
SELECT * FROM HR;
A. you obtain the results retrieved from the public synonym HR created by the database administrator
B. you obtain the results retrieved form the HR table that belongs to your schema.
C. you get an error message because you cannot retrieve from a table that has te same ame as a public synonym
D. you obtain the results retrieved from both the public synonym HR and the HR table that belongs to your shema, as a Cartesian product.
E. You obtain the results retrieved form both the public synonym HR and the HR table that belongs to your shema, as a FULL JOIN.
Answer: B
Get More Questions and Answers with Explanation at Oracle Forums.