Online Access Free 1Z0-007 Exam Questions

The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(4)
ENAME VARCHAR2 (25)
JOB_ID VARCHAR2(10)
Which SQL statement will return the ENAME, length of the ENAME, and the numeric position of the letter "a" in the ENAME column, for those employees whose ENAME ends with a the letter "n"?

• A.SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1,1) FROM EMPLOYEES WHERE INSTR(ENAME, 1, 1) = 'n';
• B.SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, ,-1,1) FROM EMPLOYEES WHERE SUBSTR(ENAME, -1, 1) = 'n';
• C.SELECT ENAME, LENGTH(ENAME), SUBSTR(ENAME, -1,1) FROM EMPLOYEES WHERE INSTR(ENAME, -1, 1) = 'n';
• D.SELECT ENAME, LENGTH(ENAME), INSTR(ENAME, 'a') FROM EMPLOYEES WHERE SUBSTR(ENAME, -1, 1) = 'n';

Examine the structure of the STUDENTS table:

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 (SELECT student_id, marks
  FROM students
  WHERE ROWNUM <= 10
  AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-
  99'
  AND course_id = 'INT_SQL'
  ORDER BY marks DESC);
• B.SELECT student_id, marks, ROWNUM "Rank"
  FROM (SELECT student_id, marks
  FROM students
  WHERE (finish_date BETWEEN '01-JAN-99 AND '31-DEC-99'
  AND course_id = 'INT_SQL'
  ORDER BY marks DESC)
  WHERE ROWNUM <= 10;
• C.SELECT student id, marks, ROWNUM "Rank"
  FROM (SELECT student_id, marks
  FROM students
  ORDER BY marks)
  WHERE ROWNUM <= 10
  AND finish date BETWEEN '01-JAN-99' AND '31-DEC-99'
  AND course_id = 'INT_SQL';
• D.SELECT student_id, marks, ROWID "Rank"
  FROM students
  WHERE ROWID <= 10
  AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99'
  AND course_id = 'INT_SQL'
  ORDER BY marks;
• E.SELECT student_ id, marks, ROWNUM "Rank"
  FROM students
  WHERE ROWNUM <= 10
  AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99
  AND course_id = 'INT_SQL'
  ORDER BY marks DESC;

Examine the description of the MARKS table:

SUBJ1 and SUBJ2 indicate the marks obtained by a student in two subjects.
Examine this SELECT statement based on the MARKS table:
SELECT subj1+subj2 total_marks, std_id
FROM marks
WHERE subj1 > AVG (subj1) AND subj2 > AVG(subj2)
ORDER BY total_marks;
What is the result of the SELECT statement?

• A.The statement returns an error at the ORDER BY clause.
• B.The statement executes successfully and returns the student ID and sum of all marks for each student who obtained more than the average mark in each subject.
• C.The statement returns an error at the WHERE clause.
• D.The statement returns an error at the SELECT clause.

A subquery can be used to _________.

• A.Create groups of data
• B.Sort data in a specific order
• C.Convert data to a different format
• D.Retrieve data based on an unknown condition

The EMPLOYEE tables have these columns:

You want to display the name and annual salary multiplied by the commission_pct for all employees. For records that have a NULL commission_pct, a zero must be displayed against the calculated column.
Which SQL statement displays the desired results?

• A.SELECT last_name, (salary * 12) * NVL(commission_pct, 0) FROM EMPLOYEES;
• B.SELECT last_name, (salary * 12) * commission_pct FROM EMPLOYEES;
• C.SELECT last_name, (salary * 12) * NVL2(commission_pct, 0) FROM EMPLOYEES;
• D.SELECT last_name, (salary * 12) * IFNULL(commission_pct, 0) FROM EMPLOYEES;