Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL etc

Posted Sep 13, 1998

Introduction to Databases for the Web: Pt. 2

By Selena Sol

Retrieving Data

Okay, enough of all that DB Admin stuff. Let's get down to the nitty gritty of retrieving data from your database.

In SQL, the "SELECT - FROM" statement is used to grab data from a database. The statement follows the generic syntax of:

SELECT column_name 
FROM table_name;

There are a few things to notice about the format of this SQL statement that will help us understand all SQL statement formats.

First, notice that the statement ends in a semicolon. Like English, SQL requires termination punctuation so that the SQL database will know when you are done speaking to it. In the case of SQL, the semicolon (;) character works like the period in English.

Second, notice that the statement spans multiple lines. This is more of a convention than a necessity. Like HTML, SQL ignores whitespace in a statement, so you could just as easily write SELECT column_name FROM table_name. The reason we break the statement up into multiple lines is to increase readability. As your SQL statements get more and more complex, you will find that if you break them up into logical blocks, they will be easier to read.

Finally, notice that we have used all uppercase letters for our keywords (like SELECT and FROM). This is a good idea as even though most implementations of SQL are case insensitive, some are not. Okay, let's get back to the example...

In other words, if you wanted to get all the names of the employees in the EMPLOYEE table, you would type:

SELECT EMP_NAME 
FROM EMPLOYEES;

In the case of our sample database, you would get the following results:

    EMP_NAME
    -------------
    Lim Li Chuen
    Lim Sing Yuen
    Loo Soon Keat
    -------------

We are going to be using the sample database throughout the day in order to show examples, so get familiar with the tables.

You needn't limit yourself to single columns of course, The column_name parameter may take a comma delimited list so that if you also wanted a report for the employee number and salary, for example, you would simply use:

SELECT EMP_NAME, EMP_NUMBER, EMP_SALARY 
FROM EMPLOYEES;

In this case, you would get the following results:

EMP_NAME	EMP_NUMBER 	EMP_SALARY
------------------------------------------
Lim Li Chuen	001		90,000
Lim Sing Yuen	002		40,000
Loo Soon Keat	003		50,000
------------------------------------------

Note that if you specify a column name that does not exist, you will get an error.

Wildcards

As in most languages, SQL provides a set of wildcards that are used as shortcuts to represent whole categories of values. For example, oftentimes, you may want all the data for the columns in your table but you don't want to write all the column names in a comma-delimited list.

To make such queries more efficient, SQL provides the "*" wildcard that specifies "ALL" of something. For example, to select all the columns in the PRODUCTS table, we would use:

SELECT * 
FROM PRODUCTS;

The database would then respond with:

P_NUM	P_QUANTITY	P_PRICE
-------------------------------
001	104	 	 99.99
002	12		865.99
003	2000   		 50.00
-------------------------------

Of course you could achieve the same results (with more work) using:

SELECT P_NUM, P_QUANTITY, P_PRICE 
FROM PRODUCTS;


SQL etc Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL etc Forum
Topic By Replies Updated
MySQL rollback UAL225 0 August 21st, 09:56 PM
Complex Search Query Galway 0 May 20th, 10:04 PM
change collation at once supercain 2 May 15th, 06:18 AM
SQL Features, tools and utilities question Neomite 1 April 10th, 09:13 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date