JRN 407 Computer Assisted Reporting Jane Briggs-Bunting Michigan State University SQL Cheat Sheet There are six basic commands in SQL, and though you can eliminate some when asking your questions, you must always keep them in this order: SELECT choosing columns or fields of information you want to compare. So, after the query is run, these columns with their data will appear on the screen. (You can also use Select to calculate or to create a new column—more on that much later!) (Required) FROM the table or tables (database) of information you want to use. (Required) the meet this condition or these conditions. In other words, filtering the records you rows want to see. (Optional) GROUP BY summarizing information whenever you do a function. (Gathers rows with identical values in a the chosen column or field, or displays a single row for each unique combination of those identical values. (Optional) HAVING allows you to limit records retrieved. It is always used in conjunction with GROUP BY, e.g. show a GROUP BY only if the Group meets this condition. HAVING does for GROUP BY what WHERE does for rows or records. (Optional) ORDER BY sorts the answer from highest to lowest or the reverse by a particular column. (Optional) SELECT and FROM must always be included in every query. The remaining four can be added as needed. SELECT, FROM and WHERE search the data GROUP BY summarizes the data (every field in GROUP BY must be in SELECT, too.) HAVING limits results of the data searched ORDER BY organizes how results are presented. More SQL Rules: Text is always "between quotes". Some numbers you may want to treat as text, zip codes, for example, so they would be treated like "48309". are just Numbers Dates are always between #02,013,99# * (asterisks)are wild cards and can be used to guess at information or help in the sorting of dirty data. You can use * at the beginning, middle, and/or end of a word. ; (semicolon)—signals the computer to run the query. However, in Access, you click on the Toolbar key with the ! on it to run the query. < means less than (note how the arrow points left for Less than!) > means greater than (ditto on how the arrow points right for Greater than) <= means less than or equal to
JRN 407 Computer Assisted Reporting Jane Briggs-Bunting Michigan State University SQL Cheat Sheet There are six basic commands in SQL, and though you can eliminate some when asking your questions, you must always keep them in this order: SELECT choosing columns or fields of information you want to compare. So, after the query is run, these columns with their data will appear on the screen. (You can also use Select to calculate or to create a new column—more on that much later!) (Required) FROM the table or tables (database) of information you want to use. (Required) the rows meet this condition or these conditions. In other words, filtering the records you want to see. (Optional) GROUP BY summarizing information whenever you do a function. (Gathers rows with identical values in a the chosen column or field, or displays a single row for each unique combination of those identical values. (Optional) HAVING allows you to limit records retrieved. It is always used in conjunction with GROUP BY, e.g. show a GROUP BY only if the Group meets this condition. HAVING does for GROUP BY what WHERE does for rows or records. (Optional) ORDER BY sorts the answer from highest to lowest or the reverse by a particular column. (Optional) SELECT and FROM must always be included in every query. The remaining four can be added as needed. SELECT, FROM and WHERE search the data GROUP BY summarizes the data (every field in GROUP BY must be in SELECT, too.) HAVING limits results of the data searched ORDER BY organizes how results are presented. More SQL Rules: Text is always "between quotes". Some numbers you may want to treat as text, zip codes, for example, so they would be treated like "48309". Numbers are just Dates are always between #02,013,99# * (asterisks)are wild cards and can be used to guess at information or help in the sorting of dirty data. You can use * at the beginning, middle, and/or end of a word. ; (semicolon)—signals the computer to run the query. However, in Access, you click on the Toolbar key with the ! on it to run the query. < means less than (note how the arrow points left for Less than!) > means greater than (ditto on how the arrow points right for Greater than) <= means less than or equal to
SQLCheatSheet.docx