Skip to main content

13. Filtering Results

Image
By

WHERE and HAVING are filters. They specify a series of search conditions, and only those rows that meet the terms of the search conditions are used to build the result set.

Where

SELECT*FROM Customers
WHERECustomerID=1;

GROUP BY

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

SELECTcolumn_name,aggregate_function(column_name)
FROMtable_name
WHEREcolumn_name operator value
GROUPBYcolumn_name;

§ Example
SELECTShippers.ShipperName,COUNT(Orders.OrderID)ASNumberOfOrdersFROM Orders
LEFTJOIN Shippers
ONOrders.ShipperID=Shippers.ShipperID
GROUPBYShipperName;

Having

§ The HAVING clause is typically used together with the GROUP BY clause to filter the results of aggregate values.
§ The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
SELECTcolumn_name,aggregate_function(column_name)
FROMtable_name
WHEREcolumn_name operator value
GROUPBYcolumn_name
HAVINGaggregate_function(column_name) operator value;

§ Example
SELECTEmployees.LastName,COUNT(Orders.OrderID)ASNumberOfOrdersFROM (Orders
INNERJOIN Employees
ONOrders.EmployeeID=Employees.EmployeeID)
GROUPBYLastName
HAVINGCOUNT(Orders.OrderID)> 10;













<<Prev                                                                                                        Next>>




Labels:

Comments

Post a Comment

Popular posts from this blog

10. SQL Aliases

By
§ SQL aliases are used to temporarily rename a table or a column heading. § Basically aliases are created to make column names more readable. Syntax for Columns SELECT column_name AS alias_name FROM table_name Syntax for Tables SELECT column_name ( s ) FROM table_name AS alias_name ; Here is a SQL statement with alias § Example SELECT o . OrderID , o . OrderDate , c . CustomerName FROM Customers AS c , Orders AS o WHERE c . CustomerName = ’Around the Horn’ AND c . CustomerID = o . CustomerID ; The same SQL statement without aliases: § Example SELECT Orders . OrderID , Orders . OrderDate , Customers . CustomerName FROM Customers , Orders WHERE Customers . CustomerName = "Around the Horn" AND Customers . CustomerID = Orders . CustomerID ; <<Prev                                                                           Next>>
Post a Comment
Read more

5. INDEX

By
§ Indexes are special lookup tables that the database search engine can use to speed up data retrieval. § An index can be created in a table to find data more quickly and efficiently. § An index helps speed up SELECT queries and WHERE clauses, but it slows down data input, with UPDATE and INSERT statements. § Indexes can be created or dropped with no effect on the data. § Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). Clustered indexes § Uses physical memory. § Easy to retrieve data. § Data is in stored form. Non-clustered indexes § No use of physical memory is needed. § More complicated, data retrieval is not that easy. § We can too createuniqueindex . Duplicate values are not allowed . CREATEINDEX index_name ON table_name ( column_name ) CREATEINDEX index_name ON table_name ( column_name ) CREATEINDEX Pindex Difference between clustered index and no
Post a Comment
Read more