{"id":9899,"date":"2019-07-04T11:46:28","date_gmt":"2019-07-04T11:46:28","guid":{"rendered":"https:\/\/ittutorial.org\/?p=9899"},"modified":"2019-07-18T10:30:30","modified_gmt":"2019-07-18T10:30:30","slug":"oracle-sql-tutorials-chapter-4-1","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/oracle-sql-tutorials-chapter-4-1\/","title":{"rendered":"Oracle SQL Tutorials \u2013 Chapter 4"},"content":{"rendered":"<p><strong>Chapter 4 &#8211; Group Functions<\/strong><\/p>\n<ul>\n<li>AVG Function<\/li>\n<li>SUM Function<\/li>\n<li>MIN Function<\/li>\n<li>MAX Function<\/li>\n<li>COUNT Function<\/li>\n<li>DISTINCT Function<\/li>\n<li>GROUP BY Statement<\/li>\n<li>HAVING Statement<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<ul>\n<li>One row functions can be applied to a one row of a table and returns a single row.<\/li>\n<li>But group functions applied to a whole table. Also can be applied to sub-groups, using GROUP BY statement.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p><strong>AVG &amp; SUM<\/strong><\/p>\n<ul>\n<li>Let\u2019s list the average and the sumof the salaries.<\/li>\n<\/ul>\n<pre>SELECT AVG (salary), SUM (salary)\r\n\r\nFROM hr.employees;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"206\" height=\"51\" class=\"wp-image-10479\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-81.png\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><strong>MIN &amp; MAX<\/strong><\/p>\n<ul>\n<li>Let\u2019s select the minimum and the maximum salary.<\/li>\n<\/ul>\n<pre>SELECT MIN (salary), MAX (salary)\r\n\r\nFROM hr.employees;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"173\" height=\"53\" class=\"wp-image-10480\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-82.png\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><strong>COUNT <\/strong><\/p>\n<ul>\n<li>Let\u2019s list the count of the employees and how many different positions.<\/li>\n<\/ul>\n<pre>SELECT COUNT (*), COUNT (DISTINCT job_id)\r\n\r\nFROM hr.employees;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"222\" height=\"46\" class=\"wp-image-10481\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-83.png\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><strong>DISTINCT<\/strong><\/p>\n<ul>\n<li>The Distinct command, groups the data and shows the results just one time.<\/li>\n<li>Let\u2019s list all the positions.<\/li>\n<\/ul>\n<pre>SELECT DISTINCT job_id FROM hr.employees;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"90\" height=\"171\" class=\"wp-image-10482\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-84.png\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><strong>GROUP BY STATEMENT<\/strong><\/p>\n<ul>\n<li>With this statement, the rows in the table can be divided into smaller groups.<\/li>\n<li>The columns in the Group By statement, must be added to Select statement.<\/li>\n<li>The aliases can\u2019t be used in Group By statement.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"513\" height=\"123\" class=\"wp-image-10483\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-85.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-85.png 513w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-85-300x72.png 300w\" sizes=\"auto, (max-width: 513px) 100vw, 513px\" \/><\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li>Let\u2019s list the average salaries of departments.<\/li>\n<\/ul>\n<pre>SELECT department_id, AVG (salary)\r\n\r\nFROM hr.employees\r\n\r\nGROUP BY department_id\r\n\r\nORDER BY 1;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"222\" height=\"173\" class=\"wp-image-10484\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-86.png\" \/><\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li>Let\u2019s write another querry using Group By function.<\/li>\n<\/ul>\n<pre>SELECT department_id,\r\n\r\njob_id,\r\n\r\nAVG (salary),\r\n\r\nSUM (salary),\r\n\r\nCOUNT (*) Employees\r\n\r\nFROM hr.employees\r\n\r\nGROUP BY department_id, job_id\r\n\r\nORDER BY department_id;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"468\" height=\"171\" class=\"wp-image-10485\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-87.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-87.png 468w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-87-300x110.png 300w\" sizes=\"auto, (max-width: 468px) 100vw, 468px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><strong>HAVING STATEMENT<\/strong><\/p>\n<ul>\n<li>Where statement can\u2019t be used with group functions.<\/li>\n<li>So, the Having statement is used instead of where condition.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"502\" height=\"163\" class=\"wp-image-10486\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-88.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-88.png 502w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-88-300x97.png 300w\" sizes=\"auto, (max-width: 502px) 100vw, 502px\" \/><\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li>Let\u2019s list the positions which average salaries are higher than 10,000.<\/li>\n<\/ul>\n<pre>SELECT job_id, AVG (salary)\r\n\r\nFROM hr.employees\r\n\r\nGROUP BY job_id\r\n\r\nHAVING AVG (salary) &gt; 10000;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"145\" height=\"174\" class=\"wp-image-10487\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-89.png\" \/><\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li>Here is another example of usage having statement.<\/li>\n<\/ul>\n<pre>SELECT job_id, SUM (salary)\r\n\r\nFROM hr.employees\r\n\r\nWHERE job_id NOT LIKE '%REP%'\r\n\r\nGROUP BY job_id\r\n\r\nHAVING SUM (salary) &gt; 13000\r\n\r\nORDER BY SUM (salary);<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"169\" height=\"173\" class=\"wp-image-10488\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-90.png\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><strong>NESTED GROUP FUNCTIONS<\/strong><\/p>\n<ul>\n<li>Here are two examples and outputs of nested functions.<\/li>\n<\/ul>\n<pre>SELECT MAX (AVG (salary))\r\n\r\nFROM hr.employees\r\n\r\nGROUP BY department_id;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"125\" height=\"46\" class=\"wp-image-10489\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-91.png\" \/><\/p>\n<p>&nbsp;<\/p>\n<pre>SELECT department_id, AVG (salary)\r\n\r\nFROM hr.employees\r\n\r\nGROUP BY department_id\r\n\r\nHAVING (MAX (salary)) &gt; ALL ( SELECT AVG (salary)\r\n\r\nFROM hr.employees\r\n\r\nGROUP BY department_id);<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"223\" height=\"48\" class=\"wp-image-10490\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-92.png\" \/><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Chapter 4 &#8211; Group Functions AVG Function SUM Function MIN Function MAX Function COUNT Function DISTINCT Function GROUP BY Statement HAVING Statement &nbsp; One row functions can be applied to a one row of a table and returns a single row. But group functions applied to a whole table. Also can be applied to sub-groups, &hellip;<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[3019],"tags":[3212,3009,3048,3010,3198,1340,3001,3013,3215,3216,3202,3014,1572,3206,3208,3207,3209,3205,3003,3210,3211,3204,3017,3016,3199,3200,3214,3213,3201,3000,3012,3203,3002,3008],"class_list":["post-9899","post","type-post","status-publish","format-standard","","category-oracle-sql","tag-desc-syntax","tag-how-to-write-sql","tag-learn-oracle","tag-learn-sql","tag-operator-precedence","tag-oracle-sql","tag-oracle-sql-tutorial","tag-oracle-sql-tutorials","tag-order-by-asc","tag-order-by-desc","tag-order-by-syntax","tag-rules-of-sql","tag-sql","tag-sql-asc","tag-sql-asc-syntax","tag-sql-ascending","tag-sql-ascending-syntax","tag-sql-assigning-variable","tag-sql-commands","tag-sql-desc","tag-sql-desc-syntax","tag-sql-how-to-assign-variable","tag-sql-komutlari","tag-sql-ogren","tag-sql-operator-precedence","tag-sql-order-by","tag-sql-order-by-desc","tag-sql-ordey-by-asc","tag-sql-ordey-by-syntax","tag-sql-tutorial","tag-sql-tutorials","tag-sql-variable","tag-what-is-sql","tag-writing-sql"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/9899","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=9899"}],"version-history":[{"count":5,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/9899\/revisions"}],"predecessor-version":[{"id":10670,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/9899\/revisions\/10670"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=9899"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=9899"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=9899"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}