Office of Digital Humanities

SQL – Structured Query Language

As mentioned, SQL is the most commonly used language for manipulating and retrieving data from database tables. It was originally proposed by IBM researchers in the 1970s, and several companies produced commercial relational databases using their own custom versions of SQL. It was not until 1986 that the language was formally standardized by ANSI, the American National Standards Institute. Several “dialects” of SQL exist today, most of which adhere to the core standard, but which also extend the language in an effort to maintain an edge against competitors.

The core function of SQL is to allow database users to:

• CREATE databases and tables
• INSERT, UPDATE or DELETE data
• SELECT data of interest

Basic SQL syntax

SQL is a deceptively simple language that can be used to create very sophisticated database queries. When you are working with existing databases the following four commands will do almost everything you need. (SQL is case-insensitive, but language elements are shown in ALL CAPS by convention, to differentiate them from the variable data.)

SELECT fields FROM tableName WHERE field=value;

INSERT INTO tableName (field1, field2, field3, etc) VALUES (value1, value2, value3, etc);

UPDATE tableName SET field1=value1,field2=value2,etc WHERE field=value;

DELETE from tableName WHERE field=value;

Exercise 1: Creating a Database

5. Now create a table in your new database using the CREATE TABLE command. When you create a table you also create one or more fields in the table. For each field you must specify the type of data that the field will contain and the maximum length of the data. See this link for a summary of the most common data types.

     CREATE TABLE people
      (person_id int(4), fname varchar(32), lname varchar(32), hire_year year, phone varchar(14));
   

6. You can verify that the table was created by using:
   

     sqlite>   .tables
   

     mysql>   SHOW TABLES;
   

   
   

7. Add a couple of records to the new people table using the SQL INSERT command (make up your own names for the records you're inserting):

      INSERT INTO people (person_id,fname,lname,hire_year,phone) VALUES (1357,'Jack','Black','1999','123-456-7890');
   

8. Use a SELECT statement to make sure it worked.
   

      SELECT * FROM people;
   

   You should see all of the records you just added.

9. You can continue on in this way to create all of your tables and inserting records into them. To save time, let's switch to an already-completed database before we continue working.

   SQLite: Download the file "acmeco.sqlite" from the DigHT 310 server under lesson_materials > Databases and save it to your Desktop. Now, in the terminal type:

     .exit
   

   This will quit sqlite3. Immediately re-enter sqlite3 using the path to the newly-downloaded file:

     sqlite3 /path/to/desktop/acmeco.sqlite
   

   Of course, using the proper file path for your computer. This will open SQLite with the new database.

   MySQL: Normally when using MySQL you would need to coordinate with the server administrator at this point to set up access permissions to your new database. To save time, for the next several items we will use an already existing database. Switch to the acmeco database using

      USE acmeco;
   

   
   

10. Now add several records to the new table using the INSERT command. Note that you don't have to fill every field for each record:
    

       INSERT INTO people (fname,lname,phone) VALUES ('Milton','Chilton','123-456-7890');
    

11. Use a SELECT statement to make sure it worked.
    

       SELECT * FROM people;
    

12. You can change the contents of a field in a record using the UPDATE command.
    First try it with no WHERE clause. What happens?

       UPDATE people SET phone = '801-123-4567'

    WARNING! This will change the phone field for every record!

    Now try it using a WHERE clause. (Format: WHERE fieldname = 'value from an existing record';)

       UPDATE people SET phone = '801-123-4567' WHERE lname = 'Chilton'

13. You can delete a record using the DELETE command
    

       DELETE FROM tablename WHERE fieldname = 'an existing value';
    

    WARNING! If you don't limit the DELETE with a WHERE clause, you will delete every record in the table!

14. To exit type:

      sqlite>   .exit
    

      mysql>   exit;
    

    
    

Exercise 2: Working with Databases in a Graphical User Interface

While a MySQL database can be managed from a comand line in this way, it quickly becomes tedious and cumbersome as the database grows larger. Fortunately there are a number of graphical interfaces available for working with both MySQL and SQLite.

Try the following in your MySQL or SQLite GUI:

1. Add records to the people database.
2. Cross reference records in people to records in department.

Exercise 3:

Figure out SQL SELECT statements that do things like the following. The SELECT tutorial we did earlier will give you ideas on how to create these.

• Create a query that will show all of the people in a certain department.

• Create a query that limits the results to only people whose names start with a certain letter.

• Create a query that gives you the number of people in a certain department. Use the SQL count() function.

References and Tutorials

There are many sources on the web for learning about SQL in general and MySQL in particular. Here is a list of some good sites.

1. A very good, interactive SQL tutorial can be found at http://sqlzoo.net/.

2. CHum 240 pages

   There used to be an entire CHum (precursor of the DigHT program) course devoted exclusively to the art and science of databases. There are a number of pages on the site for CHum 240 that may be useful.

   CHum 240 schedule

   The CREATE TABLE statement (including a summary of the most common data types)
   

   The INSERT statement
   

   The SELECT statement
   

   The UPDATE statement
   

   The DELETE statement
   

3. The web site w3schools.com also has a very good SQL tutorial:

   http://www.w3schools.com/sql/default.asp.

4. ExtendsClass has a useful web-based utility called SQLite Browser Online:

   This utility lets you import, edit, and export SQLite databases in a web browser. There are also some good SQL documentation links on the page.

5. MySQL Online Documentation

   http://dev.mysql.com/doc/refman/5.0/en/index.html

Assignments:

Return to the Databases Lessons Outline and complete the two assignments under Part 2—The SQL SELECT Tutorial and Assignment Part 2 - Creating your own database.