Section 7 Quiz Oracle Database Programming with SQL

1.         Evaluate this SQL statement:
SELECT e.employee_id, e.last_name, e.first_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id AND employees.department_id > 5000
ORDER BY 4;

Which clause contains a syntax error?

 Mark for Review
(1) Points

            FROM employees e, departments d
            SELECT e.employee_id, e.last_name, e.first_name, d.department_name
            ORDER BY 4;
            WHERE e.department_id = d.department_id
            AND employees.department_id > 5000 (*)

2.         When must column names be prefixed by table names in join syntax?           Mark for Review
(1) Points

            When the more than two tables participate in the join
            Never
            When the same column name appears in more than one table of the query (*)
            Only when query speed and database performance is a concern

3.         If table A has 10 rows and table B has 5 rows, how many rows will be returned if you perform a cartesian join on those two tables?       Mark for Review
(1) Points

            5
            15
            10
            50 (*)

4.         You need to provide a list of the first and last names of all employees who work in the Sales department who earned a bonus and had sales over $50,000. The company president would like the sales listed starting with the highest amount first. The EMPLOYEES table and the SALES_DEPT table contain the following columns:
EMPLOYEES
EMP_ID NUMBER(10) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
DEPTARTMENT_ID VARCHAR2(20)
HIRE_DATE DATE
SALARY NUMBER(10)

SALES_DEPT
SALES_ID NUMBER(10) PRIMARY KEY
SALES NUMBER(20)
QUOTA NUMBER(20)
MANAGER VARCHAR2(30)
BONUS NUMBER(10)
EMPLOYEE_ID NUMBER(10) FOREIGN KEY

Which SELECT statement will accomplish this task?

 Mark for Review
(1) Points

            SELECT e.employee_id, e.last_name, e.first_name, s.employee_id, s.bonus, s. sales
WHERE e.employee_id = s.employee_id
FROM employees e, sales_dept s AND s.bonus IS NOT NULL AND sales > 50000
ORDER BY sales DESC;

            SELECT e.employee_id, e.last_name, e.first_name, s.employee_id, s.bonus, s.sales
FROM employees e, sales_dept s
ORDER BY sales DESC
WHERE e.employee_id = s.employee_id AND sales > 50000 AND s.bonus IS NOT NULL;

            SELECT e.employee_id, e.last_name, e.first_name, s.employee_id, s.bonus, s. sales
FROM employees e, sales_dept s
WHERE e.employee_id = s.employee_id AND s.bonus IS NOT NULL AND sales > 50000
ORDER BY sales DESC;
(*)

            SELECT e.employee_id, e.last_name, e.first_name, s.employee_id, s.bonus, s. sales
ORDER BY sales DESC
FROM employees e, sales_dept s
WHERE e.employee_id = s.employee_id AND s.bonus IS NOT NULL AND sales > 50000;

5.         When joining 3 tables in a SELECT statement, how many join conditions are needed in the WHERE clause? Mark for Review
(1) Points

            3
            0
            2 (*)
            1

6.         What is produced when a join condition is not specified in a multiple-table query using Oracle proprietary Join syntax?          Mark for Review
(1) Points

            An equijoin
            A Cartesian product (*)
            A self-join
            An outer join

7.         The CUSTOMERS and SALES tables contain these columns:
CUSTOMERS
CUST_ID NUMBER(10) PRIMARY KEY
COMPANY VARCHAR2(30)
LOCATION VARCHAR2(20)

SALES
SALES_ID NUMBER(5) PRIMARY KEY
CUST_ID NUMBER(10) FOREIGN KEY
TOTAL_SALES NUMBER(30)

Which SELECT statement will return the customer ID, the company and the total sales?

 Mark for Review
(1) Points

            SELECT c.cust_id, c.company, s.total_sales
FROM customers c, sales s
WHERE c.cust_id = s.cust_id;
(*)

            SELECT cust_id, company, total_sales
FROM customers, sales
WHERE cust_id = cust_id;

            SELECT cust_id, company, total_sales
