Oracle 1Z0-007 Real Exam Questions

Introduction to Oracle9i SQL®

1: Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID  NUMBER       Primary Key
FIRST_NAME   VARCHAR2(25)
LAST_NAME    VARCHAR2(25)
Which three statements insert 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’, ‘  ‘);
Correct Answers: C E F

2: 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
F.45.95 and 45.93
Correct Answers: C

3: Which are DML statements? (Choose all that apply.)
A.COMMIT
B.MERGE
C.UPDATE
D.DELETE
E.CREATE
F.DROP…
Correct Answers: B C D

4: Evaluate the set of SQL statements:
        CREATE TABLE dept
        (deptno   NUMBER(2),
         dname     VARCHAR2(14),
         loc       VARCHAR2(13));
         ROLLBACK;
         DESCRIBE DEPT
What is true about the set?
A.The DESCRIBE DEPT statement displays the structure of the DEPT table.
B.The ROLLBACK statement frees the storage space occupied by the DEPT table.
C.The DESCRIBE DEPT statement returns an error ORA-04043: object DEPT does not exist.
D.The DESCRIBE DEPT statement displays the structure of the DEPT table only if there is a COMMIT statement introduced before the ROLLBACK statement.
Correct Answers: A

5: 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 "monthly salary plus a monthly 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;
Correct Answers: B

6: 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
Correct Answers: A F

7: Examine the structure of the EMP_DEPT_VU view:
Column Name           Type                        Remarks
EMPLOYEE_ID       NUMBER              From the EMPLOYEES table
EMP_NAME            VARCHAR2(30)   From the EMPLOYEES table
JOB_ID                   VARCHAR2(20)   From the EMPLOYEES table
SALARY                  NUMBER             From the EMPLOYEES table
DEPARTMENT_ID   NUMBER             From the DEPARTMENTS table
DEPT_NAME           VARCHAR2(30)   From the DEPARTMENTS table
Which SQL statement produces an error?
A.SELECT *
FROM   emp_dept_vu;
B.SELECT department_id, SUM(salary)
FROM   emp_dept_vu
GROUP BY department_id;
C.SELECT department_id, job_id, AVG(salary)
FROM   emp_dept_vu
GROUP BY department_id, job_id;
D.SELECT job_id, SUM(salary)
FROM   emp_dept_vu
WHERE  department_id IN (10,20)
GROUP BY job_id
HAVING SUM(salary) > 20000;
E.None of the statements produce an error; all are valid.
Correct Answers: E

8: Evaluate this SQL statement:
        SELECT e.EMPLOYEE_ID,e.LAST_NAME,e.DEPARTMENT_ID, d.DEPARTMENT_NAME
        FROM EMPLOYEES e, DEPARTMENTS d
        WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID;
In the statement, which capabilities of a SELECT statement are performed?
A.selection, projection, join
B.difference, projection, join
C.selection, intersection, join
D.intersection, projection, join
E.difference, projection, product
Correct Answers: A

9: You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column.  The table is currently empty. Which statement accomplishes this task?
A.ALTER TABLE students
ADD PRIMARY KEY student_id;
B.ALTER TABLE students
ADD CONSTRAINT PRIMARY KEY (student_id);
C.ALTER TABLE students
ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;
D.ALTER TABLE students
ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
E.ALTER TABLE students
MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
Correct Answers: D

10: 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
Correct Answers: A C D

Download  |  Password: ciscobibles.com