INSERT

The INSERT operator is used to put new records into a table. Normally it is not used to load an entire database (since other utilities can do that more efficiently). Aside from this, older implementations of SQL use it to remove columns from existing tables (before the ALTER TABLE had this capability). Format1: INSERT INTO table (fieldlist) SELECT fieldlist FROM table WHERE append_criteria; OR Format2 INSERT INTO table (col1, col2...) VALUES (val1, val2...); On the general format2 above, you can specify the columns in any order you wish and the system will match them to the appropriate table attributes.

1. Example 1

Insert a new faculty record with ID of 'F330', name of Jones, department of CIS, and rank of Instructor. (Inserting a single record). INSERT INTO FACULTY (FACID, FACNAME, DEPT, RANK) VALUES ('F330','Jones','CIS',Instructor'); Since you are inserting for all fields, you can leave off the column names after FACULTY and get the same effect. For instance, the following two examples are equivalent: INSERT INTO FACULTY VALUES ('F330','Jones','CIS',Instructor'); INSERT INTO FACULTY SELECT * FROM DATATABLE; 'Datatable' is a table that holds the data to be inserted. Since the data is already in a table, this format of the INSERT is not as useful as the first version.

2. Example 2

Insert a new student record with Id of 'S1031', name of Maria Bono, 0 credits, and no major. (Insert a record with NULL value in a field) INSERT INTO STUDENT (FNAME, LNAME, STUID, CREDITS) VALUES ('Maria', 'Bono', 'S1031', 0); Notice that the field names are rearranged from the table order. This does not matter. Also notice that major is missing and will therefore be inserted into the tuple as NULL.

3. Example 3

Create and fill a new table that shows each course and the number of students enrolled in it. (Inserting multiple tuples into a new table) CREATE TABLE ENROLLMENT (COURSENUM CHAR(7) PRIMARY KEY, STUDENTS INTEGER); INSERT INTO ENROLLMENT (COURSENUM, STUDENTS) SELECT COURSENUM, COUNT(*) FROM ENROLL GROUP BY COURSENUM;

Basis Data

INSERT

1. Example 1

2. Example 2

3. Example 3

Komentar 0

Related Posts