|
The first questions to ask are what is SQL and how do you use it with databases? SQL has three main roles:
1. Creating a database and defining its structure
2. Querying the database to obtain the data necessary to answer questions
3. Controlling database security
Defining database structure includes creating new database tables and fields, setting up rules for data entry, and so on, which is expressed by a SQL sublanguage called Data Control Language (DCL). The next section discusses querying the database. Finally, DCL deals with database security. Generally, database security is something that database administrators handle. Creating SQL every time you want to change the database structure or security sounds like hard work, and it is! Most modern database systems allow you to execute changes via a user-friendly interface without a single line of SQL.
Introducing SQL Queries
SQL queries are the most common use of SQL. A SQL sublanguage called Data Manipulation Language (DML) deals with queries and data manipulation. SQL allows you to pose a query (basically a question) to the database, and the database then provides the data that answers your query. For example, with a database that stores details of salespersons, car sales, type of cars sold, and so on, you might want to know how many cars each salesperson sold in each month and how much money they made the company. You could write a SQL query that asks this question and the database goes away and gets the data that answers it. A SQL query consists of various statements, clauses, and conditions. A statement is an instruction or a command. For example, “Get me some data” is a statement. A clause specifies limits to a statement, the limits being specified using conditions. For example, instead of “Get some data,” you might say, “Get data only for the sales that were in the month of May,” where “only for” is the clause that specifies which data to retrieve. The condition is “were in the month of May.” If the data doesn’t meet the condition’s criteria, in this case, “month of May,” then you don’t want it. Written as actual SQL code, this could be something like the following:
SELECT CarModel
FROM CarSales
WHERE CarSoldDate BETWEEN ‘May 1 2005’ AND ‘May 31 2005’;
The SELECT statement tells the database system that you want to select some data from the database. You then list the data you want, in this case CarModel data, which is a field name. You then specify the place the data needs to be taken from, in this case a table called CarSales. Finally, you have a condition. The statement above specifies that you want only the data where certain conditions are true. In this case, the condition is that the CarSoldDate is between the first and thirty-first of May 2005.
Comparing SQL to Other Programming Languages
Now that you know what SQL can be used for, you can compare it to other programming languages. To
be honest, SQL is quite different from the procedural languages such as C++, Visual Basic, Pascal, and
other third-generation programming languages, which allow the programmer to write step-by-step
instructions telling the computer exactly what to do to achieve a specified goal. Taking the car sales
example, your goal might be to select all the information about sales made in July from the New York car
showroom. Very roughly, your procedural language might be along the lines of the following:
1. Load the sales data into the computer’s memory.
2. Extract the individual items of data from the sales data.
3. Check to see if each item of data is from the month of July and from the New York showroom.
4. If it is, then make a note of the data.
5. Go to the next item of data and keep going until all items have been checked.
6. Loop through the data results and display each one.
SQL, however, is a declarative language, which means that instead of telling it what to do to get the
results you want, you simply tell it what you want, and it figures out what to do and comes back with
the results. In the car sales example, if you were using SQL, you’d specify the results you want, something
like this:
SELECT all the data from the sales table WHERE the sales were in July and made at the New York
showroom.
The SQL language is actually fairly easy to read. The actual SQL could look like this:
SELECT * FROM SalesMade WHERE SaleDate = “July 2005” AND SalesOffice = “New York”
The asterisk simply means return the data from all the fields in the record.
Understanding SQL Standards
As with databases, IBM did a lot of the original SQL work. However, a lot of other vendors took the
IBM standard and developed their own versions of it. Having so many differing dialects causes quite
a headache for the developer, and in 1986 it was adopted by the standards body the American National
Standards Institute (ANSI) and in 1987 by the International Standards Organization (ISO), who created
a standard for SQL. Although this has helped minimize differences between the various SQL dialects,
there are still differences between them.
The following table gives a brief summary of the various standards and updates to those standards.
Year Name Also Known As Changes
1986 SQL-86 SQL-87 (date when First publication of the ANSI/ISO adopted by ISO) standard
1989 SQL-89 Only small revision of the original standard
1992 SQL-92 SQL2 Major update of the original standard and still the most widely supported standard
1999 SQL-99 SQL3 Update of the 1992 standard adding new ways of selecting data and new rules on data
integrity and introducing object orientation
2003 SQL-2003 Introduced XML support and fields with autogenerated values
The SQL you write works on
most RDBMSs with only minor modifications. There are times when the various RDBMSs do things so
differently that compatible code is impossible without big changes.
Although standards are important to help bring some sort of commonality among the various RDBMSs’
implementation of SQL, at the end of the day what works in practice is what really counts.
That said, the next section shows you how to create your own SQL database.
|
