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)
SQL
SQL stands for Structured Query Language, is a form of communication that uses queries structured in a specific format to store, manage & retrieve data from a relational database.
Queries
A query is a type of computer programming language that is used to retrieve data from a database. Databases are useful in a variety of ways. They enable the retrieval of records or parts of records, as well as the performance of various calculations prior to displaying the results. A search query is one type of query that many people perform several times per day. A search query is executed every time you use a search engine to find something. When you press the Enter key, the keywords are sent to the search engine, where they are processed by an algorithm that retrieves related results from the search index. Your query's results are displayed on a search engine results page, or SER.
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;
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)
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)
Trending now
This is a popular solution!
Step by step
Solved in 3 steps