Schemer's Guide to Structured Query Language

Structured Query Language, or SQL, is a language used to manipulate and extract the information in a database. Data is stored in tables. Tables are structured by rows and fields (columns). Fields are general categories of information, while rows represent related information. SQL is used to manage the tables in a database through queries.

Creating a Database

Before you can start, you need to create a database account on the UVa Server. ITC (Office of Information, Technology and Communication) provides free access to a MySQL database to all UVa students.

1. Open https://dbm1.itc.virginia.edu in a web browser
2. Click "Get/Reset an Account"
3. Enter the information required. For security reasons, you should not use a password you use for anything important. Since you need to use the database password in plaintext in your PHP files to access the database, it is not secure.
4. You'll get a confirmation page if everything went according to plan.
5. Go to https://dbm1.itc.virginia.edu/phpMyAdmin/index.php3
6. A little box pops up asking for your username and the password you just entered.
7. Create a new database for all your CS 200 needs. Call it abc1x_cs200, where abc1x is your username, of course. Hit the "create" button to do this.
8. Now, on the left, click on abc1x_cs200. You are now in the database administration page, where you can run SQL commands (described below) to manipulate your database.

SQL Commands

The basic SQL commands are described by the following grammar:

Commands ::= Command ; Commands
Commands ::= Command
Command ::= CreateTableCommand | InsertCommand | SelectCommand | UpdateCommand | DeleteCommand | DropTableCommand

CreateTable ::= CREATE TABLE Name (FieldDefinitions)
InsertCommand ::= INSERT INTO Table ( FieldNames ) VALUES (Values)
DeleteCommand ::= DELETE FROM Table WhereClause
SelectCommand ::= SELECT Fields FROM Table JoinClause WhereClause OrderClause
UpdateCommand ::= UPDATE Table SET SetList WhereClause
DropTable ::= DROP TABLE Table

SQL commands are case-insensitive (create means the same thing as CREATE). By convention, we will use all capitals to distinguish SQL commands. The non-terminals use in these productions will be described later. This document describes the most important SQL commands; for full details see http://www.mysql.org/doc/.

Creating a Table

You create a table with the CREATE TABLE command (MySQL Documentation):

CreateTable ::= CREATE TABLE Name (FieldDefinitions)
FieldDefininitions::= FieldDefintion, FieldDefinitions
FieldDefinitions ::= FieldDefinition

FieldDefinition ::= FieldName FieldType PrimaryModifier NullModifier IncrementModifier
PrimaryModifier ::= PRIMARY KEY
PrimaryModifier ::=
NullModifier ::= NOT NULL | NULL
NullModifier ::=
OptIncrement ::= AUTO_INCREMENT
OptIncrement ::=

FieldType ::= DATE | TIME | DATETIME | INT | FLOAT | VARCHAR (Number) | TEXT
FieldName ::= Name
FieldName ::= Table.Name

Table ::= name of a table

You can create as many tables in your database as you want. Each table has any number of rows, but must have at least one field (note that CreateDefinitions cannot be replaced with the empty string, there must always be at least on definition).

In addition, at least one of the fields must be a primary key. A primary key is used to uniquely identify rows in the table. The easiest example usually concerns names. Let's say you had a table of your friends. You may have two friends named "John Smith". How would you tell them apart in your table? You need another field, in addition to name, that assigns them something unique. It could be their e-mail address, or their social security number. For our puposes, however, let's just use integers that counts starting from 1. Here's how:

CREATE TABLE CSBooks (
  BookID INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  Title VARCHAR (30),
  Author VARCHAR (30),
  Price FLOAT,
  Publisher VARCHAR (30),
  Year INT
);

Try this yourself by pasting this code in the Run SQL query ... form you get from https://dbm1.itc.virginia.edu/phpMyAdmin/index.php3 (after creating an account and database as described above) and clicking the Go button.

Inserting Rows

Now that we have created a table, we can put data in our table using the insert command: (MySQL Documentation)

InsertCommand ::= INSERT INTO Table ( FieldNames ) VALUES (Values)

Values ::= Value Values
Values ::= Value ::= Number | String | Date | Boolean

FieldNames ::= FieldName , FieldNames
FieldNames ::= FieldName

To put a new field into our CSBooks table we could use the following insert command:

INSERT INTO CSBooks (Title, Author, Price, Publisher, Year)

   VALUES ('The Mind''s I', 'Hofstadter', 18.95, 'Bantam', 1985)

Try evaluating this yourself by pasting this code in the Run SQL query ... form you get from https://dbm1.itc.virginia.edu/phpMyAdmin/index.php3. Click on Browse to see the table.

