Guide to Oracle SQL (University at Buffalo Version)
Displaying Data from Multiple Tables
Almost any "real world" database will have more than one (1) table. In fact, a single database may have ten (10) or more tables. For example, a school course database might have a facultyTable, studentTable, courseTable, gradeTable, and roomTable. The facultyTable and studentTable data may store some of the data in other tables such as the addressTable, emailTable, phoneTable, etc. This type information collection is a relational Database. Each table has a primary key and one (1) or more foreign keys.
Joining Multiple Tables
To retreive information from multiple tables you need to join tables using the secondary key from one table to relate to the primary key from another table. Tables can be "joined" without using a
JOIN clause. This is referred to as a
NATURAL JOIN SQL will determine if the two (2) tables have a field in common and, if so, use that field to produce the desired result set. However, as the next figure illustrates, this results in what is now as a Cartesian JOIN. The result set has thirty-six (36) rows because the facultyTable and the officeTable each hav six (6), so 6 X 6 = 36. Not usally what you would like to see in your result set.
|Type of Join
|SQL uses a common field to produce a Cartesian Join. Not recommended.
|This is also know as an INNER JOIN. Returns all the records from the table using the
ON clause from the left side of the equals sign (=) and only those records from the table on the right side thatmatch the specified condition.
|Returns all the records from the table on the right side of the equals sign (=) and only those records from the table on the left side that match the specified condition.
|SQL creates a temporary fopy of a table and then returns all the records from the real table and the temporary table that match the specified condition.
To retreive information from multiple tables you need to join tables using the secondary key from one table to relate to the primary key from another table.
The Self Join
There are times when the result you are looking for requires that you join one table with a copy of itself. For example, employees and their manager. The manager *is* an employee so to get the manager's name you need to reterive it from the employeeTable. This is what is known as a
SELF JOIN. You need create a temporary copy of the employeeTable and use a table alias to refer to that temporary table in the fields of your
Displaying Data That Does Not Meet a Condition
There are times when the result you are looking for requires that the data you need to reterive does not meet a certain condition. For example, customers who have never placed an order.
Join Employee, Departments, and Jobs and Display Results
- Open SQL Developer
- Connect to your database instance
- Open a new SQL editor window
- Enter code to CREATE and a new "employees_join" query that JOINs your "employees", "departments", and "jobs" tables using the secondary (foreign) keys from the "departments" and "jobs" tables and the primary key from the "employees" table.
- This query should display a result set containing the department name, employee first name, employee last name, employee email, employee hire date, employee job title, employee salary, and employee salary. Give the column headings in the result set user-friendly names.
When you have this Assignment ready for me to view and grade, you should click on this assignment and then choose "Add a File" and upload your employees_join.sql file, so I can download it and grade your work.