Answer: Insert a Record

Find a detailed explanation of how to insert a record in a table using an SQL query.

We'll cover the following

Solution

The solution is given below:

Press + to interact
/* The query to insert a new record */
INSERT INTO Employees
VALUES      (5, 'Aliza John', 65000);
/* Retrieve the records in the table where EmpID is 5 */
SELECT * 
FROM   Employees
WHERE  EmpID = 5;

Explanation

The explanation of the solution code is given below:

  • Line 2: The INSERT INTO statement is followed by the table name, Employees, which will be modified.

  • Line 3: The VALUES clause specifies the values.

  • Lines 6–8: The SELECT statement can be used to retrieve the records and verify the workings of our statement to insert the new record. This query outputs only the new record as per the condition.

Concepts

We have covered the following concepts in this question:

  • The INSERT INTO statement

  • Selective columns

  • Filtering data

Let’s discuss the concepts used in the solution:

  • The INSERT INTO statement followed by the name of the table is used to insert data in a table. The VALUES clause is used to specify the values. 

INSERT INTO TableName
VALUES      (value1, value2, valuen);

  • It’s totally up to us if we want to write a list of column names after the INSERT INTO keyword. 

INSERT INTO TableName (Column1, Column2, Columnn)
VALUES      (value1, value2, valuen);

  • We can insert multiple records in a table using the INSERT INTO statement. The only change is that we need to add records in parentheses and separate each record with the help of a comma.

INSERT INTO TableName
VALUES      (value1_1, value1_2, value1_n),
            (value2_1, value2_2, value2_n),
            (valuen_1, valuen_2, valuen_n);

  • In case there is an AUTO_INCREMENT field, we don’t need to specify the value for column1 because it is set to be incremented automatically. We must specify the list of columns for which we are inserting the values.

INSERT INTO TableName (column2, columnn)
VALUES      (value2, valuen);

  • We specify the columns to retrieve in the SELECT query; it retrieves the records on the basis of specified conditions. The FROM clause is used to specify the table from which we want to retrieve the data. 

SELECT Column1, Column2, Columnn
FROM   TableName;

  • The WHERE clause specifies the conditions on which we want to retrieve the records.

SELECT *
FROM   TableName
WHERE  condition;

Alternate solution

Let’s discuss the alternate solutions for the same problem in this section:

Session variables

In real-world applications, we need to insert a new employee with values provided by user input. We can do so by declaring a variable. We will declare a variable for EmpID, EmpName, and Salary. It achieves the same result but can be slightly less efficient than the query in the solution section. Let’s have a look at the following query:

Press + to interact
SET @EmpID = 5;
SET @EmpName = 'Aliza John';
SET @Salary = 65000;
INSERT INTO Employees (EmpID, EmpName, Salary)
VALUES (@EmpID, @EmpName, @Salary);
SELECT * FROM Employees
WHERE  EmpID = 5;

Remember

The best approach depends on your specific needs and desired output format.

Similar interview questions

Let’s discuss the variations of the same problem in this section:

Brain teaser

Write a query to insert a new record with a calculation on a numeric column. We have a new employee who will receive 20% more salary as a bonus on joining. Doesn’t that sound interesting? Let’s say we need to add a record for Allen Kim, whose initial salary is $55,000.

If you’re unsure how to do this, click the “Show Solution” button.

Find the error

Attempt the following quiz to find out the issue in the query where we are trying to insert values in a table.

Q

Consider the following CREATE TABLE statement:

CREATE TABLE Employees (
     EmpID   INT AUTO_INCREMENT PRIMARY KEY,
     EmpName VARCHAR(50),
     Salary  DECIMAL(10, 2)
);

What should be the output of the following query?

INSERT INTO Employees
VALUES      ('5', 'Allen Kim', '55000 * 1.20');

SELECT * 
FROM   Employees
WHERE  EmpID = 5;
A)
EmpID EmpName Salary
5 Allen Kim 55000.00 * 1.20
B)
EmpID EmpName Salary
5 Allen Kim 66000.00
C)

Error: Incorrect decimal value for column Salary

D)
EmpID EmpName Salary
5 Allen Kim 55000 * 1.20

Key terms

We have covered the following key terms in the lesson:

SELECT

FROM

WHERE

INSERT INTO

VALUES

SET

=

AUTO_INCREMENT