What the difference between the alter table command and the update cammand illia

Using SQL Queries to Insert, Update, Delete, and View Data

You should know how to:
Run a script to create database tables
automatically
Insert data into database tables
Create database transactions and commit data to the database
Create search conditions in SQL queries
Update and delete database records and truncate tables

Using the INSERT Command
Basic syntax for inserting data into every column: INSERT INTO tablename
VALUES (column1_value, column2_value, … );
– Must list values in same order as in CREATE TABLE – If a data value is unknown, must type NULL
– If character data, must use single quotation marks
– Value in quotations is case sensitive
Basic syntax for inserting into selected columns
INSERT INTO tablename (columnname1, columnname2, …)

VALUES (column1_value, column2_value, … );

Cannot insert a foreign key value unless the corresponding primary key is in the primary table.

5

Inserting Date and Interval Values

Oracle stores LOB data in separate (alternate) physical location from other types of data
LOB locator needs to be created to
– Hold information that identifies LOB data type, and – Point to alternate memory location
Syntax for creating blob locator
EMPTY_BLOB()

8

Purpose of transaction processing
– Enable users to see consistent view of database
– Preventing users from viewing or updating data that are part of a pending (uncommitted) transaction
New transaction begins when SQL*Plus started and command executed
Transaction ends when current transaction committed

ROLLBACK command restores database to point		10

12

Defining Search Expressions

NUMBER example: WHERE f_id = 1
Character data example: WHERE s_class = 'SR'DATE example
WHERE s_dob = TO_DATE('01/01/1980', ‘MM/DD/YYYY')

Updating Table Rows
UPDATE action query syntax
UPDATE tablename
SET column1 = new_value1, column2 = new_value2, … WHERE search condition;

Child row: a row containing a value as foreign key – Cannot delete row if it has child row. In other words, you cannot delete a “parent” row …
• Unless you, first, delete row in which foreign key value exists
– Cannot delete LOCATION row for loc_id = 9 unless you delete FACULTY row for f_id = 1
FACULTY

18

Using Sequences (continued)

DUAL
– Simple table in the SYSTEM user schema
– More efficient to retrieve pseudocolumns from DUAL SELECT sequence_name.NEXTVAL
FROM DUAL;
DBMS uses user sessions
– To ensure that all sequence users receive unique sequence numbers