Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Introduction to the Teradata® RDBMS for UNIX® Version 2 Release 2.1 - NCR

NCR Introduction to the Teradata® RDBMS for UNIX® Version 2 Release 2.1 - NCR, 1998. - 315 p.
Download (direct link): inntroduktionteradata1998.pdf
Previous << 1 .. 30 31 32 33 34 35 < 36 > 37 38 39 40 41 42 .. 76 >> Next


INSERT: First Form

Using the INSERT Statement

Before you can query a database, you must first populate its tables with data. The INSERT statement is the means for populating tables with data from within an application program. The bulk data loading programs such as Fastload and Multiload can be used to perform bulk insertions of data into tables.

There are three different forms of the INSERT statement.

In the first form on the INSERT statement, the columns to receive the values are listed separately, enclosed by parentheses.

The values to be added to those columns are also listed separately, enclosed by parentheses, and presented in the same left-to-right order as the columns for which they are intended, preceded by the keyword VALUES.

For example:

INSERT INTO Employee (Name,

EmpNo,

DeptNo,

YrsExp

)

VALUES

('Clarkson B',

10014,

600 ,

3

)

t

Note that the salary and position for Clarkson, presumably unknown, were not added to the table. Those fields in the table are null.

After this INSERT operation, the row for Clarkson in the table looks like this:

EmpNo Name DeptNo JobTitle Salary YrsExp
10014 Clarkson B 600 3

5-16

Introduction to the Teradata RDBMS for UNIX
Data Manipulation

Using the INSERT Statement

INSERT: Second Form

INSERT: Third Form

In the second form of the INSERT statement, you need only list the field values, but you must present them in the same left-to-right order the columns were defined in the CREATE TABLE statement. The list must account for the position of a column whether data is entered or not. To achieve this, the syntax requires you to indicate the position of a column with a comma.

Suppose you wanted to add an employee to the Employee table whose position and salary are not known. The INSERT statement looks like this:

INSERT INTO Employee VALUES (10015,

'Goldsmith H',

600 ,

5

In the third form of the INSERT statement, you use an embedded SELECT statement to insert values from one table into another.

Suppose you need to insert employee information into a new table you have created called PROMOTION. In the example provided below, you want information for all employees having more than 10 years experience with the organization.

Note that column values must be provided in the order in which columns are defined in the CREATE TABLE statement for the Promotion table.

The INSERT statement looks like this:

INSERT INTO Promotion SELECT Name,

DeptNo,

YrsExp FROM Employee WHERE YrsExp > 10

Introduction to the Teradata RDBMS for UNIX

5-17
Data Manipulation

Using the UPDATE Statement

Using the UPDATE Statement

Introduction

Most databases require frequent updating in order to remain current. The UPDATE statement provides the capability of changing the information in existing rows.

How to Change Rows Using the UPDATE Statement

The UPDATE statement allows you to modify existing columns in one or more rows of a table. You can use the UPDATE statement both in interactive queries and in embedded SQL applications.

Suppose you want to add position and salary data for the employees named Clarkson and Goldsmith whom you inserted into the Employee table in earlier examples.

The UPDATE statements look like these:

UPDATE Employee

SET JobTitle = 'Inspector',

Salary = 32000 WHERE EmpNo = 10 014

UPDATE Employee

SET JobTitle = 'Assembler',

Salary = 25000 WHERE EmpNo = 10 015 t

Having made these updates, you must now update the Department table to increment the employee count.

The UPDATE statement looks like this:

UPDATE Department

SET EmpCount = EmpCount +2

WHERE DeptName = 'Manufacturing'

The following UPDATE statement gives all employees a ten percent raise:

UPDATE EMPLOYEE

SET Salary = Salary * 1.1

ALL

5-18

Introduction to the Teradata RDBMS for UNIX
Data Manipulation

Using the DELETE Statement to Delete Rows from a Table

Introduction

How to Delete Rows from a Table

Using the DELETE Statement to Delete Rows from a Table

You will need to remove rows from your database from time to time. The DELETE statement provides this capability. The DELETE statement deletes one or more rows from a table. You can use the DELETE statement both in interactive queries and in embedded SQL applications.

As in the UPDATE statement, you use a WHERE clause to determine which rows are affected by a DELETE statement.

Suppose Employee T Smith has left the company and you want to delete his information from the database. This requires two updates:

Step Action
1 Delete the employee from the Employee table
2 Decrement the count in the Department table for the department of T Smith by one

The statements to achieve these actions look like these:

DELETE FROM Employee WHERE Name = 'Smith T'

UPDATE Department

SET EmpCount = EmpCount - 1

WHERE DeptNo = 500

Introduction to the Teradata RDBMS for UNIX
Previous << 1 .. 30 31 32 33 34 35 < 36 > 37 38 39 40 41 42 .. 76 >> Next