To visit my site

HOME] Consulting Design Maintenance Project Testing Training Turnkey Java C++ SQL HTML JavaScript C#


To contact us

© 2002 - 2015 All Rights Reserved Total Application Works



  • Tell a friend about this site (copy and paste the following HTML page into a file (e.g., using Notepad).)
  • <html>
    <head>
    <title>Example of a link </title>
    </head>
    <body>
    <a href="http://sumtotalz.com/TotalAppsWorks/SQL/MY_SQL_SELECT.html">
    Lists and their Tags </a>
    </table>
    </body>
    </html>



SQL Select Command

Objectives

The objective of the lesson is as follows:

  • Write an SQL query
  • Select and list all rows and columns from a table
  • Select and list selected columns from a table
  • Select and list columns from multiple tables
An example can be found at Tutorial: Basic Client/Server Application IV in the source file tcpServer.java found in zip file . The SELECT command is used in the source file tcpServer.java on line 424.

Select Introduction

When you issue a query, you are asking SQL to read the data specified by the SQL command. When we use SQL, we start all queries with the keyword SELECT. The tabular result is stored in a result table (called the result-set). A typical query statement needs only two parts; we select which columns from which table. Previously, we described the following record resulting from writing the following SQL command.

 
SELECT * FROM VisaRecords
 

where
  • SELECT - a SQL command used in the composition of queries.
  • * - is used to indicate that all columns/fields in the table should be returned. This has the effect of returning all records in the table.
  • FROM is a keyword that goes with the SELECT keyword
  • VisaRecords - specifies the name of the table the query should be issued against
The result might look like the following.

Account
Number
First
Name
Last
Name
Address City State Zip Country Birth
Date
Telephone Email
Address
Balance
1234567889 John Doe 123 Main St. Seattle WA 98101 USA 07/04/1978 206-555-0987 MyName@MyISP 5,987.95

This is a rather simple record and we could put all of the data records in one table. But, in anticipation of work to come, we will split the record and store the data in four tables. This move is to help the reader get use to how data is split amongst several tables. With this in mind, our tables will look like:
 
+------------+         +------------+         +------------+
|AccountName |         |   Balance  |         | TeleEmail  |
+------------+         +------------+         +------------+
| AccountNo  |----+--->| AccountNo  |-------->| AccountNo  |
| FirstName  |    |    | Balance    |         | Telephone  |
| LastName   |    |    | LastPay    |         | Email      |
+------------+    |    | AmountPayed|         +------------+ 
                  |    +------------+
                  |
                  |
                  |    +------------+ 
                  |    |AccountAddr |
                  |    +------------+ 
                  +--->| AccountNo  | 
                       | Address    |  
                       | City       |  
                       | State      |  
                       | Zip        |  
                       | Country    |  
                       +------------+   




Where the four table names are:
  • AccountName
  • Balance
  • TeleEmail
  • AccountAddr

and where AccountNo is the primary key in each table. In this example, AccountNo is unique. If we query the AccountName with the following query:
 
SELECT * FROM AccountName
 

the result will look like:
 AccountNo  
 FirstName  
 LastName  
1234567890 James Wilie
1444567890 Julie Smith
1333567890 Mary Easley
1255567890 Cato Willingham
1236667890 Mike Jordan
1234777890 Carole Fusemi
1234567900 Peter Wie
1234000890 Donald Hill
1222567890 Regina Bell
1238888890 Althea Keyes
1234599999 Charles Lawrence
1233267890 Robert Urengo
1989867890 John Wieland
1212127890 Keith Makey

Select * (asterisk)

when we issue the following command

 
SELECT * FROM AccountAddr
 

we get:
 AccountNo  
 Address  
 City  
 State  
 Zip  
 Country  
1234567890 123 Main St. Garner NC 27500 USA
1444567890 123 Oak St. Raleigh NC 27500 USA
1333567890 123 Walnut St. Apex NC 27500 USA
1255567890 123 Jones St. Durham NC 27500 USA
1236667890 123 Saunders St. Apex NC 27500 USA
1234777890 123 Oak Lane Raleigh NC 27500 USA
1234567900 123 Oak Way Garner NC 27500 USA
1234000890 123 Oak Blvd. Cary NC 27500 USA
1222567890 123 Walnut Lane Apex NC 27500 USA
1238888890 123 Walnut Way Raleigh NC 27500 USA
1234599999 123 Whatis St. Garner NC 27500 USA
1233267890 123 Academy St. Durham NC 27500 USA
1989867890 123 MaIn St. Apex NC 27709 USA
1212127890 404 Walnut Ave. Raleigh NC 27511 USA

Select and list columns from multiple tables

If we want to combine the results of the above two queries into one, we would issue the following command.

 
SELECT * FROM AccountName, AccountAddr WHERE
AccountName.AccountNo = AccountAddr.AccountNo
 
 AccountNo  
 FirstName  
 LastName  
 AccountNo  
 Address  
 City  
 State  
 Zip  
 Country  
1234567890 James Wilie 1234567890 123 Main St. Garner NC 27500 USA
1444567890 Julie Smith 1444567890 123 Oak St. Raleigh NC 27500 USA
1333567890 Mary Easley 1333567890 123 Walnut St. Apex NC 27500 USA
1255567890 Cato Willingham 1255567890 123 Jones St. Durham NC 27500 USA
1236667890 Mike Jordan 1236667890 123 Saunders St. Apex NC 27500 USA
1234777890 Carole Fusemi 1234777890 123 Oak Lane Raleigh NC 27500 USA
1234567900 Peter Wie 1234567900 123 Oak Way Garner NC 27500 USA
1234000890 Donald Hill 1234000890 123 Oak Blvd. Cary NC 27500 USA
1222567890 Regina Bell 1222567890 123 Walnut Lane Apex NC 27500 USA
1238888890 Althea Keyes 1238888890 123 Walnut Way Raleigh NC 27500 USA
1234599999 Charles Lawrence 1234599999 123 Whatis St. Garner NC 27500 USA
1233267890 Robert Urengo 1233267890 123 Academy St. Durham NC 27500 USA
1989867890 John Wieland 1989867890 123 MaIn St. Apex NC 27709 USA
1212127890 Keith Makey 1212127890 404 Walnut Ave. Raleigh NC 27511 USA












[
HOME] Consulting Design Maintenance Project Testing Training Turnkey Java C++ SQL HTML JavaScript C#

© 2002 - 2015 All Rights Reserved Total Application Works