Oracle – Sum Of Product Of Number

Sum of products of digits of given number can be derived by using following PLSQL block.

SET SERVEROUTPUT ON 

DECLARE
 
    p_number        INTEGER := 9753124680;
    v_number        INTEGER;
    v_digit_1       INTEGER; 
    v_digit_2       INTEGER; 
    v_product       INTEGER;
    v_product_sum   INTEGER;
     
BEGIN
     
    v_number := TO_CHAR(p_number);
    v_product_sum := 0;

    WHILE v_number <> 0 LOOP
        EXIT WHEN LENGTH(v_number) = 1;
        v_digit_1 := MOD(v_number, 10);
        v_digit_2 := SUBSTR(MOD(v_number, 100), 1, 1);
        v_product := v_digit_1 * v_digit_2;
        v_product_sum := v_product_sum + v_product;
        v_number := TRUNC(v_number / 10);
    END LOOP;
 
    DBMS_OUTPUT.PUT_LINE('Sum Of Products Of Consecutive Digits = ' || v_product_sum); 
END;
/

Oracle – Joins

A JOIN clause is used to combine rows from two or more tables to retrieve data, based on a related column between them.

  • Equi Join
  • Outer Join
    • Right Outer Join
    • Left Outer Join
    • Full Outer Join
  • Self Join
  • Cross Join
  • Natural Join
  • Non-Inner Join

Equi Join

It is a join with a join condition containing an equality operator. An equi join returns only the rows that have equivalent values for the specified columns.

Example 1

SELECT e.employee_id,
       e.first_name,
       e.hire_date,
       e.salary,
       e.department_id,
       d.department_name
  FROM employees   e,
       departments d
 WHERE e.department_id = d.department_id;

Example 2

SELECT e.employee_id,
       e.first_name,
       e.hire_date,
       e.salary,
       e.department_id,
       d.department_name,
       d.location_id,
       l.country_id
  FROM employees   e,
       departments d,
       locations l
 WHERE e.department_id = d.department_id
   AND d.location_id = l.location_id;

Example 3

SELECT e.department_id,
       d.department_name,
	   MIN(salary) "MIN Salary",
	   MAX(salary) "MAX Salary",
	   SUM(salary) "SUM Salary",
	   ROUND(AVG(salary)) "AVG Salary"
  FROM employees e,
       departments d
 WHERE e.department_id = d.department_id
 GROUP BY e.department_id, d.department_name
 ORDER BY 1;

Example 4

SELECT e.employee_id,
       e.first_name,
       e.hire_date,
       e.salary,
       e.department_id,
       d.department_name,
       l.street_address,
	   c.country_name
  FROM employees e JOIN departments d ON (e.department_id = d.department_id)
                   JOIN locations   l ON (d.location_id = l.location_id)
				   JOIN countries   c ON (l.country_id = c.country_id);

Outer Join

Right Outer Join

Records = Matched Record + Un Matched Record From Right Side

SELECT e.employee_id,
       e.first_name,
       e.hire_date,
       e.salary,
       e.department_id,
       d.department_name
  FROM employees e,
       departments d
  WHERE e.department_id(+) = d.department_id;
-- ===============================  
SELECT e.employee_id,
       e.first_name,
       e.hire_date,
       e.salary,
       e.department_id,
       d.department_name
  FROM employees e RIGHT JOIN departments d
  ON e.department_id = d.department_id;

Left Outer Join

Records = Matched Record + Un Matched Record From Left Side

SELECT e.employee_id,
       e.first_name,
       e.hire_date,
       e.salary,
       e.department_id,
       d.department_name
  FROM employees e,
       departments d
  WHERE e.department_id = d.department_id(+);
-- ===========================================
SELECT e.employee_id,
       e.first_name,
       e.hire_date,
       e.salary,
       e.department_id,
       d.department_name
  FROM employees e LEFT JOIN departments d
  ON e.department_id = d.department_id;

Full Outer Join

Records = Matched Record + Un Matched Record From Both Sides

SELECT e.employee_id,
       e.first_name,
       e.hire_date,
       e.salary,
       e.department_id,
       d.department_name
  FROM employees e FULL JOIN departments d
  ON e.department_id = d.department_id;

Self Join

A self JOIN is a regular join, but the table is joined with itself.

Example 1

SELECT e.employee_id,
       e.first_name,
	   e.hire_date,
	   e.salary,
	   e.manager_id,
	   m.first_name AS "Manager Name",
	   m.salary     AS "Manager Salary",
	   m.hire_date  AS "Manager Hire Date"
  FROM employees e, employees m
  WHERE e.manager_id = m.employee_id;

Example 2

Employee works for Manager

SELECT e1.first_name ||' works for ' ||  e2.first_name "Employee with their managers"
  FROM employees e1,
       employees e2
 WHERE e1.manager_id = e2.employee_id;

Example 3

Employees who are getting more salary than their managers.

SELECT e.employee_id,
       e.first_name,
	   e.hire_date,
	   e.salary,
	   e.manager_id,
	   m.first_name AS "Manager Name",
	   m.salary     AS "Manager Salary",
	   m.hire_date  AS "Manager Hire Date"
  FROM employees e, employees m
  WHERE e.manager_id = m.employee_id
  AND e.salary > m.salary;

Example 4

Employees who are experienced than their managers.

  SELECT e.employee_id,
       e.first_name,
	   e.hire_date,
	   e.salary,
	   e.manager_id,
	   m.first_name AS "Manager Name",
	   m.salary     AS "Manager Salary",
	   m.hire_date  AS  "Manager Hire Date"
  FROM employees e, employees m
  WHERE e.manager_id = m.employee_id
  AND e.hire_date < m.hire_date;

Cross Join

Cross Join produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table if no WHERE clause is used along with CROSS JOIN.This kind of result is called as Cartesian Product.

SELECT e.employee_id,
       e.first_name,
       m.first_name "Manager Name"
  FROM employees e CROSS JOIN employees m;
  
-- Result Count:
SELECT 107*107 FROM dual;

Natural Join

JOIN matched on one columns, If matched on more than one column, result is out of control.

