Oracle AcademyTest: Final Exam Semester 1 Section 16

Oracle AcademyTest: Final Exam Semester 1  Section 16 - Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

oracle academy test

Semester 1 Final Exam covers Sections 11-17 of Database Design.  


 Section 16 
 (Answer all questions in this section) 
      
11.  The PLAYERS table contains these columns: 
PLAYER_ID NUMBER(9) 
LAST_NAME VARCHAR2(20) 
FIRST_NAME VARCHAR2 (20) 
TEAM_ID NUMBER (4) 
MANAGER_ID NUMBER (9) 
POSITION_ID NUMBER (4) 

Which SELECT statement should you use if you want to display unique combinations of the TEAM_ID and MANAGER_ID columns? 
 Mark for Review 
(1) Points 
      
    SELECT * FROM players; 
  
    SELECT team_id, manager_id FROM players; 
  
    SELECT DISTINCT team_id, manager_id FROM players; (*) 
  
    SELECT team_id, DISTINCT manager_id FROM players; 
  
    SELECT team_id, manager_id DISTINCT FROM players; 
  
      
      Incorrect. See Section 16 Lesson 2.  
  
      
12.  Evaluate this SELECT statement: 
SELECT * 
FROM employees
WHERE department_id IN(10, 20, 30) 
AND salary > 20000; 

Which values would cause the logical condition to return TRUE?
 Mark for Review 
(1) Points 
      
    DEPARTMENT_ID = 10 and SALARY = 20000 
  
    DEPARTMENT_ID = 20 and SALARY = 20000 
  
    DEPARTMENT_ID = null and SALARY = 20001 
  
    DEPARTMENT_ID = 10 and SALARY = 20001 (*) 
  
      
      Incorrect. See Section 16 Lesson 2.  
  
      
13.  Which of the following commands will display the last name concatenated with the job ID from the employees table, separated by a comma and space, and label the resulting column "Employee and Title"?  Mark for Review 
(1) Points 
      
    SELECT " last name" ||', '|| "job_id" + "Employee and Title" FROM employees; 
  
    SELECT last_name||', '|| job_id "Employee and Title" FROM employees; (*) 
  
    SELECT " last name" ||', '|| "job_id" + "Employee and Title" FROM emp; 
  
    SELECT last_name||","|| job_id "Employee and Title" FROM employees; 
  
      
      Incorrect. See Section 16 Lesson 2.  
  
      
14.  What does the DISTINCT keyword do when it is used in a SELECT clause?  Mark for Review 
(1) Points 
      
    Hides NULL values 
  
    Eliminates all unique values and compares values 
  
    Eliminates duplicate rows in the result (*) 
  
    Eliminates only unique rows in the result 
  
      
      Incorrect. See Section 16 Lesson 1.  
  
      
 15.  When using the LIKE condition, which symbol represents any sequence of none, one or more characters?  Mark for Review 
(1) Points 
      
    _ 
  
    % (*) 
  
    # 
  
    & 
  
      
      Incorrect. See Section 16 Lesson 1.  
  
      
16.  Which of the following elements cannot be included in a WHERE clause?  Mark for Review 
(1) Points 
      
    A column alias (*) 
  
    A column name 
  
    A comparison condition 
  
    A constant 
  
      
      Correct.  
  
      
17.  Which comparison operator searches for a specified character pattern?  Mark for Review 
(1) Points 
      
    IN 
  
    LIKE (*) 
  
    BETWEEN...AND... 
  
    IS NULL 
  
      
      Incorrect. See Section 16 Lesson 1.  
  
      
  18.  Where in a SQL statement can you not use arithmetic operators?  Mark for Review 
(1) Points 
      
    SELECT 
  
    FROM (*) 
  
    WHERE 
  
    NONE 
  
      
      Incorrect. See Section 16 Lesson 1.  
  
      
  19.  Which comparison condition would you use to select rows that match a character pattern?  Mark for Review 
(1) Points 
      
    IN 
  
    LIKE (*) 
  
    ALMOST 
  
    SIMILAR 
  
      
      Incorrect. See Section 16 Lesson 1.  
  
      
 20.  The Concatenation Operator does which of the following?  Mark for Review 
(1) Points 
      
    Links rows of data together inside the database. 
  
    Links two or more columns or literals to form a single output column (*) 
  
    Is represented by the asterisk (*) symbol 
  
    Separates columns. 
  

Oracle Academy Test: Final Exam Semester 2 - Part I (1-10)

Oracle Academy my learning

Oracle Academy Test: Final Exam Semester 2 - Part I (1-10)

Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 8
(Answer all questions in this section)

Part I of the Semester 2 Final Exam covers Sections 8-9 of Database Programming with SQL.

1. Which of the following SQL statements will create a table called Birthdays with three columns for storing employee number, name and date of birth? 
(1) Points

CREATE table BIRTHDAYS (EMPNO, EMPNAME, BIRTHDATE);

CREATE table BIRTHDAYS (employee number, name, date of birth);

CREATE TABLE Birthdays (Empno NUMBER, Empname CHAR(20), Birthdate DATE); (*)

CREATE TABLE Birthdays (Empno NUMBER, Empname CHAR(20), Date of Birth DATE);


2. Which statement about table and column names is true? 

(1) Points

Table and column names must begin with a letter. (*)

Table and column names can begin with a letter or a number.

Table and column names cannot include special characters.

If any character other than letters or numbers is used in a table or column name, the name must be enclosed in double quotation marks.


3. Evaluate this CREATE TABLE statement:

1. CREATE TABLE customer#1 (

2. cust_1 NUMBER(9),

3. sales$ NUMBER(9),

4. 2date DATE DEFAULT SYSDATE);

Which line of this statement will cause an error? 

(1) Points
1

2

3

4 (*)


4. You are creating the EMPLOYEES table. This table should contain the COMMISSION_PCT column and use a value of 10 percent if no commission value is provided when a record is inserted. Which line should you include in the CREATE TABLE statement to accomplish this task?
(1) Points

commission_pct NUMBER(4,2) DEFAULT 0.10 (*)

commission_pct NUMBER(4,2) DEFAULT = 0.10

commission_pct NUMBER(4,2) DEFAULT (0.10)

commission_pct NUMBER(4,2) (DEFAULT, 0.10)


5. Which CREATE TABLE statement will fail?

(1) Points

CREATE TABLE date_1 (date_1 DATE);

CREATE TABLE date (date_id NUMBER(9)); (*)

CREATE TABLE time (time_id NUMBER(9));

CREATE TABLE time_date (time NUMBER(9));

6. Which column name is valid? 
(1) Points

1NUMBER

NUMBER

NUMBER_1$ (*)

1_NUMBER#


7. You are designing a table for the Human Resources department. This table must include a column that contains each employee's hire date. Which data type should you specify for this column? 

(1) Points

CHAR

DATE (*)

TIMESTAMP

INTERVAL YEAR TO MONTH


8. Which data types stores variable-length character data? (Select two).
(1) Points
(Choose all correct answers)

CHAR

NCHAR

CLOB (*)

VARCHAR2 (*)


9. You need to store the HIRE_DATE value with a time zone displacement value and allow data to be returned in the user's local session time zone. Which data type should you use? 
(1) Points

DATETIME

TIMESTAMP

TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH LOCAL TIME ZONE (*)


10. The ELEMENTS column is defined as:
NUMBER(6,4)
How many digits to the right of the decimal point are allowed for the ELEMENTS column? 
(1) Points

Zero

Two

Four (*)

Six

Oracle Academy Test: Final Exam Semester 2 - Part I (1-10)