NEW RB database examples

[Joseph Claeys]

The subject says it all... It seems that all of the database examples that
I come across are for the old database. Even the examples on the RS
website. I managed to fix one of them but would like to see more examples
and fixing them as I go (while a good learning experience) is becoming very
time consuming.

So does anyone knows of a good tutorial/example/code snippet/anything re:
new rbd then please let me know.

Thanks,


~ joe

This e-mail may contain confidential and privileged material for the sole
use of the intended recipient(s). Any review, use, or distribution by others
is strictly prohibited. If you are not the intended recipient(s), please
contact the sender by reply e-mail and delete all copies of this message.


_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>

Search the archives of this list here:
<http://support.realsoftware.com/listarchives/lists.html>

[Joseph J. Strout]

Post by Joseph Claeys
The subject says it all... It seems that all of the database examples that
I come across are for the old database. Even the examples on the RS
website. I managed to fix one of them but would like to see more examples
and fixing them as I go (while a good learning experience) is becoming very
time consuming.
new rbd then please let me know.

The following examples assume a database with two tables: Movies
(containing Title, Director, and Year), and Actors (containing Name
and Movie, where Movie holds the _rowid value of the corresponding
row in the Movies table). For each example, we present a plain
English description, the SQL syntax, the result code, and the values
returned by the query (where applicable, and assuming a handful of
pre-existing data).

EXAMPLE: Prove that SQL can do math.
SQL: select 2+3+2*3
RESULT: 00000
11

EXAMPLE: List all fields for all movies in the database.
SQL: select * from Movies
RESULT: 00000
1,Star Wars,George Lucas,1977
2,Raiders of the Lost Ark,George Lucas,1981
3,American Graffiti,George Lucas,1973
4,Apollo 13,Ron Howard,1995
5,Cast Away,Robert Zemeckis,2000

EXAMPLE: List distinct actors (i.e., don't list any actor more than once).
SQL: select distinct Name from Actors
RESULT: 00000
Harrison Ford
Mark Hamill
Carrie Fisher
Ron Howard
Richard Dreyfuss
Tom Hanks

EXAMPLE: Find all actors who were in Star Wars.
SQL: select a.name from Actors A, Movies M where a.movie=m._rowid and
m.title='Star Wars'
RESULT: 00000
Harrison Ford
Mark Hamill
Carrie Fisher

EXAMPLE: Find actors, with movie title and year, for all movies
directed by George Lucas.
SQL: select A.Name,M.Title,M.Year from Actors A, Movies M where
A.movie=M._rowid and M.Director='George Lucas'
RESULT: 00000
Ron Howard,American Graffiti,1973
Richard Dreyfuss,American Graffiti,1973
Harrison Ford,Raiders of the Lost Ark,1981
Harrison Ford,Star Wars,1977
Mark Hamill,Star Wars,1977
Carrie Fisher,Star Wars,1977

EXAMPLE: See if Joe has been in any movies.
SQL: select Name from Actors where Name='Joe Strout'
RESULT: 02000

EXAMPLE: Find all actors who have done a movie with Harrison Ford.
SQL: select A.name from Actors A, Actors B where A.movie = B.movie
and B.name='Harrison Ford' and A.name<>'Harrison Ford'
RESULT: 00000
Mark Hamill
Carrie Fisher

EXAMPLE: Find anyone who is both an actor and a director.
SQL: select A.Name from Actors A, Movies M where A.Name = M.Director
RESULT: 00000
Ron Howard

EXAMPLE: Add a new record to the Movies table.
SQL: insert into Movies (title, director, year) values ('Joe''s Home
Movie', 'Joe Strout', 2003)
RESULT: 00000

EXAMPLE: Verify that the record was added correctly.
SQL: select director, title from Movies where title = 'Joe''s Home Movie'
RESULT: 00000
Joe Strout,Joe's Home Movie

EXAMPLE: Delete the record we just added.
SQL: delete from Movies where director='Joe Strout'
RESULT: 00000

EXAMPLE: Attempt a table alteration while there's a transaction in progress.
SQL: alter table Movies add column Rating integer
RESULT: 25000

EXAMPLE: Find any movies with the word "War" in the title (ignoring case).
SQL: select title, year from Movies where Upper(title) like '%WAR%'
RESULT: 00000
Star Wars,1977

EXAMPLE: Find the director of any move not made in the 1900s, and
count the characters in his name.
SQL: select director, char_length(director) from Movies where year
not like '19__'
RESULT: 00000
Robert Zemeckis,15

EXAMPLE: Find out when each director made his first and last movie
(in our database).
SQL: select Director, Min(Year), Max(Year) from Movies group by Director
RESULT: 00000
George Lucas,1973,1981
Robert Zemeckis,2000,2000
Ron Howard,1995,1995

EXAMPLE: Find out how many actors we have for each movie (by title).
SQL: select A.Title, Count(B.Name) from Movies A, Actors B where
B.Movie = A._rowID group by B.Movie
RESULT: 00000
Star Wars,3
Raiders of the Lost Ark,1
American Graffiti,2
Apollo 13,1
Cast Away,1

EXAMPLE: Find how many movies each actor has done, but display only
those who have done more than one.
SQL: select Name, Count(*) from Actors group by Name having Count(*) > 1

Cheers,
- Joe
--
REAL World 2005 - The REALbasic User Conference
March 23-25, 2005, Austin, Texas
<http://www.realsoftware.com/realworld>
_______________________________________________
Unsubscribe or switch delivery mode:
<http://www.realsoftware.com/support/listmanager/>

Search the archives of this list here:
<http://support.realsoftware.com/listarchives/lists.html>