Page 113 - Computer Software Application TP - Volume 1

COMPUTER SOFTWARE APPLICATION - CITS

When you’re done, you can exit the MySQL Command Line Client:
EXIT;

This will close the connection and return you to the regular command prompt.

TASK 5 :Using Having Clause:

Assume you have a table named sales with columns product and amount. You want to know the total amount of
sales per product, but only for products with a total sales greater than a certain threshold, e.g., 100.
1 SELECT product, SUM(amount) as total sales
FROM sales

GROUP BY product
HAVING total sales > 100;
This query selects the product and the total sales (SUM(amount)) for each product from the sales table, grouped
by the product column. The HAVING clause filters the results to include only those with a total sales greater than
100.
You can add more conditions in the HAVING clause based on your requirements.
2 SELECT product, SUM(amount) as total sales

FROM sales
GROUP BY product
HAVING total sales > 100 AND COUNT(*) > 2;
Example-

When you’re done, you can exit the MySQL Command Line Client:
EXIT;

TASK 6 : Using Sub Query:
If you haven’t already created tables and inserted sample data, you can use the following SQL statements:

1 CREATE TABLE Departments:
CREATE TABLE departments (
id INT PRIMARY KEY,
name VARCHAR(50)
);

2 CREATE TABLE Employees:
CREATE TABLE employees (
id INT PRIMARY KEY,

98
CITS : IT & ITES - Computer Software Application - Exercise 29 CITS : IT & ITES - Computer Software Application - Exercise 29