PHP Tutorials

Important Link

Home » Mysql » About Mysql

Insert Query :-

Useful aggregate functions:

SQL Aggregate Functions.

SQL aggregate functions return a single value, calculated from values in a column.

AVG() - Returns the average value

COUNT() - Returns the number of rows

FIRST() - Returns the first value

LAST() - Returns the last value

MAX() - Returns the largest value

MIN() - Returns the smallest value

SUM() - Returns the sum

SQL Scalar functions

SQL scalar functions return a single value, based on the input value.

UCASE() - Converts a field to upper case

LCASE() - Converts a field to lower case

MID() - Extract characters from a text field

LEN() - Returns the length of a text field

ROUND() - Rounds a numeric field to the number of decimals specified

NOW() - Returns the current system date and time

FORMAT() - Formats how a field is to be displayed

AVG() Function :-

AVG() function returns the average value of a numeric column.

Example :-

Syntax :-
SELECT AVG(column_name) FROM table_name;
SELECT AVG(cost) AS Costaverage FROM Products;
SELECT itemname, itemprice FROM Products WHERE cost>(SELECT AVG(cost) FROM Products);

COUNT() Function :-

COUNT() function returns the number of rows that matches a specified criteria.

SELECT COUNT(column_name) FROM table_name;

COUNT(*) Function :-

COUNT(*) function returns the number of records in a table:

SELECT COUNT(*) FROM table_name;

COUNT(DISTINCT column_name) Function :-

COUNT(DISTINCT column_name) function returns the number of distinct values of the specified column.

SELECT COUNT(DISTINCT column_name) FROM table_name;

SELECT COUNT(productid) AS noof_products FROM Orders WHERE productid=3;

limit keyword:-

limit keyword gives no of row from table which you specify.

SELECT name FROM Customers ORDER BY custoid ASC LIMIT 1;

this query gives the top row of the table.

SELECT CustomerName FROM Customers ORDER BY CustomerID DESC LIMIT 1;

this query gives the bottom row of the table.

MAX() Function:-

The MAX() function returns the highest value of the selected column.

Syntax :

SELECT MAX(column_name) FROM table_name;

Example :-

SELECT MAX(salary) AS Highestsalary FROM customers;

Example :- it gives the second largest salary.

SELECT MAX(salary) FROM customers WHERE salary NOT IN (SELECT MAX(salary) FROM customers);
select MAX(Salary) from Employee WHERE Salary <> (select MAX(Salary) from Employee );

Example :- it gives two hiest salary.

select (SELECT MAX(salary) FROM customers) maxsalary, (SELECT MAX(salary) FROM customers WHERE salary NOT IN (SELECT MAX(salary) FROM customers )) as 2nd_max_salary

MIN() Function:-

The MIN() function returns the LOWEST value of the selected column.

Syntax :

SELECT MIN(column_name) FROM table_name;

HAVING Clause:-

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

The MySQL HAVING clause is used in the SELECT statement to specify filter conditions for a group of rows or aggregates.
The MySQL HAVING clause is often used with the GROUP BY clause. When using with the GROUP BY clause, we can apply a filter condition to the columns that appear in the GROUP BY clause. If the GROUP BY clause is omitted, the HAVING clause behaves like the WHERE clause.
Notice that the HAVING clause applies the filter condition to each group of rows, while the WHERE clause applies the filter condition to each individual row.

Syntax :

SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM (Orders INNER JOIN Employees ON Orders.EmployeeID=Employees.EmployeeID) GROUP BY LastName HAVING COUNT(Orders.OrderID) > 10;

GROUP BY Statement:-

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

Syntax :

SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;

SELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders LEFT JOIN Shippers ON Orders.ShipperID=Shippers.ShipperID GROUP BY ShipperName;

Round Function.

The ROUND() function is used to round a numeric field to the number of decimals specified.

Syntax :-

SELECT ROUND(column_name,decimals) FROM table_name;

SELECT ProductName, ROUND(Price,0) AS RoundedPrice FROM Products;

Between keyword.

he BETWEEN operator selects values within a range. The values can be numbers, text, or dates.

Syntax :-

SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;

SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;

SELECT * FROM Products WHERE Price NOT BETWEEN 10 AND 20;

SELECT * FROM Products WHERE (Price BETWEEN 10 AND 20) AND NOT CategoryID IN (1,2,3);

SELECT * FROM Products WHERE ProductName BETWEEN 'C' AND 'M';

SELECT * FROM Products WHERE ProductName NOT BETWEEN 'C' AND 'M';

SQL Aliases :-

SQL aliases are used to temporarily rename a table or a column heading.

Basically aliases are created to make column names more readable

Syntax :-

SELECT column_name(s) FROM table_name AS alias_name;

SELECT CustomerName AS Customer, ContactName AS [Contact Person] FROM Customers;

Count duplicate data in table.

$qry = "SELECT salary, COUNT(*) as count
FROM duplicate
GROUP BY salary
HAVING COUNT(*) > 1";

CAST()

The CAST() function is used to convert the type of an expression to a specified type. The function is similar to CONVERT() function.

CAST(expr AS type))

SELECT first_name, CAST(hire_date AS DATETIME) 'Joining Date' from employees where hire_date>('1987-09-15') ;

SELECT enum_col FROM table_name ORDER BY CAST(enum_col AS CHAR);

COALESCE Function

COALESCE function returns the first non-NULL value from the given list. If there are no non-null values The COALESCE function returns NULL.

we use COALESCE function with the City column. Instead of showing NULL, we are showing NA where the city column value is null.

SELECT Id, COALESCE(FirstName, MiddleName, LastName) AS Name, Gender, AGE, COALESCE(City, 'NA') AS City FROM Employee;

we use COALESCE function with First, Middle, and Last Name column values.

SELECT Id, COALESCE(FirstName, MiddleName, LastName) AS Name, Gender, AGE, City FROM Employee;

CONCAT function

The CONCAT function can take n number of strings as arguments and return a single string as a result by adding all the input strings.

CONCAT (string1, string2, string3, …)

The CONCAT function take one or more arguments.

It concatnate all arguments string and returns a single string as a result.

The CONCAT function returns a binary string if the arguments include any binary strings.

If the argument is numeric, it is converted to its equivalent nonbinary string form. Returns NULL if any argument is NULL.

SELECT CONCAT(‘MySQL’, ‘ITTutorials’);
// MySQLITtutorials
SELECT CONCAT(‘MySQL’, ‘ ‘, ‘Tutorials’);

Itechtuto

PHP Tutorials

Important Link

Insert Query :-

SQL Aggregate Functions.

SQL Scalar functions

AVG() Function :-

Example :-

COUNT() Function :-

COUNT(*) Function :-

COUNT(DISTINCT column_name) Function :-

limit keyword:-

MAX() Function:-

MIN() Function:-

HAVING Clause:-

GROUP BY Statement:-

Round Function.

Between keyword.

SQL Aliases :-

Count duplicate data in table.

Connect Us Socially:

Quick Links