learning by sample t-sql - part 1
DESCRIPTION
For more tutorial visit www.bukansembarang.infoTRANSCRIPT
w w w . B u k a n S e m b a r a n g . I n f o © 2 0 1 0
Page 0
Learning By Sample – T-SQL : Part 1 2010
w w w . B u k a n S e m b a r a n g . I n f o © 2 0 1 0
Learning By Sample –
T-SQL : Part 1
T- SQL : Part 1
Learning By Sample Series
w w w . B u k a n S e m b a r a n g . I n f o © 2 0 1 0
Page 1
Learning By Sample – T-SQL : Part 1 2010
Foreword
Learning By Sample ?
So, why should I made this tutorial freely accessed by everyone ? Well, surely I am just nobody, I
also just another ordinary person with ordinary knowledge. However, I always feel that some persons in
the internet giving away their tutorial for free and I feel being helped by them. Then, I remember one of
my senior wisdom words : “If you want to be given more, then start to give more”. And of course, I really
believe those words, since that I already proof it to be right all the time.
Also, I’m not an English native speaker, however, I just try to improve my English in any other
way, especially in written format. Thus, I think writing is the best practice to improve my English freely.
However, you will find many grammatical errors in my tutorial, so please send me comments and also
suggestion to improve it.
Then, why the format using Learning By Sample series ? Is it really different with other tutorials
out there ? Ehm, I already wrote three books (in Indonesian) using this kind of format, and many readers
already contact me about how this format really help them to learn from the scratch. So, why in the
world I’m not re-create it in English format, right ? Eventhough actually, it is similar with Hands On Lab
series which already famous in Microsoft site previously.
All of my lesson also being designed as short samples and short time exercise. Thus, I hope that
each of tutorial series would take only at least 10-15 minutes maximum to learn. Why keep it short ?
Because many beginner (and even expert) will find boring whenever they must keep studying more than
15 minutes (but you will never get bored when you online in such time right ?).
Another reason is just because many of samples in this series come up from my lecturing task
exercise. So, I just try to compile all my lab exercises in order to keep it tidy and also reusable for my
students. That’s why you will find many unsorted course material inside these series, however, just take
a seat and enjoy the ride !
Requirements
So, what will you need before starting this simple tutorial ? First, you need a PC/laptop/netbook
which use at least Windows XP/Vista/Seven/Server as its operating system. And, you should also install
SQL Server (at least Express Edition) from version 2005 and above. And remember, if you have SQL
Server Express Edition, make sure that you already download and install its SQL Server Management
Studio too.
w w w . B u k a n S e m b a r a n g . I n f o © 2 0 1 0
Page 2
Learning By Sample – T-SQL : Part 1 2010
Is that enough ? Of course not, you also need Northwind database attached to your database
server (which you can download it from download section of this website) that already widely being
used as sample database from SQL Server 2000. And if you ask, why use Northwind database instead of
Adventure Work database, the main reason is just because its simplicity. Northwind database contains
small number of tables and also it only covers small amount of records, thus beginner will feel
comfortable and fast in doing this lesson.
So, if you already download your Northwind script, then just open your SQL Server Management
Studio and then open the northwind.sql script file. Afterward, execute the script by pressing F5 key, and
that’s it, you will have a newly Northwind database attached to your SQL server.
Prior Knowledge needed
If you really want to follow this series, then you should aware that basic SQL knowledge really
needed. For example, this HOL wil not include any basic knowledge about T-SQL basic command and
also about the basic of SQL server.
And also, you should know the relationship diagram of Northwind database before you
understand this lesson. You can learn about Northwind relationship by creating Database Diagram in
SQL Server Management Studio. But, how to create the diagram ? Let’s follow these instructions :
1. Open SQL Server Management Studio
2. Find Object Explorer window and find Northwind database
3. Right click at Database Diagram node
4. Choose sub menu New Database Diagram
w w w . B u k a n S e m b a r a n g . I n f o © 2 0 1 0
Page 3
Learning By Sample – T-SQL : Part 1 2010
5. Then, you will have a dialog box open which contains tables from Northwind database. Choose all of
the tables to be included.
6. That’s a wrap !
w w w . B u k a n S e m b a r a n g . I n f o © 2 0 1 0
Page 4
Learning By Sample – T-SQL : Part 1 2010
Let’s get started
Which Tables ?
I’m not going to use all tables in Northwind database in this lesson. It only takes six tables, which
are : Customers, Products, Categories, Employees, Orders and Order Details.
You should also need to know each primary key from each tables and also its relationship. For
instance, you must aware that between Customers table and Orders table is connected by CustomerID
field in both tables. Try to learn all the relationship using its database diagram before you really start
this lesson. Now, are you ready ?
First query : Displaying all rows
1. Make sure that you already open SQL Server Management Studio
2. Right click at node Northwind database in Object Explorer window and choose New Query
w w w . B u k a n S e m b a r a n g . I n f o © 2 0 1 0
Page 5
Learning By Sample – T-SQL : Part 1 2010
3. Type the queries, and whenever you have done it, highlight all the query text you want to execute
and press F5. Now let’s try :
a. Displaying all customers data
Here is the partial result :
b. Displaying all employees
Another result :
w w w . B u k a n S e m b a r a n g . I n f o © 2 0 1 0
Page 6
Learning By Sample – T-SQL : Part 1 2010
If you use SQL Server 2008, you will see an autocomplete drop down list.
If you find a table/field that you will type and it shown in drop down list,
just press Enter and you will get the phrase without typing it.
4. How about displaying merely some fields rather than all of them ?
a. Displaying only product name and its price
Partial result preview :
w w w . B u k a n S e m b a r a n g . I n f o © 2 0 1 0
Page 7
Learning By Sample – T-SQL : Part 1 2010
b. Displaying employee name
The result should be displayed like this picture :
5. Easy right ? Now, let’s continue….
w w w . B u k a n S e m b a r a n g . I n f o © 2 0 1 0
Page 8
Learning By Sample – T-SQL : Part 1 2010
Two Tables or More ?
1. I’m not using JOIN technique in displaying multiple tables for this lesson. I thought it was slightly
easier for beginner to have old tricks in displaying multiple tables. I’m just using the same fields from
each related tables to display them. Now, let’s try it :
a. Displaying Order transaction with its Company name
The result :
b. Displaying sales transaction from customers which based in USA
w w w . B u k a n S e m b a r a n g . I n f o © 2 0 1 0
Page 9
Learning By Sample – T-SQL : Part 1 2010
Partial result is shown below :
c. Displaying product name and its category and sort them based upon the category name
See the result here :
2. Just as easy as before, then why should wait ? Let’s roll on….
w w w . B u k a n S e m b a r a n g . I n f o © 2 0 1 0
Page 10
Learning By Sample – T-SQL : Part 1 2010
Aggregating
1. So, what about aggregating some numeric fields ? Just like previous lesson, it’s as easy as 1 2 3….
a. Displaying summary aggregate of product unit price
Here is the result :
b. Displaying how many customers listed
This is the result :
c. Displaying the average of sales transaction in 1998
w w w . B u k a n S e m b a r a n g . I n f o © 2 0 1 0
Page 11
Learning By Sample – T-SQL : Part 1 2010
See the outcome :
d. Displaying sales omzet in 1997 only from customer which based in Finland
Here is the result :
2. Well, I think that it’s time to take a break right ? See you in next lesson…..