{"id":9764,"date":"2019-06-18T09:25:16","date_gmt":"2019-06-18T09:25:16","guid":{"rendered":"https:\/\/ittutorial.org\/?p=9764"},"modified":"2019-07-18T14:44:12","modified_gmt":"2019-07-18T14:44:12","slug":"oracle-sql-tutorials-chapter-3-2","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/oracle-sql-tutorials-chapter-3-2\/","title":{"rendered":"Oracle SQL Tutorials \u2013 Chapter 3 (Part 2 of 3)"},"content":{"rendered":"<p><strong>NUMERIC FUNCTIONS<\/strong><\/p>\n<p><strong>ROUND<\/strong><\/p>\n<ul>\n<li>Let\u2019s try some different usages of the ROUND function.<\/li>\n<\/ul>\n<pre>SELECT\r\n\r\nROUND (45.923),\r\n\r\nROUND (45.943, 1),\r\n\r\nROUND (45.924, 2),\r\n\r\nROUND (44.923, -1),\r\n\r\nROUND (55.923, -2)\r\n\r\nFROM DUAL;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"538\" height=\"47\" class=\"wp-image-9796\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/06\/word-image-91.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/06\/word-image-91.png 538w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/06\/word-image-91-300x26.png 300w\" sizes=\"auto, (max-width: 538px) 100vw, 538px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><strong>TRUNC<\/strong><\/p>\n<ul>\n<li>Let\u2019s try some different usages of the TRUNC function.<\/li>\n<\/ul>\n<pre>SELECT\r\n\r\nTRUNC (45.923),\r\n\r\nTRUNC (45.943, 1),\r\n\r\nTRUNC (45.953, 2),\r\n\r\nTRUNC (45.923, -1),\r\n\r\nTRUNC (45.923, -2)\r\n\r\nFROM DUAL;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"534\" height=\"49\" class=\"wp-image-9798\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/06\/word-image-93.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/06\/word-image-93.png 534w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/06\/word-image-93-300x28.png 300w\" sizes=\"auto, (max-width: 534px) 100vw, 534px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><strong>FLOOR<\/strong><\/p>\n<ul>\n<li>Here\u2019s the usage of the FLOOR function.<\/li>\n<\/ul>\n<pre>SELECT FLOOR(46.993)\r\n\r\nFROM DUAL;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"97\" height=\"49\" class=\"wp-image-9800\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/06\/word-image-95.png\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><strong>MOD<\/strong><\/p>\n<ul>\n<li>Here\u2019s the usage of the MOD function<\/li>\n<\/ul>\n<pre>SELECT MOD(1245,10)\r\n\r\nFROM dual;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"93\" height=\"47\" class=\"wp-image-9801\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/06\/word-image-96.png\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><strong>SIGN <\/strong><\/p>\n<ul>\n<li>The usage of the SIGN is like below.<\/li>\n<\/ul>\n<pre>SELECT SIGN(15),\r\n\r\nSIGN(-15)\r\n\r\nFROM dual;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"125\" height=\"50\" class=\"wp-image-9802\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/06\/word-image-97.png\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><strong>DATE FUNCTIONS<\/strong><\/p>\n<ul>\n<li>Let\u2019s see how to use MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, ROUND, TRUNC functions.<\/li>\n<\/ul>\n<pre>SELECT\r\n\r\nMONTHS_BETWEEN (TO_DATE ('01.08.2013', 'dd.mm.yyyy'),\r\n\r\nTO_DATE ('01.01.2013', 'dd.mm.yyyy'))\"Months Between\",\r\n\r\nSYSDATE \"Today\",\r\n\r\nADD_MONTHS (SYSDATE, 5) \"Add Months\",\r\n\r\nNEXT_DAY (SYSDATE, 'MONDAY') \"Next Monday\",\r\n\r\nLAST_DAY (SYSDATE) \"Last Day of this month\",\r\n\r\nROUND (SYSDATE - 60, 'MONTH') r1,\r\n\r\nROUND (SYSDATE, 'YEAR') r2,\r\n\r\nTRUNC (SYSDATE, 'MONTH') t1,\r\n\r\nTRUNC (SYSDATE, 'YEAR') t2\r\n\r\nFROM DUAL;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"825\" height=\"46\" class=\"wp-image-9803\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/06\/word-image-98.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/06\/word-image-98.png 825w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/06\/word-image-98-300x17.png 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/06\/word-image-98-768x43.png 768w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/06\/word-image-98-800x46.png 800w\" sizes=\"auto, (max-width: 825px) 100vw, 825px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><strong>ARITHMETIC OPERATIONS WITH DATES<\/strong><\/p>\n<p>Date + Number = Date : It adds days to date, as entered value.<\/p>\n<p>Date \u2013 Number = Date : It subtracts days from date , as entered value.<\/p>\n<p>Date \u2013 Date = Number of days : It gives the number of days between two dates.<\/p>\n<p>Date + ( Number\/24) = Date : It adds hours as entered value to the date.<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li>Let\u2019s see how to use these operations.<\/li>\n<\/ul>\n<pre>SELECT\r\n\r\nfirst_name,\r\n\r\nlast_name,\r\n\r\nhire_date,\r\n\r\nhire_date - 7,\r\n\r\nhire_date + 1 \/ 24\r\n\r\nFROM hr.employees\r\n\r\nWHERE department_id = 90;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"433\" height=\"90\" class=\"wp-image-9810\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/06\/word-image-105.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/06\/word-image-105.png 433w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/06\/word-image-105-300x62.png 300w\" sizes=\"auto, (max-width: 433px) 100vw, 433px\" \/><\/p>\n","protected":false},"excerpt":{"rendered":"<p>NUMERIC FUNCTIONS ROUND Let\u2019s try some different usages of the ROUND function. SELECT ROUND (45.923), ROUND (45.943, 1), ROUND (45.924, 2), ROUND (44.923, -1), ROUND (55.923, -2) FROM DUAL; &nbsp; TRUNC Let\u2019s try some different usages of the TRUNC function. SELECT TRUNC (45.923), TRUNC (45.943, 1), TRUNC (45.953, 2), TRUNC (45.923, -1), TRUNC (45.923, -2) &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":[1994],"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-9764","post","type-post","status-publish","format-standard","","category-oracle","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\/9764","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=9764"}],"version-history":[{"count":3,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/9764\/revisions"}],"predecessor-version":[{"id":10653,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/9764\/revisions\/10653"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=9764"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=9764"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=9764"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}