{"appState":{"pageLoadApiCallsStatus":true},"categoryState":{"relatedCategories":{"headers":{"timestamp":"2025-04-17T16:01:07+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,"articleCount":26,"bookCount":4},"_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":"2024-04-12T17:36:17+00:00","timestamp":"2024-04-12T18:01:11+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 WHERE clauses.","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 WHERE clauses.","blurb":"","authors":[{"authorId":9559,"name":"Allen G. Taylor","slug":"allen-g-taylor","description":" <p> <b>This All-in-One draws on the work of top authors in the <i>For Dummies </i>series who’ve created books designed to help data professionals do their work. The experts are Jack Hyman, Luca Massaron, Paul McFedries, John Paul Mueller, Lillian Pierson, Jonathan Reichental PhD, Joseph Schmuller PhD, Alan Simon, and Allen G. Taylor.</b> ","hasArticle":false,"_links":{"self":"https://dummies-api.dummies.com/v2/authors/9559"}},{"authorId":10600,"name":"Richard Blum","slug":"richard-blum","description":" <p><b>Richard Blum</b> has more than 30 years of experience as a systems administrator and programmer. He teaches online courses in PHP, JavaScript, HTML5, and CSS3 programming, and authored the latest edition of <i>Linux For Dummies</i>. ","hasArticle":false,"_links":{"self":"https://dummies-api.dummies.com/v2/authors/10600"}}],"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":[{"articleId":192609,"title":"How to Pray the Rosary: A Comprehensive Guide","slug":"how-to-pray-the-rosary","categoryList":["body-mind-spirit","religion-spirituality","christianity","catholicism"],"_links":{"self":"/articles/192609"}},{"articleId":208741,"title":"Kabbalah For Dummies Cheat Sheet","slug":"kabbalah-for-dummies-cheat-sheet","categoryList":["body-mind-spirit","religion-spirituality","kabbalah"],"_links":{"self":"/articles/208741"}},{"articleId":230957,"title":"Nikon D3400 For Dummies Cheat Sheet","slug":"nikon-d3400-dummies-cheat-sheet","categoryList":["home-auto-hobbies","photography"],"_links":{"self":"/articles/230957"}},{"articleId":235851,"title":"Praying the Rosary and Meditating on the Mysteries","slug":"praying-rosary-meditating-mysteries","categoryList":["body-mind-spirit","religion-spirituality","christianity","catholicism"],"_links":{"self":"/articles/235851"}},{"articleId":284787,"title":"What Your Society Says About You","slug":"what-your-society-says-about-you","categoryList":["academics-the-arts","humanities"],"_links":{"self":"/articles/284787"}}],"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=\"9559\">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":9559,"name":"Allen G. Taylor","slug":"allen-g-taylor","description":" <p> <b>This All-in-One draws on the work of top authors in the <i>For Dummies </i>series who’ve created books designed to help data professionals do their work. The experts are Jack Hyman, Luca Massaron, Paul McFedries, John Paul Mueller, Lillian Pierson, Jonathan Reichental PhD, Joseph Schmuller PhD, Alan Simon, and Allen G. Taylor.</b> ","hasArticle":false,"_links":{"self":"https://dummies-api.dummies.com/v2/authors/9559"}}],"_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-661976e795fc7\"></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-661976e796599\"></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’s no different with SQL. The following list shows you what to consider at each phase of the SQL development life cycle:</p>\n<ul>\n<li><strong>Definition phase:</strong> Precisely define the problem to be solved, its magnitude, and who will work on it.</li>\n<li><strong>Requirements phase: </strong>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.</li>\n<li><strong>Evaluation phase:</strong> 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.</li>\n<li><strong>Design phase:</strong> Create a database model and then design a database and database application that satisfy the terms of the requirements document.</li>\n<li><strong>Implementation phase:</strong> Build the database and the database application. Include copious documentation within the code and in external documents.</li>\n<li><strong>Final documentation and testing phase:</strong> 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.</li>\n<li><strong>Maintenance phase:</strong> Fix latent bugs as they arise. Provide updates and enhancements called for by the client.</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<li>JSON</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<h3>String value functions</h3>\n<table>\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>BTRIM</td>\n<td>Trims multiple characters both before and after the text</td>\n</tr>\n<tr>\n<td>LTRIM</td>\n<td>Trims multiple characters to the left of the text</td>\n</tr>\n<tr>\n<td>RTRIM</td>\n<td>Trims multiple characters to the right of the text</td>\n</tr>\n<tr>\n<td>TRIM</td>\n<td>Trims off leading or trailing blanks</td>\n</tr>\n<tr>\n<td>LPAD</td>\n<td>Adds padding characters to the left of the text</td>\n</tr>\n<tr>\n<td>RPAD</td>\n<td>Adds padding characters to the right of the text</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<h3>Numeric value functions</h3>\n<table>\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<h3>Datetime value functions</h3>\n<table>\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>\n<tbody>\n<tr>\n<th>Function</th>\n<th>Effect</th>\n</tr>\n<tr>\n<td><code>COUNT</code></td>\n<td>Returns the number of rows in the specified table</td>\n</tr>\n<tr>\n<td><code>MAX</code></td>\n<td>Returns the maximum value that occurs in the specified able</td>\n</tr>\n<tr>\n<td><code>MIN</code></td>\n<td>Returns the minimum value that occurs in the specified table</td>\n</tr>\n<tr>\n<td><code>SUM</code></td>\n<td>Adds up the values in a specified column</td>\n</tr>\n<tr>\n<td><code>AVG</code></td>\n<td>Returns the average of all the values in the specified column</td>\n</tr>\n<tr>\n<td><code>ANY_VALUE</code></td>\n<td>Returns a random value from a specified set of data</td>\n</tr>\n<tr>\n<td><code>GREATEST</code></td>\n<td>Returns the largest value from a specified set of data</td>\n</tr>\n<tr>\n<td><code>LEAST</code></td>\n<td>Returns the smallest value from a specified set of data</td>\n</tr>\n<tr>\n<td><code>LISTAGG</code></td>\n<td>Transforms values from a group of rows into a delimited string</td>\n</tr>\n</tbody>\n</table>\n<h3>Trigonometric and logarithmic functions</h3>\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<h3>JSON constructor functions</h3>\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<h3>JSON query functions</h3>\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<h3>Comparison predicates</h3>\n<table>\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<h3>Other predicates</h3>\n<table>\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":"","sponsorEbookTitle":"","sponsorEbookLink":"","sponsorEbookImage":{"src":null,"width":0,"height":0}},"primaryLearningPath":"Advance","lifeExpectancy":"Two years","lifeExpectancySetFrom":"2024-04-12T00:00:00+00:00","dummiesForKids":"no","sponsoredContent":"no","adInfo":"","adPairKey":[]},"status":"publish","visibility":"public","articleId":208690},{"headers":{"creationTime":"2016-03-26T20:43:38+00:00","modifiedTime":"2022-09-30T20:09:43+00:00","timestamp":"2022-09-30T21:01:04+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 Data Types","strippedTitle":"sql data types","slug":"sql-data-types","canonicalUrl":"","seo":{"metaDescription":"Depending on their histories, different SQL implementations support a variety of data types. Here, are lists of nine general types.","noIndex":0,"noFollow":0},"content":"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.\r\n\r\n<b>Exact Numerics:</b>\r\n<ul class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">INTEGER</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">SMALLINT</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">BIGINT</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">NUMERIC</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">DECIMAL</p>\r\n</li>\r\n \t<li>DECFLOAT</li>\r\n</ul>\r\n<b>Approximate Numerics:</b>\r\n<ul class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">REAL</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">DOUBLE PRECISION</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">FLOAT</p>\r\n</li>\r\n</ul>\r\n<b>Boolean:</b>\r\n<ul class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">BOOLEAN</p>\r\n</li>\r\n</ul>\r\n<b>Character Strings:</b>\r\n<ul class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">CHARACTER (CHAR)</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">CHARACTER VARYING (VARCHAR)</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">NATIONAL CHARACTER (NCHAR)</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">NATIONAL CHARACTER VARYING (NVARCHAR)</p>\r\n</li>\r\n</ul>\r\n<b>Datetimes:</b>\r\n<ul class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">DATE</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">TIME</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">TIMESTAMP</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">TIME WITH TIMEZONE</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">TIMESTAMP WITH TIMEZONE</p>\r\n</li>\r\n</ul>\r\n<b>Intervals:</b>\r\n<ul class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">INTERVAL DAY</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">INTERVAL YEAR</p>\r\n</li>\r\n</ul>\r\n<b>Large Objects:</b>\r\n<ul class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">BLOB</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">CLOB</p>\r\n</li>\r\n</ul>\r\n<b>Collection Types:</b>\r\n<ul class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">ARRAY</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">MULTISET</p>\r\n</li>\r\n</ul>\r\n<b>Other Types:</b>\r\n<ul class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">ROW</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">XML</p>\r\n</li>\r\n</ul>","description":"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.\r\n\r\n<b>Exact Numerics:</b>\r\n<ul class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">INTEGER</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">SMALLINT</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">BIGINT</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">NUMERIC</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">DECIMAL</p>\r\n</li>\r\n \t<li>DECFLOAT</li>\r\n</ul>\r\n<b>Approximate Numerics:</b>\r\n<ul class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">REAL</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">DOUBLE PRECISION</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">FLOAT</p>\r\n</li>\r\n</ul>\r\n<b>Boolean:</b>\r\n<ul class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">BOOLEAN</p>\r\n</li>\r\n</ul>\r\n<b>Character Strings:</b>\r\n<ul class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">CHARACTER (CHAR)</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">CHARACTER VARYING (VARCHAR)</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">NATIONAL CHARACTER (NCHAR)</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">NATIONAL CHARACTER VARYING (NVARCHAR)</p>\r\n</li>\r\n</ul>\r\n<b>Datetimes:</b>\r\n<ul class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">DATE</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">TIME</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">TIMESTAMP</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">TIME WITH TIMEZONE</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">TIMESTAMP WITH TIMEZONE</p>\r\n</li>\r\n</ul>\r\n<b>Intervals:</b>\r\n<ul class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">INTERVAL DAY</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">INTERVAL YEAR</p>\r\n</li>\r\n</ul>\r\n<b>Large Objects:</b>\r\n<ul class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">BLOB</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">CLOB</p>\r\n</li>\r\n</ul>\r\n<b>Collection Types:</b>\r\n<ul class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">ARRAY</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">MULTISET</p>\r\n</li>\r\n</ul>\r\n<b>Other Types:</b>\r\n<ul class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">ROW</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">XML</p>\r\n</li>\r\n</ul>","blurb":"","authors":[{"authorId":9559,"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. ","hasArticle":false,"_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":[{"articleId":192609,"title":"How to Pray the Rosary: A Comprehensive Guide","slug":"how-to-pray-the-rosary","categoryList":["body-mind-spirit","religion-spirituality","christianity","catholicism"],"_links":{"self":"/articles/192609"}},{"articleId":208741,"title":"Kabbalah For Dummies Cheat Sheet","slug":"kabbalah-for-dummies-cheat-sheet","categoryList":["body-mind-spirit","religion-spirituality","kabbalah"],"_links":{"self":"/articles/208741"}},{"articleId":230957,"title":"Nikon D3400 For Dummies Cheat Sheet","slug":"nikon-d3400-dummies-cheat-sheet","categoryList":["home-auto-hobbies","photography"],"_links":{"self":"/articles/230957"}},{"articleId":235851,"title":"Praying the Rosary and Meditating on the Mysteries","slug":"praying-rosary-meditating-mysteries","categoryList":["body-mind-spirit","religion-spirituality","christianity","catholicism"],"_links":{"self":"/articles/235851"}},{"articleId":284787,"title":"What Your Society Says About You","slug":"what-your-society-says-about-you","categoryList":["academics-the-arts","humanities"],"_links":{"self":"/articles/284787"}}],"inThisArticle":[],"relatedArticles":{"fromBook":[{"articleId":208690,"title":"SQL All-in-One For Dummies Cheat Sheet","slug":"sql-all-in-one-for-dummies-cheat-sheet","categoryList":["technology","programming-web-design","sql"],"_links":{"self":"https://dummies-api.dummies.com/v2/articles/208690"}},{"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=\"9559\">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":9559,"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. ","hasArticle":false,"_links":{"self":"https://dummies-api.dummies.com/v2/authors/9559"}}],"_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-633759100f0ba\"></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-633759100f98e\"></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":"","sponsorEbookTitle":"","sponsorEbookLink":"","sponsorEbookImage":{"src":null,"width":0,"height":0}},"primaryLearningPath":"Advance","lifeExpectancy":"Two years","lifeExpectancySetFrom":"2022-09-30T00:00:00+00:00","dummiesForKids":"no","sponsoredContent":"no","adInfo":"","adPairKey":[]},"status":"publish","visibility":"public","articleId":188699},{"headers":{"creationTime":"2016-03-26T21:45:57+00:00","modifiedTime":"2022-09-30T20:00:53+00:00","timestamp":"2022-09-30T21:01:03+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 Criteria for Normal Forms","strippedTitle":"sql criteria for normal forms","slug":"sql-criteria-for-normal-forms-2","canonicalUrl":"","seo":{"metaDescription":"In SQL, normal forms are defining characteristics of relational databases. Learn about the first, second, and third normal forms.","noIndex":0,"noFollow":0},"content":"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.\r\n\r\nFirst Normal Form (1NF):\r\n<ul class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">Table must be 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 one thing or one 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 being described.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Each cell (intersection of row and column) of the table must be single-valued.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">All entries in a column must be of the same kind.</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.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">The order of the columns and of the rows does not matter.</p>\r\n</li>\r\n</ul>\r\nSecond Normal Form (2NF):\r\n<ul class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">Table must be in first normal form (1NF).</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">All non-key attributes (columns) must be dependent on the entire key.</p>\r\n</li>\r\n</ul>\r\nThird Normal Form (3NF):\r\n<ul class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">Table must be in second normal form (2NF).</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Table has no transitive dependencies.</p>\r\n</li>\r\n</ul>\r\nDomain-Key Normal Form (DK/NF):\r\n<ul class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">Every constraint on the table is a logical consequence of the definition of keys and domains.</p>\r\n</li>\r\n</ul>","description":"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.\r\n\r\nFirst Normal Form (1NF):\r\n<ul class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">Table must be 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 one thing or one 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 being described.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Each cell (intersection of row and column) of the table must be single-valued.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">All entries in a column must be of the same kind.</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.</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">The order of the columns and of the rows does not matter.</p>\r\n</li>\r\n</ul>\r\nSecond Normal Form (2NF):\r\n<ul class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">Table must be in first normal form (1NF).</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">All non-key attributes (columns) must be dependent on the entire key.</p>\r\n</li>\r\n</ul>\r\nThird Normal Form (3NF):\r\n<ul class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">Table must be in second normal form (2NF).</p>\r\n</li>\r\n \t<li>\r\n<p class=\"first-para\">Table has no transitive dependencies.</p>\r\n</li>\r\n</ul>\r\nDomain-Key Normal Form (DK/NF):\r\n<ul class=\"level-one\">\r\n \t<li>\r\n<p class=\"first-para\">Every constraint on the table is a logical consequence of the definition of keys and domains.</p>\r\n</li>\r\n</ul>","blurb":"","authors":[{"authorId":9559,"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. ","hasArticle":false,"_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":[{"articleId":192609,"title":"How to Pray the Rosary: A Comprehensive Guide","slug":"how-to-pray-the-rosary","categoryList":["body-mind-spirit","religion-spirituality","christianity","catholicism"],"_links":{"self":"/articles/192609"}},{"articleId":208741,"title":"Kabbalah For Dummies Cheat Sheet","slug":"kabbalah-for-dummies-cheat-sheet","categoryList":["body-mind-spirit","religion-spirituality","kabbalah"],"_links":{"self":"/articles/208741"}},{"articleId":230957,"title":"Nikon D3400 For Dummies Cheat Sheet","slug":"nikon-d3400-dummies-cheat-sheet","categoryList":["home-auto-hobbies","photography"],"_links":{"self":"/articles/230957"}},{"articleId":235851,"title":"Praying the Rosary and Meditating on the Mysteries","slug":"praying-rosary-meditating-mysteries","categoryList":["body-mind-spirit","religion-spirituality","christianity","catholicism"],"_links":{"self":"/articles/235851"}},{"articleId":284787,"title":"What Your Society Says About You","slug":"what-your-society-says-about-you","categoryList":["academics-the-arts","humanities"],"_links":{"self":"/articles/284787"}}],"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=\"9559\">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":9559,"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. ","hasArticle":false,"_links":{"self":"https://dummies-api.dummies.com/v2/authors/9559"}}],"_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-6337590f711d8\"></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-6337590f71ae2\"></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":"","sponsorEbookTitle":"","sponsorEbookLink":"","sponsorEbookImage":{"src":null,"width":0,"height":0}},"primaryLearningPath":"Advance","lifeExpectancy":"Two years","lifeExpectancySetFrom":"2022-09-30T00:00:00+00:00","dummiesForKids":"no","sponsoredContent":"no","adInfo":"","adPairKey":[]},"status":"publish","visibility":"public","articleId":194143},{"headers":{"creationTime":"2016-03-27T16:57:23+00:00","modifiedTime":"2022-01-27T15:37:12+00:00","timestamp":"2022-09-14T18:19:04+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":" <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. ","hasArticle":false,"_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":[{"articleId":192609,"title":"How to Pray the Rosary: A Comprehensive Guide","slug":"how-to-pray-the-rosary","categoryList":["body-mind-spirit","religion-spirituality","christianity","catholicism"],"_links":{"self":"/articles/192609"}},{"articleId":208741,"title":"Kabbalah For Dummies Cheat Sheet","slug":"kabbalah-for-dummies-cheat-sheet","categoryList":["body-mind-spirit","religion-spirituality","kabbalah"],"_links":{"self":"/articles/208741"}},{"articleId":230957,"title":"Nikon D3400 For Dummies Cheat Sheet","slug":"nikon-d3400-dummies-cheat-sheet","categoryList":["home-auto-hobbies","photography"],"_links":{"self":"/articles/230957"}},{"articleId":235851,"title":"Praying the Rosary and Meditating on the Mysteries","slug":"praying-rosary-meditating-mysteries","categoryList":["body-mind-spirit","religion-spirituality","christianity","catholicism"],"_links":{"self":"/articles/235851"}},{"articleId":284787,"title":"What Your Society Says About You","slug":"what-your-society-says-about-you","categoryList":["academics-the-arts","humanities"],"_links":{"self":"/articles/284787"}}],"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=\"9559\">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":9559,"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. ","hasArticle":false,"_links":{"self":"https://dummies-api.dummies.com/v2/authors/9559"}}],"_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-63221b180fdac\"></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-63221b1810981\"></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":"","sponsorEbookTitle":"","sponsorEbookLink":"","sponsorEbookImage":{"src":null,"width":0,"height":0}},"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-09-14T18:19:01+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 &lt;&gt;'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 &lt;&gt;'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":" <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. ","hasArticle":false,"_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":[{"articleId":192609,"title":"How to Pray the Rosary: A Comprehensive Guide","slug":"how-to-pray-the-rosary","categoryList":["body-mind-spirit","religion-spirituality","christianity","catholicism"],"_links":{"self":"/articles/192609"}},{"articleId":208741,"title":"Kabbalah For Dummies Cheat Sheet","slug":"kabbalah-for-dummies-cheat-sheet","categoryList":["body-mind-spirit","religion-spirituality","kabbalah"],"_links":{"self":"/articles/208741"}},{"articleId":230957,"title":"Nikon D3400 For Dummies Cheat Sheet","slug":"nikon-d3400-dummies-cheat-sheet","categoryList":["home-auto-hobbies","photography"],"_links":{"self":"/articles/230957"}},{"articleId":235851,"title":"Praying the Rosary and Meditating on the Mysteries","slug":"praying-rosary-meditating-mysteries","categoryList":["body-mind-spirit","religion-spirituality","christianity","catholicism"],"_links":{"self":"/articles/235851"}},{"articleId":284787,"title":"What Your Society Says About You","slug":"what-your-society-says-about-you","categoryList":["academics-the-arts","humanities"],"_links":{"self":"/articles/284787"}}],"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=\"9559\">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":9559,"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. ","hasArticle":false,"_links":{"self":"https://dummies-api.dummies.com/v2/authors/9559"}}],"_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-63221b16004ac\"></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-63221b1601031\"></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":"","sponsorEbookTitle":"","sponsorEbookLink":"","sponsorEbookImage":{"src":null,"width":0,"height":0}},"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-09-14T18:19:01+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":" <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. ","hasArticle":false,"_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":[{"articleId":192609,"title":"How to Pray the Rosary: A Comprehensive Guide","slug":"how-to-pray-the-rosary","categoryList":["body-mind-spirit","religion-spirituality","christianity","catholicism"],"_links":{"self":"/articles/192609"}},{"articleId":208741,"title":"Kabbalah For Dummies Cheat Sheet","slug":"kabbalah-for-dummies-cheat-sheet","categoryList":["body-mind-spirit","religion-spirituality","kabbalah"],"_links":{"self":"/articles/208741"}},{"articleId":230957,"title":"Nikon D3400 For Dummies Cheat Sheet","slug":"nikon-d3400-dummies-cheat-sheet","categoryList":["home-auto-hobbies","photography"],"_links":{"self":"/articles/230957"}},{"articleId":235851,"title":"Praying the Rosary and Meditating on the Mysteries","slug":"praying-rosary-meditating-mysteries","categoryList":["body-mind-spirit","religion-spirituality","christianity","catholicism"],"_links":{"self":"/articles/235851"}},{"articleId":284787,"title":"What Your Society Says About You","slug":"what-your-society-says-about-you","categoryList":["academics-the-arts","humanities"],"_links":{"self":"/articles/284787"}}],"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=\"9559\">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":9559,"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. ","hasArticle":false,"_links":{"self":"https://dummies-api.dummies.com/v2/authors/9559"}}],"_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-63221b159fcc2\"></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-63221b15a0707\"></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":"","sponsorEbookTitle":"","sponsorEbookLink":"","sponsorEbookImage":{"src":null,"width":0,"height":0}},"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-09-14T18:19:01+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":" <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. ","hasArticle":false,"_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":[{"articleId":192609,"title":"How to Pray the Rosary: A Comprehensive Guide","slug":"how-to-pray-the-rosary","categoryList":["body-mind-spirit","religion-spirituality","christianity","catholicism"],"_links":{"self":"/articles/192609"}},{"articleId":208741,"title":"Kabbalah For Dummies Cheat Sheet","slug":"kabbalah-for-dummies-cheat-sheet","categoryList":["body-mind-spirit","religion-spirituality","kabbalah"],"_links":{"self":"/articles/208741"}},{"articleId":230957,"title":"Nikon D3400 For Dummies Cheat Sheet","slug":"nikon-d3400-dummies-cheat-sheet","categoryList":["home-auto-hobbies","photography"],"_links":{"self":"/articles/230957"}},{"articleId":235851,"title":"Praying the Rosary and Meditating on the Mysteries","slug":"praying-rosary-meditating-mysteries","categoryList":["body-mind-spirit","religion-spirituality","christianity","catholicism"],"_links":{"self":"/articles/235851"}},{"articleId":284787,"title":"What Your Society Says About You","slug":"what-your-society-says-about-you","categoryList":["academics-the-arts","humanities"],"_links":{"self":"/articles/284787"}}],"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=\"9559\">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":9559,"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. ","hasArticle":false,"_links":{"self":"https://dummies-api.dummies.com/v2/authors/9559"}}],"_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-63221b1596ed2\"></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-63221b1597923\"></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":"","sponsorEbookTitle":"","sponsorEbookLink":"","sponsorEbookImage":{"src":null,"width":0,"height":0}},"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-09-14T18:19:01+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":" <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. ","hasArticle":false,"_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":[{"articleId":192609,"title":"How to Pray the Rosary: A Comprehensive Guide","slug":"how-to-pray-the-rosary","categoryList":["body-mind-spirit","religion-spirituality","christianity","catholicism"],"_links":{"self":"/articles/192609"}},{"articleId":208741,"title":"Kabbalah For Dummies Cheat Sheet","slug":"kabbalah-for-dummies-cheat-sheet","categoryList":["body-mind-spirit","religion-spirituality","kabbalah"],"_links":{"self":"/articles/208741"}},{"articleId":230957,"title":"Nikon D3400 For Dummies Cheat Sheet","slug":"nikon-d3400-dummies-cheat-sheet","categoryList":["home-auto-hobbies","photography"],"_links":{"self":"/articles/230957"}},{"articleId":235851,"title":"Praying the Rosary and Meditating on the Mysteries","slug":"praying-rosary-meditating-mysteries","categoryList":["body-mind-spirit","religion-spirituality","christianity","catholicism"],"_links":{"self":"/articles/235851"}},{"articleId":284787,"title":"What Your Society Says About You","slug":"what-your-society-says-about-you","categoryList":["academics-the-arts","humanities"],"_links":{"self":"/articles/284787"}}],"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=\"9559\">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":9559,"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. ","hasArticle":false,"_links":{"self":"https://dummies-api.dummies.com/v2/authors/9559"}}],"_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-63221b158f00d\"></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-63221b158f87f\"></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":"","sponsorEbookTitle":"","sponsorEbookLink":"","sponsorEbookImage":{"src":null,"width":0,"height":0}},"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-09-14T18:19:01+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\">&lt;i&gt;Row_value&lt;/i&gt;MATCH [UNIQUE] [SIMPLE| PARTIAL | FULL ] &lt;i&gt;Subquery&lt;/i&gt;</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\">&lt;i&gt;Row_value&lt;/i&gt;MATCH [UNIQUE] [SIMPLE| PARTIAL | FULL ] &lt;i&gt;Subquery&lt;/i&gt;</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":" <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. ","hasArticle":false,"_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":[{"articleId":192609,"title":"How to Pray the Rosary: A Comprehensive Guide","slug":"how-to-pray-the-rosary","categoryList":["body-mind-spirit","religion-spirituality","christianity","catholicism"],"_links":{"self":"/articles/192609"}},{"articleId":208741,"title":"Kabbalah For Dummies Cheat Sheet","slug":"kabbalah-for-dummies-cheat-sheet","categoryList":["body-mind-spirit","religion-spirituality","kabbalah"],"_links":{"self":"/articles/208741"}},{"articleId":230957,"title":"Nikon D3400 For Dummies Cheat Sheet","slug":"nikon-d3400-dummies-cheat-sheet","categoryList":["home-auto-hobbies","photography"],"_links":{"self":"/articles/230957"}},{"articleId":235851,"title":"Praying the Rosary and Meditating on the Mysteries","slug":"praying-rosary-meditating-mysteries","categoryList":["body-mind-spirit","religion-spirituality","christianity","catholicism"],"_links":{"self":"/articles/235851"}},{"articleId":284787,"title":"What Your Society Says About You","slug":"what-your-society-says-about-you","categoryList":["academics-the-arts","humanities"],"_links":{"self":"/articles/284787"}}],"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=\"9559\">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":9559,"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. ","hasArticle":false,"_links":{"self":"https://dummies-api.dummies.com/v2/authors/9559"}}],"_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-63221b1586f63\"></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-63221b15879ad\"></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":"","sponsorEbookTitle":"","sponsorEbookLink":"","sponsorEbookImage":{"src":null,"width":0,"height":0}},"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-09-14T18:19:01+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 &lt;&gt;\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 &lt;&gt;\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":" <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. ","hasArticle":false,"_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":[{"articleId":192609,"title":"How to Pray the Rosary: A Comprehensive Guide","slug":"how-to-pray-the-rosary","categoryList":["body-mind-spirit","religion-spirituality","christianity","catholicism"],"_links":{"self":"/articles/192609"}},{"articleId":208741,"title":"Kabbalah For Dummies Cheat Sheet","slug":"kabbalah-for-dummies-cheat-sheet","categoryList":["body-mind-spirit","religion-spirituality","kabbalah"],"_links":{"self":"/articles/208741"}},{"articleId":230957,"title":"Nikon D3400 For Dummies Cheat Sheet","slug":"nikon-d3400-dummies-cheat-sheet","categoryList":["home-auto-hobbies","photography"],"_links":{"self":"/articles/230957"}},{"articleId":235851,"title":"Praying the Rosary and Meditating on the Mysteries","slug":"praying-rosary-meditating-mysteries","categoryList":["body-mind-spirit","religion-spirituality","christianity","catholicism"],"_links":{"self":"/articles/235851"}},{"articleId":284787,"title":"What Your Society Says About You","slug":"what-your-society-says-about-you","categoryList":["academics-the-arts","humanities"],"_links":{"self":"/articles/284787"}}],"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=\"9559\">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":9559,"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. ","hasArticle":false,"_links":{"self":"https://dummies-api.dummies.com/v2/authors/9559"}}],"_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-63221b157e1ef\"></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-63221b157ed53\"></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":"","sponsorEbookTitle":"","sponsorEbookLink":"","sponsorEbookImage":{"src":null,"width":0,"height":0}},"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}],"_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":26}],"articleTypeFilter":[{"articleType":"All Types","count":26},{"articleType":"Articles","count":23},{"articleType":"Cheat Sheet","count":2},{"articleType":"Step by Step","count":1}]},"filterDataLoadedStatus":"success","pageSize":10},"adsState":{"pageScripts":{"headers":{"timestamp":"2025-04-17T15:50:01+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":295890,"title":"Career Shifting","hasSubCategories":false,"url":"/collection/career-shifting-295890"},{"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":299891,"title":"For the College Bound","hasSubCategories":false,"url":"/collection/for-the-college-bound-299891"},{"collectionId":291934,"title":"For the Exam-Season Crammer","hasSubCategories":false,"url":"/collection/for-the-exam-season-crammer-291934"},{"collectionId":301547,"title":"For the Game Day Prepper","hasSubCategories":false,"url":"/collection/big-game-day-prep-made-easy-301547"}],"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":{}}},"profileState":{"auth":{},"userOptions":{},"status":"success"}}
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
Career Shifting
Contemplating the Cosmos
For Those Seeking Peace of Mind
For the Aspiring Aficionado
For the Budding Cannabis Enthusiast
For the College Bound
For the Exam-Season Crammer
For the Game Day Prepper
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-12-2024

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 Data Types

Article / Updated 09-30-2022

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. Exact Numerics: INTEGER SMALLINT BIGINT NUMERIC DECIMAL DECFLOAT Approximate Numerics: REAL DOUBLE PRECISION FLOAT Boolean: BOOLEAN Character Strings: CHARACTER (CHAR) CHARACTER VARYING (VARCHAR) NATIONAL CHARACTER (NCHAR) NATIONAL CHARACTER VARYING (NVARCHAR) Datetimes: DATE TIME TIMESTAMP TIME WITH TIMEZONE TIMESTAMP WITH TIMEZONE Intervals: INTERVAL DAY INTERVAL YEAR Large Objects: BLOB CLOB Collection Types: ARRAY MULTISET Other Types: ROW XML

View Article
SQL SQL Criteria for Normal Forms

Article / Updated 09-30-2022

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. First Normal Form (1NF): Table must be two-dimensional, with rows and columns. Each row contains data that pertains to one thing or one portion of a thing. Each column contains data for a single attribute of the thing being described. Each cell (intersection of row and column) of the table must be single-valued. All entries in a column must be of the same kind. Each column must have a unique name. No two rows may be identical. The order of the columns and of the rows does not matter. Second Normal Form (2NF): Table must be in first normal form (1NF). All non-key attributes (columns) must be dependent on the entire key. Third Normal Form (3NF): Table must be in second normal form (2NF). Table has no transitive dependencies. Domain-Key Normal Form (DK/NF): Every constraint on the table is a logical consequence of the definition of keys and domains.

View Article
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: <i>Row_value</i>MATCH [UNIQUE] [SIMPLE| PARTIAL | FULL ] <i>Subquery</i> 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
page 1
page 2
page 3

Quick Links

  • About For Dummies
  • Contact Us
  • Activate Online Content

Connect

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.

Copyright @ 2000-2024 by John Wiley & Sons, Inc., or related companies. All rights reserved, including rights for text and data mining and training of artificial technologies or similar technologies.

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