{"appState":{"pageLoadApiCallsStatus":true},"categoryState":{"relatedCategories":{"headers":{"timestamp":"2022-08-15T12:31:11+00:00"},"categoryId":33608,"data":{"title":"SQL","slug":"sql","image":{"src":null,"width":0,"height":0},"breadcrumbs":[{"name":"Technology","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33512"},"slug":"technology","categoryId":33512},{"name":"Programming & Web Design","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33592"},"slug":"programming-web-design","categoryId":33592},{"name":"SQL","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33608"},"slug":"sql","categoryId":33608}],"parentCategory":{"categoryId":33592,"title":"Programming & Web Design","slug":"programming-web-design","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33592"}},"childCategories":[],"description":"Know how to get your data moving when the database says, \"Talk SQL to me.\"","relatedArticles":{"self":"https://dummies-api.dummies.com/v2/articles?category=33608&offset=0&size=5"},"hasArticle":true,"hasBook":true},"_links":{"self":"https://dummies-api.dummies.com/v2/categories/33608"}},"relatedCategoriesLoadedStatus":"success"},"listState":{"list":{"count":10,"total":27,"items":[{"headers":{"creationTime":"2016-03-27T16:54:26+00:00","modifiedTime":"2022-04-25T15:20:42+00:00","timestamp":"2022-06-22T19:37:38+00:00"},"data":{"breadcrumbs":[{"name":"Technology","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33512"},"slug":"technology","categoryId":33512},{"name":"Programming & Web Design","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33592"},"slug":"programming-web-design","categoryId":33592},{"name":"SQL","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33608"},"slug":"sql","categoryId":33608}],"title":"SQL All-in-One For Dummies Cheat Sheet","strippedTitle":"sql all-in-one for dummies cheat sheet","slug":"sql-all-in-one-for-dummies-cheat-sheet","canonicalUrl":"","seo":{"metaDescription":"Get to know the basics of SQL development, including information on the set and value functions and using the WHERE clause.","noIndex":0,"noFollow":0},"content":"SQL is a popular and useful programming language. You can make SQL even more useful if you know the phases of SQL development, the criteria for normal forms, the data types used by SQL, a little bit about set and value functions, as well as some tips on how to filter tables with <a href=\"https://www.dummies.com/programming/php/knowing-the-mysql-where-clause-format/\" target=\"_blank\" rel=\"noopener\">WHERE clauses</a>.","description":"SQL is a popular and useful programming language. You can make SQL even more useful if you know the phases of SQL development, the criteria for normal forms, the data types used by SQL, a little bit about set and value functions, as well as some tips on how to filter tables with <a href=\"https://www.dummies.com/programming/php/knowing-the-mysql-where-clause-format/\" target=\"_blank\" rel=\"noopener\">WHERE clauses</a>.","blurb":"","authors":[{"authorId":9559,"name":"Allen G. Taylor","slug":"allen-g-taylor","description":"Allen G. Taylor is a 40-year veteran of the computer industry and the author of more than 40 books, including SQL For Dummies and Database Development For Dummies. For the latest news on Taylor's activities, check out his online courses (at pioneer-academy1.teachable.com).","_links":{"self":"https://dummies-api.dummies.com/v2/authors/9559"}}],"primaryCategoryTaxonomy":{"categoryId":33608,"title":"SQL","slug":"sql","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33608"}},"secondaryCategoryTaxonomy":{"categoryId":0,"title":null,"slug":null,"_links":null},"tertiaryCategoryTaxonomy":{"categoryId":0,"title":null,"slug":null,"_links":null},"trendingArticles":null,"inThisArticle":[],"relatedArticles":{"fromBook":[{"articleId":188699,"title":"SQL Data Types","slug":"sql-data-types","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/188699"}},{"articleId":188700,"title":"SQL Criteria for Normal Forms","slug":"sql-criteria-for-normal-forms","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/188700"}}],"fromCategory":[{"articleId":260869,"title":"Using SQL Constraints Within Transactions","slug":"using-sql-constraints-within-transactions","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260869"}},{"articleId":260866,"title":"How to Use the SQL Union Join","slug":"how-to-use-the-sql-union-join","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260866"}},{"articleId":260727,"title":"How to Declare a SQL Cursor","slug":"how-to-declare-a-sql-cursor","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260727"}},{"articleId":260720,"title":"What is SQL?","slug":"what-is-sql","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260720"}},{"articleId":260678,"title":"ODBC: Interfacing with SQL Databases","slug":"odbc-interfacing-with-sql-databases","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260678"}}]},"hasRelatedBookFromSearch":false,"relatedBook":{"bookId":281870,"slug":"sql-all-in-one-for-dummies-3rd-edition","isbn":"9781119569619","categoryList":["technology","programming-web-design","sql"],"amazon":{"default":"https://www.amazon.com/gp/product/1119569613/ref=as_li_tl?ie=UTF8&tag=wiley01-20","ca":"https://www.amazon.ca/gp/product/1119569613/ref=as_li_tl?ie=UTF8&tag=wiley01-20","indigo_ca":"http://www.tkqlhce.com/click-9208661-13710633?url=https://www.chapters.indigo.ca/en-ca/books/product/1119569613-item.html&cjsku=978111945484","gb":"https://www.amazon.co.uk/gp/product/1119569613/ref=as_li_tl?ie=UTF8&tag=wiley01-20","de":"https://www.amazon.de/gp/product/1119569613/ref=as_li_tl?ie=UTF8&tag=wiley01-20"},"image":{"src":"https://www.dummies.com/wp-content/uploads/sql-all-in-one-for-dummies-3rd-edition-cover-9781119569619-203x255.jpg","width":203,"height":255},"title":"SQL All-in-One For Dummies","testBankPinActivationLink":"","bookOutOfPrint":false,"authorsInfo":"<p><b data-author-id=\"34913\">Allen G. Taylor</b> is a 40-year veteran of the computer industry and the author of more than 40 books, including <i>SQL For Dummies</i> and <i>Database Development For Dummies</i>. For the latest news on Allen's activities, check out his online courses (at pioneer-academy1.teachable.com) and his blog (at www.allengtaylor.com). You can contact Allen at [email protected] </p>","authors":[{"authorId":34913,"name":"Allen G. Taylor","slug":"allen-g.-taylor","description":" <p><b>Allen G. Taylor</b> is a 30&#45;year veteran of the computer industry and the author of over 40 books, including <b><i>SQL For Dummies</i></b> and <i>Crystal Reports For Dummies.</i> He lectures nationally on databases, innovation, and entrepreneurship. He also teaches database development internationally through a leading online education provider. ","_links":{"self":"https://dummies-api.dummies.com/v2/authors/34913"}}],"_links":{"self":"https://dummies-api.dummies.com/v2/books/"}},"collections":[],"articleAds":{"footerAd":"<div class=\"du-ad-region row\" id=\"article_page_adhesion_ad\"><div class=\"du-ad-unit col-md-12\" data-slot-id=\"article_page_adhesion_ad\" data-refreshed=\"false\" \r\n data-target = \"[{&quot;key&quot;:&quot;cat&quot;,&quot;values&quot;:[&quot;technology&quot;,&quot;programming-web-design&quot;,&quot;sql&quot;]},{&quot;key&quot;:&quot;isbn&quot;,&quot;values&quot;:[&quot;9781119569619&quot;]}]\" id=\"du-slot-62b36f8244ee4\"></div></div>","rightAd":"<div class=\"du-ad-region row\" id=\"article_page_right_ad\"><div class=\"du-ad-unit col-md-12\" data-slot-id=\"article_page_right_ad\" data-refreshed=\"false\" \r\n data-target = \"[{&quot;key&quot;:&quot;cat&quot;,&quot;values&quot;:[&quot;technology&quot;,&quot;programming-web-design&quot;,&quot;sql&quot;]},{&quot;key&quot;:&quot;isbn&quot;,&quot;values&quot;:[&quot;9781119569619&quot;]}]\" id=\"du-slot-62b36f824561e\"></div></div>"},"articleType":{"articleType":"Cheat Sheet","articleList":[{"articleId":188695,"title":"Phases of SQL System Development","slug":"phases-of-sql-system-development","categoryList":[],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/188695"}},{"articleId":188700,"title":"SQL Criteria for Normal Forms","slug":"sql-criteria-for-normal-forms","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/188700"}},{"articleId":188699,"title":"SQL Data Types","slug":"sql-data-types","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/188699"}},{"articleId":188693,"title":"SQL Value Functions","slug":"sql-value-functions","categoryList":[],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/188693"}},{"articleId":188692,"title":"SQL Set Functions","slug":"sql-set-functions","categoryList":[],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/188692"}},{"articleId":188697,"title":"SQL WHERE Clause Predicates","slug":"sql-where-clause-predicates","categoryList":[],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/188697"}}],"content":[{"title":"Phases of SQL system development","thumb":null,"image":null,"content":"<p>In developing any system, you start at the beginning and go through to the end, and it&#8217;s no different with SQL. The following list shows you what to consider at each phase of the SQL development lifecycle:</p>\n<ul class=\"level-one\">\n<li>\n<p class=\"first-para\"><b>Definition Phase:</b> Precisely define the problem to be solved, its magnitude, and who will work on it.</p>\n</li>\n<li>\n<p class=\"first-para\"><b>Requirements Phase: </b>Develop a detailed description of exactly what the development effort will produce. Gather all relevant information and put it into a requirements document (Statement of Requirements). Get client signoff.</p>\n</li>\n<li>\n<p class=\"first-para\"><b>Evaluation Phase:</b> Determine exactly how you will meet the requirements. What tools will you use? How will you deploy your development team? Determine whether the job is doable within time and budget constraints.</p>\n</li>\n<li>\n<p class=\"first-para\"><b>Design Phase:</b> Create a database model and then design a database and database application that satisfy the terms of the requirements document.</p>\n</li>\n<li>\n<p class=\"first-para\"><b>Implementation Phase:</b> Build the database and the database application. Include copious documentation within the code and in external documents.</p>\n</li>\n<li>\n<p class=\"first-para\"><b>Final Documentation and Testing Phase:</b> Give the database and application a tough workout. Hit the system with every conceivable input condition and a few inconceivable ones. Try to overload it. See where it breaks. When it breaks, send it back to the implementers or even back to the designers. Document everything.</p>\n</li>\n<li>\n<p class=\"first-para\"><b>Maintenance Phase:</b> Fix latent bugs as they arise. Provide updates and enhancements called for by the client.</p>\n</li>\n</ul>\n"},{"title":"SQL criteria for normal forms","thumb":null,"image":null,"content":"<p>In SQL, normal forms are defining characteristics of relational databases. SQL forms get classified according to the types of modification anomalies they&#8217;re subject to. First, second, and third normal forms (1NF, 2NF, 3NF) serve as remedies to the three main sources of modification anomalies.</p>\n<p>The normal forms are nested in the sense that a table that&#8217;s in 2NF is automatically also in 1NF. Similarly, a table in 3NF is automatically in 2NF, and so on. For most practical applications, putting a database in 3NF is sufficient to ensure a high degree of integrity. To be absolutely sure of its integrity, you must put the database into DK/NF.</p>\n<p>The following lists lay out the criteria for each form:</p>\n<p><b>First Normal Form (1NF):</b></p>\n<ul class=\"level-one\">\n<li>\n<p class=\"first-para\">Table must be two-dimensional, with rows and columns.</p>\n</li>\n<li>\n<p class=\"first-para\">Each row contains data that pertains to one thing or one portion of a thing.</p>\n</li>\n<li>\n<p class=\"first-para\">Each column contains data for a single attribute of the thing being described.</p>\n</li>\n<li>\n<p class=\"first-para\">Each cell (intersection of row and column) of the table must be single-valued.</p>\n</li>\n<li>\n<p class=\"first-para\">All entries in a column must be of the same kind.</p>\n</li>\n<li>\n<p class=\"first-para\">Each column must have a unique name.</p>\n</li>\n<li>\n<p class=\"first-para\">No two rows may be identical.</p>\n</li>\n<li>\n<p class=\"first-para\">The order of the columns and of the rows does not matter.</p>\n</li>\n</ul>\n<p><b>Second Normal Form (2NF):</b></p>\n<ul class=\"level-one\">\n<li>\n<p class=\"first-para\">Table must be in first normal form (1NF).</p>\n</li>\n<li>\n<p class=\"first-para\">All non-key attributes (columns) must be dependent on the entire key.</p>\n</li>\n</ul>\n<p><b>Third Normal Form (3NF):</b></p>\n<ul class=\"level-one\">\n<li>\n<p class=\"first-para\">Table must be in second normal form (2NF).</p>\n</li>\n<li>\n<p class=\"first-para\">Table has no transitive dependencies.</p>\n</li>\n</ul>\n<p><b>Domain-Key Normal Form (DK/NF):</b></p>\n<ul class=\"level-one\">\n<li>\n<p class=\"first-para\">Every constraint on the table is a logical consequence of the definition of keys and domains.</p>\n</li>\n</ul>\n"},{"title":"SQL data types","thumb":null,"image":null,"content":"<p>Depending on their histories, different SQL implementations support a variety of data types. The SQL specification recognizes nine predefined general types, shown in the lists below</p>\n<p><b>Exact Numerics:</b></p>\n<ul class=\"level-one\">\n<li>\n<p class=\"first-para\">INTEGER</p>\n</li>\n<li>\n<p class=\"first-para\">SMALLINT</p>\n</li>\n<li>\n<p class=\"first-para\">BIGINT</p>\n</li>\n<li>\n<p class=\"first-para\">NUMERIC</p>\n</li>\n<li>\n<p class=\"first-para\">DECIMAL</p>\n</li>\n<li>DECFLOAT</li>\n</ul>\n<p><b>Approximate Numerics:</b></p>\n<ul class=\"level-one\">\n<li>\n<p class=\"first-para\">REAL</p>\n</li>\n<li>\n<p class=\"first-para\">DOUBLE PRECISION</p>\n</li>\n<li>\n<p class=\"first-para\">FLOAT</p>\n</li>\n</ul>\n<p><b>Boolean:</b></p>\n<ul class=\"level-one\">\n<li>\n<p class=\"first-para\">BOOLEAN</p>\n</li>\n</ul>\n<p><b>Character Strings:</b></p>\n<ul class=\"level-one\">\n<li>\n<p class=\"first-para\">CHARACTER (CHAR)</p>\n</li>\n<li>\n<p class=\"first-para\">CHARACTER VARYING (VARCHAR)</p>\n</li>\n<li>\n<p class=\"first-para\">NATIONAL CHARACTER (NCHAR)</p>\n</li>\n<li>\n<p class=\"first-para\">NATIONAL CHARACTER VARYING (NVARCHAR)</p>\n</li>\n</ul>\n<p><b>Datetimes:</b></p>\n<ul class=\"level-one\">\n<li>\n<p class=\"first-para\">DATE</p>\n</li>\n<li>\n<p class=\"first-para\">TIME</p>\n</li>\n<li>\n<p class=\"first-para\">TIMESTAMP</p>\n</li>\n<li>\n<p class=\"first-para\">TIME WITH TIMEZONE</p>\n</li>\n<li>\n<p class=\"first-para\">TIMESTAMP WITH TIMEZONE</p>\n</li>\n</ul>\n<p><b>Intervals:</b></p>\n<ul class=\"level-one\">\n<li>\n<p class=\"first-para\">INTERVAL DAY</p>\n</li>\n<li>\n<p class=\"first-para\">INTERVAL YEAR</p>\n</li>\n</ul>\n<p><b>Large Objects:</b></p>\n<ul class=\"level-one\">\n<li>\n<p class=\"first-para\">BLOB</p>\n</li>\n<li>\n<p class=\"first-para\">CLOB</p>\n</li>\n</ul>\n<p><b>Collection Types:</b></p>\n<ul class=\"level-one\">\n<li>\n<p class=\"first-para\">ARRAY</p>\n</li>\n<li>\n<p class=\"first-para\">MULTISET</p>\n</li>\n</ul>\n<p><b>Other Types:</b></p>\n<ul class=\"level-one\">\n<li>\n<p class=\"first-para\">ROW</p>\n</li>\n<li>\n<p class=\"first-para\">XML</p>\n</li>\n</ul>\n"},{"title":"SQL value functions","thumb":null,"image":null,"content":"<p>These SQL value functions perform operations on data. There are all kinds of operations that could conceivably be performed on data items, but these are some that are needed most often.</p>\n<table>\n<caption>String Value Functions</caption>\n<tbody>\n<tr>\n<th>Function</th>\n<th>Effect</th>\n</tr>\n<tr>\n<td>SUBSTRING</td>\n<td>Extracts a substring from a source string</td>\n</tr>\n<tr>\n<td>SUBSTRING SIMILAR</td>\n<td>Extracts a substring from a source string, using POSIX-based<br />\nregular expressions</td>\n</tr>\n<tr>\n<td>SUBSTRING_REGEX</td>\n<td>Extracts from a string the first occurrence of an XQuery<br />\nregular expression pattern and returns one occurrence of the<br />\nmatching substring</td>\n</tr>\n<tr>\n<td>TRANSLATE_REGEX</td>\n<td>Extracts from a string the first or every occurrence of an<br />\nXQuery regular expression pattern and replaces it or them with an XQuery replacement string</td>\n</tr>\n<tr>\n<td>UPPER</td>\n<td>Converts a character string to all uppercase</td>\n</tr>\n<tr>\n<td>LOWER</td>\n<td>Converts a character string to all lowercase</td>\n</tr>\n<tr>\n<td>TRIM</td>\n<td>Trims off leading or trailing blanks</td>\n</tr>\n<tr>\n<td>TRANSLATE</td>\n<td>Transforms a source string from one character set to<br />\nanother</td>\n</tr>\n<tr>\n<td>CONVERT</td>\n<td>Transforms a source string from one character set to<br />\nanother</td>\n</tr>\n</tbody>\n</table>\n<table>\n<caption>Numeric Value Functions</caption>\n<tbody>\n<tr>\n<th>Function</th>\n<th>Effect</th>\n</tr>\n<tr>\n<td>POSITION</td>\n<td>Returns the starting position of a target string within a<br />\nsource string</td>\n</tr>\n<tr>\n<td>CHARACTER_LENGTH</td>\n<td>Returns the number of characters in a string</td>\n</tr>\n<tr>\n<td>OCTET_LENGTH</td>\n<td>Returns the number of octets (bytes) in a character string</td>\n</tr>\n<tr>\n<td>EXTRACT</td>\n<td>Extracts a single field from a datetime or interval</td>\n</tr>\n</tbody>\n</table>\n<table>\n<caption>Datetime Value Functions</caption>\n<tbody>\n<tr>\n<th>Function</th>\n<th>Effect</th>\n</tr>\n<tr>\n<td>CURRENT_DATE</td>\n<td>Returns the current date</td>\n</tr>\n<tr>\n<td>CURRENT_TIME(p)</td>\n<td>Returns the current time; (p) is precision of seconds</td>\n</tr>\n<tr>\n<td>CURRENT_TIMESTAMP(p)</td>\n<td>Returns the current date and the current time; (p) is precision of seconds</td>\n</tr>\n</tbody>\n</table>\n"},{"title":"SQL set functions","thumb":null,"image":null,"content":"<p>The SQL set functions give you a quick answer to questions you may have about the characteristics of your data as a whole. How many rows does a table have? What is the highest value in the table? What is the lowest? These are the kinds of questions that the SQL set functions can answer for you.</p>\n<table width=\"586\">\n<tbody>\n<tr>\n<td width=\"181\">Function</td>\n<td width=\"399\">Effect</td>\n</tr>\n<tr>\n<td width=\"181\"><code>COUNT</code></td>\n<td width=\"399\">Returns the number of rows in the specified table</td>\n</tr>\n<tr>\n<td width=\"181\"><code>MAX</code></td>\n<td width=\"399\">Returns the maximum value that occurs in the specified able</td>\n</tr>\n<tr>\n<td width=\"181\"><code>MIN</code></td>\n<td width=\"399\">Returns the minimum value that occurs in the specified table</td>\n</tr>\n<tr>\n<td width=\"181\"><code>SUM</code></td>\n<td width=\"399\">Adds up the values in a specified column</td>\n</tr>\n<tr>\n<td width=\"181\"><code>AVG</code></td>\n<td width=\"399\">Returns the average of all the values in the specified column</td>\n</tr>\n<tr>\n<td width=\"181\"><code>LISTAGG</code></td>\n<td width=\"399\">Transforms values from a group of rows into a delimited string</td>\n</tr>\n</tbody>\n</table>\n<h2><span style=\"color: #000000; font-family: Arial Black;\">Trigonometric and Logarithmic Functions</span></h2>\n<p><code>sin</code>, <code>cos</code>, <code>tan</code>, <code>asin</code>, <code>acos</code>, <code>atan</code>, <code>sinh</code>, <code>cosh</code>, <code>tanh</code>, <code>log(&lt;base&gt;, &lt;value&gt;)</code>, <code>log10(&lt;value&gt;)</code>. <code>ln( &lt;value&gt;)</code></p>\n<h2><span style=\"color: #000000; font-family: Arial Black;\">JSON Constructor Functions</span></h2>\n<p><code>JSON_OBJECT</code></p>\n<p><code>JSON_ARRAY</code></p>\n<p><code>JSON_OBJECTAGG</code></p>\n<p><code>JSON_ARRAYAGG</code></p>\n<h2><span style=\"color: #000000; font-family: Arial Black;\">JSON Query Functions</span></h2>\n<p><code>JSON_EXISTS</code></p>\n<p><code>JSON_VALUE</code></p>\n<p><code>JSON_QUERY</code></p>\n<p><code>JSON_TABLE</code></p>\n"},{"title":"SQL WHERE clause predicates","thumb":null,"image":null,"content":"<p>Predicates boil down to either a TRUE or a FALSE result. You can filter out unwanted rows from the result of an SQL query by applying a WHERE clause whose predicate excludes the unwanted rows.</p>\n<table border=\"0\">\n<caption>Comparison Predicates    </caption>\n<tbody>\n<tr>\n<td>=</td>\n<td>Equal</td>\n</tr>\n<tr>\n<td>&lt;&gt;</td>\n<td>Not equal</td>\n</tr>\n<tr>\n<td>&lt;</td>\n<td>Less than</td>\n</tr>\n<tr>\n<td>&lt;=</td>\n<td>Less than or equal</td>\n</tr>\n<tr>\n<td>&gt;</td>\n<td>Greater than</td>\n</tr>\n<tr>\n<td>&gt;=</td>\n<td>Greater than or equal</td>\n</tr>\n</tbody>\n</table>\n<table border=\"0\">\n<caption>Other Predicates    </caption>\n<tbody>\n<tr>\n<td>ALL</td>\n<td>BETWEEN</td>\n</tr>\n<tr>\n<td>DISTINCT</td>\n<td>EXISTS</td>\n</tr>\n<tr>\n<td>IN</td>\n<td>LIKE</td>\n</tr>\n<tr>\n<td>MATCH</td>\n<td>NOT IN</td>\n</tr>\n<tr>\n<td>NOT LIKE</td>\n<td>NULL</td>\n</tr>\n<tr>\n<td>OVERLAPS</td>\n<td>SOME, ANY</td>\n</tr>\n<tr>\n<td>UNIQUE</td>\n<td></td>\n</tr>\n</tbody>\n</table>\n"}],"videoInfo":{"videoId":null,"name":null,"accountId":null,"playerId":null,"thumbnailUrl":null,"description":null,"uploadDate":null}},"sponsorship":{"sponsorshipPage":false,"backgroundImage":{"src":null,"width":0,"height":0},"brandingLine":"","brandingLink":"","brandingLogo":{"src":null,"width":0,"height":0},"sponsorAd":null,"sponsorEbookTitle":null,"sponsorEbookLink":null,"sponsorEbookImage":null},"primaryLearningPath":"Advance","lifeExpectancy":"One year","lifeExpectancySetFrom":"2022-03-09T00:00:00+00:00","dummiesForKids":"no","sponsoredContent":"no","adInfo":"","adPairKey":[]},"status":"publish","visibility":"public","articleId":208690},{"headers":{"creationTime":"2016-03-27T16:57:23+00:00","modifiedTime":"2022-01-27T15:37:12+00:00","timestamp":"2022-06-22T19:37:09+00:00"},"data":{"breadcrumbs":[{"name":"Technology","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33512"},"slug":"technology","categoryId":33512},{"name":"Programming & Web Design","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33592"},"slug":"programming-web-design","categoryId":33592},{"name":"SQL","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33608"},"slug":"sql","categoryId":33608}],"title":"SQL For Dummies Cheat Sheet","strippedTitle":"sql for dummies cheat sheet","slug":"sql-for-dummies-cheat-sheet","canonicalUrl":"","seo":{"metaDescription":"Keep this Cheat Sheet handy for SQL criteria, data types, value and set functions, and other information you'll need at your fingertips.","noIndex":0,"noFollow":0},"content":"This Cheat Sheet consists of several helpful tables and lists, containing information that comes up repeatedly when working with structured query language (SQL). In one place, you can get a quick answer to a number of different questions that frequently arise during an SQL development effort.","description":"This Cheat Sheet consists of several helpful tables and lists, containing information that comes up repeatedly when working with structured query language (SQL). In one place, you can get a quick answer to a number of different questions that frequently arise during an SQL development effort.","blurb":"","authors":[{"authorId":9559,"name":"Allen G. Taylor","slug":"allen-g-taylor","description":"Allen G. Taylor is a 40-year veteran of the computer industry and the author of more than 40 books, including SQL For Dummies and Database Development For Dummies. For the latest news on Taylor's activities, check out his online courses (at pioneer-academy1.teachable.com).","_links":{"self":"https://dummies-api.dummies.com/v2/authors/9559"}}],"primaryCategoryTaxonomy":{"categoryId":33608,"title":"SQL","slug":"sql","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33608"}},"secondaryCategoryTaxonomy":{"categoryId":0,"title":null,"slug":null,"_links":null},"tertiaryCategoryTaxonomy":{"categoryId":0,"title":null,"slug":null,"_links":null},"trendingArticles":null,"inThisArticle":[],"relatedArticles":{"fromBook":[{"articleId":260869,"title":"Using SQL Constraints Within Transactions","slug":"using-sql-constraints-within-transactions","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260869"}},{"articleId":260866,"title":"How to Use the SQL Union Join","slug":"how-to-use-the-sql-union-join","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260866"}},{"articleId":260727,"title":"How to Declare a SQL Cursor","slug":"how-to-declare-a-sql-cursor","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260727"}},{"articleId":260720,"title":"What is SQL?","slug":"what-is-sql","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260720"}},{"articleId":260678,"title":"ODBC: Interfacing with SQL Databases","slug":"odbc-interfacing-with-sql-databases","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260678"}}],"fromCategory":[{"articleId":260869,"title":"Using SQL Constraints Within Transactions","slug":"using-sql-constraints-within-transactions","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260869"}},{"articleId":260866,"title":"How to Use the SQL Union Join","slug":"how-to-use-the-sql-union-join","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260866"}},{"articleId":260727,"title":"How to Declare a SQL Cursor","slug":"how-to-declare-a-sql-cursor","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260727"}},{"articleId":260720,"title":"What is SQL?","slug":"what-is-sql","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260720"}},{"articleId":260678,"title":"ODBC: Interfacing with SQL Databases","slug":"odbc-interfacing-with-sql-databases","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260678"}}]},"hasRelatedBookFromSearch":false,"relatedBook":{"bookId":281871,"slug":"sql-for-dummies-9th-edition","isbn":"9781119527077","categoryList":["technology","programming-web-design","sql"],"amazon":{"default":"https://www.amazon.com/gp/product/1119527074/ref=as_li_tl?ie=UTF8&tag=wiley01-20","ca":"https://www.amazon.ca/gp/product/1119527074/ref=as_li_tl?ie=UTF8&tag=wiley01-20","indigo_ca":"http://www.tkqlhce.com/click-9208661-13710633?url=https://www.chapters.indigo.ca/en-ca/books/product/1119527074-item.html&cjsku=978111945484","gb":"https://www.amazon.co.uk/gp/product/1119527074/ref=as_li_tl?ie=UTF8&tag=wiley01-20","de":"https://www.amazon.de/gp/product/1119527074/ref=as_li_tl?ie=UTF8&tag=wiley01-20"},"image":{"src":"https://www.dummies.com/wp-content/uploads/sql-for-dummies-9th-edition-cover-9781119527077-203x255.jpg","width":203,"height":255},"title":"SQL For Dummies","testBankPinActivationLink":"","bookOutOfPrint":false,"authorsInfo":"<p><b data-author-id=\"34913\">Allen G. Taylor</b> is a 30-year veteran of the computer industry and the author of over 40 books, including <b><i>SQL For Dummies</i></b> and <i>Crystal Reports For Dummies.</i> He lectures nationally on databases, innovation, and entrepreneurship. He also teaches database development internationally through a leading online education provider. </p>","authors":[{"authorId":34913,"name":"Allen G. Taylor","slug":"allen-g.-taylor","description":" <p><b>Allen G. Taylor</b> is a 30&#45;year veteran of the computer industry and the author of over 40 books, including <b><i>SQL For Dummies</i></b> and <i>Crystal Reports For Dummies.</i> He lectures nationally on databases, innovation, and entrepreneurship. He also teaches database development internationally through a leading online education provider. ","_links":{"self":"https://dummies-api.dummies.com/v2/authors/34913"}}],"_links":{"self":"https://dummies-api.dummies.com/v2/books/"}},"collections":[],"articleAds":{"footerAd":"<div class=\"du-ad-region row\" id=\"article_page_adhesion_ad\"><div class=\"du-ad-unit col-md-12\" data-slot-id=\"article_page_adhesion_ad\" data-refreshed=\"false\" \r\n data-target = \"[{&quot;key&quot;:&quot;cat&quot;,&quot;values&quot;:[&quot;technology&quot;,&quot;programming-web-design&quot;,&quot;sql&quot;]},{&quot;key&quot;:&quot;isbn&quot;,&quot;values&quot;:[&quot;9781119527077&quot;]}]\" id=\"du-slot-62b36f65e5061\"></div></div>","rightAd":"<div class=\"du-ad-region row\" id=\"article_page_right_ad\"><div class=\"du-ad-unit col-md-12\" data-slot-id=\"article_page_right_ad\" data-refreshed=\"false\" \r\n data-target = \"[{&quot;key&quot;:&quot;cat&quot;,&quot;values&quot;:[&quot;technology&quot;,&quot;programming-web-design&quot;,&quot;sql&quot;]},{&quot;key&quot;:&quot;isbn&quot;,&quot;values&quot;:[&quot;9781119527077&quot;]}]\" id=\"du-slot-62b36f65e580b\"></div></div>"},"articleType":{"articleType":"Cheat Sheet","articleList":[{"articleId":194143,"title":"SQL Criteria for Normal Forms","slug":"sql-criteria-for-normal-forms-2","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/194143"}},{"articleId":194141,"title":"SQL Data Types","slug":"sql-data-types-2","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/194141"}},{"articleId":194135,"title":"SQL Value Functions","slug":"sql-value-functions-2","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/194135"}},{"articleId":194138,"title":"SQL Set Functions","slug":"sql-set-functions-2","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/194138"}},{"articleId":194148,"title":"SQL WHERE Clause Predicates","slug":"sql-where-clause-predicates-2","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/194148"}}],"content":[{"title":"SQL criteria for normal forms","thumb":null,"image":null,"content":"<p>To ensure that database tables are designed in such a way that they will hold your data reliably, you need to be sure that they are not subject to modification anomalies. Normalizing your databases will give you that assurance. Compare the SQL criteria in the following list to the tables in your database. Doing so will alert you to the possibility of anomalies, when you find that your database is not sufficiently normalized.</p>\n<p>First Normal Form (1NF):</p>\n<ul class=\"level-one\">\n<li>\n<p class=\"first-para\">Table must be two-dimensional, with rows and columns.</p>\n</li>\n<li>\n<p class=\"first-para\">Each row contains data that pertains to one thing or one portion of a thing.</p>\n</li>\n<li>\n<p class=\"first-para\">Each column contains data for a single attribute of the thing being described.</p>\n</li>\n<li>\n<p class=\"first-para\">Each cell (intersection of row and column) of the table must be single-valued.</p>\n</li>\n<li>\n<p class=\"first-para\">All entries in a column must be of the same kind.</p>\n</li>\n<li>\n<p class=\"first-para\">Each column must have a unique name.</p>\n</li>\n<li>\n<p class=\"first-para\">No two rows may be identical.</p>\n</li>\n<li>\n<p class=\"first-para\">The order of the columns and of the rows does not matter.</p>\n</li>\n</ul>\n<p>Second Normal Form (2NF):</p>\n<ul class=\"level-one\">\n<li>\n<p class=\"first-para\">Table must be in first normal form (1NF).</p>\n</li>\n<li>\n<p class=\"first-para\">All non-key attributes (columns) must be dependent on the entire key.</p>\n</li>\n</ul>\n<p>Third Normal Form (3NF):</p>\n<ul class=\"level-one\">\n<li>\n<p class=\"first-para\">Table must be in second normal form (2NF).</p>\n</li>\n<li>\n<p class=\"first-para\">Table has no transitive dependencies.</p>\n</li>\n</ul>\n<p>Domain-Key Normal Form (DK/NF):</p>\n<ul class=\"level-one\">\n<li>\n<p class=\"first-para\">Every constraint on the table is a logical consequence of the definition of keys and domains.</p>\n</li>\n</ul>\n"},{"title":"SQL data types","thumb":null,"image":null,"content":"<p>Here’s a list of all the formal data types that ISO/IEC standard SQL recognizes. In addition to these, you may define additional data types that are derived from these.</p>\n<p>Exact Numerics:</p>\n<ul class=\"level-one\">\n<li>\n<p class=\"first-para\">INTEGER</p>\n</li>\n<li>\n<p class=\"first-para\">SMALLINT</p>\n</li>\n<li>\n<p class=\"first-para\">BIGINT</p>\n</li>\n<li>\n<p class=\"first-para\">NUMERIC</p>\n</li>\n<li>\n<p class=\"first-para\">DECIMAL</p>\n</li>\n</ul>\n<p>Approximate Numerics:</p>\n<ul class=\"level-one\">\n<li>\n<p class=\"first-para\">REAL</p>\n</li>\n<li>\n<p class=\"first-para\">DOUBLE PRECISION</p>\n</li>\n<li>\n<p class=\"first-para\">FLOAT</p>\n</li>\n<li>DECFLOAT</li>\n</ul>\n<p>Binary Strings:</p>\n<ul class=\"level-one\">\n<li>\n<p class=\"first-para\">BINARY</p>\n</li>\n<li>\n<p class=\"first-para\">BINARY VARYING</p>\n</li>\n<li>\n<p class=\"first-para\">BINARY LARGE OBJECT</p>\n</li>\n</ul>\n<p>Boolean:</p>\n<ul class=\"level-one\">\n<li>\n<p class=\"first-para\">BOOLEAN</p>\n</li>\n</ul>\n<p>Character Strings:</p>\n<ul class=\"level-one\">\n<li>\n<p class=\"first-para\">CHARACTER</p>\n</li>\n<li>\n<p class=\"first-para\">CHARACTER VARYING (VARCHAR)</p>\n</li>\n<li>\n<p class=\"first-para\">CHARACTER LARGE OBJECT</p>\n</li>\n<li>\n<p class=\"first-para\">NATIONAL CHARACTER</p>\n</li>\n<li>\n<p class=\"first-para\">NATIONAL CHARACTER VARYING</p>\n</li>\n<li>\n<p class=\"first-para\">NATIONAL CHARACTER LARGE OBJECT</p>\n</li>\n</ul>\n<p>Datetimes:</p>\n<ul class=\"level-one\">\n<li>\n<p class=\"first-para\">DATE</p>\n</li>\n<li>\n<p class=\"first-para\">TIME WITHOUT TIMEZONE</p>\n</li>\n<li>\n<p class=\"first-para\">TIMESTAMP WITHOUT TIMEZONE</p>\n</li>\n<li>\n<p class=\"first-para\">TIME WITH TIMEZONE</p>\n</li>\n<li>\n<p class=\"first-para\">TIMESTAMP WITH TIMEZONE</p>\n</li>\n</ul>\n<p>Intervals:</p>\n<ul class=\"level-one\">\n<li>\n<p class=\"first-para\">INTERVAL DAY</p>\n</li>\n<li>\n<p class=\"first-para\">INTERVAL YEAR</p>\n</li>\n</ul>\n<p>Collection Types:</p>\n<ul class=\"level-one\">\n<li>\n<p class=\"first-para\">ARRAY</p>\n</li>\n<li>\n<p class=\"first-para\">MULTISET</p>\n</li>\n</ul>\n<p>Other Types:</p>\n<ul class=\"level-one\">\n<li>\n<p class=\"first-para\">ROW</p>\n</li>\n<li>\n<p class=\"first-para\">XML</p>\n</li>\n</ul>\n"},{"title":"SQL value functions","thumb":null,"image":null,"content":"<p>These SQL value functions perform operations on data. There are all kinds of operations that could conceivably be performed on data items, but these are some that are needed most often.</p>\n<table>\n<caption>String Value Functions</caption>\n<tbody>\n<tr>\n<th>Function</th>\n<th>Effect</th>\n</tr>\n<tr>\n<td>SUBSTRING</td>\n<td>Extracts a substring from a source string</td>\n</tr>\n<tr>\n<td>SUBSTRING SIMILAR</td>\n<td>Extracts a substring from a source string, using POSIX-based<br />\nregular expressions</td>\n</tr>\n<tr>\n<td>SUBSTRING_REGEX</td>\n<td>Extracts from a string the first occurrence of an XQuery<br />\nregular expression pattern and returns one occurrence of the<br />\nmatching substring</td>\n</tr>\n<tr>\n<td>TRANSLATE_REGEX</td>\n<td>Extracts from a string the first or every occurrence of an<br />\nXQuery regular expression pattern and replaces it or them with an XQuery replacement string</td>\n</tr>\n<tr>\n<td>UPPER</td>\n<td>Converts a character string to all uppercase</td>\n</tr>\n<tr>\n<td>LOWER</td>\n<td>Converts a character string to all lowercase</td>\n</tr>\n<tr>\n<td>TRIM</td>\n<td>Trims off leading or trailing blanks</td>\n</tr>\n<tr>\n<td>TRANSLATE</td>\n<td>Transforms a source string from one character set to<br />\nanother</td>\n</tr>\n<tr>\n<td>CONVERT</td>\n<td>Transforms a source string from one character set to<br />\nanother</td>\n</tr>\n</tbody>\n</table>\n<table>\n<caption>Numeric Value Functions</caption>\n<tbody>\n<tr>\n<th>Function</th>\n<th>Effect</th>\n</tr>\n<tr>\n<td>POSITION</td>\n<td>Returns the starting position of a target string within a<br />\nsource string</td>\n</tr>\n<tr>\n<td>CHARACTER_LENGTH</td>\n<td>Returns the number of characters in a string</td>\n</tr>\n<tr>\n<td>OCTET_LENGTH</td>\n<td>Returns the number of octets (bytes) in a character string</td>\n</tr>\n<tr>\n<td>EXTRACT</td>\n<td>Extracts a single field from a datetime or interval</td>\n</tr>\n</tbody>\n</table>\n<table>\n<caption>Datetime Value Functions</caption>\n<tbody>\n<tr>\n<th>Function</th>\n<th>Effect</th>\n</tr>\n<tr>\n<td>CURRENT_DATE</td>\n<td>Returns the current date</td>\n</tr>\n<tr>\n<td>CURRENT_TIME(p)</td>\n<td>Returns the current time; (p) is precision of seconds</td>\n</tr>\n<tr>\n<td>CURRENT_TIMESTAMP(p)</td>\n<td>Returns the current date and the current time; (p) is precision of seconds</td>\n</tr>\n</tbody>\n</table>\n"},{"title":"SQL set functions","thumb":null,"image":null,"content":"<p>The SQL set functions give you a quick answer to questions you may have about the characteristics of your data as a whole. How many rows does a table have? What is the highest value in the table? What is the lowest? These are the kinds of questions that the SQL set functions can answer for you.</p>\n<table width=\"586\">\n<tbody>\n<tr>\n<td width=\"181\">Function</td>\n<td width=\"399\">Effect</td>\n</tr>\n<tr>\n<td width=\"181\"><code>COUNT</code></td>\n<td width=\"399\">Returns the number of rows in the specified table</td>\n</tr>\n<tr>\n<td width=\"181\"><code>MAX</code></td>\n<td width=\"399\">Returns the maximum value that occurs in the specified able</td>\n</tr>\n<tr>\n<td width=\"181\"><code>MIN</code></td>\n<td width=\"399\">Returns the minimum value that occurs in the specified table</td>\n</tr>\n<tr>\n<td width=\"181\"><code>SUM</code></td>\n<td width=\"399\">Adds up the values in a specified column</td>\n</tr>\n<tr>\n<td width=\"181\"><code>AVG</code></td>\n<td width=\"399\">Returns the average of all the values in the specified column</td>\n</tr>\n<tr>\n<td width=\"181\"><code>LISTAGG</code></td>\n<td width=\"399\">Transforms values from a group of rows into a delimited string</td>\n</tr>\n</tbody>\n</table>\n<h2><span style=\"color: #000000; font-family: Arial Black;\">Trigonometric and Logarithmic Functions</span></h2>\n<p><code>sin</code>, <code>cos</code>, <code>tan</code>, <code>asin</code>, <code>acos</code>, <code>atan</code>, <code>sinh</code>, <code>cosh</code>, <code>tanh</code>, <code>log(&lt;base&gt;, &lt;value&gt;)</code>, <code>log10(&lt;value&gt;)</code>. <code>ln( &lt;value&gt;)</code></p>\n<h2><span style=\"color: #000000; font-family: Arial Black;\">JSON Constructor Functions</span></h2>\n<p><code>JSON_OBJECT</code></p>\n<p><code>JSON_ARRAY</code></p>\n<p><code>JSON_OBJECTAGG</code></p>\n<p><code>JSON_ARRAYAGG</code></p>\n<h2><span style=\"color: #000000; font-family: Arial Black;\">JSON Query Functions</span></h2>\n<p><code>JSON_EXISTS</code></p>\n<p><code>JSON_VALUE</code></p>\n<p><code>JSON_QUERY</code></p>\n<p><code>JSON_TABLE</code></p>\n"},{"title":"Knowing the MySQL WHERE clause format","thumb":null,"image":null,"content":"<p>The <span class=\"code\">WHERE</span> clause is used to modify a <span class=\"code\">DELETE</span>, <span class=\"code\">SELECT</span>, or <span class=\"code\">UPDATE</span> <span class=\"code\">SQL</span> query. This list shows the format you can use when writing a <span class=\"code\">WHERE</span> clause:</p>\n<pre class=\"code\"><span class=\"code\">WHERE </span><span class=\"code\"><i>exp</i></span><span class=\"code\"> AND|OR </span><span class=\"code\"><i>exp</i></span><span class=\"code\"> AND|OR </span><span class=\"code\"><i>exp</i></span><span class=\"code\">…</span></pre>\n<p>where <span class=\"code\"><i>exp</i></span> can be one of the following:</p>\n<pre class=\"code\"><span class=\"code\"><i>column</i></span><span class=\"code\"> = </span><span class=\"code\"><i>value</i></span>\r\n<span class=\"code\"><i>column</i></span><span class=\"code\"> &gt; </span><span class=\"code\"><i>value</i></span>\r\n<span class=\"code\"><i>column</i></span><span class=\"code\"> &gt;= </span><span class=\"code\"><i>value</i></span>\r\n<span class=\"code\"><i>column</i></span><span class=\"code\"> &lt; </span><span class=\"code\"><i>value</i></span>\r\n<span class=\"code\"><i>column</i></span><span class=\"code\"> &lt;= </span><span class=\"code\"><i>value</i></span>\r\n<span class=\"code\"><i>column</i></span><span class=\"code\"> BETWEEN </span><span class=\"code\"><i>value1</i></span><i> </i><span class=\"code\">AND </span><span class=\"code\"><i>value2</i></span>\r\n<span class=\"code\"><i>column</i></span><span class=\"code\"> IN (</span><span class=\"code\"><i>value1</i></span><span class=\"code\">,</span><span class=\"code\"><i>value2</i></span><span class=\"code\">,…)</span>\r\n<span class=\"code\"><i>column</i></span><span class=\"code\"> NOT IN (</span><span class=\"code\"><i>value1</i></span><span class=\"code\">,</span><span class=\"code\"><i>value2</i></span><span class=\"code\">,…)</span>\r\n<span class=\"code\"><i>column</i></span><span class=\"code\"> LIKE </span><span class=\"code\"><i>value</i></span>\r\n<span class=\"code\"><i>column</i></span><span class=\"code\"> NOT LIKE </span><span class=\"code\"><i>value</i></span></pre>\n"}],"videoInfo":{"videoId":null,"name":null,"accountId":null,"playerId":null,"thumbnailUrl":null,"description":null,"uploadDate":null}},"sponsorship":{"sponsorshipPage":false,"backgroundImage":{"src":null,"width":0,"height":0},"brandingLine":"","brandingLink":"","brandingLogo":{"src":null,"width":0,"height":0},"sponsorAd":null,"sponsorEbookTitle":null,"sponsorEbookLink":null,"sponsorEbookImage":null},"primaryLearningPath":"Advance","lifeExpectancy":"One year","lifeExpectancySetFrom":"2022-01-27T00:00:00+00:00","dummiesForKids":"no","sponsoredContent":"no","adInfo":"","adPairKey":[]},"status":"publish","visibility":"public","articleId":209258},{"headers":{"creationTime":"2016-03-26T14:00:40+00:00","modifiedTime":"2022-01-18T19:15:24+00:00","timestamp":"2022-06-22T19:37:08+00:00"},"data":{"breadcrumbs":[{"name":"Technology","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33512"},"slug":"technology","categoryId":33512},{"name":"Programming & Web Design","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33592"},"slug":"programming-web-design","categoryId":33592},{"name":"SQL","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33608"},"slug":"sql","categoryId":33608}],"title":"How to Use GROUP BY, HAVING, and ORDER BY SQL Clauses","strippedTitle":"how to use group by, having, and order by sql clauses","slug":"how-to-use-group-by-having-and-order-by-sql-clauses","canonicalUrl":"","seo":{"metaDescription":"SQL gives you options for retrieving, analyzing, and displaying the information you need with the GROUP BY, HAVING, and ORDER BY clauses. Here are some examples","noIndex":0,"noFollow":0},"content":"SQL gives you options for retrieving, analyzing, and displaying the information you need with the GROUP BY, HAVING, and ORDER BY clauses. Here are some examples of how you can use them.\r\n<h2 id=\"tab1\" >GROUP BY clauses</h2>\r\nSometimes, rather than retrieving individual records, you want to know something about a group of records. The <span class=\"code\">GROUP BY</span> clause is the tool you need.\r\n\r\nSuppose you’re the sales manager of another location, and you want to look at the performance of your sales force. If you do a simple <span class=\"code\">SELECT</span>, such as the following query:\r\n<pre class=\"code\">SELECT InvoiceNo, SaleDate, Salesperson, TotalSale\r\n FROM SALES;</pre>\r\n<img src=\"https://www.dummies.com/wp-content/uploads/SQLFDFigure101.jpg\" alt=\"\" width=\"630\" height=\"414\" />\r\n\r\nThis result gives you some idea of how well your salespeople are doing because so few total sales are involved. However, in real life, a company would have many more sales — and it wouldn’t be so easy to tell whether sales objectives were being met.\r\n\r\nTo do the real analysis, you can combine the <span class=\"code\">GROUP BY</span> clause with one of the <i>aggregate </i>functions (also called <i>set functions</i>) to get a quantitative picture of sales performance. For example, you can see which salesperson is selling more of the profitable high-ticket items by using the average (<span class=\"code\">AVG</span>) function as follows:\r\n<pre class=\"code\">SELECT Salesperson, AVG(TotalSale)\r\n FROM SALES\r\n GROUP BY Salesperson;</pre>\r\nRunning the query with a different database management system would retrieve the same result, but might appear a little different.\r\n\r\n<img src=\"https://www.dummies.com/wp-content/uploads/SQLFDFigure102.jpg\" alt=\"\" width=\"630\" height=\"412\" />\r\n\r\nThe average value of Bennett’s sales is considerably higher than that of the other two salespeople. You compare total sales with a similar query:\r\n<pre class=\"code\">SELECT Salesperson, SUM(TotalSale)\r\n FROM SALES\r\n GROUP BY Salesperson;</pre>\r\n<img src=\"https://www.dummies.com/wp-content/uploads/SQLFDFigure103.jpg\" alt=\"\" width=\"630\" height=\"412\" />\r\n\r\nBennett also has the highest total sales, which is consistent with having the highest average sales.\r\n<h2 id=\"tab2\" >HAVING clauses</h2>\r\nYou can analyze the grouped data further by using the <span class=\"code\">HAVING</span> clause. The <span class=\"code\">HAVING</span> clause is a filter that acts similar to a <span class=\"code\">WHERE</span> clause, but on groups of rows rather than on individual rows. To illustrate the function of the <span class=\"code\">HAVING</span> clause, suppose the sales manager considers Bennett to be in a class by himself.\r\n\r\nHis performance distorts the overall data for the other salespeople. (Aha — a curve-wrecker.) You can exclude Bennett’s sales from the grouped data by using a <span class=\"code\">HAVING</span> clause as follows:\r\n<pre class=\"code\">SELECT Salesperson, SUM(TotalSale)\r\n FROM SALES\r\n GROUP BY Salesperson\r\n HAVING Salesperson <>'Bennett';</pre>\r\nOnly rows where the salesperson is not Bennett are considered.\r\n\r\n<img src=\"https://www.dummies.com/wp-content/uploads/SQLFDFigure104.jpg\" alt=\"\" width=\"630\" height=\"412\" />\r\n<h2 id=\"tab3\" >ORDER BY clauses</h2>\r\nUse the <span class=\"code\">ORDER BY</span> clause to display the output table of a query in either ascending or descending alphabetical order. Whereas the <span class=\"code\">GROUP BY</span> clause gathers rows into groups and sorts the groups into alphabetical order, <span class=\"code\">ORDER BY</span> sorts individual rows. The <span class=\"code\">ORDER BY</span> clause must be the last clause that you specify in a query.\r\n\r\nIf the query also contains a <span class=\"code\">GROUP BY</span> clause, the clause first arranges the output rows into groups. The <span class=\"code\">ORDER BY</span> clause then sorts the rows within each group. If you have no <span class=\"code\">GROUP BY</span> clause, then the statement considers the entire table as a group, and the <span class=\"code\">ORDER BY</span> clause sorts all its rows according to the column (or columns) that the <span class=\"code\">ORDER BY</span> clause specifies.\r\n\r\nTo illustrate this point, consider the data in the SALES table. The SALES table contains columns for <span class=\"code\">InvoiceNo</span>, <span class=\"code\">SaleDate</span>, <span class=\"code\">Salesperson</span>, and <span class=\"code\">TotalSale</span>. If you use the following example, you see all the data in the SALES table — but in an arbitrary order:\r\n<pre class=\"code\">SELECT * FROM SALES ;</pre>\r\nIn one implementation, this may be the order in which you inserted the rows in the table; in another implementation, the order may be that of the most recent updates. The order can also change unexpectedly if anyone physically reorganizes the database. That’s one reason it’s usually a good idea to specify the order in which you want the rows.\r\n\r\nYou may, for example, want to see the rows in order by the <span class=\"code\">SaleDate</span> like this:\r\n<pre class=\"code\">SELECT * FROM SALES ORDER BY SaleDate ;</pre>\r\nThis example returns all the rows in the SALES table in order by <span class=\"code\">SaleDate</span>.\r\nFor rows with the same <span class=\"code\">SaleDate</span>, the default order depends on the implementation. You can, however, specify how to sort the rows that share the same <span class=\"code\">SaleDate</span>. You may want to see the sales for each <span class=\"code\">SaleDate</span> in order by <span class=\"code\">InvoiceNo</span>, as follows:\r\n<pre class=\"code\">SELECT * FROM SALES ORDER BY SaleDate, InvoiceNo ;</pre>\r\nThis example first orders the sales by <span class=\"code\">SaleDate</span>; then for each <span class=\"code\">SaleDate</span>, it orders the sales by <span class=\"code\">InvoiceNo</span>. But don’t confuse that example with the following query:\r\n<pre class=\"code\">SELECT * FROM SALES ORDER BY InvoiceNo, SaleDate ;</pre>\r\nThis query first orders the sales by <span class=\"code\">INVOICE_NO</span>. Then for each different <span class=\"code\">InvoiceNo</span>, the query orders the sales by <span class=\"code\">SaleDate</span>. This probably won’t yield the result you want, because it’s unlikely that multiple sale dates will exist for a single invoice number.\r\n\r\nThe following query is another example of how SQL can return data:\r\n<pre class=\"code\">SELECT * FROM SALES ORDER BY Salesperson, SaleDate ;</pre>\r\nThis example first orders by <span class=\"code\">Salesperson</span> and then by <span class=\"code\">SaleDate</span>. After you look at the data in that order, you may want to invert it, as follows:\r\n<pre class=\"code\">SELECT * FROM SALES ORDER BY SaleDate, Salesperson ;</pre>\r\nThis example orders the rows first by <span class=\"code\">SaleDate</span> and then by <span class=\"code\">Salesperson</span>.\r\n\r\nAll these ordering examples are in ascending (<span class=\"code\">ASC</span>) order, which is the default sort order. The last <span class=\"code\">SELECT</span> shows earlier sales first — and, within a given date, shows sales for <span class=\"code\">‘Adams’</span> before <span class=\"code\">‘Baker’</span>. If you prefer descending (<span class=\"code\">DESC</span>) order, you can specify this order for one or more of the order columns, as follows:\r\n<pre class=\"code\">SELECT * FROM SALES\r\nORDER BY SaleDate DESC, Salesperson ASC ;</pre>\r\nThis example specifies a descending order for sale dates, showing the more recent sales first, and an ascending order for salespeople, putting them in alphabetical order. That should give you a better picture of how Bennett’s performance stacks up against that of the other salespeople.","description":"SQL gives you options for retrieving, analyzing, and displaying the information you need with the GROUP BY, HAVING, and ORDER BY clauses. Here are some examples of how you can use them.\r\n<h2 id=\"tab1\" >GROUP BY clauses</h2>\r\nSometimes, rather than retrieving individual records, you want to know something about a group of records. The <span class=\"code\">GROUP BY</span> clause is the tool you need.\r\n\r\nSuppose you’re the sales manager of another location, and you want to look at the performance of your sales force. If you do a simple <span class=\"code\">SELECT</span>, such as the following query:\r\n<pre class=\"code\">SELECT InvoiceNo, SaleDate, Salesperson, TotalSale\r\n FROM SALES;</pre>\r\n<img src=\"https://www.dummies.com/wp-content/uploads/SQLFDFigure101.jpg\" alt=\"\" width=\"630\" height=\"414\" />\r\n\r\nThis result gives you some idea of how well your salespeople are doing because so few total sales are involved. However, in real life, a company would have many more sales — and it wouldn’t be so easy to tell whether sales objectives were being met.\r\n\r\nTo do the real analysis, you can combine the <span class=\"code\">GROUP BY</span> clause with one of the <i>aggregate </i>functions (also called <i>set functions</i>) to get a quantitative picture of sales performance. For example, you can see which salesperson is selling more of the profitable high-ticket items by using the average (<span class=\"code\">AVG</span>) function as follows:\r\n<pre class=\"code\">SELECT Salesperson, AVG(TotalSale)\r\n FROM SALES\r\n GROUP BY Salesperson;</pre>\r\nRunning the query with a different database management system would retrieve the same result, but might appear a little different.\r\n\r\n<img src=\"https://www.dummies.com/wp-content/uploads/SQLFDFigure102.jpg\" alt=\"\" width=\"630\" height=\"412\" />\r\n\r\nThe average value of Bennett’s sales is considerably higher than that of the other two salespeople. You compare total sales with a similar query:\r\n<pre class=\"code\">SELECT Salesperson, SUM(TotalSale)\r\n FROM SALES\r\n GROUP BY Salesperson;</pre>\r\n<img src=\"https://www.dummies.com/wp-content/uploads/SQLFDFigure103.jpg\" alt=\"\" width=\"630\" height=\"412\" />\r\n\r\nBennett also has the highest total sales, which is consistent with having the highest average sales.\r\n<h2 id=\"tab2\" >HAVING clauses</h2>\r\nYou can analyze the grouped data further by using the <span class=\"code\">HAVING</span> clause. The <span class=\"code\">HAVING</span> clause is a filter that acts similar to a <span class=\"code\">WHERE</span> clause, but on groups of rows rather than on individual rows. To illustrate the function of the <span class=\"code\">HAVING</span> clause, suppose the sales manager considers Bennett to be in a class by himself.\r\n\r\nHis performance distorts the overall data for the other salespeople. (Aha — a curve-wrecker.) You can exclude Bennett’s sales from the grouped data by using a <span class=\"code\">HAVING</span> clause as follows:\r\n<pre class=\"code\">SELECT Salesperson, SUM(TotalSale)\r\n FROM SALES\r\n GROUP BY Salesperson\r\n HAVING Salesperson <>'Bennett';</pre>\r\nOnly rows where the salesperson is not Bennett are considered.\r\n\r\n<img src=\"https://www.dummies.com/wp-content/uploads/SQLFDFigure104.jpg\" alt=\"\" width=\"630\" height=\"412\" />\r\n<h2 id=\"tab3\" >ORDER BY clauses</h2>\r\nUse the <span class=\"code\">ORDER BY</span> clause to display the output table of a query in either ascending or descending alphabetical order. Whereas the <span class=\"code\">GROUP BY</span> clause gathers rows into groups and sorts the groups into alphabetical order, <span class=\"code\">ORDER BY</span> sorts individual rows. The <span class=\"code\">ORDER BY</span> clause must be the last clause that you specify in a query.\r\n\r\nIf the query also contains a <span class=\"code\">GROUP BY</span> clause, the clause first arranges the output rows into groups. The <span class=\"code\">ORDER BY</span> clause then sorts the rows within each group. If you have no <span class=\"code\">GROUP BY</span> clause, then the statement considers the entire table as a group, and the <span class=\"code\">ORDER BY</span> clause sorts all its rows according to the column (or columns) that the <span class=\"code\">ORDER BY</span> clause specifies.\r\n\r\nTo illustrate this point, consider the data in the SALES table. The SALES table contains columns for <span class=\"code\">InvoiceNo</span>, <span class=\"code\">SaleDate</span>, <span class=\"code\">Salesperson</span>, and <span class=\"code\">TotalSale</span>. If you use the following example, you see all the data in the SALES table — but in an arbitrary order:\r\n<pre class=\"code\">SELECT * FROM SALES ;</pre>\r\nIn one implementation, this may be the order in which you inserted the rows in the table; in another implementation, the order may be that of the most recent updates. The order can also change unexpectedly if anyone physically reorganizes the database. That’s one reason it’s usually a good idea to specify the order in which you want the rows.\r\n\r\nYou may, for example, want to see the rows in order by the <span class=\"code\">SaleDate</span> like this:\r\n<pre class=\"code\">SELECT * FROM SALES ORDER BY SaleDate ;</pre>\r\nThis example returns all the rows in the SALES table in order by <span class=\"code\">SaleDate</span>.\r\nFor rows with the same <span class=\"code\">SaleDate</span>, the default order depends on the implementation. You can, however, specify how to sort the rows that share the same <span class=\"code\">SaleDate</span>. You may want to see the sales for each <span class=\"code\">SaleDate</span> in order by <span class=\"code\">InvoiceNo</span>, as follows:\r\n<pre class=\"code\">SELECT * FROM SALES ORDER BY SaleDate, InvoiceNo ;</pre>\r\nThis example first orders the sales by <span class=\"code\">SaleDate</span>; then for each <span class=\"code\">SaleDate</span>, it orders the sales by <span class=\"code\">InvoiceNo</span>. But don’t confuse that example with the following query:\r\n<pre class=\"code\">SELECT * FROM SALES ORDER BY InvoiceNo, SaleDate ;</pre>\r\nThis query first orders the sales by <span class=\"code\">INVOICE_NO</span>. Then for each different <span class=\"code\">InvoiceNo</span>, the query orders the sales by <span class=\"code\">SaleDate</span>. This probably won’t yield the result you want, because it’s unlikely that multiple sale dates will exist for a single invoice number.\r\n\r\nThe following query is another example of how SQL can return data:\r\n<pre class=\"code\">SELECT * FROM SALES ORDER BY Salesperson, SaleDate ;</pre>\r\nThis example first orders by <span class=\"code\">Salesperson</span> and then by <span class=\"code\">SaleDate</span>. After you look at the data in that order, you may want to invert it, as follows:\r\n<pre class=\"code\">SELECT * FROM SALES ORDER BY SaleDate, Salesperson ;</pre>\r\nThis example orders the rows first by <span class=\"code\">SaleDate</span> and then by <span class=\"code\">Salesperson</span>.\r\n\r\nAll these ordering examples are in ascending (<span class=\"code\">ASC</span>) order, which is the default sort order. The last <span class=\"code\">SELECT</span> shows earlier sales first — and, within a given date, shows sales for <span class=\"code\">‘Adams’</span> before <span class=\"code\">‘Baker’</span>. If you prefer descending (<span class=\"code\">DESC</span>) order, you can specify this order for one or more of the order columns, as follows:\r\n<pre class=\"code\">SELECT * FROM SALES\r\nORDER BY SaleDate DESC, Salesperson ASC ;</pre>\r\nThis example specifies a descending order for sale dates, showing the more recent sales first, and an ascending order for salespeople, putting them in alphabetical order. That should give you a better picture of how Bennett’s performance stacks up against that of the other salespeople.","blurb":"","authors":[{"authorId":9559,"name":"Allen G. Taylor","slug":"allen-g-taylor","description":"Allen G. Taylor is a 40-year veteran of the computer industry and the author of more than 40 books, including SQL For Dummies and Database Development For Dummies. For the latest news on Taylor's activities, check out his online courses (at pioneer-academy1.teachable.com).","_links":{"self":"https://dummies-api.dummies.com/v2/authors/9559"}}],"primaryCategoryTaxonomy":{"categoryId":33608,"title":"SQL","slug":"sql","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33608"}},"secondaryCategoryTaxonomy":{"categoryId":0,"title":null,"slug":null,"_links":null},"tertiaryCategoryTaxonomy":{"categoryId":0,"title":null,"slug":null,"_links":null},"trendingArticles":null,"inThisArticle":[{"label":"GROUP BY clauses","target":"#tab1"},{"label":"HAVING clauses","target":"#tab2"},{"label":"ORDER BY clauses","target":"#tab3"}],"relatedArticles":{"fromBook":[{"articleId":260869,"title":"Using SQL Constraints Within Transactions","slug":"using-sql-constraints-within-transactions","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260869"}},{"articleId":260866,"title":"How to Use the SQL Union Join","slug":"how-to-use-the-sql-union-join","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260866"}},{"articleId":260727,"title":"How to Declare a SQL Cursor","slug":"how-to-declare-a-sql-cursor","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260727"}},{"articleId":260720,"title":"What is SQL?","slug":"what-is-sql","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260720"}},{"articleId":260678,"title":"ODBC: Interfacing with SQL Databases","slug":"odbc-interfacing-with-sql-databases","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260678"}}],"fromCategory":[{"articleId":260869,"title":"Using SQL Constraints Within Transactions","slug":"using-sql-constraints-within-transactions","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260869"}},{"articleId":260866,"title":"How to Use the SQL Union Join","slug":"how-to-use-the-sql-union-join","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260866"}},{"articleId":260727,"title":"How to Declare a SQL Cursor","slug":"how-to-declare-a-sql-cursor","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260727"}},{"articleId":260720,"title":"What is SQL?","slug":"what-is-sql","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260720"}},{"articleId":260678,"title":"ODBC: Interfacing with SQL Databases","slug":"odbc-interfacing-with-sql-databases","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260678"}}]},"hasRelatedBookFromSearch":false,"relatedBook":{"bookId":281871,"slug":"sql-for-dummies-9th-edition","isbn":"9781119527077","categoryList":["technology","programming-web-design","sql"],"amazon":{"default":"https://www.amazon.com/gp/product/1119527074/ref=as_li_tl?ie=UTF8&tag=wiley01-20","ca":"https://www.amazon.ca/gp/product/1119527074/ref=as_li_tl?ie=UTF8&tag=wiley01-20","indigo_ca":"http://www.tkqlhce.com/click-9208661-13710633?url=https://www.chapters.indigo.ca/en-ca/books/product/1119527074-item.html&cjsku=978111945484","gb":"https://www.amazon.co.uk/gp/product/1119527074/ref=as_li_tl?ie=UTF8&tag=wiley01-20","de":"https://www.amazon.de/gp/product/1119527074/ref=as_li_tl?ie=UTF8&tag=wiley01-20"},"image":{"src":"https://www.dummies.com/wp-content/uploads/sql-for-dummies-9th-edition-cover-9781119527077-203x255.jpg","width":203,"height":255},"title":"SQL For Dummies","testBankPinActivationLink":"","bookOutOfPrint":false,"authorsInfo":"<p><b data-author-id=\"34913\">Allen G. Taylor</b> is a 30-year veteran of the computer industry and the author of over 40 books, including <b><i>SQL For Dummies</i></b> and <i>Crystal Reports For Dummies.</i> He lectures nationally on databases, innovation, and entrepreneurship. He also teaches database development internationally through a leading online education provider. </p>","authors":[{"authorId":34913,"name":"Allen G. Taylor","slug":"allen-g.-taylor","description":" <p><b>Allen G. Taylor</b> is a 30&#45;year veteran of the computer industry and the author of over 40 books, including <b><i>SQL For Dummies</i></b> and <i>Crystal Reports For Dummies.</i> He lectures nationally on databases, innovation, and entrepreneurship. He also teaches database development internationally through a leading online education provider. ","_links":{"self":"https://dummies-api.dummies.com/v2/authors/34913"}}],"_links":{"self":"https://dummies-api.dummies.com/v2/books/"}},"collections":[],"articleAds":{"footerAd":"<div class=\"du-ad-region row\" id=\"article_page_adhesion_ad\"><div class=\"du-ad-unit col-md-12\" data-slot-id=\"article_page_adhesion_ad\" data-refreshed=\"false\" \r\n data-target = \"[{&quot;key&quot;:&quot;cat&quot;,&quot;values&quot;:[&quot;technology&quot;,&quot;programming-web-design&quot;,&quot;sql&quot;]},{&quot;key&quot;:&quot;isbn&quot;,&quot;values&quot;:[&quot;9781119527077&quot;]}]\" id=\"du-slot-62b36f642f761\"></div></div>","rightAd":"<div class=\"du-ad-region row\" id=\"article_page_right_ad\"><div class=\"du-ad-unit col-md-12\" data-slot-id=\"article_page_right_ad\" data-refreshed=\"false\" \r\n data-target = \"[{&quot;key&quot;:&quot;cat&quot;,&quot;values&quot;:[&quot;technology&quot;,&quot;programming-web-design&quot;,&quot;sql&quot;]},{&quot;key&quot;:&quot;isbn&quot;,&quot;values&quot;:[&quot;9781119527077&quot;]}]\" id=\"du-slot-62b36f642fe9c\"></div></div>"},"articleType":{"articleType":"Articles","articleList":null,"content":null,"videoInfo":{"videoId":null,"name":null,"accountId":null,"playerId":null,"thumbnailUrl":null,"description":null,"uploadDate":null}},"sponsorship":{"sponsorshipPage":false,"backgroundImage":{"src":null,"width":0,"height":0},"brandingLine":"","brandingLink":"","brandingLogo":{"src":null,"width":0,"height":0},"sponsorAd":null,"sponsorEbookTitle":null,"sponsorEbookLink":null,"sponsorEbookImage":null},"primaryLearningPath":"Advance","lifeExpectancy":"Two years","lifeExpectancySetFrom":"2022-01-14T00:00:00+00:00","dummiesForKids":"no","sponsoredContent":"no","adInfo":"","adPairKey":[]},"status":"publish","visibility":"public","articleId":160800},{"headers":{"creationTime":"2016-03-26T14:31:51+00:00","modifiedTime":"2022-01-14T19:45:55+00:00","timestamp":"2022-06-22T19:37:07+00:00"},"data":{"breadcrumbs":[{"name":"Technology","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33512"},"slug":"technology","categoryId":33512},{"name":"Programming & Web Design","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33592"},"slug":"programming-web-design","categoryId":33592},{"name":"SQL","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33608"},"slug":"sql","categoryId":33608}],"title":"Reliably Retrieving Data with SQL","strippedTitle":"reliably retrieving data with sql","slug":"reliably-retrieving-data-with-sql","canonicalUrl":"","seo":{"metaDescription":"After you create a database in SQL, the next step is to fill it with data. The next step after that is to wait around until there's a need for some particular b","noIndex":0,"noFollow":0},"content":"After you create a database in SQL, the next step is to fill it with data. The next step after that is to wait around until there's a need for some particular bit of information contained somewhere in that data, like a needle in a haystack. When the time comes and you want to find that needle, you can use SQL's Data Manipulation Language (DML) to perform a virtual needle extraction.\r\n\r\nSometimes the data you want is not stored in any single table in your database, but instead pieces of it are scattered across multiple tables. SQL offers several methods of gathering such far-flung data and presenting it to you integrated together in a nice compact result set, some of which are described here:\r\n<ul class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\"><b>Relational operators </b>have the ability to combine information from multiple sources in a variety of ways.</p>\r\n<p class=\"child-para\">For example, the <span class=\"code\">UNION</span> operator will return all the rows that appear in either of two compatible tables. In contrast, the <span class=\"code\">INTERSECT</span> operator will return only the rows that appear in both of two compatible tables. When you use the <span class=\"code\">EXCEPT</span> operator, all the rows from one table will be returned, except for rows that match rows in the second table.</p>\r\nIn addition, a wide variety of join operators will enable you to fine-tune your retrievals, pulling just the data you want from whichever tables it might reside in.</li>\r\n \t<li>\r\n<p class=\"first-para\"><b>Nested queries </b>allow you to retrieve data from multiple tables. These are queries on one table that include a subquery on a different table. The subquery is executed first and its result is then passed to the main query to provide a result that contains information from both.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\"><b>Recursive queries</b> have the rather amazing ability to call themselves. This ability can be a major time saver, enabling a search tree to be traversed in a lot less time that an exhaustive search would take. Bill of Materials databases are places where recursive queries can deliver major performance gains.</p>\r\n</li>\r\n</ul>","description":"After you create a database in SQL, the next step is to fill it with data. The next step after that is to wait around until there's a need for some particular bit of information contained somewhere in that data, like a needle in a haystack. When the time comes and you want to find that needle, you can use SQL's Data Manipulation Language (DML) to perform a virtual needle extraction.\r\n\r\nSometimes the data you want is not stored in any single table in your database, but instead pieces of it are scattered across multiple tables. SQL offers several methods of gathering such far-flung data and presenting it to you integrated together in a nice compact result set, some of which are described here:\r\n<ul class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\"><b>Relational operators </b>have the ability to combine information from multiple sources in a variety of ways.</p>\r\n<p class=\"child-para\">For example, the <span class=\"code\">UNION</span> operator will return all the rows that appear in either of two compatible tables. In contrast, the <span class=\"code\">INTERSECT</span> operator will return only the rows that appear in both of two compatible tables. When you use the <span class=\"code\">EXCEPT</span> operator, all the rows from one table will be returned, except for rows that match rows in the second table.</p>\r\nIn addition, a wide variety of join operators will enable you to fine-tune your retrievals, pulling just the data you want from whichever tables it might reside in.</li>\r\n \t<li>\r\n<p class=\"first-para\"><b>Nested queries </b>allow you to retrieve data from multiple tables. These are queries on one table that include a subquery on a different table. The subquery is executed first and its result is then passed to the main query to provide a result that contains information from both.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\"><b>Recursive queries</b> have the rather amazing ability to call themselves. This ability can be a major time saver, enabling a search tree to be traversed in a lot less time that an exhaustive search would take. Bill of Materials databases are places where recursive queries can deliver major performance gains.</p>\r\n</li>\r\n</ul>","blurb":"","authors":[{"authorId":9559,"name":"Allen G. Taylor","slug":"allen-g-taylor","description":"Allen G. Taylor is a 40-year veteran of the computer industry and the author of more than 40 books, including SQL For Dummies and Database Development For Dummies. For the latest news on Taylor's activities, check out his online courses (at pioneer-academy1.teachable.com).","_links":{"self":"https://dummies-api.dummies.com/v2/authors/9559"}}],"primaryCategoryTaxonomy":{"categoryId":33608,"title":"SQL","slug":"sql","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33608"}},"secondaryCategoryTaxonomy":{"categoryId":0,"title":null,"slug":null,"_links":null},"tertiaryCategoryTaxonomy":{"categoryId":0,"title":null,"slug":null,"_links":null},"trendingArticles":null,"inThisArticle":[],"relatedArticles":{"fromBook":[{"articleId":260869,"title":"Using SQL Constraints Within Transactions","slug":"using-sql-constraints-within-transactions","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260869"}},{"articleId":260866,"title":"How to Use the SQL Union Join","slug":"how-to-use-the-sql-union-join","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260866"}},{"articleId":260727,"title":"How to Declare a SQL Cursor","slug":"how-to-declare-a-sql-cursor","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260727"}},{"articleId":260720,"title":"What is SQL?","slug":"what-is-sql","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260720"}},{"articleId":260678,"title":"ODBC: Interfacing with SQL Databases","slug":"odbc-interfacing-with-sql-databases","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260678"}}],"fromCategory":[{"articleId":260869,"title":"Using SQL Constraints Within Transactions","slug":"using-sql-constraints-within-transactions","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260869"}},{"articleId":260866,"title":"How to Use the SQL Union Join","slug":"how-to-use-the-sql-union-join","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260866"}},{"articleId":260727,"title":"How to Declare a SQL Cursor","slug":"how-to-declare-a-sql-cursor","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260727"}},{"articleId":260720,"title":"What is SQL?","slug":"what-is-sql","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260720"}},{"articleId":260678,"title":"ODBC: Interfacing with SQL Databases","slug":"odbc-interfacing-with-sql-databases","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260678"}}]},"hasRelatedBookFromSearch":false,"relatedBook":{"bookId":281871,"slug":"sql-for-dummies-9th-edition","isbn":"9781119527077","categoryList":["technology","programming-web-design","sql"],"amazon":{"default":"https://www.amazon.com/gp/product/1119527074/ref=as_li_tl?ie=UTF8&tag=wiley01-20","ca":"https://www.amazon.ca/gp/product/1119527074/ref=as_li_tl?ie=UTF8&tag=wiley01-20","indigo_ca":"http://www.tkqlhce.com/click-9208661-13710633?url=https://www.chapters.indigo.ca/en-ca/books/product/1119527074-item.html&cjsku=978111945484","gb":"https://www.amazon.co.uk/gp/product/1119527074/ref=as_li_tl?ie=UTF8&tag=wiley01-20","de":"https://www.amazon.de/gp/product/1119527074/ref=as_li_tl?ie=UTF8&tag=wiley01-20"},"image":{"src":"https://www.dummies.com/wp-content/uploads/sql-for-dummies-9th-edition-cover-9781119527077-203x255.jpg","width":203,"height":255},"title":"SQL For Dummies","testBankPinActivationLink":"","bookOutOfPrint":false,"authorsInfo":"<p><b data-author-id=\"34913\">Allen G. Taylor</b> is a 30-year veteran of the computer industry and the author of over 40 books, including <b><i>SQL For Dummies</i></b> and <i>Crystal Reports For Dummies.</i> He lectures nationally on databases, innovation, and entrepreneurship. He also teaches database development internationally through a leading online education provider. </p>","authors":[{"authorId":34913,"name":"Allen G. Taylor","slug":"allen-g.-taylor","description":" <p><b>Allen G. Taylor</b> is a 30&#45;year veteran of the computer industry and the author of over 40 books, including <b><i>SQL For Dummies</i></b> and <i>Crystal Reports For Dummies.</i> He lectures nationally on databases, innovation, and entrepreneurship. He also teaches database development internationally through a leading online education provider. ","_links":{"self":"https://dummies-api.dummies.com/v2/authors/34913"}}],"_links":{"self":"https://dummies-api.dummies.com/v2/books/"}},"collections":[],"articleAds":{"footerAd":"<div class=\"du-ad-region row\" id=\"article_page_adhesion_ad\"><div class=\"du-ad-unit col-md-12\" data-slot-id=\"article_page_adhesion_ad\" data-refreshed=\"false\" \r\n data-target = \"[{&quot;key&quot;:&quot;cat&quot;,&quot;values&quot;:[&quot;technology&quot;,&quot;programming-web-design&quot;,&quot;sql&quot;]},{&quot;key&quot;:&quot;isbn&quot;,&quot;values&quot;:[&quot;9781119527077&quot;]}]\" id=\"du-slot-62b36f63dd624\"></div></div>","rightAd":"<div class=\"du-ad-region row\" id=\"article_page_right_ad\"><div class=\"du-ad-unit col-md-12\" data-slot-id=\"article_page_right_ad\" data-refreshed=\"false\" \r\n data-target = \"[{&quot;key&quot;:&quot;cat&quot;,&quot;values&quot;:[&quot;technology&quot;,&quot;programming-web-design&quot;,&quot;sql&quot;]},{&quot;key&quot;:&quot;isbn&quot;,&quot;values&quot;:[&quot;9781119527077&quot;]}]\" id=\"du-slot-62b36f63dddc7\"></div></div>"},"articleType":{"articleType":"Articles","articleList":null,"content":null,"videoInfo":{"videoId":null,"name":null,"accountId":null,"playerId":null,"thumbnailUrl":null,"description":null,"uploadDate":null}},"sponsorship":{"sponsorshipPage":false,"backgroundImage":{"src":null,"width":0,"height":0},"brandingLine":"","brandingLink":"","brandingLogo":{"src":null,"width":0,"height":0},"sponsorAd":null,"sponsorEbookTitle":null,"sponsorEbookLink":null,"sponsorEbookImage":null},"primaryLearningPath":"Advance","lifeExpectancy":"Two years","lifeExpectancySetFrom":"2022-01-14T00:00:00+00:00","dummiesForKids":"no","sponsoredContent":"no","adInfo":"","adPairKey":[]},"status":"publish","visibility":"public","articleId":163495},{"headers":{"creationTime":"2016-03-26T14:01:06+00:00","modifiedTime":"2022-01-14T19:42:43+00:00","timestamp":"2022-06-22T19:37:07+00:00"},"data":{"breadcrumbs":[{"name":"Technology","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33512"},"slug":"technology","categoryId":33512},{"name":"Programming & Web Design","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33592"},"slug":"programming-web-design","categoryId":33592},{"name":"SQL","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33608"},"slug":"sql","categoryId":33608}],"title":"SQL First, Second, and Third Normal Forms","strippedTitle":"sql first, second, and third normal forms","slug":"sql-first-second-and-third-normal-forms","canonicalUrl":"","seo":{"metaDescription":"There are three sources of modification anomalies in SQL These are defined as first, second, and third normal forms (1NF, 2NF, 3NF). These normal forms act as r","noIndex":0,"noFollow":0},"content":"There are three sources of modification anomalies in SQL These are defined as first, second, and third <i>normal forms</i> (1NF, 2NF, 3NF). These normal forms act as remedies to modification anomalies.\r\n<h2 id=\"tab1\" >First normal form</h2>\r\nTo be in first normal form (1NF), a table must have the following qualities:\r\n<ul class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">The table is two-dimensional with rows and columns.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Each row contains data that pertains to some thing or portion of a thing.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Each column contains data for a single attribute of the thing it’s describing.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Each cell (intersection of a row and a column) of the table must have only a single value.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Entries in any column must all be of the same kind. If, for example, the entry in one row of a column contains an employee name, all the other rows must contain employee names in that column, too.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Each column must have a unique name.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">No two rows may be identical (that is, each row must be unique).</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">The order of the columns and the order of the rows are not significant.</p>\r\n</li>\r\n</ul>\r\nA table (relation) in first normal form is immune to some kinds of modification anomalies but is still subject to others. The SALES table is in first normal form, and the table is subject to deletion and insertion anomalies. First normal form may prove useful in some applications but unreliable in others.\r\n\r\n<img src=\"https://www.dummies.com/wp-content/uploads/399617.image0.jpg\" alt=\"image0.jpg\" width=\"535\" height=\"255\" />\r\n<h2 id=\"tab2\" >Second normal form</h2>\r\nTo appreciate second normal form, you must understand the idea of functional dependency. A <i>functional dependency</i> is a relationship between or among attributes. One attribute is functionally dependent on another if the value of the second attribute determines the value of the first attribute. If you know the value of the second attribute, you can determine the value of the first attribute.\r\n\r\nSuppose, for example, that a table has attributes (columns) <span class=\"code\">StandardCharge</span>, <span class=\"code\">NumberOfTests</span>, and <span class=\"code\">TotalCharge</span> that relate through the following equation:\r\n<pre class=\"code\">TotalCharge = StandardCharge * NumberOfTests</pre>\r\n<span class=\"code\">TotalCharge</span> is functionally dependent on both <span class=\"code\">StandardCharge</span> and <span class=\"code\">NumberOfTests</span>. If you know the values of <span class=\"code\">StandardCharge</span> and <span class=\"code\">NumberOfTests</span>, you can determine the value of <span class=\"code\">TotalCharge</span>.\r\n\r\nEvery table in first normal form must have a unique primary key. That key may consist of one or more than one column. A key consisting of more than one column is called a <i>composite key.</i> To be in second normal form (2NF), all non-key attributes must depend on the entire key. Thus, every relation that is in 1NF with a single attribute key is automatically in second normal form.\r\n\r\nIf a relation has a composite key, all non-key attributes must depend on all components of the key. If you have a table where some non-key attributes don’t depend on all components of the key, break the table up into two or more tables so that — in each of the new tables — all non-key attributes depend on all components of the primary key.\r\n\r\nSound confusing? Look at an example to clarify matters. Consider the SALES table. Instead of recording only a single purchase for each customer, you add a row every time a customer buys an item for the first time. An additional difference is that charter customers (those with <span class=\"code\">Customer_ID</span> values of 1001 to 1007) get a discount off the normal price.\r\n\r\n<img src=\"https://www.dummies.com/wp-content/uploads/399618.image1.jpg\" alt=\"image1.jpg\" width=\"535\" height=\"344\" />\r\n\r\n<span class=\"code\">Customer_ID</span> does not uniquely identify a row. In two rows, <span class=\"code\">Customer_ID</span> is 1001. In two other rows, <span class=\"code\">Customer_ID</span> is 1010. The combination of the <span class=\"code\">Customer_ID</span> column and the <span class=\"code\">Product</span> column uniquely identifies a row. These two columns together are a composite key.\r\n\r\nIf not for the fact that some customers qualify for a discount and others don’t, the table wouldn’t be in second normal form, because <span class=\"code\">Price</span> (a non-key attribute) would depend only on part of the key (<span class=\"code\">Product</span>). Because some customers do qualify for a discount, <span class=\"code\">Price</span> depends on both <span class=\"code\">CustomerID</span> and <span class=\"code\">Product</span>, and the table is in second normal form.\r\n<h2 id=\"tab3\" >Third normal form</h2>\r\nTables in second normal form are especially vulnerable to some types of modification anomalies — in particular, those that come from transitive dependencies.\r\n\r\nA <i>transitive dependency</i> occurs when one attribute depends on a second attribute, which depends on a third attribute. Deletions in a table with such a dependency can cause unwanted information loss. A relation in third normal form is a relation in second normal form with no transitive dependencies.\r\n\r\nLook again at the SALES table, which you know is in first normal form. As long as you constrain entries to permit only one row for each <span class=\"code\">Customer_ID</span>, you have a single-attribute primary key, and the table is in second normal form. However, the table is still subject to anomalies. What if customer 1010 is unhappy with the chlorine bleach, for example, and returns the item for a refund?\r\n\r\nYou want to remove the third row from the table, which records the fact that customer 1010 bought chlorine bleach. You have a problem: If you remove that row, you also lose the fact that chlorine bleach has a price of $4. This situation is an example of a transitive dependency. <span class=\"code\">Price</span> depends on <span class=\"code\">Product</span>, which, in turn, depends on the primary key <span class=\"code\">Customer_ID</span>.\r\n\r\nBreaking the SALES table into two tables solves the transitive dependency problem. The two tables make up a database that’s in third normal form.","description":"There are three sources of modification anomalies in SQL These are defined as first, second, and third <i>normal forms</i> (1NF, 2NF, 3NF). These normal forms act as remedies to modification anomalies.\r\n<h2 id=\"tab1\" >First normal form</h2>\r\nTo be in first normal form (1NF), a table must have the following qualities:\r\n<ul class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">The table is two-dimensional with rows and columns.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Each row contains data that pertains to some thing or portion of a thing.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Each column contains data for a single attribute of the thing it’s describing.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Each cell (intersection of a row and a column) of the table must have only a single value.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Entries in any column must all be of the same kind. If, for example, the entry in one row of a column contains an employee name, all the other rows must contain employee names in that column, too.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Each column must have a unique name.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">No two rows may be identical (that is, each row must be unique).</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">The order of the columns and the order of the rows are not significant.</p>\r\n</li>\r\n</ul>\r\nA table (relation) in first normal form is immune to some kinds of modification anomalies but is still subject to others. The SALES table is in first normal form, and the table is subject to deletion and insertion anomalies. First normal form may prove useful in some applications but unreliable in others.\r\n\r\n<img src=\"https://www.dummies.com/wp-content/uploads/399617.image0.jpg\" alt=\"image0.jpg\" width=\"535\" height=\"255\" />\r\n<h2 id=\"tab2\" >Second normal form</h2>\r\nTo appreciate second normal form, you must understand the idea of functional dependency. A <i>functional dependency</i> is a relationship between or among attributes. One attribute is functionally dependent on another if the value of the second attribute determines the value of the first attribute. If you know the value of the second attribute, you can determine the value of the first attribute.\r\n\r\nSuppose, for example, that a table has attributes (columns) <span class=\"code\">StandardCharge</span>, <span class=\"code\">NumberOfTests</span>, and <span class=\"code\">TotalCharge</span> that relate through the following equation:\r\n<pre class=\"code\">TotalCharge = StandardCharge * NumberOfTests</pre>\r\n<span class=\"code\">TotalCharge</span> is functionally dependent on both <span class=\"code\">StandardCharge</span> and <span class=\"code\">NumberOfTests</span>. If you know the values of <span class=\"code\">StandardCharge</span> and <span class=\"code\">NumberOfTests</span>, you can determine the value of <span class=\"code\">TotalCharge</span>.\r\n\r\nEvery table in first normal form must have a unique primary key. That key may consist of one or more than one column. A key consisting of more than one column is called a <i>composite key.</i> To be in second normal form (2NF), all non-key attributes must depend on the entire key. Thus, every relation that is in 1NF with a single attribute key is automatically in second normal form.\r\n\r\nIf a relation has a composite key, all non-key attributes must depend on all components of the key. If you have a table where some non-key attributes don’t depend on all components of the key, break the table up into two or more tables so that — in each of the new tables — all non-key attributes depend on all components of the primary key.\r\n\r\nSound confusing? Look at an example to clarify matters. Consider the SALES table. Instead of recording only a single purchase for each customer, you add a row every time a customer buys an item for the first time. An additional difference is that charter customers (those with <span class=\"code\">Customer_ID</span> values of 1001 to 1007) get a discount off the normal price.\r\n\r\n<img src=\"https://www.dummies.com/wp-content/uploads/399618.image1.jpg\" alt=\"image1.jpg\" width=\"535\" height=\"344\" />\r\n\r\n<span class=\"code\">Customer_ID</span> does not uniquely identify a row. In two rows, <span class=\"code\">Customer_ID</span> is 1001. In two other rows, <span class=\"code\">Customer_ID</span> is 1010. The combination of the <span class=\"code\">Customer_ID</span> column and the <span class=\"code\">Product</span> column uniquely identifies a row. These two columns together are a composite key.\r\n\r\nIf not for the fact that some customers qualify for a discount and others don’t, the table wouldn’t be in second normal form, because <span class=\"code\">Price</span> (a non-key attribute) would depend only on part of the key (<span class=\"code\">Product</span>). Because some customers do qualify for a discount, <span class=\"code\">Price</span> depends on both <span class=\"code\">CustomerID</span> and <span class=\"code\">Product</span>, and the table is in second normal form.\r\n<h2 id=\"tab3\" >Third normal form</h2>\r\nTables in second normal form are especially vulnerable to some types of modification anomalies — in particular, those that come from transitive dependencies.\r\n\r\nA <i>transitive dependency</i> occurs when one attribute depends on a second attribute, which depends on a third attribute. Deletions in a table with such a dependency can cause unwanted information loss. A relation in third normal form is a relation in second normal form with no transitive dependencies.\r\n\r\nLook again at the SALES table, which you know is in first normal form. As long as you constrain entries to permit only one row for each <span class=\"code\">Customer_ID</span>, you have a single-attribute primary key, and the table is in second normal form. However, the table is still subject to anomalies. What if customer 1010 is unhappy with the chlorine bleach, for example, and returns the item for a refund?\r\n\r\nYou want to remove the third row from the table, which records the fact that customer 1010 bought chlorine bleach. You have a problem: If you remove that row, you also lose the fact that chlorine bleach has a price of $4. This situation is an example of a transitive dependency. <span class=\"code\">Price</span> depends on <span class=\"code\">Product</span>, which, in turn, depends on the primary key <span class=\"code\">Customer_ID</span>.\r\n\r\nBreaking the SALES table into two tables solves the transitive dependency problem. The two tables make up a database that’s in third normal form.","blurb":"","authors":[{"authorId":9559,"name":"Allen G. Taylor","slug":"allen-g-taylor","description":"Allen G. Taylor is a 40-year veteran of the computer industry and the author of more than 40 books, including SQL For Dummies and Database Development For Dummies. For the latest news on Taylor's activities, check out his online courses (at pioneer-academy1.teachable.com).","_links":{"self":"https://dummies-api.dummies.com/v2/authors/9559"}}],"primaryCategoryTaxonomy":{"categoryId":33608,"title":"SQL","slug":"sql","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33608"}},"secondaryCategoryTaxonomy":{"categoryId":0,"title":null,"slug":null,"_links":null},"tertiaryCategoryTaxonomy":{"categoryId":0,"title":null,"slug":null,"_links":null},"trendingArticles":null,"inThisArticle":[{"label":"First normal form","target":"#tab1"},{"label":"Second normal form","target":"#tab2"},{"label":"Third normal form","target":"#tab3"}],"relatedArticles":{"fromBook":[{"articleId":260869,"title":"Using SQL Constraints Within Transactions","slug":"using-sql-constraints-within-transactions","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260869"}},{"articleId":260866,"title":"How to Use the SQL Union Join","slug":"how-to-use-the-sql-union-join","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260866"}},{"articleId":260727,"title":"How to Declare a SQL Cursor","slug":"how-to-declare-a-sql-cursor","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260727"}},{"articleId":260720,"title":"What is SQL?","slug":"what-is-sql","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260720"}},{"articleId":260678,"title":"ODBC: Interfacing with SQL Databases","slug":"odbc-interfacing-with-sql-databases","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260678"}}],"fromCategory":[{"articleId":260869,"title":"Using SQL Constraints Within Transactions","slug":"using-sql-constraints-within-transactions","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260869"}},{"articleId":260866,"title":"How to Use the SQL Union Join","slug":"how-to-use-the-sql-union-join","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260866"}},{"articleId":260727,"title":"How to Declare a SQL Cursor","slug":"how-to-declare-a-sql-cursor","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260727"}},{"articleId":260720,"title":"What is SQL?","slug":"what-is-sql","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260720"}},{"articleId":260678,"title":"ODBC: Interfacing with SQL Databases","slug":"odbc-interfacing-with-sql-databases","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260678"}}]},"hasRelatedBookFromSearch":false,"relatedBook":{"bookId":281871,"slug":"sql-for-dummies-9th-edition","isbn":"9781119527077","categoryList":["technology","programming-web-design","sql"],"amazon":{"default":"https://www.amazon.com/gp/product/1119527074/ref=as_li_tl?ie=UTF8&tag=wiley01-20","ca":"https://www.amazon.ca/gp/product/1119527074/ref=as_li_tl?ie=UTF8&tag=wiley01-20","indigo_ca":"http://www.tkqlhce.com/click-9208661-13710633?url=https://www.chapters.indigo.ca/en-ca/books/product/1119527074-item.html&cjsku=978111945484","gb":"https://www.amazon.co.uk/gp/product/1119527074/ref=as_li_tl?ie=UTF8&tag=wiley01-20","de":"https://www.amazon.de/gp/product/1119527074/ref=as_li_tl?ie=UTF8&tag=wiley01-20"},"image":{"src":"https://www.dummies.com/wp-content/uploads/sql-for-dummies-9th-edition-cover-9781119527077-203x255.jpg","width":203,"height":255},"title":"SQL For Dummies","testBankPinActivationLink":"","bookOutOfPrint":false,"authorsInfo":"<p><b data-author-id=\"34913\">Allen G. Taylor</b> is a 30-year veteran of the computer industry and the author of over 40 books, including <b><i>SQL For Dummies</i></b> and <i>Crystal Reports For Dummies.</i> He lectures nationally on databases, innovation, and entrepreneurship. He also teaches database development internationally through a leading online education provider. </p>","authors":[{"authorId":34913,"name":"Allen G. Taylor","slug":"allen-g.-taylor","description":" <p><b>Allen G. Taylor</b> is a 30&#45;year veteran of the computer industry and the author of over 40 books, including <b><i>SQL For Dummies</i></b> and <i>Crystal Reports For Dummies.</i> He lectures nationally on databases, innovation, and entrepreneurship. He also teaches database development internationally through a leading online education provider. ","_links":{"self":"https://dummies-api.dummies.com/v2/authors/34913"}}],"_links":{"self":"https://dummies-api.dummies.com/v2/books/"}},"collections":[],"articleAds":{"footerAd":"<div class=\"du-ad-region row\" id=\"article_page_adhesion_ad\"><div class=\"du-ad-unit col-md-12\" data-slot-id=\"article_page_adhesion_ad\" data-refreshed=\"false\" \r\n data-target = \"[{&quot;key&quot;:&quot;cat&quot;,&quot;values&quot;:[&quot;technology&quot;,&quot;programming-web-design&quot;,&quot;sql&quot;]},{&quot;key&quot;:&quot;isbn&quot;,&quot;values&quot;:[&quot;9781119527077&quot;]}]\" id=\"du-slot-62b36f63d6228\"></div></div>","rightAd":"<div class=\"du-ad-region row\" id=\"article_page_right_ad\"><div class=\"du-ad-unit col-md-12\" data-slot-id=\"article_page_right_ad\" data-refreshed=\"false\" \r\n data-target = \"[{&quot;key&quot;:&quot;cat&quot;,&quot;values&quot;:[&quot;technology&quot;,&quot;programming-web-design&quot;,&quot;sql&quot;]},{&quot;key&quot;:&quot;isbn&quot;,&quot;values&quot;:[&quot;9781119527077&quot;]}]\" id=\"du-slot-62b36f63d699e\"></div></div>"},"articleType":{"articleType":"Articles","articleList":null,"content":null,"videoInfo":{"videoId":null,"name":null,"accountId":null,"playerId":null,"thumbnailUrl":null,"description":null,"uploadDate":null}},"sponsorship":{"sponsorshipPage":false,"backgroundImage":{"src":null,"width":0,"height":0},"brandingLine":"","brandingLink":"","brandingLogo":{"src":null,"width":0,"height":0},"sponsorAd":null,"sponsorEbookTitle":null,"sponsorEbookLink":null,"sponsorEbookImage":null},"primaryLearningPath":"Advance","lifeExpectancy":"Two years","lifeExpectancySetFrom":"2022-01-14T00:00:00+00:00","dummiesForKids":"no","sponsoredContent":"no","adInfo":"","adPairKey":[]},"status":"publish","visibility":"public","articleId":160848},{"headers":{"creationTime":"2016-03-26T14:00:27+00:00","modifiedTime":"2022-01-14T19:41:00+00:00","timestamp":"2022-06-22T19:37:07+00:00"},"data":{"breadcrumbs":[{"name":"Technology","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33512"},"slug":"technology","categoryId":33512},{"name":"Programming & Web Design","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33592"},"slug":"programming-web-design","categoryId":33592},{"name":"SQL","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33608"},"slug":"sql","categoryId":33608}],"title":"How to Use SQL Column References","strippedTitle":"how to use sql column references","slug":"how-to-use-sql-column-references","canonicalUrl":"","seo":{"metaDescription":"Every column contains one value for each row of a table. SQL statements often refer to such values. A fully qualified column reference consists of the table nam","noIndex":0,"noFollow":0},"content":"Every column contains one value for each row of a table. SQL statements often refer to such values. A fully qualified column reference consists of the table name, a period, and then the column name (for example, <span class=\"code\">PRICING.Product</span>). Consider the following statement:\r\n<pre class=\"code\">SELECT PRICING.Cost\r\n FROM PRICING\r\n WHERE PRICING.Product = 'F-35' ;</pre>\r\nHere <span class=\"code\">PRICING.Product</span> is a column reference. This reference contains the value <span class=\"code\">‘F-35’</span>. <span class=\"code\">PRICING.Cost</span> is also a column reference, but you don’t know its value until the preceding <span class=\"code\">SELECT</span> statement executes.\r\nBecause it only makes sense to reference columns in the current table, you don’t generally need to use fully qualified column references. The following statement, for example, is equivalent to the previous one:\r\n<pre class=\"code\">SELECT Cost\r\n FROM PRICING\r\n WHERE Product = 'F-35' ;</pre>\r\nSometimes you may be dealing with more than one table — say, when two tables in a database contain one or more columns with the same name. In such a case, you must fully qualify column references for those columns to guarantee that you get the column you want.\r\n\r\nFor example, suppose that your company maintains facilities in both Kingston and Jefferson, and you maintain separate employee records for each site. You name the Kingston employee table <span class=\"code\">EMP_KINGSTON</span>, and you name the Jefferson employee table <span class=\"code\">EMP_JEFFERSON</span>. You want a list of employees who work at both sites, so you need to find the employees whose names appear in both tables. The following <span class=\"code\">SELECT</span> statement gives you what you want:\r\n<pre class=\"code\">SELECT EMP_KINGSTON.FirstName, EMP_KINGSTON.LastName\r\n FROM EMP_KINGSTON, EMP_JEFFERSON\r\n WHERE EMP_KINGSTON.EmpID = EMP_JEFFERSON.EmpID ;</pre>\r\nBecause each employee’s ID number is unique and remains the same regardless of the work site, you can use this ID as a link between the two tables. This retrieval returns only the names of employees who appear in both tables.","description":"Every column contains one value for each row of a table. SQL statements often refer to such values. A fully qualified column reference consists of the table name, a period, and then the column name (for example, <span class=\"code\">PRICING.Product</span>). Consider the following statement:\r\n<pre class=\"code\">SELECT PRICING.Cost\r\n FROM PRICING\r\n WHERE PRICING.Product = 'F-35' ;</pre>\r\nHere <span class=\"code\">PRICING.Product</span> is a column reference. This reference contains the value <span class=\"code\">‘F-35’</span>. <span class=\"code\">PRICING.Cost</span> is also a column reference, but you don’t know its value until the preceding <span class=\"code\">SELECT</span> statement executes.\r\nBecause it only makes sense to reference columns in the current table, you don’t generally need to use fully qualified column references. The following statement, for example, is equivalent to the previous one:\r\n<pre class=\"code\">SELECT Cost\r\n FROM PRICING\r\n WHERE Product = 'F-35' ;</pre>\r\nSometimes you may be dealing with more than one table — say, when two tables in a database contain one or more columns with the same name. In such a case, you must fully qualify column references for those columns to guarantee that you get the column you want.\r\n\r\nFor example, suppose that your company maintains facilities in both Kingston and Jefferson, and you maintain separate employee records for each site. You name the Kingston employee table <span class=\"code\">EMP_KINGSTON</span>, and you name the Jefferson employee table <span class=\"code\">EMP_JEFFERSON</span>. You want a list of employees who work at both sites, so you need to find the employees whose names appear in both tables. The following <span class=\"code\">SELECT</span> statement gives you what you want:\r\n<pre class=\"code\">SELECT EMP_KINGSTON.FirstName, EMP_KINGSTON.LastName\r\n FROM EMP_KINGSTON, EMP_JEFFERSON\r\n WHERE EMP_KINGSTON.EmpID = EMP_JEFFERSON.EmpID ;</pre>\r\nBecause each employee’s ID number is unique and remains the same regardless of the work site, you can use this ID as a link between the two tables. This retrieval returns only the names of employees who appear in both tables.","blurb":"","authors":[{"authorId":9559,"name":"Allen G. Taylor","slug":"allen-g-taylor","description":"Allen G. Taylor is a 40-year veteran of the computer industry and the author of more than 40 books, including SQL For Dummies and Database Development For Dummies. For the latest news on Taylor's activities, check out his online courses (at pioneer-academy1.teachable.com).","_links":{"self":"https://dummies-api.dummies.com/v2/authors/9559"}}],"primaryCategoryTaxonomy":{"categoryId":33608,"title":"SQL","slug":"sql","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33608"}},"secondaryCategoryTaxonomy":{"categoryId":0,"title":null,"slug":null,"_links":null},"tertiaryCategoryTaxonomy":{"categoryId":0,"title":null,"slug":null,"_links":null},"trendingArticles":null,"inThisArticle":[],"relatedArticles":{"fromBook":[{"articleId":260869,"title":"Using SQL Constraints Within Transactions","slug":"using-sql-constraints-within-transactions","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260869"}},{"articleId":260866,"title":"How to Use the SQL Union Join","slug":"how-to-use-the-sql-union-join","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260866"}},{"articleId":260727,"title":"How to Declare a SQL Cursor","slug":"how-to-declare-a-sql-cursor","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260727"}},{"articleId":260720,"title":"What is SQL?","slug":"what-is-sql","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260720"}},{"articleId":260678,"title":"ODBC: Interfacing with SQL Databases","slug":"odbc-interfacing-with-sql-databases","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260678"}}],"fromCategory":[{"articleId":260869,"title":"Using SQL Constraints Within Transactions","slug":"using-sql-constraints-within-transactions","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260869"}},{"articleId":260866,"title":"How to Use the SQL Union Join","slug":"how-to-use-the-sql-union-join","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260866"}},{"articleId":260727,"title":"How to Declare a SQL Cursor","slug":"how-to-declare-a-sql-cursor","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260727"}},{"articleId":260720,"title":"What is SQL?","slug":"what-is-sql","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260720"}},{"articleId":260678,"title":"ODBC: Interfacing with SQL Databases","slug":"odbc-interfacing-with-sql-databases","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260678"}}]},"hasRelatedBookFromSearch":false,"relatedBook":{"bookId":281871,"slug":"sql-for-dummies-9th-edition","isbn":"9781119527077","categoryList":["technology","programming-web-design","sql"],"amazon":{"default":"https://www.amazon.com/gp/product/1119527074/ref=as_li_tl?ie=UTF8&tag=wiley01-20","ca":"https://www.amazon.ca/gp/product/1119527074/ref=as_li_tl?ie=UTF8&tag=wiley01-20","indigo_ca":"http://www.tkqlhce.com/click-9208661-13710633?url=https://www.chapters.indigo.ca/en-ca/books/product/1119527074-item.html&cjsku=978111945484","gb":"https://www.amazon.co.uk/gp/product/1119527074/ref=as_li_tl?ie=UTF8&tag=wiley01-20","de":"https://www.amazon.de/gp/product/1119527074/ref=as_li_tl?ie=UTF8&tag=wiley01-20"},"image":{"src":"https://www.dummies.com/wp-content/uploads/sql-for-dummies-9th-edition-cover-9781119527077-203x255.jpg","width":203,"height":255},"title":"SQL For Dummies","testBankPinActivationLink":"","bookOutOfPrint":false,"authorsInfo":"<p><b data-author-id=\"34913\">Allen G. Taylor</b> is a 30-year veteran of the computer industry and the author of over 40 books, including <b><i>SQL For Dummies</i></b> and <i>Crystal Reports For Dummies.</i> He lectures nationally on databases, innovation, and entrepreneurship. He also teaches database development internationally through a leading online education provider. </p>","authors":[{"authorId":34913,"name":"Allen G. Taylor","slug":"allen-g.-taylor","description":" <p><b>Allen G. Taylor</b> is a 30&#45;year veteran of the computer industry and the author of over 40 books, including <b><i>SQL For Dummies</i></b> and <i>Crystal Reports For Dummies.</i> He lectures nationally on databases, innovation, and entrepreneurship. He also teaches database development internationally through a leading online education provider. ","_links":{"self":"https://dummies-api.dummies.com/v2/authors/34913"}}],"_links":{"self":"https://dummies-api.dummies.com/v2/books/"}},"collections":[],"articleAds":{"footerAd":"<div class=\"du-ad-region row\" id=\"article_page_adhesion_ad\"><div class=\"du-ad-unit col-md-12\" data-slot-id=\"article_page_adhesion_ad\" data-refreshed=\"false\" \r\n data-target = \"[{&quot;key&quot;:&quot;cat&quot;,&quot;values&quot;:[&quot;technology&quot;,&quot;programming-web-design&quot;,&quot;sql&quot;]},{&quot;key&quot;:&quot;isbn&quot;,&quot;values&quot;:[&quot;9781119527077&quot;]}]\" id=\"du-slot-62b36f63cec83\"></div></div>","rightAd":"<div class=\"du-ad-region row\" id=\"article_page_right_ad\"><div class=\"du-ad-unit col-md-12\" data-slot-id=\"article_page_right_ad\" data-refreshed=\"false\" \r\n data-target = \"[{&quot;key&quot;:&quot;cat&quot;,&quot;values&quot;:[&quot;technology&quot;,&quot;programming-web-design&quot;,&quot;sql&quot;]},{&quot;key&quot;:&quot;isbn&quot;,&quot;values&quot;:[&quot;9781119527077&quot;]}]\" id=\"du-slot-62b36f63cf66d\"></div></div>"},"articleType":{"articleType":"Articles","articleList":null,"content":null,"videoInfo":{"videoId":null,"name":null,"accountId":null,"playerId":null,"thumbnailUrl":null,"description":null,"uploadDate":null}},"sponsorship":{"sponsorshipPage":false,"backgroundImage":{"src":null,"width":0,"height":0},"brandingLine":"","brandingLink":"","brandingLogo":{"src":null,"width":0,"height":0},"sponsorAd":null,"sponsorEbookTitle":null,"sponsorEbookLink":null,"sponsorEbookImage":null},"primaryLearningPath":"Advance","lifeExpectancy":"Two years","lifeExpectancySetFrom":"2022-01-14T00:00:00+00:00","dummiesForKids":"no","sponsoredContent":"no","adInfo":"","adPairKey":[]},"status":"publish","visibility":"public","articleId":160759},{"headers":{"creationTime":"2016-03-26T14:00:35+00:00","modifiedTime":"2022-01-14T19:40:25+00:00","timestamp":"2022-06-22T19:37:07+00:00"},"data":{"breadcrumbs":[{"name":"Technology","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33512"},"slug":"technology","categoryId":33512},{"name":"Programming & Web Design","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33592"},"slug":"programming-web-design","categoryId":33592},{"name":"SQL","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33608"},"slug":"sql","categoryId":33608}],"title":"How to Use MATCH in SQL Statements","strippedTitle":"how to use match in sql statements","slug":"how-to-use-match-in-sql-statements","canonicalUrl":"","seo":{"metaDescription":"Referential integrity involves maintaining consistency in a multitable SQL database. You can lose integrity by adding a row to a child table that doesn’t have a","noIndex":0,"noFollow":0},"content":"Referential integrity involves maintaining consistency in a multitable SQL database. You can lose integrity by adding a row to a child table that doesn’t have a corresponding row in the child’s parent table. You can cause similar problems by deleting a row from a parent table if rows corresponding to that row exist in a child table.\r\n\r\nSuppose your business has a CUSTOMER table that keeps track of all your customers and a SALES table that records all sales transactions. You don’t want to add a row to SALES until after you enter the customer making the purchase into the CUSTOMER table. You also don’t want to delete a customer from the CUSTOMER table if that customer made purchases that exist in the SALES table.\r\n\r\nBefore you perform an insertion or a deletion, you may want to check the candidate row to make sure that inserting or deleting that row doesn’t cause integrity problems. The <span class=\"code\">MATCH</span> predicate can perform such a check.\r\n\r\nSay you have a CUSTOMER table and a SALES table. <span class=\"code\">CustomerID</span> is the primary key of the CUSTOMER table and acts as a foreign key in the SALES table. Every row in the CUSTOMER table must have a unique <span class=\"code\">CustomerID</span> that isn’t null.\r\n\r\n<span class=\"code\">CustomerID</span> isn’t unique in the SALES table, because repeat customers buy more than once. This situation is fine; it doesn’t threaten integrity because <span class=\"code\">CustomerID</span> is a foreign key rather than a primary key in that table.\r\n\r\nSeemingly, <span class=\"code\">CustomerID</span> can be null in the SALES table, because someone can walk in off the street, buy something, and walk out before you get a chance to enter his or her name and address into the CUSTOMER table. This situation can create trouble — a row in the child table with no corresponding row in the parent table.\r\n\r\nTo overcome this problem, you can create a generic customer in the CUSTOMER table and assign all anonymous sales to that customer. Say that a customer steps up to the cash register and claims that she bought an F-35 Strike Fighter on December 18, 2012. Although she has lost her receipt, she now wants to return the plane because it shows up like an aircraft carrier on opponents’ radar screens.\r\n\r\nYou can verify whether she bought an F-35 by searching your SALES database for a match. First, you must retrieve her <span class=\"code\">CustomerID</span> into the variable <span class=\"code\">vcustid</span>; then you can use the following syntax:\r\n<pre class=\"code\">... WHERE (:vcustid, 'F-35', '2017-12-18')\r\n MATCH\r\n (SELECT CustomerID, ProductID, SaleDate\r\n FROM SALES)</pre>\r\nIf the <span class=\"code\">MATCH</span> predicate returns a True value, the database contains a sale of the F-35 on December 18, 2017, to this client’s <span class=\"code\">CustomerID</span>. Take back the defective product and refund the customer’s money. (<b><i>Note:</i></b> If any values in the first argument of the <span class=\"code\">MATCH</span> predicate are null, a True value always returns.)\r\n\r\nSQL’s developers added the <span class=\"code\">MATCH</span> predicate and the <span class=\"code\">UNIQUE</span> predicate for the same reason — they provide a way to explicitly perform the tests defined for the implicit referential integrity (RI) and <span class=\"code\">UNIQUE</span> constraints.\r\n\r\nThe general form of the <span class=\"code\">MATCH</span> predicate is as follows:\r\n<pre class=\"code\"><i>Row_value</i>MATCH [UNIQUE] [SIMPLE| PARTIAL | FULL ] <i>Subquery</i></pre>\r\nThe <span class=\"code\">UNIQUE</span>, <span class=\"code\">SIMPLE</span>, <span class=\"code\">PARTIAL</span>, and <span class=\"code\">FULL</span> options relate to rules that come into play if the row value expression <i>R</i> has one or more columns that are null. The rules for the <span class=\"code\">MATCH</span> predicate are a copy of corresponding referential integrity rules.","description":"Referential integrity involves maintaining consistency in a multitable SQL database. You can lose integrity by adding a row to a child table that doesn’t have a corresponding row in the child’s parent table. You can cause similar problems by deleting a row from a parent table if rows corresponding to that row exist in a child table.\r\n\r\nSuppose your business has a CUSTOMER table that keeps track of all your customers and a SALES table that records all sales transactions. You don’t want to add a row to SALES until after you enter the customer making the purchase into the CUSTOMER table. You also don’t want to delete a customer from the CUSTOMER table if that customer made purchases that exist in the SALES table.\r\n\r\nBefore you perform an insertion or a deletion, you may want to check the candidate row to make sure that inserting or deleting that row doesn’t cause integrity problems. The <span class=\"code\">MATCH</span> predicate can perform such a check.\r\n\r\nSay you have a CUSTOMER table and a SALES table. <span class=\"code\">CustomerID</span> is the primary key of the CUSTOMER table and acts as a foreign key in the SALES table. Every row in the CUSTOMER table must have a unique <span class=\"code\">CustomerID</span> that isn’t null.\r\n\r\n<span class=\"code\">CustomerID</span> isn’t unique in the SALES table, because repeat customers buy more than once. This situation is fine; it doesn’t threaten integrity because <span class=\"code\">CustomerID</span> is a foreign key rather than a primary key in that table.\r\n\r\nSeemingly, <span class=\"code\">CustomerID</span> can be null in the SALES table, because someone can walk in off the street, buy something, and walk out before you get a chance to enter his or her name and address into the CUSTOMER table. This situation can create trouble — a row in the child table with no corresponding row in the parent table.\r\n\r\nTo overcome this problem, you can create a generic customer in the CUSTOMER table and assign all anonymous sales to that customer. Say that a customer steps up to the cash register and claims that she bought an F-35 Strike Fighter on December 18, 2012. Although she has lost her receipt, she now wants to return the plane because it shows up like an aircraft carrier on opponents’ radar screens.\r\n\r\nYou can verify whether she bought an F-35 by searching your SALES database for a match. First, you must retrieve her <span class=\"code\">CustomerID</span> into the variable <span class=\"code\">vcustid</span>; then you can use the following syntax:\r\n<pre class=\"code\">... WHERE (:vcustid, 'F-35', '2017-12-18')\r\n MATCH\r\n (SELECT CustomerID, ProductID, SaleDate\r\n FROM SALES)</pre>\r\nIf the <span class=\"code\">MATCH</span> predicate returns a True value, the database contains a sale of the F-35 on December 18, 2017, to this client’s <span class=\"code\">CustomerID</span>. Take back the defective product and refund the customer’s money. (<b><i>Note:</i></b> If any values in the first argument of the <span class=\"code\">MATCH</span> predicate are null, a True value always returns.)\r\n\r\nSQL’s developers added the <span class=\"code\">MATCH</span> predicate and the <span class=\"code\">UNIQUE</span> predicate for the same reason — they provide a way to explicitly perform the tests defined for the implicit referential integrity (RI) and <span class=\"code\">UNIQUE</span> constraints.\r\n\r\nThe general form of the <span class=\"code\">MATCH</span> predicate is as follows:\r\n<pre class=\"code\"><i>Row_value</i>MATCH [UNIQUE] [SIMPLE| PARTIAL | FULL ] <i>Subquery</i></pre>\r\nThe <span class=\"code\">UNIQUE</span>, <span class=\"code\">SIMPLE</span>, <span class=\"code\">PARTIAL</span>, and <span class=\"code\">FULL</span> options relate to rules that come into play if the row value expression <i>R</i> has one or more columns that are null. The rules for the <span class=\"code\">MATCH</span> predicate are a copy of corresponding referential integrity rules.","blurb":"","authors":[{"authorId":9559,"name":"Allen G. Taylor","slug":"allen-g-taylor","description":"Allen G. Taylor is a 40-year veteran of the computer industry and the author of more than 40 books, including SQL For Dummies and Database Development For Dummies. For the latest news on Taylor's activities, check out his online courses (at pioneer-academy1.teachable.com).","_links":{"self":"https://dummies-api.dummies.com/v2/authors/9559"}}],"primaryCategoryTaxonomy":{"categoryId":33608,"title":"SQL","slug":"sql","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33608"}},"secondaryCategoryTaxonomy":{"categoryId":0,"title":null,"slug":null,"_links":null},"tertiaryCategoryTaxonomy":{"categoryId":0,"title":null,"slug":null,"_links":null},"trendingArticles":null,"inThisArticle":[],"relatedArticles":{"fromBook":[{"articleId":260869,"title":"Using SQL Constraints Within Transactions","slug":"using-sql-constraints-within-transactions","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260869"}},{"articleId":260866,"title":"How to Use the SQL Union Join","slug":"how-to-use-the-sql-union-join","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260866"}},{"articleId":260727,"title":"How to Declare a SQL Cursor","slug":"how-to-declare-a-sql-cursor","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260727"}},{"articleId":260720,"title":"What is SQL?","slug":"what-is-sql","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260720"}},{"articleId":260678,"title":"ODBC: Interfacing with SQL Databases","slug":"odbc-interfacing-with-sql-databases","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260678"}}],"fromCategory":[{"articleId":260869,"title":"Using SQL Constraints Within Transactions","slug":"using-sql-constraints-within-transactions","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260869"}},{"articleId":260866,"title":"How to Use the SQL Union Join","slug":"how-to-use-the-sql-union-join","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260866"}},{"articleId":260727,"title":"How to Declare a SQL Cursor","slug":"how-to-declare-a-sql-cursor","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260727"}},{"articleId":260720,"title":"What is SQL?","slug":"what-is-sql","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260720"}},{"articleId":260678,"title":"ODBC: Interfacing with SQL Databases","slug":"odbc-interfacing-with-sql-databases","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260678"}}]},"hasRelatedBookFromSearch":false,"relatedBook":{"bookId":281871,"slug":"sql-for-dummies-9th-edition","isbn":"9781119527077","categoryList":["technology","programming-web-design","sql"],"amazon":{"default":"https://www.amazon.com/gp/product/1119527074/ref=as_li_tl?ie=UTF8&tag=wiley01-20","ca":"https://www.amazon.ca/gp/product/1119527074/ref=as_li_tl?ie=UTF8&tag=wiley01-20","indigo_ca":"http://www.tkqlhce.com/click-9208661-13710633?url=https://www.chapters.indigo.ca/en-ca/books/product/1119527074-item.html&cjsku=978111945484","gb":"https://www.amazon.co.uk/gp/product/1119527074/ref=as_li_tl?ie=UTF8&tag=wiley01-20","de":"https://www.amazon.de/gp/product/1119527074/ref=as_li_tl?ie=UTF8&tag=wiley01-20"},"image":{"src":"https://www.dummies.com/wp-content/uploads/sql-for-dummies-9th-edition-cover-9781119527077-203x255.jpg","width":203,"height":255},"title":"SQL For Dummies","testBankPinActivationLink":"","bookOutOfPrint":false,"authorsInfo":"<p><b data-author-id=\"34913\">Allen G. Taylor</b> is a 30-year veteran of the computer industry and the author of over 40 books, including <b><i>SQL For Dummies</i></b> and <i>Crystal Reports For Dummies.</i> He lectures nationally on databases, innovation, and entrepreneurship. He also teaches database development internationally through a leading online education provider. </p>","authors":[{"authorId":34913,"name":"Allen G. Taylor","slug":"allen-g.-taylor","description":" <p><b>Allen G. Taylor</b> is a 30&#45;year veteran of the computer industry and the author of over 40 books, including <b><i>SQL For Dummies</i></b> and <i>Crystal Reports For Dummies.</i> He lectures nationally on databases, innovation, and entrepreneurship. He also teaches database development internationally through a leading online education provider. ","_links":{"self":"https://dummies-api.dummies.com/v2/authors/34913"}}],"_links":{"self":"https://dummies-api.dummies.com/v2/books/"}},"collections":[],"articleAds":{"footerAd":"<div class=\"du-ad-region row\" id=\"article_page_adhesion_ad\"><div class=\"du-ad-unit col-md-12\" data-slot-id=\"article_page_adhesion_ad\" data-refreshed=\"false\" \r\n data-target = \"[{&quot;key&quot;:&quot;cat&quot;,&quot;values&quot;:[&quot;technology&quot;,&quot;programming-web-design&quot;,&quot;sql&quot;]},{&quot;key&quot;:&quot;isbn&quot;,&quot;values&quot;:[&quot;9781119527077&quot;]}]\" id=\"du-slot-62b36f63c795a\"></div></div>","rightAd":"<div class=\"du-ad-region row\" id=\"article_page_right_ad\"><div class=\"du-ad-unit col-md-12\" data-slot-id=\"article_page_right_ad\" data-refreshed=\"false\" \r\n data-target = \"[{&quot;key&quot;:&quot;cat&quot;,&quot;values&quot;:[&quot;technology&quot;,&quot;programming-web-design&quot;,&quot;sql&quot;]},{&quot;key&quot;:&quot;isbn&quot;,&quot;values&quot;:[&quot;9781119527077&quot;]}]\" id=\"du-slot-62b36f63c80c8\"></div></div>"},"articleType":{"articleType":"Articles","articleList":null,"content":null,"videoInfo":{"videoId":null,"name":null,"accountId":null,"playerId":null,"thumbnailUrl":null,"description":null,"uploadDate":null}},"sponsorship":{"sponsorshipPage":false,"backgroundImage":{"src":null,"width":0,"height":0},"brandingLine":"","brandingLink":"","brandingLogo":{"src":null,"width":0,"height":0},"sponsorAd":null,"sponsorEbookTitle":null,"sponsorEbookLink":null,"sponsorEbookImage":null},"primaryLearningPath":"Advance","lifeExpectancy":"Two years","lifeExpectancySetFrom":"2022-01-14T00:00:00+00:00","dummiesForKids":"no","sponsoredContent":"no","adInfo":"","adPairKey":[]},"status":"publish","visibility":"public","articleId":160784},{"headers":{"creationTime":"2016-03-26T14:00:36+00:00","modifiedTime":"2022-01-14T19:39:30+00:00","timestamp":"2022-06-22T19:37:07+00:00"},"data":{"breadcrumbs":[{"name":"Technology","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33512"},"slug":"technology","categoryId":33512},{"name":"Programming & Web Design","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33592"},"slug":"programming-web-design","categoryId":33592},{"name":"SQL","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33608"},"slug":"sql","categoryId":33608}],"title":"How to Use EXISTS, UNIQUE, DISTINCT, and OVERLAPS in SQL Statements","strippedTitle":"how to use exists, unique, distinct, and overlaps in sql statements","slug":"how-to-use-exists-unique-distinct-and-overlaps-in-sql-statements","canonicalUrl":"","seo":{"metaDescription":"Within the WHERE clause lies many possibilities for modifying your SQL statement. Among these possibilities are the EXISTS, UNIQUE, DISTINCT, and OVERLAPS predi","noIndex":0,"noFollow":0},"content":"Within the WHERE clause lies many possibilities for modifying your SQL statement. Among these possibilities are the EXISTS, UNIQUE, DISTINCT, and OVERLAPS predicates. Here are some examples of how to use these in your SQL statements.\r\n<h2 id=\"tab1\" >EXISTS</h2>\r\nYou can use the <span class=\"code\">EXISTS</span> predicate in conjunction with a subquery to determine whether the subquery returns any rows. If the subquery returns at least one row, that result satisfies the <span class=\"code\">EXISTS</span> condition, and the outer query executes. Consider the following example:\r\n<pre class=\"code\">SELECT FirstName, LastName\r\n FROM CUSTOMER\r\n WHERE EXISTS\r\n (SELECT DISTINCT CustomerID\r\n FROM SALES\r\n WHERE SALES.CustomerID = CUSTOMER.CustomerID);</pre>\r\nHere the SALES table contains all of your company’s sales transactions. The table includes the <span class=\"code\">CustomerID</span> of the customer who makes each purchase, as well as other pertinent information. The CUSTOMER table contains each customer’s first and last names, but no information about specific transactions.\r\n\r\nThe subquery in the preceding example returns a row for every customer who has made at least one purchase. The outer query returns the first and last names of the customers who made the purchases that the SALES table records.\r\n\r\n<span class=\"code\">EXISTS</span> is equivalent to a comparison of <span class=\"code\">COUNT</span> with zero, as the following query shows:\r\n<pre class=\"code\">SELECT FirstName, LastName\r\n FROM CUSTOMER\r\n WHERE 0 <>\r\n (SELECT COUNT(*)\r\n FROM SALES\r\n WHERE SALES.CustomerID = CUSTOMER.CustomerID);</pre>\r\nFor every row in the SALES table that contains a <span class=\"code\">CustomerID</span> that’s equal to a <span class=\"code\">CustomerID</span> in the CUSTOMER table, this statement displays the <span class=\"code\">FirstName</span> and <span class=\"code\">LastName</span> columns in the CUSTOMER table. For every sale in the SALES table, therefore, the statement displays the name of the customer who made the purchase.\r\n<h2 id=\"tab2\" >UNIQUE</h2>\r\nAs you do with the <span class=\"code\">EXISTS</span> predicate, you use the <span class=\"code\">UNIQUE</span> predicate with a subquery. Although the <span class=\"code\">EXISTS</span> predicate evaluates to True only if the subquery returns at least one row, the <span class=\"code\">UNIQUE</span> predicate evaluates to True only if no two rows returned by the subquery are identical. In other words, the <span class=\"code\">UNIQUE</span> predicate evaluates to True only if all the rows that its subquery returns are unique.\r\n\r\nConsider the following example:\r\n<pre class=\"code\">SELECT FirstName, LastName\r\n FROM CUSTOMER\r\n WHERE UNIQUE\r\n (SELECT CustomerID FROM SALES\r\n WHERE SALES.CustomerID = CUSTOMER.CustomerID);</pre>\r\nThis statement retrieves the names of all new customers for whom the SALES table records only one sale. Because a null value is an unknown value, two null values aren’t considered equal to each other; when the <span class=\"code\">UNIQUE</span> keyword is applied to a result table that contains only two null rows, the <span class=\"code\">UNIQUE</span> predicate evaluates to True.\r\n<h2 id=\"tab3\" >DISTINCT</h2>\r\nThe <span class=\"code\">DISTINCT</span> predicate is similar to the <span class=\"code\">UNIQUE</span> predicate, except in the way it treats nulls. If all the values in a result table are <span class=\"code\">UNIQUE</span>, then they’re also <span class=\"code\">DISTINCT</span> from each other.\r\n\r\nHowever, unlike the result for the <span class=\"code\">UNIQUE</span> predicate, if the <span class=\"code\">DISTINCT</span> keyword is applied to a result table that contains only two null rows, the <span class=\"code\">DISTINCT</span> predicate evaluates to False. Two null values are <i>not</i> considered distinct from each other, while at the same time they are considered to be unique.\r\n\r\nThis strange situation seems contradictory, but there’s a reason for it. In some situations, you may want to treat two null values as different from each other — in which case, use the <span class=\"code\">UNIQUE</span> predicate. When you want to treat the two nulls as if they’re the same, use the <span class=\"code\">DISTINCT</span> predicate.\r\n<h2 id=\"tab4\" >OVERLAPS</h2>\r\nYou use the <span class=\"code\">OVERLAPS</span> predicate to determine whether two time intervals overlap each other. This predicate is useful for avoiding scheduling conflicts. If the two intervals overlap, the predicate returns a True value. If they don’t overlap, the predicate returns a False value.\r\n\r\nYou can specify an interval in two ways: either as a start time and an end time or as a start time and a duration. Here are some examples:\r\n<pre class=\"code\">(TIME '2:55:00', INTERVAL '1' HOUR)\r\nOVERLAPS\r\n(TIME '3:30:00', INTERVAL '2' HOUR)</pre>\r\nThis first example returns a True because 3:30 is less than one hour after 2:55.\r\n<pre class=\"code\">(TIME '9:00:00', TIME '9:30:00')\r\nOVERLAPS\r\n(TIME '9:29:00', TIME '9:31:00')</pre>\r\nThis example returns a True because you have a one-minute overlap between the two intervals.\r\n<pre class=\"code\">(TIME '9:00:00', TIME '10:00:00')\r\nOVERLAPS\r\n(TIME '10:15:00', INTERVAL '3' HOUR)</pre>\r\nThis example returns a False because the two intervals don’t overlap.\r\n<pre class=\"code\">(TIME '9:00:00', TIME '9:30:00')\r\nOVERLAPS\r\n(TIME '9:30:00', TIME '9:35:00')</pre>\r\nThis example returns a False because even though the two intervals are contiguous, they don’t overlap.","description":"Within the WHERE clause lies many possibilities for modifying your SQL statement. Among these possibilities are the EXISTS, UNIQUE, DISTINCT, and OVERLAPS predicates. Here are some examples of how to use these in your SQL statements.\r\n<h2 id=\"tab1\" >EXISTS</h2>\r\nYou can use the <span class=\"code\">EXISTS</span> predicate in conjunction with a subquery to determine whether the subquery returns any rows. If the subquery returns at least one row, that result satisfies the <span class=\"code\">EXISTS</span> condition, and the outer query executes. Consider the following example:\r\n<pre class=\"code\">SELECT FirstName, LastName\r\n FROM CUSTOMER\r\n WHERE EXISTS\r\n (SELECT DISTINCT CustomerID\r\n FROM SALES\r\n WHERE SALES.CustomerID = CUSTOMER.CustomerID);</pre>\r\nHere the SALES table contains all of your company’s sales transactions. The table includes the <span class=\"code\">CustomerID</span> of the customer who makes each purchase, as well as other pertinent information. The CUSTOMER table contains each customer’s first and last names, but no information about specific transactions.\r\n\r\nThe subquery in the preceding example returns a row for every customer who has made at least one purchase. The outer query returns the first and last names of the customers who made the purchases that the SALES table records.\r\n\r\n<span class=\"code\">EXISTS</span> is equivalent to a comparison of <span class=\"code\">COUNT</span> with zero, as the following query shows:\r\n<pre class=\"code\">SELECT FirstName, LastName\r\n FROM CUSTOMER\r\n WHERE 0 <>\r\n (SELECT COUNT(*)\r\n FROM SALES\r\n WHERE SALES.CustomerID = CUSTOMER.CustomerID);</pre>\r\nFor every row in the SALES table that contains a <span class=\"code\">CustomerID</span> that’s equal to a <span class=\"code\">CustomerID</span> in the CUSTOMER table, this statement displays the <span class=\"code\">FirstName</span> and <span class=\"code\">LastName</span> columns in the CUSTOMER table. For every sale in the SALES table, therefore, the statement displays the name of the customer who made the purchase.\r\n<h2 id=\"tab2\" >UNIQUE</h2>\r\nAs you do with the <span class=\"code\">EXISTS</span> predicate, you use the <span class=\"code\">UNIQUE</span> predicate with a subquery. Although the <span class=\"code\">EXISTS</span> predicate evaluates to True only if the subquery returns at least one row, the <span class=\"code\">UNIQUE</span> predicate evaluates to True only if no two rows returned by the subquery are identical. In other words, the <span class=\"code\">UNIQUE</span> predicate evaluates to True only if all the rows that its subquery returns are unique.\r\n\r\nConsider the following example:\r\n<pre class=\"code\">SELECT FirstName, LastName\r\n FROM CUSTOMER\r\n WHERE UNIQUE\r\n (SELECT CustomerID FROM SALES\r\n WHERE SALES.CustomerID = CUSTOMER.CustomerID);</pre>\r\nThis statement retrieves the names of all new customers for whom the SALES table records only one sale. Because a null value is an unknown value, two null values aren’t considered equal to each other; when the <span class=\"code\">UNIQUE</span> keyword is applied to a result table that contains only two null rows, the <span class=\"code\">UNIQUE</span> predicate evaluates to True.\r\n<h2 id=\"tab3\" >DISTINCT</h2>\r\nThe <span class=\"code\">DISTINCT</span> predicate is similar to the <span class=\"code\">UNIQUE</span> predicate, except in the way it treats nulls. If all the values in a result table are <span class=\"code\">UNIQUE</span>, then they’re also <span class=\"code\">DISTINCT</span> from each other.\r\n\r\nHowever, unlike the result for the <span class=\"code\">UNIQUE</span> predicate, if the <span class=\"code\">DISTINCT</span> keyword is applied to a result table that contains only two null rows, the <span class=\"code\">DISTINCT</span> predicate evaluates to False. Two null values are <i>not</i> considered distinct from each other, while at the same time they are considered to be unique.\r\n\r\nThis strange situation seems contradictory, but there’s a reason for it. In some situations, you may want to treat two null values as different from each other — in which case, use the <span class=\"code\">UNIQUE</span> predicate. When you want to treat the two nulls as if they’re the same, use the <span class=\"code\">DISTINCT</span> predicate.\r\n<h2 id=\"tab4\" >OVERLAPS</h2>\r\nYou use the <span class=\"code\">OVERLAPS</span> predicate to determine whether two time intervals overlap each other. This predicate is useful for avoiding scheduling conflicts. If the two intervals overlap, the predicate returns a True value. If they don’t overlap, the predicate returns a False value.\r\n\r\nYou can specify an interval in two ways: either as a start time and an end time or as a start time and a duration. Here are some examples:\r\n<pre class=\"code\">(TIME '2:55:00', INTERVAL '1' HOUR)\r\nOVERLAPS\r\n(TIME '3:30:00', INTERVAL '2' HOUR)</pre>\r\nThis first example returns a True because 3:30 is less than one hour after 2:55.\r\n<pre class=\"code\">(TIME '9:00:00', TIME '9:30:00')\r\nOVERLAPS\r\n(TIME '9:29:00', TIME '9:31:00')</pre>\r\nThis example returns a True because you have a one-minute overlap between the two intervals.\r\n<pre class=\"code\">(TIME '9:00:00', TIME '10:00:00')\r\nOVERLAPS\r\n(TIME '10:15:00', INTERVAL '3' HOUR)</pre>\r\nThis example returns a False because the two intervals don’t overlap.\r\n<pre class=\"code\">(TIME '9:00:00', TIME '9:30:00')\r\nOVERLAPS\r\n(TIME '9:30:00', TIME '9:35:00')</pre>\r\nThis example returns a False because even though the two intervals are contiguous, they don’t overlap.","blurb":"","authors":[{"authorId":9559,"name":"Allen G. Taylor","slug":"allen-g-taylor","description":"Allen G. Taylor is a 40-year veteran of the computer industry and the author of more than 40 books, including SQL For Dummies and Database Development For Dummies. For the latest news on Taylor's activities, check out his online courses (at pioneer-academy1.teachable.com).","_links":{"self":"https://dummies-api.dummies.com/v2/authors/9559"}}],"primaryCategoryTaxonomy":{"categoryId":33608,"title":"SQL","slug":"sql","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33608"}},"secondaryCategoryTaxonomy":{"categoryId":0,"title":null,"slug":null,"_links":null},"tertiaryCategoryTaxonomy":{"categoryId":0,"title":null,"slug":null,"_links":null},"trendingArticles":null,"inThisArticle":[{"label":"EXISTS","target":"#tab1"},{"label":"UNIQUE","target":"#tab2"},{"label":"DISTINCT","target":"#tab3"},{"label":"OVERLAPS","target":"#tab4"}],"relatedArticles":{"fromBook":[{"articleId":260869,"title":"Using SQL Constraints Within Transactions","slug":"using-sql-constraints-within-transactions","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260869"}},{"articleId":260866,"title":"How to Use the SQL Union Join","slug":"how-to-use-the-sql-union-join","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260866"}},{"articleId":260727,"title":"How to Declare a SQL Cursor","slug":"how-to-declare-a-sql-cursor","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260727"}},{"articleId":260720,"title":"What is SQL?","slug":"what-is-sql","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260720"}},{"articleId":260678,"title":"ODBC: Interfacing with SQL Databases","slug":"odbc-interfacing-with-sql-databases","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260678"}}],"fromCategory":[{"articleId":260869,"title":"Using SQL Constraints Within Transactions","slug":"using-sql-constraints-within-transactions","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260869"}},{"articleId":260866,"title":"How to Use the SQL Union Join","slug":"how-to-use-the-sql-union-join","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260866"}},{"articleId":260727,"title":"How to Declare a SQL Cursor","slug":"how-to-declare-a-sql-cursor","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260727"}},{"articleId":260720,"title":"What is SQL?","slug":"what-is-sql","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260720"}},{"articleId":260678,"title":"ODBC: Interfacing with SQL Databases","slug":"odbc-interfacing-with-sql-databases","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260678"}}]},"hasRelatedBookFromSearch":false,"relatedBook":{"bookId":281871,"slug":"sql-for-dummies-9th-edition","isbn":"9781119527077","categoryList":["technology","programming-web-design","sql"],"amazon":{"default":"https://www.amazon.com/gp/product/1119527074/ref=as_li_tl?ie=UTF8&tag=wiley01-20","ca":"https://www.amazon.ca/gp/product/1119527074/ref=as_li_tl?ie=UTF8&tag=wiley01-20","indigo_ca":"http://www.tkqlhce.com/click-9208661-13710633?url=https://www.chapters.indigo.ca/en-ca/books/product/1119527074-item.html&cjsku=978111945484","gb":"https://www.amazon.co.uk/gp/product/1119527074/ref=as_li_tl?ie=UTF8&tag=wiley01-20","de":"https://www.amazon.de/gp/product/1119527074/ref=as_li_tl?ie=UTF8&tag=wiley01-20"},"image":{"src":"https://www.dummies.com/wp-content/uploads/sql-for-dummies-9th-edition-cover-9781119527077-203x255.jpg","width":203,"height":255},"title":"SQL For Dummies","testBankPinActivationLink":"","bookOutOfPrint":false,"authorsInfo":"<p><b data-author-id=\"34913\">Allen G. Taylor</b> is a 30-year veteran of the computer industry and the author of over 40 books, including <b><i>SQL For Dummies</i></b> and <i>Crystal Reports For Dummies.</i> He lectures nationally on databases, innovation, and entrepreneurship. He also teaches database development internationally through a leading online education provider. </p>","authors":[{"authorId":34913,"name":"Allen G. Taylor","slug":"allen-g.-taylor","description":" <p><b>Allen G. Taylor</b> is a 30&#45;year veteran of the computer industry and the author of over 40 books, including <b><i>SQL For Dummies</i></b> and <i>Crystal Reports For Dummies.</i> He lectures nationally on databases, innovation, and entrepreneurship. He also teaches database development internationally through a leading online education provider. ","_links":{"self":"https://dummies-api.dummies.com/v2/authors/34913"}}],"_links":{"self":"https://dummies-api.dummies.com/v2/books/"}},"collections":[],"articleAds":{"footerAd":"<div class=\"du-ad-region row\" id=\"article_page_adhesion_ad\"><div class=\"du-ad-unit col-md-12\" data-slot-id=\"article_page_adhesion_ad\" data-refreshed=\"false\" \r\n data-target = \"[{&quot;key&quot;:&quot;cat&quot;,&quot;values&quot;:[&quot;technology&quot;,&quot;programming-web-design&quot;,&quot;sql&quot;]},{&quot;key&quot;:&quot;isbn&quot;,&quot;values&quot;:[&quot;9781119527077&quot;]}]\" id=\"du-slot-62b36f63c118c\"></div></div>","rightAd":"<div class=\"du-ad-region row\" id=\"article_page_right_ad\"><div class=\"du-ad-unit col-md-12\" data-slot-id=\"article_page_right_ad\" data-refreshed=\"false\" \r\n data-target = \"[{&quot;key&quot;:&quot;cat&quot;,&quot;values&quot;:[&quot;technology&quot;,&quot;programming-web-design&quot;,&quot;sql&quot;]},{&quot;key&quot;:&quot;isbn&quot;,&quot;values&quot;:[&quot;9781119527077&quot;]}]\" id=\"du-slot-62b36f63c18ed\"></div></div>"},"articleType":{"articleType":"Articles","articleList":null,"content":null,"videoInfo":{"videoId":null,"name":null,"accountId":null,"playerId":null,"thumbnailUrl":null,"description":null,"uploadDate":null}},"sponsorship":{"sponsorshipPage":false,"backgroundImage":{"src":null,"width":0,"height":0},"brandingLine":"","brandingLink":"","brandingLogo":{"src":null,"width":0,"height":0},"sponsorAd":null,"sponsorEbookTitle":null,"sponsorEbookLink":null,"sponsorEbookImage":null},"primaryLearningPath":"Advance","lifeExpectancy":"Two years","lifeExpectancySetFrom":"2022-01-14T00:00:00+00:00","dummiesForKids":"no","sponsoredContent":"no","adInfo":"","adPairKey":[]},"status":"publish","visibility":"public","articleId":160789},{"headers":{"creationTime":"2016-03-26T14:01:00+00:00","modifiedTime":"2022-01-14T19:38:32+00:00","timestamp":"2022-06-22T19:37:07+00:00"},"data":{"breadcrumbs":[{"name":"Technology","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33512"},"slug":"technology","categoryId":33512},{"name":"Programming & Web Design","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33592"},"slug":"programming-web-design","categoryId":33592},{"name":"SQL","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33608"},"slug":"sql","categoryId":33608}],"title":"How to Design an SQL Database","strippedTitle":"how to design an sql database","slug":"how-to-design-a-sql-database","canonicalUrl":"","seo":{"metaDescription":"The first step to designing any database in SQL is to identify what to include and what not to include. The next steps involve deciding how the included items r","noIndex":0,"noFollow":0},"content":"The first step to designing any database in SQL is to identify what to include and what not to include. The next steps involve deciding how the included items relate to each other and then setting up tables accordingly.\r\n\r\nTo design a database in SQL, follow these basic steps:\r\n<ol class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">Decide what objects you want to include in your database.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Determine which of these objects should be tables and which should be columns within those tables.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Define tables based on how you need to organize the objects.</p>\r\n<p class=\"child-para\"><b></b>Optionally, you may want to designate a table column or a combination of columns as a key.</p>\r\n</li>\r\n</ol>\r\n<h2 id=\"tab1\" >Step 1: Define objects</h2>\r\nThe first step in designing a database is deciding which aspects of the system are important enough to include in the model. Treat each aspect as an object and create a list of all the objects you can think of. At this stage, don’t try to decide how these objects relate to each other. Just try to list them all.\r\n\r\nWhen you have a reasonably complete set of objects, move on to the next step: deciding how these objects relate to each other. Some of the objects are major entities that are crucial to giving you the results you want. Other objects are subsidiary to those major entities. Ultimately you may decide that some objects don’t belong in the model at all.\r\n<h2 id=\"tab2\" >Step 2: Identify tables and columns</h2>\r\n<i>Major entities</i> translate into database tables. Each major entity has a set of <i>attributes</i> — the table columns. Many business databases, for example, have a CUSTOMER table that keeps track of customers’ names, addresses, and other permanent information. Each attribute of a customer — such as name, street, city, state, zip code, phone number, and e-mail address — becomes a column (and a column heading) in the CUSTOMER table.\r\n\r\nIf you’re hoping to find a set of rules to help you identify which objects should be tables and which of the attributes in the system belong to which tables, think again: You may have some reasons for assigning a particular attribute to one table and other reasons for assigning <i>the same attribute</i> to another table. You must base your judgment on two goals:\r\n<ul class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">The information you want to get from the database</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">How you want to use that information</p>\r\n</li>\r\n</ul>\r\n<p class=\"Tip\">When deciding how to structure database tables, involve future users of the database as well as the people who will make decisions based on database information. If you come up with what you think is a reasonable structure, but it isn’t consistent with the way that people will use the information, your system will be frustrating to use at best — and could even produce wrong information, which is even worse.</p>\r\nTake a look at an example. Suppose you just established VetLab, a clinical microbiology laboratory that tests biological specimens sent in by veterinarians. You want to track several things, including the following:\r\n<ul class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">Clients</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Tests that you perform</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Employees</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Orders</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Results</p>\r\n</li>\r\n</ul>\r\nEach of these entities has associated attributes. Each client has a name, an address, and other contact information. Each test has a name and a standard charge. Each employee has contact information as well as a job classification and pay rate. For each order, you need to know who ordered it, when it was ordered, and what test was ordered. For each test result, you need to know the outcome of the test, whether the results were preliminary or final, and the test order number.\r\n<h2 id=\"tab3\" >Step 3: Define tables</h2>\r\nNow you want to define a table for each entity and a column for each attribute.\r\n<table>\r\n<tbody>\r\n<tr>\r\n<th>Table</th>\r\n<th>Columns</th>\r\n</tr>\r\n<tr>\r\n<td>CLIENT</td>\r\n<td>Client Name</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Address 1</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Address 2</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>City</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>State</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Postal Code</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Phone</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Fax</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Contact Person</td>\r\n</tr>\r\n<tr>\r\n<td>TESTS</td>\r\n<td>Test Name</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Standard Charge</td>\r\n</tr>\r\n<tr>\r\n<td>EMPLOYEE</td>\r\n<td>Employee Name</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Address 1</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Address 2</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>City</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>State</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Postal Code</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Home Phone</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Office Extension</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Hire Date</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Job Classification</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Hourly/Salary/Commission</td>\r\n</tr>\r\n<tr>\r\n<td>ORDERS</td>\r\n<td>Order Number</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Client Name</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Test Ordered</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Responsible Salesperson</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Order Date</td>\r\n</tr>\r\n<tr>\r\n<td>RESULTS</td>\r\n<td>Result Number</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Order Number</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Result</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Date Reported</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Preliminary/Final</td>\r\n</tr>\r\n</tbody>\r\n</table>\r\nYou can create the tables defined here by using either a rapid application development (RAD) tool or by using SQL’s Data Definition Language (DDL), as shown in the following code:\r\n<pre class=\"code\">CREATE TABLE CLIENT (\r\n ClientName CHAR (30) NOT NULL,\r\n Address1 CHAR (30),\r\n Address2 CHAR (30),\r\n City CHAR (25),\r\n State CHAR (2),\r\n PostalCode CHAR (10),\r\n Phone CHAR (13),\r\n Fax CHAR (13),\r\n ContactPerson CHAR (30) ) ;\r\nCREATE TABLE TESTS (\r\n TestName CHAR (30) NOT NULL,\r\n StandardCharge CHAR (30) ) ;\r\nCREATE TABLE EMPLOYEE (\r\n EmployeeName CHAR (30) NOT NULL,\r\n Address1 CHAR (30),\r\n Address2 CHAR (30),\r\n City CHAR (25),\r\n State CHAR (2),\r\n PostalCode CHAR (10),\r\n HomePhone CHAR (13),\r\n OfficeExtension CHAR (4),\r\n HireDate DATE,\r\n JobClassification CHAR (10),\r\n HourSalComm CHAR (1) ) ;\r\nCREATE TABLE ORDERS (\r\n OrderNumber INTEGER NOT NULL,\r\n ClientName CHAR (30),\r\n TestOrdered CHAR (30),\r\n Salesperson CHAR (30),\r\n OrderDate DATE ) ;\r\nCREATE TABLE RESULTS (\r\n ResultNumber INTEGER NOT NULL,\r\n OrderNumber INTEGER,\r\n Result CHAR(50),\r\n DateReported DATE,\r\n PrelimFinal CHAR (1) ) ;</pre>\r\nThese tables relate to each other by the attributes (columns) that they share, as the following list describes:\r\n<ul class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">The CLIENT table links to the ORDERS table by the <span class=\"code\">ClientName</span> column.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">The TESTS table links to the ORDERS table by the <span class=\"code\">TestName (TestOrdered)</span> column.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">The EMPLOYEE table links to the ORDERS table by the<span class=\"code\"> EmployeeName (Salesperson)</span> column.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">The RESULTS table links to the ORDERS table by the <span class=\"code\">OrderNumber</span> column.</p>\r\n</li>\r\n</ul>\r\nIf you want a table to serve as an integral part of a relational database, link that table to at least one other table in the database, using a common column.\r\n\r\n<img src=\"https://www.dummies.com/wp-content/uploads/399603.image0.jpg\" alt=\"image0.jpg\" width=\"402\" height=\"400\" />\r\n\r\nThe links illustrate four different <i>one-to-many</i> relationships. The diamond in the middle of each relationship shows the maximum cardinality of each end of the relationship. The number 1 denotes the “one” side of the relationship, and N denotes the “many” side.\r\n<ul class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">One client can make many orders, but each order is made by one, and only one, client.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Each test can appear on many orders, but each order calls for one, and only one, test.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Each order is taken by one, and only one, employee (or salesperson), but each salesperson can take multiple orders.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Each order can produce several preliminary test results and a final result, but each result is associated with one, and only one, order.</p>\r\n</li>\r\n</ul>\r\nThe attribute that links one table to another can have a different name in each table. Both attributes must, however, have matching data types.","description":"The first step to designing any database in SQL is to identify what to include and what not to include. The next steps involve deciding how the included items relate to each other and then setting up tables accordingly.\r\n\r\nTo design a database in SQL, follow these basic steps:\r\n<ol class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">Decide what objects you want to include in your database.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Determine which of these objects should be tables and which should be columns within those tables.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Define tables based on how you need to organize the objects.</p>\r\n<p class=\"child-para\"><b></b>Optionally, you may want to designate a table column or a combination of columns as a key.</p>\r\n</li>\r\n</ol>\r\n<h2 id=\"tab1\" >Step 1: Define objects</h2>\r\nThe first step in designing a database is deciding which aspects of the system are important enough to include in the model. Treat each aspect as an object and create a list of all the objects you can think of. At this stage, don’t try to decide how these objects relate to each other. Just try to list them all.\r\n\r\nWhen you have a reasonably complete set of objects, move on to the next step: deciding how these objects relate to each other. Some of the objects are major entities that are crucial to giving you the results you want. Other objects are subsidiary to those major entities. Ultimately you may decide that some objects don’t belong in the model at all.\r\n<h2 id=\"tab2\" >Step 2: Identify tables and columns</h2>\r\n<i>Major entities</i> translate into database tables. Each major entity has a set of <i>attributes</i> — the table columns. Many business databases, for example, have a CUSTOMER table that keeps track of customers’ names, addresses, and other permanent information. Each attribute of a customer — such as name, street, city, state, zip code, phone number, and e-mail address — becomes a column (and a column heading) in the CUSTOMER table.\r\n\r\nIf you’re hoping to find a set of rules to help you identify which objects should be tables and which of the attributes in the system belong to which tables, think again: You may have some reasons for assigning a particular attribute to one table and other reasons for assigning <i>the same attribute</i> to another table. You must base your judgment on two goals:\r\n<ul class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">The information you want to get from the database</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">How you want to use that information</p>\r\n</li>\r\n</ul>\r\n<p class=\"Tip\">When deciding how to structure database tables, involve future users of the database as well as the people who will make decisions based on database information. If you come up with what you think is a reasonable structure, but it isn’t consistent with the way that people will use the information, your system will be frustrating to use at best — and could even produce wrong information, which is even worse.</p>\r\nTake a look at an example. Suppose you just established VetLab, a clinical microbiology laboratory that tests biological specimens sent in by veterinarians. You want to track several things, including the following:\r\n<ul class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">Clients</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Tests that you perform</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Employees</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Orders</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Results</p>\r\n</li>\r\n</ul>\r\nEach of these entities has associated attributes. Each client has a name, an address, and other contact information. Each test has a name and a standard charge. Each employee has contact information as well as a job classification and pay rate. For each order, you need to know who ordered it, when it was ordered, and what test was ordered. For each test result, you need to know the outcome of the test, whether the results were preliminary or final, and the test order number.\r\n<h2 id=\"tab3\" >Step 3: Define tables</h2>\r\nNow you want to define a table for each entity and a column for each attribute.\r\n<table>\r\n<tbody>\r\n<tr>\r\n<th>Table</th>\r\n<th>Columns</th>\r\n</tr>\r\n<tr>\r\n<td>CLIENT</td>\r\n<td>Client Name</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Address 1</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Address 2</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>City</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>State</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Postal Code</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Phone</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Fax</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Contact Person</td>\r\n</tr>\r\n<tr>\r\n<td>TESTS</td>\r\n<td>Test Name</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Standard Charge</td>\r\n</tr>\r\n<tr>\r\n<td>EMPLOYEE</td>\r\n<td>Employee Name</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Address 1</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Address 2</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>City</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>State</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Postal Code</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Home Phone</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Office Extension</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Hire Date</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Job Classification</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Hourly/Salary/Commission</td>\r\n</tr>\r\n<tr>\r\n<td>ORDERS</td>\r\n<td>Order Number</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Client Name</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Test Ordered</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Responsible Salesperson</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Order Date</td>\r\n</tr>\r\n<tr>\r\n<td>RESULTS</td>\r\n<td>Result Number</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Order Number</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Result</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Date Reported</td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n</tr>\r\n<tr>\r\n<td></td>\r\n<td>Preliminary/Final</td>\r\n</tr>\r\n</tbody>\r\n</table>\r\nYou can create the tables defined here by using either a rapid application development (RAD) tool or by using SQL’s Data Definition Language (DDL), as shown in the following code:\r\n<pre class=\"code\">CREATE TABLE CLIENT (\r\n ClientName CHAR (30) NOT NULL,\r\n Address1 CHAR (30),\r\n Address2 CHAR (30),\r\n City CHAR (25),\r\n State CHAR (2),\r\n PostalCode CHAR (10),\r\n Phone CHAR (13),\r\n Fax CHAR (13),\r\n ContactPerson CHAR (30) ) ;\r\nCREATE TABLE TESTS (\r\n TestName CHAR (30) NOT NULL,\r\n StandardCharge CHAR (30) ) ;\r\nCREATE TABLE EMPLOYEE (\r\n EmployeeName CHAR (30) NOT NULL,\r\n Address1 CHAR (30),\r\n Address2 CHAR (30),\r\n City CHAR (25),\r\n State CHAR (2),\r\n PostalCode CHAR (10),\r\n HomePhone CHAR (13),\r\n OfficeExtension CHAR (4),\r\n HireDate DATE,\r\n JobClassification CHAR (10),\r\n HourSalComm CHAR (1) ) ;\r\nCREATE TABLE ORDERS (\r\n OrderNumber INTEGER NOT NULL,\r\n ClientName CHAR (30),\r\n TestOrdered CHAR (30),\r\n Salesperson CHAR (30),\r\n OrderDate DATE ) ;\r\nCREATE TABLE RESULTS (\r\n ResultNumber INTEGER NOT NULL,\r\n OrderNumber INTEGER,\r\n Result CHAR(50),\r\n DateReported DATE,\r\n PrelimFinal CHAR (1) ) ;</pre>\r\nThese tables relate to each other by the attributes (columns) that they share, as the following list describes:\r\n<ul class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">The CLIENT table links to the ORDERS table by the <span class=\"code\">ClientName</span> column.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">The TESTS table links to the ORDERS table by the <span class=\"code\">TestName (TestOrdered)</span> column.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">The EMPLOYEE table links to the ORDERS table by the<span class=\"code\"> EmployeeName (Salesperson)</span> column.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">The RESULTS table links to the ORDERS table by the <span class=\"code\">OrderNumber</span> column.</p>\r\n</li>\r\n</ul>\r\nIf you want a table to serve as an integral part of a relational database, link that table to at least one other table in the database, using a common column.\r\n\r\n<img src=\"https://www.dummies.com/wp-content/uploads/399603.image0.jpg\" alt=\"image0.jpg\" width=\"402\" height=\"400\" />\r\n\r\nThe links illustrate four different <i>one-to-many</i> relationships. The diamond in the middle of each relationship shows the maximum cardinality of each end of the relationship. The number 1 denotes the “one” side of the relationship, and N denotes the “many” side.\r\n<ul class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">One client can make many orders, but each order is made by one, and only one, client.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Each test can appear on many orders, but each order calls for one, and only one, test.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Each order is taken by one, and only one, employee (or salesperson), but each salesperson can take multiple orders.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Each order can produce several preliminary test results and a final result, but each result is associated with one, and only one, order.</p>\r\n</li>\r\n</ul>\r\nThe attribute that links one table to another can have a different name in each table. Both attributes must, however, have matching data types.","blurb":"","authors":[{"authorId":9559,"name":"Allen G. Taylor","slug":"allen-g-taylor","description":"Allen G. Taylor is a 40-year veteran of the computer industry and the author of more than 40 books, including SQL For Dummies and Database Development For Dummies. For the latest news on Taylor's activities, check out his online courses (at pioneer-academy1.teachable.com).","_links":{"self":"https://dummies-api.dummies.com/v2/authors/9559"}}],"primaryCategoryTaxonomy":{"categoryId":33608,"title":"SQL","slug":"sql","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33608"}},"secondaryCategoryTaxonomy":{"categoryId":0,"title":null,"slug":null,"_links":null},"tertiaryCategoryTaxonomy":{"categoryId":0,"title":null,"slug":null,"_links":null},"trendingArticles":null,"inThisArticle":[{"label":"Step 1: Define objects","target":"#tab1"},{"label":"Step 2: Identify tables and columns","target":"#tab2"},{"label":"Step 3: Define tables","target":"#tab3"}],"relatedArticles":{"fromBook":[{"articleId":260869,"title":"Using SQL Constraints Within Transactions","slug":"using-sql-constraints-within-transactions","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260869"}},{"articleId":260866,"title":"How to Use the SQL Union Join","slug":"how-to-use-the-sql-union-join","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260866"}},{"articleId":260727,"title":"How to Declare a SQL Cursor","slug":"how-to-declare-a-sql-cursor","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260727"}},{"articleId":260720,"title":"What is SQL?","slug":"what-is-sql","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260720"}},{"articleId":260678,"title":"ODBC: Interfacing with SQL Databases","slug":"odbc-interfacing-with-sql-databases","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260678"}}],"fromCategory":[{"articleId":260869,"title":"Using SQL Constraints Within Transactions","slug":"using-sql-constraints-within-transactions","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260869"}},{"articleId":260866,"title":"How to Use the SQL Union Join","slug":"how-to-use-the-sql-union-join","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260866"}},{"articleId":260727,"title":"How to Declare a SQL Cursor","slug":"how-to-declare-a-sql-cursor","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260727"}},{"articleId":260720,"title":"What is SQL?","slug":"what-is-sql","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260720"}},{"articleId":260678,"title":"ODBC: Interfacing with SQL Databases","slug":"odbc-interfacing-with-sql-databases","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260678"}}]},"hasRelatedBookFromSearch":false,"relatedBook":{"bookId":281871,"slug":"sql-for-dummies-9th-edition","isbn":"9781119527077","categoryList":["technology","programming-web-design","sql"],"amazon":{"default":"https://www.amazon.com/gp/product/1119527074/ref=as_li_tl?ie=UTF8&tag=wiley01-20","ca":"https://www.amazon.ca/gp/product/1119527074/ref=as_li_tl?ie=UTF8&tag=wiley01-20","indigo_ca":"http://www.tkqlhce.com/click-9208661-13710633?url=https://www.chapters.indigo.ca/en-ca/books/product/1119527074-item.html&cjsku=978111945484","gb":"https://www.amazon.co.uk/gp/product/1119527074/ref=as_li_tl?ie=UTF8&tag=wiley01-20","de":"https://www.amazon.de/gp/product/1119527074/ref=as_li_tl?ie=UTF8&tag=wiley01-20"},"image":{"src":"https://www.dummies.com/wp-content/uploads/sql-for-dummies-9th-edition-cover-9781119527077-203x255.jpg","width":203,"height":255},"title":"SQL For Dummies","testBankPinActivationLink":"","bookOutOfPrint":false,"authorsInfo":"<p><b data-author-id=\"34913\">Allen G. Taylor</b> is a 30-year veteran of the computer industry and the author of over 40 books, including <b><i>SQL For Dummies</i></b> and <i>Crystal Reports For Dummies.</i> He lectures nationally on databases, innovation, and entrepreneurship. He also teaches database development internationally through a leading online education provider. </p>","authors":[{"authorId":34913,"name":"Allen G. Taylor","slug":"allen-g.-taylor","description":" <p><b>Allen G. Taylor</b> is a 30&#45;year veteran of the computer industry and the author of over 40 books, including <b><i>SQL For Dummies</i></b> and <i>Crystal Reports For Dummies.</i> He lectures nationally on databases, innovation, and entrepreneurship. He also teaches database development internationally through a leading online education provider. ","_links":{"self":"https://dummies-api.dummies.com/v2/authors/34913"}}],"_links":{"self":"https://dummies-api.dummies.com/v2/books/"}},"collections":[],"articleAds":{"footerAd":"<div class=\"du-ad-region row\" id=\"article_page_adhesion_ad\"><div class=\"du-ad-unit col-md-12\" data-slot-id=\"article_page_adhesion_ad\" data-refreshed=\"false\" \r\n data-target = \"[{&quot;key&quot;:&quot;cat&quot;,&quot;values&quot;:[&quot;technology&quot;,&quot;programming-web-design&quot;,&quot;sql&quot;]},{&quot;key&quot;:&quot;isbn&quot;,&quot;values&quot;:[&quot;9781119527077&quot;]}]\" id=\"du-slot-62b36f63ba12a\"></div></div>","rightAd":"<div class=\"du-ad-region row\" id=\"article_page_right_ad\"><div class=\"du-ad-unit col-md-12\" data-slot-id=\"article_page_right_ad\" data-refreshed=\"false\" \r\n data-target = \"[{&quot;key&quot;:&quot;cat&quot;,&quot;values&quot;:[&quot;technology&quot;,&quot;programming-web-design&quot;,&quot;sql&quot;]},{&quot;key&quot;:&quot;isbn&quot;,&quot;values&quot;:[&quot;9781119527077&quot;]}]\" id=\"du-slot-62b36f63ba8cd\"></div></div>"},"articleType":{"articleType":"Articles","articleList":null,"content":null,"videoInfo":{"videoId":null,"name":null,"accountId":null,"playerId":null,"thumbnailUrl":null,"description":null,"uploadDate":null}},"sponsorship":{"sponsorshipPage":false,"backgroundImage":{"src":null,"width":0,"height":0},"brandingLine":"","brandingLink":"","brandingLogo":{"src":null,"width":0,"height":0},"sponsorAd":null,"sponsorEbookTitle":null,"sponsorEbookLink":null,"sponsorEbookImage":null},"primaryLearningPath":"Advance","lifeExpectancy":"Two years","lifeExpectancySetFrom":"2022-01-14T00:00:00+00:00","dummiesForKids":"no","sponsoredContent":"no","adInfo":"","adPairKey":[]},"status":"publish","visibility":"public","articleId":160840},{"headers":{"creationTime":"2016-03-26T14:01:02+00:00","modifiedTime":"2022-01-14T19:37:50+00:00","timestamp":"2022-06-22T19:37:07+00:00"},"data":{"breadcrumbs":[{"name":"Technology","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33512"},"slug":"technology","categoryId":33512},{"name":"Programming & Web Design","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33592"},"slug":"programming-web-design","categoryId":33592},{"name":"SQL","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33608"},"slug":"sql","categoryId":33608}],"title":"How to Create an SQL Table with Microsoft Access","strippedTitle":"how to create an sql table with microsoft access","slug":"how-to-create-a-sql-table-with-microsoft-access","canonicalUrl":"","seo":{"metaDescription":"Whether you’re working with Access or a full-featured enterprise-level DBMS — like Microsoft SQL Server, Oracle, or IBM DB2 — to create a table with SQL, you mu","noIndex":0,"noFollow":0},"content":"Whether you’re working with Access or a full-featured enterprise-level DBMS — like Microsoft SQL Server, Oracle, or IBM DB2 — to create a table with SQL, you must enter the same information that you’d enter if you created the table with a RAD tool.\r\n\r\nThe difference is that the RAD tool helps you by providing a visual interface — in the form of a table-creation dialog box (or some similar data-entry skeleton) — and by preventing you from entering invalid field names, types, or sizes.\r\n<p class=\"Warning\">SQL doesn’t give you as much help. You must know what you’re doing at the onset; figuring things out along the way can lead to less-than-desirable database results. You must enter the entire <span class=\"code\">CREATE TABLE</span> statement before SQL even looks at it, let alone gives you any indication of whether you made errors in the statement.</p>\r\nIn ISO/IEC standard SQL, the statement that creates a proposal-tracking table uses the following syntax:\r\n<pre class=\"code\">CREATE TABLE POWERSQL (\r\n ProposalNumber INTEGER PRIMARY KEY,\r\n FirstName CHAR (15),\r\n LastName CHAR (20),\r\n Address CHAR (30),\r\n City CHAR (25),\r\n StateProvince CHAR (2),\r\n PostalCode CHAR (10),\r\n Country CHAR (30),\r\n Phone CHAR (14),\r\n HowKnown CHAR (30),\r\nProposal CHAR (50),\r\nBusinessOrCharity CHAR (1) );</pre>\r\nThe information in the SQL statement is essentially the same information you enter using Access’s graphical user interface. The nice thing about SQL is that the language is universal. The same standard syntax works regardless of what standard-compliant DBMS product you use.\r\n\r\nIn Access 2016, creating database objects such as tables is a little more complicated. You can’t just type a <span class=\"code\">CREATE</span> statement into the SQL View Object tab. That’s because the SQL View Object tab is available only as a query tool; you have to take a few extra actions to inform Access that you’re about to enter a data-definition query rather than a query that requests information from the database.\r\n\r\nA further complication: Because table creation is an action that could possibly compromise database security, it’s disallowed by default. You must tell Access that this is a trusted database before it will accept a data-definition query.\r\n<ol class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">Click the Create tab on the Ribbon to display the icons for creation functionality.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Click Query Design in the Queries section.</p>\r\n<p class=\"child-para\">This displays the Show Table dialog box, which at this point contains several system tables along with POWER.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Select POWER and click the Add button.</p>\r\n<p class=\"child-para\">As you’ve seen in the previous example, a picture of the POWER table and its attributes appears in the upper half of the work area.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Click the Close button on the Show Table dialog box.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Click the Home tab and then pull down the View menu at the left end of the Ribbon and then choose SQL View from the menu.</p>\r\n<p class=\"child-para\">As in the previous example, Access has “helped” you by putting <span class=\"code\">SELECT FROM POWER</span> in the SQL editor. This time you don’t want the help.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Delete <span class=\"code\">SELECT FROM POWER</span> and (in its place) enter the data-definition query given earlier, as follows:</p>\r\n\r\n<pre class=\"code\">CREATE TABLE POWERSQL (\r\n ProposalNumber INTEGER PRIMARY KEY,\r\n FirstName CHAR (15),\r\n LastName CHAR (20),\r\n Address CHAR (30),\r\n City CHAR (25),\r\n StateProvince CHAR (2),\r\n PostalCode CHAR (10),\r\n Country CHAR (30),\r\n Phone CHAR (14),\r\n HowKnown CHAR (30),\r\n Proposal CHAR (50),\r\n BusinOrCharity CHAR (1) );</pre>\r\n<img src=\"https://www.dummies.com/wp-content/uploads/399599.image0.jpg\" alt=\"image0.jpg\" width=\"535\" height=\"372\" /></li>\r\n \t<li>\r\n<p class=\"first-para\">After clicking the Design tab of the Ribbon, click the red exclamation point Run icon.</p>\r\n<p class=\"child-para\">Doing so runs the query, which creates the POWERSQL table.</p>\r\n<img src=\"https://www.dummies.com/wp-content/uploads/399600.image1.jpg\" alt=\"image1.jpg\" width=\"535\" height=\"331\" />\r\n<p class=\"child-para\">You should see POWERSQL listed under All Access Objects in the column at the left edge of the window. In which case, you’re golden. Or you may not see the table in the All Access Objects list. In that case, read (and slog) on.</p>\r\n<p class=\"child-para\">Access 2016 goes to great lengths to protect you from malicious hackers and from your own inadvertent mistakes. Because running a data-definition query is potentially dangerous to the database, Access has a default that prevents the query from running. If this has happened to you, POWERSQL <i>won’t</i> appear in the column at the left of the window, because the query won’t have been executed.</p>\r\n<p class=\"child-para\">Instead, the Message Bar may appear below the Ribbon, with this terse message:</p>\r\n\r\n<pre class=\"code\">Security Warning: Certain content in the database has been disabled.</pre>\r\n<p class=\"child-para\">If you see this message, move on to the next steps.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Click the File tab and, from the menu of the left edge, choose Options.</p>\r\n<p class=\"child-para\">The Access Options dialog box appears.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Select Trust Center from the Access Options dialog box.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Click the Trust Center Settings button when it appears.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Select Message Bar from the menu on the left and then specify Show the Message Bar by clicking its option button if it isn’t already selected.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Click your way back to the place where you can execute the data-definition query that creates the POWERSQL table.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Execute the query.</p>\r\n</li>\r\n</ol>\r\n<p class=\"Remember\">Becoming proficient in SQL has long-term payoffs because it will be around for a long time. The effort you put into becoming an expert in a particular development tool is likely to yield a lower return on investment. No matter how wonderful the latest RAD tool may be, it will be superseded by newer technology within three to five years.</p>\r\nIf you can recover your investment in the tool in that time, great! Use it. If not, you may be wise to stick with the tried and true. Train your people in SQL, and your training investment will pay dividends over a much longer period.","description":"Whether you’re working with Access or a full-featured enterprise-level DBMS — like Microsoft SQL Server, Oracle, or IBM DB2 — to create a table with SQL, you must enter the same information that you’d enter if you created the table with a RAD tool.\r\n\r\nThe difference is that the RAD tool helps you by providing a visual interface — in the form of a table-creation dialog box (or some similar data-entry skeleton) — and by preventing you from entering invalid field names, types, or sizes.\r\n<p class=\"Warning\">SQL doesn’t give you as much help. You must know what you’re doing at the onset; figuring things out along the way can lead to less-than-desirable database results. You must enter the entire <span class=\"code\">CREATE TABLE</span> statement before SQL even looks at it, let alone gives you any indication of whether you made errors in the statement.</p>\r\nIn ISO/IEC standard SQL, the statement that creates a proposal-tracking table uses the following syntax:\r\n<pre class=\"code\">CREATE TABLE POWERSQL (\r\n ProposalNumber INTEGER PRIMARY KEY,\r\n FirstName CHAR (15),\r\n LastName CHAR (20),\r\n Address CHAR (30),\r\n City CHAR (25),\r\n StateProvince CHAR (2),\r\n PostalCode CHAR (10),\r\n Country CHAR (30),\r\n Phone CHAR (14),\r\n HowKnown CHAR (30),\r\nProposal CHAR (50),\r\nBusinessOrCharity CHAR (1) );</pre>\r\nThe information in the SQL statement is essentially the same information you enter using Access’s graphical user interface. The nice thing about SQL is that the language is universal. The same standard syntax works regardless of what standard-compliant DBMS product you use.\r\n\r\nIn Access 2016, creating database objects such as tables is a little more complicated. You can’t just type a <span class=\"code\">CREATE</span> statement into the SQL View Object tab. That’s because the SQL View Object tab is available only as a query tool; you have to take a few extra actions to inform Access that you’re about to enter a data-definition query rather than a query that requests information from the database.\r\n\r\nA further complication: Because table creation is an action that could possibly compromise database security, it’s disallowed by default. You must tell Access that this is a trusted database before it will accept a data-definition query.\r\n<ol class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">Click the Create tab on the Ribbon to display the icons for creation functionality.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Click Query Design in the Queries section.</p>\r\n<p class=\"child-para\">This displays the Show Table dialog box, which at this point contains several system tables along with POWER.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Select POWER and click the Add button.</p>\r\n<p class=\"child-para\">As you’ve seen in the previous example, a picture of the POWER table and its attributes appears in the upper half of the work area.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Click the Close button on the Show Table dialog box.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Click the Home tab and then pull down the View menu at the left end of the Ribbon and then choose SQL View from the menu.</p>\r\n<p class=\"child-para\">As in the previous example, Access has “helped” you by putting <span class=\"code\">SELECT FROM POWER</span> in the SQL editor. This time you don’t want the help.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Delete <span class=\"code\">SELECT FROM POWER</span> and (in its place) enter the data-definition query given earlier, as follows:</p>\r\n\r\n<pre class=\"code\">CREATE TABLE POWERSQL (\r\n ProposalNumber INTEGER PRIMARY KEY,\r\n FirstName CHAR (15),\r\n LastName CHAR (20),\r\n Address CHAR (30),\r\n City CHAR (25),\r\n StateProvince CHAR (2),\r\n PostalCode CHAR (10),\r\n Country CHAR (30),\r\n Phone CHAR (14),\r\n HowKnown CHAR (30),\r\n Proposal CHAR (50),\r\n BusinOrCharity CHAR (1) );</pre>\r\n<img src=\"https://www.dummies.com/wp-content/uploads/399599.image0.jpg\" alt=\"image0.jpg\" width=\"535\" height=\"372\" /></li>\r\n \t<li>\r\n<p class=\"first-para\">After clicking the Design tab of the Ribbon, click the red exclamation point Run icon.</p>\r\n<p class=\"child-para\">Doing so runs the query, which creates the POWERSQL table.</p>\r\n<img src=\"https://www.dummies.com/wp-content/uploads/399600.image1.jpg\" alt=\"image1.jpg\" width=\"535\" height=\"331\" />\r\n<p class=\"child-para\">You should see POWERSQL listed under All Access Objects in the column at the left edge of the window. In which case, you’re golden. Or you may not see the table in the All Access Objects list. In that case, read (and slog) on.</p>\r\n<p class=\"child-para\">Access 2016 goes to great lengths to protect you from malicious hackers and from your own inadvertent mistakes. Because running a data-definition query is potentially dangerous to the database, Access has a default that prevents the query from running. If this has happened to you, POWERSQL <i>won’t</i> appear in the column at the left of the window, because the query won’t have been executed.</p>\r\n<p class=\"child-para\">Instead, the Message Bar may appear below the Ribbon, with this terse message:</p>\r\n\r\n<pre class=\"code\">Security Warning: Certain content in the database has been disabled.</pre>\r\n<p class=\"child-para\">If you see this message, move on to the next steps.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Click the File tab and, from the menu of the left edge, choose Options.</p>\r\n<p class=\"child-para\">The Access Options dialog box appears.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Select Trust Center from the Access Options dialog box.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Click the Trust Center Settings button when it appears.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Select Message Bar from the menu on the left and then specify Show the Message Bar by clicking its option button if it isn’t already selected.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Click your way back to the place where you can execute the data-definition query that creates the POWERSQL table.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Execute the query.</p>\r\n</li>\r\n</ol>\r\n<p class=\"Remember\">Becoming proficient in SQL has long-term payoffs because it will be around for a long time. The effort you put into becoming an expert in a particular development tool is likely to yield a lower return on investment. No matter how wonderful the latest RAD tool may be, it will be superseded by newer technology within three to five years.</p>\r\nIf you can recover your investment in the tool in that time, great! Use it. If not, you may be wise to stick with the tried and true. Train your people in SQL, and your training investment will pay dividends over a much longer period.","blurb":"","authors":[{"authorId":9559,"name":"Allen G. Taylor","slug":"allen-g-taylor","description":"Allen G. Taylor is a 40-year veteran of the computer industry and the author of more than 40 books, including SQL For Dummies and Database Development For Dummies. For the latest news on Taylor's activities, check out his online courses (at pioneer-academy1.teachable.com).","_links":{"self":"https://dummies-api.dummies.com/v2/authors/9559"}}],"primaryCategoryTaxonomy":{"categoryId":33608,"title":"SQL","slug":"sql","_links":{"self":"https://dummies-api.dummies.com/v2/categories/33608"}},"secondaryCategoryTaxonomy":{"categoryId":0,"title":null,"slug":null,"_links":null},"tertiaryCategoryTaxonomy":{"categoryId":0,"title":null,"slug":null,"_links":null},"trendingArticles":null,"inThisArticle":[],"relatedArticles":{"fromBook":[{"articleId":260869,"title":"Using SQL Constraints Within Transactions","slug":"using-sql-constraints-within-transactions","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260869"}},{"articleId":260866,"title":"How to Use the SQL Union Join","slug":"how-to-use-the-sql-union-join","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260866"}},{"articleId":260727,"title":"How to Declare a SQL Cursor","slug":"how-to-declare-a-sql-cursor","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260727"}},{"articleId":260720,"title":"What is SQL?","slug":"what-is-sql","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260720"}},{"articleId":260678,"title":"ODBC: Interfacing with SQL Databases","slug":"odbc-interfacing-with-sql-databases","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260678"}}],"fromCategory":[{"articleId":260869,"title":"Using SQL Constraints Within Transactions","slug":"using-sql-constraints-within-transactions","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260869"}},{"articleId":260866,"title":"How to Use the SQL Union Join","slug":"how-to-use-the-sql-union-join","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260866"}},{"articleId":260727,"title":"How to Declare a SQL Cursor","slug":"how-to-declare-a-sql-cursor","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260727"}},{"articleId":260720,"title":"What is SQL?","slug":"what-is-sql","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260720"}},{"articleId":260678,"title":"ODBC: Interfacing with SQL Databases","slug":"odbc-interfacing-with-sql-databases","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/260678"}}]},"hasRelatedBookFromSearch":false,"relatedBook":{"bookId":281871,"slug":"sql-for-dummies-9th-edition","isbn":"9781119527077","categoryList":["technology","programming-web-design","sql"],"amazon":{"default":"https://www.amazon.com/gp/product/1119527074/ref=as_li_tl?ie=UTF8&tag=wiley01-20","ca":"https://www.amazon.ca/gp/product/1119527074/ref=as_li_tl?ie=UTF8&tag=wiley01-20","indigo_ca":"http://www.tkqlhce.com/click-9208661-13710633?url=https://www.chapters.indigo.ca/en-ca/books/product/1119527074-item.html&cjsku=978111945484","gb":"https://www.amazon.co.uk/gp/product/1119527074/ref=as_li_tl?ie=UTF8&tag=wiley01-20","de":"https://www.amazon.de/gp/product/1119527074/ref=as_li_tl?ie=UTF8&tag=wiley01-20"},"image":{"src":"https://www.dummies.com/wp-content/uploads/sql-for-dummies-9th-edition-cover-9781119527077-203x255.jpg","width":203,"height":255},"title":"SQL For Dummies","testBankPinActivationLink":"","bookOutOfPrint":false,"authorsInfo":"<p><b data-author-id=\"34913\">Allen G. Taylor</b> is a 30-year veteran of the computer industry and the author of over 40 books, including <b><i>SQL For Dummies</i></b> and <i>Crystal Reports For Dummies.</i> He lectures nationally on databases, innovation, and entrepreneurship. He also teaches database development internationally through a leading online education provider. </p>","authors":[{"authorId":34913,"name":"Allen G. Taylor","slug":"allen-g.-taylor","description":" <p><b>Allen G. Taylor</b> is a 30&#45;year veteran of the computer industry and the author of over 40 books, including <b><i>SQL For Dummies</i></b> and <i>Crystal Reports For Dummies.</i> He lectures nationally on databases, innovation, and entrepreneurship. He also teaches database development internationally through a leading online education provider. ","_links":{"self":"https://dummies-api.dummies.com/v2/authors/34913"}}],"_links":{"self":"https://dummies-api.dummies.com/v2/books/"}},"collections":[],"articleAds":{"footerAd":"<div class=\"du-ad-region row\" id=\"article_page_adhesion_ad\"><div class=\"du-ad-unit col-md-12\" data-slot-id=\"article_page_adhesion_ad\" data-refreshed=\"false\" \r\n data-target = \"[{&quot;key&quot;:&quot;cat&quot;,&quot;values&quot;:[&quot;technology&quot;,&quot;programming-web-design&quot;,&quot;sql&quot;]},{&quot;key&quot;:&quot;isbn&quot;,&quot;values&quot;:[&quot;9781119527077&quot;]}]\" id=\"du-slot-62b36f63b3065\"></div></div>","rightAd":"<div class=\"du-ad-region row\" id=\"article_page_right_ad\"><div class=\"du-ad-unit col-md-12\" data-slot-id=\"article_page_right_ad\" data-refreshed=\"false\" \r\n data-target = \"[{&quot;key&quot;:&quot;cat&quot;,&quot;values&quot;:[&quot;technology&quot;,&quot;programming-web-design&quot;,&quot;sql&quot;]},{&quot;key&quot;:&quot;isbn&quot;,&quot;values&quot;:[&quot;9781119527077&quot;]}]\" id=\"du-slot-62b36f63b380d\"></div></div>"},"articleType":{"articleType":"Articles","articleList":null,"content":null,"videoInfo":{"videoId":null,"name":null,"accountId":null,"playerId":null,"thumbnailUrl":null,"description":null,"uploadDate":null}},"sponsorship":{"sponsorshipPage":false,"backgroundImage":{"src":null,"width":0,"height":0},"brandingLine":"","brandingLink":"","brandingLogo":{"src":null,"width":0,"height":0},"sponsorAd":null,"sponsorEbookTitle":null,"sponsorEbookLink":null,"sponsorEbookImage":null},"primaryLearningPath":"Advance","lifeExpectancy":"Two years","lifeExpectancySetFrom":"2022-01-14T00:00:00+00:00","dummiesForKids":"no","sponsoredContent":"no","adInfo":"","adPairKey":[]},"status":"publish","visibility":"public","articleId":160843}],"_links":{"self":{"self":"https://dummies-api.dummies.com/v2/categories/33608/categoryArticles?sortField=time&sortOrder=1&size=10&offset=0"},"next":{"self":"https://dummies-api.dummies.com/v2/categories/33608/categoryArticles?sortField=time&sortOrder=1&size=10&offset=10"},"last":{"self":"https://dummies-api.dummies.com/v2/categories/33608/categoryArticles?sortField=time&sortOrder=1&size=10&offset=17"}}},"objectTitle":"","status":"success","pageType":"article-category","objectId":"33608","page":1,"sortField":"time","sortOrder":1,"categoriesIds":[],"articleTypes":[],"filterData":{"categoriesFilter":[{"itemId":0,"itemName":"All Categories","count":27}],"articleTypeFilter":[{"articleType":"All Types","count":27},{"articleType":"Articles","count":24},{"articleType":"Cheat Sheet","count":2},{"articleType":"Step by Step","count":1}]},"filterDataLoadedStatus":"success","pageSize":10},"adsState":{"pageScripts":{"headers":{"timestamp":"2022-08-15T06:59:02+00:00"},"adsId":0,"data":{"scripts":[{"pages":["all"],"location":"header","script":"<!--Optimizely Script-->\r\n<script src=\"https://cdn.optimizely.com/js/10563184655.js\"></script>","enabled":false},{"pages":["all"],"location":"header","script":"<!-- comScore Tag -->\r\n<script>var _comscore = _comscore || [];_comscore.push({ c1: \"2\", c2: \"15097263\" });(function() {var s = document.createElement(\"script\"), el = document.getElementsByTagName(\"script\")[0]; s.async = true;s.src = (document.location.protocol == \"https:\" ? \"https://sb\" : \"http://b\") + \".scorecardresearch.com/beacon.js\";el.parentNode.insertBefore(s, el);})();</script><noscript><img src=\"https://sb.scorecardresearch.com/p?c1=2&c2=15097263&cv=2.0&cj=1\" /></noscript>\r\n<!-- / comScore Tag -->","enabled":true},{"pages":["all"],"location":"footer","script":"<!--BEGIN QUALTRICS WEBSITE FEEDBACK SNIPPET-->\r\n<script type='text/javascript'>\r\n(function(){var g=function(e,h,f,g){\r\nthis.get=function(a){for(var a=a+\"=\",c=document.cookie.split(\";\"),b=0,e=c.length;b<e;b++){for(var d=c[b];\" \"==d.charAt(0);)d=d.substring(1,d.length);if(0==d.indexOf(a))return d.substring(a.length,d.length)}return null};\r\nthis.set=function(a,c){var b=\"\",b=new Date;b.setTime(b.getTime()+6048E5);b=\"; expires=\"+b.toGMTString();document.cookie=a+\"=\"+c+b+\"; path=/; \"};\r\nthis.check=function(){var a=this.get(f);if(a)a=a.split(\":\");else if(100!=e)\"v\"==h&&(e=Math.random()>=e/100?0:100),a=[h,e,0],this.set(f,a.join(\":\"));else return!0;var c=a[1];if(100==c)return!0;switch(a[0]){case \"v\":return!1;case \"r\":return c=a[2]%Math.floor(100/c),a[2]++,this.set(f,a.join(\":\")),!c}return!0};\r\nthis.go=function(){if(this.check()){var a=document.createElement(\"script\");a.type=\"text/javascript\";a.src=g;document.body&&document.body.appendChild(a)}};\r\nthis.start=function(){var t=this;\"complete\"!==document.readyState?window.addEventListener?window.addEventListener(\"load\",function(){t.go()},!1):window.attachEvent&&window.attachEvent(\"onload\",function(){t.go()}):t.go()};};\r\ntry{(new g(100,\"r\",\"QSI_S_ZN_5o5yqpvMVjgDOuN\",\"https://zn5o5yqpvmvjgdoun-wiley.siteintercept.qualtrics.com/SIE/?Q_ZID=ZN_5o5yqpvMVjgDOuN\")).start()}catch(i){}})();\r\n</script><div id='ZN_5o5yqpvMVjgDOuN'><!--DO NOT REMOVE-CONTENTS PLACED HERE--></div>\r\n<!--END WEBSITE FEEDBACK SNIPPET-->","enabled":false},{"pages":["all"],"location":"header","script":"<!-- Hotjar Tracking Code for http://www.dummies.com -->\r\n<script>\r\n (function(h,o,t,j,a,r){\r\n h.hj=h.hj||function(){(h.hj.q=h.hj.q||[]).push(arguments)};\r\n h._hjSettings={hjid:257151,hjsv:6};\r\n a=o.getElementsByTagName('head')[0];\r\n r=o.createElement('script');r.async=1;\r\n r.src=t+h._hjSettings.hjid+j+h._hjSettings.hjsv;\r\n a.appendChild(r);\r\n })(window,document,'https://static.hotjar.com/c/hotjar-','.js?sv=');\r\n</script>","enabled":false},{"pages":["article"],"location":"header","script":"<!-- //Connect Container: dummies --> <script src=\"//get.s-onetag.com/bffe21a1-6bb8-4928-9449-7beadb468dae/tag.min.js\" async defer></script>","enabled":true},{"pages":["homepage"],"location":"header","script":"<meta name=\"facebook-domain-verification\" content=\"irk8y0irxf718trg3uwwuexg6xpva0\" />","enabled":true},{"pages":["homepage","article","category","search"],"location":"footer","script":"<!-- Facebook Pixel Code -->\r\n<noscript>\r\n<img height=\"1\" width=\"1\" src=\"https://www.facebook.com/tr?id=256338321977984&ev=PageView&noscript=1\"/>\r\n</noscript>\r\n<!-- End Facebook Pixel Code -->","enabled":true}]}},"pageScriptsLoadedStatus":"success"},"navigationState":{"navigationCollections":[{"collectionId":287568,"title":"BYOB (Be Your Own Boss)","hasSubCategories":false,"url":"/collection/for-the-entry-level-entrepreneur-287568"},{"collectionId":293237,"title":"Be a Rad Dad","hasSubCategories":false,"url":"/collection/be-the-best-dad-293237"},{"collectionId":294090,"title":"Contemplating the Cosmos","hasSubCategories":false,"url":"/collection/theres-something-about-space-294090"},{"collectionId":287563,"title":"For Those Seeking Peace of Mind","hasSubCategories":false,"url":"/collection/for-those-seeking-peace-of-mind-287563"},{"collectionId":287570,"title":"For the Aspiring Aficionado","hasSubCategories":false,"url":"/collection/for-the-bougielicious-287570"},{"collectionId":291903,"title":"For the Budding Cannabis Enthusiast","hasSubCategories":false,"url":"/collection/for-the-budding-cannabis-enthusiast-291903"},{"collectionId":291934,"title":"For the Exam-Season Crammer","hasSubCategories":false,"url":"/collection/for-the-exam-season-crammer-291934"},{"collectionId":287569,"title":"For the Hopeless Romantic","hasSubCategories":false,"url":"/collection/for-the-hopeless-romantic-287569"},{"collectionId":287567,"title":"For the Unabashed Hippie","hasSubCategories":false,"url":"/collection/for-the-unabashed-hippie-287567"},{"collectionId":292186,"title":"Just DIY It","hasSubCategories":false,"url":"/collection/just-diy-it-292186"}],"navigationCollectionsLoadedStatus":"success","navigationCategories":{"books":{"0":{"data":[{"categoryId":33512,"title":"Technology","hasSubCategories":true,"url":"/category/books/technology-33512"},{"categoryId":33662,"title":"Academics & The Arts","hasSubCategories":true,"url":"/category/books/academics-the-arts-33662"},{"categoryId":33809,"title":"Home, Auto, & Hobbies","hasSubCategories":true,"url":"/category/books/home-auto-hobbies-33809"},{"categoryId":34038,"title":"Body, Mind, & Spirit","hasSubCategories":true,"url":"/category/books/body-mind-spirit-34038"},{"categoryId":34224,"title":"Business, Careers, & Money","hasSubCategories":true,"url":"/category/books/business-careers-money-34224"}],"breadcrumbs":[],"categoryTitle":"Level 0 Category","mainCategoryUrl":"/category/books/level-0-category-0"}},"articles":{"0":{"data":[{"categoryId":33512,"title":"Technology","hasSubCategories":true,"url":"/category/articles/technology-33512"},{"categoryId":33662,"title":"Academics & The Arts","hasSubCategories":true,"url":"/category/articles/academics-the-arts-33662"},{"categoryId":33809,"title":"Home, Auto, & Hobbies","hasSubCategories":true,"url":"/category/articles/home-auto-hobbies-33809"},{"categoryId":34038,"title":"Body, Mind, & Spirit","hasSubCategories":true,"url":"/category/articles/body-mind-spirit-34038"},{"categoryId":34224,"title":"Business, Careers, & Money","hasSubCategories":true,"url":"/category/articles/business-careers-money-34224"}],"breadcrumbs":[],"categoryTitle":"Level 0 Category","mainCategoryUrl":"/category/articles/level-0-category-0"}}},"navigationCategoriesLoadedStatus":"success"},"searchState":{"searchList":[],"searchStatus":"initial","relatedArticlesList":[],"relatedArticlesStatus":"initial"},"routeState":{"name":"ArticleCategory","path":"/category/articles/sql-33608/","hash":"","query":{},"params":{"category":"sql-33608"},"fullPath":"/category/articles/sql-33608/","meta":{"routeType":"category","breadcrumbInfo":{"suffix":"Articles","baseRoute":"/category/articles"},"prerenderWithAsyncData":true},"from":{"name":null,"path":"/","hash":"","query":{},"params":{},"fullPath":"/","meta":{}}},"sfmcState":{"status":"initial"},"profileState":{"auth":{},"userOptions":{},"status":"initial"}}
Logo
  • Articles Open Article Categories
  • Books Open Book Categories
  • Collections Open Collections list
  • Custom Solutions