SELECT e.employee_id,
       e.first_name,
	   department_id,
	   d.department_name
  FROM employees e NATURAL JOIN departments d;

Join By Using

SELECT e.employee_id,
       e.first_name,
	   department_id,
	   d.department_name
  FROM employees e JOIN departments d
  USING (department_id);

Non-Inner Join

If an ‘=’ is not used to get data from more than one table then it is called Non-Inner Join.

Creating Environment

CREATE TABLE student(
 name	VARCHAR2(25)
,mark 	NUMBER(3)
);

CREATE TABLE grade(
 grade		VARCHAR2(25)
,l_limit 	NUMBER(3)
,h_limit	NUMBER(3)
);

INSERT INTO student VALUES('Vijay' , 386);
INSERT INTO student VALUES('Fisher', 405);
INSERT INTO student VALUES('Riya'  , 451);
INSERT INTO student VALUES('Harini', 399);
INSERT INTO student VALUES('Star'  , 350);
INSERT INTO student VALUES('Pepper', 300);
INSERT INTO student VALUES('Bobby' , 249);

INSERT INTO grade VALUES('O',  451, 500);
INSERT INTO grade VALUES('A+', 401, 450);
INSERT INTO grade VALUES('A',  351, 400);
INSERT INTO grade VALUES('B+', 301, 350);
INSERT INTO grade VALUES('B',  251, 300);
INSERT INTO grade VALUES('S/W',  0, 250);

Example

SELECT s.name, 
       s.mark, 
	   g.grade
  FROM student s,
       grade   g
 WHERE s.mark BETWEEN g.l_limit AND g.h_limit 
 ORDER BY s.name;

Oracle – SQL Functions

SQL functions are built into Oracle Database and are available for use in various appropriate SQL statements.

Prerequisite

DROP TABLE emps;
SELECT employee_id FROM employees;
CREATE TABLE emps AS(SELECT * FROM employees WHERE 1=1 AND employee_id BETWEEN 100 AND 105);
ALTER TABLE emps ADD temp VARCHAR2(20);
UPDATE emps SET temp = NULL;

Single Row Functions

  • Character Functions
  • Number Functions
  • Date Functions
  • Type Conversions
  • General Functions

Character Functions

Case Manipulation

  • UPPER( )
  • LOWER( )
  • INITCAP( )
-- UPPER( )
-- =========
SELECT first_name, UPPER(first_name) FROM emps;

-- LOWER( )
-- =========
SELECT first_name, LOWER(first_name) FROM emps;

-- INITCAP( )
-- ===========
SELECT job_id, INITCAP(job_id) FROM emps;

Character Manipulation

  • LENGTH( )
  • CONCAT( )
  • REVERSE( )
  • SUBSTR( ), INSTR( )
  • LPAD( ), RPAD( )
  • LTRIM( ), RTRIM( ), TRIM( )
  • REPLACE( )
  • TRANSLATE( )
-- LENGTH() 
-- =========
-- Number of characters returned
-- LENGTH('string')

SELECT first_name, LENGTH(first_name) FROM emps;

-- CONCAT()
-- =========
-- CONCAT(char1, char2)
/*
CONCAT returns char1 concatenated with char2. Both char1 and char2 can be any of the datatypes 
CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is in the same character set as char1.
*/
SELECT first_name, CONCAT(first_name, last_name) FROM emps;

-- REVERSE()
-- ==========
-- REVERSE(string)
SELECT job_id, REVERSE(job_id) FROM emps;

-- SUBSTR()
-- =========
-- SUBSTR(string, start_position, end_position)
SELECT first_name, SUBSTR(first_name, 1, 3) FROM emps;
SELECT first_name, SUBSTR(first_name, 1, 3.7) FROM emps;

-- INSTR()
-- ========
-- INSTR(string, sub_string, start_position, occurance)
SELECT first_name, INSTR(first_name, 'a', 1, 1) FROM emps;
SELECT first_name, INSTR(first_name, 'e', 1, 2) FROM emps;

-- LPAD()
-- =======
-- LPAD(string, return_length, pad_value)
SELECT first_name, LPAD(first_name, 10, '$') FROM emps;

-- RPAD()
-- =======
-- RPAD(string, return_length, pad_value)
SELECT first_name, RPAD(first_name, 10, '*') FROM emps;

-- LTRIM()
-- ========
-- LTRIM(string, trim_value)
UPDATE emps SET temp = LPAD(first_name, 10, '$');
SELECT temp, LTRIM(temp, '$') FROM emps;

-- RTRIM()
-- ========
-- RTRIM(string, trim_value)
UPDATE emps SET temp = RPAD(first_name, 10, '$');
SELECT temp, RTRIM(temp, '$') FROM emps;

-- TRIM()
-- =======
-- TRIM(trim_value FROM string)
UPDATE emps SET temp = LPAD(RPAD(first_name, 10, '$'), 20, '$');
SELECT temp, TRIM('$' FROM temp) FROM emps;

-- REPLACE()
-- ==========
-- REPLACE(string, set_of_string_to_replace, replacement_string) 
UPDATE emps SET temp = NULL;
UPDATE emps SET temp = 'Sathiyan S' WHERE employee_id = 100;
SELECT temp, REPLACE(temp, 'Sa', 'Ab') FROM emps;

-- TRANSLATE()
-- ============
-- TRANSLATE(string, string_to_replace, replacement_string)
UPDATE emps SET temp = NULL;
UPDATE emps SET temp = 'Sathiyan S' WHERE employee_id = 100;
SELECT temp, TRANSLATE(temp, 'Sa', 'Ab') FROM emps;

-- REPLACE() & TRANSLATE()
-- ========================
SELECT 'Oracle PLSQL - System Analyst' AS sample_text,
REPLACE('Oracle PLSQL - System Analyst', 'ly', 'az') AS replace_test,
TRANSLATE('Oracle PLSQL - System Analyst', 'ly', 'az') AS translate_test
FROM dual;

Number Function

  • ROUND( )
  • TRUNC( ) – Number, Date
  • MOD( )
  • POWER( )
  • SIGN( )
  • ABS( )
  • FLOOR( )
  • CEIL( )
