w3resource

SQL Challenges-1: Convert negative numbers to positive and vice verse

SQL Challenges-1: Exercise-11 with Solution

From the following table, write a SQL query to convert negative numbers to positive and vice verse. Return the number.

Input:

Table: tablefortest

Structure:

FieldTypeNullKeyDefaultExtra
srnoint(11)YES
pos_neg_valint(11)YES

Data:

srnopos_neg_val
156
2-74
315
4-51
5-9
632

Sample Solution:

SQL Code(MySQL):

DROP TABLE IF EXISTS tablefortest; 
CREATE TABLE tablefortest(srno int,  pos_neg_val int);
INSERT INTO tablefortest VALUES (1, 56);
INSERT INTO tablefortest VALUES (2, -74);
INSERT INTO tablefortest VALUES (3, 15);
INSERT INTO tablefortest VALUES (4, -51);
INSERT INTO tablefortest VALUES (5, -9);
INSERT INTO tablefortest VALUES (6, 32);
select * from tablefortest;

SELECT srno,pos_neg_val,-1 *(pos_neg_val) AS converted_signed_value 
FROM tablefortest;

Sample Output:

srno|pos_neg_val|converted_signed_value|
----|-----------|----------------------|
   1|         56|                   -56|
   2|        -74|                    74|
   3|         15|                   -15|
   4|        -51|                    51|
   5|         -9|                     9|
   6|         32|                   -32|

SQL Code Editor:


Contribute your code and comments through Disqus.

Previous: Find active customers.
Next: Century of a given date.



SQL: Tips of the Day

How to select the nth row in a SQL database table?

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
LIMIT y OFFSET x 

Oracle, DB2 and MSSQL supports the standard windowing functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

Database: SQL

Ref: https://bit.ly/3zPxcD8