Basic SQL Commands

May 2013
S	M	T	W	T	F	S
« Apr
	1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31

Basic SQL Commands – SQL For Programmers

September 18th, 2008 |

Author: Dave

Before we get into the specifics of the commands that can be used within a stored procedure, I think it would be helpful to review some of the more basic commands that we can use that don’t really need a whole lot of discussion.

I know I said I wasn’t going to spend a lot of time on the basics, but my research indicates that a lot of people are searching for basic sql commands and very few sites are addressing that issue specifically.

So, if this is unhelpful to you, just consider this Dave’s SEO experiment.

Command	Description
SELECT	The SELECT statement returns a set of records from a table or view. It can be used in combination with JOIN to return a set of records that are a mashup of records from multiple tables but the end result from a programming perspective is that it looks like it came from one table. The set of records returned can be narrowed by using the WHERE clause and the order that they are returned can be set by using the ORDER BY clause. Most common form: SELECT field1, field2, etc FROM tableName WHERE conditions ORDER BY listOfFields
INSERT	INSERT adds new records to the table. This can be used in combination with a sub-SELECT to insert records from another table into the current table. Most common form: INSERT INTO tableName(field1,field2) VALUES(value1,value2)
UPDATE	This is most often used in combination with the WHERE clause to update a record in a table. Most common form: UPDATE tableName SET field1=value1, field2=value2 WHERE condition
DELETE	When you need to delete a record in your table, or a set of records, you will use the DELETE statement. Most common form: DELETE FROM tableName WHERE condition
WHERE	The WHERE clause specifies what set of records the operation should be performed on. We have discussed various operators in previous posts. The most common are: =, <>, <, >, IN, and LIKE
IN	IN is used with the WHERE clause to determine if the field is equal to any of the items in a list.
LIKE	We discussed this one a few posts ago. Most are familiar with the common form, LIKE ‘%content%’ to determine if ‘content’ is in the field. But there are other useful forms of this operator as well. Check out the list of previous posts below.
ORDER BY	Most often used with the SELECT statement (and I really can’t think of any other place it is used), this controls the order the records are returned. Most often this is a list of fields. By default it lists everything in ascending order. If you want the items in descending order, use the DESC modifier.

My favorite resource for SQL syntax reference is http://www.w3schools.com/sql/.

Other post in SQL For Programmers

SQL For Developers - 9 Reasons to bother - August 13th, 2008
MSSQL CREATE and DROP Database - SQL for Programmers - August 20th, 2008
MSSQL CREATE TABLE - SQL For Programmers - August 22nd, 2008
SQL For Programmers - ALTERing the TABLE - September 1st, 2008
SQL For Programmers - Finding a String - September 3rd, 2008
SQL For Programmers - Finding IN a List - September 8th, 2008
SQL For Programmers - Stored Procedure Basics - September 16th, 2008
Basic SQL Commands - SQL For Programmers - September 18th, 2008
SQL IF/WHILE Blocks - SQL For Programmers - September 30th, 2008
SQL WHILE - SQL For Programmers - October 2nd, 2008
Temporary Tables - SQL For Programmers - October 7th, 2008
SQL CURSOR - SQL For Programmers - October 13th, 2008
SQL CURSOR Performance - SQL For Programmers - October 22nd, 2008
Random in SQL - SQL For Programmers - November 4th, 2008
SQL - Filtering WHERE condition on two rows - November 26th, 2008
SQL - Transactions - April 15th, 2009
SQL For Programmers – New Question - July 6th, 2009
SQL SELECT CASE Instead of IIF - October 20th, 2009
SQL For Programmers - Stored Procedures (Better than LINQ) - August 28th, 2012

SQL For Programmers – Finding IN a List
In the last SQL post, we looked at looking for content that was LIKE other content. While this has its uses, it is limited in its ability to find more than one pattern. So what if we want to fin...
SQL CURSOR Performance – SQL For Programmers
Last week I showed how to use the SQL CURSOR to loop through records in a database. In that article I mentioned that you want to avoid using the CURSOR if you can because it has performance p...
SQL For Programmers – Finding a String
Many times in our queries, we aren't looking for an exact match. We are looking for one string that exists in another. There are a couple statements available to us that will allow us to do this...
Random in SQL – SQL For Programmers
Last week I had an interesting assignment: Given a specific record, randomly retrieve three related records from the database. Naturally, as a programmer, I started looking for the SQL random f...
SQL – Filtering WHERE condition on two rows
I received the following question a couple of days ago from a programmer using the "Ask a Question" form which you can access from the menu of this blog: Given the following table: Name...