Working with Multiple Tables

 Using Sub-queries:

• Sub-queries allow you to filter results from one table based on conditions or data from another table.
• Example: Retrieving employee records from the employees table where the department ID exists in the departments table:

SELECT * FROM employees WHERE department_ID IN (SELECT department_ID_department FROM departments);

Sub-queries for Filtering by Related Data:

• You can use sub-queries to filter results based on related data in another table.
• Example: Retrieving employees from a specific location by using a sub-query from the departments table:

SELECT * FROM employees WHERE department_ID IN (SELECT department_ID_department FROM departments WHERE location_ID = 'L0002');

Sub-queries for Joining Tables:

• Sub-queries can be used to join tables based on certain criteria.
• Example: Retrieving department ID and department name for employees earning more than $70,000:

SELECT department_ID_department, department_name FROM departments WHERE department_ID_department IN (SELECT department_ID FROM employees WHERE salary > 70000);

Using Implicit Joins:

• You can specify multiple tables in the FROM clause to perform implicit joins.
• Example: Joining employees and departments tables without using explicit join operators:

SELECT * FROM employees, departments WHERE employees.department_ID = departments.department_ID_department;

Using Aliases for Tables:

• Aliases can be used to shorten table names and improve query readability.
• Example: Using table aliases and fully qualifying column names:

SELECT E.Employee_ID, D.Department_ID_department FROM employees E, departments D WHERE E.Department_ID = D.Department_ID_department;

By using sub-queries and implicit joins, you can effectively work with multiple tables in your SQL queries to retrieve the desired data based on various conditions and relationships. Thanks for watching!