/*
Natural Value: 
Rounding Poisition  | Value	
		0  	 	  	|  0
		10 	 	  	| -1
		100	 	  	| -2
		1000 	  	| -3

Decimal Value:
Rounding Poisition  | Value	
		1  	 	  	| 1
		2 	 	  	| 2
		3	 	  	| 3
		4	 	  	| 4
*/

-- ROUND()
-- =======
-- Syntax: ROUND(number, position_value)
SELECT ROUND(234.789,  1) FROM dual; -- 234.8
SELECT ROUND(234.729,  1) FROM dual; -- 234.7
SELECT ROUND(234.729,  2) FROM dual; -- 234.73
SELECT ROUND(234.755,  2) FROM dual; -- 234.76
SELECT ROUND(234.7553, 3) FROM dual; -- 234.755
SELECT ROUND(234.7558, 3) FROM dual; -- 234.756
SELECT ROUND(234.7558, 0) FROM dual; -- 235
SELECT ROUND(234.789, -1) FROM dual; -- 230
SELECT ROUND(236.789, -1) FROM dual; -- 240
SELECT ROUND(234.729, -2) FROM dual; -- 200
SELECT ROUND(254.729, -2) FROM dual; -- 300
SELECT ROUND(534.7553,-3) FROM dual; -- 1000
SELECT ROUND(234.7553,-3) FROM dual; -- 0

-- TRUNC()
-- =======
-- Syntax: TRUNC(number, position_value) or TRUNC(date, options)
SELECT TRUNC(7865.3452332, 4) FROM dual; -- 7865.3452
SELECT TRUNC(7865.3452332, 3) FROM dual; -- 7865.345
SELECT TRUNC(7865.3452332, 2) FROM dual; -- 7865.34
SELECT TRUNC(7865.3452332, 1) FROM dual; -- 7865.3
SELECT TRUNC(7865.3452332, 0) FROM dual; -- 7865
SELECT TRUNC(7865.3452332,-1) FROM dual; -- 7860
SELECT TRUNC(7865.3452332,-2) FROM dual; -- 7800
SELECT TRUNC(7865.3452332,-3) FROM dual; -- 7000
SELECT TRUNC(7865.3452332,-4) FROM dual; -- 0

-- Current Month Starting
SELECT TRUNC(SYSDATE, 'MONTH') FROM dual;
-- Current Quarter Starting 
SELECT TRUNC(SYSDATE, 'Q') FROM dual;
-- Year Starting
SELECT TRUNC(SYSDATE, 'YEAR') FROM dual;
-- First Day Of Current Week(Sunday)
SELECT TRUNC(SYSDATE, 'DAY') FROM dual;
-- Current Date
SELECT TRUNC(SYSDATE, 'DDD') FROM dual;

-- MOD()
-- ======
-- Syntax: MOD(dividend_number, divisor_number), Returns Reminder
SELECT MOD(16,5), MOD(16,4) FROM dual;

-- POWER()
-- ========
-- Syntax: POWER(number, power_value)
SELECT POWER(2,2), POWER(6,2), POWER(3,3)  FROM dual;

-- SIGN()
-- =======	
-- Syntax: SIGN(number), Returns 1 if number is +ve or -1 if number is -ve
SELECT SIGN(-5), SIGN(0), SIGN(5) FROM dual;

-- ABS()
-- ======
-- Syntax: ABS(number), Returns Always +ve value of given number
SELECT ABS(-10), ABS(10) FROM dual;

-- FLOOR()	
-- ========
-- Syntax: FLOOR(number), Returns near lowest integer value
SELECT FLOOR(17.9) FROM dual; -- 17
SELECT FLOOR(17.0) FROM dual; -- 17

-- CEIL()
-- =======
-- Syntax: CEIL(number), Returns near greatest integer value
SELECT CEIL(17.1) FROM dual;  -- 18
SELECT CEIL(17.0) FROM dual;  -- 17

Date Function

  • ADD_MONTHS( )
  • MONTHS_BETWEEN( )
  • LAST_DAY( )
  • NEXT_DAY( )
-- Arithmetic Operations 
-- ======================
-- Note: SYSDATE is not a Date Function

SELECT SYSDATE    FROM dual;
SELECT SYSDATE+13 FROM dual;
SELECT SYSDATE-13 FROM dual;
SELECT TO_DATE('22-APR-19') - TO_DATE('01-APR-19') FROM dual; 
SELECT SYSDATE+13 - SYSDATE FROM dual; 

-- ADD_MONTHS()
-- =============
-- Syntax: ADD_MONTHS(date, number_months), Returns Date
SELECT ADD_MONTHS(SYSDATE,  3) FROM dual;
SELECT ADD_MONTHS(SYSDATE, -3) FROM dual;

-- MONTHS_BETWEEN()
-- =================
-- Syntax: MONTHS_BETWEEN(date_1, date_2), Returns Difference 
SELECT ROUND(MONTHS_BETWEEN(SYSDATE, '22-DEC-16')) "MONTHS BETWEEN" FROM dual;
SELECT ROUND(MONTHS_BETWEEN(SYSDATE, '01-DEC-87')/12) "AGE" FROM dual;

-- LAST_DAY()
-- ===========
-- Syntax: LAST_DAY(date), Returns Last Day Of Current Month
SELECT LAST_DAY(SYSDATE)   FROM dual;
SELECT LAST_DAY(SYSDATE-30)FROM dual;
SELECT LAST_DAY(SYSDATE+30)FROM dual;

-- NEXT_DAY()
-- ===========
-- Syntax: NEXT_DAY(date, weekday or weekday in number), Returns the first weekday that is greater than a date.
SELECT NEXT_DAY(SYSDATE, 'FRIDAY') FROM dual;
SELECT NEXT_DAY('02-Aug-03', 'TUESDAY') FROM dual;
SELECT NEXT_DAY('24-DEC-03', 'TUESDAY') FROM dual;
SELECT NEXT_DAY(SYSDATE + (50*7), 'TUESDAY') FROM dual;
SELECT NEXT_DAY(SYSDATE, 4) FROM dual;
SELECT NEXT_DAY(SYSDATE, 7) FROM dual;
SELECT NEXT_DAY(SYSDATE, 8) FROM dual;
--ORA-01846: not a valid day of the week

