Database Reference
In-Depth Information
EXAMPLE 28
Change the street address of customer 524 to 1445 Rivard.
113
UPDATE Customer
SET Street=
'
1445 Rivard
'
WHERE CustomerNum=
'
524
'
;
EXAMPLE 29
Add a new sales rep to the Rep table. Her number is 16; her name is Sharon Rands; and her address is 826
Raymond, Altonville, FL 32543. She has not yet earned any commission, but her commission rate is 5% (0.05).
INSERT INTO Rep
VALUES
(
'
16
'
,
'
Rands
'
,
'
Sharon
'
,
'
826 Raymond
'
,
'
Altonville
'
,
'
FL
'
,
'
32543
'
,0.00,0.05)
;
EXAMPLE 30
Delete any row in the OrderLine table in which the part number is BV06.
DELETE
FROM OrderLine
WHERE PartNum=
'
BV06
'
;
EXAMPLE 31
Create a new table named SmallCust consisting of all fields from the Customer table and those rows in which
the credit limit is less than or equal to $7,500.
SELECT *
INTO SmallCust
FROM Customer
WHERE CreditLimit
<
=7500
;
Oracle and MySQL:
CREATE TABLE SmallCust
(CustomerNum CHAR(3),
CustomerName CHAR(35),
Street CHAR(15),
City CHAR(15),
State CHAR(2),
Zip CHAR(5),
Balance DECIMAL(8,2),
CreditLimit DECIMAL(8,2),
RepNum CHAR(2))
;
INSERT INTO SmallCust
SELECT *
FROM Customer
WHERE CreditLimit
<
=7500
;