Page 100 - CITS - Computer Software Application -TT

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