Page 99 - CITS - Computer Software Application -TT
P. 99

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
   94   95   96   97   98   99   100   101   102   103   104