Basic SQL SELECT

When selecting fields from the database, all that is needed in the query statement are the SELECT and FROM clauses with their corresponding details, the one to specify which fields, and the other to specify from which database objects, typically tables or views. This lesson covers these two most basic clauses and also provides an introduction to the mechanics of interacting with the user interfaces we use to write and execute SQL queries.

FILTERING

When you want to view only a subset of records, such as only those for a particular carrier, the WHERE clause allows you to filter your result set. You specify a particular value (or values) that you want to keep (or exclude), and all that is returned are the records with those characteristics.

ORDERING

With the ORDER BY clause, you can arrange the records so that you can more easily see trends.

GROUP BY allows you to specify the categories by which you want to aggregate the data in order to summarize the information, and HAVING lets you limit the results of the aggregated values.

As a technical aside, we discuss why companies use Development, Test, and Production environments. We also look at the differences between databases using an OLTP structure and an OLAP structure. We end with a quick overview of the metadata queries available in several RDMBS applications.

When you need to filter or limit the result set by criteria in more than one field, or negate the criteria, conjunctions allow you to string together multiple conditions to achieve those results.

The CASE statement lets you modify your result set without making changes to the underlying data in the source tables. Arranged in one of two general forms, it runs the data through conditions to make updates that will flow through to the final results. You can insert it into any clause of the SELECT statement as if it were just another field in the table.

Understanding JOINs is essential for querying a database. Most information you will need will be spread across multiple tables, and JOINs pull this data together more efficiently than other methods. The INNER, the LEFT, RIGHT and FULL OUTER, and the CROSS JOINs connect data tables in different ways in order to provide the set of data fields and records needed.

Applying a consistent style will improve your SQL code's readability and clarity. Learning how to use aliases well and when and where to add comments goes a long way to making your code more user-friendly.

When you have more than one data table with similar fields and with records that are clearly split into separate tables, set operators will combine these data subsets into one set. UNION, INTERSECT, and EXCEPT/MINUS combine the record sets, merging duplicates when desired.

Use subqueries to build smaller query blocks that are arranged to generate larger, more complex queries. Be careful to not substitute a subquery for a more efficient method, but make use of these to better organize the query logic and to avoid the impulse to create a table or view to encapsulate the temporary need for data. Subqueries are a powerful technique that enhances the query's modularity.

We'll take a look back at all we covered in this course.

A lot more can be done with SQL. This provided some of the basics. Come again and be on the lookout for my other courses!