Humanities Home page   Office of Digital Humanities
Back     BYU LiveCode Lessons Gateway

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:

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

MySQL DatabaseSQLite Database

(Note: In order to do this command line exercise the MySQL server administrator must grant the dight310 user elevated permission levels on the database. These elevated permissions were only available during the class period, since leaving them in place would expose other databases to inadvertent damage. See the instructor if you want to try this outside of class.)

In this exercise we will use a Unix command line program—called mysql—to create and populate a database table.

  1. On Mac OS X, launch /Applications/Utilities/Terminal.app
    On Windows, in the Search box next to the Start menu type cmd and press Enter.
    Then log in to server via SSH after the > prompt:

    > ssh username@host.computer.edu
     # (use the username and host computer
     #  address given you in class)
    

    Enter the proper password when prompted.
    (Type 'yes' if asked a yes/no question here.)

  1. On a system that has SQLite installed, launch a command line program.
    On Mac OS X, launch /Applications/Utilities/Terminal.app
    On Windows, in the Search box next to the Start menu type cmd and press Enter.
  1. Launch mysql

     > mysql -u username -p
       # (use the mysql username and password
       #  given you in class)
    

    If you have successfully launched mysql you'll see a prompt like this at the beginning of the line:

     mysql>
  1. Launch the SQLite program by entering the name of the command line program sqlite3 after the > prompt:

    Mac:
    > sqlite3 /path/to/file/dbname.sqlite
    Windows:
    > sqlite3 /path/to/file/dbname.sqlite

    If you have successfully launched sqlite you'll see a prompt like this at the beginning of the line:

      sqlite>
  1. Create a new database using the CREATE DATABASE command, giving your database a unique name:

     CREATE DATABASE mytestdb;

    Note: In the mysql command line interface, all commands should be terminated with a semicolon (;)

  1. (When you specify the file path to the SQLite database file, the database is opened if it exists; if it does not yet exist, the empty database file is automatically created.)

  1. Tell mysql you want to use the new database you just created:

      USE mytestdb;
    
  1. (Sqlite3 is a single-database-per-session application, so there is no need to tell it which database to use.)

  1. 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));
    
  2. You can verify that the table was created by using:

      sqlite>   .tables
    
      mysql>   SHOW TABLES;
    

  3. 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');
    
  4. Use a SELECT statement to make sure it worked.

       SELECT * FROM people;
    

    You should see all of the records you just added.

  5. 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;
    

  6. 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');
    
  7. Use a SELECT statement to make sure it worked.

       SELECT * FROM people;
    
  8. 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'
  9. 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!

  10. 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.

There are a number of GUI's available for working with SQLite databases. For example there is a good, open source GUI called DB Browser for SQLite that can be downloaded from http://sqlitebrowser.org. There is even a low-cost commercial SQLite GUI, called SQLiteAdmin, which was created in LiveCode.

For working with MySQL, one of the most widely used GUI's is phpMyAdmin, a web-based graphical interface to MySQL databases that allows us to visualize the data in a much more user-friendly format. Our MySQL server has phpMyAdmin installed, and it can be reached at http://humacademic.byu.edu/phpmyadmin. You can log in to this interface using the login credentials that I gave you in class.

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.

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.


Back     BYU LiveCode Lessons Gateway
Maintained by Devin Asay.
Copyright © 2005 Brigham Young University.
This page last updated on February 25, 2019 11:32:52.