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';
Answer: B
Q.Examine the data in the EMPLOYEES and EMP_HIST tables:
EMPLOYEES
EMPLOYEE_ID - 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 - ** - EX_DIR - 8000
120 - Ravi - 20 - 110 - SA_DIR - 6500
EMP_HIST
EMPLOYEE_ID - NAME - JOB_ID - SALARY
101 - Smith - SA_CLERK - 2000
103 - Chris - IT_CLERK - 22
104 - John - HR_CLERK - 2000
106 - Smith - AD_ASST - 3000
108 - Jennifer - HR_MGR – 4500
The EMP_HIST table is updated at the end of every year. The employee ID, name, jobID, and salary of each existing employee are modified with the latest data. New employee details are added to the table. Which statement accomplishes this task?
A. UPDATE emp_hist SET employee_id, name, job_id, salary = (SELECT employee_id, name, job_id, salary FROM employees) WHERE employee_id IN (SELECT employee_id FROM employees);
B. MERGE INTO emp_hist eh USING employees e ON (eh.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET eh.name = e.name, eh.job_id = e.job_id, eh.salary = e.salary WHEN NOT MATCHED THEN INSERT VALUES (e.employee id, e.name, e.job id, e.salary);
C. MERGE INTO emp_hist eh USING employees e ON (eh.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE emp hist SET eh.name = e.name, eh.job_id = e.job_id, eh.salary = e.s alary WHEN NOT MATCHED THEN INSERT INTO emp_hist VALUES (e.employee_id, e.name, e.job_id, e.salary);
D. MERGE INTO emp_hist eh USING employees e WHEN MATCHED THEN UPDATE emp_hist SET eh.name = e.name, eh.job_id = e.job_id, eh.salary = e.salary WHEN NOT MATCHED THEN INSERT INTO emp_hist VALUES (e.employee_id, e.name, e.job_id, e.salary);
Answer: B
Q. The EMP table has these columns: ENAME VARCHAR2(35) SALARY NUMBER (8,2) HIRE_DATE DATE Management wants a list of names of employees who have been with the company for more than five yeas. Which SQL statement displays the required results?
A. SELECT ENAME FROM EMP WHERE SYSDATE-HIRE_DATE>5
B. SELECT ENAME FROM EMP WHERE HIRE_DATE-SYSDATE > 5
C. SELECT ENAME FROM EMP WHERE (SYSDATE-HIRE_DATE)/365 > 5
D. SELECT ENAME FROM EMP WHERE (SYSDATE-HIRE_DATE)* 365 > 5
Answer: C
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.
Answer: C
Q. 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
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)
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;
Answer : B,D
Q. 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
Answer : A
Q. 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 emloyees 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
Q. Click the Exhibit button and examine the data from the ORDERS and CUSTOMERS tables.
ORDERS
ORD_ID - ORD_DATE - CUST_ID - ORD_TOTAL
100 - 12.JAN-2000 - 15 - 10000
101 - 09-MAR-2000 - 40 - 8000
102 - 09-MAR-2000 - 35 - 12500
103 - 15-MAR-2000 - 15 - 12000
104 - 25-JUN-2000 - 15 - 6000
105 - 18-JUL-2000 - 20 - 5000
106 - 18-JUL-2000 - 35 - 7000
107 - 21-JUL-2000 - 20 - 6500
108 - 04-AUG-2000 - 10 - 8000
CUSTOMERS
CUST_ID - CUST_NAME - CITY
10 - Smith - Los Angeles
15 - Bob - San Francisco
20 - Martin - Chicago
25 - Mary - New York
30 - Rina - Chicago
35 - Smith - New York
40 - Linda - New York
Evaluate the SQL statement:
SELECT * FROM orders WHERE cust_id = (SELECT cust_id FROM customers WHERE cust_name = 'Smith') What is the result when the query is executed?
A. ORD_ID ORD_DATE CUST_ID ORD_TOTAL 102 09-MAR-2000 35 12500 106 18-JUL-2000 35 7000 108 04-AUG-2000 10 8000
B. ORD_ID ORD_DATE CUST_ID ORD_TOTAL 102 09-MAR-2000 35 12500 106 18-JUL-2000 35 7000
C. ORD_ID ORD_DATE CUST_ID ORD_TOTAL 108 04-AUG-2000 10 8000
D. The query fails because the subquery returns more than one row.
E. The query fails because the outer query and the inner query are using different tables.
Answer: D
Q. 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
Q. You need to give the MANAGER role the ability to select from insert into and modify existing rows in the STUDENT_GRADES table.
Anyone given this MANAGER role should be able to pass those privileges on to others. Which statement accomplishes this.
A. GRANT select, insert, update ON student_grades TO manager;
B. GRANT select, insert, update ON student_grades TO ROLE manager
C. GRANT select, insert, modity ON student_grades TO manager WITH GRANT OPTION;
D. GRANT select, insert, update ON student_grades TO manager WITH GRANT OPTION
E. GRANT select, insert, update ON student_grades TO ROLE manager WITH GRANT OPTION;
F. GRANT select, insert, modify ON student_grades TO ROLE manager WITH GRANT OPTION
Answer: D
Q. Which of the following queries can you use to search for employees with the pattern A_B in their names?
A. SELECT last_name FROM employees WHERE last_name LIKE %A_B% ESCAPE ;
B. SELECT last_name FROM employees WHERE last_name LIKE A_B% ESCAPE %;
C. SELECT last_name FROM employees WHERE last_name LIKE %A_B% ESCAPE ;
D. SELECT last_name FROM employees WHERE last_name LIKE %A_B% ESCAPE \;
ANS: C
Q13. Refer to the SQL codes below:
SELECT manager_id, last_name, hire_date, salary, AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg FROM employees; What has been achieved?
A. it calculates, for each employee in the employees table, the average salary of the employees reporting to his/her respective manager
B. it calculates, for each employee in the employees table, the average salary of the employees reporting to the same manager who were hired in the range just before through just after the employee
C. because of a syntax problem, no row will be returned
D. it calculates, for each employee in the employees table, the average salary of the employees reporting to his/her respective manager who were hired just after the employee
E. it calculates, for each employee in the employees table, the average salary of the employees reporting to his/her respective manager who were hired just before the employee
ANS:B
Q. What does the following SQL script do?
SELECT AVG(MAX(salary)) FROM employees GROUP BY department_id;
A. it has a syntax error, the AVG clause is not valid
B. it calculates the average of the maximum salaries of all the departments
C. it has a syntax error, the MAX clause is not valid
D. it has no error, but the GROUP BY clause is not effective
E. it has no error, but the AVG clause is not effective
F. it has no error, but the MAX clause is not effective
ANS:B
Q. Which of the following are the valid ordering sequences of an ORDER BY clause?
A. ASEC
B. DESC
C. REVERT
D. RANDOM
E. ASC
ANS: BE
Q. Which two tasks can your perform by using the TO_CHAR function? (Choose two)
A. Convert 10 to ‘TEN’
B. Convert ‘10’ to 10
C. Convert ‘10’ to ‘10’
D. Convert ‘TEN’ to 10
E. Convert a date to a character expression
F. Convert a character expression to a date
Q. Which are DML statements? (Choose all that apply)
A. COMMIT…
B. MERGE…
C. UPDATE…
D. DELETE…
E. CREATE…
F. DROP…
Q. 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 s1;
UPDATE new_emp set name = UPPER(name);
Savepoint s2;
Delete from new_emp;
Rollback to s2;
Delete from new_emp where employee_id =180;
UPDATE new_emp set name = 'James';
Rollback 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.
Q. Which SQL statement generates the alias Annual Salary for the calculated column SALARY*12?
A. SELECT ename, salary*12 ‘Annual Salary’ FROM employees;
B. SELECT ename, salary*12 “Annual Salary” FROM employees;
C. SELECT ename, salary*12 AS Annual Salary FROM employees;
D. SELECT ename, salary*12 AS INITCAP(“ANNUAL SALARY”) FROM employees
Q. Which three statements about subqueries are true? (Choose three)
A. A single row subquery can retrieve only one column and one row.
B. A single row subquery can retrieve only one row but many columns.
C. A multiple row subquery can retrieve multiple rows and multiple columns.
D. A multiple row subquery can be compared by using the “>” operator.
E. A single row subquery can use the IN operator.
F. A multiple row subquery can use the “=” operator.