Stop Memorizing SQL Part 2: Creating Tables and Choosing the Right Data Types

Creating Tables and Choosing the Right Data Types

Before storing data in a database, we first need to create a table that defines the structure of the information. A database table is very similar to an Excel worksheet, where each column represents a specific type of data such as numbers, text, or dates. In this article, we will learn a simple step-by-step method to create tables and choose the correct data types using an easy decision rule. This approach makes it easier for beginners to understand how database structures are designed.

A table in a database is like an Excel worksheet.

An Excel worksheet has:

●       Worksheet name

●       Column names

●       Rows of data

Similarly, a database table also has:

●       Table name

●       Column names

●       Records (rows)

 

 

Table = Excel sheet

 

English sentence

Create a table called student with student id, name, class and stream.

Step 1: Give the Table Name

Decide the name of the table.

Example:
 student

---

Step 2: Identify the Column Names

Columns are the fields that store information.

Example columns:

●       Stud_id

●       Name

●       Class

●       Stream

---

Step 3: Choose the Correct Data Type

We must decide what type of data each column will store.

Use this simple 3-Question Rule:

Question 1: Is it a number?
 ➡ Use INT

Question 2: Is it text or words?
 ➡ Use VARCHAR

Question 3: Is it a date?
 ➡ Use DATE

 

Column	Data Type	Reason
Stud_id	INT	Number
Name	VARCHAR	Text
Class	INT	Number
Stream	VARCHAR	Text

 

Which datatype to choose?

 

3-question rule:

Question 1: Is it a number?
 → INT

Question 2: Is it text?
 → VARCHAR

Question 3: Is it date?
 → DATE

 

Step 4: Write the SQL Syntax

1. Write CREATE TABLE followed by the table name
   
   
2. Write column names with their data types
   
   
3. Separate columns using comma (,)
   
   
4. Do not put comma after the last column

 

 

General syntax:

 

CREATE TABLE table_name

(

column_name datatype,

column_name datatype,

column_name datatype

);

 

Step 5 - Final result

 

CREATE TABLE student

(

Stud_id INT,

Name VARCHAR(30),

Class INT,

Stream VARCHAR(20)

);

 

Part 3 : A Practical SQL Cheat Sheet for Beginners

 

Part 1: Understanding the core SQL commands and how to map English actions to SQL operations