Key data concept – data queries

A basic SQL statement has just three elements, highlighted in the following code in all caps:

SELECT column_name_1, column_name_2, …, column_name_n
FROM schema_name.data_table_name
WHERE condition

You need to provide a schema_name. Remember when we connected to a database, we saw a set of logical containers into which the tables were organized. These are examples of schemas. The terminology schema may mean slightly different things in different databases, but most organize the tables into a set of logical containers, and you need to prefix table names in your query with the container name using a dot to separate the two elements.

Often, a database administrator will create what is known as a view. These are pre-written, often quite complex, queries spanning multiple tables that define a particular dataset. As an end user, you can use them just like tables.

The SELECT and FROM clauses are mandatory, but the WHERE condition is optional and is just a logical expression to limit the data that is returned. For example:

WHERE column_name_1 = 'some text' AND column_name_2 > 4

If you want to include more than one table in your query, you will have to use what is known as a join.

SELECT a.column_name_1, b.column_name_2, …, etc
FROM schema_name.data_table_name_1 a
JOIN schema_name.data_table_name_2 b on a.column_name_x = b.column_name_y
WHERE condition

Note how the aliases a and b have been used for the two tables. Aliases can be anything you like, but are usually short and make the statement easier to write and read.

The JOIN statement ensures that you only return rows where the join condition is true, as well as any other condition you defined. There are other types of joins. The following link of the w3schools website provides a helpful tutorial: http://www.w3schools.com/sql/sql_join.asp