Pin Me

Tips on How to Write SQL Scripts

written by: George Garza•edited by: Linda Richter•updated: 5/20/2011

Learn how to write SQL scripts, or Structured Query Language, a programming language used in databases. It retrieves data from a variety of tables, which are the principal holders of information. A series of tables in one group forms a database, and SQL is the scripting language.

  • slide 1 of 4

    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.

    Tables-Connections 

    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

  • slide 2 of 4

    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.

  • slide 3 of 4

    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

    Inner Join 

    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.

    Left Outer Join 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.

    Right Outer Join 

    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

  • slide 4 of 4

    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