Type Conversion

  • TO_CHAR( )
  • TO_DATE( )
  • TO_NUMBER( )
-- TO_CHAR()
-- ==========
-- Syntax: TO_CHAR(date, format), Return string
SELECT TO_CHAR(SYSDATE, 'DD') AS "DD" FROM dual;
SELECT TO_CHAR(SYSDATE, 'DDD') AS "DDD" FROM dual;
SELECT TO_CHAR(SYSDATE, 'DDTH MONTH YYYY') AS "DDTH MONTH YYYY" FROM dual;
SELECT TO_CHAR(SYSDATE, 'DDSPTH') AS "DDSPTH" FROM dual;
SELECT TO_CHAR(SYSDATE, 'HH12:MI:SS') AS "12 Hrs" FROM dual;
SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') AS "24 Hrs" FROM dual;
SELECT TO_CHAR(SYSDATE, 'DAY') AS "DAY" FROM dual;
SELECT TO_CHAR(SYSDATE, 'DD/MM/YYYY') AS "DD/MM/YYYY" FROM dual;

-- TO_DATE()
-- ==========
-- Syntax: TO_DATE(string, format), Return date
SELECT TO_DATE('12/04/2019', 'DD/MM/YYYY') FROM dual;
SELECT TO_DATE('04-12-2019', 'MM-DD-YYYY') FROM dual;
SELECT TO_DATE('2019/02/09', 'YYYY/MM/DD') FROM dual;
SELECT TO_DATE('01/04/2019 18:30:25', 'DD-MM-YYYY HH24:MI:SS') FROM dual;

-- TO_NUMBER()
-- ============
-- Syntax: TO_NUMBER(string), Return number.
SELECT TO_NUMBER('10') FROM dual;

General Functions

  • NVL( )
  • NVL2( )
  • NULLIF( )
  • COALESCE( )
-- NVL()
-- ======
SELECT employee_id, commission_pct, NVL(commission_pct, 0) "commission_pct_nvl"
  FROM employees;

-- NVL2()
-- =======
SELECT employee_id,
       commission_pct,
       NVL(commission_pct, 0) "NVL",
       NVL2(commission_pct, 3, 1) "NVL2"
  FROM employees;

-- NVL TO CHAR
-- ============
SELECT employee_id,
       commission_pct,
       NVL(commission_pct, 0) "NVL",
       NVL2(commission_pct, 1, 0) "NVL2",
       NVL(TO_CHAR(commission_pct), 'No commission') "NVL TO_CHAR"
  FROM employees;

-- NULLIF()
-- =========
SELECT NULLIF(1,2)                    FROM dual; -- 1
SELECT NULLIF(12, 12)                 FROM dual; -- NULL
SELECT NULLIF('Sathiyan', 'SATHIYAN') FROM dual; -- Sathiyan
SELECT NULLIF('Hello', 'Hello')       FROM dual; -- NULL

-- COALESCE()
-- ===========
SELECT COALESCE(100, NULL , NULL, 87, NULL ,98)  FROM dual;
SELECT COALESCE(NULL, NULL , NULL, 87, NULL ,98) FROM dual;

Multiple Row Functions

Group Function/ Aggregate Function

  • MIN( )
  • MAX( )
  • SUM( )
  • AVG( )
  • COUNT( )
SELECT COUNT(employee_id) "COUNT",
       MIN(salary) "Minimum",
       MAX(salary) "Maximum",
       SUM(salary) "Salary Sum",
       ROUND(AVG(salary)) "Average"
  FROM employees;

GROUP BY, HAVING

SELECT department_id, 
	   SUM(salary)
  FROM employees
 WHERE department_id  > 50
 GROUP BY department_id
HAVING SUM(salary) > 35000;

Analytical Functions

  • RANK() OVER( ), DENSE_RANK( ) OVER( )
  • AVG( ) OVER( )
  • LEAD( ), LAG( )
-- RANK() OVER( ), DENSE_RANK( ) OVER( )
-- ======================================
SELECT employee_id,
       first_name,
       salary,
       RANK() OVER(ORDER BY salary DESC) "RANK",
       DENSE_RANK() OVER(ORDER BY salary DESC) "D_RANK"
  FROM emps;

-- AVG( ) OVER( )
-- ===============
SELECT employee_id,
       first_name,
       salary,
       AVG(salary) OVER(ORDER BY salary DESC) "AVG"
  FROM emps;

-- LEAD( ) OVER( )
-- ================
-- Syntax: LEAD ( value_expr [, offset] [, default] ) OVER ( [query_partition_clause] order_by_clause )
SELECT first_name,
       hire_date,
       LEAD(hire_date, 1) OVER(ORDER BY hire_date) AS Next_Hired
  FROM emps;


-- LAG( ) OVER( )
-- ===============
-- Syntax: LAG ( value_expr [, offset] [, default] ) OVER ( [query_partition_clause] order_by_clause )
SELECT first_name,
       hire_date,
       salary,
       LAG(salary, 1, 0) OVER(ORDER BY hire_date) AS Prev_Salary
  FROM emps;

Reference

Oracle – CASE & DECODE

DECODE and CASE both provides IF-THEN-ELSE functionality in Oracle SQL. Decode Function and Case Statement is used to transform data values at retrieval time.
Everything DECODE can do, CASE can. There is a lot else CASE can do though, which DECODE cannot. We’ll go through some examples in this post.

DECODECASE
FunctionExpression
Performs an equality check onlyLogical comparisons
SQL onlyBoth PL/SQL and SQL
Works with scalar value aloneWorks with sub-queries also
NULLNULL IS NULL
Does not expects datatype consistency Expects datatype consistency
Oracle StandardANSI SQL Standard

Decode

Syntax

DECODE(VALUE, search_1, result_1 
           [, search_2, result_2]
           [, search_N, result_N]
           [, default]
           )

Example 1

SELECT department_id, department_name,
DECODE(department_id, 10, 'Admin'
                    , 20, 'Marketing'
                    , 30, 'Purchasing'
                    , 40, 'HR'
                    , 50, 'Logistics'
                    ,     'Others') decoded_name
  FROM departments;

Example 2 – Using Decode In Update

