SQL Tuning Tip

While creating procedure, Function, Package or anonymous block, sometimes we need to use LOOP based on some conditions. While using that condition we can apply some tactics to make the condition better and improve the performance. Let's look at the below example:-

Say, I have to create a loop based on some condition below:-

I have to see that is there any records in the table based on condition, If yes then go to the LOOP otherwise don't do anything. Code can be below:-

DECLARE

VAR_A NUMBER;

BEGIN

SELECT COUNT(1) INTO VAR_A

FROM EMPLOYEE;

IF VAR_A >0 THEN

LOOP

----

----DO SOMETHING

----

END LOOP;

END IF;

END;

In above code there is no problem with logic as we need to perform some task if my variable is having count more than 0. But it can be improved by using ROWNUM column in where condition as shown below:-

DECLARE

VAR_A NUMBER;

BEGIN

SELECT COUNT(1) INTO VAR_A
FROM EMPLOYEE
WHERE ROWNUM<=2;

IF VAR_A >0 THEN

LOOP
----
----DO SOMETHING
----

END LOOP;

END IF;

END;

Above code will also do the same thing what the earlier code could have done but while counting it can take little time than first code.

Find out version in Greenplum

We can find out version of the Greenplum database using below query:-

gpstate -i

Here are 5 frequently asked questions (FAQs) about finding out the version in Greenplum:-

1. How can I check the version of Greenplum Database?

   - You can check the version of Greenplum Database by connecting to the database and running a SQL query to retrieve the version information. Here's an example query:

     

     SELECT version();

     

     This query will return the version information of the Greenplum Database.

2. Is there a command-line tool to check the Greenplum version?

   - Yes, you can use the `gpversion` command-line utility to check the version of Greenplum. Simply run the following command in your terminal:

     bash

     gpversion

     

     This command will display the version information of the Greenplum Database installed on your system.

3. Can I check the Greenplum version from the Greenplum Command Center (GPCC)?

   - Yes, if you have Greenplum Command Center (GPCC) installed and configured, you can view the Greenplum version from the GPCC web interface. The version information is typically available on the dashboard or in the system information section.

4. How do I interpret the version information returned by Greenplum?

   - The version information returned by Greenplum typically includes the major version number, minor version number, and any additional release or build information. For example, a version string might look like "Greenplum Database 6.0.0 build version".

5. Is it important to know the Greenplum version?

   - Yes, knowing the Greenplum version is important for various reasons, including compatibility with client tools and applications, understanding feature availability and limitations, and planning for upgrades or patches. Additionally, different versions may have different performance characteristics or bug fixes that could affect your deployment.

These FAQs should help you understand how to find out the version of Greenplum Database and why version information is important for managing and maintaining your Greenplum environment.

Find out version in Teradata

We can find out version of the teradata by using below query:-

SELECT * FROM DBC.DBCINFO;




Read Also:-  Find out version Greenplum

Find out version in Oracle

We can find out version of the Oracle database by using below query:-

SELECT BANNER FROM V$VERSION;

SELECT * FROM V$VERSION;

Here are 5 frequently asked questions (FAQs) about finding out the version in Oracle:-

1. How do I check the Oracle Database version?

   - You can check the Oracle Database version by querying the `v$version` or `v$instance` data dictionary views. These views provide information about the database version, edition, and other details. For example:

     

     SELECT * FROM v$version;

     

     This query will return the version information for various components of the Oracle Database.

2. Can I determine the Oracle Database version without connecting to the database?

   - No, you cannot determine the Oracle Database version without connecting to the database. Version information is stored within the database and can only be accessed through a database connection.

3. What does the Oracle Database version number consist of?

   - The Oracle Database version number typically consists of several components, including the major version number, release number, and patch set update (PSU) number. For example, Oracle Database 19c has a major version number of 19, a release number of 1, and may have a PSU number indicating a specific patch set update.

4. How often should I check the Oracle Database version?

   - It's a good practice to check the Oracle Database version periodically, especially when planning upgrades, applying patches, or troubleshooting compatibility issues with applications or third-party tools. Additionally, version information can help ensure that your database is supported by Oracle and receives necessary updates and security patches.

5. Can I determine the Oracle Database version from SQL*Plus or SQL Developer?

   - Yes, you can determine the Oracle Database version from SQL*Plus or SQL Developer by executing the same SQL query against the `v$version` or `v$instance` views. Both SQL*Plus and SQL Developer provide a SQL interface to interact with the database and execute queries.

These FAQs should provide a comprehensive understanding of how to find out the version in Oracle Database and why version information is important for managing and maintaining databases.

Find out version in MySql

We can find out version of the MySql database by using below query:-

mysql> SHOW VARIABLES LIKE "%version%";





Read Also:-  Find out version in MariaDB

Find out version in SQL

We can find out version of the SQL database by using below query:-

SELECT @@VERSION




Read Also:-  Find out version in Oracle

single-row subquery returns more than one row in Oracle

ORA-01427: single-row subquery returns more than one row error occures when we are comparing two or more values with one using equals to (=) sign in subquery.

We can avoid this error using IN clause shown below:-

CREATE TABLE P_TEST
(
 EMP_NO   NUMBER,
 EMP_NAME  VARCHAR2(10),
 DEPTNO   NUMBER
);

CREATE TABLE P_TEST_2
(
 DEPTNO   NUMBER,
 DEPTNAME  VARCHAR2(10)
);

INSERT INTO P_TEST
VALUES (1,'CHANCHAL',10);

INSERT INTO P_TEST
VALUES (2,'WANKHADE',20);

INSERT INTO P_TEST
VALUES (3,'SCOTT',30);

INSERT INTO P_TEST_2
VALUES (10,'FIANANCE');

INSERT INTO P_TEST_2
VALUES (20,'HR');

INSERT INTO P_TEST_2
VALUES (30,'IT');

SQL>
SQL> SELECT DEPTNO FROM P_TEST
  2  WHERE DEPTNO =
  3  (SELECT DEPTNO FROM P_TEST_2);
(SELECT DEPTNO FROM P_TEST_2)
 *
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row

Above sql statement is not working. Let's try to use below on with IN clause:-

SQL>
SQL>
SQL> SELECT * FROM P_TEST
  2  WHERE DEPTNO IN
  3  (SELECT DEPTNO FROM P_TEST_2);
    EMP_NO EMP_NAME       DEPTNO
---------- ---------- ----------
         1 CHANCHAL           10
         2 WANKHADE           20
         3 SCOTT              30
3 rows selected.

Executing Windows command from Oracle Script

We can execute windows command from Oracle script. To achieve this we have a command called HOST which execute the command on host and again return to the Oracle prompt.

Let's take a scenario, you have to delete file automatically from windows directory while you are running the Oracle script and you don't want manual intervention. You can use HOST command to do so.

Say, you have 5 procedure that you needs to execute, but after executing 3 procedure you need to DELETE the file from the windows directory, to do this you can perform steps shown below:-

SQL> EXEC PROCEDURE_1;
      procedure executed successfully.
SQL> EXEC PROCEDURE_2;
      procedure executed successfully.
SQL> EXEC PROCEDURE_3;
      procedure executed successfully.
