Guide to Oracle SQL (Buffalo State University 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.
SELECT firstName, lastName, officeNumber FROM facultyTable, officeTable; FIRSTNAME LASTNAME OFFICE -------------------- -------------------- ------ Don Teddon BA123 Don Teddon CA321 Don Teddon DA231 Don Teddon BA321 Don Teddon CA123 Don Teddon DA132 Steve Smith BA123 Steve Smith CA321 Steve Smith DA231 Steve Smith BA321 Steve Smith CA123 FIRSTNAME LASTNAME OFFICE -------------------- -------------------- ------ Steve Smith DA132 Becky Thomas BA123 Becky Thomas CA321 Becky Thomas DA231 Becky Thomas BA321 Becky Thomas CA123 Becky Thomas DA132 Mikhail Baryshinokov BA123 Mikhail Baryshinokov CA321 Mikhail Baryshinokov DA231 Mikhail Baryshinokov BA321 FIRSTNAME LASTNAME OFFICE -------------------- -------------------- ------ Mikhail Baryshinokov CA123 Mikhail Baryshinokov DA132 Arthur Mitchell BA123 Arthur Mitchell CA321 Arthur Mitchell DA231 Arthur Mitchell BA321 Arthur Mitchell CA123 Arthur Mitchell DA132 Alice Alonzo BA123 Alice Alonzo CA321 Alice Alonzo DA231 FIRSTNAME LASTNAME OFFICE -------------------- -------------------- ------ Alice Alonzo BA321 Alice Alonzo CA123 Alice Alonzo DA132 36 rows selected.
JOIN Clause| Type of Join | Description |
|---|---|
| NATURAL JOIN | SQL uses a common field to produce a Cartesian Join. Not recommended. |
| EQUI JOIN | 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. |
| OUTER JOIN | 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. |
| SELF JOIN | 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. |
The JOIN Clause
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.
SELECT firstName, lastName, officeNumber FROM facultyTable, officeTable WHERE facultyTable.officeid = officeTable.officeid; FIRSTNAME LASTNAME OFFICE -------------------- -------------------- ------ Don Teddon BA123 Mikhail Baryshinokov BA123 Arthur Mitchell CA321 Steve Smith CA321 Alice Alonzo DA231 Becky Thomas DA231 6 rows selected.
SELECT firstName, lastName, officeNumber FROM facultyTable INNER JOIN officeTable ON facultyTable.officeid = officeTable.officeid; LASTNAME FIRSTNAME OFFICE -------------------- -------------------- ------ Teddon Don BA123 Baryshinokov Mikhail BA123 Mitchell Arthur CA321 Smith Steve CA321 Alonzo Alice DA231 Thomas Becky DA231 6 rows selected.
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 SELECT statement.
SELECT e.ename AS Employee, m.ename AS Manager FROM emp e, emp m WHERE e.mgr = m.empno ORDER BY m.ename; EMPLOYEE MANAGER -------------------- -------------------- Wanjiru Bikele Rupp Bikele Felix Keino Powell Rupp Bolt Shorter Shorter Wanjiru Keino Wanjiru 7 rows selected.
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.
SELECT c.customer_id AS "Customer ID", cust_last_name AS "Last Name", cust_first_name AS "First Name" FROM customers c WHERE NOT EXISTS (SELECT * FROM orders o WHERE c.customer_id = o.customer_id) ORDER BY c.cust_last_name, cust_first_name; Customer ID Last Name First Name ----------- -------------------- --------------- 8 Bogart Sally 3 Buckley Charlotte 1 Edwards Sally 25 Gerland Jim 6 Hackman Ridley 2 Hitchcock Bo 7 Landis Marilou 9 Sen Dianne 4 Sharif Bob 5 Spielberg Sachin 10 Stanton Alexander 11 rows selected.
Join Employee, Departments, and Jobs and Display Results
Requirements
- 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.
