Answer: Insert a Record
Find a detailed explanation of how to insert a record in a table using an SQL query.
Solution
The solution is given below:
/* The query to insert a new record */INSERT INTO EmployeesVALUES (5, 'Aliza John', 65000);/* Retrieve the records in the table where EmpID is 5 */SELECT *FROM EmployeesWHERE 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
statementSelective 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. TheVALUES
clause is used to specify the values.
INSERT INTO TableNameVALUES (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 TableNameVALUES (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 forcolumn1
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. TheFROM
clause is used to specify the table from which we want to retrieve the data.
SELECT Column1, Column2, ColumnnFROM TableName;
The
WHERE
clause specifies the conditions on which we want to retrieve the records.
SELECT *FROM TableNameWHERE condition;
Alternate solution
Let’s discuss the alternate solutions for the same problem in this section:
Parameterized query
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:
SET @EmpID = 5;SET @EmpName = 'Aliza John';SET @Salary = 65000;INSERT INTO Employees (EmpID, EmpName, Salary)VALUES (@EmpID, @EmpName, @Salary);SELECT * FROM EmployeesWHERE 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.
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;
EmpID | EmpName | Salary |
---|---|---|
5 | Allen Kim | 55000.00 * 1.20 |
EmpID | EmpName | Salary |
---|---|---|
5 | Allen Kim | 66000.00 |
Error: Incorrect decimal value for column Salary
EmpID | EmpName | Salary |
---|---|---|
5 | Allen Kim | 55000 * 1.20 |
Key terms
We have covered the following key terms in the lesson:
|
|
|
|
|
|
|
|