Tips on How to Write SQL Scripts

Overview

A database is made up of a group of tables. A table is a collection of rows and columns, like the white pages in a telephone book. A table has a primary key, which is a column with a unique value for each row. In the telephone book example, the telephone number would be the primary key. Why? Because the names in the name column may contain duplicates. A variant of the primary key is the foreign key, which is used to connect different tables together so that they show the relationship of one to another.

The first tip in designing a query is to know how all the tables are related to one another. A graphic showing the tables and the connections is important. This allows you to see the primary keys (the unique column) and foreign keys (columns that connect to other tables).

SQL is an industry standard used by Microsoft (SQL Server 2005/2008 and Access), Oracle, and MySql. For more information see a short introduction to SQL.

Image: Microsoft

Elements of a Basic SQL Script

To obtain the data from the tables, programmers use a language called SQL. Using the tables above, here are some examples of SQL scripts:

Select Period_ID , Period_Desc

from Period ;

Here is a breakdown of the code, the Period_ID and Period_Desc are columns in the Period table. The query will pull all of the rows from just those two columns.

We can go a step further by adding some conditions. For example, the information most relevant for a report may be in the year 2010. Here is what that SQL script would look like.

Select Period_ID , Period_Desc

from Period

where year = “2010”;

Here are some other example using the Product Table.

Select * from Product;

The * is a substitute for the word “all.” In this case all the data from the Product table would be required. So the results would be,

The Product_ID, the Product_Desc, the Brand, and the Size. All four columns would be displayed, plus all of the rows in the table.

Some Advanced SQL Scripts and Tips

The power of SQL scripts comes from the ability to connect multiple tables together and produce one or more reports. A join is an SQL method used to get information from more than one table, depending on the relationship between them. The primary keys are the links between the tables.

Advanced Select Query

Select Period_ID, Period_Desc, Market_ID, Units, Dollars, Brand

from Period, Sales, Market, Product

where Period.Period_Id = Sales.Period_id, Sales.Product_Id = Product.Product_Id, Sales.Period_ID=Market.Market_Id;

Here is a breakdown of this code. The programmer wants to get information from all four tables. Each table is connected to another by way of the keys: Period_Id (Period and Sales), Market_Id(Sales and Market), and Product_Id(Sales and Product). The information that is needed is from five columns: Period_ID, Period_Desc, Market_ID, Units, Dollars, Brand.

Inner Join Query

An inner join returns rows when there is at least one match in both tables. This is the structure of an inner join:

SELECT columns

FROM table_1 JOIN table_2

ON table_1. primarykey = table_2.foreignkey

Select Period.Period_Id, Sales.Period_Id

from Period Join Sales

where Period.Period_Id = Sales.Period_Id

Left Outer Join

This query returns all of the rows from the left table, even if in the right table there are no matches.

Here is some sample code for a left outer join:

Select Sales.Period_ID, Market.Market_ID

left outer join Market

On Sales.Market_ID = MarketMarket._ID

Right Outer Join

The logic behind this is that the query returns all of the rows from the right table, even if in the left table there are no matches.

Select Sales.Period_ID, Product.Product_ID

Right outer join Sales

On Sales.Market_ID = Product.Product_ID

From these examples, you can see that creating an image can help you anticipate the results that you are looking for. In this case the Venn diagram can help identify where the results are expected from. For more on Venn Diagrams see Math Lesson Plans in Venn Diagrams.

Image: GGarza

Source: W3Schools

Summary

SQL has a variety of tools and variants that are used for producing information. Some of the tips that are valuable in designing a query involve diagrams. The first is to graph all of the tables that are present and show their interconnectedness. The next involve using Venn diagrams to describe the logic of the tables that are used to produce information. Examples of inner and outer joins were provided in this introduction on how to write SQL scripts.

Source: SQL Tutorial