Question 5

Explanation

Correct SQL Statement:

INSERT INTO transaction (trn_id, end_date)
VALUES ('A5', TO_DATE('15-FEB-2023', 'DD-MONTH-YYYY'));

• trn_id = 'A5'

• end_date = a valid date using TO_DATE

• start_date is omitted, so Oracle uses the default: SYSDATE

• This is the only correct and syntactically valid insert statement among the options.

In the solution, we did not list the start date so it was filled with the default system date as shown in the Exhibit. The explicit data conversion method TO_DATE was used to convert the date to the table format.

Let's review the table definition and evaluate the correct way to insert a row using the default value for start_date and provide values for other columns.

Table Definition (Exhibit 1)

CREATE TABLE transaction (
  trn_id      CHAR(2) PRIMARY KEY,
  start_date  DATE DEFAULT SYSDATE,
  end_date    DATE NOT NULL
);

• trn_id: must be provided, it's the primary key.

• start_date: has a default of SYSDATE if not explicitly provided.

• end_date: is NOT NULL, so it must be explicitly provided in the INSERT.

Incorrect Options (with reasons):

1. INSERT INTO transaction VALUES ('A1', DEFAULT, TO_DATE(DEFAULT+10));

   • DEFAULT cannot be used like this inside expressions (DEFAULT+10 is invalid).

   • Also, TO_DATE(DEFAULT+10) makes no sense and will raise an error.

2. INSERT INTO transaction VALUES ('A1', DEFAULT, TO_DATE('SYSDATE+10'));

   • 'SYSDATE+10' is a string, not a valid date.

   • TO_DATE('SYSDATE+10') is invalid unless it's a date literal, which this is not.

3. INSERT INTO transaction (trn_id, start_date, end_date) VALUES ('A5, 10-DEC-2025');

   • Syntax is incorrect:

     • Missing closing ' around 'A5'

     • Missing value for start_date

     • Only 2 values supplied for 3 columns

4. INSERT INTO transaction (trn_id, end_date) VALUES("A5", TO_DATE('15-FEB-2023', 'DD-MM-RR'));

   • Uses double quotes "A5" — which are for identifiers, not string literals.

   • Should use single quotes 'A5'.

5. INSERT INTO transaction (trn_id, start_date, end_date) VALUES ("A5, 10-DEC-2025");

   • Same issues: bad quoting, wrong number of values, and invalid syntax.

The Oracle INSERT statement is used to insert a single record or multiple records into a table in Oracle.

Syntax

The syntax for the Oracle INSERT statement when inserting a single record using the VALUES keyword is:

INSERT INTO table
(column1, column2, ... column_n )
VALUES
(expression1, expression2, ... expression_n );

Or the syntax for the Oracle INSERT statement when inserting multiple records using a SELECT statement is:

INSERT INTO table
(column1, column2, ... column_n )
SELECT expression1, expression2, ... expression_n
FROM source_table
[WHERE conditions];

Parameters or Arguments

table - The table to insert the records into.

column1, column2, ... column_n - The columns in the table to insert values.

expression1, expression2, ... expression_n - The values to assign to the columns in the table. So column1 would be assigned the value of expression1, column2 would be assigned the value of expression2, and so on. source_table The source table when inserting data from another table.

WHERE conditions Optional.  - The conditions that must be met for the records to be inserted.

Note

When inserting records into a table using the Oracle INSERT statement, you must provide a value for every NOT NULL column.

You can omit a column from the Oracle INSERT statement if the column allows NULL values.

The Oracle/PLSQL TO_DATE function converts a string to a date.

Syntax

The syntax for the TO_DATE function in Oracle/PLSQL is:

TO_DATE( string1 [, format_mask] [, nls_language] )

Parameters or Arguments

string1 - The string that will be converted to a date.

format_mask Optional. - This is the format that will be used to convert string1 to a date.

nls_language Optional. - This is the nls language used to convert string1 to a date.

Returns

The TO_DATE function returns a date value.