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

COMPUTER SOFTWARE APPLICATION - CITS




           Here’s an example to illustrate how you might use a subquery with the UPDATE statement:

           Suppose you have two tables, orders and customers, and you want to update the customer_id in the orders table
           based on a customer’s name from the customers table
           UPDATE orders
           SET customer_id = (SELECT customer_id FROM customers WHERE customer_name = ‘John Doe’)

           WHERE order_id = 123;
           In this example, the UPDATE statement updates the customer_id column in the orders table with the result of the
           subquery. The subquery retrieves the customer_id from the customers table for the customer with the name ‘John
           Doe,’ and this value is used to update the specified order (order_id = 123) in the orders table.
           Subqueries with the DELETE Statement
           Subqueries can also be used with the DELETE statement in SQL to remove rows from a table based on the
           results of a subquery. This allows you to delete specific records in a table using information derived from another
           table or a subquery’s results. Here’s a basic syntax for using subqueries with the DELETE statement:

           DELETE FROM target_table
           WHERE columnN operator (SELECT columnX FROM source_table WHERE condition);
           In this syntax:
           •  target_table is the table from which you want to delete rows.
           •  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.
           •  condition is an optional condition that filters the data from the source_table before deletion.

           Here’s an example to illustrate how you might use a subquery with the DELETE statement:
           Suppose you have two tables, employees and salaries, and you want to delete employees from the employees
           table whose salary exceeds the average salary in the salaries table:

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

           In this example, the DELETE statement removes rows from the employees table where the employee_id matches
           the result of the subquery. The subquery calculates the average salary from the salaries table and filters employees
           with salaries greater than the calculated average.


            Functions used in query like sum, average, max, min,
            count etc


           Functions like sum, average, max, min, count, and others are commonly used in various database query languages,
           such as SQL, to perform calculations and aggregations on data. Here’s a brief overview of these functions:
           1  SUM: The SUM function is used to calculate the total of a numeric column in a database table. For example,
              you can use it to find the total sales for a particular product.
              SELECT SUM(sales_amount) FROM sales_data;
           2  AVERAGE (AVG): The AVERAGE function, often abbreviated  as AVG, calculates  the average  value  of a
              numeric column.
              SELECT AVG(temperature) FROM weather_data




                                                           87

                              CITS : IT&ITES - Computer software application - Lesson 18 - 36
   95   96   97   98   99   100   101   102   103   104   105