CS457 Syllabus & Progress

SQL: Insert, Delete and Update

Inserting tuples into a database

The INSERT command can be used to insert one or more tuples into a relation.
There are 2 forms of the INSERT command:

Inserting a tuple using literal (constant) values

Syntax:

INSERT INTO relationName VALUES ( .... ) or INSERT INTO relationName(attributeList) VALUES ( .... )

Format 1: Inserting ONE COMPLETE tuple:

all attribute values

exact order

relation schema

You can omit the list of attributes.

Example:

INSERT INTO employee VALUES ('Richard', 'K', 'Marini', '222669999', '30-Dec-52', '98 Oak Street, Katy, TX', 'M', 37000, '987654321', 4);

Format 2: Inserting a PARTIAL tuple:
- NOTE:: The attributes not in the list are given the NULL value

Inserting a tuple using the result from a SELECT command

Syntax:

INSERT INTO relationName (SELECT ...)

Example: expense report for each department:

CREATE TABLE dept_info ( DName CHAR(20), No_Emps INTEGER, Tot_Sal DECIMAL(9,2) );
INSERT INTO dept_info (SELECT dname, count(*), sum(salary) FROM employee, department WHERE dno = dnumber GROUP BY dname );

SELECT * FROM dept_info; Output: DNAME NO_EMPS TOT_SAL -------------------- ---------- ---------- Administration 3 93000 Headquarters 1 55000 Research 4 133000

Import Utilities

All respectable Database Management Systems provide utilities to import data from text files or sometimes from spreadsheet applications (e.g., excel spreadsheet).

Oracle:

The Oracle's import utility is called sqlldr (SQL loader).
It is a separate application outside the Oracle Data server supplied by Oracle to import data.
Documentation: click here

MySQL:

The import utility in MySQL is an additional command added to the MySQL SQL language

Sample Import data command: (for the complete syntax, see: click here)

/* Create a dummy table to receive the data */ create table test (a char(10), b int, constraint primary key(a) ); /* Populate the table with the data file "test.dat" */ LOAD DATA LOCAL INFILE "test.dat" REPLACE INTO TABLE test FIELDS TERMINATED BY ',' Or: (skip inserted records with existing primary key) LOAD DATA LOCAL INFILE "test.dat" IGNORE INTO TABLE test FIELDS TERMINATED BY ','

Demo:

/home/cs377001/demo/insert/00readme

Execute the commands in the 00readme file for demo....

Deleting tuples from a database

You can delete one or more tuples from a relation using the DELETE command.

Syntax:

DELETE FROM relationName [WHERE tuple-boolean-condition]

Example 1:

Delete the employee 'John Smith'

DELETE FROM employee WHERE fname = 'John' AND lname = 'Smith';

Example 2:

Delete all employees from the 'Research' department

DELETE FROM employee WHERE dno IN (SELECT dnumber FROM department WHERE dname = 'Research')

Example 3:

Delete all employees from the 'Research' department have more than 2 dependents

DELETE FROM employee WHERE dno IN (SELECT dnumber FROM department WHERE dname = 'Research') AND ssn IN (SELECT essn FROM dependent GROUP BY essn HAVING COUNT(name) > 2)

Updating tuples in a database

You can update one attribute value in one or more tuples from a relation using the UPDATE command.

Syntax:

UPDATE relationName SET attributeName = expression [WHERE tuple-boolean-condition]

Example 1:

Change the address of employee 'John Smith' to '123 Pike Lane, Austin, TX'

UPDATE employee SET address = '123 Pike Lane, Austin, TX' WHERE fname = 'John' AND lname = 'Smith';

Example 2:

Give all employees in the 'Research' department a 10% raise

UPDATE employee SET salary = 1.1 * salary WHERE dno IN (SELECT dnumber FROM department WHERE dname = 'Research')

Demo

Demo of insert:

See: /home/cs377001/demo/SQL-DML