UPDATE table_x SET gender =  DECODE(gender, 'M', 'F', 'M') WHERE gender IS NOT NULL;

Example 3 – Order By Using a List of Values

-- Create Table
-- =============
CREATE TABLE emp_job_map(
    emp_name VARCHAR(64),
    job_name VARCHAR(64)
);

-- Inserting Values
-- =================
INSERT INTO emp_job_map VALUES('King','Engineer');
INSERT INTO emp_job_map VALUES('Bishop','Policeman');
INSERT INTO emp_job_map VALUES('Annamalai','Lawyer');
INSERT INTO emp_job_map VALUES('Louisa','Doctor');
INSERT INTO emp_job_map VALUES('Krishna','Engineer');
INSERT INTO emp_job_map VALUES('Jack','Doctor');
INSERT INTO emp_job_map VALUES('Daniel','Nurse');

-- Order By Using a List of Values
-- ================================
SELECT emp_name, job_name
  FROM emp_job_map
 ORDER BY DECODE(job_name,
                'Policeman',1,
                'Lawyer',2) ASC, 
                job_name ASC;

Case

Syntax

CASE expression WHEN this1 THEN that1
                WHEN this2 THEN that2
                [ ELSE that]
 END

Example 1

SELECT employee_id, first_name, 
 (CASE department_id
 WHEN 10 THEN 'Admin'
 WHEN 20 THEN 'Marketing'
 WHEN 30 THEN 'Purchasing'
 WHEN 40 THEN 'HR'
 WHEN 50 THEN 'Logistics'
 ELSE 'Unknown'
  END) department_case_name
FROM employees
ORDER BY department_case_name;

Example 2 – Searchable Sub-Query

SELECT CASE
       -- Predicate with "IN"
       WHEN salary IN (9000,10000) THEN '9K-10K'
       -- Searchable Subquery
       WHEN employee_id IN (SELECT manager_id FROM departments) THEN 'Dept_Mgr'
       ELSE 'Unknown'
       END case_category
  FROM employees;

Example 3 – Logical Operations

SELECT employee_id, salary,
      (CASE
       WHEN salary < 3000 THEN 'Low'
       WHEN salary BETWEEN 3000 AND 6000 THEN 'Medium'
       WHEN salary > 6000 THEN 'High'
       ELSE 'N/A'
        END) case_salary
FROM employees
ORDER BY employee_id;

Reference

Oracle – Synonyms

A synonym is an alias for a database object. The object can be a table, view, synonym, sequence, PL/SQL stored procedure, PL/SQL function, PL/SQL package, materialized view.

Synonyms can be created as PRIVATE (by default) or PUBLIC.

  • Private – It exist only in specific user schema.
  • Public – Available to all users in the database.

The name of the object for which you are creating the synonym. It can be one of the following:

  • Table
  • View
  • Sequence
  • Stored Procedure
  • Function
  • Package
  • Materialized View
  • User-defined Object
  • Synonym

You can refer to synonyms in the following statements:

  • Select
  • Insert
  • Update
  • Delete
  • Explain Plan
  • Lock Table

Syntax

Create Synonym (Replace for Altering Synonym)

-- General
-- ========
CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema .] synonym_name FOR [schema .] object_name [@ dblink];

-- Private
-- ========
CREATE OR REPLACE SYNONYM synonym_name FOR object_name;

-- Public
-- =======
CREATE OR REPLACE PUBLIC SYNONYM synonym_name FOR object_name;

-- Public Access On Object For Public Synonym
-- ===========================================
GRANT SELECT ON object_name TO PUBLIC;

Drop Synonym

-- Private
-- ========
DROP SYNONYM synonym_name;

-- Public
-- ========
DROP PUBLIC SYNONYM synonym_name;

Data Dictionary Table

-- Synonyms Owned 
-- ===============
SELECT * FROM USER_SYNONYMS;
SELECT * FROM USER_SYNONYMS WHERE table_name= UPPER('synonym_name');

-- Synonyms Accessible
-- ====================
SELECT * FROM ALL_SYNONYMS;
SELECT * FROM ALL_SYNONYMS WHERE table_name= UPPER('synonym_name');

-- All Synonyms
-- =============
SELECT * FROM DBA_SYNONYMS;
SELECT * FROM DBA_SYNONYMS WHERE table_name= UPPER('synonym_name');

Examples

Prerequisite

DROP TABLE super_heroes_marvel_dc;
CREATE TABLE super_heroes_marvel_dc AS (SELECT employee_id AS id, first_name AS name, salary FROM employees WHERE 1=2);

INSERT INTO emps VALUES(1 , 'Stan Lee'   , 50000);
INSERT INTO emps VALUES(2 , 'Thor'       , 45000);
INSERT INTO emps VALUES(3 , 'Jane Foster', 40000);
INSERT INTO emps VALUES(4 , 'Mary Jane'  , 35000);
INSERT INTO emps VALUES(5 , 'Spider man' , 30000);
INSERT INTO emps VALUES(6 , 'Stark'      , 25000);
INSERT INTO emps VALUES(7 , 'Pepper'     , 20000);
INSERT INTO emps VALUES(8 , 'Oliver'     , 15000);
INSERT INTO emps VALUES(9 , 'Overwatch'  , 10000);
INSERT INTO emps VALUES(10, 'Ethan Hunt' , 5000);

Example 1 – Public

-- Select From Table
-- ==================
SELECT * FROM hr.super_heroes_marvel_dc;

-- Grant Permission to Public
-- ===========================
GRANT SELECT ON hr.super_heroes_marvel_dc TO PUBLIC;

-- Creating Synonym
-- =================
CREATE OR REPLACE PUBLIC SYNONYM heroes FOR hr.super_heroes_marvel_dc;

-- Select From Synonym
-- ====================
SELECT * FROM heroes;

Example 2 – Synonyms for Synonyms

-- Synonym
-- ========
CREATE OR REPLACE SYNONYM heroes_sal FOR super_heroes_marvel_dc;

-- Synonyms for Synonyms
-- ======================
CREATE OR REPLACE SYNONYM heroes FOR heroes_sal;

