Database Programming - Study Guide Sections 3-4

Database Programming - Study Guide Sections 3-4

Database Programming - Study Guide Sections 3-4

Name ______Date ______

  1. Explain the joining of tables?

Sometimes data from more than one table is needed on the same report.

  1. What are a Cartesian Product and a CROSS JOIN, are there any differences?

A Cartesian Product and a CROSS JOIN merge the tables’ data into one big report. Many rows and columns are displayed. The only difference is how the FROM clause is written, a comma separating the tables for a Cartesian Product and the actual words ‘CROSS JOIN’ separating the tables for a CROSS JOIN.

  1. How do you avoid a Cartesian Product or a CROSS JOIN?

Do not omit a join clause.

  1. What is the only relational operator used for an equijoin?

The equality sign ‘=’.

  1. On a join statement the SELECT clause specifies….

The column names to retrieve.

  1. On a join statement the FROM clause specifies….

The two tables that the database must access.

  1. On a join statement the WHERE clause specifies….

How the tables are joined.

  1. How are rows of data restricted in a join statement using a WHERE clause?

In addition to the join on the FROM statement, other conditions may apply…looking for a last name, checking if the salary is greater then 2000…

  1. How are column names known to belong to which table in a join?

A table prefix is added to each column name to designate the table it belongs to.

  1. List and describe the Oracle Proprietary Joins

Equijoin

Nonequijoin

Outer join

Self join

  1. List and describe the SQL Compliant Joins

Cross joins

Natural joins

Using clause

Right Outer joins

Left Outer joins

Full Outer joins

  1. How long can table alias be and why use a table alias?

30 characters long, to improve performance due to the computer not having to search for what column is on what table, should be has short as can be.

  1. Once a table alias is used on a FROM, are they always used in the SELECT statement?

Yes

  1. To join four (4) tables together, how many join conditions are needed?

Three join conditions.

  1. What condition must be present for a nonequijoin?

A nonequijoin is a join condition that does not include an equality sign.

  1. When the same column name appears in more than one table, the column name must be prefixed with ______.

The table name.

  1. Why use an outer join?

To generate the rows that are missing….if a row does not satisfy a join condition, the row will not appear.

  1. Can the plus sign (outer join) be placed on both sides of a condition clause?

No, only on one side.

  1. What is the difference between a LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN?

Left Outer Join displays the data that satisfies the join condition and the missing data from the left table that does not satisfy the join condition. Right Outer Join displays the data that satisfies the join condition and the missing data from the right table that does not satisfy the join condition. Full Outer Join displays the data that satisfies the join condition and the missing data from both tables that does not satisfy the join condition.

  1. Why use a self join?

To join a table to itself.

  1. What join is based on all columns in the two tables that have the same name?

NATURAL JOIN

  1. Can the join above in question #20 be joined with columns with the same name but different data types, why?

No, the data types must be the same for the data to match.

  1. What join clause will correct the error in question #21?

JOIN USING clause

  1. To specify arbitrary conditions or specify columns to join, what join clause is used?

JOIN ON

  1. Does every SELECT statement that joins tables have to have a join condition?

Yes, tables must be join before data will be displayed.

  1. Create a query to display the employee number, last name, and department name of all employees who earn a commission.

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

FROM employees e, departments d

WHERE e.department_id = d.department_id

AND e.commission_pct IS NOT NULL;

  1. Create a query to display the last name, job, department number, and department name for all employees who work in Oxford.

SELECT e.last_name, e.job_id, d.department_id, d.department_name

FROM employees e JOIN departments d

ON (e.department_id = d.department_id)

JOIN locations l

ON (d.location_id = l.location_id)

AND l.city = 'Oxford';

  1. Create a query to display the last name and employee number along with their manager’s last name and number. When you are done, modify the statement of display of all employees including King who has no manager.

SELECT e.employee_id, e.last_name, m.employee_id manager, m.last_name

FROM employees e, employees m

WHERE e.manager_id = m.employee_id;

SELECT e.employee_id, e.last_name, m.employee_id manager, m.last_name

FROM employees e, employees m

WHERE e.manager_id = m.employee_id(+)

  1. Create a query to display the employee last name, department name, and location id of all employees in department 90.

SELECT e.last_name, d.department_name, d.location_id

FROM employees e, departments d

WHERE e.department_id = d.department_id

AND d.department_id = 90;

Oracle Academy11-May-2005

Top View