- slide 1 of 4
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).
- 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
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:
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.
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.
- slide 4 of 4
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