SQL Tutorial

SQL (Structured Query Language) is the language that PowerBuilder uses to talk to all of the popular RDBMSs, e.g. Oracle, Sybase, Informix, DB2, MS SQLServer, SQL Anywhere etc. Though the DataWindow in PowerBuilder generates the SQL necessary for most routine things, you need to understand the underlying logic. SQL is a standard query language that can be used to enter, query, and change data in a database. SQL is also used to create and administer databases. Though each RDBMS has some variations on the basic SQL, we will cover common syntax here, by taking an example.

I strongly suggest that you create these two tables in the Examples database itself (from the database painter) and run these queries from the Database Administration in the database painter. This will give you a hands-on feel for SQL, which you can never get just by reading.

Let us say we have two tables 'Players' and 'Teams' as follows:

Players

Nbr	Name	Average	Team
1	Baerga, C.	.322	T1
2	Lofton, K.	.322	T1
3	Belle, A.	.311	T1
4	Thome, J.	.330	T1
5	Ramirez, M.	.330	T1
6	ONeill, P.	.222	T2
7	Mattingly, D.	.233	T2
8	Boggs, W.	.255	T2
9	Bonds, B.	.303	T3
10	Williams, M.	.277	T3
11	Santiago, C.	.111	T3

Teams

Team	City	Name
T1	Cleveland	Indians
T2	New York	Yankees
T3	San Francisco	Giants

[Top] [Tables]

Sample SQL Queries (The query itself is in italics. Type upto and including the semi colon.):

SELECT * FROM Players; will list all information for all of the players.
SELECT Name FROM Players; will list all of the players names.
SELECT Name, Average FROM Players; will list all of the player's names and corresponding averages.
SELECT * FROM Players ORDER BY Average; will list all of the player's information sorted by the batting average.
SELECT * FROM Players ORDER BY Average, Name; will list all of the player's information sorted by the batting average and alphabetically. Note Baerga and Lofton.
SELECT DISTINCT Average FROM Players; will list the player averages that differ, i.e. .322 will appear only once.

[Top] [Tables]

SELECT Players.Name, Teams.Name FROM Players, Teams WHERE Players.Team=Teams.Team; will list the player names and their corresponding team nicknames.
SELECT Name, Average FROM Players WHERE Average > .300; will list the good hitters in the list of players.
SELECT Players.Name, Average FROM Players,Teams WHERE Average>.300 AND Players.Team=Teams.Team and Teams.Name LIKE 'Indians'; will list all of the Indian's hitters since they have the best lineup in baseball.
SELECT Players.Name, Average FROM Players,Teams WHERE Average > .300 AND Players.Team=Teams.Team AND Teams.Name IN ('Indians', 'Yankees'); still gives a listing of all of the Indian's hitters since the Yankees are a so-so team.
SELECT Count(*) FROM Players WHERE Average>= .300 AND Average < .330; will return 4.
SELECT Count(*) FROM Players WHERE Average >= .330; will list 2 since Manny Ramirez and Jim Thome are the best.

[Top] [Tables]

SELECT Name FROM Players WHERE Average >= .330; yep, Manny and Jim are the best.
SELECT MAX(Average) FROM Players; .330 will be printed.
SELECT AVG(Average) FROM Players,Teams WHERE Players.Team = Teams.Team AND Teams.Name='Indians'; is notable because it is above .300 -- how many teams can claim five great hitters in their lineup?
SELECT SUM(Average) FROM Players,Teams WHERE Players.Team = Teams.Team AND Teams.Name NOT IN ('Indians','Yankees'); is probably still less than the Indians average. (Finding the sum of batting averages doesn't really make sense, but it could be done.)
SELECT Name FROM Players WHERE Average > (select avg(average) from players); prints a list of players with batting averages that are "above average".
SELECT Name FROM Players WHERE Average < (select avg(average) from players)/2; prints a list of players with a batting average that is less than half of all of the averages.

[Top] [Tables]

This example courtesy of UWEC.

Number of hits since 10/01/96:

This web site is maintained by Ashok Ramachandran
Revised: December 30, 1998