Transcript Action Queries

Action Queries
CS 320
Review: SQL Command Types
 Data Definition Language (DDL)
 Used to create and modify database objects
 Data Manipulation Language (DML)
 Used to insert, update, delete, and view the
data in database objects
 Control Language (CL)
 Used to create database transactions
SQL INSERT command

2 approaches:
 Insert
a value for every table field
 Insert values for selected table fields

Basic rules for both approaches:
 You
must insert a unique value for primary
key fields
 You must provide a value for fields with NOT
NULL constraints
Inserting a value for every field
INSERT INTO tablename
VALUES(value1, value2, …)


You must include a value for every field
You must list the values in the correct order
 To find the correct order, look at the table in the Object Browser

Why?
 The DBMS is expecting certain data types for each field. If the
data types are not correct an error occurs
INSERT INTO candy_product VALUES
(6, 'Chocolate Covered Ants', 6.25, 7.50)
Inserting values for selected fields

Syntax:
INSERT INTO tablename (field1, field2, …)
VALUES(value1, value2, …)
INSERT INTO candy_customer (cust_id, cust_name)
VALUES (100, 'Joe Jones')



You can insert the field names and corresponding values in any order,
but …
The names specified before the VALUES clause and the corresponding
values following the VALUES clause must be in the same order!
This is the preferred way to do an insert


Easier to maintain – makes it easy to see what fields are being inserted
into
Your command won't "break" if someone adds a new field to the table
How do you specify different data
type values?

Numeric values (SMALLINT, INT, BIGINT, FLOAT,
DOUBLE, DECIMAL, …)


Type the number (3, 2.75)
Character values (CHAR, VARCHAR)

Enclose characters in single quotation marks ('Joline',
'Caramel Crunch')
For character fields with embedded quotation marks, type the
escape backslash (\) followed by the quotation mark:
('Joline\'s White Chocolate Fudge')

How do you specify date data values?

Just as you did in search conditions:
 As

a quoted string in YYYY-MM-DD format
Example
INSERT INTO candy_purchase VALUES
(1, 1, 5, '2011-09-06', '2011-09-06', 3.5, 'PAID');
Review: Surrogate Keys
A surrogate key is a primary key field
created solely for the purpose of being a
unique identifier in a database
 You create surrogate key columns in
MySQL using an AUTO_INCREMENT
column:

CREATE TABLE candy_customer
(cust_id BIGINT AUTO_INCREMENT PRIMARY KEY,
cust_name VARCHAR(30))
Inserting Records into
AUTO_INCREMENT Columns
 Use the INSERT command format that specifies
selected field names
 Omit inserting the surrogate key value

The DBMS automatically inserts the next value in the
sequence
INSERT INTO candy_product
(prod_desc, prod_cost, prod_price)
VALUES
('Gummy Worms', 8.50, 12.50);
Inserting Foreign Key Values

Insert them just like any other value, except …

You MUST have inserted the parent record first!
CANDY_PURCHASE
Child
record
Parent
record
CANDY_PRODUCT
PROD_ID
PROD_DESC
PURCH_ID
PROD_COSTPROD_PRICE
1 Celestial Cashew Crunch
$
7.45
$
10.00
2 Unbrittle Peanut Paradise
$
5.75
$
9.00
3 Mystery Melange
$
7.75
$
10.50
4 Millionaire’s Macadamia Mix
$
12.50
$
16.00
5 Nuts Not Nachos
$
6.25
$
9.50
PROD_ID
CUST_ID
PURCH_
DATE
PURCH_
DELIVERY_ PURCH_
DATE
POUNDS
PURCH_
STATUS
1
1
5 28-Oct-04
28-Oct-04
3.5 PAID
2
2
6 28-Oct-04
30-Oct-04
15 PAID
3
1
9 28-Oct-04
28-Oct-04
2 PAID
3
3
9 28-Oct-04
28-Oct-04
3.7 PAID
4
3
2 28-Oct-04
5
1
7 29-Oct-04
29-Oct-04
3.7 NOT PAID
5
2
7 29-Oct-04
29-Oct-04
1.2 NOT PAID
5
3
7 29-Oct-04
29-Oct-04
4.4 NOT PAID
6
2
7 29-Oct-04
7
2
10 29-Oct-04
14 NOT PAID
7
5
10 29-Oct-04
4.8 NOT PAID
8
1
4 29-Oct-04
8
5
4 29-Oct-04
9
5
4 29-Oct-04
3.7 PAID
3 PAID
29-Oct-04
1 PAID
7.6 PAID
29-Oct-04
3.5 NOT PAID
Inserting NULL values
If inserting a value for every field, use the
keyword NULL
 If inserting values for selected fields, omit
