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: 4-Display employees' last name and employee number along with their manager's last name and manager number. Label the columns Employee, Emp#, Manager Mgr#, respectively. 5-Display the last name, department number,department name and region name for all employees who work in Europe. 6-What is the name of the manager for employee WINSTON (first name). 7-Display last name of employees whose manager is KING. 8-Display last name, salary and job title of all employees who earns more than the lowest salary. (Employees and Jobs) 9-Display all employees including King, who has no manager. Order the results by the employee number. 10-Display the name and hire date of any employee hired after employee Davis. PART B: Will the following select statements work? If the queries don’t work, explain why and then correct them. a-) SELECT NAME, EMPLOYEE_ID, SALARY FROM EMPLOYEE_TBL E, EMPLOYEE_PAY_TBL EP WHERE EMPLOYEE_ID = EMPLOYEE_ID AND NAME LIKE '%MITH'; b-) SELECT E.NAME, E.EMPLOYEE_ID, EP.SALARY FROM EMPLOYEE_TBL E, EMPLOYEE_PAY_TBL EP WHERE NAME LIKE '%MITH'; c-) SELECT E.NAME, E.EMPLOYEE_ID, EP.SALARY FROM EMPLOYEE_TBL E, EMPLOYEE_PAY_TBL EP WHERE E.EMPLOYEE_ID = EP.EMPLOYEE_ID AND E.NAME LIKE '%MITH'; d-) SELECT ENAME, COUNT(*) FROM EMP WHERE ENAME = '&NAME' ORDER BY ENAME; e-) SELECT DEPT_ID, COUNT(*) FROM EMP GROUP BY DEPT_ID HAVING ENAME='ALIYE'; f-) SELECT F_NAME, L_NAME WHERE F_NAME LIKE '%A' FROM EMP, DEPT; g-) SELECT NAME, SALARY FROM DEPT, EMP WHERE DEPT.ID = EMP.ID AND SUM(SALARY);