SQL - Structured Query Language

Resources:

Books:

• Sams Teach Yourself SQL in 10 Minutes, Ben Forta, SAMS Publishing
• The Practical SQL Handbook, Judith S. Bowman etal, Pearson Educational
• Sams Teach Yourself SQL  in 21 Days,by Ryan Stephens, SAMS Publishing

Online Tutorials:

• SQLCourse.com - TutorialPlease try this one!!!
• FirstSQL.com - Tutorial
• BayConGroup.com - Tutorial
• A Gentle Introduction to SQL

Online References:

• MySQL.Org
• Reference Manual
• The FreeCountry.com tutorials, readings, reference documents
• Microsoft SQL Reference

Defined:

SQL (pronounced "ess-que-el") stands for Structured Query Language. SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database.

Why SQL ?

• It is a language common to all databases (although every database vendor has their own slight variations on SQL commands), learn SQL once then apply it to accessing data from any database in any programming language and/or via the WEB.
• One correctly structured SQL statement can literally save you hundreds of lines of JAVA, VB, or C++ code.
• SQL is powerful, with one line of SQL code you can search records from multiple tables, extract relevant data, sort it, and perform calculations.  The results are then given to your program in tabular form.
• SQL is a structured textual command that can be submitted to a database management system.

How is SQL used in JAVA?

• Java has some standard objects (defined in java.sql library) that enable you to submit SQL commands and process the results.
• The first step is for your program to create a connection to a database via the Connection object.
• Your program then will build the sql commands based on information entered by the user then ask the SQL Statement (or PreparedStatement) object to execute it.
• Answers are returned in a ResultSet object.
• Before you can do any of this you need to understand the format of SQL commands!

Using the SQL Demo Program

• SQLDemo is a Java application I wrote to allow you to practice SQL commands before using them in JAVA programs.  This program will also help you get used to the sequence of steps necessary to access databases in JAVA programs as the buttons on the program reflect Java SQL operations. It should work on any database so long as you have created a data source name for the database.
• To download, right click here and save target as SQLDemo2.zip on your desktop or in a folder.
• Extract the folder SQLDemo2, inside will be SQLDemo2.jar and a lib folder, just double click on the jar to run. (Note, the jar must be in the same location as the lib folder)
• Sample MS Access database used in these examples: SampleBusiness.MDB
  
• Click here for information on setting up a Data Source Name for the sample database
  
• First  press the connect button, select a database type and fill in the parameters.  If SQLDemo was able to connect to the database it will display information about the tables in your database in the Database Schema window. Messages/Errors will appear in the Message Log window.

• To Issue a SQL command, type the command in the SQL window. For SELECT statements press the Execute Query button, for other statements press Execute Update Button.

• Query results are displayed in the results table.  The status of all opertions is displayed in the message log.
• To connect to a different datasouce, press Close Connection, then enter the new data source name and press Connect.

Keys to success:

• Be aware of how big your result set will be. A database may have millions of records, always use a where clause and only specify the columns you need. Avoid using Select * unless you really need all the columns!
• Make SQL do all the work.  Your Java program should only have to ask the user for information, format the sql command, and display the results.

Basic SQL Commands

• SELECT - used to extract information from one or more tables in a database
• INSERT - used to add new records to a table
• DELETE - used to delete one or more records from a database table
• UPDATE - used to change one or more fields of one or more records in a table

• CREATE - used to create tables
• DROP - used to delete entire tables
• ALTER - used to modify table structures (add/remove columns)

Notation in this document:

• anything between [] is an option item
• anything in all capitals is a SQL keyword, this is not a rule, it just makes it easier to read the statements.
• a semi-colon at the end of a single SQL statement is not necessary for most database system. Some do require it and many examples you will see have a semicolon at the end.
• when creating your database, never give a name to a column that is an SQL keyword(e.g. Date).  This confuses SQL and your statments will not work. A list of SQL keywords is listed at the end of this web page. Also, don't put spaces in your column names!
  

Select Command - Single Table

• Format:

SELECT [distinct] column-list FROM table [WHERE logical-expression] [ORDER BY column-list ASC/DESC]

