Oracle Mock Test 1

30 Questions

Settings
Please wait...
Oracle Quizzes & Trivia

Mock test for 10g oracle


Questions and Answers
  • 1. 
    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

  • 2. 
    You want to display the titles of books that meet these criteria: 1. Purchased before January 21, 2001 2. Price is less then $500 or greater than $900 You want to sort the results by their data of purchase, starting with the most recently bought book. Which statement should you use?
    • A. 

      SELECT book_title FROM books WHERE price between 500 and 900 AND purchase_date < ’21-JAN-2001’ ORDER BY purchase_date;

    • B. 

      SELECT book_title FROM books WHERE price IN (500,900) AND purchase_date < ’21-JAN-2001’ ORDER BY purchase date ASC;

    • C. 

      . SELECT book_title FROM books WHERE price < 500 or > 900 AND purchase_date < ’21-JAN-2001’ ORDER BY purchase date DESC;

    • D. 

      SELECT book_title FROM books WHERE (price < 500 OR price > 900) AND purchase_date < ’21-JAN-2001’ ORDER BY purchase date DESC;

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

      You want all unmatched data from one table.

    • B. 

      You want all matched data from both tables.

    • C. 

      You want all unmatched data from both tables

    • D. 

      One of the tables has more data than other.

  • 4. 
    Which of the following stores data externally?
    • A. 

      BFILE

    • B. 

      BLOB

    • C. 

      CLOB

    • D. 

      LONG RAW

  • 5. 
    Which operator can be used with a multiple row subquery?
    • A. 

      =

    • B. 

      LIKE

    • C. 

      NOT IN

    • D. 

      BETWEEN

    • E. 

      IS

  • 6. 
    Which is a major problem with SQL? 
    • A. 

      SQL cannot support object-orientation

    • B. 

      The same query can be written in many ways, each with vastly different execution plans.

    • C. 

      SQL syntax is too difficult for non-computer professionals to use

    • D. 

      SQL creates excessive locks within the Oracle database

  • 7. 
    You need to display the last names of those employees who have the letter "A" as the second character in their names. Which SQL statement displays the required results? 
    • A. 

      SELECT last_name FROM EMP WHERE last_name LIKE'_A%;

    • B. 

      SELECT last_name FROM EMP WHERE last name='*A%

    • C. 

      SELECT last_name FROM EMP WHERE last name ='* _A%;

    • D. 

      SELECT last_name FROM EMP WHERE last name LIKE '* a%

  • 8. 
    Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) Which three statements inserts a row into the table? (Choose three)
    • A. 

      INSERT INTO employees VALUES ( NULL, ‘John’,‘Smith’);

    • B. 

      INSERT INTO employees( first_name, last_name) VALUES(‘John’,‘Smith’);

    • C. 

      INSERT INTO employees VALUES (‘1000’,‘John’,NULL);

    • D. 

      INSERT INTO employees(first_name,last_name, employee_id) VALUES ( 1000, ‘John’,‘Smith’);

    • E. 

      . INSERT INTO employees (employee_id) VALUES (1000);

    • F. 

      INSERT INTO employees (employee_id, first_name, last_name) VALUES ( 1000, ‘John’,‘’);

  • 9. 
     Mark for review View the image below and examine the data in the EMPLOYEES and DEPARTMENTS tables. You want to retrieve all employees' last names, along with their managers' last names and their department names. Which query would you use?
    • A. 

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

    • B. 

      SELECT e.last_name, m.last_name, department_name FROM employees e LEFT OUTER JOIN employees m on ( e.manager_id = m.employee_id) LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);

    • C. 

      SELECT e.last_name, m.last_name, department_name FROM employees e RIGHT OUTER JOIN employees m on ( e.manager_id = m.employee_id) LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);

    • D. 

      SELECT e.last_name, m.last_name, department_name FROM employees e LEFT OUTER JOIN employees m on ( e.manager_id = m.employee_id) RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id);

    • E. 

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

    • F. 

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

  • 10. 
    DML operations be performed on our table through a complex view. True or false
    • A. 

      True

    • B. 

      False

  • 11. 
    Select TCL statements  (Multiple answers)
    • A. 

      DROP

    • B. 

      COMMIT

    • C. 

      ROLLBACK

    • D. 

      DELETE

    • E. 

      SAVEPOINT

  • 12. 
    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;

  • 13. 
    What is a TRUNCATE statement in Oracle? 
    • A. 

      A DDL command and can be rolled back

    • B. 

      A DDL command and cannot be rolled back

    • C. 

      A TCL command and can be rolled back

    • D. 

      A DML command and cannot be rolled back

  • 14. 
    Examine the data in the EMPLOYEES table: LAST_NAME DEPARTMENT_ID SALARY Getz 10 3000 Davis 20 1500 King 20 2200 Davis 30 5000 … Which three subqueries work? (Choose three)
    • A. 

      SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary)

    • B. 

      SELECT last_name FROM employees Where salary > ANY (SELECT MAX(salary) FROM employees GROUP BY department_id);

    • C. 

      SELECT department_id FROM employees WHERE SALARY > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id);

    • D. 

      SELECT * FROM employees WHERE salary = (SELECT AVG(salary) FROM employees GROUP BY department_id);

    • E. 

      SELECT distinct department_id FROM employees Where salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id);

    • F. 

      SELECT * FROM employees where salary > (SELECT MIN(salary) FROM employees GROUP BY department.id);

  • 15. 
    How would you add a foreign key constraint on the dept_no column in the EMP table. Referring to the ID column in the DEPT table?
    • A. 

      Use the ALTER TABLE command with the ADD clause on the EMP table.

    • B. 

      Use the ALTER TABLE command with the ADD clause in the DEPT table.

    • C. 

      Use the ALTER TABLE command with the MODIFY clause on the DEPT table.

    • D. 

      Use the ALTER TABLE command with the MODIFY clause on the EMP table.

    • E. 

      This task cannot be accomplished

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

      46 and 45.93

    • B. 

      50 and 45.93

    • C. 

      50 and 45.9

    • D. 

      46 and 45

    • E. 

      45.95 and 46

  • 17. 
    What is the syntax for removing a PRIMARY KEY constraint and all its dependent constraints?
    • A. 

      ALTER TABLE table_name DROP PRIMARY KEY CASCADE;

    • B. 

      ALTER TABLE table_name DISABLE CONSTRAINT PRIMARY KEY CASCADE;

    • C. 

      ALTER TABLE table_name REMOVE CONSTRAINT PRIMARY KEY CASCADE;

    • D. 

      A PRIMARY KEY constraint CANNOT be removed if it has dependent constraints.

  • 18. 
    A subquery can be used to _________. 
    • A. 

      Create groups of data

    • B. 

      Sort data in a specific order

    • C. 

      Retrieve data based on an unknown condition

    • D. 

      Convert data to a different format

  • 19. 
    You are granted the CREATE VIEW privilege. What does this allow you to do? 
    • A. 

      Create a view in any scheme

    • B. 

      Create a view in your schema

    • C. 

      Create a view only if it is based on tables that you created

    • D. 

      Create a view that is accessible by everyone

    • E. 

      Create a table view

  • 20. 
    In a query with a GROUP BY clause, the columns in the SELECT clause
    • A. 

      Must be aggregate functions only

    • B. 

      Must also appear in the GROUP BY clause

    • C. 

      Must also appear in the GROUP BY and HAVING clauses

  • 21. 
    Mark for review which two tasks can you perform using only the TO_CHAR function? (Choose two.)  
    • A. 

      Convert 10 to 'TEN'

    • B. 

      Convert 10 to '10'

    • C. 

      Convert '10' to 10

    • D. 

      Convert a date to a character expression

    • E. 

      Convert a character expression to a date

  • 22. 
    Evaluate the SQL statement: SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) b WHERE a.dept_id = b.dept_id AND a.sal < b.maxsal; What is the result of the statement?
    • A. 

      The statement produces an error at line 1.

    • B. 

      The statement produces an error at line 3.

    • C. 

      The statement produces an error at line 6.

    • D. 

      The statement returns the employee name, salary, department ID, and maximum salary earned in the department of the employee for all employees who earn less than the maximum salary in their department.

    • E. 

      The statement returns the employee name, salary, department ID, and maximum salary earned in the department of the employee for all departments that pay less salary then the maximum salary paid in the company

  • 23. 
    The ORDERS table has these columns   ORDER_ID                    NUMBER (4)                 NOT NULL CUSTOMER_ID             NUMBER (12)                NOT NULL ORDER_TOTAL            NUMBER (10, 2)   The ORDERS table tracks the Order number, the order total and the customer to whom the Order belongs. Which two statements retrieve orders with an inclusive total that ranges between 100.00 and 200.00 dollars? (Choose Two).
    • A. 

      SELECT customer_id, order_id, order_total FROM orders HAVING order total BETWEEN 100 and 2000

    • B. 

      SELECT customer_id, order_id, order_total FROM orders WHERE order_total BETWEEN 100 and 2000

    • C. 

      SELECT customer_id, order_id, order _total FROM orders WHERE order_total>= 100 and order_total

    • D. 

      SELECT customer_id, order_id, order_total FROM orders WHERE order_total >= 100 and

  • 24. 
    Mark for review. Which four are types of functions available in SQL? (Choose 4) 
    • A. 

      String

    • B. 

      Character

    • C. 

      Integer

    • D. 

      Date

    • E. 

      Translation

    • F. 

      Conversion

    • G. 

      Calendar

    • H. 

      Numeric

  • 25. 
    Evaluate the SQL statement DROP TABLE DEPT: Which four statements are true of the SQL statement? (Choose two) 
    • A. 

      All data in the table is deleted, and the table structure is also deleted.

    • B. 

      All data in the table is deleted, but the structure of the table is retained.

    • C. 

      All indexes based on the DEPT table are dropped.

    • D. 

      All synonyms based on the DEPT table are deleted

    • E. 

      All views based on the DEPT table are deleted.

  • 26. 
    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 '\\';

  • 27. 
    Examine the structure of student table: Name Null Type STU ID NOT NULL NUMBER(3) NAME VARCHER2(25) ADDRESS VARCHER2(50) GRADUATION DATE Currently the table is empty. You have decided that null values should not be allowed for the NAME column. Which statement restricts NULL values from being entered into column?
    • A. 

      ALTER TABLE student ADD CONSTRAINT name(NOT NULL);

    • B. 

      ALTER TABLE student ADD CONSTRAINT NOT NULL (name);

    • C. 

      ALTER TABLE student MODIFY CONSTRAINT name(NOT NULL);

    • D. 

      ALTER TABLE student MODIFY(name varcher2(25) NOT NULL);

  • 28. 
    Which of the following is not a number function
    • A. 

      To_numbert()

    • B. 

      Sinh()

    • C. 

      Sqrt()

    • D. 

      Round()

  • 29. 
    You want of display the details or all employees whose last names is Smith. But you are not sure in which case last names are stored. Which statement will list all the employees whose last name is Smith?
    • A. 

      Select last name, first name. FROM emp WHERE last name= ‘smith’;

    • B. 

      Select last name, first name. FROM emp WHERE UPPER (last name)= ‘smith’;

    • C. 

      Select last name, first name. FROM emp WHERE last name=UPPER (‘smith’);

    • D. 

      Select last name, first name. FROM emp WHERE LOWER (last name)= ‘smith’;

  • 30. 
    Which statement explicitly names a constraint? 
    • A. 

      ALTER TABLE student_grades ADD CONSTRAINT NAME=student_id_fk FOREIGN KEY (student_id) REFERENCES student(student_id);

    • B. 

      ALTER TABLE student_grades ADD FOREIGN KEY (student_id) REFERENCES students (student_id);

    • C. 

      ALTER TABLE student grades ADD NAMED CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students (student_id);

    • D. 

      ALTER TABLE student_grades ADD CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students (student_id);