the field

INSERT INTO candy_product VALUES
(7, 'Everlasting Gopstoppers', NULL, NULL);
INSERT INTO candy_purchase (prod_id, prod_desc)
VALUES (7, 'Everlasting Gopstoppers');
Updating Records

General syntax:

Notes:


UPDATE tablename
SET field1 = new_value,
field2 = new_value, …
WHERE search_condition(s);
Records can be updated in only one table at a time
You can update multiple records in the same table if they all
match the search condition
UPDATE candy_product
SET prod_desc = 'Chocolate Obsession',
prod_price = 8.5
WHERE prod_id = 6;
UPDATE candy_product
SET prod_price = prod_price * 1.1
WHERE prod_price > 10;
Deleting Records
 Syntax:
DELETE FROM tablename
WHERE search_condition;
DELETE FROM candy_product
WHERE prod_id = 6;
 Notes:
Deletes multiple records if search condition
specifies multiple records
 If the search condition is omitted, all table
records are deleted
 You can’t delete a record in a parent table
that is referenced as a foreign key

Safe Mode in MySQL

By default, MySQL executes UPDATE and
DELETE commands in “Safe Mode”
 Forbids
statements in which the WHERE clause uses
a non-key field in the search condition
 Forbids statements in which the WHERE clause is
omitted
Disabling Safe Mode
Click Edit – Preferences
 Select the SQL Editor tab, then clear the
“Safe Updates” check box
 Click OK