• the column-list specifies which columns to retrieve from the tables. Each column name is separated by a comman. You may use * to indicate that you want all the columns. Table specifies the table to use to extract data. The order of the columns in your result set will be set by the order of the columns in the column list.  The order of the records in the result set will be determined by the ORDER BY clause. Your result may give a different name to a column by using the AS operatior.

• Single table examples, the first one selects all columns and all rows of the Products table, the second one selects only the Name and Cost columns from all rows of the Products table:

SELECT * FROM Products
SELECT Name, Cost FROM Products
SELECT Name AS Product_Name FROM Products

• the where logical expression indicates the criteria for selecting the records.  SELECT statements that use a single table, the where clause is used to determine which records (rows) to retrieve.  SELECT statments that use more than one table, the where clause can be used to specify how to join (match) records in the tables. Omitting the WHERE clause specifies that all rows are used.

• WHERE logical-expression formats:

column-name comparison-operator value-1

column-name [NOT] LIKE pattern-string

column-name [NOT] IN (value-1 [, value-2] ... )

column-name [NOT] BETWEEN value-1 AND value-2

column-name IS [NOT] NULL

    

• Comparison operators:

=  equal to
>  greater than
<  less than
>=  greater than or equal to
<=  less than or equal to
<>  not equal to

• Examples, the first selects all products whose cost is greater than 10, the second selects all products whose quantity is less than or equal to 100, and the third selects all products whose name is Nails. Note, string literals are enclosed in single quotes.

SELECT * FROM Products WHERE Cost > 10.0
SELECT * FROM Products WHERE Quantity <= 100
SELECT * FROM Products WHERE Name = 'Nails'

• LIKE Pattern-string - matches a string value against a pattern string containing wild-card characters. The wild-card characters for LIKE are percent '%', underscore  '_', and [] . Underscore matches any single character. Percent matches zero or more characters. [] match one of a set of characers, e.g. [ab] will match either an 'a' or a 'b'.Pattern strings are enclosed in single quotes. Examples, the first example selects all records of the Products table whose names begin with an S, the second example selects all records of the Products table whose Name can start with any two characters that are followed by the letter TR, the third example selects all records whose name does not start with an S, the last example will match any name starting with an A, B, or C:

SELECT * FROM Products WHERE Name LIKE 'S%'
SELECT * FROM Products WHERE Name LIKE '__TR%'
SELECT * FROM Products WHERE Name NOT LIKE 'S%'
SELECT * From Products WHERE Name LIKE '[ABC]%'

•  IN - matches any value in the values list, values list is enclosed in ( ). Examples, the first one selects all products whose Name is Nails, Hammers, or Saws. The second one select products whose costs are anything but 33, 44 or 55.

SELECT * FROM Products WHERE Name IN ('Nails', 'Hammers', 'Saws')
SELECT * FROM Products WHERE Cost NOT IN (33,44,55)

• BETWEEN - operator implements a range comparison, that is, it tests whether a value is between two other values. This comparison tests if the value of the column is greater than or equal to value-1 and less than or equal to value-2. Examples, the first example selects all products whose cost is between 10 and 25 inclusive, the second example selects all products whose quantity is not between 50 and 100 (inclusive).

SELECT * FROM Products WHERE Cost BETWEEN 10.0 AND 25.0
SELECT * FROM Products WHERE Quantity NOT BETWEEN 50 AND 100

    

• IS NULL - tests to see if the value of a column is NULL , that is, it has never been entered into the table.  Example, get a list of all products whose cost values have never been initialized, second example gets a list of all products whose quantity has been initialized. Note, a value of 0 is not the same as NULL, and you cannot use NULL with the = comparison operator:

SELECT * FROM Products WHERE Cost IS NULL
SELECT * FROM Products WHERE Quantity IS NOT NULL

• WHERE clauses can have multiple logical expression linked together with AND and OR operators. Examples

SELECT * FROM Products WHERE Cost > 10 AND Quantity < 100
SELECT * FROM Products WHERE Name LIKE 'S%' OR Name LIKE 'T%'

• ORDER BY  clause. The ORDER BY clause is optional and must appear last in your SQL SELECT statement. Following the ORDER BY keyword is a list of column names separated by commas. The keywords DESC (for descending) or ASC (ascending) may be used after each column name to denote the direction of the sort. ASC is the default. The first column name in the list is the first one applied to sorting, if there is a tie on that value, then the second column name is used. Examples, the first two are equivalent, they select all records from the products table and sort them in ascending order using Cost, the third example selects all records from the Products table and sorts them by cost in descending order (highest first), the last example example selects all records from the Products table and sorts them by name, if two products have the same name, they are listed in order of highest cost first:

