Halaman

Thursday, August 8, 2019

Answer Sections 7 Quiz Database Programming with SQL 2019 Learner - English


Section 7


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?                                     
                                               
                Nonequi-Join

                                               
                Self join

                                               
                Equijoin

                                               
                Outer join (*)

                                                                               
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?
                                                                               
                                               
                The self-join of the player table (*)

                                               
                The join between the player table and the team table on MANAGER_ID

                                               
                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


Which statement about joining tables with a non-equijoin is false?   

                                               
                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

The following statement is an example of a nonequi-join?
SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary
    BETWEEN j.lowest_sal AND j.highest_sal;

True or False?
                                                            
                                               
                True (*)

                                               
                False

                                                                               
Which operator is typically used in a nonequijoin?     

                                               
                IN

                                               
                NOT

                                               
                *

                                               
                OR

                                               
                >=, <=, or BETWEEN ...AND (*)

Which symbol is used to perform an outer join?        
                                                                               
                                               
                *

                                               
                (+) (*)

                                               
                #

                                               
                ||

                                                                               
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
                                                                  
                                               
                e.department_id = d.department_id

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

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

                                               
                e.department_id = d.department_id(+)

You need to create a report that lists all employees in department 10 (Sales) whose salary is not equal to $25,000 per year. Which query should you issue to accomplish this task?   

                                               
                SELECT last_name, first_name, salary
FROM employees
WHERE salary != 25000 AND dept_id = 10;
(*)


                                               
                SELECT last_name, first_name, salary
FROM employees
WHERE salary = 25000 AND dept_id = 10;

                                               
                SELECT last_name, first_name, salary
FROM employees
WHERE salary <= 25000 AND dept_id = 10;

                                               
                SELECT last_name, first_name, salary
FROM employees
WHERE salary > 25000 AND dept_id = 10;

                                                                               
What is the result of a query that selects from two tables but includes no join condition?                                                                        
                                               
                A Cartesian product (*)

                                               
                A selection of rows from the first table only

                                               
                A syntax error

                                               
                A selection of matched rows from both tables

                                                                               
What is produced when a join condition is not specified in a multiple-table query using Oracle proprietary Join syntax?   
                            
                                               
                A self-join

                                               
                An outer join

                                               
                A Cartesian product (*)

                                               
                An equijoin

When must column names be prefixed by table names in join syntax? 
                                                                               
                                               
                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

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

                                                                
                                               
                No, Oracle will return a Column Ambiguously Defined error. (*)

                                               
                Yes, there are no syntax errors in that statement

                                               
                Yes, Oracle will resolve which department_id colum comes from which table.

                                               
                No, Oracle will not allow joins in the WHERE clause

                                                                               
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?  
                                                                               
                                               
                10

                                               
                15

                                               
                50 (*)

                                               
                5

                                                                               
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?
                                                               
                                               
                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;

                                               
                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;

                                                                               
 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?
                                                               
                                               
                AND employees.department_id > 5000 (*)

                                               
                FROM employees e, departments d

                                               
                WHERE e.department_id = d.department_id

                                               
                ORDER BY 4;

                                               
                SELECT e.employee_id, e.last_name, e.first_name, d.department_name

2 comments:

  1. Que pinta Kanna Kobayashi en esta página? ...

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete

Final Exam Java Programming 2019 Learner - English

Final Exam