Page 99 - CITS - Computer Software Application -TT

COMPUTER SOFTWARE APPLICATION - CITS

In this example, the INSERT statement retrieves data from the new_hires table, specifically the employee_id,
employee_name, and salary columns, but only for records where the new_salary is greater than 50,000. It then
inserts this data into the employees table
Subqueries with the SELECT Statement
Subqueries can be used with the SELECT statement in SQL to retrieve data from one or more tables based on
the results of a nested query. This allows you to create more complex and dynamic queries by incorporating the
results of one query into another. Here’s the basic syntax for using subqueries with the SELECT statement:
SELECT column1, column2, ...
FROM table1

WHERE columnN operator (SELECT columnX FROM tableY WHERE condition);
In this syntax:
• column1, column2, etc., represent the columns you want to retrieve in your main query.
• table1 is the main table you’re querying.
• columnN is a column from table1 that you want to compare using an operator.

• operator is a comparison operator like ‘=’, ‘<’,’ >’, ‘IN’, etc.
• The subquery within parentheses (SELECT columnX FROM tableY WHERE condition) is embedded in the
WHERE clause and retrieves data from another table (tableY) based on a specific condition.

Here’s an example to illustrate how you might use a subquery with the SELECT statement:
Suppose you have two tables, employees and salaries, and you want to retrieve the names of employees who
earn a salary greater than the average salary in the salaries table:
ELECT employee_name

FROM employees
WHERE employee_id IN (SELECT employee_id FROM salaries WHERE salary > (SELECT AVG(salary) FROM
salaries));

In this example, the main SELECT statement retrieves the employee_name from the employees table for
employees whose employee_id matches the result of the subquery. The subquery calculates the average salary
from the salaries table and then filters employees who earn more than the calculated average.
Subqueries with the UPDATE Statement
Subqueries can also be used with the UPDATE statement in SQL to modify data in a table based on the results
of a subquery. This allows you to update records in one table using information from another table or using the
results of a subquery. Here’s a basic syntax for using subqueries with the UPDATE statement:
UPDATE target_table

SET column1 = value1, column2 = value2, ...
WHERE columnN operator (SELECT columnX FROM source_table WHERE condition);
In this syntax:
• target_table is the table you want to update.
• column1, column2, etc., are the columns in target_table that you want to update.
• value1, value2, etc., are the new values you want to set for the corresponding columns.

• columnN is a column in target_table that you want to compare using an operator.
• operator is a comparison operator like =, <, >, IN, etc.
• The subquery within parentheses (SELECT columnX FROM source_table WHERE condition) is embedded in
the WHERE clause and retrieves data from another table (source_table) based on a specific condition.

86

CITS : IT&ITES - Computer software application - Lesson 18 - 36