Java Solutions: SQL

Showing posts with label SQL. Show all posts

Tuesday, June 30, 2020

PostgreSQL: ROUND makes the value 0.5 returned from a calculation to 0 instead of 1 - Solved

In PostgreSQL, things can look odd as the following queries show.

postgres=> select round(3/cast(6 as float));
round
-------
0

postgres=> select 3/cast(6 as float);
?column?
----------
0.5

postgres=> select round(0.5);
round
-------
1

To make round(3/cast(6 as float)) to return 1 instead of 0, you need to put another layer of parenthesis to it.

postgres=> select round((3/cast(6 as numeric)));
round
-------
1

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

Watch the blessing and loving online channel: SupremeMasterTV live

If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book for free here.

Wednesday, December 27, 2017

SQL: Add single quotes, double quotes or string around a query result value

Lets say you have a Fruit table.

name price desc
---------- --------- ----------------
Apple 1.94 Very popular
Mango 2.05 Popular in Summer

Now, you want to list the fruits and their descriptions and the descriptions be printed with single or double quotes around them. You can either use the string quoting literals introduced in 10g or by concatenating the quotes.

The string quoting literal is to put your string in q'[your string]'. The square brackets can be replace by { }, < >, ( ), or ! !.

1. Single quote

SQL> select name, q'[']' || desc || q'[']' from fruit;
or
SQL> select name, q'!'!' || desc || q'<'>' from fruit;
or
SQL> select name, '''' || desc || '''' from fruit;

The '''' is four single quotes

2. Double quote

SQL> select name, q'["]' || desc || q'["]' from fruit;
or
SQL> select name, q'(")' || desc || q'{"}';
or
SQL> select '"' || desc || '"' from fruit;

The '"' is a double quote surrounded by single quotes.

3. String

Now, you want to add a string "Popularity: " in front of the descriptions.

SQL> select name, q'{Popularity: }' || desc from fruit;
or
SQL> select name, 'Popularity: ' || desc from fruit;

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

Thursday, May 11, 2017

SQL: retrieve the first / last number of digits from a number field

A. Retrieve the first number of digits

If the values in your number field have a fixed length, you can divide the number by 10 to the p power, where p is the number of digits you want to remove from the end of the number.

For example you have a hireDate in your employee table, which is in the Julian format such as 2017030. If you just want to retrieve the year of employment, you can use the SQL below.

SQL> select floor(hireDate/1000) as Year from employee;

If the values in your field does not have a fixed length, the following SQL can serve you purpose of retrieving the first number of digits.

SQL> select departmentName from department where substr(to_char(departmentNo), 1, 2) = '14';

Use the substr function, you can get digits starting from any position. For example. your employee code has the first 3 digits represent the college, followed by another 3 digits representing the department, and another 3 digits for employee status. The SQL below will help you to retrieve the depart part of the employee code

SQL> select name, substr(to_char(employeeCode), 4, 3) as dept from employee;

To get the employee status:

SQL> select name, substr(to_char(employeeCode), 7, 3) as status from employee;

B. Retrieve the last number of digits

To retrieve the last three digits of the above employeeCode,

SQL> select name, substr(to_char(employeeCode), length(employeeCode) - 3, 3) as status from employee;

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

SQL: substring of a string field - get the first number of characters of a string in Oracle

For example, you would like a list of employees whose first names start with "Chr", the SUBSTR function would help you to achieve it.

SUBSTR takes three arguments, the field name, the beginning position (starting from 1), and the length of the substring.

SQL> select firstName, middleName, lastName, title, department from employee
where SUBSTR (firstName, 1, 3) = 'Chr';

The output will look something like:

firstName middleName lastName title department
---------------- ------------------ --------------- ------------ --------------
Christine Anna Arendt librarian Library
Christopher Jones security guard Security

If you would like to display only the first four characters of the department:

SQL> select firstName, middleName, lastName, title, SUBSTR(department, 1,4) as department
from employee
where SUBSTR (firstName, 1, 3) = 'Chr';

The output would look something like:

firstName middleName lastName title department
---------------- ------------------ --------------- ------------ --------------
Christine Anna Arendt librarian Libr
Christopher Jones security guard Secu

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

Friday, December 16, 2016

SQL: UPSERT syntax and examples

UPSERT is part of the data manipulation language (DML) that allows one to use a single statement to atomically either INSERT a row, or on the basis of the row already existing, UPDATE that row instead, while safely giving little to no further thought to concurrency. The "essential property of UPSERT" is that one of those two outcomes must be guaranteed, regardless of concurrent activity.

PostgreSQL:

Starting PostgreSQL 9.5, UPSERT becomes part of its DML.

Use the ON CONFLICT clause:

For example, you have a store table and you want to set the store name to 'Unforgettable Fruits' where the store id is 559. You can use the following SQL to guarantee the result regardless if store 559 already exists.

INSERT INTO store (id, name) values (559, 'Unforgettable Fruits')
ON CONFLICT (id)
DO UPDATE SET name = 'Unforgettable Fruits';

Use the WITH clause:

Syntax:

WITH upsert AS
(UPDATE <table name>
SET <column name> = <value>
WHERE <condition> RETURNING *)
INSERT into <table name> (<columns separated by comma>)
SELECT <values separated by comma>
WHERE not exists (SELECT * from upsert);

With the above store example:

WITH upsert AS
(update store set name = 'Unforgettable Fruits' where id=559 returning *) insert into store (id, name) select 559, 'Unforgettable Fruits' where not exists (select * from upsert);

Oracle:

The above SQL starting with 'WITH upsert AS' does not work in Oracle. However Oracle can use the MERGE clause to do the same job. MERGE is typically used to merge two tables, and was introduced in the 2003 SQL standard.

Syntax:

MERGE into <table name>
USING
<source table/view/result of sub-query>
ON
<match condition>
WHEN MATCHED THEN
<update clause>
<delete clause>
WHEN NOT MATCHED THEN
<insert clause>

Using the above store example.

merge into store using (select id, name from store_temp b) on (a.id = b.id) when matched then update set a.name = b.name when not matched then insert (a.id, a.name) values (b.id, b.name);

The MERGE clause is also supported in DB2 and MS SQL.

MySQL:

The ON DUPLICATE KEY UPDATE clause: Checks if a primary key or unique index is violated by an insert, it will then do an update.

Syntax:

INSERT into <table name> (<columns separated by comma>) values (<values separated by comma>)
ON DUPLICATE KEY <update clause>

Using the above store example, which has the id as the primary key:

INSERT into store (id, name) values (559, 'Unforgettable Fruits')
ON DUPLICATE KEY UPDATE name='Unforgettable Fruits';

Of course, you can always do a select first to check if the record exists, then decide if you need to update or insert. You can also do an update first, if it returns zero, then insert.

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

Watch the blessing and loving online channel: SupremeMasterTV live

Syntax:

Tuesday, November 22, 2016

SQL: WITH clause (Common Table Expressions)

The SQL WITH clause was introduced by Oracle in the Oracle 9i release 2 database. Not every database supports WITH clause. However, PostgreSQL dose support it. The SQL WITH clause allows you to give a sub-query block a name (a process also called sub-query refactoring), which can be referenced in several places within the main SQL query. The name assigned to the sub-query is treated as though it is an inline view or table. The SQL WITH clause is basically a drop-in replacement to the normal sub-query.

WITH clause provides a way to write auxiliary statements for use in a larger query. These statements, which are often referred to as Common Table Expressions or CTEs, can be thought of as defining temporary tables that exist just for one query.

Syntax For The SQL WITH Clause

The following is the syntax of the SQL WITH clause when using a single sub-query alias.

WITH <alias_name> AS (sql_subquery_statement)SELECT column_list FROM <alias_name> [, tablename][WHERE <join_condition>]

When using multiple subquery aliases, the sysntax is as follows.

WITH <alias_name_A> AS (sql_subquery_statement),

<alias_name_B> AS(sql_subquery_statement_from_alias_name_A

or sql_subquery_statement )SELECT <column_list>FROM <alias_name_A>, <alias_name_B> [, tablename][WHERE <join_condition>]

The only advantage of using a CTE over a sub select is that you can actually name the sub query.

Following is an example.

WITH regional_sales AS (

SELECT region, SUM(amount) AS total_sales
FROM orders
GROUP BY region),
top_regions AS (
SELECT region
FROM regional_sales
WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales))

SELECT region,
product,
SUM(quantity) AS product_units,
SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

WITH Clause Is Most Useful For Recursion

When WITH clause is used to recursively perform a task, it can accomplish things not otherwise possible in standard SQL. In this case, a WITH query can refer to its own output. An example is this query to sum the integers from 1 through 100 shown below.

WITH numbs(n) AS ( //recursively put all qualified n into numbs
SELECT 1 from dual //initialize the first value
UNION ALL
SELECT n+1 FROM numbs //select from itself
WHERE n < 100 //condition to stop
)
SELECT sum(n) FROM numbs;

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

Thursday, November 17, 2016

SQL: Keep finding the next missing value from a column of sequential data without inserting the previous/current missing value

You have a FRUIT table in your database. The data of the table look like below.

fruitKey name description
---------- ----------------- -------------------------------------
1 Apple Sells best in summer
5 FruitA Need to be frozen
6 FruitB Great
10 FruitC Sweet
11 FruitD For children
12 FruitF Blue color
19 FruitG Need to buy more

Now, you have more fruits coming in and you want to put them in your FRUIT table. You want the new records to fill in the fruitKey gaps instead of incrementing the max value of it. The following SQL will get the minimum missing fruitKey which is 2.

SELECT MIN(f1.fruitKey + 1) AS fruitKey
FROM FRUIT f1
Where NOT EXISTS (SELECT * FROM FRUIT f2
WHERE f2.fruitKey = f1.fuitKey + 1;

However, you are required to summit all the insert statements at once for the system to process. The above SQL will not return the next missing fruitKey which is 3, instead, it will always return 2. The SQL below will return all the gaps in the fruitKey.

SELECT (f1.fruitKey + 1) AS fruitKey_low,
(select min(f1.fruitKey) - 1 from FRUIT f3
where f3.fruitKey > f1.fruitKey) AS fruitKey_high
FROM FRUIT f1
WHERE NOT EXISTS (SELECT * FROM FRUIT f2
WHERE f2.fruitKey = f1.fuitKey + 1

The result looks like this.

fruitKey_low fruitKey_hight
-------------- -----------------
2 4
7 9
13 18

If you are creating the insert statements manually, you can pick the values from the above result as your fruitKeys. But most likely, you are going to write a program to create the insert statements and submit them to the database automatically. In this case, you need the SQL below to get the next missing fruitKey.

WITH G AS
(
SELECT (f1.fruitKey + 1) AS fruitKey_low,
(select min(f3.fruitKey) - 1 from FRUIT f3
where f3.fruitKey > f1.fruitKey) AS fruitKey_high
FROM FRUIT f1
WHERE NOT EXISTS (SELECT * FROM FRUIT f2
WHERE f2.fruitKey = f1.fuitKey + 1
),
M AS
(
SELECT CASE
WHEN (fruitKey_high - fruitKey_low >= 0) and (fruitKey_low > <current missing key>) THEN fruitKey_low
WHEN (fruitKey_high - fruitKey_low > 0) and (fruitKey_low <= <current missing key>)
and (fruitKey_high > <current missing key>)
THEN <current missing key> + 1
ELSE (select max(fruitKey)+1 from FRUIT)
END AS fruitKey
FROM G
)
SELECT nvl (MIN(fruitKey), (select max(fruitKey)+1 from FRUIT)) AS fruitKey FROM M;

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

Friday, November 11, 2016

Solved - PostgreSQL: add days or hours to date

PostgreSQL does not have the DateAdd function. It uses the INTERVAL type to add days and hours to a date.

1. Add days to a date.

select <the date> + cast(<the days> || ' days' as INTERVAL) AS nDate from <your table>;
Or
select <the date> + (<the days> * interval '1 day') AS nDate from <your table>;

2. Add hours to a date

select <the date> + cast(<the hours> || ' hours' as INTERVAL) AS nDate from <your table>;
Or
select <the date> + (<the hours> * interval '1 hour') AS nDate from <your table>;

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

Wednesday, November 2, 2016

SQL: remove all duplicate rows

Delete from <table name> a
where a.rowid > ANY (select b.rowid
from <table name> b
//should include all columns required to be unique
where b.<column name 1> = a.<column name 1>
and b.<column name 2> = a.<column name 2>
and b.<column name 3> = a.<column name 3>

. . . . . .

);

OR

Delete from <table name> a
where exists (select *
from <table name> b
//should include all columns required to be unique
where b.<column name 1> = a.<column name 1>
and b.<column name 2> = a.<column name 2>
and b.<column name 3> = a.<column name 3>

. . . . . .

and a.rowid > b.rowid);

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

Thursday, October 6, 2016

postgresql alter table hangs

When the SQL, "Alter table Fruit add Column Location VarChar(30))", is executed at the psql terminal, it hangs.

It turns out that there is a lock on a table. The SQL below helps find all the locks in your database.

SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_statement, blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;

Then use the SQL below to kill the blocking process.

select pg_terminate_backend(<blocking_pid>);

Reference:

Lock Monitoring

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

Monday, February 29, 2016

SQL: How to update a column with values from another table?

Lets say that you have a FRUIT table containing columns: FRUIT_ID, NAME, PRICE, and POPULARITY. The FRUIT_ID is the primary key

And you have another ORDER table having these columns: ORDER_ID, FRUIT_ID, AMOUNT, and TOTAL_PRICE, The ORDER_ID is the primary key. The FRUIT_ID is the foreign key referencing the FRUIT table.

Now, for some reason, you want the ORDER table to have a FRUIT_NAME column. You can use the following SQL to copy the NAME from the FRUIT table to the ORDER table after you add the FRUIT_NAME column to the ORDER table.

UPDATE ORDER ord
SET FRUIT_NAME = (SELECT frt.NAME
FROM FRUIT frt
WHERE frt.FRUIT_ID = ord.FRUIT_ID)
WHERE FRUIT_NAME is null;

To add the FRUIT_ID column to the ORDER table:

ALTER TABLE ORDER
ADD FRUIT_ID VARCHAR2(9);

Wednesday, December 17, 2014

SQL: Increase/Decrease an amount of a field/column

For example, you have three fields (name varchar2, price float, order integer) in the Fruit table. You now want to decrease the price by 0.5 and increase the order by 5 for the Apple. Following is one way of doing it.

UPDATE Fruit
SET price = price - 0.5, order = order + 5
WHERE name = 'APPLE';

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

Monday, November 3, 2014

Oracle: Save query result to a local spread sheet or text file with fields separated by comma, pipeline, or other characters

At the command prompt, type sqlplus <username>/<password>, then hit enter.

Do the following to save to a file.

SQL> SPOOL <file path/file name><.txt or .csv> CREATE/APPEND/REPLACE

(By default, the option is REPLACE if you don't specify it.)

SQL> SET colsep <delimiter such as , or |>

//Print the column header to the output file. Default is ON
SQL> SET head ON/OFF

//Print underline beneath the column headers. Default is ON
SQL> SET UNDERLINE ON/OFF

//Print the total number of rows selected
SQL> SET FEEDBACK ON/OFF

SQL> <your SELECT sql statement>

SQL> SPOOL OFF

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

Friday, August 22, 2014

Bit And in Java and SQL

The Bit And compares each digits of two numbers in their binary format.

Decimal format Binary format
2 00000010
16 00010000
18 00010010

The Bit And works in the way that if two numbers both have 1 at a digit position in their binary formats, the result number has a 1 for that digit position, otherwise the result has a 0 for that digit position.

For numbers 2 and 16, since there has no position in their binary format that both numbers have 1, so the result of Bit And of 2 and 16 is 0 (00000000).

The result of Bit And of numbers 2 and 18, however, is 2 (00000010) due to the second position is 1 for both numbers.

In Java the operation symbol for Bit And is "&". For example,

if ((theNumber & 2) == 2) {
. . . . . .
}

In SQL the operation symbol for Bit And is "bitand". For example,

SELECT name, price
FROM fruit
WHERE bitand (criteria, 8) = 8;

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

Friday, July 25, 2014

SQL convert a Long or integer millisecond to a date string

Assume that you have an Order table in your database. The table has an OrderDate column whose data type is integer or long representing the millisecond the order is placed :-).

On your report, you of course would like the OrderDate to be displayed as a date string instead of an integer or long. The following SQL would get it done for you.

SELECT productName, orderAmount, price, to_char(to_date('1970-01-01 00', 'yyyy-mm-dd hh24') + orderDate/1000/60/60/24, 'MM/DD/YY') orderDate from Oder order by orderDate desc;

You may replace the 'MM/DD/YY' with any format that meets your purpose, such as 'MM/DD/YYYY HH:mm:ss' and 'YYYY-MM-DD HH24:MI:SS am'

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

Friday, July 11, 2014

SQL: select another column if the column value is null/0/certain value

How to construct a sql to select from a column only when the value of the column is not a certain value (e.g. null, 0, etc), otherwise select from another column?

Lets assume that you have two tables, Fruit and Apple, in your database.

table Fruit
name price
Apple 1.69
Orange 0.98
Pear 1.29

table Apple
name price
Golden Delicious 1.49
Fuji 1.89
Gala 0

The following sql gets the price for each type of apple. If the price in the Apple table for that type of apple is 0, then use the Apple price in the Fruit table.

SELECT a.name name
CASE a.price //the column
WHEN 0 THEN f.price //if a.price=0, gets f.price
ELSE a.price //else gets a.price
END AS price //the resulting field name is price
FROM Apple a, Fruit f
WHERE f.name='Apple';

The general form of the sql for getting value from another column if value is a certain known value is as below.

SELECT
CASE <columnA>
WHEN <value1> THEN <columnB> //e.g. WHEN NULL or WHEN LIKE '%ard%'
WHEN <value2> THEN <columnC>
......
ELSE <columnA>
END AS <field name in the result set>
FROM <list of tables>

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

Wednesday, July 2, 2014

java.sql.SQLException: ORA-01791: not a SELECTed expression - resolved

When the unique or distinct key word is used in the SELECT clause and at the same time an ORDER BY clause is attached to the end of a query, it sometimes returns the SQLException that says "not a selected expression" , remove either the ORDER BY clause or the unique/distinct key word from the query stops the exception.

The following two queries throw such a SQLException.

1. SELECT unique personId, nvl (firstname, ' '), nvl (lastname, ' '), ssn
FROM person ORDER BY lastname, firstname;

2. SELECT unique personId, firstname, lastname, ssn
FROM person ORDER BY lastname, firstname, middlename;

The real cause of the exception is that for the ORDER BY to work properly when a unique/distinct key word is used, it has to be able to clearly identify the columns put in the ORDER BY clause from the SELECT clause. If a column in the ORDER BY clause is missing or unable to be identified, the exception is generated.

In the above first query, both the lastname and firstname in the ORDER BY are not clearly specified in the SELECT clause. In the above second query, the middlename in the ORDER BY clause is missing from the SELECT clause.

If you don't mind of duplicate or incorrectly ordered results, you may either remove the unique/distinct key word or the column causing the problem from the ORDER BY clause. Otherwise, you need to modify your query to make it work.

The following modifications will fix the above two queries,

1. SELECT unique personId, nvl (firstname, ' ') firstname, nvl (lastname, ' ') lastname, ssn
FROM person ORDER BY lastname, firstname;

OR

SELECT unique personId, nvl (firstname, ' '), nvl (lastname, ' '), ssn
FROM person ORDER BY nvl (lastname, ' '), nvl (firstname, ' ');

2. SELECT personId, firstname, lastname, ssn FROM (
SELECT unique personId, firstname, lastname, middlename, ssn
FROM person ORDER BY lastname, firstname, middlename);

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

Thursday, June 5, 2014

java.sql.SQLException: Fail to convert to internal representation

Stack Trace:
java.sql.SQLException: Fail to convert to internal representation
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
at oracle.jdbc.driver.CharCommonAccessor.getLong(CharCommonAccessor.java:239)
at oracle.jdbc.driver.OracleResultSetImpl.getLong(OracleResultSetImpl.java:558)

The above exception occurs when a field data is retrieved from the ResultSet using a wrong data type that does not match the data type in the database, for example retrieving a varchar field in the database table from the ResultSet using getInt() or retrieving a integer field with getLong(), etc.

If you are using a counter such as

int counter = 1;
while (rs.next()) {
rs.getString(counter++);
rs.getInt(counter++);

...........
}

Check your code to make sure that the order of the fields in the select clause of your sql statement is in the same order as you retrieving the data from the result set (though this is not required for processing a result set). The data type of the get method has to match the data type of the field in the database.

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

Wednesday, May 7, 2014

The sign function of SQL

The SQL sign function returns -1, 0, or 1 when the corresponding field of a table in the database is numeric and the value is negative, 0, or positive.

For example

SELECT sign (dailyChange) PriceChange from PRICE where DATE = to_date('03/21/2014', 'MM/dd/yyyy');

If on March 21, 2014, the price was decreased, that is the dailyChange is a negative value, the output of the query is -1; if the price did not change, the output is 0; and if the price was increased, the output of the query is 1.

Another example

SELECT sign(TotalCharge - Paid) OweMoney from ORDER where customer_ID = 5588;

If the customer paid less than the total charge, the output is 1; if the customer paid the exact amount of total charge, the output is 0; and if the customer paid more than the total charge, the output is -1.

References
1. ORACLE/PLSQL: SIGN FUNCTION

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

The decode function of SQL

The SQL decode function assigns a value to the output field according to the corresponding field value of a table in the database. For example

SELECT decode(ID, 1, 'Apple', 2, 'Pear', 3, 'Papaya', 'Others') Fruit, PRICE from FRUIT;

The output has two columns, Fruit and PRICE. When the ID value in the FRUIT table in the database is 1 the Fruit value in the output will be Apple; when the ID is 2, the Fruit value will be Pear; when the ID value is 3, the Fruit value will be Papaya; and when the ID in the FRUIT table is neither 1 nor 2 nor 3, the Fruit value in the output. is Others.

References:

1. Decode

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