FROM customers c, sales s
WHERE c.cust_id = s.cust_id;

            SELECT c.cust_id, c.company, s.total_sales
FROM customers c, sales s
WHERE c.cust_id = s.cust_id (+);

8.         You have the following EMPLOYEES table:
EMPLOYEE_ID NUMBER(5) NOT NULL PRIMARY KEY
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
ADDRESS VARCHAR2(35)
CITY VARCHAR2(25)
STATE VARCHAR2(2)
ZIP NUMBER(9)
TELEPHONE NUMBER(10)
DEPARTMENT_ID NUMBER(5) NOT NULL FOREIGN KEY

The BONUS table includes the following columns:

BONUS_ID NUMBER(5) NOT NULL PRIMARY KEY
ANNUAL_SALARY NUMBER(10)
BONUS_PCT NUMBER(3, 2)
EMPLOYEE_ID VARCHAR2(5) NOT NULL FOREIGN KEY

You want to determine the amount of each employee's bonus as a calculation of salary times bonus. Which of the following queries should you issue?

 Mark for Review
(1) Points

            SELECT e.first_name, e.last_name, b.annual_salary * b. bonus_pct
FROM employees e, bonus b
WHERE e.employee_id = b.employee_id;
(*)

            SELECT e.first_name, e.last_name, b.annual_salary, b. bonus_pct
FROM employees e, bonus b
WHERE e.employee_id = b.employee_id;

            SELECT first_name, last_name, annual_salary * bonus_pct
FROM employees, bonus NATURAL JOIN;

            SELECT e.first_name, e.last_name, b.annual_salary, b. bonus_pct
FROM employees, bonus
WHERE e.employee_id = b.employee_id;

9.         The ID column in the CLIENT table that corresponds to the CLIENT_ID column of the ORDER table contains null values for rows that need to be displayed. Which type of join should you use to display the data?            Mark for Review
(1) Points

            Self join
            Nonequi-Join
            Outer join (*)
            Equijoin

10.       Evaluate this SELECT statement:
SELECT p.player_id, m.last_name, m.first_name, t.team_name
FROM player p
LEFT OUTER JOIN player m ON (p.manager_id = m.player_id)
LEFT OUTER JOIN team t ON (p.team_id = t.team_id);

Which join is evaluated first?

 Mark for Review
(1) Points

            The join between the player table and the team table on MANAGER_ID
            The self-join of the player table (*)
            The join between the player table and the team table on TEAM_ID
            The join between the player table and the team table on PLAYER_ID

11.       Using Oracle Proprietary join syntax, which two operators can be used in an outer join condition using the outer join operator (+)?      Mark for Review
(1) Points

            OR and =
            AND and = (*)
            IN and =
            BETWEEN...AND... and IN

12.       You need to join the EMPLOYEES table and the SCHEDULES table, but the two tables do not have any corresponding columns. Which type of join will you create?  Mark for Review
(1) Points

            A full outer join
            It is not possible to join these two tables.
            A non-equijoin (*)
            An equijoin

13.       Using Oracle Proprietary join syntax, which operator would you use after one of the column names in the WHERE clause when creating an outer join?   Mark for Review
(1) Points

            *
            (+) (*)
            =
            +

14.       Which statement about outer joins is true?       Mark for Review
(1) Points

            The OR operator cannot be used to link outer join conditions. (*)
            The tables must be aliased.
            Outer joins are always evaluated before other types of joins in the query.
            The FULL, RIGHT, or LEFT keyword must be included.

15.       The following is a valid outer join statement:
SELECT c.country_name, d.department_name
FROM countries c, departments d
WHERE c.country_id (+) = d.country_id (+)

True or False?

 Mark for Review
(1) Points

            True
            False (*)

1.         When joining 3 tables in a SELECT statement, how many join conditions are needed in the WHERE clause? Mark for Review
(1) Points

            0
            3
            1
            2 (*)

2.         What is the result of a query that selects from two tables but includes no join condition?       Mark for Review
(1) Points

            A syntax error
            A Cartesian product (*)
            A selection of matched rows from both tables
            A selection of rows from the first table only

