Structured Query Language (SQL) Fundamentals

D. Curtis Jamison1

1 George Mason University, Manassas, Virginia
Publication Name:  Current Protocols in Bioinformatics
Unit Number:  Unit 9.2
DOI:  10.1002/0471250953.bi0902s00
Online Posting Date:  February, 2003
GO TO THE FULL TEXT: PDF or HTML at Wiley Online Library

Abstract

Relational databases provide the most common platform for storing data. The Structured Query Language (SQL) is a powerful tool for interacting with relational database systems. SQL enables the user to concoct complex and powerful queries in a straightforward manner, allowing sophisticated data analysis using simple syntax and structure. This unit demonstrates how to use the MySQL package to build and interact with a relational database.

     
 
GO TO THE FULL PROTOCOL:
PDF or HTML at Wiley Online Library

Table of Contents

  • Basic Protocol 1: Creating a Database
  • Support Protocol 1: Changing a Schema
  • Support Protocol 2: Adding Users and Permissions
  • Basic Protocol 2: Adding Data to a Table
  • Alternate Protocol 1: A Batch Method to Load Data into a Table
  • Basic Protocol 3: Removing Data from a Table
  • Basic Protocol 4: Changing Data in a Table
  • Basic Protocol 5: Retrieving Data
  • Alternate Protocol 2: Batch Processing Command Scripts
  • Commentary
  • Figures
  • Tables
     
 
GO TO THE FULL PROTOCOL:
PDF or HTML at Wiley Online Library

Materials

Basic Protocol 1: Creating a Database

  Necessary Resources
  • Hardware
  • A computer capable of running MySQL, such as one with a Windows, OS/2, or Unix‐based operating system
  • Software
  • A working installation of MySQL, version 3.22.11 or higher. One must also have DBA permissions (see protocol 3). MySQL is available for free under the GNU Public License. It may be downloaded from http://www.mysql.com.

Support Protocol 1: Changing a Schema

  Necessary Resources
  • Hardware
  • A computer capable of running MySQL, such as one with a Windows, OS/2, or Unix‐based operating system
  • Software
    • A working installation of MySQL, version 3.22.11 or higher. One must also have DBA permissions. MySQL is available for free under the GNU Public License. It may be downloaded from http://www.mysql.com.
  • Files
    • The primers database created in protocol 1. Schema shown in Figure

Support Protocol 2: Adding Users and Permissions

  Necessary Resources
  • Hardware
  • Computer capable of running MySQL
  • Software
  • Newly installed copy of MySQL, version 3.22.11 or higher
  • Files
  • The primers database created in protocol 1

Basic Protocol 2: Adding Data to a Table

  Necessary Resources
  • Hardware
  • A computer capable of running MySQL, such as one with a Windows, OS/2, or Unix‐based operating system.
  • Software
  • A working installation of MySQL, version 3.22.11 or higher. One must also have DBA permissions. MySQL is available for free under the GNU Public License. It may be downloaded from http://www.mysql.com.
  • Files
  • The primers database created in protocol 1, as altered in protocol 2

Alternate Protocol 1: A Batch Method to Load Data into a Table

  Necessary Resources
  • Hardware
  • A computer capable of running MySQL, such as one with a Windows, OS/2, or Unix‐based operating system
  • Software
  • A working installation of MySQL, version 3.22.11 or higher. One must also have DBA permissions. MySQL is available for free under the GNU Public License. It may be downloaded from http://www.mysql.com.
  • Files
  • The primers database created in protocol 1, as altered in protocol 2
  • Tab‐delimited trial data file, oligo.txt. This file is available at the Current Protocols Website: http://www3.interscience.wiley.com/c_p/cpbi_sampledatafiles.htm.

Basic Protocol 3: Removing Data from a Table

  Necessary Resources
  • Hardware
  • A computer capable of running MySQL, such as one with a Windows, OS/2, or Unix‐based operating system
  • Software
  • A working installation of MySQL, version 3.22.11 or higher. One must also have DBA permissions. MySQL is available for free under the GNU Public License. It may be downloaded from http://www.mysql.com.
  • Files
  • Primer database created in protocol 1, as altered in protocol 2, loaded with the data from oligo.txt as shown in protocol 5

Basic Protocol 4: Changing Data in a Table

  Necessary Resources
  • Hardware
  • A computer capable of running MySQL, such as one with a Windows, OS/2, or Unix‐based operating system.
  • Software
  • A working installation of MySQL, version 3.22.11 or higher. One must also have DBA permissions. MySQL is available for free under the GNU Public License. It may be downloaded from http://www.mysql.com
  • Files
  • The primers database created in protocol 1, as altered in protocol 2.

Basic Protocol 5: Retrieving Data

  Necessary Resources
  • Hardware
  • A computer capable of running MySQL, such as one with a Windows, OS/2, or Unix‐based operating system
  • Software
  • A working installation of MySQL, version 3.22.11 or higher. One must also have DBA permissions. MySQL is available for free under the GNU Public License. It may be downloaded from http://www.mysql.com.
  • Files
  • The primers database created in protocol 1, as altered in protocol 2
  • Four data files containing the larger search set: cpboligo.txt, cpbprotocol.txt, cpbbuffer.txt, and cpbsequence.txt. These files are available from the Current Protocols Web site: http://www3.interscience.wiley.com/c_p/cpbi_sampledatafiles.htm.

Alternate Protocol 2: Batch Processing Command Scripts

  Necessary Resources
  • Hardware
  • Computer capable of running MySQL
  • Software
  • Working installation of MySQL version 3.22.11 or higher.
  • Files
  • The primers database created in protocol 1, as altered in protocol 2, and loaded with the larger search data set as described in protocol 8, step
  • Script.txt file, which contains a set of four SQL commands. This file is available from the Current Protocols Web site: http://www3.interscience.wiley.com/c_p/cpbi_sampledatafiles.htm.
GO TO THE FULL PROTOCOL:
PDF or HTML at Wiley Online Library

Figures

Videos

Literature Cited

Key References
   DuBois, P. 1999. MySQL. New Riders. Indianapolis, Ind.
  A comprehensive guide, with many examples and tutorials. An excellent reference for beginners.
   Gulutzan, P. and Pelzer, T. 1999. SQL‐99 Complete, Really. CMP Books. Gilroy, Calif.
  A complete description of the SQL99 standards.
   Kline, K. and Kline, D. 20001. SQL in a Nutshell. O'Reilly and Associates. Sebastopol, Calif.
  A compact yet comprehensive guide to SQL statements. Includes several different SQL dialects like MySQL and Oracle.
Internet Resources
   http://www.mysql.com
  The main MySQL site.
   http://www.useractive.com
  A hands‐on tutorial.
GO TO THE FULL PROTOCOL:
PDF or HTML at Wiley Online Library

Supplementary Material

Note: To access the files listed below, download the associated 

Zip file at the bottom of the page (right click/save as).

 

1. First data file containing the larger search set: cpbbuffer.txt
2. Second data file containing the larger search set: cpboligo.txt
3. Third data file containing the larger search set: cpbprotocol.txt
4. Fourth data file containing the larger search set: cpbsequence.txt
5. Tab-delimited trial data file: oligo.txt
6. Data file containing a set of four SQL commands: script.txt

 

CPBI9.2.zip