My SQL WORKBENCH **Please note that you may actually use as many tables as you need. You are not limited to use only two tables. Example: SELECT * FROM DEPARTMENTS; SELECT * FROM EMPLOYEES; Now join departments and employees tables. SELECT FIRST_NAME, DEPARTMENT_NAME FROM EMPLOYEES, DEPARTMENTS WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID; NOTE: If you omit the join condition, the result will be the Cartesian Product of the two tables. A Cartesian Product joins all rows of the first table with all rows of the second table. That is if the first table has n rows and the second table has m rows, the output will have n*m rows. The following query will produce a Cartesian Product: SELECT FIRST_NAME, DEPARTMENT_NAME FROM EMPLOYEES, DEPARTMENTS; **When table names are long, qualifying column names might be very time consuming. Usually people prefer to use table aliases for this purpose. Example: List the name of all employees who work for 'Purchasing' department SELECT E.FIRST_NAME "EMPLOYEE NAME" FROM EMPLOYEES E, DEPARTMENT D WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND UPPER(D.DEPARTMENT_NAME) = 'PURCHASING'; **Table joins can be performed on columns that are not primary or foreign keys as well. SELECT E.FIRST_NAME FROM EMPLOYEES A, EMPLOYEES B WHERE A.SALARY = B.SALARY AND A.EMPLOYEE_ID != B.EMPLOYEE_ID;     **All these joins are called equijoin because they use an equality to form the join. It is also possible to form nonequijoins. These are joins that use an operator (+) than other. Normally if a row does not satisfy a join condition, it will be left out of the result. You want to see rows of a table that do not satisfy the join condition as well, you have to perform an outer join. Outer join is performed by placing a (+) sign on the right hand side of one of the attribute of join condition. The (+) sign makes it possible to join a 'NULL' row of the table it is close to with rows from the other table. In other words (+) will allow NULL rows on the side that is placed. Syntax of an outer join: SELECT table1.column11, table1.column12, ..., table2.column21, table2.column22 FROM table1, table2 WHERE table1.column11(+) = table2.column21(+); **Please be careful, you can use only one (+) in the join condition. Outer Examples: Example1: Display last name of all employees with their department information (department id and department name) including those employees who does not work for any department. SELECT e.last_name,d.department_id,d.department_name FROM employees e, departments d WHERE e.department_id=d.department_id(+); Example2: Display last name of all employees with their department information (department id and department name) including those departments that they do not have any employee. SELECT e.last_name,d.department_id,d.department_name FROM employees e, departments d WHERE e.department_id(+)=d.department_id; SELF JOIN Joining a Table to Itself: Sometimes you need to join a able to itself. To find the name of each employee's manager, you need to join the EMPLOYEES table to itself. Example:Find the name of the managers for all employees. SELECT e.last_name,m.last_name FROM employees e, employees m WHERE .manager_id=m.employee_id; SQL> desc locations Name Null? Type ----------------------------------------- -------- ---------------------------- LOCATION_ID NOT NULL NUMBER(6) DEPARTMENT_ID NOT NULL NUMBER(6) ADDRESS VARCHAR2(25) CITY NOT NULL VARCHAR2(25) STATE NOT NULL VARCHAR2(25) COUNTRY VARCHAR2(20)     EXERCISES PART A: 1-Display the addresses of all the departments. Show locationID, street address,city,state and country name in the output. (Use Locations table) 2-Display the last name, department number and deparment name for all employees. 3-Display the last name, job, department number, and department name for all employees work in Toronto. (Employees,Departments,Locations)

My SQL WORKBENCH

**Please note that you may actually use as many tables as you need. You are not limited to use only two
tables.
Example:
SELECT *
FROM DEPARTMENTS;
SELECT *
FROM EMPLOYEES;
Now join departments and employees tables.
SELECT FIRST_NAME, DEPARTMENT_NAME
FROM EMPLOYEES, DEPARTMENTS
WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID;
NOTE: If you omit the join condition, the result will be the Cartesian Product of the two tables. A Cartesian
Product joins all rows of the first table with all rows of the second table.
That is if the first table has n rows and the second table has m rows, the output will have n*m rows.
The following query will produce a Cartesian Product:
SELECT FIRST_NAME, DEPARTMENT_NAME
FROM EMPLOYEES, DEPARTMENTS;
**When table names are long, qualifying column names might be very time consuming. Usually people
prefer to use table aliases for this purpose.
Example: List the name of all employees who work for 'Purchasing' department
SELECT E.FIRST_NAME "EMPLOYEE NAME"
FROM EMPLOYEES E, DEPARTMENT D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND
UPPER(D.DEPARTMENT_NAME) = 'PURCHASING';
**Table joins can be performed on columns that are not primary or foreign keys as well.
SELECT E.FIRST_NAME
FROM EMPLOYEES A, EMPLOYEES B
WHERE A.SALARY = B.SALARY AND A.EMPLOYEE_ID != B.EMPLOYEE_ID;

 

**All these joins are called equijoin because they use an equality to form the join. It is also possible to form
nonequijoins. These are joins that use an operator (+) than other.
Normally if a row does not satisfy a join condition, it will be left out of the result. You want to see rows of a
table that do not satisfy the join condition as well, you have to perform an outer join. Outer join is performed
by placing a (+) sign on the right hand side of one of the attribute of join condition.
The (+) sign makes it possible to join a 'NULL' row of the table it is close to with rows from the other
table. In other words (+) will allow NULL rows on the side that is placed.
Syntax of an outer join:
SELECT table1.column11, table1.column12, ..., table2.column21, table2.column22
FROM table1, table2
WHERE table1.column11(+) = table2.column21(+);
**Please be careful, you can use only one (+) in the join condition.
Outer Examples:
Example1: Display last name of all employees with their department information (department id and department
name) including those employees who does not work for any department.
SELECT e.last_name,d.department_id,d.department_name
FROM employees e, departments d
WHERE e.department_id=d.department_id(+);
Example2: Display last name of all employees with their department information (department id and
department name) including those departments that they do not have any employee.
SELECT e.last_name,d.department_id,d.department_name
FROM employees e, departments d
WHERE e.department_id(+)=d.department_id;
SELF JOIN
Joining a Table to Itself:
Sometimes you need to join a able to itself.
To find the name of each employee's manager, you need to join the EMPLOYEES table to itself.
Example:Find the name of the managers for all employees.
SELECT e.last_name,m.last_name
FROM employees e, employees m
WHERE .manager_id=m.employee_id;
SQL> desc locations Name Null? Type
----------------------------------------- --------
----------------------------
LOCATION_ID NOT NULL NUMBER(6)
DEPARTMENT_ID NOT NULL NUMBER(6)
ADDRESS VARCHAR2(25)
CITY NOT NULL VARCHAR2(25)
STATE NOT NULL VARCHAR2(25)
COUNTRY VARCHAR2(20)

 

EXERCISES
PART A:
1-Display the addresses of all the departments. Show locationID, street address,city,state and country name in the
output. (Use Locations table)
2-Display the last name, department number and deparment name for all employees.
3-Display the last name, job, department number, and department name for all employees work in
Toronto. (Employees,Departments,Locations)