C. SELECT
TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'fmDdspth \YYYY HH:MI:SS AM') NEW_DATE FROM dual; D. SELECT
TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'fmtDdspth \YYYY fmtHH:MI:SS AM') NEW_DATE FROM dual;
Q: 95 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
Q: 96 Which three statements correctly describe the functions and use of constraints? (Choose three.)
A. Constraints provide data independence. B. Constraints make complex queries easy. C. Constraints enforce rules at the view level. D. Constraints enforce rules at the table level.
E. Constraints prevent the deletion of a table if there are dependencies. F. Constraints prevent the deletion of an index if there are dependencies. Q: 97 Evaluate this SQL statement: SELECT ename, sal, 12*sal+100 FROM 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 \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)+100 FROM emp;
D. SELECT ename, sal+100,*12 FROM emp;
Q: 98 Click the Exhibit button and examine the data from the EMP table. The COMMISSION column shows the monthly commission earned by the employee.
Which two tasks would require subqueries or joins in order to be performed in a single step? (Choose two.)
A. listing the employees who earn the same amount of commission as employee 3 B. finding the total commission earned by the employees in department 10
C. finding the number of employees who earn a commission that is higher than the average commission of the company
D. listing the departments whose average commission is more than 600
E. listing the employees who do not earn commission and who are working for department 20 in descending order of the employee ID
F. listing the employees whose annual commission is more than 6000
Q: 99 Click the Exhibit button and examine the data from the ORDERS and CUSTOMERS tables.
Which SQL statement retrieves the order ID, customer ID, and order total for the orders that are placed
on the same day that Martin placed his orders? A. SELECT ord_id, cust_id, ord_total FROM orders, customers WHERE cust_name='Martin'
AND ord_date IN ('18-JUL-2000','21-JUL-2000'); B. SELECT ord_id, cust_id, ord_total FROM orders
WHERE ord_date IN (SELECT ord_date FROM orders
WHERE cust_id = (SELECT cust_id FROM customers WHERE cust_name = 'Martin'));
C. SELECT ord_id, cust_id, ord_total FROM orders
WHERE ord_date IN (SELECT ord_date FROM orders, customers
WHERE cust_name = 'Martin');
D. SELECT ord_id, cust_id, ord_total FROM orders
WHERE cust_id IN (SELECT cust_id FROM customers
WHERE cust_name = 'Martin');
Q: 100 What are two reasons to create synonyms? (Choose two.) A. You have too many tables. B. Your tables are too long.
C. Your tables have difficult names.
D. You want to work on your own tables. E. You want to use another schema's tables. F. You have too many columns in your tables.
Q: 101 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 \FROM employees;
C. SELECT ename, salary*12 AS Annual Salary FROM employees;
D. SELECT ename, salary*12 AS INITCAP(\FROM employees
Q: 102 A SELECT statement can be used to perform these three functions: 1. Choose rows from a table. 2. Choose columns from a table.
3. Bring together data that is stored in different tables by creating a link between them.
Which set of keywords describes these capabilities? A. difference, projection, join B. selection, projection, join C. selection, intersection, join D. intersection, projection, join E. difference, projection, product
Q: 103 The STUDENT_GRADES table has these columns: STUDENT_ID NUMBER(12) SEMESTER_END DATE GPA NUMBER(4,3)
The registrar requested a report listing the students' grade point averages (GPA) sorted from highest
grade point average to lowest.
Which statement produces a report that displays the student ID and GPA in the sorted order requested by the registrar?
A. SELECT student_id, gpa FROM student_grades ORDER BY gpa ASC; B. SELECT student_id, gpa FROM student_grades
SORT ORDER BY gpa ASC; C. SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa;
D. SELECT student_id, gpa FROM student_grades ORDER BY gpa;
E. SELECT student_id, gpa FROM student_grades
SORT ORDER BY gpa DESC; F. SELECT student_id, gpa FROM student_grades ORDER BY gpa DESC;
Q: 104 Examine the structure of the EMPLOYEES and DEPARTMENTS tables:
EMPLOYEES
EMPLOYEE_ID NUMBER DEPARTMENT_ID NUMBER MANAGER_ID NUMBER
LAST_NAME VARCHAR2(25) DEPARTMENTS
DEPARTMENT_ID NUMBER MANAGER_ID NUMBER
DEPARTMENT_NAME VARCHAR2(35) LOCATION_ID NUMBER
You want to create a report displaying employee last names, department names, and locations. Which query should you use?
A. SELECT e.last_name, d. department_name, d.location_id FROM employees e NATURAL JOIN departments D USING department_id ;
B. SELECT last_name, department_name, location_id FROM employees NATURAL JOIN departments WHERE e.department_id =d.department_id;
C. SELECT e.last_name, d.department_name, d.location_id FROM employees e NATURAL JOIN departments d;
D. SELECT e.last_name, d.department_name, d.location_id FROM employees e JOIN departments d USING (department_id );
Q: 105 Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE
Which UPDATE statement is valid? A. UPDATE employees SET first_name = 'John' SET last_name ='Smith'
WHERE employee_id = 180; B. UPDATE employees SET first_name = 'John', SET last_name ='Smith'
WHERE employee_id = 180; C. UPDATE employees SET first_name = 'John' AND last_name ='Smith' WHERE employee_id = 180; D. UPDATE employees
SET first_name = 'John', last_name ='Smith' WHERE employee_id = 180;
Q: 106 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
Q: 107 The STUDENT_GRADES table has these columns: STUDENT_ID NUMBER(12) SEMESTER_END DATE GPA NUMBER(4,3)
Which statement finds the highest grade point average (GPA) per semester? A. SELECT MAX(gpa) FROM student_grades
WHERE gpa IS NOT NULL; B. SELECT (gpa) FROM student_grades GROUP BY semester_end
WHERE gpa IS NOT NULL;
C. SELECT MAX(gpa) FROM student_grades
WHERE gpa IS NOT NULL GROUP BY semester_end; D. SELECT MAX(gpa) GROUP BY semester_end WHERE gpa IS NOT NULL FROM student_grades; E. SELECT MAX(gpa) FROM student_grades GROUP BY semester_end
WHERE gpa IS NOT NULL;
Q: 108 Click the Exhibit button to examine the structure of the EMPLOYEES,