Article Categories

Book Categories

Collections

Explore all collections
BYOB (Be Your Own Boss)
Be a Rad Dad
Contemplating the Cosmos
For Those Seeking Peace of Mind
For the Aspiring Aficionado
For the Budding Cannabis Enthusiast
For the Exam-Season Crammer
For the Hopeless Romantic
For the Unabashed Hippie
Just DIY It
Log In
  • Home
  • Technology Articles
  • Programming & Web Design Articles
  • SQL Articles

SQL Articles

Know how to get your data moving when the database says, "Talk SQL to me."

Articles From SQL

page 1
page 2
page 3

Filter Results

27 results
27 results
SQL SQL All-in-One For Dummies Cheat Sheet

Cheat Sheet / Updated 04-25-2022

SQL is a popular and useful programming language. You can make SQL even more useful if you know the phases of SQL development, the criteria for normal forms, the data types used by SQL, a little bit about set and value functions, as well as some tips on how to filter tables with WHERE clauses.

View Cheat Sheet
SQL SQL For Dummies Cheat Sheet

Cheat Sheet / Updated 01-27-2022

This Cheat Sheet consists of several helpful tables and lists, containing information that comes up repeatedly when working with structured query language (SQL). In one place, you can get a quick answer to a number of different questions that frequently arise during an SQL development effort.