Click Query – Reconnect to Server to
make change take affect
Fully-populated CANDY database tables
CANDY_CUSTOMER
CUST_ID CUST_NAME
CUST_TYPE CUST_ADDR
CUST_ZIP CUST_PHONE CUST_USERNAME CUST_PASSWORD
1 Jones, Joe
P
1234 Main St.
91212 434-1231
jonesj
2 Armstrong,Inc.
R
231 Globe Blvd.
91212 434-7664
armstrong
3 Sw edish Burgers
R
1889 20th N.E.
91213 434-9090
sw edburg
4 Pickled Pickles
R
194 CityView
91289 324-8909
pickpick
5 The Candy Kid
W
2121 Main St.
91212 563-4545
kidcandy
6 Waterman, Al
P
23 Yankee Blvd.
91234
w ateral
7 Bobby Bon Bons
R
12 Nichi Cres.
91212 434-9045
bobbybon
8 Crow sh, Elias
P
7 77th Ave.
91211 434-0007
crow el
9 Montag, Susie
P
981 Montview
91213 456-2091
montags
10 Columberg Sw eets W
239 East Falls
91209 874-9092
columsw e
1234
3333
2353
5333
2351
8900
3011
1033
9633
8399
CANDY_PURCHASE
CANDY_CUST_TYPE
CUST_TYPE_ID
CUST_TYPE_DESC
PURCH_ID
PROD_ID
CUST_ID
PURCH_
DATE
PURCH_
DELIVERY_ PURCH_
DATE
POUNDS
PURCH_
STATUS
P
Private
1
1
5 28-Oct-04
28-Oct-04
3.5 PAID
R
Retail
2
2
6 28-Oct-04
30-Oct-04
15 PAID
W
Wholesale
3
1
9 28-Oct-04
28-Oct-04
2 PAID
3
3
9 28-Oct-04
28-Oct-04
3.7 PAID
4
3
2 28-Oct-04
5
1
7 29-Oct-04
29-Oct-04
3.7 NOT PAID
5
2
7 29-Oct-04
29-Oct-04
1.2 NOT PAID
29-Oct-04
4.4 NOT PAID
CANDY_PRODUCT
PROD_ID
PROD_DESC
PROD_COSTPROD_PRICE
3.7 PAID
1 Celestial Cashew Crunch
$
7.45
$
10.00
5
3
7 29-Oct-04
2 Unbrittle Peanut Paradise
$
5.75
$
9.00
6
2
7 29-Oct-04
3 Mystery Melange
$
7.75
$
10.50
7
2
10 29-Oct-04
14 NOT PAID
4 Millionaire’s Macadamia Mix
$
12.50
$
16.00
7
5
10 29-Oct-04
4.8 NOT PAID
5 Nuts Not Nachos
$
6.25
$
9.50
8
1
4 29-Oct-04
8
5
4 29-Oct-04
9
5
4 29-Oct-04
3 PAID
29-Oct-04
1 PAID
7.6 PAID
29-Oct-04
3.5 NOT PAID
Test Yourself: Assume you have a fullypopulated CANDY database. What will
happen when you execute the following
command?
INSERT INTO candy_cust_type VALUES
('W', 'Wholesale')
a.
b.
The command will succeed
The command will fail
Test Yourself: Assume you have a fullypopulated CANDY database. What will
happen when you execute the following
command?
INSERT INTO candy_cust_type VALUES
('W', 'Wholesale')
a.
b.
The command will succeed
The command will fail
Test Yourself: Assume you have a fullypopulated CANDY database. What will
happen when you execute the following
command?
INSERT INTO candy_purchase VALUES
(10, 5, 2, '2011-07-14', 75, 'NOT PAID');
a.
b.
The command will succeed
The command will fail
Test Yourself: Assume you have a fullypopulated CANDY database. What will
happen when you execute the following
command?
INSERT INTO candy_purchase VALUES
(10, 5, 2, '2011-07-14', 75, 'NOT PAID');
a.
b.
The command will succeed
The command will fail
PURCH_ID
PROD_ID
CUST_ID
PURCH_
DATE
PURCH_
DELIVERY_ PURCH_
DATE
POUNDS
PURCH_
STATUS
1
1
5 28-Oct-04
28-Oct-04
3.5 PAID
2
2
6 28-Oct-04
30-Oct-04
15 PAID
3
1
9 28-Oct-04
28-Oct-04
2 PAID
3
3
9 28-Oct-04
28-Oct-04
3.7 PAID
4
3
2 28-Oct-04
5
1
7 29-Oct-04
29-Oct-04
3.7 NOT PAID
5
2
7 29-Oct-04
29-Oct-04
1.2 NOT PAID
3.7 PAID
Test Yourself: Assume you have a fullypopulated CANDY database. What will
happen when you execute the following
command?
INSERT INTO candy_purchase VALUES
(10, 5, 2, '2011-07-14', '2011-07-15', 'NOT PAID', 12);
a.
b.
The command will succeed
The command will fail
Test Yourself: Assume you have a fullypopulated CANDY database. What will
happen when you execute the following
command?
INSERT INTO candy_purchase VALUES
(10, 5, 2, '2011-07-14', '2011-07-15', 'NOT PAID', 12);
a.
b.
The command will succeed
The command will fail
PURCH_ID
PROD_ID
CUST_ID
PURCH_
DATE
PURCH_
DELIVERY_ PURCH_
DATE
POUNDS
PURCH_
STATUS
1
1
5 28-Oct-04
28-Oct-04
3.5 PAID
2
2
6 28-Oct-04
30-Oct-04
15 PAID
3
1
9 28-Oct-04
28-Oct-04
2 PAID
3
3
9 28-Oct-04
28-Oct-04
3.7 PAID
4
3
2 28-Oct-04
5
1
7 29-Oct-04
29-Oct-04
3.7 NOT PAID
5
2
7 29-Oct-04
29-Oct-04
1.2 NOT PAID
3.7 PAID
Test Yourself: Assume you have a fullypopulated CANDY database. What will
happen when you execute the following
command?
INSERT INTO candy_purchase (purch_id, cust_id, prod_id,
purch_pounds, purch_date, purch_delivery_date,
purch_status)
VALUES (10, 5, 2, 12, '2011-07-14', '2011-07-15', 'NOT
PAID');
a.
b.
The command will succeed
The command will fail
Test Yourself: Assume you have a fullypopulated CANDY database. What will
happen when you execute the following
command?
INSERT INTO candy_purchase (purch_id, cust_id, prod_id,
purch_pounds, purch_date, purch_delivery_date,
purch_status)
VALUES (10, 5, 2, 12, '2011-07-14', '2011-07-15', 'NOT
PAID');
a.
b.
The command will succeed
The command will fail
Test Yourself: Assume you have a fullypopulated CANDY database. How many
records will the following command delete?
DELETE FROM candy_customer;
a.
b.
c.
d.
3
4
10
None of the above
Test Yourself: Assume you have a fullypopulated CANDY database. How many
records will the following command delete?
DELETE FROM candy_customer;
a.
b.
c.
d.
3
4
10
None of the above
Your Turn #1: INSERT

If necessary, run the CANDY script to create a fully-populated
CANDY database. Then, create a new MySQL script that
contains the commands for the following action queries:
1.
2.
3.
Write a command to add a new record for yourself in the
CANDY_CUSTOMER table. Use the auto increment field to
generate the surrogate key value, and use values of your choice
for other table fields..
Write a command to add a new product to the CANDY_PRODUCT
table. Use the auto increment field to generate the surrogate key
value, and use values of your choice for other table fields.
Write a command to add a new purchase record to the
CANDY_PURCHASE table that references the new customer and
product records that you just inserted. (You will need to run a
SELECT command to determine the CUST_ID and PROD_ID
foreign key values). Use values of your choice for other table
fields, except insert values of NULL for the PURCH_DATE and
PURCH_DELIVERY_DATE fields.
Your Turn #1: Solution
USE candy;
INSERT INTO candy_customer (cust_name, cust_type,
cust_addr, cust_zip, cust_phone, cust_username,
cust_password)
VALUES ('Joline Morrison', 'P', '34 Mountain Shadow
Road', '81122', '7155555555', 'morrisjp', '1111');
INSERT INTO candy_product (prod_desc, prod_cost,
prod_price) VALUES ('Fudge Mountain', 7.50, 8.50);
INSERT INTO candy_purchase (purch_id, prod_id,
cust_id, purch_pounds, purch_status)
VALUES (10, 6, 11, 200, 'PAID');
Your Turn #2: UPDATE

Add the following commands to the MySQL script you
just created:
1.
2.
3.
Write a command to update the CUST_ZIP field of your
CANDY_CUSTOMER record to an alternate value.
Write a command to update the PURCH_DATE field of your
CANDY_PURCHASE record so it displays today's date. (Use
the CURRENT_DATE() function to retrieve the current system
date.)
Write a command to increase the PROD_COST and
PROD_PRICE values of your CANDY_PRODUCT record by
$1. Use an addition arithmetic operation to do this.
Your Turn #2: Solution
UPDATE candy_customer
SET cust_zip = '81113'
WHERE cust_id = 11;
UPDATE candy_purchase
SET purch_date = CURRENT_DATE()
WHERE purch_id = 10
AND prod_id = 6;
UPDATE candy_product
SET prod_cost = prod_cost + 1,
prod_price = prod_price + 1
WHERE prod_id = 6;
Your Turn #3: DELETE

Add the following commands to the MySQL script you
just created:
1.
Write commands to delete all of the records you inserted. Be
sure to delete the records in the correct order so you delete
child records before you delete parent records.
Your Turn #3: Solution
DELETE FROM candy_purchase
WHERE purch_id = 10
AND prod_id = 6;
DELETE FROM candy_product
WHERE prod_id = 6;
DELETE FROM candy_customer
WHERE cust_id = 11;