-- Select From Synonyms for Synonyms
-- ==================================
SELECT * FROM heroes;

Example 3 – DB Link Synonym Usage

-- Creating DB Link
-- =================
CREATE DATABASE LINK my_db_link CONNECT TO user IDENTIFIED BY passwd USING 'alias';

-- Creating Synonym For DB Link Table
-- ===================================
CREATE SYNONYM my_table FOR remote_table@my_db_link;

-- Select From DB Link Table Synonym
-- ==================================
SELECT * FROM my_table;

Reference

Oracle – Indexes

An index is an object in the database that allows you to find specific data in a table faster. You don’t need to ask Oracle to use an index if it’s already there. Most of the time, Oracle will work out that it needs to use it.

Index Types

  • Unique Index
    • Primary Key
    • Unique Key
  • B-Tree
  • Bitmap
  • Function-Based

Unique

Oracle create unique index for Primary key and unique key constraints.

B-Tree Indexes vs Bitmap Indexes

B-Tree IndexBitmap Index
Good for high-cardinality dataGood for low-cardinality data
Good for OLTP databases (lots of updating)Good for data warehousing applications
Use a large amount of spaceUse relatively little space
Easy to updateDifficult to update

B-Tree Indexes

The B*Tree index has several sub types. Some of them are,

  • Index Organized Tables – A table stored in a B*Tree structure
  • Reverse Key Indexes – The bytes in the key are reversed. This is used to stop sequence keys being on the same block like 999001, 999002, 999003 would be reversed to 100999, 200999, 300999 thus these would be located on different blocks.

There are only a few things you need to specify to create a b-tree index:

  • index_name: The name of the new index you’re creating, which must be a unique name.
  • table_name: The name of the table you want to create the index for.
  • columns: A single column name, or a comma-separated list of the columns you want to create the index on.
-- Syntax
-- =======
CREATE INDEX index_name
ON table_name (columns);

-- Example
-- ========
CREATE INDEX emp_id_idx
ON emps(employee_id);

-- Syntax - Reverse Index 
-- =======================
CREATE INDEX index_name
ON table_name (column_name)
REVERSE;

-- Example - Reverse Index 
-- ========================
CREATE INDEX emp_fn_ln_ridx 
ON emps (firstname, lastname) 
REVERSE;

-- Syntax - Composite Index
-- =========================
CREATE INDEX index_name
ON table_name (col1, col2);

-- Example - Composite Index
-- ==========================
CREATE INDEX emp_fn_ln_cidx 
ON emps (firstname, lastname);

-- Syntax - Index Organized Tables
-- ================================
CREATE TABLE table_name (id INTEGER PRIMARY KEY, value VARCHAR2(50)) 
SEGMENT CREATION IMMEDIATE -- This Line Must from version 11g. 
ORGANIZATION INDEX;

Bitmap Indexes

With a bitmap index , a single index entry uses a bitmap to point to many rows simultaneously, they are used with low data that is mostly read-only. They should not be used with OLTP systems.

-- Syntax
-- =======
CREATE BITMAP INDEX index_name
ON table_name (columns);

-- Example
-- ========
CREATE BITMAP INDEX emp_id_bm_idx
ON emps(employee_id);

Function Based Indexes

These are B*Tree or bitmap indexes that store the computed result of a function on a row(s) – not the column data itself.

-- Syntax
-- =======
CREATE INDEX index_name
ON table_name(function(column_name));

-- Example
-- ========
CREATE INDEX emp_sal_fn_idx
ON employee(salary * 12);

Alter – Rename, Rebuild Indexes

-- Rename
-- =======

ALTER INDEX emp_sal_fn_idx
RENAME TO employee_sal_fn_idx;

-- Rebuild
-- ========
ALTER INDEX employee_sal_fn_idx
REBUILD COMPUTE STATISTICS;

Disable, Drop Indexes

-- Disable
-- ========
ALTER INDEX index_name ON table_name DISABLE;

-- Rebuild (Enable)
-- =================
ALTER TABLE index_name ON table_name REBUILD;

-- Drop
-- =====
DROP INDEX index_name;

Cardinality vs Selectivity

Cardinality and selectivity are two terms that are often used in the SQL world when working with indexes.

  • Cardinality is the number of distinct values in a particular column.
  • Selectivity is the ratio of cardinality on an indexed column to the total number of rows in a table.

Selectivity = Cardinality/Total_Rows. A selectivity value of a query closer to 1 is said to have high selectivity, and closer to 0 is called low selectivity. If a query has a high selectivity, then it is normally fast to select using an index. If a query has low selectivity, it is often not worth the time to use the index in the query, and Oracle may not use it.

References

Oracle – Sequence

In Oracle, you can create an auto-number field by using sequences. A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.

Syntax

Create Sequence

CREATE SEQUENCE sequence_name
START WITH initial_value
INCREMENT BY increment_value
MINVALUE minimum value
MAXVALUE maximum value
CYCLE | NOCYCLE
CACHE value | NOCACHE
ORDER value | NOORDER;
  • sequence_name: Name of the sequence.
  • initial_value: starting value from where the sequence starts. Initial_value should be greater than or equal to minimum value and less than equal to maximum value.
  • increment_value: Value by which sequence will increment itself. Increment_value can be positive or negative.
  • minimum_value: Minimum value of the sequence.
  • maximum_value: Maximum value of the sequence.
  • cycle: When sequence reaches its set_limit it starts from beginning.
  • nocycle: An exception will be thrown if sequence exceeds its max_value.
  • cache: This clause directs Oracle server to cache ’n’ values of a sequence. Its value is 20 by default.
  • nocache: It is the default behavior of the sequence.
  • order: It ensures that sequence values are generated in order. Generally, it is least usable clause in sequence definition but they are helpful in applications which use sequence as timestamp value.
  • noorder: It is the default behavior of the sequence.

Alter Sequence

ALTER SEQUENCE sequence_name -- You Can Alter any (one) of the following.. Or all...
INCREMENT BY n
MAXVALUE n
MINVALUE n
CYCLE | NOCYCLE
CACHE n | NOCACHE
ORDER n | NOORDER;

Drop Sequence

DROP SEQUENCE sequence_name;

Sequence Usage

