Database Domain 4 Lesson 1 to 6 BETWEEN and AND To return records between two numbers or two dates, one would use the BETWEEN keyword. It is important to add single quotes around dates to ensure the query runs properly. If the date field keeps track of time, make sure to reflect that in the criteria. A query may need multiple filters to search through data. The AND keyword can combine criteria to narrow the search results when added to a WHERE clause. OR and NOT While the AND keyword filters information in a WHERE clause, the OR keyword expands the possible records returned in a query. The two keywords can work in the same criterion to search for two or more columns. Keywords can also search for the opposite of a criterion. The NOT keyword combined with a WHERE clause will filter any results that are opposite of the column in a query. TOP, IN, and NOT IN One can limit the number of records shown to increase the speed of a query. This limitation can be useful to get an idea of what is in a table without running the full table. To do this, one can add the TOP keyword after the SELECT keyword along with the number of records desired. When querying multiple OR conditions, one can use the IN keyword to search instead. One can use parenthesis to search for more than two conditions. The NOT IN keyword will retrieve records that do not match a criteria group. ANY and ALL One can join several tables to get a filter on two fields using a subquery. This subquery is done through using a SELECT statement within a SELECT statement. The ANY keyword will return a set of records if a match is there. The ALL keyword will return a set of records if every record matches the same or similar criteria. NULL, NOT NULL, and Comparison Operators The NULL keyword searches for fields with empty values. If one needs to find missing information, a WHERE clause combined with the NULL keyword will return the necessary fields. The NOT NULL keyword shows records of a field that do have values. Comparison operators show values that are greater than or less than a number. They call also retrieve information that occurs before or after a certain date. GROUP BY, SUM, and HAVING Aggregates are summaries of numeric portions of data. The most common aggregate is the SUM aggregate. The SUM clause finds the total of a group of numbers in a defined category. The GROUP BY clause will group records in a query when using an aggregate. The HAVING clause filters data based on aggregates and is added after the grouping takes place. MIX and MAX The MIN and MAX clauses will show the lowest and highest amounts in the desired column, respectively. The COUNT clause shows the number of records for each item in a group. The AVG clause shows the average over a group of numbers. These clauses can be sorted if desired.