Structured_Query_Language.doc

(1)

Structured Query Language

Tutorial Series Volume 1

College of Digital Economy and Information Technology

2008/11/17

(2)

Introduction

SQL stands for Structured Query Language. SQL serves as the default database programming language commonly used by most database technology vendors including Microsoft, Oracle, MySQL, and many others. Although each of these vendors adds proprietary commands, each vendor also respects the core of the SQL standards1_{as defined by the American National Standards Institute}2

(ANSI).

SQL is divided into two primary parts: DML and DDL. DML stands for Data Manipulation Language and provides users with the ability to query and manipulate data in a database. DDL stands for Data Definition Language and provides users with the ability to create database structures. In this tutorial, we will explore both the DML and DDL capabilities of SQL.

Getting Started

In order to use this tutorial, you will need the following materials:

1. the movies database;

2. a computer with database software that provides a command line interface for running SQL statements;

3. patience.

In this tutorial, you will learn how to:

1. query a database with SQL syntax;

2. insert data into a table;

3. update data in a table;

4. delete data from a table;

5. join data from two tables;

6. create tables;

7. delete tables.

1_{Consult http://en.wikipedia.org/wiki/SQL for a review of the relevant SQL standards.}

2_{http://www.ansi.org/}

(4)

Data Manipulation Language

Querying a Database

The SELECT command retrieves data from a specific database table.

The general format for the SELECT command is as follows:

SELECT <field1, field2, field3,...> FROM <table>;

where field1, field2, field3 are fields in table.

For example, the usage of the command below returns all of the fields and rows in the table:

SELECT * FROM MOVIE;

If a user, on the other hand, wishes to retrieve only a single field, the user needs only to specify the field in the SQL clause:

SELECT TITLE FROM MOVIE;

Other examples include:

SELECT MOVIEID, TITLE FROM MOVIE;

SELECT TITLE, MOVIEID FROM MOVIE;

Notice that SQL will respect the order of the fields as typed in the command. Compare the order of the fields displayed from each command above.

In some cases, a user may wish to view only the unique values contained in a field. The keyword

DISTINCT provides this functionality.

For example, compare the functionality of the following two queries:

SELECT RELEASEYEAR FROM MOVIE;

SELECT DISTINCT RELEASEYEAR FROM MOVIE;

The second query should contain fewer records because the DISTINCT keyword only retrieves unique values from the table.

In addition to retrieving the data, SQL provides users with a mechanism for sorting the data. For example, if a user wants to retrieve all of the records in the MOVIE table and sort the records by

RELEASEYEAR, the SQL command would be:

SELECT * FROM MOVIE ORDER BY RELEASEYEAR ASC;

(5)

SELECT * FROM MOVIE ORDER BY RELEASEYEAR DESC;

Finally, in cases where users wish to retrieve specific rows of data, SQL commands can use the

WHERE clause to specify criteria for the selection of records. For example, if a user wants to retrieve movies from the table that were release in 1998, the user should issue the following command:

SELECT * FROM MOVIE WHERE RELEASEYEAR=1998;

Moreover, a user may wish to include a number of criteria to narrow the selection even further.

SELECT * FROM MOVIE WHERE RELEASEYEAR=1998 AND TITLE='Saving Private Ryan';

In addition to these basic commands, SQL also allows users to aggregate information. The simplest form of aggregation is the COUNT keyword.

SELECT COUNT(*) FROM MOVIE;

The command above will return the total number of records in the table MOVIE.

If we wish to improve the power of aggregate commands, we can use them with the GROUP BY clause as in the following command:

SELECT RELEASEYEAR, COUNT(RELEASEYEAR) FROM MOVIE GROUP BY RELEASEYEAR;

The command above should display each year contained in the RELEASEYEAR column with the number of movies released in that year in a column beside it.

Exercises

1. Examine the following query:

SELECT * FROM MOVIE WHERE RELEASEYEAR=1998 AND TITLE='Saving Private Ryan';

Why does 'Saving Private Ryan' have single quotes around it? Why does 1998 not have quotes around it?

2. How many movies were released in 1997?

3. Identify the primary keys and the foreign keys in the MOVIE table.

(6)

Inserting Data

Inserting data is a relatively straight-forward process with SQL. The simplest way to insert a row is as follows:

INSERT INTO DIRECTOR VALUES(12, 'FirstName', 'LastName', 1972);

The command will insert the values into the table because the values are presented in the correct order.

The full version of this command is as follows:

INSERT INTO DIRECTOR (DirectorID, LastName, FirstName, BirthYear) VALUES (13, 'LastName', 'FirstName', 1972);

The full version of the command will let you add data to specific columns if the database model allows it. For example:

INSERT INTO DIRECTOR (DirectorID, LastName, FirstName) VALUES (14, 'LastName', 'FirstName');

the command above will only insert data into the specified columns. No data will be entered into the BirthYear field.

Exercises

1. Try to insert a record with a DirectorID that has already been assigned in the system. What happens and why?

2. Try to insert a record by changing the order of the fields and the order of the values clause. Does the record insert properly? Would you want to insert records with the fields out of order?

(7)

Updating Data

Updating data in an SQL table is also rather straight-forward. Before actually jumping in, keep in mind that the UPDATE command in SQL is greedy meaning that the SQL processor will update any record that meets its criteria set. Therefore, be extremely careful with UPDATE because if it is used improperly you will update ever record in a table!

The most common operation for update involves updating a single row. For example, if a user wanted to update the Director with DirectoID 14, the user would type:

UPDATE DIRECTOR SET FirstName='Joe' WHERE DIRECTORID=14

The command will update the FirstName value to Joe where the DirectorID is equal to 14.

If a user wishes to update more than one field, the command syntax is as follows:

UPDATE DIRECTOR SET FirstName='Joe', LastName='Smith' WHERE DIRECTORID=14

If a user wishes to update more than one record, the user needs only to modify the WHERE clause.

UPDATE DIRECTOR SET FirstName='Joe', LastName='Smith' WHERE DIRECTORID IN (13,14);

Exercises

1. What will the following command do?

UPDATE DIRECTOR SET FirstName='Joe', LastName='Smith';

Why should we be very careful with this command?

2. What happens if we try to update a primary key? For example, what happens if we execute the following command?

UPDATE DIRECTOR SET DIRECTORID=100 WHERE DIRECTORID=14

(8)

Deleting Data

The same warnings and cautions that apply to the UPDATE statement also apply to the DELETE

statement. If the DELETE command is not used properly, you will have nothing left in your database!

DELETE * FROM DIRECTOR WHERE DIRECTORID=14;

The command above will remove the record with DIRECTORID = 14.

The mechanics of the DELETE statement are similar to those of the UPDATE statement.

Use these commands with care!

Advanced Queries

In order to execute complex queries, you will need to understand the underlying database model. In particular, you must understand the relationships between tables in the database. Before beginning this section, review the Movies database to identify how the tables are related.

For example, let's say that we want to view a list of movies next to the directors who created them. We could execute a query like this:

SELECT DIRECTOR.FirstName, DIRECTOR.LastName, MOVIE.Title FROM MOVIE

INNER JOIN DIRECTOR ON MOVIE.DIRECTORID = DIRECTOR.DIRECTORID;

We will walk through the syntax point by point.

We specify the fields we wish to select as ever except that we prepend the name of each table to the field name. In this example, we select three fields: FirstName and LastName from the DIRECTOR

table and Title from the MOVIE table. After specifying the fields, we reference our primary table in this case MOVIE. After the reference to the table, the syntax changes drastically because we specify a join type. In this example, we use an INNER JOIN which means that we will include all rows that exist in both joined tables. The DIRECTOR reference tells the SQL processor that we mean to join DIRECTOR to MOVIE. The ON clause tells the SQL Processor how we mean to join the two tables. In this example, we want to include all records where the DIRECTORID fields in the MOVIE

and DIRECTOR tables match.

As with other queries, we can include additional keywords to control the format and data included in the result set.

SELECT DIRECTOR.FirstName, DIRECTOR.LastName, MOVIE.Title FROM MOVIE

(9)

The query above will sort the data by the Director's Last Name.

SELECT DIRECTOR.FirstName, DIRECTOR.LastName,

COUNT(MOVIE.Title) AS 'Movie Count' FROM MOVIE

INNER JOIN DIRECTOR ON MOVIE.DIRECTORID = DIRECTOR.DIRECTORID GROUP BY DIRECTOR.FirstName, DIRECTOR.LastName

ORDER BY DIRECTOR.LastName ASC;

In the example above, the query will return a count of the number of movies each director made. Notice that we have now specified an ALIAS for the generated COUNT field: Movie Count. We have also added a GROUP BY as well as an ORDER BY clause.

INNER JOIN DIRECTOR ON MOVIE.DIRECTORID = DIRECTOR.DIRECTORID WHERE DIRECTOR.LastName = 'Kurosawa'

GROUP BY DIRECTOR.FirstName, DIRECTOR.LastName ORDER BY DIRECTOR.LastName ASC;

In this example, the query will only return a row for the Director Kurosawa because the WHERE clause limits the selection to that one director.

INNER JOIN DIRECTOR ON MOVIE.DIRECTORID = DIRECTOR.DIRECTORID WHERE DIRECTOR.LastName IN ('Kurosawa','Hitchcock')

GROUP BY DIRECTOR.FirstName, DIRECTOR.LastName ORDER BY DIRECTOR.LastName ASC;

Finally, in the example above, we use the IN keyword to specify a list of Directors to include in the query results.

(10)

Exercises

1. What happens if a record exists in the DIRECTOR table, but no corresponding record exists in the MOVIE table? When you join the tables with INNER JOIN, will the record from the DIRECTOR table appear or not? How would you explain this?

2. There are several other aggregate functions in SQL including: MAX, MIN, and AVG. Experiment with these functions.

3. In addition to equals, you can evaluate inequality, greater than, less than, etc. Experiment with these operators in the WHERE clause.

(11)

Data Definition Language

Creating Tables

In order to create a new table, we use the following syntax:

CREATE TABLE TEST (FirstName TEXT(20), LastName TEXT(20));

If you execute the command above, you will create a table called TEST with two text fields: FirstName and Last Name. The size limit of both fields will be 20 characters in length.

Exercises

1. Create the tables necessary to track your favorite CDs. Do not cheat; use only SQL. In particular, create tables with different data types.

Deleting Tables

As with any destructive command, be very careful. A mistake with this command will certaily

give you a very bad day.

In order to delete an entire table, as opposed to all of the data in a table, simply type the

command:

DROP TABLE TEST;

(12)

Further Study

For those who wish to pursue additional topics in this area, I recommend the following steps:

1. Familiarize yourself with all of the commands and their usage. Consult the Bibliography below and work through the tutorials. When you feel that you are comfortable with all of the DML and DDL commands, create a database to store information important to you such as CDs or contacts.

2. After you have worked with your own database for a while, you will want to increase your knowledge of databases by exploring additional database technologies. Download and install MySQL. MySQL is free and is a fairly robust database. Practice the commands with the MySQL interface. Build a database to track data in MySQL.

3. After you become comfortable with MySQL, learn to use some of the advanced features of the database including:

INDEXES, VIEWS, SCHEMAS, TRIGGERS, etc.

4. After you have built a database with the advanced features from step 3, you are ready to capture logic in a stored procedure. Start programming stored procedures in MySQL. Consult the

Bibliography for more information and tutorials.

(13)

Bibliography

Online Materials

Tutorials

SQL Tutorial. W3C Schools Tutorials. November 17, 2008.

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

SQL Tutorial – Learn SQL. Art Branch Inc. November 17, 2008.

http://www.sql-tutorial.net/

SQL Tutorial. 1keydata.com. November 17, 2008.

http://www.1keydata.com/sql/sql.html

Vendor References

MSDN SQL Developer Center. Microsoft Corporation. November 17, 2008.

http://msdn.microsoft.com/en-us/library/ms130214.aspx

Getting Started: Oracle SQL Developer 1.2. Oracle Technology Network. November 17, 2008.

http://www.oracle.com/technology/getting-started/sqldev.html

MySQL 5.0 Reference Manual: Tutorial. MySQL AB, 2008 Sun Microsystems, Inc. November 17, 2008.

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

Standards

SQL. Wikipedia. November 17, 2008.

http://en.wikipedia.org/wiki/SQL

Structured_Query_Language.doc