Inserting Rows Cheat Sheet < Introduction to SQL

 

Inserting Rows

INSERT

clause is used to insert new rows in a table.

Syntax

1

2

3

4

5

6

INSERT INTO

table_name (column1, column2,..., columnN)

VALUES

(value11, value12,..., value1N),

(value21, value22,..., value2N),

...;

SQL

Any number of rows from 1 to n can be inserted into a specified table using the above syntax.

Database Let's learn more about the INSERT clause by going hands-on on the

player

and

match_details

tables that store the details of players and matches in a tournament respectively.

• player
  table stores the name, age and score of players.

• match_details
  table stores name of team, opponent team name, place, date and match result

Examples

1. Insert
   name
   ,
   age
   and
   score
    of 2 players in the
   player
   table.

1

2

3

4

5

INSERT INTO

player (name, age, score)

VALUES

("Rakesh", 39, 35),

("Sai", 47, 30);

SQL

Upon executing the above code, both the entries would be added to the

player

table.

Let's view the added data!

We can retrieve the inserted data by using the following command.

1

2

SELECT *

FROM player;

SQL

We shall know more about retrieving data in further cheat sheets.

2. Similarly, let's insert the details of 2 matches in the match_details table.

1

2

3

4

5

INSERT INTO

match_details (team_name, played_with, venue, date, is_won)

VALUES

("CSK", "MI", "Chennai", "2020-04-21", true),

("SRH", "RR", "Hyderabad", "2020-04-23", true);

SQL

Note

1. Boolean values can be either given as (TRUE or FALSE) or (1 or 0). But in the database, the values are stored as 1 or 0.
2. Date object is represented as: ‘YYYY-MM-DD’
3. Datetime object is represented as: ‘YYYY-MM-DD HH:MM:SS’

Possible Mistakes

Mistake 1

The number of values that we're inserting must match with the number of column names that are specified in the query.

1

2

3

4

INSERT INTO

player(name, age, score)

VALUES

("Virat", 31);

SQL

1

Error: 2 values for 3 columns

SQL

Mistake 2

We have to specify only the existing tables in the database.

1

2

3

4

INSERT INTO

players_information(name, age, score)

VALUES

("Virat", 31, 30);

SQL

1

Error: no such table: players_information

SQL

Mistake 3

Do not add additional parenthesis

()

post

VALUES

keyword in the code.

1

2

3

4

INSERT INTO

player (name, age, score)

VALUES

(("Rakesh", 39, 35), ("Sai", 47, 30));

SQL

1

Error: 2 values for 3 columns

SQL

Mistake 4

While inserting data, be careful with the datatypes of the input values. Input value datatype should be same as the column datatype.

1

2

3

4

INSERT INTO

player(name, age, score)

VALUES

("Virat", 31, "Hundred");

SQL

Warning

If the datatype of the input value doesn't match with the datatype of column, SQLite doesn't raise an error.

Try it Yourself!

• Three new players have joined the tournament. Try inserting the players' data in the
  player
  table.

name	age	score
Ram	28	70
Sita	25	30
Ravi	30	53