-- Next Value (Sequence incremented to next value)
-- ===============================================
-- sequence_name.NEXTVAL
SELECT sequence_name.NEXTVAL FROM dual;

-- Current Value (Showing current incremented value of sequence)
-- =============================================================
-- sequence_name.CURRVAL
SELECT sequence_name.CURRVAL FROM dual;

Sequence Table

SELECT * FROM USER_SEQUENCES WHERE SEQUENCE_NAME = UPPER('sequence_name');

SELECT * FROM ALL_SEQUENCES WHERE SEQUENCE_NAME = UPPER('sequence_name');

Examples

Prerequisite

DROP TABLE emps;
CREATE TABLE emps AS (SELECT employee_id AS id, first_name AS name, salary FROM employees WHERE 1=2);

Example 1

-- Sequence Creation
-- ==================
CREATE SEQUENCE emps_id_seq
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9
NOCYCLE;

-- Inserting Values
-- =================
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Stan Lee'   , 50000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Thor'       , 45000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Jane Foster', 40000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Mary Jane'  , 35000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Spider man' , 30000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Stark'      , 25000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Pepper'     , 20000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Oliver'     , 15000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Overwatch'  , 10000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Ethan Hunt' , 5000);
-- ORA-08004: sequence EMPS_ID_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated

-- Checking 
-- =========
SELECT * FROM emps;
SELECT emps_id_seq.CURRVAL FROM dual;

-- Altering Max Value
-- ===================
ALTER SEQUENCE emps_id_seq
MAXVALUE 10;

-- Inserting Again
-- ================
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Ethan Hunt' , 5000);

-- Dropping Sequence & Clearing Table
-- ===================================
DROP SEQUENCE emps_id_seq;
TRUNCATE TABLE emps;

Example 2

-- Sequence Creation
-- ==================
CREATE SEQUENCE emps_id_seq
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 21 -- This Value should be greater than 20 to use cycle.
CYCLE;

-- Inserting Values
-- =================
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Stan Lee'   , 50000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Thor'       , 45000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Jane Foster', 40000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Mary Jane'  , 35000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Spidarman'  , 30000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Stark'      , 25000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Pepper'     , 20000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Oliver'     , 15000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Captain America', 10001);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Captain Marvel' , 10002);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Wonder Women'   , 10003);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Flash'          , 10004);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Batman'         , 10005);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Deadpool'       , 10006);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Superman'       , 10007);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Ant Man'        , 10008);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Daredevil'      , 10009);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Electra'        , 10010);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Watchmen'       , 10011);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Catwomen'       , 10012);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Vibe'           , 10013);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'killer Frost'   , 5000); -- Starting Again From 1
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Ethan Hunt'     , 5000);

-- Checking 
-- =========
SELECT * FROM emps;
SELECT emps_id_seq.CURRVAL FROM dual;

-- Dropping Sequence & Clearing Table
-- ===================================
DROP SEQUENCE emps_id_seq;
TRUNCATE TABLE emps;
DROP TABLE emps;

Example 3 – Primary Key Auto Increment (Up to 11g)

-- Creating Table
-- ===============
CREATE TABLE invoices(
  id NUMBER PRIMARY KEY,
  inv_name VARCHAR2(25)
);

-- Creating Sequence
-- ==================
CREATE SEQUENCE invoices_id_seq
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 999999
NOCYCLE;

-- Creating Trigger
-- =================
CREATE OR REPLACE TRIGGER trg_invoices_id
  BEFORE INSERT ON invoices
  FOR EACH ROW
BEGIN
  SELECT invoices_id_seq.NEXTVAL
    INTO :NEW.id
    FROM dual;
END;

-- Inserting Without Primary Key
-- ==============================
INSERT INTO invoices(inv_name) VALUES ('INV/02042019/000001');
INSERT INTO invoices(inv_name) VALUES ('INV/02042019/000002');

-- Checking 
-- =========
SELECT * FROM invoices;
SELECT invoices_id_seq.CURRVAL FROM dual;

-- Clearing Table & Dropping Sequence
-- ===================================
TRUNCATE TABLE invoices;
DROP SEQUENCE invoices_id_seq;
DROP TABLE invoices;

Example 4 – Unique, Sequential Code

  • Making “inv_name” auto-generated.
    • Eg: “INV/01042019/000001”
  • Making primary key(id) also auto-generated.
-- Creating Table
-- ===============
CREATE TABLE invoices(
  id NUMBER PRIMARY KEY,
  inv_name VARCHAR2(25)
);

-- Creating Sequence
-- ==================
CREATE SEQUENCE invoices_id_seq
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 999999
NOCYCLE;

-- Creating Function
-- ==================
CREATE OR REPLACE FUNCTION next_inv_name_fn 
  RETURN VARCHAR2
  AS
  BEGIN
  RETURN('INV/' || TO_CHAR(SYSDATE, 'DDMMYYYY') || '/' || LPAD(invoices_id_seq.NEXTVAL, 6, 0));
  END;

-- Creating Trigger
-- =================
CREATE OR REPLACE TRIGGER trg_invoices_id
  BEFORE INSERT ON invoices
  FOR EACH ROW
BEGIN
  SELECT invoices_id_seq.CURRVAL
    INTO :NEW.id
    FROM dual;
END;

-- Inserting Values
-- =================
INSERT INTO invoices(inv_name) VALUES (next_inv_name_fn);
INSERT INTO invoices(inv_name) VALUES (next_inv_name_fn);
INSERT INTO invoices(inv_name) VALUES (next_inv_name_fn);

-- Checking
-- =========
SELECT * FROM invoices;
SELECT invoices_id_seq.CURRVAL FROM dual;

-- Clearing Table & Dropping Sequence
-- ===================================
TRUNCATE TABLE invoices;
DROP SEQUENCE invoices_id_seq;
DROP TABLE invoices;

References

MySQL – Connect DB with Python mysql.connector

MySQL Connector/Python enables Python programs to access MySQL databases, using an API that is compliant with the Python Database API Specification v2.0 (PEP 249). It is written in pure Python and does not have any dependencies except for the Python Standard Library.

Syntax

