Oracle Test 3

50 Questions | Total Attempts: 419

SettingsSettingsSettings
Please wait...
Oracle Quizzes & Trivia

Oracle questions


Questions and Answers
  • 1. 
    After creating a new user for your Oracle database, a user still complains he or she cannot log in because of insufficient privileges errors. Which of the following actions should you take?
    • A. 

      Reset the user's password

    • B. 

      Grant create table privileges to the user

    • C. 

      . Grant the CONNECT role to the user

    • D. 

      . Unlock the user's account

  • 2. 
    Which two statements about sequences are true? (Choose two)
    • A. 

      A. You use a NEXTVAL pseudo column to look at the next possible value that would be generated from a sequence, without actually retrieving the value.

    • B. 

      B. You use a CURRVAL pseudo column to look at the current value just generated from a sequence, without affecting the further values to be generated from the sequence.

    • C. 

      C. You use a NEXTVAL pseudo column to obtain the next possible value from a sequence by actually retrieving the value form the sequence

    • D. 

      D. You use a CURRVAL pseudo column to generate a value from a sequence that would be used for a specified database column.

    • E. 

      E. If a sequence starting from a value 100 and incremented by 1 is used by more than one application, then all of these applications could have a value of 105 assigned to their column whose value is being generated by the sequence.

    • F. 

      F. You use a REUSE clause when creating a sequence to restart the sequence once it generates the maximum value defined for the sequence.

  • 3. 
    . When should you create a role? (Choose two)
    • A. 

      A. to simplify the process of creating new users using the CREATE USER xxx IDENTIFIED by yyyy statement

    • B. 

      B. to grant a group of related privileges to a user

    • C. 

      C. When the number of people using the database is very high

    • D. 

      D. to simplify the process of granting and revoking privileges

    • E. 

      E. To simplify profile maintenance for a user who is constantly traveling.

  • 4. 
     Click the Exhibit button to examine the structure of the EMPOLOYEES, DEPARTMENTS and TAX tables. EMPLOYEES   EMPLOYEE_ID             NUMBER                      NOT NULL        primary key EMP_NAME                  VARCHAR2 (30) JOB_ID                         VARCHAR2 (20) SALARY                       NUMBER MGR_ID                        NUMBER                      Reference EMPLOYEE_ID Column DEPARTMENT_ID         NUMBER                      Foreign key to DEPARTMENT_ID TO column of the DEPARTMENT table   DEPARTMENTS DEPARTMENT_ID         NUMBER                      NOT NULL        primary key DEPARTMENT_NAME VARCHAR2 (30) MGR_ID                        NUMBER                      Reference MGR_ID column of the EMPLOYEES table   TAX MIN_SALARY               NUMBER MAX_SALARY              NUMBER TAX_PERCENT            NUMBER For which situation would you use a nonequijoin query?
    • A. 

      A. to find the tax percentage for each of the employees

    • B. 

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

    • C. 

      C. to find the name, salary and the department name of employees who are not working with Smith

    • D. 

      D. to find the number of employees working for the Administrative department and earning less than 4000

    • E. 

      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

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

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

    • B. 

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

    • C. 

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

    • D. 

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

    • E. 

      E. ALTER TABLE student grades ADD NAME student_id_fk FOREIGN KEY (student_id) REFERENCES students (student_id)

  • 6. 
                 150. You created a view called EMP_DEPT_VU that contains three columns from the EMPLOYEES and DEPARTMENTS tables EMPLOYEE_ID, EMPLOYEE_NAME AND DEPARTMENT_NAME   The DEPARTMENT_ID column of the EMPLOYEES table is the foreign key to the primary key DEPARTMENT_ID column of the DEPARTMENTS table.   You want to modify the view by adding a fourth column, MANAGER_Id of NUMBER data type from the EMPLOYEES table.   How can you accomplish this task?
    • A. 

      A. ALTER VIEW emp_dept_vu (ADD manager_id NUMBER),

    • B. 

      B. MODIFY VIEW emp_dept_vu (ADD manager_id NUMBER);

    • C. 

      C. ALTER VIEW emp_dept_vu AS SELECT employee_id, employee_name Department_name, manager_id FROM employees e, departments d WHERE department_id = d.department_id;

    • D. 

      D. MODIFY VIEW emp_depat_vu AS SELECT employee_id, employee_name, Department_name, manager_id FROM employees e, departments d WHERE e.department_id = d.department_id;

    • E. 

      E. CREATE OR REPLACE VIEW emp_dept_vu AS SELECT employee_id, employee_ name, Department_name, manager _id FROM employees e, departments d WHERE e.department_id=d.department_id;

    • F. 

      F. You must remove the existing view first, and then run the CRATE VIEW command with a new column list to modify a view.

  • 7. 
     Examine the structure of the EMPLOYEES table: Column name              Data type                      Remarks EMPOYEE_ID               NUMBER                      NOT NULL, Primary Key EMP_NAME                  VARCHAR2 (30) JOB_ID                         VARCHAR2 (20)            NOT NULL SAL                              NUMBER MGR_ID                        NUMBER                      References EMPLOYEE_ID column DEPARTMENT_ID         NUMBER                      Foreign key to DEPARTMENT_ID column Of the DEPARTMENTS table   You need to create a view called EMP_VU that allows the users to insert rows through the view. Which SQL statement, when used to create the EMP_VU view, allows the users to insert rows?  
    • A. 

      A. CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, Department_id FROM employees WHERE mgr_id IN (102,120);

    • B. 

      B. CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, job_id, Department_id FROM employees WHERE mgr_id IN (102, 120);

    • C. 

      C. CREATE VIEW emp_Vu AS SELECT department_id, SUM(sal) TOTAL SAL FROM employees WHERE mgr_id IN (102, 120) GROUP BY department_id;

    • D. 

      D. CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, job_id, DISTINCT department_id FROM employees

  • 8. 
    Click the Exhibit button to examine the data of the EMPLOYEES table.   EMPLOYEES (EMPLOYEE_ID is the primary key. MGR_ID is the ID of managers and refers to the EMPLOYEE_ID)   EMPLOYEE_ID - EMP_NINE - DEPT_ID - MGR_ID - JOB_ID - SALARY   101 - Smith - 20 - 120 - SA_REP - 4000 102 - Martin - 10 - 105 - CLERK - 2500 103 - Chris - 20 - 120 - IT_ADMIN - 4200 104 - John - 30 - 108 - HR_CLERK - 2500 105 - Diana - 30 - 108 - HR_MGR - 5000 106 - Bryan - 40 - 110 - AD_ASST - 5000 108 - Jennifer - 30 - 110 - HR_DIR - 6500 110 - Bob - 40 - ** - EX_DIR - 8000 120 - Ravi - 20 - 110 - SA_DIR - 6500   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. 

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

    • B. 

      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. 

      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. 

      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. 

      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;

  • 9. 
     You need to calculate the total of all salaries in the accounting department. Which group function should you use?     
    • A. 

      A. MAX

    • B. 

      B. MIN

    • C. 

      C. SUM

    • D. 

      D. COUNT

    • E. 

      E. TOTAL

    • F. 

      F. LARGEST

  • 10. 
    Examine the data in the EMPLOYEES and EMP_HIST tables: EMPLOYEES EMPLOYEE_IDNAME   DEPT_ID          MGR_ID            JOB_ID SALARY 101 - Smith - 20 - 120 - SA_REP - 4000 102 - Martin - 10 - 105 - CLERK - 2500 103 - Chris - 20 - 120 - IT_ADMIN - 4200 104 - John - 30 - 108 - HR_CLERK - 2500 105 - Diana - 30 - 108 - IT_ADMIN - 5000 106 - Smith - 40 - 110 - AD_ASST - 3000 108 - Jennifer - 30 - 110 - HR_DIR - 6500 110 - Bob - 40 - ** - EX_DIR - 8000 120 - Ravi - 20 - 110 - SA_DIR - 6500   EMP_HIST EMPLOYEE_ID - NAME - JOB_ID - SALARY 101 - Smith - SA_CLERK - 2000 103 - Chris - IT_CLERK - 22 104 - John - HR_CLERK - 2000 106 - Smith - AD_ASST - 3000 108 - Jennifer - HR_MGR – 4500   The EMP_HIST table is updated at the end of every year. The employee ID, name, jobID, and salary of each existing employee are modified with the latest data. New employee details are added to the table. Which statement accomplishes this task?  
    • A. 

      A. UPDATE emp_hist SET employee_id, name, job_id, salary = (SELECT employee_id, name, job_id, salary FROM employees) WHERE employee_id IN (SELECT employee_id FROM employees);

    • B. 

      B. MERGE INTO emp_hist eh USING employees e ON (eh.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET eh.name = e.name, eh.job_id = e.job_id, eh.salary = e.salary WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.name, e.job_id id, e.salary);

    • C. 

      C. MERGE INTO emp_hist eh USING employees e ON (eh.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE emp_hist SET eh.name = e.name, eh.job_id = e.job_id, eh.salary = e.salary WHEN NOT MATCHED THEN INSERT INTO emp_hist VALUES (e.employee_id, e.name, e.job_id, e.salary);

    • D. 

      D. MERGE INTO emp_hist eh USING employees e WHEN MATCHED THEN UPDATE emp_hist SET eh.name = e.name, eh.job_id = e.job_id, eh.salary = e.salary WHEN NOT MATCHED THEN INSERT INTO emp_hist VALUES (e.employee_id, e.name, e.job_id, e.salary);

  • 11. 
    The CUSTOMERS table has these columns:   CUSTOMER_ID             NUMBER (4)                 NOT NULL CUSTOMER_NAME      VARCHAR2 (100)          NOT NULL STREET_ADDRESS      VARCHAR2 (150) CITY_ADDRESS           VARCHAR2 (50) STATE_ADDRESS        VARCHAR2 (50) PROVINCE_ADDRESS VARCHAR2 (50) COUNTRY_ADDRESS   VARCHAR2 (50) POSTE_CODE              VARCHAR2 (12) CUSTOMER_PHONE    VARCHAR2 (20)   THE CUSTOMER_ID column is the primary key for the table which two statements find the number of customer? (Choose two.)  
    • A. 

      A. SELECT TOTAL (*) FROM customers;

    • B. 

      B. SELECT COUNT (*) FROM customers;

    • C. 

      C. SELECT TOTAL (customer_id) FROM customer;

    • D. 

      D. SELECT COUNT(customer_id) FROM customer;

    • E. 

      E. SELECT COUNT(customers) FROM customers;

  • 12. 
    Examine the structure of the EMPLOYEES table: EMPLOYEE_ID                         NUMBER                      NOT NULL EMP_ID                                    VARCHAR2 (30) JOB_ID                                     VARCHAR2 (20)            DEFAULT 'SA_REP' SAL                                          NUMBER COMM_PCT                              NUMBER MGR_ID                                    NUMBER DEPARTMENT_ID                     NUMBER   You need to update the records of employees 103 and 115. The UPDATE statement you specify should update the rows with the values specified below: JOB_ID: Default value specified for this column definition SAL: maximum salary earned for the job ID SA_REP COMM_PCT: Default value is specified for the column, the value should be NULL DEPARTMENT_ID: Supplied by the user during run time through substitution variable Which UPDATE statement meets the requirements?  
    • A. 

      A. UPDATE employees SET job_id=DEFAULT AND Sal=(SELECT MAX(sal) FROM employees WHERE job_id='SA_REP' AND comm_pct=DEFALUT AND department_id =&did WHERE employee_id IN (103, 115),

    • B. 

      B. UPDATE employees SET job_id = DEFAULT AND Sal = MAX(sal) AND comm_pct = DEFAULT OR NULL AND department _id = & did WHERE employee_id IN (103,115) AND job_id = 'SA_REP'

    • C. 

      C. UPDATE employees SET job_id = DEFAULT Sal = (SELECT MAX (sal) FROM employees WHERE job_id = 'SA_REP') comm_pct = DEFAULT, department _id = &did WHERE employee_id IN (103,115)

    • D. 

      D. UPDATE employees SET job_id = DEFAULT sal = MAX (sal) comm_pct = DEFAULT department_id = &did WHERE employee_id IN (103,115) AND job_id = 'SA_REP' E. UPDATE employees SET job_id = DEFAULT Sal = (SELECT MAX(sal) FROM employees WHERE job_id = 'SA_REP') comm_pct = DEFAULT OR NULL, department_id = &did WHERE employee_id IN (103,115)

  • 13. 
    154. Examine the structure of the EMPLOYEES table: Column name              Data type                      Remarks EMPOYEE_ID               NUMBER                      NOT NULL, Primary Key EMP_NAME                  VARCHAR2 (30) JOB_ID                         VARCHAR2 (20)            NOT NULL SAL                              NUMBER MGR_ID                        NUMBER                      References EMPLOYEE_ID column DEPARTMENT_ID         NUMBER                      Foreign key to DEPARTMENT_ID column Of the DEPARTMENTS table   You need to create a view called EMP_VU that allows the users to insert rows through the view. Which SQL statement, when used to create the EMP_VU view, allows the users to insert rows?  
    • A. 

      E. CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, Department_id FROM employees WHERE mgr_id IN (102,120);

    • B. 

      F. CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, job_id, Department_id FROM employees WHERE mgr_id IN (102, 120);

    • C. 

      G. CREATE VIEW emp_Vu AS SELECT department_id, SUM(sal) TOTAL SAL FROM employees WHERE mgr_id IN (102, 120) GROUP BY department_id;

    • D. 

      H. CREATE VIEW emp_Vu AS SELECT employee_id, emp_name, job_id, DISTINCT department_id FROM employees

  • 14. 
    The EMPLOYEES table contains these columns: LAST_NAME                VARCHAR2 (25) SALARY                       NUMBER (6, 2) COMMISSION_PCT      NUMBER (6)   You need to write a query that will produce these results: 1. Display the salary multiplied by the commission_pct 2. Exclude employees with a zero commission_pct 3. Display a zero for employees with a null commission value   Evaluate the SQL statement: SELECT LAST_NAME, SALARY * COMMISSION_PCT FROM EMPLOYEES WHERE COMMISSION_PCT IS NOT NULL;   What does the statement provide?    
    • A. 

      A. all of the desired results

    • B. 

      B. two of the desired results

    • C. 

      C. one of the desired results

    • D. 

      D. an error statement

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

      E. create groups of data

    • B. 

      F. sort data in a specific order

    • C. 

      G. convert data to a different format

    • D. 

      H. retrieve data based on an unknown condition

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

      A. WHERE

    • B. 

      B. HAVING

    • C. 

      C. RESTRICT

    • D. 

      D. GROUP BY

    • E. 

      E. ORDER BY

  • 17. 
    The EMP table has these columns: ENAME                        VARCHAR2 (35) SALARY                       NUMBER (8, 2) HIRE_DATE                  DATE Management wants a list of names of employees who have been with the company for more than five yeas. Which SQL statement displays the required results?    
    • A. 

      A. SELECT ENAME FROM EMP WHERE SYSDATE-HIRE_DATE>5

    • B. 

      B. SELECT ENAME FROM EMP WHERE HIRE_DATE-SYSDATE > 5

    • C. 

      C. SELECT ENAME FROM EMP WHERE (SYSDATE-HIRE_DATE)/365 > 5

    • D. 

      D. SELECT ENAME FROM EMP WHERE (SYSDATE-HIRE_DATE)* 365 > 5

  • 18. 
      122. Management has asked you to calculate the value 12* salary* commission_pct for all the employees in the EMP table. The EMP table contains these columns:   LAST NAME                 VARCHAR2 (35)            NOT NULL SALARY                       NUMBER (9, 2) NOT NULL COMMISSION_PCT      NUMBER (4, 2)   Which statement ensures that a value is displayed in the calculated column for all employees?     
    • A. 

      A. SELECT last_name, 12 * salary* commission_pct FROM emp;

    • B. 

      B. SELECT last_name, 12 * salary* (commission_pct,0) FROM emp;

    • C. 

      C. SELECT last_name, 12 * salary* (nvl(commission_pct,0) FROM emp;

    • D. 

      D. SELECT last_name, 12 * salary* (decode(commission_pct,0)) FROM emp;

  • 19. 
     Mark for review. Which two are true about aggregate functions? (Choose two.)     
    • A. 

      A. You can use aggregate functions in any clause of a SELECT statement.

    • B. 

      B. You can use aggregate functions only in the column list of the SELECT clause and in the WHERE clause of a SELECT statement.

    • C. 

      C. You can mix single row columns with aggregate functions in the column list of a SELECT statement by grouping on the single row columns.

    • D. 

      D. You can pass column names, expressions, constants, or functions as parameters to an aggregate function.

    • E. 

      E. You can use aggregate functions on a table, only by grouping the whole table as one single group.

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

      A. string

    • B. 

      B. character

    • C. 

      C. integer

    • D. 

      E. numeric

    • E. 

      F. translation

    • F. 

      G. date

    • G. 

      H. conversion

  • 21. 
     Evaluate this SQL statement: SELECT e.EMPLOYEE_ID, e.LAST_NAME, e.DEPARTMENT_ID, d.DEPARTMENT_NAME FROM EMP e, DEPARTMENT d WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID; In the statement, which capabilities of a SELECT statement are performed?    
    • A. 

      A. Selection, projection, join

    • B. 

      B. Difference, projection, join

    • C. 

      C. Selection, intersection, join

    • D. 

      D. Intersection, projection, join

    • E. 

      E. Difference, projection, product

  • 22. 
    Mark for review Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables: EMPLOYEES EMPLOYEE_ID                         NUMBER                      Primary Key FIRST_NAME                           VARCHAR2 (25) LAST_NAME                            VARCHAR2 (25) HIRE_DATE                              DATE   NEW_EMPLOYEES EMPLOYEE_ID                         NUMBER                      Primary Key NAME                                       VARCHAR2 (60) Which DELETE statement is valid?     
    • A. 

      A. DELETE FROM employees WHERE employee_id = (SELECT employee_id FROM employees);

    • B. 

      B. DELETE * FROM employees WHERE employee_id = (SELECT employee_id FROM new_employees);

    • C. 

      C. DELETE FROM employees WHERE employee_id IN (SELECT employee_id FROM new_employees WHERE name ='Carrey');

    • D. 

      D. DELETE * FROM employees WHERE employee_id IN (SELECT employee_id FROM new_employees WHERE last_name ='Carrey');

  • 23. 
     Mark for review. You need to design a student registration database that contains several tables storing academic information.   The STUDENTS table stores information about a student. The STUDENT_GRADES table stores information about the student's grades. Both of the tables have a column named STUDENT_ID. The STUDENT_ID column in the STUDENTS table is a primary key.   You need to create a foreign key on the STUDENT_ID column of the STUDENT_GRADES table that points to the STUDENT_ID column of the STUDENTS table. Which statement creates the foreign key?     
    • A. 

      A. CREATE TABLE student_grades (student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT student_id_fk REFERENCES (student_id) FOREIGN KEY students(student_id));

    • B. 

      B. CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));

    • C. 

      C. CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT FOREIGN KEY (student_id) REFERENCES students(student_id));

    • D. 

      D. CREATE TABLE student_grades(student_id NUMBER(12),semester_end DATE, gpa NUMBER(4,3), CONSTRAINT student_id_fk FOREIGN KEY (student_id) REFERENCES students(student_id));

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

      A. SELECT dear customer, customer_name, FROM customers;

    • B. 

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

    • C. 

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

    • D. 

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

  • 25. 
     What is true about sequences?     
    • A. 

      A. Once created, a sequence belongs to a specific schema.

    • B. 

      B. Once created, a sequence is linked to a specific table.

    • C. 

      C. Once created, a sequence is automatically available to all users.

    • D. 

      D. Only the DBA can control which sequence is used by a certain table.