Feature from 2.6 (July/August 2004)

In the previous installment, we learned the basics of the SQL SELECT statement. However, there are more features to it. Aggregate functions can do math for you and ORDER BY and GROUP BY statements provide helpful data organization, and queries can span multiple tables to help link related tables together. We'll also learn how to put data into the database with the INSERT statement and change it with the UPDATE statement. For the examples in this tutorial, we'll assume two tables: Customers and Orders. The databases will be related so that each order has a customer number. Aggregate Functions Each database engine implements certain aggregate functions that will sum data, average data, retrieve the maximum or minimum values, and count the number of results. There may be more functions, but almost every database engine includes AVG, MAX, MIN, SUM, and COUNT. AVG() will return the average values of the data provided. MAX() will return the maximum value in the set of data provided. MIN() will return the minimum value, and SUM() will add the data. COUNT() will return the number of rows it was provided. In most cases, a column name is given to the function, like AVG(Orders.PurchasePrice). However, COUNT() is special and can be supplied with a "*". Organizing returned data SQL provides an ORDER BY statement that is very powerful. If you wanted to order the results of a query by the customer's last name, you would have a query like: SELECT * FROM Customers ORDER BY LastName However, if there are many people who have the same last name, it should resort on sorting next by the customer's first name. To do this, simply change it to: SELECT * FROM Customers ORDER BY LastName, FirstName What if you wanted to find the purchase prices of all orders, sorted by the purchase price? Typically when examining sales, the person wants to see the data in descending order, rather than ascending order. This is done by adding "DESC" following the column you want to be sorted in descending order. For example, the following query will return all the customers' current balances in descending order, while sorting anyone with the same balance by their last name in alphabetical order: SELECT * FROM Customers ORDER BY CurrentBalance DESC, LastName Grouping Data What if you want to know the total made on each product? A simple query like "SELECT ProductName, SUM(PurchasePrice) FROM Orders" will return the same sum in every row. To solve this, the GROUP BY statement is used. It will group rows together by the column you specify. The query will read: SELECT ProductName, SUM(PurchasePrice) FROM Orders GROUP BY ProductName Now, there will only be one result for each product name, and the second column will have the total of all of the prices for that particular product. Without the GROUP BY statement, it would have returned the sum of all purchase prices in the table. Referring to two tables So, we know how to grab the Name of the customer, and find out what the particular product a particular customer ordered. If you used two select statements, it would look something like: SELECT Name FROM Customers WHERE CustomerID=25 SELECT ProductName FROM Orders WHERE CustomerID=25 However, we can do the above queries in a single query by utilizing something called a "join." To get both the Name from the Customers table, and the ProductName from the Orders table, we'll modify the SELECT statement a bit. Let's start with the first query above, and make this modification (modifications will be in italics): SELECT Customers.Name FROM Customers WHERE Customers.CustomerID=25 All this does is makes the query explicit. We want the "Name" field from the Customers table, so we specify Customers.Name. Next, we'll add in the part to get the product name: SELECT Customers.Name, Orders.ProductName FROM Customers, Orders WHERE Customers.CustomerID=25 AND Orders.CustomerID=25 And there we have it: we are getting both the Name and the ProductName from the database. However, what if we want to get it for all of the customers instead of just one? You can make comparisons across tables like this: SELECT Customers.Name, Orders.ProductName FROM Customers, Orders WHERE Customers.CustomerID=Orders.CustomerID This query will return every customer name and what they ordered for each order in the database. Thinking in SQL The SELECT statement is extremely powerful and fairly straightforward. The hardest part most of the time is thinking in SQL. We'll take a look at a query that may need to be run on a our sample database to help illustrate how to think of a problem from a SQL perspective. First, how about finding all of the orders for a dinner-table, and finding out the customer's address for each of those orders? Let's try to figure out what the different parts are -- what are we looking for, what are the constraints, and what's the relationship between the tables? Well, we are looking for the addresses from the Customers table, so we know we have a query resembling: SELECT Address FROM Customers WHERE (...) Next, what are the constraints? The ProductName field on the Orders table needs to be "Table." So, we modify the statement to read: SELECT Customers.Address FROM Customers, Orders WHERE Orders.ProductName='Table' And finally, what is the relationship between Orders and Customers? Well, from our previous examples, it is the CustomerID field in each table. So, we finish the query with: SELECT Customers.Address FROM Customers, Orders WHERE Orders.ProductName='Table' AND Customers.CustomerID=Orders.CustomerID And there's the final query. It helps to break down the problem into the parts that are required for each SELECT statement, and then assembling the query in several steps. Getting data into the database So, now you know how to get data out of a database, how about putting it in? This is accomplished with the INSERT statement. The syntax is: INSERT INTO <Table Name> (<Columns>) VALUES (<Values>) For example, adding an order to our Orders table might look like: INSERT INTO Orders (ProductName, PurchasePrice, CustomerID) VALUES ('Table', 9.50, 37) The comma-delimited list of Columns corresponds directly with the values put in. For example, the first field is ProductName, so in the Values list, the first field is the product's name, Table. The second column is the Purchase price, and the third column is the CustomerID. All fields that aren't specified will take on their default values. For most database engines, this means the unspecified columns will be filled with NULL values. For engines that don't distinguish between NULL fields, they will be filled with default values. Changing the data The UPDATE statement is used to modify the data already in the database. It resembles the SELECT statement, because it uses the WHERE clause. The syntax is: UPDATE <Table Name> SET <Expression> [WHERE <Expression>] To update every record, the WHERE clause doesn't need to be specified. For example, to change every record's purchase price to $7.99, you would say: UPDATE Orders SET PurchasePrice=7.99 However, that isn't very useful or practical. Something more likely to need updating would be to change the address of John Smith. The way to do that would be: UPDATE Customers SET Address='100 Foo St.' WHERE FirstName='John' AND LastName='Smith' Since the WHERE clause is the exact same as the SELECT statement uses, it is just as powerful. Removing data Oops, what if you inserted some data you don't want any more? The DELETE statement will help you remove it. The syntax is: DELETE FROM <Table Name> [WHERE <Expression>] To remove all the rows in the Orders table, the query is "DELETE FROM Orders". However, that isn't very practical for most databases. The optional WHERE clause is the same as what the UPDATE and SELECT statement uses. So, if we want to remove John Smith's customer record, we can say: DELETE FROM Customers WHERE FirstName='John' and LastName='Smith' What's next? The next installment of this tutorial will show a few more advanced SELECT statement features like JOINs and sub-selects. Also, we will cover creating tables, modifying them, and cover a few more miscellaneous features that are not as common, but still are useful when the job calls for them.

Clearance Sale!

Dealing with Databases

Learning SQL Part 2

Print:
PDF: