SQL stands for Structured Query Language, and is the language that you use to communicate with most enterprise class database systems, for example MySQL and Microsoft SQL Server 2005. Whether you are using a desktop database management program, or writing the code yourself, each time you are using SQL Server to communicate with the database server. SQL can be a basic language to learn, although it is dependent on your ability to get to grips with new things, as well as how far you want to go with the use of it.
SQL can be used for many different purposes in relation to database management; for example in the web development world it is used to pull records that are stored in databases, and to edit and delete the records which are stored in the database.
SQL Server can be used standalone in desktop database management applications, as well as in dynamic web applications that you might develop. This means that SQL Server can be used as part of web applications developed using languages such as ASP.NET and PHP; SQL is required for use with a database within a web application otherwise the application won’t be able to communicate with the database server as needed.
SQL can’t be used with most desktop database programs, such as Microsoft Access, since they use a different type of database architecture to store data. However, if you are using a Microsoft Access database with a dynamic web application, i.e. an ASP.NET website, you will need to use SQL in order to pull and edit the database from the Microsoft Access database file.
SQL itself could be classed as a dynamic language, since you are able to feed it custom variables in order to ensure that you are able to retrieve the right data from the database that you are interacting with.
For our examples, we will be using Microsoft SQL Server Manager Express to run our queries on a test database that we have previously created. We are using this program since it allows us to run individual queries on databases and at the same time displays the output in an orderly and readable fashion.
The most simple query that you can run on a database is the ‘select’ query. This query is used to pull data from a database for display either in a web page or other application where it may be needed. The ‘select’ query has many different variables that you can specify to ensure that the correct data is pulled, and also in the right order.
The first query we’ll run is ‘SELECT * FROM Test’. The ‘*’ tells the database to select all the colums of database from the table ‘Test’. You can restrict this to only select certain data columns, i.e. ‘SELECT Id, Name FROM Test’ would only select the ‘Id’ and ‘Name’ columns from the ‘Test’ table.
So, to begin with we have pre-filled our ‘Test’ table with the following data:
The first query will be ‘SELECT * FROM Test’. This should return all columns of data, as follows:
Next, we will run ‘SELECT Name FROM Test’. This should only return one column of data, the ‘Name’ column.
Next, we will introduce the ‘WHERE’ clause to our ‘SELECT’ query. The ‘WHERE’ clause is normally used to allow for more precise and targeted results from a database, and is mainly used in more dynamic pages such as search pages and also where a specific row of data needs to be pulled from the database. For example, if you are running an online shop then you will probably find that the ‘WHERE’ clause is being used to pull specific products from your product database.
For our ‘WHERE’ query, we want to retrieve Barry’s record from our database, so we will run the following query: ‘SELECT * FROM Test WHERE (Id = ‘2’)’. It is recommended that you use brackets around each ‘WHERE’ variable since it can help secure your code again SQL injections which can be performed where weak SQL code has been used.
As you can see, the query has returned the correct record. If being used in a server side script, then you can easily feed variables to the ‘WHERE’ clause from the page’s query string and from text boxes or other elements which have been posted using a form.
You can also use the ‘WHERE” clause in the context of searching the database table for something that resembles what you are looking for. We are going to run the query ‘SELECT * FROM Test WHERE (Name LIKE ‘Barry’)’. The ‘LIKE’ variables replaces the ‘=’, meaning that the script isn’t looking for an exact match when searching the database table.
The next SQL we will discuss is the ‘ORDER BY’ clause that allows you to order the data that you have pulled from the database using the ‘SELECT’ clause. In most cases, you are able to put the data into either ascending or descending order. The ‘ORDER BY’ clause normally takes up the last section of the SQL query, since it is more important to select all the necessary data first. For our example, we will be ordering the ‘Id’ field both in ascending and descending order. The SQL query to list the ‘Id’ field in ascending order is ‘SELECT * FROM Test ORDER BY Id ASC’, and for descending order: ‘SELECT * FROM Test ORDER BY Id DESC’. The following is returned:
That’s it! We have now covered the basics of SQL queries, meaning that you should be able to incorporate your knowledge into your server side scripts to enable you to interact with your databases in the way that you need to.
With the use of SQL in databases, you are able to dramatically increase the dynamic functionality of your website, since you are able to utilize a database as one central store for all the data that you need to ensure that your website is able to run smoothly. For example, you could make use of a basic ‘SELECT’ query with the ‘LIKE’ clause to create a search engine for your website.