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; |
| 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. |
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; |
| 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; |
| 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; |
| 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. |
| 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. |
| 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. |
| 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. |
