Okay probably the first thing you will ask is what is SQL? SQL stands for Structured Query Language or See-Quill for short. SQL will allow you to execute commands to manipulate data in a database. As usual I will do my best to break things down step by step and spoon feed you slowly with the information. I promise though that SQL is not really that complicated.
Using T-SQL in Visual Studio 2010
This page is devoted to learning how to create SQL queries using Visual Studio 2010. The database used in this for Microsoft Access 2007. However if your preference is using SQL Server then I am suggesting to click on the tab at the top of this page that says SQL Server. Just know however that that section can be quite intimidating for a new beginner. That is why I devised separate tabs for Beginner and Advanced.
One of the easiest commands in SQL is called the SELECT statement. Here is an example.
SELECT * FROM CustomerTable
This will look at a table called CustomerTable and select all the records (that's what the asterisk does) and display them on a screen. Here is a visual example. Notice that the query actually shows all the columns, which represent the asterisk (*) which means to show everything in that table. The tool I use defaulted these fields to show all the column names.
The cool thing about the select statement is you can perform a lot of different queries on it to narrow down information. So the next lesson will show you how to search for data in a table.
SELECT CustLastName FROM CustomerTable
So if the CustomerTable has a column called CustLastName then all those records will be shown. You can also select multiple columns from a table. Keep in mind that a column consists of a bunch of cells where data is stored. Just think of an Excel spread sheet and that may shed some light on this for you. Here is your next example that will extract data from several columns in one table.
SELECT CustFirstName, CustLastName, CustAddress FROM CustomerTable
Did you get that? Now you are printing out the customer's first/last name and their address.
Once you have a basic understanding of SELECT then you will want to learn next how to create your own searches (or queries) to generate a report. Here is an example.
SELECT CustLastName FROM CustomerTable
This statement will search through the table CustomerTable to see if the column name CustLastName has produced a find for a customer by the name of "Smith". So basically this will show only customers with that last name on your screen.
With the WHERE statement you can produce reports that show specific detail. The next example will show how to check within a range.
SELECT CustFirstName, CustLastName, CustDateBirth FROM CustomerTable
This is shown in the picture below via the Transact SQL tool in ASP.NET. This query was generated using an Access 2007 database. To use this in ASP.NET you will need to first have a database table added and then right click on the new database name and select New Query. Perhaps I may cover how to use Visual Studio much further down the road.
Here are the columns in the table called CustomerTable. You can also download the Access 2007 database file here too to try it for yourself.
Finally here are the results of that query I just spoke about. If you examine the query look at the line that shows (CustDateBirth > 1968) AND (CustDateBirth < 1990). This shows that the column called CustDateBirth is checking to see if the customer's date of birth is > 1968 (greater than 1968) AND also if the CustDateBirth is < 1990 (less than 1990). This is true since the customer was born in 1969 and is less than 1990. Maybe this is a bad example since a date of birth does not change, but I just thought I would show you how data comparison is performed using SQL.
The next thing I am going to focus on is using a query to check if something is not equal to a result. Here is another demonstration.
SELECT CustFirstName, CustLastName, CityState FROM CustomerTable
This is very similar to the first WHERE example. This time we are checking to see if the column name CityState is not equal to 'AZ'. In English that means show me the results of a query for those customers that do not live in Arizona. The symbols <> means greater or less than. There is also a operator called NOT that can be used like WHERE NOT (CityState = 'AZ') to do the same thing. Here are the results below.
In the next example I will show another way to check the range of values using the BETWEEN statement. You may have noticed that I added a new field to the table called Age. This is basically stating if the customer's age is between 25 and 35 then show the results of that query.
SELECT CustFirstName, CustLastName, Age
Once again I will show you the results of this query so you can see realtime what is happening. I think that a picture is worth a thousand words.
The program code is shown above first and then the explanation is below.