ƒ First Name: Jerry

ƒ Last Name: Maxon

ƒ Address: 123 N. Hampton Lane

3. Change the name of the last record you added from "Jerry" to "Sam."


4. Delete all records from the Customers table whose first name is Jerry.


5. Create an SQL statement that returns the number of customers whose names begin
   with M.


6. Create an SQL statement that returns the average number of products each
   customer purchases. (Hint: use the quantity column.)


7. Create an SQL statement that returns all the customers who have placed an order in
   the past month, sort it in ascending alphabetic order, and group the records by date.

Day 10: Letting MySQL Do the Work—Intrinsic Functions

Overview

Almost all databases provide the user with a series of common functions. These functions provide
administrators or people querying the database a way of performing complex tasks within a query. Some of
these tasks may be string manipulation or complex mathematical equations. This allows users to take
advantage of the more powerful processing power of a database server.

MySQL provides a lot of intrinsic functions. Today, you will learn

ƒ Programming at the server level and how MySQL can help

ƒ Basic functions, such as numerical operations, string maniplations, and logical

conditions

Programming at the Server Level

A lot of discussion goes on about where to put the programming logic. Do you let the application that is
installed on the user's desktop do all the heavy processing, or do you let it take place on the database
server? What about a third place—a middle tier? The answers to these questions are not easy. A lot
depends on the application and the purposes for which it is used. A traditional Web-based application has
different needs than a three-tier application. These needs vary from heavy database traffic, as in a Web-
based store, to heavy data manipulation, as in a finance application. The remainder of this section takes a
look at some scenarios and explains why and where the best place to put the logic is.

The first example is a Web-based application, such as the Meet-A-Geek Web site. The following are the

general steps in a Web-based application:

1. A user types the URL of a Web site into his or her browser.


2. The browser then finds the server and requests a Web page.


3. The Web server answers the request and sends the page.


4. The browser receives the page.


5. The user navigates around the site repeating steps 1–4 until he or she gets to a page
   that requires some sort of database access (a form or dynamically generated page).


6. A program or script runs, getting the requested data from the database server.


7. The page is then returned to the user.

As you can see, there are a lot of trips back and forth to the database and Web servers. Now imagine a

site that receives a lot of traffic—the trips and requests to and from the server could be astronomical.

To help ease the load on the servers, you could add the programming logic at the client. The client is

the computer or application that is either receiving or sending the requests. The more logic you can

place at the client level, such as error handling or string manipulation, the better off you are.

Now don't think you can't use any intrinsic functions on the database server—you can, just consider the

size of your database and the amount of traffic you think that your site is going to have. If you have a

small site with a relatively small amount of data, you are pretty free to do anything you want. But if your

site is extremely busy and you need a lot of calculations done or a lot of string manipulation needs to be

provided, think about placing this logic on the client.