SELECT * FROM Products ORDER BY Cost
SELECT * FROM Products ORDER BY Cost ASC
SELECT * FROM Products ORDER BY Cost DESC
SELECT * FROM Products ORDER BY Name ASC, Cost DESC

• Adding calculated fields to the result - you can create extra columns in your result set based on the query columns by adding an equation of the form :  column-name1 operator column-name2 AS calculated- name to the column-list.  Operators are +, - , *, and /. Example, create a result set reflecting the cost of inventory in the Products table, this result set has four columns, Name, Quantity, Cost, and Total_Value:

SELECT Name, Quantity, Cost, Quantity*Cost AS Total_Value FROM Products

Select Command - Multiple Tables Join

• When you are referencing multiple tables in the same SQL command, its best to refer to columns using the "dot" notation,  tableName.columnName, e.g. Products.Cost. This removes any ambiguity in case the two tables have the columns with the same name.  Many programmers always use the dot notation for single table opertaions in order  to make the code easier to read.
  
• Using Joins in a Select command allows you to create a result set based on data from two or more tables.  The Join operation combine records from the two tables when the records have fields with matching values.  There are a couple of ways to do this:


• Use the SQL WHERE clause is used to specify whcih columns should match one another, format:

SELECT [distinct] column-list FROM table-list WHERE table1.column1 = table2.column2 GROUP BY column-list

• Or better yet, use the Inner Join function,   instead of specifying the columns for the join in the WHERE clause, you can use the inner join operation, e.g.
  

SELECT [distinct] column-list From table1 INNER JOIN table2 ON table1.column = table2.column GROUP BY column-list 

(there other types of joins,  your database class will cover these)

• Like before, the column-list will dictate what columns are to appear in the result set. The table-list specifies the tables to be used in the join, table names are separated by commas. The WHERE clause is used to tell which columns from each table to use to match records in the join. Often times two tables will have columns with the same name.   Examples (reference the table structure below):

• Return a list of all customers (first and last name) who have purchased a drill

SELECT Customers.LastName, Customers.FirstName FROM Customers,Orders WHERE Orders.ProductName = 'Drill' AND
  Orders.CustomerPhone = Customers.Phone

SELECT Customers.LastName, Customers.FirstName FROM Customers INNER JOIN Orders ON Orders.CustomerPhone = Customers.Phone 
WHERE Orders.ProductName = 'Drill'

• Return a list of all customers who have made purchases worth more than 100.

SELECT Customers.LastName, Customers.FirstName FROM Customers, Orders WHERE Orders.CustomerPhone = Customers.Phone 
AND Orders.Quantity*Orders.PriceEach > 100

SELECT Customers.LastName, Customers.FirstName FROM Customers INNER JOIN Orders ON Orders.CustomerPhone = Customers.Phone 
WHERE Orders.Quantity*Orders.PriceEach > 100

• Some queries could return copies of the same data in the result set.  In the previous example, if customer Frank Smith had made 3 different purchases of drills, his name would be in the result set 3 times.  To get around that, you use the Group By  or the Distinct clause. Example, modify the previous query to just return the list of names of people who have purchased a drill,  without any duplicates.

SELECT Distinct Customers.LastName, Customers.FirstName FROM Customers INNER JOIN Orders ON Orders.CustomerPhone = Customers.Phone 
WHERE Orders.ProductName = 'Drill'

SELECT Customers.LastName, Customers.FirstName FROM Customers INNER JOIN Orders ON Orders.CustomerPhone = Customers.Phone 
WHERE Orders.ProductName = 'Drill' Group By LastName, FirstName

• Group by creates internal sets of records with matching fields, this allows you to use special aggregation functions on those sets of records. For example, you can use Count to count the number of records in a group, or Sum to sum up values for all the records in the group. The group by clause cannot have any calculated fields and must have all the non calcualted fields in the column list.
  