Two idiosyncrasies to note. Firstly, notice that we enclose a string with single quotes ('). Numbers and dates are not enclosed. However, one of the fields (Title) has a single quote in the text. If we wrote 'The Mind's I' , then SQL would think the book is called "The Mind" and that "s I" was some sort of SQL command. Instead, we need to use '' to make the single quote. SQL treats two consecutive single quotes differently as a single quote in the quoted string.

Secondly, note that we do not tell SQL what to insert in the BookID field. This is because when we created the table we used AUTO_INCREMENT to indicate that the BookID field should automatically increment when we insert new entries in our table. This means that the database will automatically assign the next available integer (in this case, 7) to be the key for this row.

The table now looks like this:

BookID	Title	Author	Price	Pubisher	Year
1	The Mind's I	Hofstadter	18.95	Bantam	1985

We can insert a few more entries into our table the same way:

INSERT INTO CSBooks (Title, Author, Price, Publisher, Year)
   VALUES ('GEB', 'Hofstadter', 19.95, 'Basic', 1979);

INSERT INTO CSBooks (Title, Author, Price, Publisher, Year)
   VALUES ('Cryptonomicon', 'Stephenson', 14.25, 'Perennial', 1999);

INSERT INTO CSBooks (Title, Author, Price, Publisher, Year)
   VALUES ('The Code Book', 'Singh', 14.00, 'Anchor', 2000);

INSERT INTO CSBooks (Title, Author, Price, Publisher, Year)
   VALUES ('Snow Crash', 'Stephenson', 13.75, 'Bantam', 1992);

INSERT INTO CSBooks (Title, Author, Price, Publisher, Year)
   VALUES ('Fermat''s Enigma', 'Singh', 11.85, 'Anchor', 1997);

After this, our table is:

BookID	Title	Author	Price	Publisher	Year
1	The Mind's I	Hofstadter	18.95	Bantam	1985
2	GEB	Hofstadter	19.95	Basic	1979
3	Cryptonomicon	Stephenson	14.25	Perennial	1999
4	The Code Book	Singh	14.00	Anchor	2000
5	Snow Crash	Stephenson	13.75	Bantam	1992
6	Fermat's Enigma	Singh	11.85	Anchor	1997

Delete

The delete command removes entries from a table: (MySQL Documentation)

DeleteCommand ::= DELETE FROM Table WhereClause
WhereClause ::= WHERE Conditions
WhereClause ::=

Conditions ::= Condition Conjunction Conditions
Conditions ::= Condition
Conjunction ::= AND | OR
Condition ::= Field Comparator Value
Comparator ::= < | > | = | <= | >=

The where clause of a delete command specifies which rows to delete using boolean conditions. All rows for which the Conditions specified in the where clause are true are permanently removed from the table. For example, to remove all books published by Anchor that cost more than $12.00, we could use the following query:

DELETE FROM CSBooks WHERE Publisher='Anchor' AND Price > 12.00

This removes the entry for The Code Book from our table. If no conditions are specified in the delete command every entry will be deleted, so be careful! If you only want to delete one entry, the safest bet is to tell it to delete based on the primary key. For example, to delete The Mind's I do:

DELETE FROM CSBooks WHERE BookID=1

After this, our table is:

BookID	Title	Author	Price	Publisher	Year
2	GEB	Hofstadter	19.95	Basic	1979
3	Cryptonomicon	Stephenson	14.25	Perennial	1999
5	Snow Crash	Stephenson	13.75	Bantam	1992
6	Fermat's Enigma	Singh	11.85	Anchor	1997

Select

The select command is used to extract a table from a table: (MySQL Documentation)

SelectCommand ::= SELECT FieldSpecifier FROM Table JoinClause WhereClause OrderClause

FieldSpecifier ::= Fields Fields ::= Field, Fields
Fields ::= FieldModifier Field
Fields ::= *

Field ::= name of a field in the table AS Name
Field ::= name of a field in the table

FieldSpecifier ::= Operator ( Fields ) AS Name
Operator ::= AVG | COUNT | MIN | MAX | SUM

FieldModifier ::= DISTINCT
FieldModifier ::=

JoinClause ::= INNER JOIN Table ON expandedField = expandedField
JoinClause ::=

OrderClause ::= ORDER BY Field Descending
Descending ::= DESC
Descending ::=

A select command evaluates to a new table containing the selected entries. For example, if we wanted to get all the titles and their authors from our CSBooks table we could use the following query:

SELECT Title, Author

FROM CSBooks

The result would be a table that looks like this:

Title	Author
GEB	Hofstadter
Cryptonomicon	Stephenson
Snow Crash	Stephenson
Fermat's Enigma	Singh

To select all of the fields, the asterix (*) can be used instead of listing field names.

We can add a where clause to our query to make it more specific. Where clauses are the same as was introduced for the delete command. For example, we could use the following query to find all the books by Stephenson that are less then $15:

SELECT * FROM CSBooks
   WHERE Author='Stephenson' AND Price < 14.00

This would result in the table:

BookID	Title	Author	Price	Publisher	Year
5	Snow Crash	Stephenson	13.75	Bantam	1992

We can also use SELECT queries to do simple calculations on our results. To find the total price of all books published before 1990 we could use the following query:

SELECT SUM(Price) AS Total FROM CSBooks WHERE Year < 1995

When performing calculations, you are, in essence, creating a new field. Therefore you must give it a name using AS. The result of this query would be:

Total

33.70

Other calculations available include AVG to find an average of a set of numbers, COUNT to find out how many rows of results there are, MIN to find the minimum value, and MAX to find the maximum. The following query will count how many books Anchor has published:

SELECT COUNT(*) FROM CSBooks WHERE Publisher="Anchor"

We may want a list of the authors in our table. For this situation we would not want duplicate names. This can be accomplished using the DISTINCT keyword in the following manner:

SELECT DISTINCT Author

FROM CSBooks

This will automatically filter all duplicates from the results and will produce the table

Author

Hofstadter

Singh

Stephenson

SQL can also specify that the results should be sorted by a particular field by adding an order clause at the end of the select statement. To get a list of the books sorted by date we could use the query

SELECT * FROM CSBooks ORDER BY Year

which will result in the table

BookID	Title	Author	Price	Pubisher	Year
2	GEB	Hofstadter	19.95	Basic	1979
5	Snow Crash	Stephenson	13.75	Bantam	1992
6	Fermat's Enigma	Singh	11.85	Anchor	1997
3	Cryptonomicon	Stephenson	14.25	Perennial	1999

To sort the list in the reverse order, add the keyword DESC to the end of the query.

Join

There is one option in a select command, called a join, that is useful when dealing with multiple tables. A join combines two tables by a common field. Suppose we have the following table named Orders, of people who have ordered books. Note that we reference the books by BookID since that is the primary key of the CSBooks table.

BookID	Name
2	Ada
5	Gödel
3	Bach
6	Bach

To get a full list of orders we could use the following query:

SELECT Name, Title, Author, Price, Publisher, Year 

FROM (

  Orders INNER JOIN CSBooks

  ON Orders.BookID = CSBooks.BookID

The resulting table looks like this:

Name	Title	Author	Price	Publisher	Year
Ada	GEB	Hofstadter	19.95	Basic	1979
Gödel	Snow Crash	Stephenson	13.75	Bantam	1992
Bach	Cryptonomicon	Stephenson	14.25	Perennial	1999
Bach	Fermat's Enigma	Singh	11.85	Anchor	1997

The join is a very powerful mechanism. By combining joins with order and where clauses you can express compilcate queries on databases.

There are many different ways to express the same join. Just so you're not confused by other people's code, the following three SQL statements are precisely identical to each other:

SELECT Name, Title, Author, Price, Publisher, Year FROM CSBooks
   JOIN Orders WHERE CSBooks.Publisher = 'Anchor' AND CSBooks.BookID = Orders.BookID

 

SELECT Name, Title, Author, Price, Publisher, Year FROM CSBooks, Orders
   WHERE CSBooks.Publisher = 'Anchor' AND CSBooks.BookID = Orders.BookID

SELECT Name, Title, Author, Price, Publisher, Year FROM Orders
   INNER JOIN CSBooks USING (Title) WHERE CSBooks.Publisher = 'Anchor'

Select can do much more than what is described here, see the MySQL Documentation if you need to do a more complicated selection.

Update

An update command can be used to change information already in the table. Update has a similar syntax to the other queries with some extra information to specify the information to change using the SET keyword:

UpdateCommand ::= UPDATE Table SET SetList WhereClause

SetList ::= Set, SetList
SetList ::=
Set ::= Field = Value

For example, the following query will change all of Stephenson's publishers to Doubleday and raise the price to 49.95.

UPDATE CSBooks SET Publisher='Doubleday', Price=49.95 WHERE Author='Stephenson'

Like the delete command, if no conditions are specified all of the rows will be set with the given information. Also like the DELETE query, you'll also want to reference the primary key to ensure that you're operating on the rows that you think you are.

Drop Table

The drop table command permanently removes an entire table from the database:

DropTable ::= DROP TABLE Table

Be careful! You lose everything in the table after a drop command.

Credits: This guide was created by Jon Erdman, Portman Wills and David Evans.

University of Virginia Department of Computer Science CS 200: Computer Science

David Evans evans@virginia.edu Using these Materials