View Cheat Sheet
SQL How to Use GROUP BY, HAVING, and ORDER BY SQL Clauses

Article / Updated 01-18-2022

SQL gives you options for retrieving, analyzing, and displaying the information you need with the GROUP BY, HAVING, and ORDER BY clauses. Here are some examples of how you can use them. GROUP BY clauses Sometimes, rather than retrieving individual records, you want to know something about a group of records. The GROUP BY clause is the tool you need. Suppose you’re the sales manager of another location, and you want to look at the performance of your sales force. If you do a simple SELECT, such as the following query: SELECT InvoiceNo, SaleDate, Salesperson, TotalSale FROM SALES; This result gives you some idea of how well your salespeople are doing because so few total sales are involved. However, in real life, a company would have many more sales — and it wouldn’t be so easy to tell whether sales objectives were being met. To do the real analysis, you can combine the GROUP BY clause with one of the aggregate functions (also called set functions) to get a quantitative picture of sales performance. For example, you can see which salesperson is selling more of the profitable high-ticket items by using the average (AVG) function as follows: SELECT Salesperson, AVG(TotalSale) FROM SALES GROUP BY Salesperson; Running the query with a different database management system would retrieve the same result, but might appear a little different. The average value of Bennett’s sales is considerably higher than that of the other two salespeople. You compare total sales with a similar query: SELECT Salesperson, SUM(TotalSale) FROM SALES GROUP BY Salesperson; Bennett also has the highest total sales, which is consistent with having the highest average sales. HAVING clauses You can analyze the grouped data further by using the HAVING clause. The HAVING clause is a filter that acts similar to a WHERE clause, but on groups of rows rather than on individual rows. To illustrate the function of the HAVING clause, suppose the sales manager considers Bennett to be in a class by himself. His performance distorts the overall data for the other salespeople. (Aha — a curve-wrecker.) You can exclude Bennett’s sales from the grouped data by using a HAVING clause as follows: SELECT Salesperson, SUM(TotalSale) FROM SALES GROUP BY Salesperson HAVING Salesperson <>'Bennett'; Only rows where the salesperson is not Bennett are considered. ORDER BY clauses Use the ORDER BY clause to display the output table of a query in either ascending or descending alphabetical order. Whereas the GROUP BY clause gathers rows into groups and sorts the groups into alphabetical order, ORDER BY sorts individual rows. The ORDER BY clause must be the last clause that you specify in a query. If the query also contains a GROUP BY clause, the clause first arranges the output rows into groups. The ORDER BY clause then sorts the rows within each group. If you have no GROUP BY clause, then the statement considers the entire table as a group, and the ORDER BY clause sorts all its rows according to the column (or columns) that the ORDER BY clause specifies. To illustrate this point, consider the data in the SALES table. The SALES table contains columns for InvoiceNo, SaleDate, Salesperson, and TotalSale. If you use the following example, you see all the data in the SALES table — but in an arbitrary order: SELECT * FROM SALES ; In one implementation, this may be the order in which you inserted the rows in the table; in another implementation, the order may be that of the most recent updates. The order can also change unexpectedly if anyone physically reorganizes the database. That’s one reason it’s usually a good idea to specify the order in which you want the rows. You may, for example, want to see the rows in order by the SaleDate like this: SELECT * FROM SALES ORDER BY SaleDate ; This example returns all the rows in the SALES table in order by SaleDate. For rows with the same SaleDate, the default order depends on the implementation. You can, however, specify how to sort the rows that share the same SaleDate. You may want to see the sales for each SaleDate in order by InvoiceNo, as follows: SELECT * FROM SALES ORDER BY SaleDate, InvoiceNo ; This example first orders the sales by SaleDate; then for each SaleDate, it orders the sales by InvoiceNo. But don’t confuse that example with the following query: SELECT * FROM SALES ORDER BY InvoiceNo, SaleDate ; This query first orders the sales by INVOICE_NO. Then for each different InvoiceNo, the query orders the sales by SaleDate. This probably won’t yield the result you want, because it’s unlikely that multiple sale dates will exist for a single invoice number. The following query is another example of how SQL can return data: SELECT * FROM SALES ORDER BY Salesperson, SaleDate ; This example first orders by Salesperson and then by SaleDate. After you look at the data in that order, you may want to invert it, as follows: SELECT * FROM SALES ORDER BY SaleDate, Salesperson ; This example orders the rows first by SaleDate and then by Salesperson. All these ordering examples are in ascending (ASC) order, which is the default sort order. The last SELECT shows earlier sales first — and, within a given date, shows sales for ‘Adams’ before ‘Baker’. If you prefer descending (DESC) order, you can specify this order for one or more of the order columns, as follows: SELECT * FROM SALES ORDER BY SaleDate DESC, Salesperson ASC ; This example specifies a descending order for sale dates, showing the more recent sales first, and an ascending order for salespeople, putting them in alphabetical order. That should give you a better picture of how Bennett’s performance stacks up against that of the other salespeople.