>>> import mysql.connector
>>> connection = mysql.connector.connect(host="localhost", user="myusername", password="mypassword", database="mydatabase")

Example

>>> import mysql.connector
>>> 
>>> connection = mysql.connector.connect(host="localhost", user="sathiyan", password="mysql", database="mysql_db")
>>> cursor = connection.cursor()
>>> query = ("SELECT name FROM res_partner")
>>> query_execute = cursor.execute(query)
>>> rows = cursor.fetchall()
>>> for row in rows:
...     print(row[0])
...
"ABC Pvt. Ltd."
"A-Z Pvt. Ltd."
"Y! Ltd."
>>> cursor.close()
>>> connection.close()

Reference

PostgreSQL – Connect DB with Python psycopg2

Psycopg is the most popular PostgreSQL database adapter for the Python programming language. Many Python types are supported out-of-the-box and adapted to matching PostgreSQL data types; adaptation can be extended and customized thanks to a flexible objects adaptation system.

Syntax

>>> import psycopg2
>>> connection = psycopg2.connect(host="hostname_or_ip",database="db_name", user="user_name", password="password")

Example

>>> import psycopg2
>>>
>>> connection = psycopg2.connect(host="localhost",database="odoo_12", user="sathiyan", password="postgres")
>>> cursor = connection.cursor()
>>> query = ("SELECT name FROM res_partner")
>>> query_execute = cursor.execute(query)
>>> rows = cursor.fetchall()
>>> for row in rows:
...     print(row[0])
... 

"ABC Pvt. Ltd."
"A-Z Pvt. Ltd."
"Y! Ltd."
>>> cursor.close()
>>> connection.close()

Reference

Linux – Bash Shell Scripting Examples

Bash scripting with some examples that can be used in real time scenarios. Here we go…

Creating Directory By Checking Existence

#!/bin/bash

echo "Enter New Directory Name: "
read new_dir
if [ -d "$new_dir" ]
then
echo "$new_dir Directory Exist"
else
mkdir $new_dir
echo "$new_dir Directory Created"
fi

Deleting Directory By Checking Existence

#!/bin/bash

echo "Enter Directory Name To Be Deleted: "
read existing_dir
if [ -d "$existing_dir" ]
then
rm -rf $existing_dir
echo "$existing_dir Directory Exist - All Of It's Contents Deleted"
else
echo "$existing_dir Directory Does Not Exist"
fi

Reading a File By Checking Existence

#!/bin/bash

echo "Enter File Name: "
read filename
if [ -f "$filename" ]; 
then
echo "File Exists - Reading a File"
nano "$filename"
else
echo "File Does Not Exist"
fi

Deleting a File By Checking Existence

#!/bin/bash

echo "Enter File Name To Be Deleted: "
read filename
if [ -f "$filename" ]; 
then
echo "File Exists - Deleting File"
rm -i $filename
else
echo "File Does Not Exist"
fi

Append to File By Checking Existence

#!/bin/bash

echo "Enter File Name To Be Appended: "
read filename
if [ -f "$filename" ]; 
then
printf "\n"
echo "Before Appending The File"
cat $filename
printf "\n"
echo "File Exists - Reading Data To Be Appended: "
read appending_string
echo $appending_string >> $filename
printf "\n"
echo "After Appending The Data To The File"
cat $filename
else
echo "File Does Not Exist"
fi

Parsing Date and Time

#!/bin/bash

year=$(date +"%Y")
echo "Year Is "$year
echo "12 Hr Clock "$(date +"%r")
echo "24 Hr Clock "$(date)
echo "Current Date is: "$(date +"%d-%m-%Y")
echo "Current Time is: "$(date +"%H:%M:%S")
printf "\n"
echo "File Name Parsing With Date and Time"
now=$(date +"%d_%m_%Y_%H_%M_%S")
file_name="diskusage_$now.txt"
echo $file_name

Checking Disk Space

#!/bin/bash

# set alert level 90% is default
alert=90
# Exclude list of unwanted monitoring, if several partitions then use "|" to separate the partitions.
# An example: exclude_list="/dev/hdd1|/dev/hdc5"
exclude_list="/dev/loop*"
# ================================================================

function main_prog() {
while read output;
do
  used_percent=$(echo $output | awk '{ print $2}' | cut -d'%' -f1)
  partition=$(echo $output | awk '{print $1}')
  if [ "$used_percent" -ge "$alert" ]; 
  then
     echo "Partition "$partition " running out of space, using" $used_percent%" on server $(hostname), $(date)" 
  fi
done
}
 
if [ "$exclude_list" != "" ]; 
then
  df -H | grep -vE "^Filesystem|tmpfs|cdrom|${exclude_list}" | awk '{print $1 " " $5}' | main_prog
else
  df -H | grep -vE "^Filesystem|tmpfs|cdrom" | awk '{print $5 " " $6}' | main_prog
fi

Checking Server Utilization

#!/bin/bash

# Modified Existing Code
# Credits - https://www.tecmint.com/basic-shell-programming-part-ii/
now=$(date +"%d_%m_%Y_%H_%M_%S")
file_name="server_utilization_$now.txt"
exec 1> /home/sathiyan/Templates/$file_name
date;
echo "Up Time:"
uptime
echo "Currently Connected:"
w
echo "--------------------"
echo "Last Logins:"
last -a |head -3
echo "--------------------"
echo "Disk and Memory Usage:"
df -h | xargs | awk '{print "Free/total disk: " $11 " / " $9}'
free -m | xargs | awk '{print "Free/total memory: " $17 " / " $8 " MB"}'
echo "--------------------"
start_log=$(head -1 /var/log/messages |cut -c 1-12)
oom=$(grep -ci kill /var/log/messages)
echo -n "OOM Errors Since $start_log :" $oom
echo ""
echo "--------------------"
echo "Utilization and Most Expensive Processes:"
top -b |head -3
echo
top -b |head -10 |tail -4
echo "--------------------"
echo "Open TCP Ports:"
nmap -p- -T4 127.0.0.1
echo "--------------------"
echo "Current Connections:"
ss -s
echo "--------------------"
echo "Processes:"
ps auxf --width=200
echo "--------------------"
echo "vmstat:"
vmstat 1 5

Reference