Oracle_test7

20 Questions | Total Attempts: 203

SettingsSettingsSettings
Please wait...
Oracle_test7

This test is used to test your skills in joins , functions


Related Topics
Questions and Answers
  • 1. 
    You want to retrieve all employees, whether or not they have matching departments in the departments table. Which query would you use?
    • A. 

      SELECT last_name, department_name FROM employees e LEFT OUTER JOIN departments d ON (e. department _ id = d. department _id);

    • B. 

      SELECT last_name, department_name FROM employees JOIN departments(+);

    • C. 

      SELECT last_name, department_name ON (e. department_ id = d. departments_id); FROM employees(+) e JOIN departments d

    • D. 

      SELECT last_name, department_name FROM employees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id);

    • E. 

      SELECT last_name, department_name FROM employees(+) , departments ON (e. department _ id = d. department _id);

  • 2. 
    Examine the structure of the EMPLOYEES, DEPARTMENTS, and LOCATIONS tables. EMPLOYEES(id,name,deptid) ,DEPT(deptid,dname,locid) , Location(locid,name) Which two SQL statements produce the name, department name, and the city of all the employees who earn more then 10000?
    • A. 

      SELECT emp_name, department_name, city FROM employees e JOIN departments d USING (department_id) JOIN locations 1 USING (location_id) WHERE salary > 10000;

    • B. 

      SELECT emp_name, department_name, city FROM employees e, departments d, locations 1 JOIN ON (e.department_id = d.department id) AND (d.location_id =1.location_id) AND salary > 10000;

    • C. 

      SELECT emp_name, department_name, city FROM employees e, departments d, locations 1 WHERE salary > 10000;

    • D. 

      . SELECT emp_name, department_name, city FROM employees e, departments d, locations 1 WHERE e.department_id = d.department_id AND d.location_id = 1.location_id AND salary > 10000

    • E. 

      SELECT emp_name, department_name, city FROM employees e NATURAL JOIN departments, locations WHERE salary > 10000;

  • 3. 
    Which statement lists the ID, name, and salary of the employee, and the ID and name of the employee's manager, for all the employees who have a manager and earn more than 4000?
    • A. 

      SELECT employee_id "Emp_id", emp_name "Employee",salary,employee_id "Mgr_id", emp_name "Manager"FROM employees WHERE salary > 4000;

    • B. 

      SELECT e.employee_id "Emp_id", e.emp_name "Employee",e.salary,m.employee_id "Mgr_id", m.emp_name "Manager"FROM employees e, employees m WHERE e.mgr_id = m.mgr_id AND e.salary > 4000;

    • C. 

      SELECT e.employee_id "Emp_id", e.emp_name "Employee",e.salary,m.employee_id "Mgr_id", m.emp_name "Manager"FROM employees e, employees m WHERE e.mgr_id = m.employee_id AND e.salary > 4000;

    • D. 

      SELECT e.employee_id "Emp_id", e.emp_name "Employee",e.salary,m.mgr_id "Mgr_id", m.emp_name "manager" FROM employees e, employees m WHERE e.mgr_id = m.employee_id AND e.salary > 4000;

    • E. 

      SELECT e.employee_id "Emp_id", e.emp_name "Employee",e.salary,m.mgr_id "Mgr_id", m.emp_name "Manager"FROM employees e, employees m WHERE e.employee_id = m.employee_id AND e.salary > 4000;

  • 4. 
    In which case would you use a FULL OUTER JOIN?
    • A. 

      You want all matched and unmatched data from only one table

    • B. 

      You want all unmatched data from one table.

    • C. 

      You want all matched data from both tables.

    • D. 

      You want all unmatched data from both tables.

    • E. 

      One of the tables has more data than the other.

  • 5. 
    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

  • 6. 
    Examine the structure of the EMPLOYEES, DEPARTMENTS, and TAX tables. EMPLOYEES (ID ,ename,sal,deptid) DEPARTMENTS(deptid,dname) TAX(minsal ,maxsal, taxpercent) For which situation would you use a nonequijoin query?
    • A. 

      To find the tax percentage for each of the employees.

    • B. 

      To list the name, job id, and manager name for all the employees.

    • C. 

      To find the name, salary, and department name of employees who are not working with Smith

    • D. 

      To find the number of employees working for the Administrative department and earning less then 4000

    • E. 

      To display name, salary, manager ID, and department name of all the employees, even if the employees do not have a department ID assigned.

  • 7. 
    Which two are character manipulation functions?
    • A. 

      TRIM

    • B. 

      REPLACE

    • C. 

      TRUNC

    • D. 

      TO_DATE

    • E. 

      MOD

  • 8. 
    • A. 

      You can join a maximum of two tables through an equijoin.

    • B. 

      You can join a maximum of two columns through an equijoin.

    • C. 

      You specify an equijoin condition in the SELECT or FROM clauses of a SELECT statement.

    • D. 

      . To join two tables through an equijoin, the columns in the join condition must be primary key and foreign key columns

    • E. 

      You can join n tables (all having single column primary keys) in a SQL statement by specifying a minimum of n-1 join conditions

  • 9. 
    Which clause should you use to exclude group results?
    • A. 

      WHERE

    • B. 

      HAVING

    • C. 

      RESTRICT

    • D. 

      GROUP BY

    • E. 

      ORDER BY

  • 10. 
    Which three are true regarding the use of outer joins?
    • A. 

      You cannot use IN operator in a condition that involves an outerjoin

    • B. 

      You use (+) on both sides of the WHERE condition to perform an outerjoin

    • C. 

      . You cannot link a condition that is involved in an outerjoin to another condition by using the OR operator

    • D. 

      You use an outerjoin to see only the rows that do not meet the join condition.

    • E. 

      In the WHERE condition, you use (+) following the name of the column in the table without matching rows, to perform an outerjoin.

  • 11. 
    Which two statements are true about WHERE and HAVING clauses?
    • A. 

      A WHERE clause can be used to restrict both rows and groups.

    • B. 

      A WHERE clause can be used to restrict rows only.

    • C. 

      . A HAVING clause can be used to restrict both rows and groups.

    • D. 

      A HAVING clause can be used to restrict groups only.

    • E. 

      . A HAVING clause CANNOT be used in subqueries.

  • 12. 
    Evaluate the SQL statement: SELECT ROUND(45.953, -1), TRUNC(45.936, 2) FROM dual; Which values are displayed?
    • A. 

      46 and 45

    • B. 

      46 and 45.93

    • C. 

      50 and 45.93

    • D. 

      50 and 45.9

    • E. 

      45 and 45.93

  • 13. 
    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), INSTR(ENAME, 'a') FROM EMPLOYEES WHERE SUBSTR (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), SUBSTR(ENAME, -1,1) FROM EMPLOYEES WHERE INSTR (ENAME, -1,1) = 'n';

  • 14. 
    Which SQL statement returns a numeric value?
    • A. 

      SELECT ADD_MONTHS(MAX(hire_Date), 6) FROM EMP;

    • B. 

      SELECT ROUND(hire_date) FROM EMP;

    • C. 

      SELECT sysdate-hire_date FROM EMP;

    • D. 

      SELECT TO_NUMBER(hire_date + 7) FROM EMP;

  • 15. 
    Which is a valid CREATE TABLE statement?
    • A. 

      CREATE TABLE EMP9$# (empid number(2));

    • B. 

      CREATE TABLE EMP*123 (empid number(2));

    • C. 

      CREATE TABLE PACKAGE (packid number(2));

    • D. 

      CREATE TABLE 1EMP_TEST (empid number(2));

  • 16. 
    What is the result of this expression? Select one answer. TRUNC(ROUND(FLOOR(CEIL(1.2)-.1) + .3445, 3), 2)
    • A. 

      0.34

    • B. 

      1.345

    • C. 

      1.35

    • D. 

      1.3445

    • E. 

      1.34

  • 17. 
    Which of these set operators will not sort the rows? (Choose the best answer.)
    • A. 

      INTERSECT

    • B. 

      MINUS

    • C. 

      UNION

    • D. 

      UNION ALL

  • 18. 
    If a compound query contains both a MINUS and an INTERSECT operator, which will be applied first? (Choose the best answer.)
    • A. 

      The INTERSECT, because INTERSECT has higher precedence than MINUS

    • B. 

      The MINUS, because MINUS has a higher precedence than INTERSECT.

    • C. 

      The precedence is determined by the order in which they are specified.

    • D. 

      It is not possible for a compound query to include both MINUS and INTERSECT

  • 19. 
    Consider this compound query: select empno, hired from emp union all select emp_id,hired,fired from ex_emp; The columns EMP.EMPNO and EX_EMP.EMP_ID are integer; the column EMP.HIRED is timestamp; the columns EX_EMP.HIRED and EX_EMP.FIRED are date. Why will the statement fail? (Choose the best answer.)
    • A. 

      Because the columns EMPNO and EMP_ID have different names

    • B. 

      Because the columns EMP.HIRED and EX_EMP.HIRED are different data types

    • C. 

      Because there are two columns in the first query and three columns in the second query

    • D. 

      For all the reasons above

    • E. 

      The query will succeed.

  • 20. 
    Study this statement: select ename from emp union all select ename from ex_emp; In what order will the rows be returned? (Choose the best answer.)
    • A. 

      The rows from each table will be grouped and within each group will be sorted on ENAME

    • B. 

      The rows from each table will be grouped but not sorted.

    • C. 

      The rows will not be grouped but will all be sorted on ENAME.

    • D. 

      The rows will be neither grouped nor sorted.