View Article
SQL Reliably Retrieving Data with SQL

Article / Updated 01-14-2022

After you create a database in SQL, the next step is to fill it with data. The next step after that is to wait around until there's a need for some particular bit of information contained somewhere in that data, like a needle in a haystack. When the time comes and you want to find that needle, you can use SQL's Data Manipulation Language (DML) to perform a virtual needle extraction. Sometimes the data you want is not stored in any single table in your database, but instead pieces of it are scattered across multiple tables. SQL offers several methods of gathering such far-flung data and presenting it to you integrated together in a nice compact result set, some of which are described here: Relational operators have the ability to combine information from multiple sources in a variety of ways. For example, the UNION operator will return all the rows that appear in either of two compatible tables. In contrast, the INTERSECT operator will return only the rows that appear in both of two compatible tables. When you use the EXCEPT operator, all the rows from one table will be returned, except for rows that match rows in the second table. In addition, a wide variety of join operators will enable you to fine-tune your retrievals, pulling just the data you want from whichever tables it might reside in. Nested queries allow you to retrieve data from multiple tables. These are queries on one table that include a subquery on a different table. The subquery is executed first and its result is then passed to the main query to provide a result that contains information from both. Recursive queries have the rather amazing ability to call themselves. This ability can be a major time saver, enabling a search tree to be traversed in a lot less time that an exhaustive search would take. Bill of Materials databases are places where recursive queries can deliver major performance gains.

