SQL ALL Operator

SQL – HAVING CLAUSE

The HAVING clause enables you to specify conditions that filter which group results appear in the final results.

Syntax:

  • Select
  • From
  • Where
  • Group by
  • Having
  • Order by

For Example:

SELECT Employees.LastName, COUNT(Orders.OrderID)AS NumberOfOrders

From (Orders INNER JOIN Employees

ON Orders. EmployeeID=Employees.EmployeeID)

GROUPBY LastName

HAVING COUNT(Orders.OrderID)>10;

Result:

WILD CARDS :

  • SQL wildcards are used to search for data within a table.
  • Wildcard characters are used with the SQL LIKE operator.

Syntax for wildcard:

  • selects all customers with a City starting with “ber“:SELECT * FROM Customers
    WHERE City LIKE ‘ber%’;
  • selects all customers with a City containing the pattern “es“:SELECT * FROM Customers
    WHERE City LIKE ‘%es%’;
  • selects all customers with a City starting with any character, followed by “erlin“:SELECT * FROM Customers
    WHERE City LIKE ‘_erlin’;
  • selects all customers with a City starting with “b”, “s”, or “p”:

SELECT * FROM Customers
WHERE City LIKE ‘[bsp]%’;

  • selects all customers with a City starting with “a”, “b”, or “c

SELECT * FROM Customers
WHERE City LIKE ‘[a-c]%’;

  • selects all customers with a City NOT starting with “b”, “s”, or “p“:

SELECT * FROM Customers
WHERE City LIKE ‘[!bsp]%’;

ORDER BY :

  • The ORDER BY keyword is used to sort the result-set by one or more columns.
  • The ORDER BY keyword sorts the records in ascending order by default.
  • To sort the records in a descending order, you can use the DESC keyword

   Syntax :

SELECT column_name, column_name
FROM table_name
ORDER BY column_name ASC|DESC, column_name ASC|DESC;

GROUP BY:

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;

For example :

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

Order and shippers table

Result :

Write a Reply or Comment

Your email address will not be published.