SQL Numeric

7 Key excerpts on "SQL Numeric"

• 

  eBook - ePub

  Joe Celko's SQL for Smarties

  Advanced SQL Programming

  • Joe Celko(Author)
  • 2014(Publication Date)
  • Morgan Kaufmann

    (Publisher)

  Part 2 Data Types Passage contains an image

  Chapter 10

  Numeric Data in SQL

  Abstract

  SQL is not a computational language; the arithmetic capability of the SQL is weaker than that of almost any other language you have ever used. But there are some tricks that you need to know working with numbers in SQL and when passing them to a host program.

  Keywords NULL Exact numeric data types INTEGER BIGINT SMALLINT NUMERIC(p, s) DECIMAL (p, s) BIT Byte BOOLEAN Approximate numeric FLOAT(p) REAL DOUBLE PRECISION IEEE floating point Type conversions INCITS/H2 Database Standards Committee NULLIF() COALESCE() Mathematical functions MOD() IP addresses

  Sql is not a computational language; the arithmetic capability of the SQL is weaker than that of almost any other language you have ever used. But there are some tricks that you need to know working with numbers in SQL and when passing them to a host program. Much of the arithmetic and the functions are implementations defined, so you should experiment with your particular product and make notes on the default precision, and tools in the math library of your database.

  This section deals with the arithmetic that you would use across a row instead of down a column; they are not quite the same.

  The SQL Standard has a very wide range of numeric types. The idea is that any host language can find an SQL Numeric type that matches one of its own. Remember that SQL is meant to be used with a host language and not by itself.

  Numbers in SQL are classified as either exact or approximate. An exact numeric value has a precision, p, and a scale, s. The precision is a positive integer that determines the number of significant digits in a particular radix. The standard says the radix can be either binary or decimal, so you need to know what your implementation does. The scale is a nonnegative integer that tells you how many radix places the number has.

  Today, there are not many base-ten platforms, so you almost certainly have a binary machine. However, a number can have one of many binary representations — twos-complement, ones-complement, high end or low end and various word sizes. The proper mental model of numbers in SQL is not to worry about the “bits and bytes ” level of the physical representation, but to think in abstract terms.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  SQL For Dummies

  • Allen G. Taylor(Author)
  • 2018(Publication Date)
  • For Dummies

    (Publisher)

  Within each of these general types may be several subtypes (exact numerics, approximate numerics, character strings, bit strings, large object strings). In addition to the built-in, predefined types, SQL supports collection types, constructed types, and user-defined types, all of which I discuss later in this chapter.

  If you use an SQL implementation that supports data types that aren’t described in the SQL specification, you can keep your database more portable by avoiding these undescribed data types. Before you decide to create and use a user-defined data type, make sure that any DBMS you may want to port to in the future also supports user-defined types.

  Exact numerics

  As you can probably guess from the name, the exact numeric data types enable you to express the value of a number exactly. Five data types fall into this category:

  • INTEGER
  • SMALLINT
  • BIGINT
  • NUMERIC
  • DECIMAL
  • DECFLOAT

  INTEGER data type

  Data of the INTEGER type has no fractional part, and its precision depends on the specific SQL implementation. As the database developer, you can't specify the precision.

  The precision of a number is the maximum number of significant digits the number can have.

  SMALLINT data type

  The SMALLINT data type is also for integers, but the precision of a SMALLINT in a specific implementation can't be any larger than the precision of an INTEGER on the same implementation. In many implementations, SMALLINT and INTEGER are the same.

  If you're defining a database table column to hold integer data and you know that the range of values in the column won’t exceed the precision of SMALLINT data on your implementation, assign the column the SMALLINT type rather than the INTEGER type. This assignment may enable your DBMS to conserve storage space.

  BIGINT data type

  The BIGINT data type is defined as a type whose precision is at least as great as that of the INTEGER type (it may be greater). The exact precision of a BIGINT data type depends on the SQL implementation used.

  NUMERIC data type

  NUMERIC data can have a fractional component in addition to its integer component. You can specify both the precision and the scale of NUMERIC

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Querying Databricks with Spark SQL

  Leverage SQL to query and analyze Big Data for insights (English Edition)

  • Adam Aspin(Author)
  • 2023(Publication Date)
  • BPB Publications

    (Publisher)

  The chapter aims to enhance developers’ essential mathematical skills with Databricks. It takes a more real-world approach and shows how Databricks deals with numbers internally. Initially, this means learning to ensure that SQL accepts data as being numeric. This may be necessary because what appears as a number to humans may not be interpreted as a numeric value by SQL. Making sure that numbers are correctly handled will help in writing robust SQL that can handle potentially unpleasant surprises and deliver the expected results. After this, you begin learning to use SQL to write more complex formulas for data analysis.

  In this chapter, you will learn how to:

  • Ensure that you are using the appropriate datatypes in certain types of calculation.
  • Understand numeric datatypes.
  • Remove currency symbols from source data so that you can calculate columns that are not pure numbers.
  • Handle divide-by-zero errors.
  • Use calculated columns several times in a SQL snippet without having to copy the calculation.
  • Test for errors in SQL calculations and resolve them so that queries do not fail but perform the appropriate calculation while handling errors.
  • Convert figures stored as text into usable numbers where possible.

  This chapter is focused on enhancing the results of the analysis with some more complex calculation techniques. It establishes a firm base for developers to deliver sound and accurate metrics to improve analysis with SQL.

  Calculating the percentage represented by each record in a dataset

  All businesses like consistent customers. Therefore, the CEO of Prestige Cars wants to know what percentage of individual sales can be attributed to each client. After reviewing the approaches applied in the previous chapters, you delivered the required output to the CEO using the following SQL:

  SELECT CustomerName ,FORMAT_NUMBER( COUNT(CustomerName) / (SELECT COUNT(*) FROM allsales) , "0.00 %") AS PercentageSalesPerCustomer FROM allsales GROUP BY CustomerName ORDER BY CustomerName;

  Running the preceding query gives the results as shown in Figure 17.1

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Querying MySQL

  Make your MySQL database analytics accessible with SQL operations, data extraction, and custom queries (English Edition)

  • Adam Aspin(Author)
  • 2022(Publication Date)
  • BPB Publications

    (Publisher)

  Table 6.1: Numeric Data Types

  You may be wondering why there are so many numeric data types—especially for integers. The reasons are largely a question of the space that data can take up on disk and in memory. Put simply, if a field uses a data type that can contain the maximum value that will ever be stored in that field, it will take up less space. It will also make fetching data from disk into memory faster.

  This means that database designers nearly always try and use appropriate numeric data types so that databases run faster and cost less to maintain. It is as simple as that.

  Originally this was because disk storage and main memory were extremely expensive. Now, despite ever-lower hardware costs, the exponential growth of data means that it is still important to choose appropriate numeric data types when designing and building databases.

  Tricks and Traps

  Handling numeric data types can require a little practice. So here are a few essential points to help you:

  • When casting numeric data to an integer data type, you must always use an integer type that can hold the largest value that you are converting to an integer. So, for example, if the field that you want to cast contains values in the millions, you cannot use the SmallInt data type since it only goes up to 32,767 (or 65535 if it is a signed value). This data type physically cannot store a larger value. Indeed, if you try a data type conversion on a field that contains values that are simply too large for the destination data type to hold, then MySQL refuses to display any output and only shows an error message.
  • When casting numbers to the Decimal data type, you should specify the total allowable numbers (including decimals) as well as the number of decimal places. So, for instance, the following code CAST(COST AS DECIMAL(10,2)) converts the contents of the Cost field to the numeric data type. It allows a maximum value of 99999999.99—ten figures in all, and two decimal places.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Querying SQL Server

  Run T-SQL operations, data extraction, data manipulation, and custom queries to deliver simplified analytics (English Edition)

  • Adam Aspin(Author)
  • 2022(Publication Date)
  • BPB Publications

    (Publisher)

  This is done by applying the CAST() function to the numeric fields and specifying that each is converted as an INT. The result is that the values are now integers—and the decimals have disappeared.

  In this exercise, you have effectively done something similar to what you did in Section 2 when you calculated percentages. Previously you wrote SQL to include decimals. This time, you removed any decimals from the calculation. Both techniques use the same function—CAST().

  17.5 Numeric Data Types

  In Chapter 10 you saw how to find out the data type of a field. In the last couple of sections, you have seen how to convert numeric data types to other numeric data types as and when required.

  As a result, now is probably a good moment to explain all the various ways that SQL Server can store and manipulate numbers. In other words, it is time to look at all the numeric data types that SQL Server offers. Each has its uses and limitations, and they are given in

  Table 17.1 .

  Data Type	Range	Comments
  Bit	0 or 1	Essentially used for true or false (also called Boolean) values
  Tinyint	0 to 255	An integer value without any decimals
  SmallInt	–32,768 to +32,767	An integer value without any decimals
  Int	–2,147,483,648 to +2,147,483,647	An integer value without any decimals
  Bigint	–9,223,372,036,854,775,808 to +9,223,372,036,854,775,807	An integer value without any decimals
  SmallMoney	–214,748.3648 to +214,748.3647	Four decimals are set
  Money	–922,337,203,685,477.5808 to +922,337,203,685,477.5807	Four decimals are set
  Numeric	–10^38 (then subtract one) to +10^38 (then subtract one)	You must specify the precision and scale (total allowable numbers and numbers after the decimal)
  Decimal	–10^38(then subtract one) to +10^38 (then subtract one)	You must specify the precision and scale (total allowable numbers and numbers after the decimal)
  Float	–1.79E+308 to –2.23E–308, 0 and 2.23E–308 to 1.79E+308	Potentially huge numbers—but not stored precisely
  Real	–3.40E +38 to –1.18E – 38, 0 and 1.18E –38 to 3.40E +38

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  BCS Glossary of Computing

  • Arnold Burdett, Dan Bowen, Diana Butler, Aline Cumming, Frank Hurvid, Adrian Jackson, John Jaworski, Percy Mett, Thomas Ng, Penny Patterson, Marianne Scheer, Hazel Shaw, Alfred Vella, John Woollard, David Fuller(Authors)
  • 2016(Publication Date)
  • BCS, The Chartered Institute for IT

    (Publisher)

  Some systems provide a wide range of data or field types, such as date, sample (sound recordings) and video (moving pictures). These are particularly useful in database systems. Data, variable and field types can be specified for any data item that has an identifiable structure. Some of the more important ones are detailed below:

  Alphanumeric data is a general term used for textual data, which may include letters, digits and, sometimes, punctuation. It includes both character data type and string data type.

  Character data is a single character represented by the codes from the character set in use on the computer. See also character .

  String data is textual data in the form of a list of characters, for example words and punctuation. String data is made up of character data and will usually vary in length.

  Boolean data or logical data can only have one of two values, true or false (see truth value ). This makes it easy to use the values of Boolean variables to control the flow of a program. See also Section E8 Truth tables and logic gates.

  Sample data (digitally recorded sound data ) and video data (a video clip ) are large complex data structures containing all the information needed to enable a suitable subroutine to play a sound sample or display a video clip.

  Date data is in a form recognised as representing a date, for example 1.2.09 or 1st February 2009. Date data must represent a valid date, for example 11/12/09 is allowed, but 31st April 2009 is not allowed because April only has 30 days.

  Numeric data can have a variety of types (see numeric data type ).

  Passage contains an image

  D2 NUMERIC DATA REPRESENTATION

  This section defines terms associated with some of the methods used to store numbers in ways that can be efficiently processed. Other related material can be found in

  Section D1 Data representation

  .

  Much of the data stored in a computer are numbers of some kind. Numbers create special problems for the computer scientist because they vary in type and size in an unpredictable way.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Microsoft SQL Server 2012 Administration

  Real-World Skills for MCSA Certification and Beyond (Exams 70-461, 70-462, and 70-463)

  • Tom Carpenter(Author)
  • 2013(Publication Date)
  • Sybex

    (Publisher)

  Table 10.1 provides information about numeric data types.

  TABLE 10.1 Numeric data types

  Data type	Value range	Storage size
  Tinyint	0 to 255	1 byte
  Smallint	−32,768 to 32,767	2 bytes
  Int	−2,147,483,648 to 2,147,483,647	4 bytes
  Bigint	−9,223,372,036,854,775,808 to 9,223,372,036,854,775,807	8 bytes
  Bit	0 or 1	With 8 or fewer bits in a table, 1 byte; from 9 to 16 bits, 2 bytes; and so on
  Decimal	−1038 + 1 to 1038 − 1	Depends on precision; also called numeric; from 5 to 17 bytes; see Books Online for more information
  Smallmoney	−214,748.3648 to 214,748.3647	4 bytes
  Money	−922,337,203,685,477.5808 to 922,337,203,685,477.5807	8 bytes
  Float	−1.79E+308 to −2.23E−308, 0 and 2.23E−308 to 1.79E+308	Depends on the value of the mantissa used to store the float number
  Real	−3.40E + 38 to −1.18E - 38, 0 and 1.18E − 38 to 3.40E + 38	4 bytes

  Date and Time

  The date and time category contains data types for storing time-specific information. SQL Server 2008 introduced the date data type, also found in SQL Server 2012, which includes only the date and not the time. Additionally, SQL Server still supports the time, datetime , and datetimeoffset data types.

  Table 10.2 lists the date and time data types, with each one’s formatting structures, value range, and storage size.

  TABLE 10.2 Date and time data types

  Character and Unicode Character Strings

  The character strings and Unicode character strings are used to store exactly what they sound like they would store: character data. The difference between the two categories is that the Unicode character strings include character set information with each entry, and the character strings do not. The result is that Unicode character strings consume twice as much space as character strings; however, Microsoft still recommends the use of unicode data types because they are more transferable to different character sets. Both variable and fixed-length character data types are available. You will find the character data types listed in Table 10.3

  Sign up to read

  Learn more about book