View Article
SQL SQL First, Second, and Third Normal Forms

Article / Updated 01-14-2022

There are three sources of modification anomalies in SQL These are defined as first, second, and third normal forms (1NF, 2NF, 3NF). These normal forms act as remedies to modification anomalies. First normal form To be in first normal form (1NF), a table must have the following qualities: The table is two-dimensional with rows and columns. Each row contains data that pertains to some thing or portion of a thing. Each column contains data for a single attribute of the thing it’s describing. Each cell (intersection of a row and a column) of the table must have only a single value. Entries in any column must all be of the same kind. If, for example, the entry in one row of a column contains an employee name, all the other rows must contain employee names in that column, too. Each column must have a unique name. No two rows may be identical (that is, each row must be unique). The order of the columns and the order of the rows are not significant. A table (relation) in first normal form is immune to some kinds of modification anomalies but is still subject to others. The SALES table is in first normal form, and the table is subject to deletion and insertion anomalies. First normal form may prove useful in some applications but unreliable in others. Second normal form To appreciate second normal form, you must understand the idea of functional dependency. A functional dependency is a relationship between or among attributes. One attribute is functionally dependent on another if the value of the second attribute determines the value of the first attribute. If you know the value of the second attribute, you can determine the value of the first attribute. Suppose, for example, that a table has attributes (columns) StandardCharge, NumberOfTests, and TotalCharge that relate through the following equation: TotalCharge = StandardCharge * NumberOfTests TotalCharge is functionally dependent on both StandardCharge and NumberOfTests. If you know the values of StandardCharge and NumberOfTests, you can determine the value of TotalCharge. Every table in first normal form must have a unique primary key. That key may consist of one or more than one column. A key consisting of more than one column is called a composite key. To be in second normal form (2NF), all non-key attributes must depend on the entire key. Thus, every relation that is in 1NF with a single attribute key is automatically in second normal form. If a relation has a composite key, all non-key attributes must depend on all components of the key. If you have a table where some non-key attributes don’t depend on all components of the key, break the table up into two or more tables so that — in each of the new tables — all non-key attributes depend on all components of the primary key. Sound confusing? Look at an example to clarify matters. Consider the SALES table. Instead of recording only a single purchase for each customer, you add a row every time a customer buys an item for the first time. An additional difference is that charter customers (those with Customer_ID values of 1001 to 1007) get a discount off the normal price. Customer_ID does not uniquely identify a row. In two rows, Customer_ID is 1001. In two other rows, Customer_ID is 1010. The combination of the Customer_ID column and the Product column uniquely identifies a row. These two columns together are a composite key. If not for the fact that some customers qualify for a discount and others don’t, the table wouldn’t be in second normal form, because Price (a non-key attribute) would depend only on part of the key (Product). Because some customers do qualify for a discount, Price depends on both CustomerID and Product, and the table is in second normal form. Third normal form Tables in second normal form are especially vulnerable to some types of modification anomalies — in particular, those that come from transitive dependencies. A transitive dependency occurs when one attribute depends on a second attribute, which depends on a third attribute. Deletions in a table with such a dependency can cause unwanted information loss. A relation in third normal form is a relation in second normal form with no transitive dependencies. Look again at the SALES table, which you know is in first normal form. As long as you constrain entries to permit only one row for each Customer_ID, you have a single-attribute primary key, and the table is in second normal form. However, the table is still subject to anomalies. What if customer 1010 is unhappy with the chlorine bleach, for example, and returns the item for a refund? You want to remove the third row from the table, which records the fact that customer 1010 bought chlorine bleach. You have a problem: If you remove that row, you also lose the fact that chlorine bleach has a price of $4. This situation is an example of a transitive dependency. Price depends on Product, which, in turn, depends on the primary key Customer_ID. Breaking the SALES table into two tables solves the transitive dependency problem. The two tables make up a database that’s in third normal form.

