Data type conversion


NVL

In Oracle NVL (or COALESCE) allows converting the NULL value to a default value for the purposes of calculations. It works with numbers and strings. In MySQL, you may use COALESCE or IFNULL. In Microsoft SQL Server, you may use ISNULL or COALESCE.
En Oracle NVL (o COALESCE) permite convertir el valor de NULL a un valor predefinido para propósitos de cálculos. Este funciona con números y cadenas de texto. En MySQL se usa COALESCE o IFNULL para estos propósitos. En Microsoft SQL Server ISNULL o COALESCE.

Problem 1
city_bank > Test the following SQL command.
Pruebe el siguiente comando de SQL.

SQL
-- __________________________________ Oracle
SELECT name,
NVL(birth_date, '01-JAN-1970') AS friend_birth_date
FROM friend;
-- __________________________________ MySQL
SELECT name,
COALESCE(birth_date, '01-JAN-1970') AS friend_birth_date
FROM friend;
-- __________________________________ Microsoft
SELECT name,
ISNULL(birth_date, '01-JAN-1970') AS friend_birth_date
FROM friend;


cb_friend_birthdate

Problem 2
aeromexico> Write an SQL command to produce the sentences shown in Oracle.
Escribir un comando SQL en Oracle para obtener la salida mostrada.

aero_nvl

TO_NUMBER()

It converts a string to a number in Oracle.
Convierte una cadena de texto a un número en Oracle.

TO_CHAR

It converts a number to a string in Oracle.
Convierte un número a una cadena de texto en Oracle.

Tip
MySQL and Microsoft SQL Server support the command CAST to perform conversions. For instance, CAST('5' AS UNSIGNED INT) converts the text '5' to the integer value of 5.
MySQL y Microsoft SQL Server proporcionan el comando CAST para convertir a cualquier tipo de dato. Por ejemplo CAST('5' AS UNSIGNED INT) convierte el texto '5' al entero 5.

Problem 3
aeromexico > Test the following SQL command in Oracle. Each '9' represents a digit.
Pruebe el siguiente comando de SQL in Oracle. Cada '9' representa un digito.

SQL
SELECT destination,
TO_CHAR(flight_cost, '$9,999,999.99') AS price
FROM mexico
WHERE flight_cost IS NOT NULL;

aero_to_char

Problem 4
aeromexico > Test the following SQL command in MySQL. Where the second argument of FORMAT is the number of digits (rounding) after the decimal point; the function adds commas as shown.
Pruebe el siguiente comando de SQL in MySQL. Donde el segundo argumento de FORMAT es el número de decimales (redondeando); la función agrega comas como se muestra.

SQL
SELECT destination,
CONCAT('$', FORMAT(flight_cost, 2)) AS price
FROM mexico
WHERE flight_cost IS NOT NULL;

aero_format

Problem 5
kimberly > Test the following SQL command in Oracle. Observe the difference between '0' and '9' when using TO_CHAR.
Pruebe el siguiente comando de SQL in Oracle. Observe la diferencia entre '0' y '9' al usar TO_CHAR.

SQL
SELECT descr,
     TO_CHAR (cost, '$99,999.99') AS price,
     TO_CHAR (density, '0.999') AS d0999,
     TO_CHAR (density, '9.999') AS d9999,
     TO_CHAR (density, '0.000') AS d0000,
     TO_CHAR (density, '00.000') AS d00000,
     TO_CHAR (density, '99.999') AS d99999
FROM material;

kim_to_char

Problem 6
ford> Write the SQL to increment by 55 to the area code of the phone numbers. Using: (a) Microsoft SQL Server, (b) Oracle, (c) MySQL.
Escriba un comando SQL para sumar 55 al código de área de los números de teléfono de las tiendas. Usando: (a) Microsoft SQL Server, (b) Oracle, (c) MySQL.

kim_newphone

Problem 7
circuit_city> Write an SQL command to list the credit for each client. Using: (a) Microsoft SQL Server, (b) Oracle, (c) MySQL.
Escriba un comando SQL para listar el crédito que tiene cada cliente. Usando: (a) Microsoft SQL Server, (b) Oracle, (c) MySQL.

cc_credit

Problem 8
city_bank> Write an SQL command to list the balance for each account using MySQL as shown.
Escriba un comando SQL para listar el balance de cada cuenta usando MySQL como se muestra.

cb_balance

Problem 9
city_bank> Write an SQL command to list the address, type and balance for each client.
Escriba un comando SQL para listar la dirección, el tipo y el balance para cada cliente.

cb_byclient

© Copyright 2000-2019 Wintempla selo. All Rights Reserved. Sep 05 2019. Home