-
manipulating Database Data
Dealing with data
Retrieving the data you want from table
Displaying only selected information from one or more tables
Updating the information in tables avd views
Adding a new row to table
Changing some or all of the data in a table row
Deleting a table row
-
you want to do four things with data:
- 1. add it to a table
- 2. retrieve and display it.
- 2. change it
- 4. delete it from table
-
retrieving data
SELECT column_list FROM table_name
WHERE condition ;
SELECT * FROM CUSTOMER ;
- SELECT FirstName, LastName, Phone
- FROM CUSTOMER
- WHERE State = ‘NH’ AND Status = ‘Active’ ;
-
creating views
- You can manipulate a view just as you can manipulate a real table. The difference is that a view’s data doesn’t have an independent existence. The view derives its data from the table or tables from which you draw the view’s
- columns. Each application can have its own unique views of the same data.
-
creating views with a modified attribute
-
updating views
After you create a table, that table is automatically capable of accommodating insertions, updates, and deletions. Views don’t necessarily exhibit the same capability. If you update a view, you’re actually updating its underlying table. Here are a few potential problems you may encounter when you update views:
? Some views may draw components from two or more tables. If you update such a view, the underlying tables may not be updated properly.
? A view may include an expression in a SELECT list. Since expressions don’t map directly to rows in tables, your DBMS won’t know how to update an expression.
-
Adding new data
Every database table starts out empty. After you create a table, either by using SQL’s DDL or a RAD tool, that table is nothing but a structured shell containing no data. To make the table useful, you must put some data into it. You may or may not have that data already stored in digital form. Your data may appear in one of the following forms:
- ? Not yet compiled in any digital format: If your data is not already in digital form, someone will probably have to enter the data manually, one record at a time. You can also enter data by using optical scanners and voice recognition systems, but the use of such devices for data entry is
- relatively rare.
- ? Compiled in some sort of digital format: If your data is already in digital form but perhaps not in the format of the database tables that you use, you need to translate the data into the appropriate format and then insert the data into the database.
- ? Compiled in the correct digital format: If your data is already in digital form and in the correct format, you’re ready to transfer it to a new database.
-
Adding data one row at a time:
INSERT
- INSERT INTO table_1 [(column_1, column_2, ..., column_n)]
- VALUES (value_1, value_2, ..., value_n) ;
Asindicatedbythesquarebrackets([]),thelistingofcolumnnamesis optional. The default column list order is the order of the columns in the table. If you put the VALUES in the same order as the columns in the table, these elements go into the correct columns — whether you explicitly specify those columns or not. If you want to specify the VALUES in some order other than the order of the columns in the table, you must list the column names, putting the columns in an order that corresponds to the order of the VALUES.
- To enter a record into the CUSTOMER table, for example, use the following syntax:
-
adding data only to selected columns
-
transferring selected columns and rows between tables
- SELECT FirstName, LastName
- FROM PROSPECT
- WHERE State = ‘ME’
- UNION
- SELECT FirstName, LastName
- FROM CUSTOMER
- WHERE State = ‘ME’ ;
- ? The SELECT statements specify that the columns included in the result table are FirstName and LastName.
- ? The WHERE clauses restrict the rows included to those with the value ‘ME’ in the State column.
- ? The State column isn’t included in the results table but is present in both the PROSPECT and CUSTOMER tables.
- ? The UNION operator combines the results from the SELECT statement on PROSPECT with the results of the SELECT on CUSTOMER, deletes any duplicate rows, and then displays the result.
- INSERT INTO PROSPECT
- SELECT * FROM CUSTOMER
- WHERE State = ‘ME’ ;
-
-
Transferring Data:
INSERT
UPDATE
MERGE
For example, consider the VetLab database that I describe in Chapter 5. Suppose some people in the EMPLOYEE table are salespeople who have taken orders, whereas others are nonsales employees or salespeople who have not yet taken an order. The year just concluded has been profitable, and you want to share some of that success with the employees. You decide to give a bonus of $100 to everyone who has taken at least one order and a bonus of $50 to everyone else. First, you create a BONUS table and insert into it a record for each employee who appears at least once in the ORDERS table, assigning each record a default bonus value of $100.
Next, you want to use the MERGE statement to insert new records for those employees who have not taken orders, giving them $50 bonuses. Here’s some code that builds and fills the BONUS table:
-
deleting obsolete data
- DELETE FROM CUSTOMER
- WHERE FirstName = ‘David’ AND LastName = ‘Taylor’ ;
|
|
