Conceptual Explanation

In the terminology of SQL, everything is a query, even if the query does not ask what to us would be a question. Some queries potentially return a substantial amount of information in the form of what is termed a recordset, which is a dynamically constructed table of information. The qualifier “potentially” is used with the term substantial because the recordset might be empty. Other queries, such as one to create a table or to insert a record, return true or false indicating success or failure.

Recall that databases are made up of tables; tables are made up of records; and records are made up of fields. In this chapter, you will see examples of the basic SQL statements. There are many statements, with many variations. As is the case with most computer software, you can do useful work even if you do not know every variant of every statement or command.

SQL Statements

SQL has statements for creating databases and then creating the tables within the databases. The create statement requires you to specify the name, the datatype, and, optionally, other aspects of each field.

One field can be designated as being the primary key. This means two things to the DBMS—say, Access or MySQL. First, a field designated as a primary key must contain unique values. The system will signal an error and not allow a record to be added to the database if the primary key field has a value already in the database. The second point of significance about a primary key is that the system will create an index for it. This is an internal mechanism to speed access to the record using values for the index field. You can use the create statement to designate any field an index field. You do this if and only if your application will involve looking up records using that field a significant amount of time, because building an index costs something in terms of time and storage space.

Fields can be designated as required, meaning that any new record needs to have a value for the field. The definition of a field can include a directive for the DBMS to generate a value by incrementing an internal counter. This is a common setting for a primary field, but is not necessary. Your application might use what could be called a naturally occurring or intrinsic value for a primary key, such as a social security number. Fields that are not required do not have to be set when a new record is inserted into the database table.

SQL also has a command to alter the definition of a table. You can add new fields (called columns in some databases), add indexes, change or modify datatypes, and other operations. Use of the alter statement does require that you consider carefully what to do about the data already in the table.

The SQL drop statement is used to delete an entire table, something that you might decide to do during development. However, it is also something that could happen during normal operations. Some software has provisions for what are called temporary tables, and others require you to take care of the creation and deletion of the tables directly. You will see this in the quiz show projects.

Once the database tables are defined, it is time to add data. The SQL command for that is the insert statement. One variant of the insert statement allows you to specify a set of fields with a set of values. The variant that will be used here sets values for all of the fields defined for the table.

Databases typically are not static. The SQL statement to change the information in a database is update. The update statement allows you to change a field to a value specified using an expression, which could involve the original value of the field. The update statement can apply to this field for all the records or, more commonly, just certain records designated using a condition held in the where clause of the statement.

You can also change a database by deleting records. The SQL delete statement has clauses to specify which records are to be deleted and/or how many are to be deleted.

All the SQL queries mentioned so far perform some change to the database. The only value returned, assuming that SQL is used by a program, is a return code indicating if the operation performed successfully.

Just as databases typically are not static, they are also not intended to be “write only” fields. The SQL select statement is the vehicle for gathering information from the database. The SQL produces what is called a recordset. A recordset is like a table and like an array, but it is best thought of as having its own properties. Recordsets have rows and columns. A pointer indicates what row is the current row of a recordset. You will learn how to manipulate a recordset in subsequent chapters.

You can think of the select statement as being a mini-program all on its own. A select statement allows you to:

• Specify the fields from one or more tables. The use of more than one table is called a join, and you can indicate how this join operation is to be done using what is called the on clause or the where clause.

• Specify functions such as the sum, average, min, max, and count for the values of fields for all the records selected or by group.

• Group records by the value of a field using the group by clause. This provides a way to produce summary or aggregated information for specified groups.

• Order the result in regular (ascending) or descending order by one or more fields using order by or order by desc clauses.

• Specify which records using the where clause with one or more conditions. The conditions can involve field values from more than one table.

• Limit the number of records chosen. The limit clause can be used to specify the first N records, or it can be used for something like paging in which you specify a set of records starting at the P position for N more records.

• Designate a condition based on a calculation done after grouping using the having clause. This allows you to limit reports based on conditions calculated in terms of the summary functions.

Joins

Two types of join operations will be described in this text: the simple join and the left join. The simple join has other names. Explaining joins requires concrete examples; more follow in the Examples section of this chapter. Remember the database in Chapter 5, “Database Basics,” with catalog, customers, and orders. Table 7.1 holds sample data for the catalog table; namely, product records. Table 7.2 holds data on customers. Table 7.3 holds orders.

Table 7.1: Catalog Records

prod_id	prod_name	cost	category	prod_desc
1	Bird	2.50	Animal	Flapping bird
2	Crane	3.00	Animal	Traditional crane
3	Box	1.00	Box	Magazine cover box
4	Ornament	2.00	Modular	Six-part preliminary bas

Table 7.2: Customer Records

cust_id	cust_name	billing	shipping
1	John Smith	cash	123 Main Street NY, NY 12345
2	Mary Jones	Mastercard XX	10 Elm Street, apt 23 Town, State 11111

Table 7.3: ORDERS

ord_id	prod_id	cust_id	qty	date	status
1	3	2	5	Sept 23, 2002	Shipped
2	1	2	3	Sept 23, 2002	Backordered
3	2	1	3	Sept 24, 2002	Shipped
4	1	1	4	Sept 24, 2002	Backordered
5	4	2	1	Sept 25, 2002	Shipped

