Simple SQL queries

The following sql will give us the number of rows in the user table:

select count(*) from users;

The following sql will tell us the schema of the user table:

describe users

A simple query from one table will typically have the following structure:

• the select list (which columns in our report)
• the name of the table
• the where clauses (which rows we want to see)
• the order by clauses (how we want the rows arranged)

select email from users where email like '%ail.com';

select email from users where email like '%ail.com' order by email;
select count(*) from users where registration_date >= '1998-09-01' and registration_date < '1998-10-01' and email like '%mit.edu';
select user_id, email  from users  where 0 < (select count(*)             from classified_ads            where classified_ads.user_id = users.user_id);
select user_id, email  from users  where exists (select 1               from classified_ads               where classified_ads.user_id = users.user_id);
AND A JOIN:
select users.user_id, users.email, classified_ads.posted from users, classified_ads where users.user_id = classified_ads.user_id order by users.email, posted;
The above query will not show users who have not posted any classifieds. 
If we want to see users who have not posted any classifieds, then we need an outer join.
select users.user_id, users.email, classified_ads.posted from users, classified_ads where users.user_id = classified_ads.user_id(+) order by users.email, posted;

Extending a simple query into a JOIN

Suppose that you have a query from one table returning almost everything that you need, except for one column that's in another table. Here's a way to develop the JOIN without risking breaking your application:

1. add the new table to your FROM clause
2. add a WHERE constraint to prevent Oracle from building a Cartesian product
3. hunt for ambiguous column names in the SELECT list and other portions of the query; prefix these with table names if necessary
4. test that you've not broken anything in your zeal to add additional info
5. add a new column to the SELECT list