INSERT: Inserts data (rows) into an existing table using the INSERT, INTO, and VALUES keywords.
- The INTO keyword indicates the table name.
- The VALUES keyword indicates what to insert.
- You can insert a partial row or an entire row.
- The NULL keyword indicates no value.
QueryInsertRow: Insert an entire row into the database. Enter an order for Jacob Lin. The new order needed for Jacob Lin is D1002, a digital cable plan.
| CustomerID | PlanID | OrderDate | CancelationDate |
|---|---|---|---|
| 1 | D1002 | 11/11/2009 | |
| 10 | D1002 | 3/15/2008 | 3/30/2011 |
| 10 | D1003 | 3/15/2008 | |
| 10 | D2202 | 1/1/2010 | |
| 2 | W1001 | 3/15/2011 | |
| 2 | L2003 | 3/15/2011 | |
| 2 | I1001 | 3/20/2011 | |
| 3 | L2002 | 7/10/2010 | |
| 3 | L2001 | 7/10/2010 | |
| 4 | L2004 | 2/15/2011 | |
| 7 | L2004 | 5/14/2011 | |
| 6 | D1002 | 11/4/2010 | |
| 6 | D1101 | 11/4/2010 | |
| 5 | I1001 | 6/6/2009 | |
| 8 | I1001 | 3/14/2009 | |
| 6 | I1001 | 11/4/2010 | |
| 7 | L2001 | 5/13/2011 | |
| 4 | L2001 | 2/15/2011 | |
| 8 | L2001 | 3/17/2009 | |
| 8 | L2002 | 3/17/2009 | |
| 9 | L2003 | 6/11/2010 |
Two SQL statements are needed; one to locate Jacob Lin's customer id; one to insert Jacob Lin's order.
SELECT CustomerID
FROM Customer
WHERE FirstName = 'Jacob' AND LastName = 'Lin';
INSERT INTO Orders (CustomerID, PlanID, OrderDate, CancelationDate)
VALUES (4, 'D1002', '02/24/11', NULL);
Note: MYSQL users must use the following alternate insert statement due to datatype differences:
INSERT INTO Orders (CustomerID, PlanID, OrderDate, CancelationDate)
VALUES (4, 'D1002', '2011/02/24', NULL);
Results:
| CustomerID | PlanID | OrderDate | CancelationDate |
|---|---|---|---|
| 1 | D1002 | 11/11/2009 | |
| 2 | W1001 | 3/15/2011 | |
| 2 | L2003 | 3/15/2011 | |
| 2 | I1001 | 3/20/2011 | |
| 3 | L2002 | 7/10/2010 | |
| 3 | L2001 | 7/10/2010 | |
| 4 | L2004 | 2/15/2011 | |
| 4 | L2001 | 2/15/2011 | |
| 4 | D1002 | 2/24/2011 | |
| 5 | I1001 | 6/6/2009 | |
| 6 | D1002 | 11/4/2010 | |
| 6 | D1101 | 11/4/2010 | |
| 6 | I1001 | 11/4/2010 | |
| 7 | L2001 | 5/13/2011 | |
| 7 | L2004 | 5/14/2011 | |
| 8 | I1001 | 3/14/2009 | |
| 8 | L2001 | 3/17/2009 | |
| 8 | L2002 | 3/17/2009 | |
| 9 | L2003 | 6/11/2010 | |
| 10 | D2202 | 1/1/2010 | |
| 10 | D1003 | 3/15/2008 | |
| 10 | D1002 | 3/15/2008 | 3/30/2008 |
note: Jacob Lin now has another record, CustomerID 4, PlanID D1002, OrderDate 2/24/2011.
note: If we don't have all of the fields of the record, the missing fields will be inserted with NULL values.
QueryTransferData: Create query to transfer data from one table to another table. Use SELECT INTO to select records from an existing table and transfer to a new table:
SELECT *
INTO NewCustomer
FROM Customer;
Then, transfer the records to the other table
INSERT INTO Customer2 (CustomerID, FirstName, LastName, HomePhone, Address, State, City, PostalCode)
SELECT CustomerID, FirstName, LastName, HomePhone, Address, State, City, PostalCode
FROM Customer;
UPDATE: Changes data in the database by indicating the columns to be updated and the logic for selecting rows. Can update one row or all rows.
SET: Assigns new values to indicated columns.
QueryUpdate: Create query to update the long distance service Plan Descriptions:
| PlanID | PlanName | PlanDescription | PlanPrice | DiscountedPrice |
|---|---|---|---|---|
| D1002 | Digital Cable | Digital cable plan | $40.00 | $35.00 |
| D1003 | Digital Phone | Cell phone | $40.00 | $35.00 |
| D1101 | Data IP Services A | Plan A Data/IP services | $100.00 | $85.00 |
| D2202 | Data IP Services B | Plan B Data/IP services | $150.00 | $125.00 |
| E1001 | Premium e-mail | $9.99 | $6.99 | |
| I1001 | Internet | Internet/email | $19.99 | $16.99 |
| L2001 | Local Phone | In state calling | $39.00 | $29.99 |
| L2002 | Long Distance E | Plan A Out of state per/min | $0.15 | $0.13 |
| L2003 | Long Distance W | Plan B Out of state per/min | $0.11 | $0.09 |
| L2004 | Long Distance N | Plan C Out of state per/min | $0.18 | $0.16 |
| W1001 | Web Hosting | hosting, site creation | $19.99 | $16.99 |
Type three UPDATE statements for the long distance plan descriptions
UPDATE ServicePlans
SET PlanName = 'Long Distance (East)'
WHERE PlanName = 'Long Distance E';
UPDATE ServicePlans
SET PlanName = 'Long Distance (West)'
WHERE PlanName = 'Long Distance W;
UPDATE ServicePlans
SET PlanName= 'Long Distance (North)'
WHERE PlanName = 'Long Distance N';
Type the SELECT statement to show the Service Plan table
SELECT *
FROM ServicePlans;
Results:
| PlanID | PlanName | PlanDescription | PlanPrice | DiscountedPrice |
|---|---|---|---|---|
| D1002 | Digital Cable | Digital cable plan | $40.00 | $35.00 |
| D1003 | Digital Phone | Cell phone | $40.00 | $35.00 |
| D1101 | Data IP Services A | Plan A Data/IP services | $100.00 | $85.00 |
| D2202 | Data IP Services B | Plan B Data/IP services | $150.00 | $125.00 |
| E1001 | Premium e-mail | $9.99 | $6.99 | |
| I1001 | Internet | Internet/email | $19.99 | $16.99 |
| L2001 | Local Phone | In state calling | $39.00 | $29.99 |
| L2002 | Long Distance (East) | Plan A Out of state per/min | $0.15 | $0.13 |
| L2003 | Long Distance (West) | Plan B Out of state per/min | $0.11 | $0.09 |
| L2004 | Long Distance (North) | Plan C Out of state per/min | $0.10 | $0.09 |
| W1001 | Web Hosting | hosting, site creation | $19.99 | $16.99 |
note: Do not use the WHERE keyword when you want to update every record in the table.
DELETE: The delete keyword deletes entire rows from a database. DELETE can delete single rows, multiple rows, or all rows from a table.
QueryDelete: Create a query using the DELETE statement to delete PlanID D1101 and replace all customers having PlanID D1101 to PlanID D2202:
Query to find out who has PlanID D1101 and their customer number. Then, write a query to display their customer information.
SELECT *
FROM Customer
WHERE CustomerID
IN
(SELECT CustomerID
FROM Orders
WHERE PlanID IN ('D1101'))
Results:
| CustomerID | FirstName | LastName | HomePhone | Address | State | City | PostalCode |
|---|---|---|---|---|---|---|---|
| 6 | Jill | Stephens | (813) 215-5551 | 1544 33rd Ave E | FL | Miami | 98911 |
The next step is to change the above CustomerID 6 PlanID from D1101 to D2202. Then, delete PlanID D1101 from the PlanID table.
The following table shows the Orders table:
| CustomerID | PlanID | OrderDate | CancelationDate |
|---|---|---|---|
| 1 | D1002 | 11/11/2009 | |
| 2 | W1001 | 3/15/2011 | |
| 2 | L2003 | 3/15/2011 | |
| 2 | I1001 | 3/20/2011 | |
| 3 | L2002 | 7/10/2010 | |
| 3 | L2001 | 7/10/2010 | |
| 4 | L2004 | 2/15/2011 | |
| 4 | L2001 | 2/15/2011 | |
| 4 | D1002 | 2/24/2011 | |
| 5 | I1001 | 6/6/2009 | |
| 6 | D1002 | 11/4/2010 | |
| 6 | D1101 | 11/4/2010 | |
| 6 | I1001 | 11/4/2010 | |
| 7 | L2001 | 5/13/2011 | |
| 7 | L2004 | 5/14/2011 | |
| 8 | I1001 | 3/14/2009 | |
| 8 | L2001 | 3/17/2009 | |
| 8 | L2002 | 3/17/2009 | |
| 9 | L2003 | 6/11/2010 | |
| 10 | D2202 | 1/1/2010 | |
| 10 | D1003 | 3/15/2008 | |
| 10 | D1002 | 3/15/2008 | 3/30/2008 |
Update the PlanID
UPDATE Orders
SET PlanID = 'D2202'
WHERE PlanID = 'D1101';
Delete PlanID D1101 from the Service Plan Table. The Service Plan Table before the DELETE:
| PlanID | PlanName | PlanDescription | PlanPrice | DiscountedPrice |
|---|---|---|---|---|
| D1002 | Digital Cable | Digital cable plan | $40.00 | $35.00 |
| D1003 | Digital Phone | Cell phone | $40.00 | $35.00 |
| D1101 | Data IP Services A | Plan A Data/IP services | $100.00 | $85.00 |
| D2202 | Data IP Services B | Plan B Data/IP services | $150.00 | $125.00 |
| E1001 | Premium e-mail | $9.99 | $6.99 | |
| I1001 | Internet | Internet/email | $19.99 | $16.99 |
| L2001 | Local Phone | In state calling | $39.00 | $29.99 |
| L2002 | Long Distance (East) | Plan A Out of state per/min | $0.15 | $0.13 |
| L2003 | Long Distance (West) | Plan B Out of state per/min | $0.11 | $0.09 |
| L2004 | Long Distance N | Plan C Out of state per/min | $0.18 | $0.16 |
| W1001 | Web Hosting | hosting, site creation | $19.99 | $16.99 |
Now, delete the PlanID D1101
DELETE FROM ServicePlans
WHERE PlanID = 'D1101';
Results:
| PlanID | PlanName | PlanDescription | PlanPrice | DiscountedPrice |
|---|---|---|---|---|
| D1002 | Digital Cable | Digital cable plan | $40.00 | $35.00 |
| D1003 | Digital Phone | Cell phone | $40.00 | $35.00 |
| D2202 | Data IP Services B | Plan B Data/IP services | $150.00 | $125.00 |
| E1001 | Premium e-mail | $9.99 | $6.99 | |
| I1001 | Internet | Internet/email | $19.99 | $16.99 |
| L2001 | Local Phone | In state calling | $39.00 | $29.99 |
| L2002 | Long Distance (East) | Plan A Out of state per/min | $0.15 | $0.13 |
| L2003 | Long Distance (West) | Plan B Out of state per/min | $0.11 | $0.09 |
| L2004 | Long Distance N | Plan C Out of state per/min | $0.18 | $0.16 |
| W1001 | Web Hosting | hosting, site creation | $19.99 | $16.99 |
No comments:
Post a Comment
"Comment As:" anonymous if you would rather not sign into an account!