SQL inserting NULL values
Inserting NULL values
The SQL INSERT statement can also be used to insert NULL value for a column.
Example:
Sample table : agents+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | | A006 | McDen | London | 0.15 | 078-22255588 | | | A004 | Ivan | Torento | 0.15 | 008-22544166 | | | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
To add values'A001','Jodi','London','.12','NULL' for a single row into the table 'agents' then, the following SQL statement can be used:
SQL Code:
-- This SQL code attempts to insert a new row into the 'agents' table.
-- INSERT INTO statement begins
INSERT INTO agents
-- Specifies the table 'agents' where the data will be inserted
VALUES ("A001","Jodi","London",.12,NULL);
-- Specifies the values to be inserted into each column of the table:
-- "A001" will be inserted into the 'agent_code' column (assuming it's a string data type)
-- "Jodi" will be inserted into the 'agent_name' column
-- "London" will be inserted into the 'working_area' column
-- .12 will be inserted into the 'commission' column (assuming it's a numeric data type)
-- NULL will be inserted into the 'salary' column, assuming it allows NULL values
Explanation:
- This SQL code attempts to perform an INSERT operation into the 'agents' table.
- The INSERT INTO statement specifies the table 'agents' where the data will be inserted.
- The VALUES keyword is used to specify the values to be inserted into each column of the table, in the order in which the columns are defined in the table schema.
- Each value provided in the VALUES clause corresponds to a column in the table.
SQL insert values in specific columns
The SQL INSERT INTO statement can also be used to insert one or more specific columns for a row. It is required to mention the column(s) name in the SQL query.
Example:
Sample table : agents+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | | A006 | McDen | London | 0.15 | 078-22255588 | | | A004 | Ivan | Torento | 0.15 | 008-22544166 | | | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
To add values'A001','Jodi', and ', 12' against the columns 'agent_code', 'agent_name' and 'commission' into the table 'agents', the following SQL statement can be used:
SQL Code:
-- This SQL code attempts to insert a new row into the 'agents' table, specifying only certain columns.
-- INSERT INTO statement begins
INSERT INTO agents
-- Specifies the table 'agents' where the data will be inserted
(agent_code,agent_name,commission)
-- Specifies the columns into which data will be inserted: 'agent_code', 'agent_name', and 'commission'
VALUES ("A001","Jodi",.12);
-- Specifies the values to be inserted into the specified columns:
-- "A001" will be inserted into the 'agent_code' column (assuming it's a string data type)
-- "Jodi" will be inserted into the 'agent_name' column
-- .12 will be inserted into the 'commission' column (assuming it's a numeric data type)
Explanation:
- This SQL code attempts to perform an INSERT operation into the 'agents' table, specifying only certain columns for which data will be provided.
- The INSERT INTO statement specifies the table 'agents' where the data will be inserted.
- In the parentheses following the table name, specific columns are listed: 'agent_code', 'agent_name', and 'commission'.
- The VALUES keyword is used to specify the values to be inserted into the specified columns, in the same order as the columns listed in the parentheses.
- Each value provided in the VALUES clause corresponds to the respective column specified earlier.
Here is a new document which is a collection of questions with short and simple answers, useful for learning SQL as well as for interviews.
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
Previous: Insert statement
Next: Inserting the result of a query in another table