[ Team LiB ] Previous Section Next Section

What Is SQL?

Structured Query Language, or SQL, is the successor of an IBM product called SEQUEL, for Structured English Query Language. SQL is an ANSI (American National Standards Institute) standard computer language that allows developers to access and manipulate a database system. It has been widely adopted as the language of choice to work with all the leading databases such as MS Access, DB2, Informix, MS SQL Server, Oracle, Sybase, and Cloudscape. SQL includes commands to create and manage databases. SQL as a language can be broken into several smaller components as explained in the next sections.

SQL Data Types

SQL supports a variety of different data types that map to the different types of data that can be stored in the database. These include data types such as binary, bit, char, datetime, float, integer, varchar, and several others.

Various Java data types, such as String, Long, Float, byte, short, and so on, are mapped to the SQL data types so that you can move data from SQL queries into Java objects. Every database has a slightly different mapping of Java data structures to SQL data types.

SQL Data Definition Language

The Data Definition Language, or DDL, is used to create and modify a database. DDL includes commands such as CREATE, ALTER, and DROP. The CREATE command is used to create a database and the definition of the tables and columns for each table. The ALTER command is used to alter the tables within a database. The DROP command is used to delete the database and all its contents.

Some examples of the various DDL commands are

  • CREATE DATABASE databaseName

    This creates a database with the given name.

  • CREATE TABLE tableName ( columnName dataType,...)

    This creates a table in the database with the given definition of columns and the data type for each column.

  • ALTER TABLE tableName ADD columnName dataType

    This modifies a table definition to add a column.

  • ALTER TABLE tableName DROP columnName

    This modifies a table definition to delete a column.

  • DROP DATABASE databaseName

    This deletes the database.

  • DROP TABLE tableName

    This deletes a particular table from the database.

SQL Data Manipulation Language

The Data Manipulation Language, or DML, is used to manage the data stored in the database. This includes commands such as INSERT, UPDATE, and DELETE.

The INSERT statement is used to insert data into a table, one record at a time. The UPDATE command is used to modify the contents of the columns within a single row of a specific database table. The DELETE command is used to delete specific rows from a table in the database.

Some examples of DML statements are

  • INSERT INTO tableName (column1, column2,...) VALUES (value1, value2,....)

    This inserts values into a particular table.

  • UPDATE tableName SET column1 = new WHERE column1 = old

    This modifies the value of a column within a table given a specific condition.

  • DELETE FROM tableName WHERE column1 = value

    This deletes a row from a table, given a specific condition

SQL Data Query Language

After you have created the database and added some data to it, you will definitely want the ability to retrieve that data. This is what the Data Query Language allows you to do. It includes the basic SELECT command that allows you to select rows from a table in the database. The Data Query Language also includes functionality such as the WHERE clause that allows you to specify search criteria to search the data in the database.

Some examples of the Data Query Language are

  • SELECT * FROM tableName

    This selects all rows from a table

  • SELECT column1... FROM tableName WHERE column2 = value

    This selects specific columns from a table given a specific condition.

  • SELECT column1... FROM tableName WHERE column1 = value AND column2 = otherValue

    This is similar to the previous statement, only with a more complex conditional statement.

Learn More About SQL


To learn more about SQL and the syntax and usage of these commands, you can go to http://www.w3schools.com/sql/default.asp. You can also read about it in a book such as Sams Teach Yourself SQL in 24 Hours, 3rd edition, by Stephens and Plew (ISBN: 0672324423).

    [ Team LiB ] Previous Section Next Section