Queries for Updating and Creating Using SQL

 

Introduction. 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

  1. ANSI Standard SQL works across platforms.
  2. SQL executes quite close to the machine and is fast relative to most every other form of database processing.
  3. SQL can handle very large datasets with a large variety of users.

  4. SQL has been designed with the fairly minimalist engineering advantages of a "less is more" strategy.  As long as the less handles your requirements
    1. less is more because there is less that can go wrong
    2. you are closer to the machine
    3. you have the essential/required operations and not a lot of overhead
  5. SQL environments tend to have very good built-in security features.
  6. Stored procedures in SQL are precompiled pieces of code that execute very efficiently and can accept inputs from some type of middleware.
  7. A single stored procedure can be used at different locations in a web.

  8.  

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

Short Description

CREATE

This command is used to create new tables and indices

ALTER

This is used to add or remove fields and indices to and from a table

DROP

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

Short Description

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.

UPDATE

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.

DELETE

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.

  1. to CREATE tables
  2. to CREATE stored procedures

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 ," & _
"chrDeptName varchar (255) NULL, " & _
"txtDeptDesc text NULL, " & _
"chrDeptImage varchar (255) NULL, " & _
"CONSTRAINT PK___1__12 PRIMARY KEY CLUSTERED ( idDepartment))"

Without getting into a huge amount of detail this creates 

  1. a TABLE for which you have dbo = database owner permissions called Department
  2. an idDepartment field that auto-increments for each new entry
  3. a chrDeptName field for the name of each department
  4. a txtDeptDesc field for the description for each department
  5. a chrDeptImage field that contains the name of the image associated with each department
  6. the CONSTRAINT makes idDepartment the unique identifier for each record

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 " & _
"SELECT * FROM Department WHERE idDepartment = @idDepartment

What this does is 

  1. create a stored procedure called sp_RetrieveDept
  2. that gets the department id from the middleware as an input via the @idDepartment
  3. then uses this id to select a record for a particular department the user has selected based on the WHERE clause

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.

INSERT INTO Department(chrDeptName, txtDeptDesc, chrDeptImage) 
VALUES('Cool Backstreet Jazz', 'Jazz that will make you dance.','dance.gif')

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) 
VALUES(@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.