SQL programming is at the heart of today’s Web 2.0 applications. Learn the basic 4 commands SELECT, UPDATE, INSERT and DELETE.
This Transact-SQL primer covers concepts and syntax of some basic SQL commands. In order to begin working with SQL Server 2005 and try out the examples listed here, install the free SQL 2005 platform applications from Microsoft. For a detailed walk-through on obtaining and installing this software, see “Install SQL Server 2005 Express.”
There are four basic SQL commands that will be discussed. While each of these commands have depth and complexity that are beyond the scope of this primer, they have their basic forms and understanding them is an excellent place to begin.
SELECT
Perhaps the most commonly used SQL command of all, SELECT is used to view records in the database. Open SQL Server Management Studio Express and select the AdventureWorks database. Open a new query window. Type the following command then click “Execute” or equivalently type Ctrl-e.
SELECT *
FROM Person.Contact
The results are displayed below the query windows. In the lower right corner it shows how long the query took and how many records were retrieved. There are 19,972 records in the Person.Contact table. In the command above, the “*” indicates a selection of all fields, or columns, in the table. To limit these, replace the “*” with a list of the columns desired.
SELECT Title, FirstName, LastName, EmailAddress
FROM Person.Contact
View the result and see that only the four columns listed are now included in the results. To view only the contacts that have a title of “Sr.” use the WHERE clause to specify a limitation on the type of records returned. This yields 11 records.
SELECT Title, FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE Title = ‘Sr.’
Notice that the form [column] = [value] is used by the WHERE clause to limit the records returned to only those that match the specified value in the specified field. The use of single-quotes is important to specify string values, as double-quotes have a different meaning in Transact-SQL.
UPDATE
The UPDATE command is used to modify 1 or more records in a table. Use the WHERE clause to limit which records are updated, just as it was used to limit which records were returned for the SELECT command. View a record in the Person.Contact table, then update it and view it again.
SELECT *
FROM Person.Contact
WHERE ContactID = 10
UPDATE Person.Contact
SET FirstName = ‘Ron’
WHERE ContactID = 10
SELECT *
FROM Person.Contact
WHERE ContactID = 10
Notice that the contact’s first name has been changed from “Ronald” to “Ron.” It is extremely important that the WHERE clause is used with UPDATE. If it were removed from the UPDATE statement above, every single contact in the Person.Contact table would now have the first name “Ron” because there was nothing to limit the scope of the update. Without a limit, or conditional, SQL will happily update every record.
INSERT
The INSERT command creates a new record in a table. View the records in the Production.Location table, add a new record to it, then view it again. The SELECT statements used to view the records are left to the reader as an exercise.
INSERT
INTO Production.Location
(Name, CostRate, Availability)
VALUES (‘Plastics’, 14.5, 90.0)
The list of columns after the table name specify that values will be provided for Name, CostRate and Availability. The other two columns in the table, LocationID and ModifiedDate are automatically set by SQL so values are not provided for them. Notice that the number of columns listed must match the number of values provided.
DELETE
Finally, the DELETE command, as expected, deletes one or more records from a table. The same caution must be taken with DELETE as with UPDATE. If no WHERE clause is specified, every single record in the table will be deleted! Delete the “Plastics” record that was just inserted into Production.Location.
DELETE
FROM Production.Location
WHERE LocationID = 61 Practice variations of these four commands on the AdventureWorks database. Don’t be afraid to make mistakes, as a fresh copy of the database can always be installed.
Recent Comments