{"id":1107,"date":"2022-10-13T09:33:15","date_gmt":"2022-10-13T13:33:15","guid":{"rendered":"https:\/\/folvera.commons.gc.cuny.edu\/?p=1107"},"modified":"2022-10-13T09:33:15","modified_gmt":"2022-10-13T13:33:15","slug":"introduction-to-structured-query-language-for-data-analytics-sf22sql1001-day-4","status":"publish","type":"post","link":"https:\/\/folvera.commons.gc.cuny.edu\/?p=1107","title":{"rendered":"Introduction to Structured Query Language for Data Analytics (SF22SQL1001) \u2014 Day 4"},"content":{"rendered":"<p>We continue working with functions. Some affect strings (<tt>RIGHT<\/tt>, <tt>LEFT<\/tt>, <tt>SUBSTRING<\/tt>, etc.) while others affect numbers (<tt>TIME<\/tt>, <tt>DATE<\/tt>, <tt>FORMAT<\/tt>, etc.) as well as aggregate functions, which need the <tt>GROUP BY<\/tt> clause.<\/p>\n<pre>\r\n    SELECT field1,\r\n      aggregate_function(field2),\r\n      field3\r\n       -- other fields if any\r\n    FROM table1\r\n    INNER|LEFT|RIGHT JOIN table2\r\n      ON table1.shared_data1 = table2.shared_data2\r\n        AND table1.shared_data2 = table2.shared_data2\r\n       -- other tables if any\r\n    WHERE condition1\r\n      AND|OR condition2\r\n      -- other conditions if any\r\n    GROUP BY field1\r\n    ORDER BY field1 ASC|DESC,\r\n      field2 ASC|DESC,\r\n      field3 ASC|DESC,\r\n      ...; -- other fields if any\r\n<\/pre>\n<p>Download the <a href=\"https:\/\/folvera.commons.gc.cuny.edu\/wp-content\/blogs.dir\/2501\/files\/2022\/10\/SF22SQL1001_20221012.pdf\">class notes<\/a> for day 4.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>We continue working with functions. Some affect strings (RIGHT, LEFT, SUBSTRING, etc.) while others affect numbers (TIME, DATE, FORMAT, etc.) as well as aggregate functions, which need the GROUP BY clause. SELECT field1, aggregate_function(field2), field3 &#8212; other fields if any FROM table1 INNER|LEFT|RIGHT JOIN table2 ON table1.shared_data1 = table2.shared_data2 AND table1.shared_data2 = table2.shared_data2 &#8212; other [&hellip;]<\/p>\n","protected":false},"author":7213,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"send_to_group_blog":0,"footnotes":""},"categories":[31,3,1,2],"tags":[],"class_list":["post-1107","post","type-post","status-publish","format-standard","hentry","category-sf22sql1001","category-computer-programming","category-cuny","category-sql"],"_links":{"self":[{"href":"https:\/\/folvera.commons.gc.cuny.edu\/index.php?rest_route=\/wp\/v2\/posts\/1107","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/folvera.commons.gc.cuny.edu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/folvera.commons.gc.cuny.edu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/folvera.commons.gc.cuny.edu\/index.php?rest_route=\/wp\/v2\/users\/7213"}],"replies":[{"embeddable":true,"href":"https:\/\/folvera.commons.gc.cuny.edu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1107"}],"version-history":[{"count":2,"href":"https:\/\/folvera.commons.gc.cuny.edu\/index.php?rest_route=\/wp\/v2\/posts\/1107\/revisions"}],"predecessor-version":[{"id":1110,"href":"https:\/\/folvera.commons.gc.cuny.edu\/index.php?rest_route=\/wp\/v2\/posts\/1107\/revisions\/1110"}],"wp:attachment":[{"href":"https:\/\/folvera.commons.gc.cuny.edu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1107"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/folvera.commons.gc.cuny.edu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1107"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/folvera.commons.gc.cuny.edu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1107"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}