Scribd
Upload
2 views

Database II Sect. -- MySQL Commands Pt.3

The document provides an overview of various MySQL commands and operators, including the LIKE operator for pattern matching, the GROUP BY clause for organizing data, and the HAVING clause for filtering grouped data. It also explains aggregation functions such as MIN, MAX, COUNT, SUM, and AVG with examples related to employee and project data. Additionally, it hints at upcoming sections on JOIN operations.

Uploaded by

mahermostafa564
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
2 views

Database II Sect. -- MySQL Commands Pt.3

The document provides an overview of various MySQL commands and operators, including the LIKE operator for pattern matching, the GROUP BY clause for organizing data, and the HAVING clause for filtering grouped data. It also explains aggregation functions such as MIN, MAX, COUNT, SUM, and AVG with examples related to employee and project data. Additionally, it hints at upcoming sections on JOIN operations.

Uploaded by

mahermostafa564
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 11

IN

Select * from emp where empid in(1,2,5)


Select * from emp where empid not in(1,2,5)
MYSQL - LIKE OPERATOR
The LIKE Operator in MySQL database is a logical operator that is used to retrieve the data
from a table, based on a specified pattern.
Select empname from emp where empname like ‘%h’;
Select empname from emp where empname like ‘_h’;

Select empname from emp where empname like Null;


To select all employees from the emp table whose names start with the letter “A":
SELECT * FROM emp WHERE empname LIKE ‘A%’;
To select all works entries from the works table where the names of the employee starts with
“a":
SELECT * FROM works WHERE eid IN (SELECT empid FROM emp WHERE empname LIKE A%');
MYSQL GROUP BY
The GROUP BY clause in MySQL is used to arrange identical data in a table into
groups
Select distinct age , count(age)
Group by age
Out example
SELECT AGE, AVG(SALARY) AS AVG_SALARY FROM CUSTOMERS GROUP BY AGE
HAVING AVG(salary) > 8000;
MYSQL HAVING
The MySQL HAVING Clause is used to filter grouped rows in a table based on
conditions.
SELECT column1, column2, aggregate_function(column) FROM table_name
GROUP BY column1, column2, ...
HAVING condition
Select age , count(age) from emp
Group by age
Having count(age) >= 2
AGGREGATION
MIN() - returns the smallest value within the selected column
MAX() - returns the largest value within the selected column
COUNT() - returns the number of rows in a set
SUM() - returns the total sum of a numerical column
AVG() - returns the average value of a numerical column
MIN() AND MAX():
To find the smallest and largest salary among employees:
SELECT MIN(salary) AS min_salary, MAX(salary) AS max_salary
FROM emp;
To find the earliest and latest start date among projects:
SELECT MIN(startdate) AS earliest_start, MAX(startdate) AS latest_start
FROM proj;
COUNT
Count number of Employees
SELECT COUNT(*) AS emp_count FROM emp;
Count number of projects
SELECT COUNT(*) AS proj_count FROM proj;
SUM
To calculate the total billing rate across all works:
SELECT SUM(billing_rate) AS total_billing_rate FROM works;
AVG
To find the average age of employees:
SELECT AVG(age) AS avg_age FROM emp;
Next sections
Join

You might also like