DML Statements - Inserting Data || DBMS Tutorial 6

DML Statements - Inserting Data 

 
DML Statements

Inserting Data

Data Manipulation Language (DML):

Data Manipulation Language (DML) enables users to access or manipulate data in a relational database. DML provides commands for retrieval, creation, deletion, and modification of information in a database. DML requires a user to specify what data are needed without specifying how to get it. The database engine is left to figure out effective means of retrieving data.

Insert Statement Syntax:

Insert statement is used to add tuples (records) to the table. It supports three alternate syntaxes as shown below:

If column names are not used then values must be provided for all columns in the order of their specification during table creation. If Column names are used then the data provided in values clause must have the same data type of column at the same position. Multiple rows can be inserted through a single INSERT statement only when it is used with SELECT statement.

Insert Statement:

Employee Table Structure

Computer Table Data

Without columns:

The simplest syntax for insert statement uses value clause without column names. All the column values must be specified in the same order as they appear in the table. The NULL keyword needs to be used in any attribute expects a null value.

Query:

INSERT INTO Employee VALUES (6, 'James Potter', '01-Jun-2014', 75000.00, 1000.00, 'ETA', 'PM', NULL, 1004);

1 row(s) created.

Without columns - incorrect order:

Any attempt to insert data by specifying values in an incorrect order will result in an error.

Query:

INSERT INTO Employee VALUES (7, 'Ethan McCarty', 90000.00, '01-Feb-2014', 1200.00, 'ETA', 'PM', NULL, NULL);

Output:

ORA-00932: inconsistent datatypes: expected DATE got NUMBER

Without columns - missing values:

The value must be specified for all columns of the table. Any missing value will result in failure.
Query:

INSERT INTO Employee VALUES (7, '01-Feb-2014', 9000.00, 1200.00, 'ETA', 'PM', NULL, NULL);

Output:
ORA-00947: not enough values

Syntax error:

Alphanumeric values must be enclosed in quotes.

Query:

INSERT INTO Employee VALUES (7, Ethan, '01-Feb-2014', 90000.00, 1200.00, 'ETA', 'PM', NULL, NULL);

Output:

ORA-00984: column not allowed here

***

Employee Table Structure

Computer Table Data

Employee Table

Columns in Correct order:

Using column names is the preferred syntax for inserting records into a table.

Query:

INSERT INTO Employee (Id, Ename, DOJ, Salary, Bonus, Dept, Designation, Manager, Compid) VALUES (7, 'Ethan McCarty', '01-Feb-2014', 90000.00, 1200.00, 'ETA', 'PM', NULL, NULL); 

Output:

1 row(s) created.

Columns in Any order:

Column names need not be supplied in the same order as they appear in the table. However, the values should be supplied in the same order as the column names in the Insert statement.

Query:

INSERT INTO Employee (Id, Compid, Ename, DOJ, Salary, Bonus, Dept, Designation, Manager) VALUES (8, 1004, 'Emily Rayner', '01-Jan-2014', 25000.00, 100.0, 'ETA', 'SE', NULL);

Output:

1 row(s) created.

Columns in Incorrect order 1:

If values are supplied in an incorrect order then insertion will fail if data type mismatch occurs.

Query:

INSERT INTO Employee (Id, Ename, DOJ, Salary, Bonus, Dept, Designation, Manager, CompId) VALUES (9, '01-Jul-2014', 'Jack Abraham', 30000.00, NULL, 'ETA', 'SSE', NULL, NULL);

Output:

ORA-01858: a non-numeric character was found where a numeric was expected

Columns in Incorrect order 1:

If values are supplied in an incorrect order then insertion will fail if constraint violation occurs.

Query:

INSERT INTO Employee (Id, Ename, DOJ, Salary, Bonus, Dept, Designation, Manager, Compid) VALUES (9, 'Jack Abraham', '01-Jul-2014', NULL , 30000.00 , 'ETA', 'SSE', NULL, NULL);

Output:

ORA-01400: cannot insert NULL into ("P65593220"."EMPLOYEE"."SALARY")

Omitting Null:

Attributes that allow null values can be omitted from the insert statement if null needs to be inserted. The omission should be from both column names and values clause.

Query:

INSERT INTO Employee (Id, Ename, DOJ, Salary, Dept, Designation, Manager, Compid) VALUES (9, 'Jack Abraham', '01-Jul-2014', 30000.00 , 'ETA', 'SSE', NULL, NULL);

Output:

1 row(s) created.

Omitting Default:

Attributes that have default values can be omitted from the insert statement if the default value needs to be inserted. This works even if the attribute has NOT NULL clause.

Query:

INSERT INTO Employee (Id, Ename, Salary, Bonus, Dept, Designation, Manager, CompId) VALUES (10, 'Ayaz Mohammad', 40000.00, NULL, 'ICP', 'TA', NULL, NULL); 

Output:

1 row(s) created.

Quiz 59: Student Table

Which of the following statements will execute successfully?

a) INSERT INTO Student VALUES(1, 'Alice', 'Female', '20-JAN-15');
b) INSERT INTO Student VALUES(1, "Alice", 'F', SYSDATE);
c) INSERT INTO Student VALUES(1, 'Alice', 'F', '2015-JAN-20');
d) INSERT INTO Student VALUES(1, 'Alice', 'F', NULL); ---

Quiz 60:
Student Table

Which of the following statements will execute successfully?