• The query below retrieves a table showing each customer who has ever made a purchase (phone, lastname, firstname), the number of the purchases, and the total amount spent on all purchases. The table is ordered by total spent (descending).  Note the use of the Group By clause. How would you like to have to write JAVA/VB code that did all that????

   SELECT Customers.Phone, Customers.LastName, Customers.FirstName, 
   Count(Orders.OrderNumber) AS NumberOfPurchases,
   SUM(Orders.Quantity*Orders.PriceEach) AS TotalSpent
   From Customers INNER JOIN Orders ON Orders.CustomerPhone = Customers.Phone
   GROUP BY Customers.Phone, Customers.LastName, Customers.FirstName 
   Order BY SUM(Orders.Quantity*Orders.PriceEach) DESC

• Aggregation functions:
• Avg()
• Count()
• Sum()
• Min()
• Max()

Insert Command

• Format:

INSERT INTO table [(column-list)] VALUES (value-list)

• The column list is optional.  When it is present, only the columns listed will be assigned values. Unlisted columns are set to null, so unlisted columns must allow nulls. The values from the VALUES Clause (first form) are assigned to the corresponding column in column-list in order. If the optional column-list is missing, the default column list is substituted. The default column list contains all columns in the table in the order they  appear in the table. Column names are listed between ( )with commas in between each name.  No quote marks are needed around column names.
• The values list contains the actual data values to be inserted. They must appear in the correct order (corresponding to the columns list) and have the correct data type for their column.  String values must be enclosed by single quote marks '
• Examples, the first example inserts a complete record into the products table with the column list defined, the second example inserts a complete record into the products table using the default column list, the third example inserts a partial record. the value for cost is set to null (not 0), this will only work if the column for cost was defined to allow null values.

INSERT INTO Products (name, quantity, cost) VALUES ('Nails',100, 22.33);
INSERT INTO Products VALUES ('Screws', 200, .03);
INSERT INTO Products (name, quantity) VALUES('Hammers',200);

• if one of the columns is an autonumber field (indentity column), then you must specifiy the column list and leave that column out. (ie don't assign a value to it).

INSERT INTO ORDERS (CustomerPhone, ProductName, Quantity, PriceEach,OrderDate)
          Values ('222-3333', 'Screws', 44, .05, #2004-12-04 12:30:00#);

• To determine the identity value generated by an insert you have to use the select function to call a special function. these functions vary from database manager to database manager, in most casees they will return the generated id of the last insert (to any table) for active connection.
  
• MS SQLServer  use  SELECT SCOPE_IDENTITY()
• MySQL use use SELECT  last_insert_id()

• Special notes on times and dates,  never name your column in your database date or time as these are SQL keywords.  To get the date/time into ms access, either:
•  #'s around it and use the format  #YYYY-MM-DD HH:MM:SS# example:

#2004-12-04 12:30:00#

• use the alternate ODBC format for timestamps, {ts 'YYYY-MM-DD HH:MM:SS'} example:

{ts '2004-12-04 12:30:00'}

Delete Command

• Format :

DELETE FROM table [WHERE logical expression]

• The optional WHERE Clause has the same format as in the SELECT Statement.  The WHERE clause chooses which table rows to delete. If it is missing, all rows are in table are removed.
• Examples, the first example deletes all the records from the Products table, the second example deletes all the records with a Name of 'Nails' from the Products table, the third example delete all records from the Products table whose cost is less than 5 cents.

DELETE FROM Products
DELETE FROM Products WHERE Name = 'Nails'
DELETE FROM Products WHERE Cost < .05

Update Command

• Format:

UPDATE table SET set-list [WHERE logical expression]

• The optional WHERE Clause has the same format as in the SELECT Statement.  The WHERE clause chooses which table rows to update. If it is missing, all rows are in table are updated. The set-list contains assignments of new values for selected columns.

SET column-1 = value-1 [, column-2 = value-2] ...

• Examples, the first example sets the Cost of the Nails product to 34, the second example sets the Cost and Quantity of the Saws product to 22 and 100 respectively, the third example sets the cost of all products to 1.00:

UPDATE Products SET Cost = 34 WHERE Name = 'Nails'
UPDATE Products SET Cost = 22, Quantity = 100 WHERE Name = 'Saws'
UPDATE Products SET Cost = 1.00

    

Partial List of SQL KeyWords (Varies with implementation)