{"id":91,"date":"2014-02-03T16:59:58","date_gmt":"2014-02-03T23:59:58","guid":{"rendered":"https:\/\/blogs.ubc.ca\/coetoolbox\/?page_id=91"},"modified":"2016-10-13T16:54:15","modified_gmt":"2016-10-13T23:54:15","slug":"queries","status":"publish","type":"page","link":"https:\/\/blogs.ubc.ca\/coetoolbox\/databases\/queries\/","title":{"rendered":"Queries Basics"},"content":{"rendered":"<p><img loading=\"lazy\" decoding=\"async\" class=\"alignright size-thumbnail wp-image-970\" src=\"https:\/\/blogs.ubc.ca\/coetoolbox\/files\/2014\/12\/query-150x150.png\" alt=\"query\" width=\"150\" height=\"150\" \/>The most common operation in SQL is the query, which is performed with the SELECT statement. A typical query has the form:<\/p>\n<p style=\"padding-left: 30px;\"><span style=\"color: #800080;\"><span style=\"color: #800000;\">SELECT<\/span> <\/span>Columns<br \/>\n<span style=\"color: #800080;\"><span style=\"color: #800000;\">FROM<\/span> <\/span>Tables<br \/>\n<span style=\"color: #800080;\"><span style=\"color: #800000;\">WHERE<\/span> <\/span>Conditions<\/p>\n<p>The result of a SQL query is a new table called the <strong>result set<\/strong>. Let&#8217;s see how to use each clause of a query.<\/p>\n<h2>SELECT<\/h2>\n<p>Indicates a list of columns to be included in the result set.<\/p>\n<ul>\n<li>Use comma to select more than one column.<\/li>\n<li>Use asterisk (<span style=\"color: #0000ff;\">*<\/span>) to select all the columns of the queried table(s).<br \/>\nE.g. SELECT <span style=\"color: #993300;\">*<\/span> \u000bFROM Students.<\/li>\n<li>Use <span style=\"color: #0000ff;\">AS<\/span> statement to name columns of the result set. This can be useful when column names are big or not very readable.<br \/>\nE.g. SELECT StudentName <span style=\"color: #993300;\">AS<\/span> Name<\/li>\n<li>Use <span style=\"color: #0000ff;\">DISTINCT<\/span> keyword to return only different values from queries that may contain duplicated values.<br \/>\nE.g. SELECT <span style=\"color: #993300;\">DISTINCT<\/span> Program FROM Students.<\/li>\n<\/ul>\n<h2>FROM<\/h2>\n<p>Indicates the table(s) from which data is to be retrieved. Tables can be named using <strong>aliases<\/strong> to make them easier to reference, especially when table names are long or when querying more than one table. For example:<\/p>\n<p style=\"padding-left: 30px;\">SELECT * FROM <span style=\"color: #800000;\">Students <span style=\"color: #0000ff;\">AS S<\/span><\/span><br \/>\nor<br \/>\nFROM <span style=\"color: #800000;\">Students <span style=\"color: #0000ff;\">S<\/span><\/span><\/p>\n<h2>WHERE<\/h2>\n<p>Eliminates all rows from the result set that don&#8217;t meet a condition or a set of conditions. Conditions are expressed using column names from the tables queried and <strong>operators<\/strong>:<\/p>\n<table border=\"1\" cellspacing=\"0\" cellpadding=\"0\">\n<tbody>\n<tr>\n<td>Operator<\/td>\n<td>Values<\/td>\n<td>Example<\/td>\n<\/tr>\n<tr>\n<td>Arithmetic operations<\/td>\n<td>+, -, *, \/<\/td>\n<td><\/td>\n<\/tr>\n<tr>\n<td>Comparison Operators<\/td>\n<td>=, &lt;&gt;, &lt;, &gt;, &lt;=, &gt;=<\/td>\n<td>WHERE Program = &#8216;MMOR&#8217;<\/td>\n<\/tr>\n<tr>\n<td>Logical operators<\/td>\n<td>AND, OR, NOT<\/td>\n<td>WHERE Program = &#8216;MMOR&#8217; AND Year = 2014<br \/>\nWHERE NOT Program = &#8216;MMOR&#8217;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h2>ORDER BY<\/h2>\n<p>This optional clause sorts the results in a specific order, <span style=\"color: #0000ff;\">ASC<\/span> for ascending order or <span style=\"color: #0000ff;\">DESC<\/span> for descending order:<\/p>\n<p style=\"padding-left: 30px;\">SELECT *<br \/>\nFROM Students<br \/>\n<span style=\"color: #993300;\">ORDER BY<\/span> StudentName <span style=\"color: #800000;\">DESC<\/span><\/p>\n<p>Ascending order is the default option and will be assumed if it&#8217;s not specified.<\/p>\n<p style=\"padding-left: 30px;\">SELECT *<br \/>\nFROM Students<br \/>\n<span style=\"color: #993300;\">ORDER BY<\/span> Year<\/p>\n<p>You can state multiple columns separated by comma to sort by multiple columns.<\/p>\n<p style=\"padding-left: 30px;\">SELECT *<br \/>\nFROM Students<br \/>\n<span style=\"color: #993300;\">ORDER BY<\/span> Year, Age DESC<\/p>\n<p>You can find other examples with these statements <a href=\"http:\/\/www.w3schools.com\/sql\">here<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The most common operation in SQL is the query, which is performed with the SELECT statement. A typical query has the form: SELECT Columns FROM Tables WHERE Conditions The result of a SQL query is a new table called the result set. Let&#8217;s see how to use each clause of a query. SELECT Indicates a [&hellip;]<\/p>\n","protected":false},"author":22982,"featured_media":0,"parent":74,"menu_order":3,"comment_status":"open","ping_status":"closed","template":"page-templates\/full-width.php","meta":{"footnotes":""},"class_list":["post-91","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/91","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/users\/22982"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/comments?post=91"}],"version-history":[{"count":36,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/91\/revisions"}],"predecessor-version":[{"id":1520,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/91\/revisions\/1520"}],"up":[{"embeddable":true,"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/pages\/74"}],"wp:attachment":[{"href":"https:\/\/blogs.ubc.ca\/coetoolbox\/wp-json\/wp\/v2\/media?parent=91"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}