a) INSERT INTO Student(Id, Name, Gender, DOJ) VALUES(1, 'Alice', 'F', NULL); ---
b) INSERT INTO Student(Id, Gender) VALUES( 1, 'F');
c) INSERT INTO Student(Id, Name) VALUES(1, 'Alice'); ---
d) INSERT INTO Student(Id, Name, Gender, DOJ) VALUES (1,NULL, 'F', '20-JAN-15');

Employee Table Structure

Computer Table Structure

Employee Table

Computer Table

Primary Key Violation - Duplicates:

Any attempt to insert a record that violates the primary key constraint fails. Primary key columns must have unique values.

Quary:

INSERT INTO Employee (Id, Ename, DOJ, Salary, Bonus, Dept, Designation, Manager, Compid) VALUES(1, 'James Potter', '01-Jun-2014', 75000.00,1000.00, 'ICP', 'PM', NULL, 1001);

Output:

ORA-00001: unique constraint (P65593220.SYS_C008934130) violated

Primary Key Violation - Null:

Any attempt to insert a NULL value in primary key column fails. Primary key columns do not allow NULLs.

Query:

INSERT INTO Employee (Id, Ename, DOJ, Salary, Bonus, Dept, Designation, Manager, CompId) VALUES(NULL, 'James Potter', '01-Jun-2014', 75000.00,1000.00, 'ICP', 'PM', NULL, 1001);

Output:

ORA-01400: cannot insert NULL into ("P65593220"."EMPLOYEE"."ID")

Unique Constraint Allows Nulls:

Unlike Primary Key, NULL values can be entered into columns with a unique constraint.

Query:

INSERT INTO Employee (Id, Ename, DOJ, Salary, Bonus, Dept, Designation, Manager, CompId) VALUES(7, 'Ethan McCarty', '01-Feb-2014', 90000.00,1200.00, 'ETA', 'PM', NULL, NULL);

Output:

1 row(s) created.

Unique Constraint Violation:

Any attempt to insert a record that violates the unique key constraint fails. Unique key columns must have unique values.

Query:

INSERT INTO Employee (Id, Ename, DOJ, Salary, Bonus, Dept, Designation, Manager, CompId) VALUES(7, 'Ethan McCarty', '01-Feb-2014', 90000.00,1200.00, 'ETA', 'PM', NULL, 1001);

Output:

ORA-00001: unique constraint (P65593220.SYS_C008934131) violated

Unique Constraint Allows Multiple Nulls:

Unique constraint does not prevent multiple NULL values on the column. This is because two NULLs are not treated equally by SQL.

Query:

INSERT INTO Employee (Id, Ename, DOJ, Salary, Bonus, Dept, Designation, Manager, CompId) VALUES(8, 'Emily Rayner', '01-Jan-2014', 25000.00,100.0, 'ETA', 'SE', NULL, NULL);

Output:

1 row(s) created.

Foreign Key Violation 1:

Any attempt to insert a record that violates a foreign key constraint fails. The foreign key could be due to reference to the same table or a different table.

Query:

INSERT INTO Employee (Id, Ename, DOJ, Salary, Bonus, Dept, Designation, Manager, CompId) VALUES(9, 'Jack Abraham', '01-Jul-2014', 30000.00,NULL, 'ETA', 'SSE', 6, NULL);

Output:

ORA-02291: integrity constraint (P65593220.SYS_C008934132) violated - parent key not found

Foreign Key Violation 2:

Any attempt to insert a record that violates a foreign key constraint fails. The foreign key could be due to reference to the same table or a different table.

Query:

INSERT INTO Employee (Id, Ename, DOJ, Salary, Bonus, Dept, Designation, Manager, CompId) VALUES(9, 'Jack Abraham', '01-Jul-2014', 30000.00,NULL, 'ETA', 'SSE', 2, 1005);

Output:

ORA-02291: integrity constraint (P65593220.SYS_C008934133) violated - parent key not found

Quiz 61: Consider an empty Student table provided below:

Following ALTER statements are successfully executed on this table sequentially.

Queries:

ALTER TABLE Student DROP (DOB, Age);
ALTER TABLE Student ADD Gender CHAR(1);
ALTER TABLE Student MODIFY NAME NUMBER;
ALTER TABLE Student RENAME COLUMN Id TO StudentId;

Which INSERT statement will execute successfully on the modified STUDENT table?

a) INSERT INTO Student VALUES(1, 'Jack', 'M')
b) INSERT INTO Student VALUES(1, 3, 30)
c) INSERT INTO Student VALUES(1, 3, 'M') ---
d) INSERT INTO Student(Id, Name, Age, DOB) VALUES(1, 'Jack', 30, '09-30-1985')

Exercise 10: Insert

Insert a record into Salesman table with the values "11, Elizabeth, London" as "SId, SName, Location" respectively.

Exercise 11: Insert

Insert a record into Product table with the values "110, Bat, 50, Sports, NULL" as "ProdId, PDesc, Price, Category, Discount" respectively.

DML Statements - Inserting Data
DBMS Tutorial

Back    Next

---

I hope you likable this nice post. Do not forget to share it together with your friends, the Sharing Button is below the post. Apart from this, if there's any downside within the intermediate, don't hesitate to request the Comment Box. we are going to be happy to help you.

I will continue to write more and more on this blog, so do not forget to make our blog BlogLearner as a bookmark (Ctrl + D) on your mobile or computer and subscribe to us to get all the posts in your email. Do not forget to share these posts, if you like it. You can facilitate us reach additional individuals by sharing it on social networking sites like Facebook or Twitter.

DML Statements - Inserting Data

#dbms #database_management_system #dbms_tutorial #database #database_system #inserting_data #dml_statements #inserting_data

#rrkksinha #bloglearner