How to insert data into table with example in SQL

 SQL Create Table

In his article, we will learn how to create table in SQL by using  syntax  and  example you will also learn how to  insert data into table by using insert into statement. 

Table of content:

•  SQL Create Table
• How to create Table?
• How to insert data into table?
• Exercise
• Conclusion

ALSO READ

• How to select column name or Data from table?| Select statement
• Create table in SQL with PRIMARY KEY?

How to create Table?

In SQL, a table is a structured collection of data organized into rows and columns. It's like a spreadsheet or a grid that holds related information. Each column represents a specific attribute or property of the data, while each row represents an individual record or entry.

To create a table, you need to define its structure by specifying the column names and their data types.

 In SQL CREATE TABLE statement is used to create table in a database.

Create table example with employee id 

Here's a simple example:

Let's say we want to create a table called "Employees" to store information about students. We might have columns like "EmployeeID," "FirstName," "LastName,",'' Age"."Department" and "Salary." 

EmployeeID	FirstName	LastName	Age	Department	Salary
1	Ramesh	Gupta	25	Sales	50000.00
2	Meena	Panchal	30	Marketing	60000.00
3	Raju	Gujar	35	Finance	70000.00
4	Ranjana	Mane	28	Human Resources	55000.00

 CREATE TABLE Employee (
        EmployeeID INT PRIMARY KEY,
        FirstName VARCHAR(50),
        LastName VARCHAR(50),
        Age INT,
        Department VARCHAR(50),
        Salary DECIMAL(10, 2)
      );

      INSERT INTO Employee (EmployeeID, FirstName, LastName, Age, Department, Salary)
      VALUES (1, 'Ramesh', 'Gupta', 25, 'Sales', 50000.00);

      INSERT INTO Employee (EmployeeID, FirstName, LastName, Age, Department, Salary)
      VALUES (2, 'Meena', 'Panchal', 30, 'Marketing', 60000.00);

      INSERT INTO Employee (EmployeeID, FirstName, LastName, Age, Department, Salary)
      VALUES (3, 'Raju', 'Gujar', 35, 'Finance', 70000.00);

      INSERT INTO Employee (EmployeeID, FirstName, LastName, Age, Department, Salary)
      VALUES (4, 'Ranjana', 'Mane', 28, 'Human Resources', 55000.00);

Here's the syntax for creating a table in SQL: 

•  In the above syntax CREATE TABLE is the statement used to create a new table.
• table_name is the name you choose for your table. You can use any valid name you prefer.
• Inside the parentheses, you list the columns of your table along with their data types.
• Each column is defined with the syntax column_name data type.
• You can have multiple columns, separated by commas.

Lets take one example to understand it better. 

Create table example with student id

• In the above example, we create a table named "students" with six columns:

• The "student_id" column is defined as INT, representing the unique identifier for each student. By declaring it as the primary key using the PRIMARY KEY constraint, the DBMS enforces the following characteristics:

•  Uniqueness: 

Each value in the "student_id" column must be unique, ensuring that no two students can have the same ID.

•  Non-nullability: 

The "student_id" column cannot have a null value. Every student must have a valid ID.

With the primary key constraint in place, you can uniquely identify and access individual student records using their "student_id" values. It helps maintain data integrity, prevents duplicate entries for student IDs, and enables efficient indexing for faster retrieval of student information.

Remember that a primary key can consist of one or more columns, depending on your table's requirements. However, in this example, we have used a single column, "student_id," as the primary key.

• "first_name" of type VARCHAR(50) to store the first name of the student, allowing up to 50 characters.
• "last_name" of type VARCHAR(50) to store the last name of the student, allowing up to 50 characters.
• "age" of type INT to store the age of the student.
• "grade" of type CHAR(1) to store the grade level of the student as a single character, such as 'A', 'B', 'C', etc.
• "gpa" of type DECIMAL(3, 2) to store the grade point average of the student with a precision of 3 and a scale of 2.

Once you have created the table with CREATE TABLE statement, the next steps typically involve inserting data into the table and performing queries to retrieve information. 

view the structure of a table

To view the structure of a table in a database, you use a SQL command called "DESCRIBE" or "SHOW." This command helps you see information about the columns in the table, like their names, data types (such as numbers or text), and other properties.

For instance, imagine you have a table named "employees." If you run the command:

DESCRIBE employees;

The database will show you a list that explains each column in the "employees" table. It might tell you that the table has columns like "employee_id," "first_name," "last_name," and so on. It will also mention what kind of data each column holds, like numbers or text, making it easier to understand how the table is organized.

How to insert data into table?

Once you have defined the table structure, you can start inserting data into it using the INSERT INTO statement. 

The INSERT INTO statement is used to insert new records into a table. It allows you to add data to the columns of the table. 

The basic syntax of the INSERT INTO statement is as follows:

   INSERT INTO table_name (column1, column2, column3, ...)
      VALUES (value1, value2, value3, ...);

example

 INSERT INTO students (student_id, first_name, last_name, age, grade, gpa)
      VALUES (1, 'Seema', 'Rane', 20, 'A', 3.8);

In this example, we want to insert a new student record into the "students" table. 

We specify the columns in which we want to insert data: "student_id", "first_name", "last_name", "age", "grade", and "gpa". 

We provide the corresponding values for each column: 1, 'Seema', 'Rane', 20, 'A', and 3.8. This INSERT INTO statement will add a new row to the "students" table with the specified values for each column. You can insert multiple records at once by listing multiple sets of values separated by commas. 

Each set of values corresponds to a new record in the table. Remember to ensure that the values you provide match the data types and order of the columns in the table.

This is the basic idea of creating and using a table in SQL. Of course, there's a lot more you can do with SQL, such as adding constraints, modifying the table structure, or querying data using conditions. After creating or inserting data into table you can retrieve data from it.

Exercise on create table and insert data into it

Create a table called "products" with the following columns:

1. "product_id" as an integer primary key.
2. "product_name" as a string with a maximum length of 100 characters.
3. "price" as a decimal number with a precision of 10 and a scale of 2.
4. "quantity" as an integer.
5. "category" as a string with a maximum length of 50 characters.
6. Write the SQL statement to create the "products" table.

  CREATE TABLE products (
          product_id INT PRIMARY KEY,
          product_name VARCHAR(100),
          price DECIMAL(10, 2),
          quantity INT,
          category VARCHAR(50)
        );

Once the table is created, you can use the following SQL statement to insert data into the "products" table as an example:

 INSERT INTO products (product_id, product_name, price, quantity, category)
VALUES
    (1, 'Widget A', 19.99, 100, 'Widgets'),
    (2, 'Gadget B', 49.95, 50, 'Gadgets'),
    (3, 'Doodad C', 9.99, 200, 'Doodads');

Conclusion:

In conclusion, creating a table in SQL involves using the CREATE TABLE statement followed by defining the columns and their data types. You can also add additional constraints, such as primary keys, to ensure data integrity. The primary key uniquely identifies each record in the table. By using the INSERT INTO statement, you can add data to the table by specifying the column names and their corresponding values. This allows you to populate the table with records.

SQL provides a straightforward and structured approach to defining tables and inserting data, making it a powerful tool for managing databases and organizing information.

Remember to customize the table creation and data insertion statements according to your specific requirements, such as column names, data types, and values. Regularly practice creating tables and manipulating data in SQL to strengthen your skills in working with databases.

.