Conducting business in Japan is pretty easyonce you know the language. The same is true of SQL Server: It's pretty easy to manipulate and access data, once you know the language. In this case, the language is SQL, or Structured Query Language. Originally developed by IBM, SQL has since become a standard across the database industry. Well, kind of. As with America and England, Microsoft's SQL Server and Oracle's "Oracle" are two relational databases divided by a common language. The core parts of the SQL language are pretty consistent between vendors, but each supplier adds a lot of extra features and syntax variations designed by Edgar Codd wannabes.
This section describes those DDL and DML statements that will be most useful in our development of the Library program. You'll be glad to know that SQL isn't too picky about the formatting of the various statements. Upper- and lowercase distinctions are ignored; SELECT is the same as select is the same as SeLeCt. (Traditional SQL code is mostly uppercase. I use uppercase for all keywords, and mixed case for tables, fields, and other custom items. Whatever you choose, consistency is important.) Also, employ whitespace as you see fit. You can put statements on one gigantic line, or put every word on a separate line. The only time whitespace and case matters is in the actual data text strings; whatever you type, that's how it stays.
SQL statements normally end with a semicolon, but some tools do not require you to include the semicolon, and other tools require that you exclude it. When using the SQL Server visual client tools (Management Studio and Management Studio Express), semicolons are optional, but it's a good idea to include them when you are using multiple statements together, one after another. SQL statements used in Visual Basic code never include semicolons.
Later, when you look at a SQL script I wrote, you will see the word "GO" from time to time. In SQL Server, this command says, "For all of the other statements that appeared so far, go ahead and process them now."
This may come as a shock to you, but before you can store any data in a table, you have to create the table. SQL has just the tool to do this: the CREATE TABLE statement. It's one of the many DDL statements. The basic syntax is pretty straightforward.
CREATE TABLE tableName ( fieldName1 dataType options, fieldName2 dataType options, and so on... )
Just fill in the parts, and you're ready to populate (data, that is). Table and field names are built from letters and digits; you can include spaces and some other special characters, but it makes for difficult coding later on. Each vendor has its own collection of data types; I'll stick with the SQL Server versions here. The options let you specify things such as whether the field requires data or not, whether it represents the table's primary key, and other similar constraints. Extensions to the syntax let you set up constraints that apply to the entire table, indexes (which let you sort or search a specific column more quickly), and data storage specifics.
Here's a sample CREATE TABLE statement that could be used for the table of order line items (refer to Table 4-4).
CREATE TABLE LineItems ( RecordID bigint IDENTITY PRIMARY KEY, OrderID bigint NOT NULL, CustomerID varchar(20) NOT NULL REFERENCES Customers (CustomerID), ProductID varchar(20) NOT NULL, Quantity smallint NOT NULL )
The IDENTITY keyword lets SQL Server take charge of filling the RecordID field with data; it will use a sequential counter to supply a unique RecordID value with each new record. The PRIMARY KEY clause identifies the RecordID field as the unique identifying value for each record in the table. The bigint and smallint data types indicate appropriately sized integer fields, while the varchar type provides space for text, up to the maximum length specified in the parentheses (20 characters). The REFERENCES option clause identifies a relationship between this LineItems table and another table named Customers; values in the LineItems.CustomerID field match the key values from the Customers.CustomerID field. (Note the "dot" syntax to separate table and field names. It shows up everywhere in SQL.) References between tables are also known as foreign references.
If you need to make structure or option changes to a table or its fields after it is created, SQL includes an ALTER TABLE statement that can change almost everything in the table. Additionally, there is a related DROP TABLE statement used to get rid of a table and all of its data. You might want to avoid this statement on live production data as the users tend to get a bit irritable when their data suddenly disappears off the surface of the earth.
Table 4-5 summarizes the available data types used in SQL Server.
Although DDL statements are powerful, they aren't used that much. Once you create your database objects, there's not much call for tinkering. The DML (Data Manipulation Language) statements are more useful for everyday data surfing.
The INSERT statement adds data records to a table. Data is added to a table one record at a time. (A variation of INSERT lets you insert multiple records, but those records must come from another existing table source.) To use the INSERT statement, specify the destination table and fields, and then the individual values to put into each field. One data value corresponds to each specified data column name.
INSERT INTO LineItems (OrderID, CustomerID, ProductID, Quantity) VALUES (10002, 'BW3', 'BEV01COF', 1)
Assuming this statement goes with the CREATE TABLE statement written earlier, this insert action will add a new record to the LineItems table with five new fieldsfour specified fields, plus the primary key automatically added to the RecordID field (because it was marked as IDENTITY). SQL Server also does a variety of data integrity checks on your behalf. Each data field you add must be of the right data type, but you already expected that. Because we designed the CustomerID field to be a reference to the Customer table, the insert will fail if customer 'BW3' does not already exist in the Customer table.
Numeric literals can be included in your SQL statements as needed without any additional qualification. String literals are always surrounded by single quotes, as is done for the customer and product IDs in this INSERT statement. If you need to include single quotes in the literal, enter them twice.
Surround literal date and time values with single quotes.
Such date and time values accept any recognized format, although you should use a format that is not easy for SQL Server to misinterpret.
Many field types support an "unassigned" value, a value that indicates that the field contains no data at all. Such a value is known as the "null" value, and is specified in SQL Server using the "NULL" keyword. You cannot assign NULL to primary key fields, or to any field marked with the "NOT NULL" option.
To remove a previously added record, use the DELETE statement.
DELETE FROM LineItems WHERE RecordID = 92231
The DELETE statement includes a "WHERE" clause (the "WHERE RecordID = 92231" part). WHERE clauses let you indicate one or more records in a table by making comparisons with data fields. Your WHERE clauses can include AND and OR keywords to join multiple conditions, and parentheses for grouping.
DELETE FROM LineItems WHERE OrderID = 10001 AND ProductID = 'BRD05RYE'
Such a DELETE statement may delete zero, one, or 1,000 records, so precision in the WHERE clause is important. To delete all records in the table, exclude the WHERE clause altogether.
DELETE FROM LineItems
The UPDATE statement also uses a WHERE clause to modify values in existing table records.
UPDATE LineItems SET Quantity = 4 WHERE RecordID = 92231
Assignments are made to fields with the SET clause; put the field name (Quantity) on the left side of the equals sign, and the new value on the right (4). To assign multiple values at once, separate each assignment with a comma. You can also include formulas and calculations.
UPDATE LineItems SET Quantity = Quantity + 1, ProductID = 'BEV02POP' WHERE RecordID = 92231
As with the DELETE statement, the UPDATE statement may update zero, one, or many records based on which records match the WHERE clause.
The final DML statement, and the one most often used, is SELECT.
SELECT ProductID, Quantity FROM LineItems WHERE RecordID = 92231
SELECT scans a table (LineItems), looking for all records matching a given criteria (RecordID = 92231), and returns a smaller table that contains just the indicated fields (ProductID and Quantity) for the matching records. The most basic query returns all rows and columns.
SELECT * FROM LineItems
This returns all records from the table in no particular order. The asterisk (*) means "include all fields."
The optional ORDER BY clause returns the results in a specific order.
SELECT * FROM LineItems WHERE Quantity > 5 ORDER BY ProductID, Quantity DESC
This query returns all records that have a Quantity field value of more than five, and sorts the results first by the ProductID column (in ascending order), and then by the numeric quantity (in descending order, specified with DESC).
Aggregate functions and grouping features let you summarize results from the larger set of data. The following query documents the total ordered quantity for each product in the table.
SELECT ProductID, SUM(Quantity) FROM LineItems GROUP BY ProductID
You can use joins to link the data from two or more distinct tables. The following query joins the LineItems and Customer tables on their matching CustomerID columns. This SELECT statement also demonstrates the use of table abbreviations (the LI and CU prefixes) added through the AS clauses; they aren't usually necessary, but they can help make a complex query more readable.
SELECT LI.OrderID, CU.CustomerName, LI.ProductID FROM LineItems AS LI INNER JOIN Customer AS CU ON LI.CustomerID = CU.CustomerID ORDER BY LI.OrderID, CU.CustomerName
This table uses an "inner join," one of the five main types of joins, each of which returns different sets of records based on the relationship between the first (left) and second (right) table in the join.
Joining focuses on the relationship that two tables have. (This use of "relationship," by the way, is not the basis for the term "relational database.") Some tables exist in a "parent-child" relationship; one "parent" record has one or more dependent "child" records in another table. This is often true of orders; a single "order header" has multiple "line items." This type of relationship is known as one-to-many, because one record is tied to many records in the other table. And the relationship is unidirectional; a given child record does not tie to multiple parent records.
A one-to-one relationship ties a single record in one table to a single record in another table. It's pretty straightforward, and is often used to enhance the values found in the original record through a supplementary record in a second table.
In a many-to-many relationship, a single record in one table is associated with multiple records in a second table, and a single record in that second table is also associated with multiple records in the first table. A real-world example would be the relationship between teachers and students in a college setting. One teacher has multiple students in the classroom, but each student also has multiple teachers each semester. Practical implementations of many-to-many relationships actually require three tables: the two related tables, and a "go between" table that links them together. I will show you a sample of such a table in the "Project" section of this chapter.
Beyond Basic SQL
The sample statements I listed here scratch only the surface of the data manipulation possibilities available through SQL. But by now you should have noticed that SQL is remarkably English-like in syntax, much more than even Visual Basic. In fact, the original name for the languageSEQUELwas an acronym for "Structured English Query Language." As the SQL statements get more complex, they will look less and less like an eighth-grade essay, and more like random collections of English words.
The goal here is to introduce you to the basic structure of SQL statements. Most of the statements we will encounter in the Library Project will be no more complex than the samples included here. If you're hungry for more, the "Books Online" component installed with SQL Server (a separate download for the Express Edition) has some pretty good usage documentation. There are also several good books available on the ins and outs of SQL, including vendor-specific dialects.