CS1120: Schemer's Guide to SQL

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 UniqueModifier NullModifier IncrementModifier
PrimaryModifier ::= PRIMARY KEY
PrimaryModifier ::=
UniqueModifier ::= UNIQUE
UniqueModifier ::=
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://dbm2.itc.virginia.edu/phpmyadmin/ (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://dbm2.itc.virginia.edu/phpmyadmin/ . 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

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

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

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. It was revised for CS 150 in Spring 2009 by Westley Weimer.

CS1120: Schemer's Guide to SQL

cs1120 Spring 2010

classes book syllabus ps exams guides forum

cs1120: Computer Science
from Ada and Euclid to Quantum Computing and the World Wide Web

Instructor
Westley Weimer

Teaching Assistants
David Noble
Kinga Dobolyi
Nick Wasilewski
Patrick Mutchler
Rachel Rater
Zak Fry
Email Address
cs1120-staff@cs.virginia.edu

Class Meetings
Mondays and Wednesdays, 3:30-4:45pm in MEC 341

Office & Lab Hours
Mon 12:30-13:30 Small Hall Lab
Mon 13:30-14:00 Olsson 219
Mon 14:00-15:00 Small Hall Lab
Mon 17:00-19:30 Thornton Stacks
Tue 11:00-12:30 Olsson 001
Wed 10:30-13:00 Thornton Stacks
Wed 13:30-14:00 Olsson 219
Thu 10:00-12:30 Thornton Stacks
Sun 13:00-14:30 Olsson 001
Sun 14:30-17:00 Olsson 001

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