3.         Which statement about the join syntax of an Oracle Proprietary join syntax SELECT statement is true?            Mark for Review
(1) Points

            The FROM clause represents the join criteria.
            The JOIN keyword must be included.
            The WHERE clause represents the join criteria. (*)
            The ON keyword must be included.

4.         You have two tables named EMPLOYEES and SALES. You want to identify the sales representatives who have generated at least $100,000 in revenue.
Which query should you issue?           Mark for Review
(1) Points

            SELECT first_name, last_name, sales
FROM employees e, sales s
WHERE e.employee_id = s.employee_id AND revenue > 100000;

            SELECT e.first_name, e.last_name, s.sales
FROM employees, sales
WHERE e.employee_id = s.employee_id AND revenue >= 100000;

            SELECT e.first_name, e.last_name, s.sales
FROM employees e, sales s
WHERE e.employee_id = s.employee_id AND revenue > 100000;

            SELECT e.first_name, e.last_name, s.sales
FROM employees e, sales s
WHERE e.employee_id = s.employee_id AND revenue >= 100000;
(*)

5.         If table A has 10 rows and table B has 5 rows, how many rows will be returned if you perform a equi-join on those two tables?         Mark for Review
(1) Points

            It depends on how many rows have matching data in each of the two tables. (*)
            5
            50
            10

6.         You have the following EMPLOYEES table:
EMPLOYEE_ID NUMBER(5) NOT NULL PRIMARY KEY
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
ADDRESS VARCHAR2(35)
CITY VARCHAR2(25)
STATE VARCHAR2(2)
ZIP NUMBER(9)
TELEPHONE NUMBER(10)
DEPARTMENT_ID NUMBER(5) NOT NULL FOREIGN KEY

The BONUS table includes the following columns:

BONUS_ID NUMBER(5) NOT NULL PRIMARY KEY
ANNUAL_SALARY NUMBER(10)
BONUS_PCT NUMBER(3, 2)
EMPLOYEE_ID VARCHAR2(5) NOT NULL FOREIGN KEY

You want to determine the amount of each employee's bonus as a calculation of salary times bonus. Which of the following queries should you issue?

 Mark for Review
(1) Points

            SELECT first_name, last_name, annual_salary * bonus_pct
FROM employees, bonus NATURAL JOIN;

            SELECT e.first_name, e.last_name, b.annual_salary, b. bonus_pct
FROM employees, bonus
WHERE e.employee_id = b.employee_id;

            SELECT e.first_name, e.last_name, b.annual_salary, b. bonus_pct
FROM employees e, bonus b
WHERE e.employee_id = b.employee_id;

            SELECT e.first_name, e.last_name, b.annual_salary * b. bonus_pct
FROM employees e, bonus b
WHERE e.employee_id = b.employee_id;
(*)

7.         The PATIENTS and DOCTORS tables contain these columns:
PATIENTS
PATIENT_ID NUMBER(9)
LAST_NAME VARCHAR2 (20)
FIRST_NAME VARCHAR2 (20)

DOCTORS
DOCTOR_ID NUMBER(9)
LAST_NAME VARCHAR2 (20)
FIRST_NAME VARCHAR2 (20)

You issue this statement:
SELECT patient_id, doctor_id
FROM patients, doctors;

Which result will this statement provide?

 Mark for Review
(1) Points

            A report containing all possible combinations of the PATIENT_ID and DOCTOR_ID values (*)
            A report containing each patient's id value and his doctor's id value
            A report with NO duplicate PATIENT_ID or DOCTOR_ID values
            A syntax error

8.         You need to provide a list of the first and last names of all employees who work in the Sales department who earned a bonus and had sales over $50,000. The company president would like the sales listed starting with the highest amount first. The EMPLOYEES table and the SALES_DEPT table contain the following columns:
EMPLOYEES
EMP_ID NUMBER(10) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
DEPTARTMENT_ID VARCHAR2(20)
HIRE_DATE DATE
SALARY NUMBER(10)

