The SQL language – which stands for "Structured Query Language" though these days "SQL" is pretty much a word itself – is shared by most major relational database management platforms including Microsoft SQL Server.
There are many American National Standards Institute (ANSI) standards for SQL and the very basics of the syntax have been standardized for some time.
You can't get far in the SQL Server world without some SQL up your sleeve. Here are the basics of the SQL language.
SELECT (for starters)
A SELECT query tells SQL Server what data you want from it.
Technically speaking, SQL Server follows an ANSI-standard SQL syntax. This means most of the queries that I'll be showing you also work on other SQL-based platforms like MySQL and Oracle.
A basic SELECT query takes the following form:
SELECT { * | column,column,column } FROM table [WHERE criteria] [ORDER BY column]
After the keyword SELECT, specify * if every column from the table should be included. Otherwise you should provide a comma-delimited list of column names that you want.
Then use the FROM keyword to specify the table name the columns live in.
By default, SQL Server will return every row in the table. You can also specify a WHERE clause, which lists criteria for the rows you want returned.
For example, …WHERE OrderAmount = 1.00 will return only the rows where the OrderAmount column contains the value 1.00.
The columns specified in the WHERE clause do not have to be in the list of columns you SELECT. In other words, you can filter out rows based on columns you won't actually see.
In my example, it makes sense to do this since I'm only getting the rows that have 1.00 in OrderAmount. I don't need to see the OrderAmount column in my query output because I know what that column will contain.
Here are some more WHERE examples to give you an idea of what SQL Server can do:
- …WHERE DateOrdered BETWEEN '7/1/2000' AND '7/1/2001'
- …WHERE OrderAmount > 5000 AND OrderAmount < 2000
- …WHERE CustomerName LIKE '%one%'
- …WHERE (OrderAmount > 5000 AND OrderAmount < 2000) OR (SpecialOrder = 'Express')
- …WHERE InvoiceNumber IN (1002,476382,7261)
You've seen the use of the following operators: BETWEEN, AND, LIKE (which uses % as wildcards), OR, and IN .
The BETWEEN operator specifies ranges (note that dates are treated as strings and included in single quotes).
The IN operator provides a comma-separated list of acceptable values.
A useful alternative for the IN operator is a subquery:
…WHERE InvoiceNumber IN (SELECT InvoiceNumber FROM BadOrders)
The subquery executes first, and theWHERE clause will include all rows where the InvoiceNumber appears in the BadOrders table.
Finally, the optional ORDER BY clause lets you specify the order that the rows are returned. Once again you can specify columns not included in the output. For example, you might sort on a date field you don't need to look at.
Here are some more examples:
- …ORDER BY DateOrdered
- …ORDER BY DateOrdered DESCENDING
- …ORDER BY DateOrdered,InvoiceNumber
The default sort order is ascending, and you can specify the ASCENDING keyword if you wish. The DESCENDING keyword reverses that order. The last example shows two columns for sorting: Rows will come out sorted first by date ordered and then by invoice number for rows that have the same DateOrdered value.
The final option I want point out is you can select a subset of matching rows. For example:
SELECT TOP 10 * FROM Orders WHERE OrderAmount > 5000 ORDER BY DateOrdered DESCENDING
This query grabs all the rows that have an order amount more than 5,000 and orders them with the most recently-placed order first. It will then send you only the first 10 of those rows, and will include every column in the table.
You can also specify …TOP 10 PERCENT… rather than a fixed number.
All combined, these are useful for grabbing "pages" of data from the server. Similarly, a BOTTOM keyword does exactly the opposite of the TOP keyword.
There are several more options associated with SELECT that we'll take a more detailed look at below.
INSERT
Now that you know how to get data out of the database, let's work on putting some in.
The INSERT query allows you to add new rows to the database. To do this you need to specify a table to put the data in, the columns you are providing values for and then the values themselves. Rows are inserted one at a time.
The query looks like this:
INSERT INTO table (column,column,column) VALUES(value,value,value)
There is a way to omit the column list, but from a practical perspective it's easier to include it.
The values are listed in the same order as the columns – that's how SQL Server knows which value goes where. You need to specify columns and values for any column that doesn't either (a) have a default value or (b) allow an empty (null) value. Both the default and the "nullability" of a column is defined in the table's design.
Here's an INSERT example:
INSERT INTO Orders (Invoice,Amount,ShipMethod) VALUES(10383,30.00,'Express')
Note that date and text values are enclosed in single quotation marks. This can present problems if the string contains a single quote such as:
INSERT INTO Customers (Name,ZIP) VALUES ('Tim O'Leary','89123')
This results in an error because SQL Server will see Tim O as one value and not understand why Leary' is in there.
The trick is to replace all literal single quotes with a double-single quote. I know, that's like saying "put your seat back up," but here's what it looks like:
INSERT INTO Customers (Name,ZIP) VALUES ('Tim O''Leary','89123')
The dual single quotes after O and before Leary will be interpreted as one single quote when SQL Server executes the query. And that's about all there is to it!
The two biggest "gotchas" involve making sure you specify every column that requires a value (and not provided by default), and getting your punctuation – those single quotes and commas – correct.
UPDATE
When you need to make a change to existing data, an UPDATE query is your best friend.
This type of query is designed to update multiple rows at once and takes the following basic form:
UPDATE table SET column = value, column = value [WHERE criteria]
To change the completed column on every row in the Orders table, enter:
UPDATE Orders SET Completed = 1
However, you probably won't always want to change every row. In this case, add a WHERE clause:
UPDATE Orders SET Completed = 1 WHERE DateOrdered = DATE()
I threw in the built-in SQL Server DATE() function, which provides the current date, and as a result, I don't need to use single quotation marks.
I could also set multiple columns:
UPDATE Customers SET LastOrder = DATE(), OrderCount = OrderCount + 1, Frequent = 'Yes' WHERE CustomerID = 758392
Once again, I used the DATE() function and this time I also incremented the contents of the OrderCount column (by taking whatever was in that column already and adding 1), and then set the Frequent column to contain the string value Yes.
SQL Server's built-in functions can be very useful, and most can be used in WHERE clauses as well as in the SET clause. Many can also be used in a SELECT statement (we'll get to that later).
A complete list of functions is available in the Microsoft Developer Network library. While this list for SQL Server 2000, they all work in 2005 and 2008 as well. The later versions just add more.
DELETE
Finally, there's the DELETE query, which removes rows from a table.
This query is almost always combined with a WHERE clause:
DELETE FROM Customers WHERE Inactive = 1
Without a WHERE clause, every row in the table will be deleted –something to avoid in most circumstances!
 |
|
| TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of . |
|
| |
All Rights Reserved, , TechTarget |
|
|
|
|
Part 1: 
Don Jones is a co-founder of Concentrated Technology LLC, the author of more than 30 IT books and a speaker at technical conferences worldwide. Contact him through his website at 