View Article
SQL How to Use SQL Column References

Article / Updated 01-14-2022

Every column contains one value for each row of a table. SQL statements often refer to such values. A fully qualified column reference consists of the table name, a period, and then the column name (for example, PRICING.Product). Consider the following statement: SELECT PRICING.Cost FROM PRICING WHERE PRICING.Product = 'F-35' ; Here PRICING.Product is a column reference. This reference contains the value ‘F-35’. PRICING.Cost is also a column reference, but you don’t know its value until the preceding SELECT statement executes. Because it only makes sense to reference columns in the current table, you don’t generally need to use fully qualified column references. The following statement, for example, is equivalent to the previous one: SELECT Cost FROM PRICING WHERE Product = 'F-35' ; Sometimes you may be dealing with more than one table — say, when two tables in a database contain one or more columns with the same name. In such a case, you must fully qualify column references for those columns to guarantee that you get the column you want. For example, suppose that your company maintains facilities in both Kingston and Jefferson, and you maintain separate employee records for each site. You name the Kingston employee table EMP_KINGSTON, and you name the Jefferson employee table EMP_JEFFERSON. You want a list of employees who work at both sites, so you need to find the employees whose names appear in both tables. The following SELECT statement gives you what you want: SELECT EMP_KINGSTON.FirstName, EMP_KINGSTON.LastName FROM EMP_KINGSTON, EMP_JEFFERSON WHERE EMP_KINGSTON.EmpID = EMP_JEFFERSON.EmpID ; Because each employee’s ID number is unique and remains the same regardless of the work site, you can use this ID as a link between the two tables. This retrieval returns only the names of employees who appear in both tables.

