Lesson 3: Understanding SQL Data Types

Welcome to Lesson 3. In the previous lesson, we learned how to fetch data using SELECT. But before we can fetch data, we need to store it.

Imagine you are packing for a trip. You wouldn't wrap your shoes in a paper bag, and you wouldn't pour soup into a suitcase. You need the right container for the right item.

In SQL, Data Types are those containers.

1. Why Do We Need Data Types?

When we create a table, we must tell the database exactly what kind of data goes into each column. This helps the database:

  • Save Space: Numbers take up less space than text.
  • Prevent Errors: It stops users from entering "Hello" into a "Salary" column.
  • Sort Correctly: It knows that 10 is smaller than 2 (which isn't always true in text mode!).

2. The 3 Main Categories of Data Types

While there are many types, 90% of the time you will use these three categories:

Category Type Name Description
Numbers INTEGER Whole numbers (e.g., 1, 500, -99). used for IDs, Age, Counts.
Decimals DECIMAL(p,s) Exact numbers with decimals (e.g., 10.50). Used for Money and Prices.
Text VARCHAR(n)
 Variable length text. The most common type for Names, Emails, Cities.
Date DATE Stores Year, Month, Day (YYYY-MM-DD). Used for DOB, Join Date.

3. The Most Popular Interview Question: CHAR vs VARCHAR

Both CHAR and VARCHAR store text. So, what is the difference?

CHAR (Fixed Length)

If you write CHAR(10), the database always reserves 10 spaces of memory, even if you only type "A". It fills the rest with empty spaces.

  • Use for: Data that is always the same length (e.g., Pincodes, Gender 'M'/'F').

VARCHAR (Variable Length)

If you write VARCHAR(10) and type "A", the database only uses 1 space.

  • Use for: Data that varies in length (e.g., Names, Emails, Addresses).
💡 Pro Tip: Always use VARCHAR unless you are 100% sure the length will never change. It saves memory!

4. Your Turn: Create a Table with Types

Let's build a Students table using the types we just learned.

  • ID: Number (INT)
  • Name: Text (VARCHAR)
  • GPA: Decimal (DECIMAL)
  • Dob: Date (DATE)

Step 1: Open OracleSQL or SQL Fiddle.

Step 2: Copy and Run this code to create the table and insert data:

CREATE TABLE students (
  student_id INT,
  first_name VARCHAR(50),
  gpa DECIMAL(3,2),
  dob DATE
);

INSERT INTO students VALUES (1, 'Rahul', 3.8, '2000-05-15');
INSERT INTO students VALUES (2, 'Priya', 9.2, '2001-08-20');

Step 3: Now verify your data types worked by running this:

SELECT * FROM students;

Summary: Today we learned that every column needs a datatype. We learned to use INT for numbers, VARCHAR for text, and DATE for time.

Next Lesson: In Lesson 4, we will learn about Constraints — the rules that keep our data clean.

Return to Course Syllabus

Comments

Post a Comment

Popular posts from this blog

Lesson 1: What is a Database?

Welcome to the first lesson of your SQL journey. Introduction When I first started learning coding at ThinkByByte, I was confused about where data actually lives. To handle this data efficiently, we use a DBMS (Database Management System). In this lesson, we will understand what DBMS is, why it is needed, and how it works. What is DBMS? DBMS (Database Management System) is a software system that allows users to store, manage, retrieve, and modify data efficiently in a structured format. A Real-Life Example Imagine an organization 🏢: Employee Details = Data Register or Computer System = DBMS Employer or Management = User The DBMS helps management to: Add new employees Update employee data Check employee data Remove resigned/old employees' data Why Do We Need DBMS? In earlier days, data was stored using file systems like text files or Excel documents. This created many problems. Problems with File Systems: ❌ Duplicate Data: The same information is...
Read more

Lesson 2: Introduction to SQL

Welcome to Lesson 2. In the last lesson, we learned that the database is like a "digital filing cabinet." Now, we are going to learn the language used to talk to that cabinet. That language is SQL.  In this Introduction to SQL for beginners , you will learn how to use SQL to fetch data from a database. 1. What is SQL? SQL stands for Structured Query Language . It is the standard language for storing, manipulating, and retrieving data in a relational database. You: "Hey database, show me the names of all employees who joined today." SQL: SELECT emp_name FROM employees; Database: "Here is the list." Important Note: SQL is NOT a database (like Oracle or MySQL). SQL is the language used to talk to those databases. 2. The 3 Golden Rules of SQL Syntax Just like English has grammar rules, SQL has syntax rules. If you break them, the database won't understand you. Rule Explanation 1. Semico...
Read more

Lesson 4: SQL Constraints (Rules for Your Data).

Welcome to Lesson 4. In the last lesson, we learned about Data Types (Text, Numbers, Dates). But simply knowing the type isn't enough. Imagine a "Sign Up" form on a website. What happens if: A user tries to sign up without a password ? Two users try to use the same email address ? The database must stop this! We use Constraints to enforce these rules. 1. What are SQL Constraints? Constraints are rules we apply to columns to ensure the data is accurate and reliable. If you try to insert data that breaks a rule, the database will say "Error" and reject it. 2. The 6 Most Important Constraints Here are the rules you will use in almost every table: Constraint The Rule Real Life Example NOT NULL The column cannot be empty. A user must have a Password. UNIQUE No duplicate values allowed. Two users cannot have the same Email . DEFAULT ...
Read more