Section B.3. SQL

B.3. SQL

The language of choice for querying and manipulating databases is Structured Query Language , often referred to as SQL. SQL is often pronounced "sequel". SQL is a declarative language, as opposed to a procedural language, and it can take a while to get used to working with a declarative language if you are used to languages like VB or C#.

Most programmers tend to think in terms of a sequence of steps: "Find me all the bugs , then get the reporter's ID, then use that ID to look up that user 's records in People, then get me the email address". In a declarative language, such as SQL, you declare the entire query, and the query engine returns a set of results. You are not thinking about a set of steps; rather, you are thinking about designing and "shaping" a set of data. Your goal is to make a single declaration that will return the right records. You do that by creating temporary "wide" tables that include all the fields you need and then filtering for only those records you want. "Widen the Bugs table with the People table, joining the two on the PersonID , then filter for only those that meet my criteria".

The heart of SQL is the query . A query is a statement that returns a set of records from the database. Typically, queries are in this form:

 Select <column,column,column> from <table> where <column> = <value> 

For example, you might like to see information about your customers in the United States. To do so you would write:

 Select CompanyName, ContactName from Customers where Country = 'USA' 

B.3.1. Joining Tables

SQL is capable of much more powerful queries (see the sidebar for the full syntax as provided by Microsoft).

For example, suppose you'd like to know about all the orders by all the customers in the United States after January 1 of 1997. You might create this query:

 Select CompanyName, ContactName, city, orderDate, shippedDate,     productID, unitprice, quantity   from Customers c     join Orders o on o.customerID = c.customerID     join [order details] od on od.orderid = o.orderid     where c.Country = 'USA' and orderDate > '1/1/97' 

SQL Select Statement

 SELECT statement ::=         < query_expression >         [ ORDER BY {   order_by_expression     column_position   [ ASC  DESC ] }             [ ,...   n   ]    ]         [ COMPUTE             { { AVG  COUNT  MAX  MIN  SUM }  (    expression    )  } [ ,...   n   ]             [ BY   expression   [ ,...   n   ]  ]         ]         [ FOR { BROWSE  XML { RAW  AUTO  EXPLICIT }                 [ , XMLDATA ]                 [ , ELEMENTS ]                 [ , BINARY base64 ]             }     ]         [ OPTION ( < query_hint > [ ,...   n   ]) ]     < query expression > ::=         { < query specification >  ( < query expression > ) }         [ UNION [ ALL ] < query specification  ( < query expression > ) [...   n   ] ]     < query specification > ::=         SELECT [ ALL  DISTINCT ]             [ { TOP   integer   TOP   integer   PERCENT } [ WITH TIES ] ]             < select_list >         [ INTO   new_table   ]         [ FROM { < table_source > } [ ,...   n   ] ]         [ WHERE < search_condition > ]         [ GROUP BY [ ALL ]   group_by_expression   [ ,...   n   ]             [ WITH { CUBE  ROLLUP } ]         ]         [ HAVING < search_condition > ] 


At first glance, you appear to be selecting orderDate from the Customers table, but that is not possible because the Customers table does not have an orderDate . The key phrase is:

 join Orders o on o.customerID = c.customerID 

It is as if the join phrase creates a temporary table that is the width of the Customers table and the Orders table joined together. The on keyword dictates how the tables are joined. In this case, the tables are joined on the CustomerID column in Customers and in Orders .

Each record in Customers (represented by the alias c ) is joined to the appropriate record in Orders (represented by the alias o ) when the CustomerID fields match in both records. Similarly, each record in the Order Details record is joined to the Orders table by matching the OrderID column in both tables.

When you join two tables you can say "get every record that exists in either" (this is called an outer join ) or you can say, as we've done here, "get only those records that exist in both tables" (called an inner join ).

Inner joins are the default, so writing join is the same as writing inner join .


The inner join shown above says: get only the records in Orders that match the records in Customers by having the same value in the CustomerID field ( on o.customerID = c.customerID ).

The where clause further constrains the search to those records where the Country field in Customers is an exact match for the string USA and where the orderDate in the orders table is greater than January 1, 1997.

 where c.Country = 'USA' and orderDate > '1/1/97' 

I did not specify the table for the orderDate . I could have written:

 where c.Country = 'USA' and o.orderDate > '1/1/97' 

but since Orders is the only table with an orderDate column, there is no ambiguity if I just use the column name . SQL is able to translate the string "1/1/97" into a DateTime representing January 1, 1997.

B.3.2. Using SQL to Manipulate the Database

SQL can be used for searching for and retrieving data and for creating, updating, and deleting tables and generally managing and manipulating the content and the structure of the database. For example, you can update the ContactName of a specific company:

 Update Customers set ContactName = 'Jesse Liberty' where CompanyName = 'Save-a-lot     Markets' 

For a full explanation of SQL and details on using it well, see Transact-SQL Programming , by Kevin Kline, Lee Gould, and Andrew Zanevsky (O'Reilly).




Programming ASP. NET
Programming ASP.NET 3.5
ISBN: 0596529562
EAN: 2147483647
Year: 2003
Pages: 173

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net