SALES_DEPT
SALES_ID NUMBER(10) PRIMARY KEY
SALES NUMBER(20)
QUOTA NUMBER(20)
MANAGER VARCHAR2(30)
BONUS NUMBER(10)
EMPLOYEE_ID NUMBER(10) FOREIGN KEY

Which SELECT statement will accomplish this task?

 Mark for Review
(1) Points

            SELECT e.employee_id, e.last_name, e.first_name, s.employee_id, s.bonus, s. sales
WHERE e.employee_id = s.employee_id
FROM employees e, sales_dept s AND s.bonus IS NOT NULL AND sales > 50000
ORDER BY sales DESC;

            SELECT e.employee_id, e.last_name, e.first_name, s.employee_id, s.bonus, s. sales
ORDER BY sales DESC
FROM employees e, sales_dept s
WHERE e.employee_id = s.employee_id AND s.bonus IS NOT NULL AND sales > 50000;

            SELECT e.employee_id, e.last_name, e.first_name, s.employee_id, s.bonus, s.sales
FROM employees e, sales_dept s
ORDER BY sales DESC
WHERE e.employee_id = s.employee_id AND sales > 50000 AND s.bonus IS NOT NULL;

            SELECT e.employee_id, e.last_name, e.first_name, s.employee_id, s.bonus, s. sales
FROM employees e, sales_dept s
WHERE e.employee_id = s.employee_id AND s.bonus IS NOT NULL AND sales > 50000
ORDER BY sales DESC;
(*)

9.         Which statement about joining tables with a non-equijoin is false?     Mark for Review
(1) Points

            A WHERE clause must specify a column in one table that is compared to a column in the second table (*)

            The number of join conditions required is always one less than the number of tables being joined

            The columns being joined must have compatible data types
            None of the above

10.       The ID column in the CLIENT table that corresponds to the CLIENT_ID column of the ORDER table contains null values for rows that need to be displayed. Which type of join should you use to display the data?            Mark for Review
(1) Points

            Self join
            Nonequi-Join
            Equijoin
            Outer join (*)
2.         When must column names be prefixed by table names in join syntax?           Mark for Review
(1) Points

            Only when query speed and database performance is a concern
            When the more than two tables participate in the join
            When the same column name appears in more than one table of the query (*)
            Never
8.         The CUSTOMERS and SALES tables contain these columns:
CUSTOMERS
CUST_ID NUMBER(10) PRIMARY KEY
COMPANY VARCHAR2(30)
LOCATION VARCHAR2(20)

SALES
SALES_ID NUMBER(5) PRIMARY KEY
CUST_ID NUMBER(10) FOREIGN KEY
TOTAL_SALES NUMBER(30)

Which SELECT statement will return the customer ID, the company and the total sales?

 Mark for Review
(1) Points

            SELECT cust_id, company, total_sales
FROM customers c, sales s
WHERE c.cust_id = s.cust_id;

            SELECT c.cust_id, c.company, s.total_sales
FROM customers c, sales s
WHERE c.cust_id = s.cust_id;
(*)
                                 
            SELECT c.cust_id, c.company, s.total_sales
FROM customers c, sales s
WHERE c.cust_id = s.cust_id (+);

            SELECT cust_id, company, total_sales
FROM customers, sales
WHERE cust_id = cust_id;

9.         You need to join the EMPLOYEES table and the SCHEDULES table, but the two tables do not have any corresponding columns. Which type of join will you create?  Mark for Review
(1) Points

            A non-equijoin (*)
            It is not possible to join these two tables.
            An equijoin
            A full outer join

10.       Using Oracle Proprietary join syntax, which operator would you use after one of the column names in the WHERE clause when creating an outer join?   Mark for Review
(1) Points

            (+) (*)
            +
            *
            =

11.       The EMPLOYEE_ID column in the EMPLOYEES table corresponds to the EMPLOYEE_ID column of the ORDERS table.
The EMPLOYEE_ID column in the ORDERS table contains null values for rows that you need to display.
Which type of join should you use to display the data?           Mark for Review
(1) Points

            Natural join
            Self-join
            Equijoin
            Outer join (*)

12.       To perform a valid outer join between DEPARMENTS and EMPLOYEES to list departments without employees, select the correct WHERE clause for the following select statement:
SELECT d.department_name, e.last_name
FROM employees e, departments d
WHERE

 Mark for Review
(1) Points

            e.department_id = d.department_id(+)
            e.department_id(+) = d.department_id(+)
            e.department_id = d.department_id
            e.department_id(+) = d.department_id (*)

11.       Using Oracle Proprietary join syntax, which two operators can be used in an outer join condition using the outer join operator (+)?      Mark for Review
(1) Points

            IN and =
            BETWEEN...AND... and IN
            AND and = (*)
            OR and =

12.       To perform a valid outer join between DEPARMENTS and EMPLOYEES to list departments without employees, select the correct WHERE clause for the following select statement:
SELECT d.department_name, e.last_name
FROM employees e, departments d
WHERE

 Mark for Review
(1) Points

            e.department_id = d.department_id
            e.department_id(+) = d.department_id(+)
            e.department_id = d.department_id(+)
            e.department_id(+) = d.department_id (*)

13.       The EMPLOYEE_ID column in the EMPLOYEES table corresponds to the EMPLOYEE_ID column of the ORDERS table.
The EMPLOYEE_ID column in the ORDERS table contains null values for rows that you need to display.
Which type of join should you use to display the data?           Mark for Review
(1) Points

            Natural join
            Self-join
            Equijoin
            Outer join (*)

14.       Which operator is typically used in a nonequijoin?     Mark for Review
(1) Points

            >=, <=, or BETWEEN ...AND (*)
            NOT
            *
            OR
            IN

15.       Using Oracle Proprietary join syntax, which operator would you use after one of the column names in the WHERE clause when creating an outer join?   Mark for Review
(1) Points

            (+) (*)
            =
            +
            *

1.         Will the following statement work?
SELECT department_name, last_name
FROM employees, departments
WHERE department_id = department_id;

 Mark for Review
(1) Points

            No, Oracle will return a Column Ambiguously Defined error. (*)
            Yes, Oracle will resolve which department_id colum comes from which table.
            Yes, there are no syntax errors in that statement
            No, Oracle will not allow joins in the WHERE clause


13.       Nonequijoins are normally used with which of the following? (Choose two)  Mark for Review
(1) Points
                                    (Choose all correct answers)  

            Ranges of numbers (*)
            Ranges of rowids
            Ranges of text
            ranges of columns
            Ranges of dates (*)

14.       Using Oracle Proprietary join syntax, which two operators can be used in an outer join condition using the outer join operator (+)?      Mark for Review
(1) Points

            BETWEEN...AND... and IN
            IN and =
            AND and = (*)
            OR and =

15.       Which statement about joining tables with a non-equijoin is false?     Mark for Review
(1) Points

            A WHERE clause must specify a column in one table that is compared to a column in the second table (*)

            The number of join conditions required is always one less than the number of tables being joined

            The columns being joined must have compatible data types
            None of the above


3.         Oracle proprietary JOINS can use the WHERE clause for conditions other than the join-condition. True or False?          Mark for Review
(1) Points

            True (*)
            False

4.         What is the minimum number of join conditions required to join 5 tables together?    Mark for Review
(1) Points

            4 (*)
            3
            5
            One more than the number of tables

5.         What happens when you create a Cartesian product?  Mark for Review
(1) Points

            All rows from one table are joined to all rows of another table (*)
            The table is joined to another equal table
            All rows that do not match in the WHERE clause are displayed
            The table is joined to itself, one column to the next column, exhausting all possibilities

6.         Which statement about the join syntax of an Oracle Proprietary join syntax SELECT statement is true?            Mark for Review
(1) Points

            The FROM clause represents the join criteria.
            The WHERE clause represents the join criteria. (*)
            The ON keyword must be included.
            The JOIN keyword must be included.

7.         What is the result of a query that selects from two tables but includes no join condition?       Mark for Review
(1) Points

            A selection of matched rows from both tables
            A selection of rows from the first table only
            A Cartesian product (*)
            A syntax error