INSERT Statements

Estimated reading: 2 minutes 19 views

1. Write an SQL query to insert a single row into a table

To insert a single row, use the INSERT INTO statement followed by the column names and the values to be inserted.
Example:

				
					INSERT INTO employees (id, name, role, salary) 
VALUES (1, 'John Doe', 'Manager', 50000);

2. How can you insert multiple rows into a table in one query?

To insert multiple rows, use multiple VALUES sets in a single INSERT INTO statement.
Example:

				
					INSERT INTO employees (id, name, role, salary) 
VALUES (2, 'Jane Smith', 'Developer', 45000),
       (3, 'Tom Brown', 'Analyst', 40000),
       (4, 'Lucy Green', 'Tester', 35000);

3. What happens if you try to insert a row that violates a unique constraint? How would you handle it?

If you try to insert a row that violates a unique constraint, Oracle will raise a ORA-00001: unique constraint violated error.
To handle it, you can use ON CONFLICT or handle the error with EXCEPTION handling, or use MERGE for conditional insert/update.
Example:

				
					-- Handling the error with a unique constraint
INSERT INTO employees (id, name, role, salary) 
VALUES (1, 'John Doe', 'Manager', 50000)
ON CONFLICT (id) DO NOTHING;

4. Can you insert values into only specific columns of a table? Provide an example.

Yes, you can insert values into specific columns by specifying only the columns you want to insert data into.
Example:

				
					INSERT INTO employees (id, name) 
VALUES (5, 'Sarah Lee');

5. What is the use of the DEFAULT keyword in an INSERT statement?

The DEFAULT keyword is used to insert the default value of a column, as defined in the table schema (e.g., DEFAULT for a NULL or a pre-set value).
Example:

				
					INSERT INTO employees (id, name, role) 
VALUES (6, 'David Harris', DEFAULT);

6. How would you insert data from one table into another using a query?

Use an INSERT INTO ... SELECT statement to insert data from one table into another.
Example:

				
					INSERT INTO employees_archive (id, name, role, salary) 
SELECT id, name, role, salary FROM employees WHERE salary > 40000;