FIGURE 7-2 Intellisense
TutorKey TutorLastName TutorFirstName TutorPhone TutorEmail TutorHire Date TutorStatus 980010000 Roberts Martha 2065551467 [email protected] 2010-01-06 Active 980010001 Brown Susan 2065553528 [email protected] 2009-02-01 Active 980010002 Foster Daniel 2065553490 [email protected] 2009-02-12 Active
980010003 Anderson Nathan 3065556320 NULL 2009-03-02 Inactive
980010004 Lewis Ginger 2065552985 [email protected] 2009-03-15 Active FIGURE 7-3 Result Set
Things You Should Know
SQL is the programming language used for manipulating database objects and data in relational databases. It is both an ANSI (American National Standards Institute) and an ISO (International Standards Organization) standard.
The first version of SQL was developed at IBM in the 1970s to work with their RBase relational database. The first ANSI standard for SQL was issued in 1986. The ISO committee ratified the stan-dard in 1987. This first stanstan-dard was not widely used. Database technologies had already moved past it. Most database manufacturers had already added features that were not included in the standard.
A major revision was issued in 1992. This standard was much more robust and is still the de facto standard of many RDBMSs today. More changes were added to the standard in 1999 to define the use of triggers and procedures. Revisions in 2003 and 2006 defined how to incorporate XML and XQuery into SQL.
Most RDBMSs comply with the standard to a fairly high degree. What this means for the user is that the SQL they write for one product will translate fairly easily to another product. Much of the SQL you write for SQL Server, for instance, will work without change in Oracle or MySQL. Each RDBMS, however, is free to add proprietary features onto SQL as well as to implement the standard.
Typically, these features are additional functions or administrative extensions.
The Nature of SQL
SQL is a declarative language. This means it is different from the procedural languages you may have encountered in other programming languages such as C++ or Java or C# or Visual Basic. In those languages, you have to specify how something is to be done. You have to carefully list each step in the proper order to accomplish a task. In SQL, you say what you want done, not how to do it. In the preceding example, for instance, Sharon writes
SELECT * FROM Tutor
The SELECT tells the DBMS you want to retrieve data. The * is a wildcard that says “select all columns.” The FROM keyword directs the RDBMS to a table in the current database. The statement
She clicks Tutor in the list and then clicks the Execute button and gets these
results:
as a whole declares “return all the columns and all the rows from the table Tutor.” Again, it declares what you want to do, not how to do it. The RDBMS determines how to process the request. Different RDBMSs will process it differently because they have developed different query optimization engines in order to produce the results as efficiently and quickly as possible.
DECLARATIVE LANGUAGE A language in which programmers declare what they want to do, not how they want to do it.
PROCEDURAL LANGUAGE A language in which a programmer defines how to do a given procedure.
T H I N G S T O T H I N K A B O U T What are the advantages of a declarative
lan-guage as opposed to a procedural lanlan-guage?
What advantages might a procedural language have over a declarative language?
SQL is not case sensitive, though the column names and values can be if the database options are set to be case sensitive. It is traditional, however, to type SQL keywords in all uppercase for read-ability. SQL also ignores most white spaces. That means you can organize an SQL statement on the page any way that makes it most readable to you. In many DBMSs, SQL statements are terminated by a semicolon. SQL Server does not require the semicolon, though using one can be a good habit to develop. In this book, the semicolons are not included.
Usually, SQL is divided into at least two broad areas of functionality: Data Manipulation Language (DML), which includes all the commands for selecting and manipulating database data, and Data Definition Language (DDL), which includes all the commands for creating, altering, and dropping database objects such as tables, procedures, constraints, and indexes. In this book, we are only going to focus on the DML.
DDL
Data Definition Language.
DML
Data Manipulation Language.
She executes the statement and gets these results:
Things You Should Know
The Basic SELECT Statement
The syntax for the simplest SELECT statement is SELECT [Column1], [Column2], etc.
FROM [Table]
FIGURE 7-4 Selected SQL Statement
SELECT StudentLastName, StudentFirstName, StudentEmail FROM Student
Sharon decides to run another query just to check the data. She types another SQL statement into the Query window. She selects the statement so that only it will run when she clicks Execute.
FIGURE 7-5 Result Table StudentLastName StudentFirstName StudentEmail
Min Ly [email protected]
Bradbury Ron [email protected]
Carlos Juan [email protected]
Peterson Laura NULL
Carter Shannon [email protected]
Martinez Sandy [email protected]
Nguyen Lu [email protected]
Zukof Mark NULL
Taylor Patty [email protected]
Thomas Lawrence NULL
You can select any number of columns from the database table. The columns are separated by commas, but there is no comma after the last column in the list. The columns are returned in the order they are listed. So in our example:
SELECT StudentLastName, StudentFirstname, Studentemail FROM Student
Sharon also used a variation of this syntax:
SELECT * FROM Tutor
The asterisk (*) is a wildcard character that tells the RDBMS to return all the columns in the table. The advantage of this is obvious—you don’t have to key in all the columns. But there are disadvantages. For one, you have no say in the order in which the columns are returned. They will simply be returned in the order they have in the table. Also, the wildcard method is less efficient. The database must first query the system table to identify the columns, and then query the data table to access the data. And, finally, in SQL code, which is embedded in an application (such as a Web page), there is no guarantee that the columns returned will always be the same. If someone modified the database, the query may return unexpected columns and cause errors in the program. In general, it is better to specify the columns you wish returned, though the wildcard method can be useful during testing and development.
Distinct
Sometimes you only want to return one instance of each value. For instance, suppose you want to run a query on the session table to see all the tutors that have scheduled sessions. If you run
SELECT tutorkey FROM Session
You will get these results:
Each tutor key repeats for as many sessions as the tutor is scheduled for. If you wanted to see only one instance of each TutorKey, you can use the DISTINCT keyword:
SELECT DISTINCT tutorkey FROM Session
This results in the following:
FIGURE 7-6 Result Table
TutorKey 980010003 980010001 980010001 980010004 980010001 980010001 980010002 980010004 980010004 980010002 980010004 980010004 980010002 980010002
FIGURE 7-7 Distinct Results
TutorKey 980010001 980010002 980010003 980010004
DISTINCT operates on the whole row, not on individual columns. The whole row must be identical to be excluded.
Calculations
You can perform calculations in a SELECT clause. For instance, you can calculate how many hours are there in a typical year with a statement such as:
SELECT 365 * 24
The query will return a column labeled “No column Name” with the value “8760.” To name the column, you can alias it—see the following SELECT statement. More useful calculations, perhaps, can be made by using the math operators with values in table columns. Assume, for instance, that you had a table that stored the item number, the price of an item, and the quantity ordered. You could calculate the total due with a query like the following:
SELECT ItemNumber, ItemPrice, Quantity, ItemPrice * Quantity FROM CustomerOrder
Following is a table of the arithmetic operators: