SQL Numeric Types 🎯

Welcome to our comprehensive guide on SQL Numeric Types! In this tutorial, we'll explore the various numeric data types available in SQL, their usage, and practical examples. Let's get started!

Understanding SQL Numeric Types 📝

Numeric types in SQL are used to store numerical data. These types are essential in any database system, as they allow for precise mathematical operations.

In SQL, we have three main numeric data types:

INTEGER
DECIMAL
FLOAT

Each type has its own range and precision. Let's delve into each one.

INTEGER 💡

An integer is a whole number, either positive or negative. In SQL, the INTEGER type can store numbers from -2147483648 to 2147483647.

sql

-- Example: Creating an INTEGER column
CREATE TABLE example_table (
    id INT
);

Quiz 💡

Question: What is the maximum value an INTEGER can store in SQL? A: 2147483648 B: 9223372036854775807 C: 1000000000 Correct: A Explanation: The maximum value an INTEGER can store in SQL is 2147483648.

DECIMAL 💡

A DECIMAL is used to store numbers with decimal points. In SQL, DECIMALs can store numbers with up to 38 digits (16 digits before the decimal point and 15 digits after it).

sql

-- Example: Creating a DECIMAL column
CREATE TABLE example_table (
    decimal_num DECIMAL(10, 2)
);

In the example above, the DECIMAL(10, 2) means we can store a number with 10 digits in total, out of which 2 digits are after the decimal point.

FLOAT 💡

FLOAT is used to store floating-point numbers with decimal points. In SQL, the FLOAT type can store up to 15 significant digits.

sql

-- Example: Creating a FLOAT column
CREATE TABLE example_table (
    float_num FLOAT
);

Quiz 💡

Question: Which data type can store numbers with up to 38 digits in SQL? A: DECIMAL B: INTEGER C: FLOAT Correct: A Explanation: DECIMALs can store numbers with up to 38 digits in SQL.

Practical Example 💡

Let's create a table to store user's data, including their age (INTEGER), income (DECIMAL), and weight (FLOAT).

sql

CREATE TABLE users (
    id INT PRIMARY KEY,
    age INT,
    income DECIMAL(10, 2),
    weight FLOAT
);

Remember, using the appropriate data type for each column can greatly improve the performance and accuracy of your database. Happy learning! 🌟

This is just the start of our SQL Numeric Types tutorial. In the next lesson, we'll explore SQL String Types. Stay tuned! 🌟

Notes:

Always choose the appropriate numeric data type for your data to ensure optimal performance and accuracy.
Be careful when using FLOATs, as they may lose precision during mathematical operations.
DECIMALs can provide more precise results than FLOATs, but they may use more storage space.
In SQL, you can convert one data type to another using conversion functions, such as CAST or CONVERT.

Challenge:

Create a table to store employee data, including their employee ID (INTEGER), salary (DECIMAL), and years of experience (INTEGER).
Insert sample data into the table.
Perform basic mathematical operations on the data (e.g., calculate total salary, average salary per year of experience).