SQL> HOST 'DEL D:/CHANCHAL/TEST.CSV'
SQL> EXEC PROCEDURE_4;
      procedure executed successfully.
SQL> EXEC PROCEDURE_5;
      procedure executed successfully.


Like shown above, you can perform many other windows operations like creating windows directory, file , rename file and many more.....

COALESCE function in MariaDB

COALESCE function in MariaDB return default value if there is NULL value in the second expression.

Syntax:-

COALESCE (DEFAULT_VALUE,'EXPRESSION,'')

Example:-

SELECT * fROM EMPLOYEE;

     EMPNO EMPNAME                  SALARY     DEPTNO
---------- -------------------- ---------- ----------
         1 CHANCHAL                   1000         10
         2 WANKHADE                   2000         20
         3 SCOTT                      3000        200
         4 TIGER                      4000        200
         5 JOHN                       5000        300
         6 JAKE                       6000        300
         7 null      null       null


SELECT COALESCE(EMPNAME,'CHANCHAL') AS EMPNAME FROM EMPLOYEE WHERE EMPNO=7;

--> CHANCHAL

SELECT COALESCE(NULL,'CHANCHAL') AS EMPNAME FROM EMPLOYEE WHERE EMPNO=7;

--> CHANCHAL

SELECT COALESCE(EMPNO,100) FROM EMPLOYEE WHERE EMPNO=7;

--> 7




Read Also:-  IFNULL function in MySql

COALESCE function in Greenplum

COALESCE function in Greenplum return default value if there is NULL value in the second expression.

Syntax:-

COALESCE (DEFAULT_VALUE,'EXPRESSION,'')

Example:-

SELECT * fROM EMPLOYEE;

     EMPNO EMPNAME                  SALARY     DEPTNO
---------- -------------------- ---------- ----------
         1 CHANCHAL                   1000         10
         2 WANKHADE                   2000         20
         3 SCOTT                      3000        200
         4 TIGER                      4000        200
         5 JOHN                       5000        300
         6 JAKE                       6000        300
         7 null      null       null


SELECT COALESCE(EMPNO,EMPNAME,'') AS EMPNAME FROM EMPLOYEE;;

--> 7

In above example, if EMPNAME is null then it will show EMPNO values.

Here are 5 frequently asked questions (FAQs) about the COALESCE function in Greenplum, along with their answers:-

1. What is the COALESCE function in Greenplum?

   - The COALESCE function in Greenplum is used to return the first non-null expression in a list of expressions. It evaluates the arguments in order and returns the value of the first expression that is not null. If all expressions are null, COALESCE returns null.

2. How do I use the COALESCE function in Greenplum?

   - To use the COALESCE function, simply provide a list of expressions separated by commas as arguments. For example:

     

     SELECT COALESCE(column1, column2, 'default') AS result FROM table_name;

     

     This query will return the first non-null value from column1, column2, or the string 'default'.

3. Can I use COALESCE with aggregate functions in Greenplum?

   - Yes, you can use COALESCE with aggregate functions in Greenplum. It's often used in conjunction with aggregate functions to handle null values gracefully. For example:

     

     SELECT SUM(COALESCE(column1, 0)) AS total FROM table_name;

     

     This query will return the sum of column1, replacing any null values with 0.

4. What happens if all expressions in COALESCE are null?

   - If all expressions provided to the COALESCE function are null, it will return null. However, you can provide a default value as the last argument to COALESCE, which will be returned if all expressions evaluate to null.

5. Are there any performance considerations when using COALESCE in Greenplum?

   - While COALESCE is a convenient function for handling null values, excessive use of it, especially in complex queries or with large datasets, can impact performance. It's important to use COALESCE judiciously and optimize queries where possible to ensure optimal performance.

These FAQs should provide a good understanding of the COALESCE function in Greenplum and how it can be used effectively in SQL queries.

COALESCE function in Teradata

COALESCE function in Teradata return default value if there is NULL value in the first expression.

Syntax:-

COALESCE(EXPRESSION,'DEFAULT_VALUE);

Example:-

SELECT * fROM EMPLOYEE;

     EMPNO EMPNAME                  SALARY     DEPTNO
---------- -------------------- ---------- ----------
         1 CHANCHAL                   1000         10
         2 WANKHADE                   2000         20
         3 SCOTT                      3000        200
         4 TIGER                      4000        200
         5 JOHN                       5000        300
         6 JAKE                       6000        300
         7 null      null       null

SELECT COALESCE(EMPNAME,'CHANCHAL') as EMPNAME;

EMPNAME
-------------------
CHANCHAL


You can have many default values. Teradata will check if first expression is null then it will show second expression if it is not null, if second
is also null then it will show third expression likewise.

SELECT EMPNO, COALESCE (EMPNAME,NULL,'CHANCHAL') FROM  EMPLOYEE;

-->  CHANCHAL

SELECT EMPNO, COALESCE (EMPNAME,NULL,NULL,'WANKHADE') FROM  EMPLOYEE;

-->  WANKHADE




Read Also:-  COALESCE function in Greenplum

NVL function in Oracle

NVL function in Oracle return default value if there is NULL value in the first expression.

Syntax:-

NVL(EXPRESSION,'DEFAULT_VALUE);

Example:-

SELECT EMPNO,NVL(EMPNAME,'CHANCHAL') AS EMPNAME,NVL(SALARY,9000) AS SALARY
FROM EMPLOYEE
WHERE EMPNO=7;

SQL> SELECT * FROM EMPLOYEE;

     EMPNO EMPNAME                  SALARY     DEPTNO
---------- -------------------- ---------- ----------
         1 CHANCHAL                   1000         10
         2 WANKHADE                   2000         20
         3 SCOTT                      3000        200
         4 TIGER                      4000        200
         5 JOHN                       5000        300
         6 JAKE                       6000        300
         7

7 rows selected.

SQL> SELECT EMPNO,NVL(EMPNAME,'CHANCHAL') AS EMPNAME,NVL(SALARY,9000) AS SALARY
  2  FROM EMPLOYEE
  3  WHERE EMPNO=7;

     EMPNO EMPNAME                  SALARY
---------- -------------------- ----------
         7 CHANCHAL                   9000




Read Also:-  ISNULL function in SQL

IFNULL function in MySql

IFNULL function in MySql return default value if there is NULL value in the first expression.

Syntax:-

IFNULL (EXPRESSION,'DEFAULT_VALUE');

Example:-

SELECT * fROM EMPLOYEE;

     EMPNO EMPNAME                  SALARY     DEPTNO
---------- -------------------- ---------- ----------
         1 CHANCHAL                   1000         10
         2 WANKHADE                   2000         20
         3 SCOTT                      3000        200
         4 TIGER                      4000        200
         5 JOHN                       5000        300
         6 JAKE                       6000        300
         7 null      null       null


SELECT EMPNO,IFNULL(EMPNAME,'CHANCHAL') EMPNAME,IFNULL(SALARY,2000) SALARY
FROM EMPLOYEE
WHERE EMPNO=7;

     EMPNO EMPNAME                  SALARY
---------- -------------------- ----------
         7 SCOTT                     10000


or we can use the COALESCE() function, like this:

SELECT EMPNO,COALESCE(EMPNAME,'CHANCHAL') AS EMPNAME,COALESCE(SALARY,5000) AS SALARY
FROM EMPLOYEE
WHERE EMPNO=7;

     EMPNO EMPNAME                  SALARY
---------- -------------------- ----------
         7 SCOTT                     10000




Read Also:-  COALESCE function in MariaDB

ISNULL function in SQL

ISNULL function in SQL return default value if there is NULL value in the first expression.

Syntax:-

ISNULL (EXPRESSION,'DEFAULT_VALUE');

EXAMPLE:-

SELECT * FROM EMPLOYEE;


SELECT * fROM EMPLOYEE;

     EMPNO EMPNAME                  SALARY     DEPTNO
---------- -------------------- ---------- ----------
         1 CHANCHAL                   1000         10
         2 WANKHADE                   2000         20
         3 SCOTT                      3000        200
         4 TIGER                      4000        200
         5 JOHN                       5000        300
         6 JAKE                       6000        300
         7 null      null       null

SELECT EMPNO,ISNULL(EMPNAME,'SCOTT') AS EMPNAME,ISNULL(SALARY,10000) SALARY
FROM EMPLOYEE
WHERE EMPNO=7;


     EMPNO EMPNAME                  SALARY
---------- -------------------- ----------
         7 SCOTT                     10000


SELECT ISNULL(NULL,'CHANCHAL WANKHADE');

--> CHANCHAL WANKHADE

SELECT ISNULL('CHANCHAL','WANKHADE');

--> CHANCHAL

We can use it to handle the date as well.

SELECT ISNULL(NULL, '2010-01-01');

--> '2010-01-01'




Read Also:- NVL function in Oracle

ALIASES in MariaDB

We can have temporary name to a COLUMN or TABLE. This is called alias to column or table.

Synatx:-

SELECT EMPNAME AS EMP_NAME, EMPNO AS EMP_NO FROM EMPLOYEE;



SELECT EMPNAME AS EMP_NAME, EMPNO AS EMP_NO FROM EMPLOYEE;

EMP_NAME                 EMP_NO
-------------------- ----------
CHANCHAL                      1
WANKHADE                      2



Above EMP_NAME and EMP_NO are temporary names to the column. It is also called as column "ALIAS".

Now let's see how to alias tables:-

SELECT EMPNAME EMP_NAME, EMPNO EMP_NO,D.DEPTNO DEPT_NO
FROM EMPLOYEE AS E INNER JOIN DEPARTMENT AS D
ON E.DEPTNO=D.DEPTNO;



SELECT EMPNAME EMP_NAME, EMPNO EMP_NO,D.DEPTNO DEPT_NO
FROM EMPLOYEE AS E INNER JOIN DEPARTMENT AS D
ON E.DEPTNO=D.DEPTNO;

EMP_NAME                 EMP_NO    DEPT_NO
-------------------- ---------- ----------
CHANCHAL                      1         10
WANKHADE                      2         20

Above E and D are the temporary names for the employee and department table which are also called as table "ALIAS".




Also Read:-  ALIAS in MySql

ALIASES in Greenplum

We can have temporary name to a COLUMN or TABLE. This is called alias to column or table.

Synatx:-

SELECT EMPNAME AS EMP_NAME, EMPNO AS EMP_NO FROM EMPLOYEE;



SELECT EMPNAME AS EMP_NAME, EMPNO AS EMP_NO FROM EMPLOYEE;

EMP_NAME                 EMP_NO
-------------------- ----------
CHANCHAL                      1
WANKHADE                      2



Above EMP_NAME and EMP_NO are temporary names to the column. It is also called as column "ALIAS".

Now let's see how to alias tables:-

SELECT EMPNAME EMP_NAME, EMPNO EMP_NO,D.DEPTNO DEPT_NO
FROM EMPLOYEE AS E INNER JOIN DEPARTMENT AS D
ON E.DEPTNO=D.DEPTNO;



SELECT EMPNAME EMP_NAME, EMPNO EMP_NO,D.DEPTNO DEPT_NO
FROM EMPLOYEE E INNER JOIN DEPARTMENT D
ON E.DEPTNO=D.DEPTNO;

EMP_NAME                 EMP_NO    DEPT_NO
-------------------- ---------- ----------
CHANCHAL                      1         10
WANKHADE                      2         20

Above E and D are the temporary names for the employee and department table which are also called as table "ALIAS".




Also Read:-  ALIAS in Teradata

ALIASES in Teradata

We can have temporary name to a COLUMN or TABLE. This is called alias to column or table.

Synatx:-

SELECT EMPNAME AS EMP_NAME, EMPNO AS EMP_NO FROM EMPLOYEE;



SELECT EMPNAME AS EMP_NAME, EMPNO AS EMP_NO FROM EMPLOYEE;

EMP_NAME                 EMP_NO
-------------------- ----------
CHANCHAL                      1
WANKHADE                      2



Above EMP_NAME and EMP_NO are temporary names to the column. It is also called as column "ALIAS".

Now let's see how to alias tables:-

SELECT EMPNAME EMP_NAME, EMPNO EMP_NO,D.DEPTNO DEPT_NO
FROM EMPLOYEE AS E INNER JOIN DEPARTMENT AS D
ON E.DEPTNO=D.DEPTNO;



SELECT EMPNAME EMP_NAME, EMPNO EMP_NO,D.DEPTNO DEPT_NO
FROM EMPLOYEE AS E INNER JOIN DEPARTMENT AS D
ON E.DEPTNO=D.DEPTNO;

EMP_NAME                 EMP_NO    DEPT_NO
-------------------- ---------- ----------
CHANCHAL                      1         10
WANKHADE                      2         20

Above E and D are the temporary names for the employee and department table which are also called as table "ALIAS".




Also Read:-  ALIAS in Greenplum

ALIASES in Oracle

We can have temporary name to a COLUMN or TABLE. This is called alias to column or table.

Synatx:-

SELECT EMPNAME EMP_NAME, EMPNO EMP_NO FROM EMPLOYEE;



SELECT EMPNAME EMP_NAME, EMPNO EMP_NO FROM EMPLOYEE;

EMP_NAME                 EMP_NO
-------------------- ----------
CHANCHAL                      1
WANKHADE                      2



Above EMP_NAME and EMP_NO are temporary names to the column. It is also called as column "ALIAS".

Now let's see how to alias tables:-

SELECT EMPNAME EMP_NAME, EMPNO EMP_NO,D.DEPTNO DEPT_NO
FROM EMPLOYEE E,DEPARTMENT D
WHERE E.DEPTNO=D.DEPTNO;



SELECT EMPNAME EMP_NAME, EMPNO EMP_NO,D.DEPTNO DEPT_NO
FROM EMPLOYEE E,DEPARTMENT D
WHERE E.DEPTNO=D.DEPTNO;

EMP_NAME                 EMP_NO    DEPT_NO
-------------------- ---------- ----------
CHANCHAL                      1         10
WANKHADE                      2         20

Above E and D are the temporary names for the employee and department table which are also called as table "ALIAS".




Also Read:-  ALIAS in SQL

ALIASES in MySQL

We can have temporary name to a COLUMN or TABLE. This is called alias to column or table.

Synatx:-

SELECT EMPNAME AS EMP_NAME, EMPNO AS EMP_NO FROM EMPLOYEE;



SELECT EMPNAME AS EMP_NAME, EMPNO AS EMP_NO FROM EMPLOYEE;

EMP_NAME                 EMP_NO
-------------------- ----------
CHANCHAL                      1
WANKHADE                      2



Above EMP_NAME and EMP_NO are temporary names to the column. It is also called as column "ALIAS".

Now let's see how to alias tables:-

SELECT EMPNAME EMP_NAME, EMPNO EMP_NO,D.DEPTNO DEPT_NO
FROM EMPLOYEE AS E INNER JOIN DEPARTMENT AS D
ON E.DEPTNO=D.DEPTNO;



SELECT EMPNAME EMP_NAME, EMPNO EMP_NO,D.DEPTNO DEPT_NO
FROM EMPLOYEE AS E INNER JOIN DEPARTMENT AS D
ON E.DEPTNO=D.DEPTNO;

EMP_NAME                 EMP_NO    DEPT_NO
-------------------- ---------- ----------
CHANCHAL                      1         10
WANKHADE                      2         20

Above E and D are the temporary names for the employee and department table which are also called as table "ALIAS".




Also Read:-  ALIAS in MairaDB

ALIASES in SQL

We can have temporary name to a COLUMN or TABLE. This is called alias to column or table.

Synatx:-

SELECT EMPNAME AS EMP_NAME, EMPNO AS EMP_NO FROM EMPLOYEE;



SELECT EMPNAME AS EMP_NAME, EMPNO AS EMP_NO FROM EMPLOYEE;

EMP_NAME                 EMP_NO
-------------------- ----------
CHANCHAL                      1
WANKHADE                      2



Above EMP_NAME and EMP_NO are temporary names to the column. It is also called as column "ALIAS".

Now let's see how to alias tables:-

SELECT EMPNAME EMP_NAME, EMPNO EMP_NO,D.DEPTNO DEPT_NO
FROM EMPLOYEE AS E , DEPARTMENT AS D
ON E.DEPTNO=D.DEPTNO;



SELECT EMPNAME EMP_NAME, EMPNO EMP_NO,D.DEPTNO DEPT_NO
FROM EMPLOYEE AS E , DEPARTMENT AS D
ON E.DEPTNO=D.DEPTNO;

EMP_NAME                 EMP_NO    DEPT_NO
-------------------- ---------- ----------
CHANCHAL                      1         10
WANKHADE                      2         20

Above E and D are the temporary names for the employee and department table which are also called as table "ALIAS".




Also Read:-  ALIAS in Oracle

INITCAP function in MariaDB

INITCAP function is used to show the first character in every word in uppercase and rest in lowercase.

Synatx:-

SELECT INITCAP('STATEMENT');

Example:-

SELECT INITCAP('this is chanchal wankhade.') initcap_func;

--> This Is Chanchal Wankhade.

SELECT INITCAP('THIS IS CHANCHAL WANKHADE.') initcap_func;

--> This Is Chanchal Wankhade.

Here are some common questions related to initializing the capitalization of strings in MariaDB:-

1. How can I capitalize the first letter of each word in a string in MariaDB?

   - You can achieve this by using a combination of functions like CONCAT, UPPER, LOWER, and SUBSTRING_INDEX. Here's an example:

     

     SELECT CONCAT(UPPER(SUBSTRING_INDEX(column_name, ' ', 1)), ' ', 

                   UPPER(SUBSTRING_INDEX(SUBSTRING_INDEX(column_name, ' ', 2), ' ', -1)), ' ',

                   UPPER(SUBSTRING_INDEX(SUBSTRING_INDEX(column_name, ' ', 3), ' ', -1))) AS initcap_string

     FROM table_name;

     

     This query capitalizes the first letter of each word in the column_name column.

2. Is there a built-in INITCAP function in MariaDB like in other databases?

   - No, MariaDB doesn't have a built-in INITCAP function. You need to use a combination of string functions to achieve similar functionality.

3. Can I create a custom INITCAP function in MariaDB?

   - Yes, you can create a stored function to implement INITCAP functionality. Here's an example of how you might implement it:

     

     DELIMITER //

     CREATE FUNCTION INITCAP(str VARCHAR(255)) RETURNS VARCHAR(255)

     BEGIN

       DECLARE result VARCHAR(255);

       SET result = CONCAT(UPPER(SUBSTRING(str, 1, 1)), LOWER(SUBSTRING(str, 2)));

       RETURN result;

     END //

     DELIMITER ;

     

     You can then use this INITCAP function in your queries.

4. Does MariaDB provide any alternatives for capitalizing strings?

   - While MariaDB doesn't have an INITCAP function, you can still use other string functions like UCASE and LCASE to convert strings to uppercase or lowercase, respectively.

5. Are there any performance considerations when using custom functions for string manipulation in MariaDB?

   - Custom functions may introduce some overhead compared to built-in functions, but the impact depends on factors such as the complexity of the function and the volume of data being processed. It's essential to consider performance implications and test your queries in a realistic environment.

INITCAP function in Greenplum

INITCAP function is used to show the first character in every word in uppercase and rest in lowercase.

Synatx:-

SELECT INITCAP('STATEMENT');

Example:-

SELECT INITCAP('this is chanchal wankhade.') initcap_func;

--> This Is Chanchal Wankhade.

SELECT INITCAP('THIS IS CHANCHAL WANKHADE.') initcap_func;

--> This Is Chanchal Wankhade.

Here are some common questions related to capitalizing strings in Greenplum:-

1. How can I capitalize the first letter of each word in a string in Greenplum?

   - You can achieve this by using a combination of functions like INITCAP, SPLIT_PART, UPPER, and SUBSTRING. Here's an example:

     

     SELECT INITCAP(SPLIT_PART(column_name, ' ', 1)) || ' ' ||

            INITCAP(SPLIT_PART(column_name, ' ', 2)) || ' ' ||

            INITCAP(SPLIT_PART(column_name, ' ', 3)) AS initcap_string

     FROM table_name;

     

     This query capitalizes the first letter of each word in the column_name column.

2. Is there a built-in INITCAP function in Greenplum like in other databases?

   - No, Greenplum doesn't have a built-in INITCAP function. You need to use a combination of string functions to achieve similar functionality.

3. Can I create a custom INITCAP function in Greenplum?

   - Yes, you can create a custom user-defined function (UDF) to implement INITCAP functionality. You would typically write this function in a programming language like Python or PL/pgSQL and then load it into Greenplum as an extension.

4. Are there any alternative methods for capitalizing strings in Greenplum?

   - While Greenplum doesn't have an INITCAP function, you can still use other string functions like UPPER and SUBSTRING to manipulate strings as needed.

5. Are there any performance considerations when using custom functions for string manipulation in Greenplum?

   - Custom functions may introduce some overhead compared to built-in functions, but the impact depends on factors such as the complexity of the function and the volume of data being processed. It's essential to consider performance implications and test your queries in a realistic environment. Additionally, in Greenplum, distributing data efficiently across segments can help improve query performance.

INITCAP function in Teradata

INITCAP function is used to show the first character in every word in uppercase and rest in lowercase.

Synatx:-

SELECT INITCAP('STATEMENT');

Example:-

SELECT INITCAP('this is chanchal wankhade.') initcap_func;

--> This Is Chanchal Wankhade.

SELECT INITCAP('THIS IS CHANCHAL WANKHADE.') initcap_func;

--> This Is Chanchal Wankhade.

In Teradata, there isn't a built-in INITCAP function like in some other database systems. However, you can achieve similar functionality using a combination of string functions. Here are some common questions related to capitalizing strings in Teradata:

1. How can I capitalize the first letter of each word in a string in Teradata?

   - You can achieve this by using a combination of functions like INITCAP, SUBSTRING, and UPPER. Here's an example:

     

     SELECT INITCAP(SUBSTRING(column_name FROM 1 FOR 1)) || 

            LOWER(SUBSTRING(column_name FROM 2)) AS initcap_string

     FROM table_name;

     

     This query capitalizes the first letter of each word in the column_name column.

2. Is there a built-in INITCAP function in Teradata like in other databases?

   - No, Teradata doesn't have a built-in INITCAP function. You need to use a combination of string functions to achieve similar functionality.

3. Can I create a custom INITCAP function in Teradata?

   - Yes, you can create a custom function using Teradata's procedural SQL (PL/SQL) or External Stored Procedures (XSP). However, creating custom functions may involve more complexity compared to using built-in functions.

4. Are there any alternative methods for capitalizing strings in Teradata?

   - While Teradata doesn't have an INITCAP function, you can still use other string functions like UPPER and LOWER to manipulate strings as needed.

5. Are there any performance considerations when using custom functions for string manipulation in Teradata?

   - Custom functions may introduce some overhead compared to built-in functions, but the impact depends on factors such as the complexity of the function and the volume of data being processed. It's essential to consider performance implications and test your queries in a realistic environment. Additionally, in Teradata, optimizing queries and proper indexing can help improve performance.

INITCAP function in Oracle

INITCAP function is used to show the first character in every word in uppercase and rest in lowercase.

Synatx:-

SELECT INITCAP('STATEMENT') FROM DUAL;

Example:-

SELECT INITCAP('this is chanchal wankhade.') initcap_func FROM DUAL;

--> This Is Chanchal Wankhade.

SELECT INITCAP('THIS IS CHANCHAL WANKHADE.') initcap_func FROM DUAL;

--> This Is Chanchal Wankhade.

In Oracle, the INITCAP function is available for capitalizing the first letter of each word in a string. Here are 5 frequently asked questions about the INITCAP function in Oracle:

1. What is the purpose of the INITCAP function in Oracle?

   - The INITCAP function in Oracle is used to capitalize the first letter of each word in a string while converting all other letters to lowercase. It helps standardize the capitalization of string data for display purposes.

2. What is the syntax of the INITCAP function in Oracle?

   - The syntax of the INITCAP function in Oracle is simple:

     

     INITCAP(string)

     

     Where string is the input string expression that you want to capitalize.

3. Is the INITCAP function in Oracle case-sensitive?

   - No, the INITCAP function in Oracle is case-insensitive. It capitalizes the first letter of each word in the string while converting all other letters to lowercase.

4. Can the INITCAP function be used with columns in a SELECT statement in Oracle?

   - Yes, the INITCAP function can be used with columns in a SELECT statement in Oracle. For example:

     

     SELECT INITCAP(column_name) FROM table_name;

     

     This query will retrieve the values of the specified column with the first letter of each word capitalized.

5. Does the INITCAP function in Oracle handle special characters and multibyte characters properly?

   - Yes, the INITCAP function in Oracle properly handles special characters and multibyte characters. It capitalizes the first letter of each word while preserving the integrity of special characters and multibyte characters within the string.

These questions and answers should provide a comprehensive understanding of the INITCAP function in Oracle.

INITCAP function in MySql

INITCAP function is used to show the first character in every word in uppercase and rest in lowercase.

Synatx:-

SELECT INITCAP('STATEMENT');

Example:-

SELECT INITCAP('this is chanchal wankhade.') initcap_func;

--> This Is Chanchal Wankhade.

SELECT INITCAP('THIS IS CHANCHAL WANKHADE.') initcap_func;

--> This Is Chanchal Wankhade.

In MySQL, the INITCAP function isn't built-in. However, you can achieve similar functionality using a combination of string functions. Here are answers to common questions related to capitalizing strings in MySQL:-

1. How can I capitalize the first letter of each word in a string in MySQL?

   - You can achieve this by using a combination of functions like CONCAT, UPPER, LOWER, SUBSTRING, and INSTR. Here's an example:

     

     SELECT CONCAT(UPPER(SUBSTRING(column_name, 1, 1)), 

                   LOWER(SUBSTRING(column_name, 2, INSTR(column_name, ' ') - 1)), 

                   ' ', 

                   UPPER(SUBSTRING(column_name, INSTR(column_name, ' ') + 1, 1)),

                   LOWER(SUBSTRING(column_name, INSTR(column_name, ' ') + 2))) AS initcap_string

     FROM table_name;

     

     This query capitalizes the first letter of each word in the column_name column.

2. Is there a built-in INITCAP function in MySQL like in other databases?

   - No, MySQL doesn't have a built-in INITCAP function. You need to use a combination of string functions to achieve similar functionality.

3. Can I create a custom INITCAP function in MySQL?

   - Yes, you can create a custom stored function to implement INITCAP functionality. You can write this function in SQL or another language supported by MySQL (such as JavaScript or Python) and then load it into MySQL as a user-defined function.

4. Are there any alternative methods for capitalizing strings in MySQL?

   - While MySQL doesn't have an INITCAP function, you can still use other string functions like UPPER and LOWER to manipulate strings as needed.

5. Are there any performance considerations when using custom functions for string manipulation in MySQL?

   - Custom functions may introduce some overhead compared to built-in functions, but the impact depends on factors such as the complexity of the function and the volume of data being processed. It's essential to consider performance implications and test your queries in a realistic environment. Additionally, in MySQL, indexing and optimizing queries can help improve performance.

INITCAP function in SQL

INITCAP function is used to show the first character in every word in uppercase and rest in lowercase.

Synatx:-

SELECT INITCAP('STATEMENT');

Example:-

SELECT INITCAP('this is chanchal wankhade.') initcap_func;

--> This Is Chanchal Wankhade.

SELECT INITCAP('THIS IS CHANCHAL WANKHADE.') initcap_func;

--> This Is Chanchal Wankhade.





Read Also:-  INITCAP function in Oracle

How to find out current date of the database in MariaDB

SELECT CURDATE() CURRENT_DATE;




Read Also:-  Find out current date in MySql

How to find out current date of the database in Greenplum

SELECT CURRENT_TIMESTAMP CURRENT_DATE;




Read Also:-  Find out current date in Teradata

How to find out current date of the database in Teradata

SELECT CURRENT_DATE CURRENT_DATE;

Above query will return only date. if you want to fetch date with time then you can use below query:-

SELECT CURRENT_TIMESTAMP(0) CURRENT_DATE;

Here are 5 frequently asked questions (FAQs) about finding out the current date of the database in Teradata:-

1. What function can I use to retrieve the current date in Teradata?

   - You can use the `CURRENT_DATE` function in Teradata to retrieve the current date from the database server's system clock. This function returns the current date in the format 'YYYY-MM-DD'.

2. How do I use the CURRENT_DATE function in Teradata?

   - To use the CURRENT_DATE function, simply include it in a SELECT statement:

     

     SELECT CURRENT_DATE;

     

     This query will return the current date.

3. Can I customize the format of the current date returned by CURRENT_DATE?

   - No, the CURRENT_DATE function in Teradata returns the current date in the default 'YYYY-MM-DD' format. If you need the date in a different format, you can use Teradata's formatting functions to convert it accordingly.

4. Does the CURRENT_DATE function return the date based on the database server's timezone?

   - Yes, the CURRENT_DATE function returns the date based on the timezone of the Teradata database server. It retrieves the current date from the system clock of the database server.

5. Can I use CURRENT_DATE in a WHERE clause or as a default value for a column?

   - Yes, you can use the CURRENT_DATE function in a WHERE clause to filter rows based on the current date, or as a default value for a column to automatically populate it with the current date:

     

     SELECT * FROM table_name WHERE date_column = CURRENT_DATE;

     

     

     INSERT INTO table_name (date_column) VALUES (CURRENT_DATE);

     

     These examples demonstrate using CURRENT_DATE in a WHERE clause and as a default value in an INSERT statement, respectively.

These FAQs should provide a good understanding of how to find out the current date of the database in Teradata using the CURRENT_DATE function.

How to find out current date of the database in Oracle

SELECT SYSDATE FROM DUAL;

Above query will return only date. if you want to fetch date with time then you can use below query:-

SELECT SYSTIMESTAMP CURRENT_DATE FROM DUAL;




Read Also:-  Find out current date in SQL

How to find out current date of the database in MySql

SELECT CURDATE() CURRENT_DATE;

Above query will return only date. if you want to fetch date with time then you can use below query:-

SELECT NOW() CURRENT_DATE;



Read Also:-  Find out current date in MariaDB

How to find out current date of the database in SQL

SELECT CURDATE() CURRENT_DATE;

Above query will return only date. if you want to fetch date with time then you can use below query:-

SELECT NOW() CURRENT_DATE;




Read Also:-  Find out Current date in Oracle

ROUND function in MariaDB

ROUND function is used to round the number of decimal places. ROUND function takes two parameters,
one is the number to round and second is the number of decimal it should round to.

You must provide second parameter otherwise it will round the number to 0 decimal.

Syntax is:-

ROUND(NUMERIC_FIELD,[ROUND_NUMBER_TO_DECIMAL]);

Example:-

SELECT ROUND(9.542,2) ROUND_TO;

--> 9.54

SELECT ROUND(6.6045,2) ROUND_TO;

--> 6.6

Note:- Above it have rounded to number to two decimal but since the 0 is second decimal so it will skip the same.

SELECT ROUND(98.5264,3) ROUND_TO;

--> 98.526

SELECT ROUND(99999.8574) ROUND_TO;

--> 99999




Read Also:- ROUND function in MySql

ROUND function in Greenplum

ROUND function is used to round the number of decimal places. ROUND function takes two parameters,
one is the number to round and second is the number of decimal it should round to.

You must provide second parameter otherwise it will round the number to 0 decimal.

Syntax is:-

ROUND(NUMERIC_FIELD,[ROUND_NUMBER_TO_DECIMAL]);

Example:-

SELECT ROUND(80.5446,2) ROUND_TO;

--> 80.54

SELECT ROUND(80.50411,2) ROUND_TO;

--> 80.5

Note:- Above it have rounded to number to two decimal but since the 0 is second decimal so it will skip the same.

SELECT ROUND(80.9851,3) ROUND_TO;

--> 80.985

SELECT ROUND(9.8574) ROUND_TO;

--> 9




Read Also:- ROUND function in Teradata

ROUND function in Teradata

ROUND function is used to round the number of decimal places. ROUND function takes two parameters,
one is the number to round and second is the number of decimal it should round to.

You must provide second parameter otherwise it will round the number to 0 decimal.

Syntax is:-

ROUND(NUMERIC_FIELD,[ROUND_NUMBER_TO_DECIMAL]);

Example:-

SELECT ROUND(50.352,2) ROUND_TO;

--> 50.35

SELECT ROUND(50.4082,2) ROUND_TO;

--> 50.41

Note:- Above it have rounded to number to two decimal but since the 0 is second decimal so it will skip the same.

SELECT ROUND(70.5462,3) ROUND_TO;

--> 70.546

SELECT ROUND(3.6564) ROUND_TO;

--> 4

Here are 5 frequently asked questions (FAQs) about round functions in Teradata:-

1. What are round functions in Teradata?

   - Round functions in Teradata are SQL functions used to round numeric values to a specified number of decimal places or to the nearest integer. These functions are useful for formatting and presenting numeric data in a desired manner.

2. Which round functions are available in Teradata?

   - Teradata provides several round functions such as ROUND, ROUNDHALFUP, ROUNDHALFDOWN, ROUNDDOWN, ROUNDUP, TRUNC, and CEIL. Each function has its specific behavior regarding rounding and truncating numeric values.

3. How does the ROUND function work in Teradata?

   - The ROUND function in Teradata rounds a numeric value to a specified number of decimal places. It follows standard rounding rules: if the decimal part is 0.5 or greater, the number is rounded up; otherwise, it is rounded down.

4. What's the difference between ROUND and TRUNC functions in Teradata?

   - The ROUND function in Teradata rounds a numeric value to a specified number of decimal places, following standard rounding rules. On the other hand, the TRUNC function truncates a numeric value towards zero, removing the digits after the specified number of decimal places without rounding.

5. How can I round a numeric value to the nearest integer in Teradata?

   - To round a numeric value to the nearest integer in Teradata, you can use the ROUND function with zero decimal places. For example:

     

     SELECT ROUND(123.45, 0); -- Result: 123

     SELECT ROUND(123.65, 0); -- Result: 124

     

   

These FAQs should provide a good understanding of round functions in Teradata and how they can be used to manipulate numeric values effectively.

ROUND function in Oracle

ROUND function is used to round the number of decimal places. ROUND function takes two parameters,
one is the number to round and second is the number of decimal it should round to.

You must provide second parameter otherwise it will round the number to 0 decimal.

Syntax is:-

ROUND(NUMERIC_FIELD,[ROUND_NUMBER_TO_DECIMAL]);

Example:-

SELECT ROUND(30.545,2) ROUND_TO FROM DUAL;

--> 30.54

SELECT ROUND(30.5031,2) ROUND_TO FROM DUAL;

--> 30.5

Note:- Above it have rounded to number to two decimal but since the 0 is second decimal so it will skip the same.

SELECT ROUND(800.7854,3) ROUND_TO FROM DUAL;

--> 800.785

SELECT ROUND(300.6564) ROUND_TO FROM DUAL;

--> 300




Read Also:- ROUND function in SQL

ROUND function in MySql

ROUND function is used to round the number of decimal places. ROUND function takes two parameters,
one is the number to round and second is the number of decimal it should round to.

You must provide second parameter otherwise it will round the number to 0 decimal.

Syntax is:-

ROUND(NUMERIC_FIELD,[ROUND_NUMBER_TO_DECIMAL]);

Example:-

SELECT ROUND(20.123,2) ROUND_TO;

--> 10.12

SELECT ROUND(20.1023,2) ROUND_TO;

--> 10.1

Note:- Above it have rounded to number to two decimal but since the 0 is second decimal so it will skip the same.

SELECT ROUND(500.456,2) ROUND_TO;

--> 500.45

SELECT ROUND(500.6564) ROUND_TO;

--> 500




Read Also:- ROUND function in MariaDB

ROUND function in SQL

ROUND function is used to round the number of decimal places. ROUND function takes two parameters,
one is the number to round and second is the number of decimal it should round to.

You must provide second parameter otherwise it will round the number to 0 decimal.

Syntax is:-

ROUND(NUMERIC_FIELD,[ROUND_NUMBER_TO_DECIMAL]);

Example:-

SELECT ROUND(10.111,2) ROUND_TO;

--> 10.11

SELECT ROUND(10.101,2) ROUND_TO;

--> 10.1

Note:- Above it have rounded to number to two decimal but since the 0 is second decimal so it will skip the same.

SELECT ROUND(500.123,2) ROUND_TO;

--> 500.12

SELECT ROUND(500.123) ROUND_TO;

--> 500

CEIL function in Greenplum

The CEIL functions round the specified number up, and return the smallest number that is greater than or equal to the specified number.

Syntax is:-

CEIL (NUMBER);

Example is:-

SELECT CEILING(12.12);
--> 13

SELECT CEILING(99);
--> 99

SELECT CEILING(-99.99);
--> -99

SELECT CEILING(-32);
--> -32

Read Also:-  CEIL function in Teradata

CEIL function in Teradata

The CEIL functions round the specified number up, and return the smallest number that is greater than or equal to the specified number.

Syntax is:-

CEIL (NUMBER);

Example is:-

SELECT CEILING(11.12);
--> 12

SELECT CEILING(21.11);
--> 22

SELECT CEILING(-1.2);
--> -1

SELECT CEILING(1);
--> 1

Here are 5 frequently asked questions (FAQs) about the CEIL function in Teradata, along with their answers:-

1. What is the CEIL function in Teradata?

   - The CEIL function in Teradata is used to return the smallest integer greater than or equal to the specified numeric expression. It rounds up the numeric value to the nearest integer.

2. How do I use the CEIL function in Teradata?

   - To use the CEIL function, simply provide a numeric expression as its argument. For example:

     

     SELECT CEIL(4.25); -- Result: 5

     

     This query will return 5, as CEIL rounds up 4.25 to the nearest integer.

3. Can I use CEIL with negative numbers in Teradata?

   - Yes, you can use CEIL with negative numbers in Teradata. CEIL rounds negative numbers towards zero, so it will return the smallest integer greater than or equal to the specified negative number.

     

     SELECT CEIL(-3.75); -- Result: -3

     

     This query will return -3, as CEIL rounds up -3.75 to the nearest integer.

4. What happens if I provide a non-numeric expression to CEIL in Teradata?

   - If you provide a non-numeric expression to the CEIL function in Teradata, it will return an error. CEIL requires a numeric expression as its argument.

5. Can I combine CEIL with other functions in Teradata?

   - Yes, you can combine CEIL with other functions or expressions in Teradata. It's often used in conjunction with mathematical operations or as part of more complex calculations.

     

     SELECT CEIL(SQRT(16)); -- Result: 4

     

     This query calculates the square root of 16 (which is 4) and then rounds it up to the nearest integer using CEIL.

These FAQs should provide a good understanding of the CEIL function in Teradata and how it can be used in SQL queries.

CEIL function in Oracle

The CEIL functions round the specified number up, and return the smallest number that is greater than or equal to the specified number.

Syntax is:-

CEIL (NUMBER);

Example is:-

SELECT CEILING(1);
--> 1

SELECT CEIL(10.23) CEIL_FUNCTION FROM DUAL;
--> 11

SELECT CEIL(99.1) CEIL_FUNCTION FROM DUAL;
--> 100

SELECT CEIL(99) CEIL_FUNCTION FROM DUAL;
--> 99

SELECT CEIL(-99.1) CEIL_FUNCTION FROM DUAL;
--> -99

Read Also:-  CEIL function in Greenplum

CEILING function in SQL

The CEILING functions round the specified number up, and return the smallest number that is greater than or equal to the specified number.

Syntax is:-

CEILING (NUMBER);

Example is:-

SELECT CEILING(1);
--> 1

SELECT CEILING(10.15);
--> 11

SELECT CEILING(45.10);
--> 46

SELECT CEILING(-12.34);
--> -12

Read Also:-  CEIL function in Oracle

CEIL function in MySql

The CEIL functions round the specified number up, and return the smallest number that is greater than or equal to the specified number.

Syntax is:-

CEIL (NUMBER);

Example is:-

mysql> SELECT CEIL(11);
--> 11

mysql> SELECT CEIL(45.50);
--> 46

mysql> SELECT CEIL(-22.2);
--> 23

mysql> SELECT CEIL(-22);
--> -22

mysql> SELECT CEIL(-12.51);
--> -12

mysql> SELECT CEIL(-50.5);
--> -50

Read Also:-  CEIL function in SQL

CEIL function in MariaDB

The CEIL functions round the specified number up, and return the smallest number that is greater than or equal to the specified number.

Syntax is:-

CEIL (NUMBER)

Example is:-

SELECT CEIL(11.5);
--> 12

SELECT CEIL(11.1);
--> 12

SELECT CEIL(11);
--> 11

SELECT CEIL(-11.7);
--> -11

SELECT CEIL(-11.3);
--> -11

SELECT CEIL(-11);
--> -11


Read Also:-  CEIL function in MySql

ABS Function in Greenplum

ABS is short name of ABSOLUTE. When we use it, it shows the absolute value of the number.

When output comes in a negetive integer, it converts it in possitive integer by multiplying it by -1.

Let's have an example:-

SELECT ABS(70);

--> 70

SELECT ABS(70.70);

--> 70.7

SELECT ABS(-70);

--> 70

SELECT ABS(-70.70);

--> 70.7

SELECT ABS(-70*10);

--> 700

SELECT ABS(70*10);

--> 700


Read Also:-  ABS function in Teradata

ABS Function in MariaDB

ABS is short name of ABSOLUTE. When we use it, it shows the absolute value of the number.

When output comes in a negetive integer, it converts it in possitive integer by multiplying it by -1.

Let's have an example:-

SELECT ABS(80);

--> 80

SELECT ABS(80.80);

--> 80.8

SELECT ABS(-80);

--> 80

SELECT ABS(-80.80);

--> 80.8

SELECT ABS(-80*10);

--> 800

SELECT ABS(80*10);

--> 800

Read Also:-  ABS function in MySql

ABS Function in Teradata

ABS is short name of ABSOLUTE. When we use it, it shows the absolute value of the number.

When output comes in a negetive integer, it converts it in possitive integer by multiplying it by -1.

Let's have an example:-

SELECT ABS(50);

--> 50

SELECT ABS(50.50);

--> 50.5

SELECT ABS(-50);

--> 50

SELECT ABS(-50.50);

--> 50.5

SELECT ABS(-50*10);

--> 500

SELECT ABS(50*10);

--> 500

Read Also:-  ABS function in Greenplum

ABS Function in Oracle

ABS is short name of ABSOLUTE. When we use it, it shows the absolute value of the number.

When output comes in a negetive integer, it converts it in possitive integer by multiplying it by -1.

Let's have an example:-

SQL> SELECT ABS(10) ABS_VALUE FROM DUAL;

 ABS_VALUE
----------
        10

SQL> SELECT ABS(10.10) ABS_VALUE FROM DUAL;

 ABS_VALUE
----------
      10.1

SQL> SELECT ABS(-10) ABS_VALUE FROM DUAL;

 ABS_VALUE
----------
        10

SQL> SELECT ABS(-10.10) ABS_VALUE FROM DUAL;

 ABS_VALUE
----------
      10.1

SQL> SELECT ABS(-10*10) ABS_VALUE FROM DUAL;

 ABS_VALUE
----------
       100

SQL> SELECT ABS(10*10) ABS_VALUE FROM DUAL;

 ABS_VALUE
----------
       100



Read Also:-  ABS function in SQL

ABS Function in MySql

ABS is short name of ABSOLUTE. When we use it, it shows the absolute value of the number.

When output comes in a negetive integer, it converts it in possitive integer by multiplying it by -1.

Let's have an example:-

SELECT ABS(30);

--> 30

SELECT ABS(30.30);

--> 30.3

SELECT ABS(-30);

--> 30

SELECT ABS(-30.30);

--> 30.3

SELECT ABS(-30*30);

--> 900

SELECT ABS(30*30);

--> 900


Read Also:-  ABS function in MariaDB

ABS Function in SQL

ABS is short name of ABSOLUTE. When we use it, it shows the absolute value of the number.

When output comes in a negetive integer, it converts it in possitive integer by multiplying it by -1.

Let's have an example:-

SELECT ABS(20);

-->20

SELECT ABS(20.20);

-->20.2

SELECT ABS(-10);

-->10

SELECT ABS(-10.10);

--> 10.1

SELECT ABS(-20*20);

--> 400

SELECT ABS(20*20);

--> 400


Read Also:-  ABS function in Oracle

ORA-01427 in Oracle

ORA-01427: single-row subquery returns more than one row.
This error occurs when we try to compare multiple values with one value in Oracle.

Let's see how this error  occurs and how we can get rid of it.

Let's create two tables:-

CREATE TABLE P_TEST
(
EMP_NO NUMBER,
EMP_NAME VARCHAR2(10),
DEPTNO NUMBER
);

CREATE TABLE P_TEST_2
(
DEPTNO NUMBER,
DEPTNAME VARCHAR2(10)
);

Insert some data into it:-

INSERT INTO P_TEST

VALUES (1,'CHANCHAL',10);

INSERT INTO P_TEST

VALUES (2,'WANKHADE',20);

INSERT INTO P_TEST

VALUES (3,'SCOTT',30);

INSERT INTO P_TEST_2

VALUES (10,'FIANANCE');

INSERT INTO P_TEST_2

VALUES (20,'HR');

INSERT INTO P_TEST_2

VALUES (30,'IT');

Now try to compare the Deptno with subquery:-

SQL> SELECT DEPTNO FROM P_TEST

  2  WHERE DEPTNO =

  3  (SELECT DEPTNO FROM P_TEST_2);

(SELECT DEPTNO FROM P_TEST_2)

 *

ERROR at line 3:

ORA-01427: single-row subquery returns more than one row

It's showing error. 

To avoid this error, we need to use IN operator instate of  equals(=) operator shown below:-

SQL> SELECT * FROM P_TEST

  2  WHERE DEPTNO IN

  3  (SELECT DEPTNO FROM P_TEST_2);

    EMP_NO     EMP_NAME          DEPTNO

   ----------       ----------               ----------

         1              CHANCHAL                    10

         2              WANKHADE                   20

         3               SCOTT                         30

3 rows selected.

Read Also:-  

UNION and UNION ALL Operators in Oracle

UNION and UNION ALL operators are used to retrieve matching of non-matching records from two queries.

To see the difference between both let's create two tables as shown below:-

CREATE TABLE EMP
(
EMP_NO NUMBER,
EMP_NAME VARCHAR2(20)
);


CREATE TABLE EMP_2
(
EMP_NO NUMBER,
EMP_NAME VARCHAR2(20)
);

INSERT INTO EMP
VALUES (1,'CHANCHAL');

INSERT INTO EMP
VALUES (2,'WANKHADE');

INSERT INTO EMP_2
VALUES (1,'CHANCHAL');

INSERT INTO EMP_2
VALUES (2,'WANKHADE');

COMMIT;

1)  UNION operator is used to fetch only unique records from both the queries. UNION operator worries        about duplicate and hence slower than UNION ALL operator.

Syntax is:-

FIRST_SELECT_STATEMENT

UNION

SECOND_SELECT_STATEMENT;

Example is:-

SELECT * FROM EMP

UNION

SELECT * FROM EMP_2;

2) UNION ALL operator is used to fetch all the matching and non-matching records from both the queries.       UNION ALL operator does not worry about duplicate records and hence much faster than UNION           operator.

Syntax is:-

FIRST_SELECT_STATEMENT

UNION ALL

SECOND_SELECT_STATEMENT;

Example is:-

SELECT * FROM EMP

UNION ALL

SELECT * FROM EMP_2;

Here are some frequently asked questions about the UNION and UNION ALL operators in Oracle, along with their answers:

1. What is the purpose of the UNION and UNION ALL operators in Oracle?

   - Both the UNION and UNION ALL operators in Oracle are used to combine the result sets of two or more SELECT statements into a single result set. They are particularly useful when you want to combine data from multiple tables or queries.

2. What is the difference between UNION and UNION ALL in Oracle?

   - The main difference between UNION and UNION ALL is that UNION removes duplicate rows from the result set, while UNION ALL includes all rows, including duplicates. As a result, UNION ALL is typically faster than UNION because it doesn't need to perform the extra step of eliminating duplicates.

3. What is the syntax of the UNION operator in Oracle?

   - The syntax of the UNION operator in Oracle is as follows:

     

     SELECT column1, column2, ...

     FROM table1

     UNION

     SELECT column1, column2, ...

     FROM table2;

     

     This query combines the result sets of two SELECT statements from table1 and table2, removing duplicate rows.

4. What is the syntax of the UNION ALL operator in Oracle?

   - The syntax of the UNION ALL operator in Oracle is similar to UNION:

     

     SELECT column1, column2, ...

     FROM table1

     UNION ALL

     SELECT column1, column2, ...

     FROM table2;

     

     This query combines the result sets of two SELECT statements from table1 and table2, including all rows, including duplicates.

5. When should I use UNION and when should I use UNION ALL in Oracle?

   - Use UNION when you want to eliminate duplicate rows from the combined result set, and you are willing to pay the performance cost for doing so. Use UNION ALL when you want to include all rows, including duplicates, and you want better performance because it doesn't need to eliminate duplicates.

These questions and answers should provide a good understanding of the UNION and UNION ALL operators in Oracle.