Suppose you need to examine all the information on all orders by a customer with cust_id equal to 2. You could obtain this information by the following select statement:

    SELECT * FROM orders WHERE cust_id=2

For this situation, you only need to look at the orders table. Before continuing with the explanation of join, please note that the where condition made use of a single equal sign (=) to test for equality. This is in contrast with what you will need to use to test for equality in PHP or JavaScript, where you need to use two equal signs together (==).

However, what if your question actually concerned the orders by Mary Jones, or, more realistically, the orders for each customer? You could perform the first task operation using two queries: one to the customer table to get Mary’s cust_id, and the other to make the query against the orders table. To get this information for each customer, you could do multiple pairs of queries. However, the join facility allows you to do everything in one step. The task is to examine the customers table and the orders table together. Moreover, instead of joining every record (row) of the customer table with every record of the orders table, you will join “on” the records having the same value of the cust_id fields. The statement:

    SELECT * FROM customer JOIN orders      ON customers.cust_id = orders.cust_id

produces the temporary set of records shown in Table 7.4.

Table 7.4: Join of Customer and Orders

cust_id	cust_name	billing	shipping	ord_id	prod_id	cust_id	qty	date	status
1	John Smith	Cash	123 Main Street NY, NY 12345	3	2	1	3
1	John Smith	Cash	123 Main Street NY, NY 12345	4	1	1	4
2	Mary Jones	Mastercard XX	10 Elm Street, apt 23 Town, State 11111	1	3	2	5
2	Mary Jones	Mastercard XX	10 Elm Street, apt 23 Town, State 11111	2	1	2	3
2	Mary Jones	Mastercard XX	10 Elm Street, apt 23 Town, State 11111	3	4	2	1

If you do not want all of this, you can select just some of the fields. You might be especially annoyed by the presence of the cust_id field from the customer table, and then the same value again from the orders table, but this is what is there. The select statement to get the information with just the customer name and the order information is:

    SELECT customer.cust_id, order_id, prod_id, qty, date,      status FROM customer JOIN orders ON customer.cust_id =     orders.order_id

You do not need to use the same name for the customer id field when you design the two tables. If you do, you must use the table name with a dot to “tell” the system which field you wanted in any case when it is ambiguous, even in cases such as this one when it does not matter because those two fields are the same. The table dot construction is called a qualifier. The qualifiers are not necessary for the other fields selected, although some professionals feel it is good practice to include them. SQL provides a mechanism for creating a shorthand:

    SELECT c.cust_id, o.order_id, o.prod_id, o.qty, o.date,      o.status FROM customer as c JOIN orders as o      ON c.cust_id = o.cust_id

There is an alternative way to perform this same task of getting the customers’ order information. The English explanation of it is subtly different. How about joining together the two tables completely, with all rows of one table paired with all rows of the other, but then selecting only those rows satisfying the condition of equal cust_id fields? The select statement is:

    SELECT c.cust_id, o.order_id, o.prod_id, o.qty, o.date,      o.status FROM customer as c, orders as o      WHERE c.cust_id = o.cust_id 

This select statement is doing a join, but the JOIN term goes away. It is replaced by the comma between the mentions of the table names. The WHERE condition does the work of the ON condition. You will find yourself coming up with one or the other of these in different situations. As has been stated, at some point in development, review the select statements to see if you are using the most efficient form. The MySQL explain command is helpful here. Alternatively, some DBMS software provides ways to compile queries to improve performance. This is also called stored procedures.

The left join is a feature of the select statement to be used to detect the absence of records. Assume one more entry in the customer table: a Sally Bean, with address, billing, and shipping information. The record is shown in Table 7.5.

3

Sally Bean

Visa

Hilltop Town, State 22222

Assume no additions to the orders table—Sally has not ordered anything. The SQL statements indicated previously, both the one with the on condition and the one with the where condition, will produce the same results as indicated. This could be what you want. However, if you want the customers who have not ordered anything to be mentioned, you can use the following SQL statement:

SELECT customer.cust_id, order_id, prod_id, qty, date, status FROM customer LEFT JOIN orders ON customer.cust_id = orders.order_id

This will produce the result shown in Table 7.6.

Table 7.6: Left Join of Customer and Orders

cust_id	cust_name	billing	shipping	ord_id	prod_id	cust_id	qty	date	status
1	John Smith	Cash	123 Main Street NY, NY 12345	3	2	1	3
1	John Smith	Cash	123 Main Street NY, NY 12345	4	1	1	4
2	Mary Jones	Mastercard XX	10 Elm Street, apt 23 Town, State 11111	1	3	2	5
2	Mary Jones	Mastercard XX	10 Elm Street, apt 23 Town, State 11111	2	1	2	3
2	Mary Jones	Mastercard XX	10 Elm Street, apt 23 Town, State 11111	3	4	2	1
3	Sally	Bean	Hilltop Town, State 22222	Null	Null	Null	Null	Null	Null

Since the table on the right—namely, orders—had no rows with cust_id the same as that of Sally Bean, there is exactly one row joined with Sally’s record and the values are null. You can check for these null values and you will see an example of this in the quiz show application covered in Chapter 15, “Quiz Show.”