Queries for Updating and Creating Using SQL
In the last webpage we introduced queries based
on the SELECT SQL command with a variety of clauses. Now we want to work
with some more SQL commands in order to do other things.
Remember, in general, it is generally more computationally efficient to use SQL for database operations when it is functional. Some advantages are
As stated previously, SQL commands can be partitioned into data manipulation language statements (DML) and data definition language statements (DDL). In the present webpage we will present some additional data manipulation language (DML) commands and present a few data definition language (DDL) commands. We will start with a table of new DDL commands.
SQL DDL Command
|This command is used to create new tables and indices|
|This is used to add or remove fields and indices to and from a table|
|This is used to delete tables|
|The remaining commands belong to the DML subset of SQL like the commands we studied earlier.|
SQL DML Command
INSERT _ INTO
|This is used to add records to a table. Single records can be appended by specifying values or records can be inserted based on the results of another SELECT statement.|
|This is used to modify the records in a table based on some specified criteria. It is most worthwhile to use when changing multiple records or fields in multiple tables.|
|This is used to remove records from a table that meet some specified criteria.|
|It is my opinion that these SQL commands are best
illustrated by a large variety of examples. In general, we will not be
able to "play" with these commands like we could with SELECT commands
because we are actually modifying the data sources. If everyone in the
class starts doing this it can really muck up the source database even if
everything works as planned. Many of the examples actually come from the
Wild Willies web that we are going to create.
Two CREATE Examples. There are two main ways we will use the CREATE command.
We will first present the code required to CREATE an imaginary table in our web database that could contain local contact information about a shopper. The command would be something like
CREATE TABLE LocalInfo (StudentNumber TEXT(7), LocalAddress TEXT(30), LocalCity TEXT(15), LocalState TEXT(2), LocalZip TEXT(10))
This creates the LocalInfo table with fields for the StudentNumber, LocalAddress, LocalCity, LocalState and LocalZip.
More specific to our Wild Willies store the SQL to create a table that will store information about each department in our web store looks like the following.
CREATE TABLE dbo.Department (
idDepartment int IDENTITY (1, 1) NOT NULL ," & _
Without getting into a huge amount of detail this creates
Since you aren't going to have to write any of this sort of code from scratch, we won't get into more detail about this in this course.
You can also CREATE stored procedures using an SQL command like the following
CREATE PROCEDURE sp_RetrieveDept @idDepartment
int AS " & _
What this does is
You will see the importance of this later when we develop the web. Notice this stored procedure makes use of a SELECT command with a WHERE clause.
Actually implementing these CREATE commands requires relatively clever use of ASPs because we do not have permissions sufficient to allow us direct interaction with the databases on the server.
An ALTER Example. Let's say that you have just created a new LocalInfo table for retaining information about your shopper's and you realize you forgot to include a field for the LocalPhone and HomePhone. You can fix this with the following SQL commands.
ALTER TABLE LocalInfo ADD COLUMN LocalPhone TEXT(12)
ALTER TABLE LocalInfo ADD COLUMN HomePhone TEXT(12)
That these commands work can be verified by Refreshing the List and then re-expanding the list of fields in the LocalInfo table.
You can also use the ALTER command to drop a field from the table. Let's say you now decide that you don't want the HomePhone in your new table. The following SQL command will take care of this situation.
ALTER TABLE LocalInfo DROP COLUMN HomePhone
This can again be verified by first Refreshing the List and then re-expanding the list of fields for the LocalInfo table.
We are not likely to use the ALTER command in this class in the Wild Willies web, at least I can't presently remember any instances where we do this.
A DROP Example. Well, you think you've been pushed around enough for this example? The final thing you are going to do is now get rid of the LocalInfo table you so imaginatively created and altered. While you are likely to need such a table in reality, you aren't going to need it for this course. The following command will solve your problem.
DROP TABLE LocalInfo
Well, this is the complete life cycle of our LocalInfo table.
INSERT INTO Commands. Now we want to get back to the more commonly used DML commands. In the Wild Willies web store we will use the INSERT INTO commands primarily when putting initial data into tables such as information about departments, products, shipping and taxes. The other places will relate to inserting things like order information and customer information into tables. The following examples will illustrate one of each.
Department(chrDeptName, txtDeptDesc, chrDeptImage)
There are several lines of this sort of code in one of the later pages on initializing the data in the tables.
While this next INSERT INTO command is quite long, and actually part of a stored procedure that will be created, it is useful to see how this sort of thing is done using SQL.
INSERT INTO BasketItem(idBasket,
intQuantity, intPrice, chrName, idProduct, chrSize, chrColor)
This is actually used in the creation of a stored procedure that will insert an item a customer orders into the BasketItem table to be displayed in their shopping basket identified by idBasket. All of the parameters with the preceding @ symbol are values that the stored procedure needs to be pick up from the user via the ASP middleware.
UPDATE Commands. It is often the case that you want to modify current entries in a table based on some criteria. The UPDATE command can be very useful in such situations. Let's assume you run a web based business and the SalesTaxRate within California change and you need to update all the entries to reflect this in your Orders table. The following command is what will work.
UPDATE Orders SET SalesTaxRate = 0.065 WHERE Orders.ShipState = 'CA'
Obviously, there can be many other useful applications for such a command. Hopefully you can see the advantage of using an SQL command in this situation rather than working your way through the database to modify all of the instances where CA is the shipping destination.
This sot of command is used in Wild Willies when you change something about an order such as the quantity or color attribute. There are stored procedures that implement this, but the SQL that is used within it is likely to look something like the following which updates the quantity of an item in the basket identified by the idBasket and idProduct.
UPDATE BasketItem SET intQuantity = @intQuantity WHERE idBasket = @idBasket AND idProduct = @idProduct
Again, the @ symbols precede entries that are obtained from an ASP form interacting with a shopper.
DELETE Commands. Another relatively frequently used SQL DML command is the DELETE command. It is sometimes the case that you want to delete all of the records from a table that meet some specified criteria. Consider the following.
DELETE FROM Customers WHERE Customers.City = 'Mount Vernon' AND Customers.State = 'VA"
This is would delete all of your customers that live in Mount Vernon, Virginia.
This sort of SQL is used within some of the stored procedures within the Wild Willies store web. The following statement relates to deleting an item from the shopper's basket.
DELETE FROM BasketItem WHERE idBasket = @idBasket AND idBasketItem = @idBasketItem
As usual, the @ symbols precede entries that are obtained from an ASP form interacting with a shopper.
This completes our survey of SQL.