Just laying around and thought I would write a quick post on writing basic SELECT statements.  I realize that some of my blogs will seem simple to some, but the main goal of my blog and the main website is to help consultant, programmers and/or anyone else for that matter.  I have met far too many consultants who bear the title "Technical Consultant" that cannot perform the most basic technical tasks.  With this in mind I have started this site and blog to help give consultants or anyone else a place to learn and share.

Alright, back to the topic at hand.  Many times the first place to start when approaching a database problem is to view the data in question.  This can be as simple as selecting all the data in one table, some of the data in one table, or it can be far more complex, joining many tables with complex conditions.  I am going to focus on the simple cases that most consultants would see on a regular basis.

The most basic structure of a SELECT statement is as follows

SELECT column1, column2, .....columnN FROM table_name;

This will return all rows of data in the table but only the columns which are listed.  A very common alternative is

SELECT * FROM table_name;

This will return all columns and all rows from the given table name.  But what if we don't want all the rows.  In this case we apply a WHERE clause to the end of the SELECT statement.

SELECT * FROM table_name WHERE condition1 [AND, OR] condition2 .....[AND, OR] conditionN;

This will select all columns from the table but only those rows that meet the specified conditions.  For example

SELECT COMPANY_NAME, ADDRESS FROM COMPANY_INFO WHERE STATE = 'New York';

Will return all company names and addresses for the state of New York only.  We could add more conditions using AND as well as OR as follows

SELECT COMPANY_NAME, ADDRESS FROM COMPANY_INFO WHERE (STATE = 'New York' OR STATE='Texas');

Another common situation that I come across is checking to see if a given value exists in a list.  For this we can use the IN statement.

SELECT COMPANY_NAME, ADDRESS FROM COMPANY_INFO WHERE STATE IN ('New York','Texas');

Alternatively this list could come from another table.  This requires us to use and embedded SELECT statement.  We do this by surrounding the second where clause with parenthesis.

SELECT COMPANY_NAME, ADDRESS FROM COMPANY_INFO WHERE STATE IN ( SELECT STATE FROM STATE_TABLE WHERE STATE_NAME IN ('New York', Texas') );

The last case that I want to cover before falling asleep is when the data we want to view is also in different tables.  Lets assume, although maybe a little extreme, that there is a table COMPANIES for company names, STATES for the state and ADDRESSES for the address of the company.  All table contain the same primary key of COMPANY_NAME.  If we wanted to select the same data as we have above we could write

SELECT c.COMPANY_NAME, a.ADDRESS, s.STATE
FROM COMPANIES c, ADDRESSES a, STATES s
WHERE
a.COMPANY_NAME = c.COMPANY_NAME AND
a.COMPANY_ NAME = s.COMPANY_NAME;

I don't want to get into too much detail on joins in this post but this would be an inner join.  We are only going to return the values where the COMPANY_NAME is equal between the COMPANIES table and the other two tables.

One last point that almost all consultants will need to know at some point is how to return the number or records returned by a given query.  We do this using the COUNT statement.

SELECT COUNT(*) FROM table_name;

This will return the total number of rows for the table given.  Alternatively we can supply the COUNT method with any SQL query that returns any number of rows.  For example

SELECT COUNT( SELECT COMPANY_NAME, ADDRESS FROM COMPANY_INFO WHERE STATE = 'New York')) ;

I know this is far from a perfect tutorial but hopefully it can help someone looking for a quick answer.