View Article
SQL How to Use MATCH in SQL Statements

Article / Updated 01-14-2022

Referential integrity involves maintaining consistency in a multitable SQL database. You can lose integrity by adding a row to a child table that doesn’t have a corresponding row in the child’s parent table. You can cause similar problems by deleting a row from a parent table if rows corresponding to that row exist in a child table. Suppose your business has a CUSTOMER table that keeps track of all your customers and a SALES table that records all sales transactions. You don’t want to add a row to SALES until after you enter the customer making the purchase into the CUSTOMER table. You also don’t want to delete a customer from the CUSTOMER table if that customer made purchases that exist in the SALES table. Before you perform an insertion or a deletion, you may want to check the candidate row to make sure that inserting or deleting that row doesn’t cause integrity problems. The MATCH predicate can perform such a check. Say you have a CUSTOMER table and a SALES table. CustomerID is the primary key of the CUSTOMER table and acts as a foreign key in the SALES table. Every row in the CUSTOMER table must have a unique CustomerID that isn’t null. CustomerID isn’t unique in the SALES table, because repeat customers buy more than once. This situation is fine; it doesn’t threaten integrity because CustomerID is a foreign key rather than a primary key in that table. Seemingly, CustomerID can be null in the SALES table, because someone can walk in off the street, buy something, and walk out before you get a chance to enter his or her name and address into the CUSTOMER table. This situation can create trouble — a row in the child table with no corresponding row in the parent table. To overcome this problem, you can create a generic customer in the CUSTOMER table and assign all anonymous sales to that customer. Say that a customer steps up to the cash register and claims that she bought an F-35 Strike Fighter on December 18, 2012. Although she has lost her receipt, she now wants to return the plane because it shows up like an aircraft carrier on opponents’ radar screens. You can verify whether she bought an F-35 by searching your SALES database for a match. First, you must retrieve her CustomerID into the variable vcustid; then you can use the following syntax: ... WHERE (:vcustid, 'F-35', '2017-12-18') MATCH (SELECT CustomerID, ProductID, SaleDate FROM SALES) If the MATCH predicate returns a True value, the database contains a sale of the F-35 on December 18, 2017, to this client’s CustomerID. Take back the defective product and refund the customer’s money. (Note: If any values in the first argument of the MATCH predicate are null, a True value always returns.) SQL’s developers added the MATCH predicate and the UNIQUE predicate for the same reason — they provide a way to explicitly perform the tests defined for the implicit referential integrity (RI) and UNIQUE constraints. The general form of the MATCH predicate is as follows: Row_valueMATCH [UNIQUE] [SIMPLE| PARTIAL | FULL ] Subquery The UNIQUE, SIMPLE, PARTIAL, and FULL options relate to rules that come into play if the row value expression R has one or more columns that are null. The rules for the MATCH predicate are a copy of corresponding referential integrity rules.

