Transcript Chapter 2 - Database Design

Section 8 - Manipulating Data
 The
INSERT statement adds rows of data to
the database
 The UPDATE statement changes columns
of existing data
 The DELETE statement deletes rows of
data from the database
1
Add, Change, or Remove Data
 Data
Modification Statements
– INSERT - adds rows to a table
– UPDATE - changes information in a table
– DELETE - deletes rows from a table
 You
can modify only ONE table per SQL
statement
– May be based on data from more than one table
– You can use a SQL subquery
2
INSERT Statement
 Adds
Rows to a Table
 There are two ways to use an insert
statement...
– the VALUES keyword
– the Subquery [SELECT statement]
3
INSERT Syntax
 INSERT INTO
table_name
[ (column_1, column_2, etc.) ]
VALUES
(constant_1, constant_2, etc.)
 In
embedded SQL, you may use variables
instead of constants
4
INSERT Example 1
 If
you are adding values to every column
defined for a table you don't have to specify
the column names
– INSERT INTO publishers
VALUES (
'1662',
'Jardin, Inc.',
'55th Avenue',
'Camden',
'NJ');
5
INSERT Example 2
 If
you add a row using less than all of the
columns defined for a table you must
specify the column names
– INSERT INTO publishers (
(pub_id, pub_name)
VALUES
('1756', 'Healthtext');
 Each
column must match a value in the list
 The columns do not have to be listed in
CREATE TABLE statement order
6
INSERT Example 3
 Using
the SELECT as a subquery...
– INSERT INTO authors
(au_id, au_lname, au_fname)
SELECT ed_id, ed_lname, ed_fname
FROM editors
WHERE ed_lname = 'SMITH';
 This
adds all the editors with the last name
of Smith to the authors table
7
Compatible Structures
 When
Inserting from one table to another...
– The matching columns must be of the same
datatype
– Or they must convert to the correct datatype
(e.g. a character datatype column may be
loaded into a numeric datatype column if all the
data in the character column is numeric)
8
Exercise
 Write
a statement to add a row into the
Titleauthors table
9
Discussion
 If
we want to add all the columns to the
titleauthors table we can skip listing the
column names... as long as we follow the
CREATE TABLE column order
 INSERT INTO
titleauthors
('BU1234', '149-48-9082', 1, 1.00);
10
Exercise
 Write
a statement to add a row into the
Titles table
11
Discussion
 If
wanted to add a row to the titles table
with the minimum required columns
specified… Example: here's how we might
add a new book by Bill Gates...
INSERT INTO titles
(title_id, title, contract)
VALUES
('BU1234', 'The Geek Shall Inherit the Earth", 1);
12
Exercise
 Write
a statement to make the author Albert
Ringer an editor as well
13
Discussion
 Here
we must use a Subquery
 First write the Select statement
SELECT au_id, au_lname, au_fname
FROM authors
WHERE au_lname = 'Ringer'
AND au_fname = 'Albert';
14
Discussion - cont.
 Now
place the correct INSERT statement
above the SELECT
INSERT INTO editors
(ed_id, ed_lname, ed_fname)
SELECT au_id, au_lname, au_fname
FROM authors
WHERE au_lname = 'Ringer'
AND au_fname = 'Albert';
15
UPDATE Statement
 Changes
existing data for...
– A Single row
– A Group of rows
– All rows in a table
 Updates
can come from constants,
variables(in Embedded SQL) or other tables
16
UPDATE Syntax
 UPDATE
table_name
SET column_name1 = expression1,
column_name2 = expression2...
[ WHERE search_conditions ]
 Expressions
must be of the same datatype
as the column you're updating (or convert to
the same datatype)
17
UPDATE Examples
 Change
the price on all the books in the
titles table to $17.99
UPDATE titles
SET price = 17.99;
18
Update Example 2
 Double
the price and add $1,000 to the
advance for all books that have sold more
than 10,000 copies
UPDATE title
SET price = price * 2,
advance = advance + 1000
WHERE ytd_sales > 10000;
19
Exercise
 Reduce
the price of all books by 20% if
they have sold under a thousand books this
year.
20
Discussion
 UPDATE
titles
SET price = price * .80
WHERE ytd_sales < 1000;
This sets the column price to 80% of itself
(20% off) where the year-to-sales is less
than 1000 books sold.
21
Exercise
 The
publisher "New Age Books" has been
relocated to Seattle, Washington. All of the
editors have moved to a Seattle commune at
123 Flower Street. Make the appropriate
changes to the editors table.
22
Discussion
 This
seems like a simple request, change
the city and state to Seattle, WA if the editor
is working for New Age Books, but let's
look at the SQL required to do this.
23
Discussion
 We
know we want to update the editors
table
UPDATE editors
SET city = 'Seattle',
state = 'WA'
...but only for New Age Book editors...
24
Discussion
 Look
at the chart on Page 43 of your book
 First we must find out which books the
editors are editing by looking at the
titleditors table
 Then we need to find out which publisher is
is publishing that book by looking at the
titles table
 The we need to find out which of these
publishers are named 'New Age Books'
25
Discussion

UPDATE editors
SET city = 'SEATTLE',
state = 'WA'
WHERE ed_id IN
(SELECT ed_id FROM titleditors
WHERE title_id IN
(SELECT title_id FROM titles
WHERE pub_id =
(SELECT pub_id FROM publishers
WHERE pub_name = "New Age Books");
26
Discussion
 Read
the statement from the bottom up...
– The pub_id is found for 'New Age Books' and
passed up to the next SELECT statement
– The title_ids for that pub_id are found and
passed up to the next SELECT statement
– The ed_ids associated with those books are
found and passed up to the WHERE clause of
the update statement.
– Now the Update statement changes the city and
state for the appropriate list of editors
27
Removing Data
 Use
the DELETE statement to remove data
from a table
 SYNTAX:
– DELETE FROM table_name
[WHERE search_conditions];
28
Examples
 DELETE
FROM titles
WHERE price = 2.99;
– This deletes all rows from the titles table
where the price of the book is $2.99
 DELETE
FROM titles;
– This deletes all rows from the titles table, but
does not delete the titles table itself
29
Example: Delete with Select
 DELETE
from authors
WHERE au_lname IN
(SELECT ed_lname
FROM editors
WHERE ed_pos = 'Managing');
This deletes all authors who have the same
last name as any Managing Editor
30
Exercise
 Delete
all editors that live in Seattle, WA.
31
Discussion
 DELETE
FROM editors
WHERE city = 'Seattle'
AND state = 'WA';
32
Exercise
 'New Age
Books' has gone out of business.
Remove all relevant information from the
database
33
Discussion
 What's
wrong with this?
DELETE FROM publishers
WHERE pub_name = 'New Age Books';
34
Discussion
 There
is nothing wrong with the statement,
this will remove the 'New Age Books'
publisher from the database, but what about
all the books they were selling? What
about the authors?
 If
we remove the publisher first we will lose
the information we need to reference the
other data that needs to be deleted
35
Discussion
 We
can find out the books published by matching
with pub_id on the titles table, but look at how
many places the title_id is referenced: the
titleauthors table, the titleditors table, and the
salesdetail table.
 Do we remove the editors that were working on
books for New Age Books?
 Do we remove the authors who wrote books
published by New Age Books? What if they've
written for another publisher?
 Do we still want to keep track of sales history
information?
36
Constraints
 Many
DBMS's offer an extention to SQL
called CONSTRAINTS
 These enforce rules which preserve the
referential integrity in a database. For
example, you can specify that a publisher
cannot be deleted, unless all the books that
reference it have been deleted first.
37
Last Slide Section 8
 Please
complete Assignment 7
38