SQL - The need

SQL makes databases independent from the applications using the databases. Applications can send a request to a database and receive an answer without having to use the same programming language or operating system or the same computer. You write your request in SQL. SQL is transported to the database using Web services, software links, anything you can connect though, and the results travel back the same way. Think of email as a similar connection. You can create email using any email client and send email to any other computer using any email software.

SQL stands for Structured Query Language. It is a language for writing requests and the reply can be in a different format. It is a language for writing queries plus it can handle updates of exisitng data and inserts of new data. The language is structured to keep the language simple, to enforce accuracy, and to allow queries within queries. The bit about queries within queries needs a little more explanation.

Suppose you wanted to list all the cities with a population or more than a million then list all the suburbs in all those cites. You can achieve the result in several ways. One way is to write a query to list cities with a population greater then a million. You then write a query to list all the suburbs in a city, say Sydney. You can then take the first query and put it inside the second query to make the second query list all the suburbs in the list of cities returned by the first query.

Relational databases store data as rows in tables. Our previous city population example could use a table similar to the following list, which we will call city_population.

Our city suburb example could use the following list, named city_suburb.

You do not have to learn SQL at this stage because I will revisit in detail. A quick translation of the city population query to SQL reads as follows.
select city from city_population where population > 1000000
The result of the query will be the following list.

The following query is a quick translation of the example Sydney suburb list request followed by the resultant list.
select suburb from city_suburb where city = 'Sydney'

We can combine the two requests into following query as a quick translation of the example list of suburbs in cities with a population over one million.
select city, suburb from city_suburb where city in( select city from city_population where population > 1000000 )

The SQL query is just a string of text that can be sent from program to program, computer to computer, or across the Internet using any method of sending text that fits your requirements for reliability, security, and speed. You will hear about web sites that have separate web servers and database servers with requests flowing from the Web server to the database server across the network.

The result, the data returned from the query, can also be in any format you want provided the database server can serve data in that format. If you use Web services for the request, the result could be in XML or JSON. Some databases, including IBM's DB2, use ODBC as a standard way of submitting requests and returning results. Other databases offer ODBC as an option. ODBC and other communications can be secured inside a VPN. (Are you sick of the TLAs yet?)

Acronyms

TLA means Three Letter Acronym and is a good example of a TLA. SQL is also a TLA. ODBC is a TLAE, Three Letter Acronym Extended. :-)

ODBC stands for Open DataBase Connectivity. It is the database equivalent of Web services.

XML, Extensible Markup Language, is a structured markup language similar to HTML because they both use the same base, SGM. XML is data oriented instead of Web oriented. XML is a good format for returning results from a database query when the results have to go through Web services instead of ODBC.

JSON is simple compared to XML and is designed to feed direct into Javascript.

Direct connections

Programming languages have ways to connect direct to databases. PHP has functions for each type of database. You can access MySQL through functions that have names starting with mysql_. In PHP you can also access databases using the generic PDO. PHP can also talk to ODBC. In all cases, the data is returned in a format native to the programming language. PHP can return the result as an object or as an array.

SQL for non query actions

SQL can also define databases, define database tables, alter database structures, perform maintenance, and control access.

SQL is not a replacement for programming languages

Some database manufacturers extend SQL to do all sorts of weird things. The smarter database developers provide a way to add external language modules to perform odd tasks. An example is the emulation of a complicated data type in a database that does not have a built in way to handle that data type. None of it is a replacement for programming languages. Your business logic and application should be outside the database.