View Article
SQL How to Use EXISTS, UNIQUE, DISTINCT, and OVERLAPS in SQL Statements

Article / Updated 01-14-2022

Within the WHERE clause lies many possibilities for modifying your SQL statement. Among these possibilities are the EXISTS, UNIQUE, DISTINCT, and OVERLAPS predicates. Here are some examples of how to use these in your SQL statements. EXISTS You can use the EXISTS predicate in conjunction with a subquery to determine whether the subquery returns any rows. If the subquery returns at least one row, that result satisfies the EXISTS condition, and the outer query executes. Consider the following example: SELECT FirstName, LastName FROM CUSTOMER WHERE EXISTS (SELECT DISTINCT CustomerID FROM SALES WHERE SALES.CustomerID = CUSTOMER.CustomerID); Here the SALES table contains all of your company’s sales transactions. The table includes the CustomerID of the customer who makes each purchase, as well as other pertinent information. The CUSTOMER table contains each customer’s first and last names, but no information about specific transactions. The subquery in the preceding example returns a row for every customer who has made at least one purchase. The outer query returns the first and last names of the customers who made the purchases that the SALES table records. EXISTS is equivalent to a comparison of COUNT with zero, as the following query shows: SELECT FirstName, LastName FROM CUSTOMER WHERE 0 <> (SELECT COUNT(*) FROM SALES WHERE SALES.CustomerID = CUSTOMER.CustomerID); For every row in the SALES table that contains a CustomerID that’s equal to a CustomerID in the CUSTOMER table, this statement displays the FirstName and LastName columns in the CUSTOMER table. For every sale in the SALES table, therefore, the statement displays the name of the customer who made the purchase. UNIQUE As you do with the EXISTS predicate, you use the UNIQUE predicate with a subquery. Although the EXISTS predicate evaluates to True only if the subquery returns at least one row, the UNIQUE predicate evaluates to True only if no two rows returned by the subquery are identical. In other words, the UNIQUE predicate evaluates to True only if all the rows that its subquery returns are unique. Consider the following example: SELECT FirstName, LastName FROM CUSTOMER WHERE UNIQUE (SELECT CustomerID FROM SALES WHERE SALES.CustomerID = CUSTOMER.CustomerID); This statement retrieves the names of all new customers for whom the SALES table records only one sale. Because a null value is an unknown value, two null values aren’t considered equal to each other; when the UNIQUE keyword is applied to a result table that contains only two null rows, the UNIQUE predicate evaluates to True. DISTINCT The DISTINCT predicate is similar to the UNIQUE predicate, except in the way it treats nulls. If all the values in a result table are UNIQUE, then they’re also DISTINCT from each other. However, unlike the result for the UNIQUE predicate, if the DISTINCT keyword is applied to a result table that contains only two null rows, the DISTINCT predicate evaluates to False. Two null values are not considered distinct from each other, while at the same time they are considered to be unique. This strange situation seems contradictory, but there’s a reason for it. In some situations, you may want to treat two null values as different from each other — in which case, use the UNIQUE predicate. When you want to treat the two nulls as if they’re the same, use the DISTINCT predicate. OVERLAPS You use the OVERLAPS predicate to determine whether two time intervals overlap each other. This predicate is useful for avoiding scheduling conflicts. If the two intervals overlap, the predicate returns a True value. If they don’t overlap, the predicate returns a False value. You can specify an interval in two ways: either as a start time and an end time or as a start time and a duration. Here are some examples: (TIME '2:55:00', INTERVAL '1' HOUR) OVERLAPS (TIME '3:30:00', INTERVAL '2' HOUR) This first example returns a True because 3:30 is less than one hour after 2:55. (TIME '9:00:00', TIME '9:30:00') OVERLAPS (TIME '9:29:00', TIME '9:31:00') This example returns a True because you have a one-minute overlap between the two intervals. (TIME '9:00:00', TIME '10:00:00') OVERLAPS (TIME '10:15:00', INTERVAL '3' HOUR) This example returns a False because the two intervals don’t overlap. (TIME '9:00:00', TIME '9:30:00') OVERLAPS (TIME '9:30:00', TIME '9:35:00') This example returns a False because even though the two intervals are contiguous, they don’t overlap.

View Article
SQL How to Design an SQL Database

Article / Updated 01-14-2022

The first step to designing any database in SQL is to identify what to include and what not to include. The next steps involve deciding how the included items relate to each other and then setting up tables accordingly. To design a database in SQL, follow these basic steps: Decide what objects you want to include in your database. Determine which of these objects should be tables and which should be columns within those tables. Define tables based on how you need to organize the objects. Optionally, you may want to designate a table column or a combination of columns as a key. Step 1: Define objects The first step in designing a database is deciding which aspects of the system are important enough to include in the model. Treat each aspect as an object and create a list of all the objects you can think of. At this stage, don’t try to decide how these objects relate to each other. Just try to list them all. When you have a reasonably complete set of objects, move on to the next step: deciding how these objects relate to each other. Some of the objects are major entities that are crucial to giving you the results you want. Other objects are subsidiary to those major entities. Ultimately you may decide that some objects don’t belong in the model at all. Step 2: Identify tables and columns Major entities translate into database tables. Each major entity has a set of attributes — the table columns. Many business databases, for example, have a CUSTOMER table that keeps track of customers’ names, addresses, and other permanent information. Each attribute of a customer — such as name, street, city, state, zip code, phone number, and e-mail address — becomes a column (and a column heading) in the CUSTOMER table. If you’re hoping to find a set of rules to help you identify which objects should be tables and which of the attributes in the system belong to which tables, think again: You may have some reasons for assigning a particular attribute to one table and other reasons for assigning the same attribute to another table. You must base your judgment on two goals: The information you want to get from the database How you want to use that information When deciding how to structure database tables, involve future users of the database as well as the people who will make decisions based on database information. If you come up with what you think is a reasonable structure, but it isn’t consistent with the way that people will use the information, your system will be frustrating to use at best — and could even produce wrong information, which is even worse. Take a look at an example. Suppose you just established VetLab, a clinical microbiology laboratory that tests biological specimens sent in by veterinarians. You want to track several things, including the following: Clients Tests that you perform Employees Orders Results Each of these entities has associated attributes. Each client has a name, an address, and other contact information. Each test has a name and a standard charge. Each employee has contact information as well as a job classification and pay rate. For each order, you need to know who ordered it, when it was ordered, and what test was ordered. For each test result, you need to know the outcome of the test, whether the results were preliminary or final, and the test order number. Step 3: Define tables Now you want to define a table for each entity and a column for each attribute. Table Columns CLIENT Client Name Address 1 Address 2 City State Postal Code Phone Fax Contact Person TESTS Test Name Standard Charge EMPLOYEE Employee Name Address 1 Address 2 City State Postal Code Home Phone Office Extension Hire Date Job Classification Hourly/Salary/Commission ORDERS Order Number Client Name Test Ordered Responsible Salesperson Order Date RESULTS Result Number Order Number Result Date Reported Preliminary/Final You can create the tables defined here by using either a rapid application development (RAD) tool or by using SQL’s Data Definition Language (DDL), as shown in the following code: CREATE TABLE CLIENT ( ClientName CHAR (30) NOT NULL, Address1 CHAR (30), Address2 CHAR (30), City CHAR (25), State CHAR (2), PostalCode CHAR (10), Phone CHAR (13), Fax CHAR (13), ContactPerson CHAR (30) ) ; CREATE TABLE TESTS ( TestName CHAR (30) NOT NULL, StandardCharge CHAR (30) ) ; CREATE TABLE EMPLOYEE ( EmployeeName CHAR (30) NOT NULL, Address1 CHAR (30), Address2 CHAR (30), City CHAR (25), State CHAR (2), PostalCode CHAR (10), HomePhone CHAR (13), OfficeExtension CHAR (4), HireDate DATE, JobClassification CHAR (10), HourSalComm CHAR (1) ) ; CREATE TABLE ORDERS ( OrderNumber INTEGER NOT NULL, ClientName CHAR (30), TestOrdered CHAR (30), Salesperson CHAR (30), OrderDate DATE ) ; CREATE TABLE RESULTS ( ResultNumber INTEGER NOT NULL, OrderNumber INTEGER, Result CHAR(50), DateReported DATE, PrelimFinal CHAR (1) ) ; These tables relate to each other by the attributes (columns) that they share, as the following list describes: The CLIENT table links to the ORDERS table by the ClientName column. The TESTS table links to the ORDERS table by the TestName (TestOrdered) column. The EMPLOYEE table links to the ORDERS table by the EmployeeName (Salesperson) column. The RESULTS table links to the ORDERS table by the OrderNumber column. If you want a table to serve as an integral part of a relational database, link that table to at least one other table in the database, using a common column. The links illustrate four different one-to-many relationships. The diamond in the middle of each relationship shows the maximum cardinality of each end of the relationship. The number 1 denotes the “one” side of the relationship, and N denotes the “many” side. One client can make many orders, but each order is made by one, and only one, client. Each test can appear on many orders, but each order calls for one, and only one, test. Each order is taken by one, and only one, employee (or salesperson), but each salesperson can take multiple orders. Each order can produce several preliminary test results and a final result, but each result is associated with one, and only one, order. The attribute that links one table to another can have a different name in each table. Both attributes must, however, have matching data types.

View Article
SQL How to Create an SQL Table with Microsoft Access

Article / Updated 01-14-2022

Whether you’re working with Access or a full-featured enterprise-level DBMS — like Microsoft SQL Server, Oracle, or IBM DB2 — to create a table with SQL, you must enter the same information that you’d enter if you created the table with a RAD tool. The difference is that the RAD tool helps you by providing a visual interface — in the form of a table-creation dialog box (or some similar data-entry skeleton) — and by preventing you from entering invalid field names, types, or sizes. SQL doesn’t give you as much help. You must know what you’re doing at the onset; figuring things out along the way can lead to less-than-desirable database results. You must enter the entire CREATE TABLE statement before SQL even looks at it, let alone gives you any indication of whether you made errors in the statement. In ISO/IEC standard SQL, the statement that creates a proposal-tracking table uses the following syntax: CREATE TABLE POWERSQL ( ProposalNumber INTEGER PRIMARY KEY, FirstName CHAR (15), LastName CHAR (20), Address CHAR (30), City CHAR (25), StateProvince CHAR (2), PostalCode CHAR (10), Country CHAR (30), Phone CHAR (14), HowKnown CHAR (30), Proposal CHAR (50), BusinessOrCharity CHAR (1) ); The information in the SQL statement is essentially the same information you enter using Access’s graphical user interface. The nice thing about SQL is that the language is universal. The same standard syntax works regardless of what standard-compliant DBMS product you use. In Access 2016, creating database objects such as tables is a little more complicated. You can’t just type a CREATE statement into the SQL View Object tab. That’s because the SQL View Object tab is available only as a query tool; you have to take a few extra actions to inform Access that you’re about to enter a data-definition query rather than a query that requests information from the database. A further complication: Because table creation is an action that could possibly compromise database security, it’s disallowed by default. You must tell Access that this is a trusted database before it will accept a data-definition query. Click the Create tab on the Ribbon to display the icons for creation functionality. Click Query Design in the Queries section. This displays the Show Table dialog box, which at this point contains several system tables along with POWER. Select POWER and click the Add button. As you’ve seen in the previous example, a picture of the POWER table and its attributes appears in the upper half of the work area. Click the Close button on the Show Table dialog box. Click the Home tab and then pull down the View menu at the left end of the Ribbon and then choose SQL View from the menu. As in the previous example, Access has “helped” you by putting SELECT FROM POWER in the SQL editor. This time you don’t want the help. Delete SELECT FROM POWER and (in its place) enter the data-definition query given earlier, as follows: CREATE TABLE POWERSQL ( ProposalNumber INTEGER PRIMARY KEY, FirstName CHAR (15), LastName CHAR (20), Address CHAR (30), City CHAR (25), StateProvince CHAR (2), PostalCode CHAR (10), Country CHAR (30), Phone CHAR (14), HowKnown CHAR (30), Proposal CHAR (50), BusinOrCharity CHAR (1) ); After clicking the Design tab of the Ribbon, click the red exclamation point Run icon. Doing so runs the query, which creates the POWERSQL table. You should see POWERSQL listed under All Access Objects in the column at the left edge of the window. In which case, you’re golden. Or you may not see the table in the All Access Objects list. In that case, read (and slog) on. Access 2016 goes to great lengths to protect you from malicious hackers and from your own inadvertent mistakes. Because running a data-definition query is potentially dangerous to the database, Access has a default that prevents the query from running. If this has happened to you, POWERSQL won’t appear in the column at the left of the window, because the query won’t have been executed. Instead, the Message Bar may appear below the Ribbon, with this terse message: Security Warning: Certain content in the database has been disabled. If you see this message, move on to the next steps. Click the File tab and, from the menu of the left edge, choose Options. The Access Options dialog box appears. Select Trust Center from the Access Options dialog box. Click the Trust Center Settings button when it appears. Select Message Bar from the menu on the left and then specify Show the Message Bar by clicking its option button if it isn’t already selected. Click your way back to the place where you can execute the data-definition query that creates the POWERSQL table. Execute the query. Becoming proficient in SQL has long-term payoffs because it will be around for a long time. The effort you put into becoming an expert in a particular development tool is likely to yield a lower return on investment. No matter how wonderful the latest RAD tool may be, it will be superseded by newer technology within three to five years. If you can recover your investment in the tool in that time, great! Use it. If not, you may be wise to stick with the tried and true. Train your people in SQL, and your training investment will pay dividends over a much longer period.

View Article
page 1
page 2
page 3

Quick Links

  • About For Dummies
  • Contact Us
  • Activate A Book Pin

Connect

Opt in to our newsletter!

By entering your email address and clicking the “Submit” button, you agree to the Terms of Use and Privacy Policy & to receive electronic communications from Dummies.com, which may include marketing promotions, news and updates.

About Dummies

Dummies has always stood for taking on complex concepts and making them easy to understand. Dummies helps everyone be more knowledgeable and confident in applying what they know. Whether it's to pass that big test, qualify for that big promotion or even master that cooking technique; people who rely on dummies, rely on it to learn the critical skills and relevant information necessary for success.

Terms of Use
Privacy Policy
Cookies Settings
Do Not Sell My Personal Info - CA Only