Oracle Mock Test

20 Questions

Settings
Please wait...
Oracle Quizzes & Trivia

Questions and Answers
  • 1. 
    Which two statements are true regarding the default behavior of the ORDER BY clause? (Choose two.)
    • A. 

      Null values are left out of the sort.

    • B. 

      Character values are displayed from Z to A.

    • C. 

      Date values are displayed with the earliest value first.

    • D. 

      Null values are displayed last for descending sequences.

    • E. 

      Numeric values are displayed with the lowest values first.

  • 2. 
    Which statement adds a constraint that ensures the CUSTOMER_NAME column of the CUSTOMERS table holds a value?
    • A. 

      ALTER TABLE customers ADD CONSTRAINT cust_name_nn CHECK customer_name IS NOT NULL;

    • B. 

      ALTER TABLE customers MODIFY CONSTRAINT cust_name_nn CHECK customer_name IS NOT NULL;

    • C. 

      ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn NOT NULL;

    • D. 

      ALTER TABLE customers MODIFY customer_name CONSTRAINT cust_name_nn IS NOT NULL;

    • E. 

      ALTER TABLE customers MODIFY name CONSTRAINT cust_name_nn NOT NULL;

    • F. 

      ALTER TABLE customers ADD CONSTRAINT cust_name_nn CHECK customer_name NOT NULL;

  • 3. 
    Examine the SQL statement that creates ORDERS table: CREATE TABLE orders (SER_NO NUMBER UNIQUE, ORDER_ID NUMBER, ORDER_DATE DATE NOT NULL, STATUS VARCHAR2(10) CHECK (status IN ('CREDIT', 'CASH')), PROD_ID NUMBER REFERENCES PRODUCTS(PRODUCT_ID), ORD_TOTAL NUMBER, PRIMARY KEY (order_id, order_date)); For which columns would an index be automatically created when you execute the above SQL statement? (Choose two.)
    • A. 

      SER_NO

    • B. 

      ORDER_ID

    • C. 

      STATUS

    • D. 

      PROD_ID

    • E. 

      ORD_TOTAL

    • F. 

      Composite index on ORDER_ID and ORDER_DATE

  • 4. 
    Evaluate this SQL statement: SELECT e.employee_id, (.15* e.salary) + (.5 * e.commission_pct) + (s.sales_amount * (.35 * e.bonus)) AS CALC_VALUE FROM employees e, sales s WHERE e.employee_id = s.emp_id; What will happen if you remove all the parentheses from the calculation?
    • A. 

      The value displayed in the CALC_VALUE column will be lower.

    • B. 

      The value displayed in the CALC_VALUE column will be higher.

    • C. 

      There will be no difference in the value displayed in the CALC_VALUE column.

    • D. 

      An error will be reported.

  • 5. 
    What is necessary for your query on an existing view to execute successfully?
    • A. 

      The underlying tables must have data.

    • B. 

      You need SELECT privileges on the view.

    • C. 

      The underlying tables must be in the same schema.

    • D. 

      You need SELECT privileges only on the underlying tables.

  • 6. 
    The EMPLOYEES table contains these columns: EMPLOYEE_ID NUMBER(4) LAST_NAME VARCHAR2 (25) JOB_ID VARCHAR2(10) You want to search for strings that contain 'SA_' in the JOB_ID column. Which SQL statement do you use?
    • A. 

      SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA\_%' ESCAPE '\';

    • B. 

      SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA_';

    • C. 

      SELECT employee_id, last_name, job_id FROM employees WHERE job_id LIKE '%SA_' ESCAPE "\";

    • D. 

      SELECT employee_id, last_name, job_id FROM employees WHERE job_id = '%SA_';

  • 7. 
    Examine this statement: SELECT student_id, gpa FROM student_grades WHERE gpa > &&value; You run the statement once, and when prompted you enter a value of 2.0. A report is produced. What happens when you run the statement a second time?
    • A. 

      An error is returned.

    • B. 

      You are prompted to enter a new value.

    • C. 

      A report is produced that matches the first report produced.

    • D. 

      You are asked whether you want a new value or if you want to run the report based on the previous value.

  • 8. 
    • A. 

      SELECT TO_CHAR(2000, '$#,###.##') FROM dual;

    • B. 

      SELECT TO_CHAR(2000, '$0,000.00') FROM dual;

    • C. 

      SELECT TO_CHAR(2000, '$9,999.00') FROM dual;

    • D. 

      SELECT TO_CHAR(2000, '$9,999.99') FROM dual;

    • E. 

      SELECT TO_CHAR(2000, '$2,000.00') FROM dual;

    • F. 

      SELECT TO_CHAR(2000, '$N,NNN.NN') FROM dual;

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

  • 10. 
    The CUSTOMERS table has these columns: CUSTOMER_ID NUMBER(4) NOT NULL CUSTOMER_NAME VARCHAR2(100) NOT NULL CUSTOMER_ADDRESS VARCHAR2(150) CUSTOMER_PHONE VARCHAR2(20) You need to produce output that states "Dear Customer customer_name, ". The customer_name data values come from the CUSTOMER_NAME column in the CUSTOMERS table. Which statement produces this output?
    • A. 

      SELECT dear customer, customer_name, FROM customers;

    • B. 

      SELECT "Dear Customer", customer_name || ',' FROM customers;

    • C. 

      SELECT 'Dear Customer ' || customer_name ',' FROM customers;

    • D. 

      SELECT 'Dear Customer ' || customer_name || ',' FROM customers;

    • E. 

      SELECT "Dear Customer " || customer_name || "," FROM customers;

    • F. 

      SELECT 'Dear Customer ' || customer_name || ',' || FROM customers;

  • 11. 
    • A. 

      SELECT TO_CHAR(SYSDATE,'yyyy') FROM dual;

    • B. 

      SELECT TO_DATE(SYSDATE,'yyyy') FROM dual;

    • C. 

      SELECT DECODE(SUBSTR(SYSDATE, 8), 'YYYY') FROM dual;

    • D. 

      SELECT DECODE(SUBSTR(SYSDATE, 8), 'year') FROM dual;

    • E. 

      SELECT TO_CHAR(SUBSTR(SYSDATE, 8,2),'yyyy') FROM dual;

  • 12. 
    Examine the description of the MARKS table: STD_ID NUMBER(4) STUDENT_NAME VARCHAR2(30) SUBJ1 NUMBER(3) SUBJ2 NUMBER(3) SUBJ3 NUMBER(3) SUBJ1, SUBJ2, and SUBJ3 indicate the marks (grades) obtained by a student in the three subjects. Which two statements are valid? (Choose two.)
    • A. 

      SELECT SUM(subj1, subj2, subj3) FROM marks;

    • B. 

      SELECT SUM(subj1 + subj2 + subj3) FROM marks;

    • C. 

      SELECT SUM(subj1), SUM(subj2), SUM(subj3) FROM marks;

    • D. 

      SELECT MAX(subj1, subj2, subj3) FROM marks;

    • E. 

      SELECT MINIMUM(subj1) FROM marks;

    • F. 

      SELECT COUNT(std_id) FROM marks WHERE subj1 >= AVG(subj1);

  • 13. 
    Which are iSQL*Plus commands? (Choose all that apply.)
    • A. 

      INSERT

    • B. 

      UPDATE

    • C. 

      SELECT

    • D. 

      DESCRIBE

    • E. 

      DELETE

    • F. 

      RENAME

  • 14. 
    Which three SELECT statements display 2000 in the format "$2,000.00"? (Choose three.)
    • A. 

      SELECT TO_CHAR(2000, '$#,###.##') FROM dual;

    • B. 

      SELECT TO_CHAR(2000, '$0,000.00') FROM dual;

    • C. 

      SELECT TO_CHAR(2000, '$9,999.00') FROM dual;

    • D. 

      SELECT TO_CHAR(2000, '$9,999.99') FROM dual;

    • E. 

      SELECT TO_CHAR(2000, '$2,000.00') FROM dual;

    • F. 

      SELECT TO_CHAR(2000, '$N,NNN.NN') FROM dual;

  • 15. 
    The INVENTORY table contains these columns: ID_NUMBER NUMBER PK CATEGORY VARCHAR2(10) LOCATION NUMBER DESCRIPTION VARCHAR2(30) PRICE NUMBER(7,2) QUANTITY NUMBER . You want to return the total of the extended amounts for each item category and location, including only those inventory items that have a price greater than $100.00. The extended amount of each item
    • A. 

      SELECT category, SUM(price * quantity) TOTAL, location FROM inventory WHERE price > 100.00 GROUP BY category;

    • B. 

      SELECT category, location, SUM(price) FROM inventory WHERE price > 100.00 GROUP BY category, location;

    • C. 

      SELECT category, SUM(price * quantity) TOTAL, location FROM inventory WHERE price > 100.00;

    • D. 

      SELECT category, SUM(price * quantity) TOTAL, location FROM inventory WHERE price > 100.00 GROUP BY category, location;

  • 16. 
    Which two statements about the evaluation of clauses in a SELECT statement are true? (Choose two.)
    • A. 

      The Oracle Server will evaluate a HAVING clause before a WHERE clause.

    • B. 

      The Oracle Server will evaluate a WHERE clause before a GROUP BY clause.

    • C. 

      The Oracle Server will evaluate a GROUP BY clause before a HAVING clause.

    • D. 

      The Oracle Server will evaluate an ORDER BY clause before a WHERE clause.

    • E. 

      The Oracle Server will evaluate an ORDER BY clause before a HAVING clause.

  • 17. 
    Which two tasks can you 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

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

  • 19. 
    • A. 

      SELECT SUBSTR( ‘HelloWorld’,1) FROM dual;

    • B. 

      SELECT INITCAP(TRIM (‘HelloWorld’, 1,1)) FROM dual;

    • C. 

      SELECT LOWER(SUBSTR(‘HellowWorld’, 1, 1) FROM dual;

    • D. 

      SELECT LOWER(SUBSTR(‘HelloWorld’, 2, 1) FROM dual;

    • E. 

      SELECT LOWER(TRIM (‘H’ FROM ‘HelloWorld’)) FROM dual;

  • 20. 
    Which three functions can be used to manipulate character, number, or date column values? (Choose three.)
    • A. 

      CONCAT

    • B. 

      ROUND

    • C. 

      TRUNC

    • D. 

      RPAD

    • E. 

      INSTR