sql-III-6 manipulating database

Card Set Information

sql-III-6 manipulating database
2012-04-03 14:47:24
sql part III

sql-paart III
Show Answers:

  1. 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
  2. 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
  3. retrieving data

    SELECT column_list FROM table_name
    WHERE condition ;

    • SELECT FirstName, LastName, Phone
    • WHERE State = ‘NH’ AND Status = ‘Active’ ;
  4. 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.
  5. creating views with a modified attribute

  6. 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.
  7. 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.
  8. Adding data one row at a time:

    • 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:
  9. adding data only to selected columns
  10. transferring selected columns and rows between tables
    • SELECT FirstName, LastName
    • WHERE State = ‘ME’

    • UNION
    • SELECT FirstName, LastName
    • 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.

    • WHERE State = ‘ME’ ;
  11. Updating Existing Data
    • SET City = ‘Kankakee’, Telephone = ‘666-6666’
    • WHERE Name = ‘Abe Abelson’ ;

    • SET AreaCode = ‘(619)’
    • WHERE City = ‘Philo’ ;

    • SET City = ‘Rantoul’ ;

    • SET SalePrice = (SalePrice * 1.1)
    • WHERE VendorID IN
    • WHERE VendorName = ‘Cumulonimbus Corporation’) ;
  12. Transferring Data:

    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:

  13. deleting obsolete data
    • WHERE FirstName = ‘David’ AND LastName = ‘Taylor’ ;