Master SQL Server From Scratch

Clear, interactive, and structured SQL Server lessons designed for beginners and developers.

COURSE SYLLABUS
1. Introduction to SQL Server
Introduction to SQL Server SQL Server Installation SQL Server Fundamentals
2. Data Types
Data Types Numeric Data Types Character & String Data Types Date & Time Data Types Other SQL Server Data Types
3. Database & Tables
Database & Tables Introduction Creating a Database Creating Tables Table Structure Altering Tables Dropping Tables
4. Constraints
Constraints Introduction PRIMARY KEY FOREIGN KEY UNIQUE Constraint NOT NULL & DEFAULT CHECK Constraint
5. SQL Basics
SQL Basics Introduction SQL Commands SQL Command Categories SELECT Statement INSERT, UPDATE & DELETE
6. CRUD Operations
CRUD Operations Introduction INSERT Operation SELECT Operation UPDATE Operation DELETE Operation
7. SQL Queries
Queries Introduction WHERE Clause ORDER BY Clause DISTINCT LIKE Operator IN, BETWEEN & IS NULL
8. Joins
Joins Introduction INNER JOIN LEFT JOIN RIGHT JOIN FULL OUTER JOIN CROSS JOIN
9. SQL Functions
SQL Functions Introduction String Functions Numeric Functions Date & Time Functions Conversion Functions NULL Functions
10. Grouping & Aggregate Functions
GROUP BY Aggregate Functions COUNT() SUM() AVG() MIN() & MAX()
11. Subqueries
Subqueries Introduction Single-Row Subquery Multiple-Row Subquery Correlated Subquery Subquery with DML
12. Views
SQL Views Introduction Creating a View Updating a View Altering a View Dropping a View
13. Stored Procedures
Stored Procedures Introduction Creating a Stored Procedure Input Parameters Output Parameters Executing & Modifying Stored Procedures
14. Triggers
Triggers Introduction AFTER INSERT Trigger AFTER UPDATE Trigger AFTER DELETE Trigger INSTEAD OF Trigger
15. Transactions
Transactions Introduction COMMIT ROLLBACK SAVEPOINT Transaction with TRY...CATCH
16. Indexes
Indexes Introduction Clustered Index Non-Clustered Index Creating & Dropping Indexes Index Performance
17. Common Table Expressions
CTE Introduction Simple CTE CTE with JOIN CTE with Aggregate Functions Recursive CTE
18. Window Functions
Window Functions Introduction ROW_NUMBER() RANK() and DENSE_RANK() LEAD() and LAG() PARTITION BY
19. Advanced SQL
Advanced SQL Introduction Temporary Tables Table Variables Dynamic SQL Error Handling Performance Optimization

Numeric Data Types

SQL Server provides different numeric data types to store whole numbers, decimal values and approximate numeric values. Choosing the correct numeric data type helps store data accurately and efficiently.

What are Numeric Data Types?

Numeric data types are used to store numbers in SQL Server. They can be used for values such as age, quantity, marks, salary, price, percentage and other numerical information.

SQL Server provides several numeric data types depending on whether the value is a whole number or requires decimal precision.

Common Numeric Data Types

Data Type Description Example
tinyint Stores small whole numbers from 0 to 255. 100
smallint Stores small-range whole numbers. 15000
int Stores whole numbers and is commonly used for identifiers and quantities. 50000
bigint Stores very large whole numbers. 9000000000
decimal Stores exact numeric values with fixed precision and scale. 12500.50
numeric Equivalent to decimal and stores exact numeric values. 99.95
float Stores approximate floating-point numbers. 123.456
real Stores approximate single-precision numbers. 45.67

Integer Data Types

Integer data types are used when the value does not contain decimal places.

Data Type Storage Example
tinyint 1 byte 100
smallint 2 bytes 15000
int 4 bytes 500000
bigint 8 bytes 9000000000

Decimal and Exact Numeric Values

The decimal and numeric data types are used when exact decimal values are required. They are commonly used for financial and calculation-related values.

SQL
CREATE TABLE Products
(
    ProductId INT,
    ProductName VARCHAR(100),
    Price DECIMAL(10,2)
);

In DECIMAL(10,2), the first value represents the total precision and the second value represents the number of digits after the decimal point.

Example: Using Numeric Data Types

The following example creates a Students table using different numeric data types.

SQL
CREATE TABLE Students
(
    StudentId INT,
    Age INT,
    Marks DECIMAL(5,2),
    Fees DECIMAL(10,2),
    TotalStudents BIGINT
);

Here, integer values are stored using integer data types, while values requiring decimal precision are stored using DECIMAL.

Output

SQL Output
StudentId    Age    Marks    Fees
----------------------------------
1            21     85.50    45000.00
2            22     91.25    50000.00
3            20     78.75    40000.00

The output shows how SQL Server stores whole numbers and decimal values using appropriate numeric data types.

Choosing the Correct Numeric Data Type

The numeric data type should be selected according to the size and type of value that needs to be stored.

  • Use INT for commonly used whole numbers.
  • Use BIGINT for very large whole numbers.
  • Use DECIMAL for exact decimal values.
  • Use FLOAT when approximate numeric values are acceptable.
Summary

In this section, we learned about SQL Server numeric data types such as TINYINT, SMALLINT, INT, BIGINT, DECIMAL, NUMERIC, FLOAT and REAL. We also learned how to choose an appropriate numeric data type according to the type and size of data being stored.