Saindra Santyadiputra

Seperti yang telah kita ketahui, konsep subnetting adalah meminjam bit host untuk dikorbankan menjadi alamat network. Metode subnetting terdiri dari 2 macam:
- Melihat berdasarkan kebutuhan jumlah network
- Melihat berdasarkan kebutuhan jumlah IP Address

Pada VLSM menggunakan metode kedua, yakni melihat berdasarkan kebutuhan jumlah IP Address terbanyak dalam suatu network.

Contoh kasus yang digunakan adalah sebagai berikut:



















Apabila diurutkan berdasarkan kebutuhan jumlah IP Address, dapat diurutkan menjadi:

1. LAN Branch-1 = 120 IP Address
2. LAN Branch-2 = 60 IP Address
3. LAN Branch-3 = 14 IP Address
4. LAN Branch-4 = 14 IP Address
5. WAN 1 = 2 IP Address
6. WAN 2 = 2 IP Address
7. WAN 3 = 2 IP Address

*Ket: 2^n = 2 pangkat n

I. Menentukan alamat subnetwork LAN Branch-1
2^n-2 >= 120, n=7 (7= jumlah bit 0 yang tetap)

IP Asal: 192.168.3.0
SM Asal : 255.255.255.00000000 (/24)
SM Baru : 255.255.255.10000000 (/25)

Terbentuk dua network:
- 192.168.3.0 /25 <== Digunakan untuk network LAN Branch-1
- 192.168.3.128 /25 <== di-subnet lagi untuk mendapatkan subnetwork baru

LAN Branch-1
- Alamat subnetwork : 192.168.3.0 /25
- Range IP Address : 192.168.3.1 – 192.168.3.126
- Alamat broadcast : 192.168.3.127

II. Menentukan alamat subnetwork LAN Branch-2
2^n-2 >= 60, n=6 (6= jumlah bit 0 yang tetap)

IP Asal : 192.168.3.128
SM Asal : 255.255.255.10000000 (/25)
SM Baru : 255.255.255.11000000 (/26)

Terbentuk dua network:
- 192.168.3.128 /26 <== Digunakan untuk network LAN Branch-2
- 192.168.3.192 /26 <== di-subnet lagi untuk mendapatkan subnetwork baru

LAN Branch-2
- Alamat subnetwork : 192.168.3.128 /26
- Range IP Address : 192.168.3.129 – 192.168.3.190
- Alamat broadcast : 192.168.3.191

III. Menentukan alamat subnetwork LAN Branch-3
2^n-2 >= 14, n=4 (4= jumlah bit 0 yang tetap)

IP Asal : 192.168.3.192
SM Asal : 255.255.255.11000000 (/26 )
SM Baru : 255.255.255.11110000 (/28 )

Terbentuk empat network:
- 192.168.3.192 /28 <== Digunakan untuk network LAN Branch-3
- 192.168.3.208 /28 <== Digunakan untuk network LAN Head Office
- 192.168.3.224 /28 <== di-subnet lagi untuk mendapatkan subnetwork baru
- 192.168.3.240 /28

Dari subnetting di atas didapatkan 4 buah network baru karena perubahan bit biner dari /26 ke /28 ada 2 bit, sehingga terbentuk 2(2) = 4 network.
LAN Branch-3
- Alamat subnetwork : 192.168.3.192 /28
- Range IP Address : 192.168.3.193 – 192.168.3.206
- Alamat broadcast : 192.168.3.207

LAN Head Office
- Alamat subnetwork : 192.168.3.208 /28
- Range IP Address : 192.168.3.209 – 192.168.3.222
- Alamat broadcast : 192.168.3.223

IV. Menentukan alamat WAN (Wide Area Network).

Untuk mendapatkan subnetwork address koneksi WAN, dapat memecah subnetwork 192.168.3.224 /28.

Berikut ini pemecahannya
2^n-2 >= 2, n=2 (2= jumlah bit 0 yang tetap)
IP Asal : 192.168.3.224
SM Asal : 255.255.255.11110000 (/28 )
SM Baru : 255.255.255.11111100 (/30 )

Terbentuk 4 network:
- 192.168.3.224 /30 <== Digunakan untuk network WAN 1
- 192.168.3.228 /30 <== Digunakan untuk network WAN 2
- 192.168.3.232 /30 <== Digunakan untuk network WAN 3
- 192.168.3.236 /30 <== dapat digunakan apabila ada pengembangan jaringan.

Proses subnetting di atas dapat disingkat menjadi:
- 192.168.3.0 /24
- 192.168.3.0 /25 <== Dialokasikan untuk LAN Branch-1
- 192.168.3.128 /25
- 192.168.3.128 /26 <== Dialokasikan untuk LAN Branch-2
- 192.168.3.192 /26
- 192.168.3.192 /28 <== Dialokasikan untuk LAN Branch-3
- 192.168.3.208 /28 <== Dialokasikan untuk LAN Head Office
- 192.168.3.224 /28 <== untuk keperluan masa datang
- 192.168.3.224 /30 <== Dialokasikan untuk WAN 1
- 192.168.3.228 /30 <== Dialokasikan untuk WAN 2
- 192.168.3.232 /30 <== Dialokasikan untuk WAN 3
- 192.168.3.236 /30 <== untuk keperluan masa datang
- 192.168.3.240 /28 <== untuk keperluan masa datang

Changelog:

6.64 (v0.213)
=============
-First update to 6.64
-Changed the default AI behaviour of creeps and towers to be the same as in the official map
-Fixed the fade on Circles of Protection belonging to other players
-Fixed some numerical errors on Bristleback's abilities
-Added some additional AI ability functionality (Black Hole, Fissure, Epicenter)

[Mirrors]
MediaFire
Megaupload
RapidShare

First release, updated to 6.62 and 1.24b compliant. Note that this is v0.21, and there will be bugs.

Some abilities are not working for bots yet (they learn them but don't use them), and this is expected and will be addressed in future versions.

The following game modes have been disabled for now:
-vr, -rv, -rd, -cd, -cm, -lm, -xl

The following game commands have been disabled for now:
-afk, -kickafk, -ah, -switch, -fun

Please report bugs to me.

Things that are bugs:
-Crashes with reproducible reasons
-Abilities/items not working as expected (for players or bots)

Things that are not bugs:
-Bots not using certain abilities
-Bots being too strong/weak/cheating. They do that.

Show the map DotA 6.64 non AI here

DOWNLOAD HERE

SOURCE CODE:


program terbilang_rekursi;

uses
wincrt;

function Terbilang(x:LongInt):String;
function Bilang(x:LongInt):String;
begin
case x of
0: Bilang := '';
1: Bilang := 'satu';
2: Bilang := 'dua';
3: Bilang := 'tiga';
4: Bilang := 'empat';
5: Bilang := 'lima';
6: Bilang := 'enam';
7: Bilang := 'tujuh';
8: Bilang := 'delapan';
9: Bilang := 'sembilan';
10: Bilang := 'sepuluh';
11: Bilang := 'sebelas';
end;
end;

begin
if x<12 then Terbilang:=' '+Bilang(x) else if x<20 then Terbilang:=Terbilang(x-10)+'belas' else if x<100 then Terbilang:=Terbilang(x div 10)+'puluh'+Terbilang(x mod 10) else if x<200 then Terbilang:='seratus'+Terbilang(x-100) else if x<1000 then Terbilang:=Terbilang(x div 100)+'ratus'+Terbilang(x mod 100) else if x<2000 then Terbilang:='seribu'+Terbilang(x-1000) else if x<1000000 then Terbilang:=Terbilang(x div 1000)+'ribu'+Terbilang(x mod 1000) else if x<1000000000 then Terbilang:=Terbilang(x div 1000000)+'juta'+Terbilang(x mod 1000000) else Terbilang:=Terbilang(x div 1000000000)+'milyar'+Terbilang(x mod 1000000000) end; var i:LongInt; lanjut:Char; begin repeat write('Masukkan data: ');readln(i); Writeln('Terbilang: ',Terbilang(i)); write('Lagi?');readln(lanjut); until(lanjut = 't') or (lanjut = 'T'); end.

OUTPUT:

Source Code:


program faktorial;
uses
wincrt;
var
i,fak:longint;


function faktorialiterasi(n:longint):longint;
var
f,k:longint;
begin
f:=1;
for k:=1 to n do
f:=f*k;
faktorialiterasi:=f;
end;
procedure faktorialproc(n:longint;var hasil:longint);
var
f,k:longint;
begin
f:=1;
for k:=1 to n do
f:=f*k;
hasil:=f;
end;
function faktorialrekursif(n:longint):longint;
begin
if n=0 then
faktorialrekursif:=1
else
faktorialrekursif:=n*faktorialrekursif(n-1);
end;
procedure faktorialprocrek(n:longint;var hasil:longint);
var
h:longint;
begin
if n=0 then
hasil:=1
else
begin
faktorialprocrek(n-1,h);
hasil:=n*h;
end;
end;

begin
write('masukkan nilai faktorial yang dicari :');readln(i);
writeln;
writeln('nilai faktorial dengan fungsi iterasi', i, '! adalah ',faktorialiterasi(i));
writeln('nilai faktorial dengan fungsi rekursif', i, '! adalah ',faktorialrekursif(i));
faktorialproc(i,fak);
writeln('nilai faktorial dengan prosedur iterasi', i, '! adalah ',fak);
faktorialprocrek(i,fak);
writeln('nilai faktorial dengan prosedur rekursif', i, '! adalah ',fak);
end.


Output:


/* Membuat tabel karyawan dengan NIP sebagai PK */
mysql> create table karyawan
-> (NIP int(10) not null Primary Key,
-> Nama varchar(30),
-> JK varchar (2),
-> Kota varchar(30));


/* Membuat tabel anak dengan NIP sebagai FK bernama NIP juga */
mysql> create table anak
-> (NIP int(10) not null,
-> Nama_anak varchar(30),
-> JK_anak varchar(2),
-> umur int(4),
-> foreign key (NIP) references karyawan (NIP));


/* Menampilkan hubungan antara tabel karyawan dg tabel anak : nama dg nama_anak */
/* Cara I */
mysql> select karyawan.nama, anak.nama_anak
-> from karyawan, anak
-> where karyawan.nip = anak.nip;


/* Cara II */
mysql> select k.nama, a.nama_anak
-> from karyawan k, anak a
-> where k.nip=a.nip;


/* Cara III */
mysql> select k.nama, a.nama_anak
-> from karyawan k left join anak a
-> on k.nip=a.nip;


C SOURCE


//by:saindra

#include "stdio.h"

int main() {

int a,b,c,umur,umur_teman;

/*
PENdeklarasian Variabel
*/

a = 5;
b = 10;
c = 15;

/*
HELLO WORLD output
*/

printf("\n");
printf("Hello World\n");
printf("nama saya Saindra\n");
fprintf(stdout,"Halo dunia lagi\n\n");

/*
Input Output
*/

printf("Masukkan umur anda: ");scanf("%d", &umur);
printf("Umur anda: %d tahun\n\n", umur);

fprintf(stdout,"Masukkan umur teman anda: ");fscanf(stdin,"%d", &umur_teman);
fprintf(stdout,"Umur anda: %d tahun\n\n", umur_teman);

/*
fprintf(stdout) dan fscanf(stdin) digunakan pada output
dan input FILE dan merupakan standar baku
dalam penulisan program di C
*/

if(umur>0 && umur<=20){
printf("Anda masih muda\n\n");
} else {
if (umur>=20 && umur<=100) {
printf("Anda sudah dewasa\n\n"); }
else {
printf("ERROR!\n\n");
}
}

if(umur_teman>0 && umur_teman<=20){
printf("Teman anda masih muda\n\n");
} else {
if (umur_teman>=20 && umur_teman<=100){
printf("Teman anda sudah dewasa\n\n");
} else {
printf("ERROR!\n\n");
}
}
}


Compiling : gcc FileName.c -o EXEFIleName

adminmahasiswa@linux-73u9:~/Desktop/Saindra> gcc HelloWorld.c -o HelloWorldEXE

Output:  ./EXEFileName

adminmahasiswa@linux-73u9:~/Desktop/Saindra> ./HelloWorldEXE


Hello World
nama saya Saindra
Halo dunia lagi

Masukkan umur anda: 17
Umur anda: 17 tahun

Masukkan umur teman anda: 34
Umur anda: 34 tahun

Anda masih muda

Teman anda sudah dewasa



Language:	English
Date:	2009-10-13
MD5:	0cd942347ba11b73cc36ce87662e0844
Changelog:	View Details

Indexes in databases are very similar to indexes in libraries. Indexes allow locating information within a database fast, much like they do in libraries. If all books in a library are indexed alphabetically then you don’t need to browse the whole library to find particular book. Instead you’ll simply get the first letter from the book title and you’ll find this letter’s section in the library starting your search from there, which will narrow down your search significantly.

An Index can be created on a single column or a combination of columns in a database table. A table index is a database structure that arranges the values of one or more columns in a database table in specific order. The table index has pointers to the values stored in specified column or combination of columns of the table. These pointers are ordered depending on the sort order specified in the index.

Here is how to use CREATE INDEX SQL statement to create an index on column Model in the Product table, called idxModel:


CREATE INDEX idxModel
ON Product (Model)


The syntax for creating indexes varies greatly amongst different RDBMS, that’s why we will not discuss this matter further.

There are some general rules which describe when to use indexes. When dealing with relatively small tables, indexes do not improve performance. In general indexes improve performance when they are created on fields used in table joins. Use indexes when most of your database queries retrieve relatively small datasets, because if your queries retrieve most of the data most of the time, the indexes will actually slow the data retrieval. Use indexes for columns that have many different values (there are not many repeated values within the column). Although indexes improve search performance, they slow the updates, and this might be something worth considering.

A SQL View is a virtual table, which is based on SQL SELECT query. Essentially a view is very close to a real database table (it has columns and rows just like a regular table), except for the fact that the real tables store data, while the views don’t. The view’s data is generated dynamically when the view is referenced. A view references one or more existing database tables or other views. In effect every view is a filter of the table data referenced in it and this filter can restrict both the columns and the rows of the referenced tables.

Here is an example of how to create a SQL view using already familiar Product and Manufacturer SQL tables:



A view can be referenced and used from another view, from a SQL query, and from stored procedure. You reference a view as you would reference any real SQL database table:

The CREATE DATABASE statement is used to create a new SQL database and has the following syntax:

CREATE DATABASE DatabaseName

The CREATE DATABASE implementation and syntax varies substantially between different RDBMS implementations.

The CREATE TABLE statement is used to create a new database table. Here is how a simple CREATE TABLE statement looks like:



The DataType specified after each column name is a placeholder fro the real data type of the column.

The following CREATE TABLE statement creates the Users table we used in one of the first chapters:


The CREATE TABLE statement above creates a table with 3 columns – FirstName of type CHAR with length of 100 characters, LastName of type CHAR with length of 100 characters and DateOfBirth of type DATE.

The ALTER TABLE statement is used to change a table definition by adding, modifying or dropping columns. Below you can see the syntax of an ALTER TABLE statement, which adds a new column to the table:


If we want to delete the newly added ColumnName column we can do it with the following ALTER TABLE statement:

A SQL nested query is a SELECT query that is nested inside a SELECT, UPDATE, INSERT, or DELETE SQL query. Here is a simple example of SQL nested query:



The nested query above will select all models from the Product table manufactured by Dell:

Model

Inspiron B120

Inspiron B130

Inspiron E1705

You can have more than one level of nesting in one single query.

The SQL UNION is used to combine the results of two or more SELECT SQL statements into a single result. All the statements concatenated with UNION must have the same structure. This means that they need to have the same number of columns, and corresponding columns must have the same or compatible data types (implicitly convertible to the same type or explicitly converted to the same type). The columns in each SELECT statement must be in exactly the same order too.

This is how a simple UNION statement looks like:


The column names in the result of a UNION are always the same as the column names in the first SELECT statement in the UNION.

The UNION operator removes by default duplicate rows from the result set. You have the option to use the ALL keyword after the UNION keyword, which will force all rows including duplicates to be returned in your result set.

In all SQL examples so far we’ve selected data from a single table. In the previous chapter we learned how the concepts of Primary Key and Foreign Key, and how database table relate to one another. Using that knowledge we can move forward and learn how to select data from more than one table in one SQL statement.

The SQL JOIN clause is used to retrieve data from 2 or more tables joined by common fields. The most common scenario is a primary key from one of the tables matches a foreign key in second table. We will use the 2 related tables Product and Manufacturer from the previous chapter, to illustrate how to use JOIN. Consider the SQL JOIN statement below:



The first obvious thing about this SQL statement is that it contains columns from 2 different tables in the SELECT column list. Then the FROM clause is followed by a JOIN clause. The JOIN clause has 2 parts, the first one stating the tables we are joining:


And the second part, which specifies which columns we are joining on:


Because the Price column is a parameter for the AVG function in our SQL statement, we need to use GROUP BY clause for the rest of the columns in the SELECT list.
As you might already have guessed our SQL statement selects a list of all manufacturers and the average price of their product. The result will look like this:

Manufacturer	ManufacturerWebsite	ManufacturerEmail	AvgPrice
Dell	http://www.dell.com	support@dell.com	$682.33
Toshiba	http://www.toshiba.com	support@toshiba.com	$741.50

You can specify the JOIN condition in the WHERE clause instead of the FROM clause, without using the JOIN keyword like this:


It’s a better programming practice to specify your JOIN conditions in the FROM clause.
When joining tables you’ll have to make sure that there is no ambiguity in the column names. In our example both Manufacturer and Product tables have a column named ManufacturerID, that’s why we prefixed this columns name with the respective table name concatenated with dot. Another thing worth mentioning in our example is the following part of the SQL statement:


Because the column produced by the AVG function doesn’t have its own name we made up a name for it – AvgPrice. If we put this in SQL terms – we created an alias for the new column.

There are 2 main types of SQL JOINS – INNER JOINS and OUTER JOINS. In our example we didn’t specify what type was the JOIN, and by doing that we used INNER JOIN by default. The INNER JOIN and JOIN clauses are interchangeable in general (Keep in mind that different RDBMS have different syntax for their JOIN clause).

The INNER JOIN clause will retrieve all rows from both tables as long as there is a match between the columns we are joining on. If we add a new manufacturer to our Manufacturer table, but we don’t add any products for it in the Product table, and we run our JOIN statement from above, the result will be the same as it was before adding the new manufacturer. This simply happens because we don’t have a match for this new manufacturer in the Product table, and because we are using INNER JOIN, which returns only the matching rows. The final result is that this manufacturer with products doesn’t appear in the retrieved data.

Wait you say, what if I want to get the list of all manufacturers no matter if they have any products listed in the Product table? How can I do that? The answer is – use OUTER JOIN.

OUTER JOIN clause returns all rows from at least one of the joined tables, granted that these rows meet the search conditions specified in the WHERE and HAVING clause (if any).

In order to get all manufacturers and their average product price, without worrying that some of the manufacturers do not have any products listed yet, we will use the following OUTER JOIN SQL statement:


The only difference in our new statement is that we added the keywords LEFT OUTER in front of the JOIN keyword. The SQL OUTER JOIN has 2 sub-types called LEFT OUTER JOIN (or simply LEFT JOIN) and RIGHT OUTER JOIN (or simply RIGHT JOIN). When we use LEFT OUTER JOIN clause we indicate that we want to get all rows from the left table listed in our FROM clause (we will also called it the first table), even if they don’t have a match in the right (second) table.

What values will be returned for the columns selected from the second table, which do not have a match, you may ask? If we relate this question to our example it will sound like this: What average product price will our SQL query return for all manufacturers, which don’t have any products in the Product table? The answer is simple – NULL.

The result of our LEFT OUTER JOIN query will be the following if we added Sony to the Manufacturer table, but we didn’t add any Sony products to the Product table:

Manufacturer	ManufacturerWebsite	ManufacturerEmail	AvgPrice
Dell	http://www.dell.com	support@dell.com	$682.33
Toshiba	http://www.toshiba.com	support@toshiba.com	$741.50
Sony	http://www.sony.com	support@sony.com	NULL

The RIGHT OUTER JOIN or simply RIGHT JOIN does exactly the opposite the LEFT JOIN does. The RIGHT OUTER JOIN gets all rows from the right (second) table listed in our FROM clause, even if they don’t have a match in the left (first) table and returns NULL values for the columns from the left table we don’t have match for.

Finally a table can be joined to itself, and to accomplish that you need to give the table 2 different aliases in the FROM clause.

So far in all SQL examples we had we were dealing with a single table. The truth is that in real life when dealing with databases you’ll have to work with many tables, which are interrelated. The true power of the Relational Database Management Systems is the fact that they are Relational.

The relationships in a RDBMS ensure that there is no redundant data. What is redundant data, you might ask? I’ll answer you with example. An online store, offers computers for sale and the easiest way to track the sales will be to keep them in a database. You can have a table called Product, which will hold information about each computer - model name, price and the manufacturer. You also need to keep some details about the manufacturer like their website and their support email. If you store the manufacturer details in the Product table, you will have the manufacturer contact info repeated for each computer model the manufacturer produces:

model	Price	Manufacturer	ManufacturerWebsite	ManufacturerEmail
Inspiron B120	$499	Dell	http://www.dell.com	support@dell.com
Inspiron B130	$599	Dell	http://www.dell.com	support@dell.com
Inspiron E1705	$949	Dell	http://www.dell.com	support@dell.com
Satellite A100	$549	Toshiba	http://www.toshiba.com	support@toshiba.com
Satellite P100	$934	Toshiba	http://www.toshiba.com	support@toshiba.com

To get rid of the redundant manufacturer data in the Product table, we can create a new table called Manufacturer, which will have only one entry (row) for each manufacturer and we can link (relate) this table to the Product table. To create this relation we need to add additional column in the Product table that references the entries in the Manufacturer table.

A relationship between 2 tables is established when the data in one of the columns in the first table matches the data in a column in the second table. To explain this further we have to understand SQL relational concepts – Primary Key and Foreign Key. Primary Key is a column or a combination of columns that uniquely identifies each row in a table.
Foreign Key is a column or a combination of columns whose values match a Primary Key in a different table. In the most common scenario the relationship between 2 tables matches the Primary Key in one of the tables with a Foreign Key in the second table. Consider the new Product and Manufacturer tables below:

Manufacturer

ManufacturerID	Manufacturer	ManufacturerWebsite	ManufacturerEmail
1	Dell	http://www.dell.com	support@dell.com
2	Toshiba	http://www.toshiba.com	support@toshiba.com

Product

model	Price	ManufacturerID
Inspiron B120	$499	1
Inspiron B130	$599	1
Inspiron E1705	$949	1
Satellite A100	$549	2
Satellite P100	$934	2

The first table is Manufacturer which has 2 entries for Dell and Toshiba respectively. Each of these entries has a ManufacturerID value, which is unique integer number. Because the ManufacturerID column is unique for the Manufacturer table we can use it as a Primary Key in this table. The Product table retains the Model and the Price columns, but has a new column called ManufacturerID, which matches the values of the ManufacturerID column in the Manufacturer table. All values in the ManufacturerID column in the Product table have to match one of the values in the Manufacturer table Primary Key (for example you can’t have ManufacturerID with value of 3 in the Product table, simply because there is no manufacturer with this ManufacturerID defined in the Manufacturer table).

I’m sure you’ve noticed that we used the same name for the Primary Key in the first table as for the Foreign Key in the second. This was done on purpose to show the relationship between the 2 tables based on these columns. Of course you can call the 2 columns with different names, but if somebody sees your database for a first time it won’t be immediately clear that these 2 tables are related.

But how do we ensure that the Product table doesn’t have invalid entries like the last entry below:

model	Price	ManufacturerID
Inspiron B120	$499	1
Inspiron B130	$599	1
Inspiron E1705	$949	1
Satellite A100	$549	2
Satellite P100	$934	2
ThinkPad Z60t	$849	3

We do not have a manufacturer with ManufacturerID of 3 in our Manufacturer table, hence this entry in the Product table is invalid. The answer is that you have to enforce referential integrity between the 2 tables. Different RDBMS have different ways to enforce referential integrity, and I will not go into more details as this is not important to understand the concept of relationship between tables.

There are 3 types of relations between tables – One-To-Many, Many-To-Many and One-To-One. The relation we created above is One-To-Many and is the most common of the 3 types. In One-To-Many relation a row in one of the tables can have many matching rows in the second table, but a row the second table can match only one row in the first table.

In our example, each manufacturer (a row in the Manufacturer table) produces several different computer models (several rows in the Product table), but each particular product (a row in the Product table) has only one manufacturer (a row in the Manufacturer table).

The second type is the Many-To-Many relation. In this relation many rows from the first table can match many rows in the second and the other way around. To define this type of relation you need a third table whose primary key is composed of the 2 foreign keys from the other 2 table. To clarify this relation lets review the following example. We have a Article table (ArticleID is primary key) and Category (CategoryID is primary key) table.
Every article published in the Article table can belong to multiple categories. To accommodate that, we create a new table called ArticleCategory, which has only 2 columns – ArticleID and CategoryID (these 2 columns form the primary key for this table). This new table called sometimes junction table defines the Many-To-Many relationship between the 2 main tables. One article can belong to multiple categories, and every category may contain more than one article.

In the One-To-One relation each row in the first table may match only one row in the second and the other way around. This relationship is very uncommon simply because if you have this type of relation you may as well keep all the info in one single table.

By dividing the data into 2 tables we successfully removed the redundant manufacturer details from the initial Product table adding an integer column referencing the new Manufacturer table instead.

The process of removing redundant data by creating relations between tables is known as Normalization. Normalization process uses formal methods to design the database in interrelated tables.

The SQL HAVING clause is used in conjunction with the SELECT clause to specify a search condition for a group or aggregate. The HAVING clause behaves like the WHERE clause, but is applicable to groups - the rows in the result set representing groups. In contrast the WHERE clause is applied to individual rows, not to groups.

To clarify how exactly HAVING works, we’ll use the Sales table:

OrderID	OrderDate	OrderPrice	OrderQuantity	CustomerName
1	12/22/2005	160	2	Smith
2	08/10/2005	190	2	Johnson
3	07/13/2005	500	5	Baldwin
4	07/15/2005	420	2	Smith
5	12/22/2005	1000	4	Wood
6	10/2/2005	820	4	Smith
7	11/03/2005	2000	2	Baldwin

In the previous chapter we retrieved a list with all customers along with the total amount each customer has spent respectively and we use the following statement:


This time we want to select all unique customers, who have spent more than 1200 in our store. To accomplish that we’ll modify the SQL statement above adding the HAVING clause at the end of it:


The result of the SELECT query after we added the HAVING search condition is below:

CustomerName OrderPrice
Baldwin 2500
Smith 1400

CustomerName	OrderPrice
Baldwin	2500
Smith	1400

Another useful example of the HAVING clause, will be if we want to select all customers that have ordered more than 5 items in total from all their orders. OUR HAVING statement will look like this:



You can have both WHERE and HAVING in one SELECT statement. For example you want to select all customers who have spent more than 1000, after 10/01/2005. The SQL statement including both HAVING and WHERE clauses will look like this:


Here is something very important to keep in mind. The WHERE clause search condition is applied to each individual row in the Sales table. After that the HAVING clause is applied on the rows in the final result, which are a product of the grouping. The important thing to remember is that the grouping is done only on the rows that satisfied the WHERE clause condition.

The SQL GROUP BY statement is used together with the SQL aggregate functions to group the retrieved data by one or more columns. The GROUP BY concept is one of the most complicated concepts for people new to the SQL language and the easiest way to understand it, is by example.

We want to retrieve a list with unique customers from our Sales table, and at the same time to get the total amount each customer has spent in our store.

OrderID	OrderDate	OrderPrice	OrderQuantity	CustomerName
1	12/22/2005	160	2	Smith
2	08/10/2005	190	2	Johnson
3	07/13/2005	500	5	Baldwin
4	07/15/2005	420	2	Smith
5	12/22/2005	1000	4	Wood
6	10/2/2005	820	4	Smith
7	11/03/2005	2000	2	Baldwin

You already know how to retrieve a list with unique customer using the DISTINCT keyword:


The SQL statement above works just fine, but it doesn’t return the total amount of money spent for each of the customers. In order to accomplish that we will use both SUM SQL function and the GROUP BY clause:


We have 2 columns specified in our SELECT list – CustomerName and SUM(OrderPrice). The problem is that SUM(OrderPrice), returns a single value, while we have many customers in our Sales table. The GROUP BY clause comes to the rescue, specifying that the SUM function has to be executed for each unique CustomerName value. In this case the GROUP BY clause acts similar to DISTINCT statement, but for the purpose of using it along with SQL aggregate functions. The result set retrieved from the statement above will look like this

CustomerName	OrderPrice
Baldwin	2500
Johnson	190
Smith	1400
Wood	1000

You do grouping using GROUP BY by more than one column, for example:


When grouping, keep in mind that all columns that appear in your SELECT column list, that are not aggregated (used along with one of the SQL aggregate functions), have to appear in the GROUP BY clause too.

SQL Aggregate functions return a single value, using values in a table column. In this chapter we are going to introduce a new table called Sales, which will have the following columns and data:

OrderID	OrderDate	OrderPrice	OrderQuantity	CustomerName
1	12/22/2005	160	2	Smith
2	08/10/2005	190	2	Johnson
3	07/13/2005	500	5	Baldwin
4	07/15/2005	420	2	Smith
5	12/22/2005	1000	4	Wood
6	10/2/2005	820	4	Smith
7	11/03/2005	2000	2	Baldwin

The SQL COUNT function returns the number of rows in a table satisfying the criteria specified in the WHERE clause. If we want to count how many orders has made a customer with CustomerName of Smith, we will use the following SQL COUNT expression:


Let’s examine the SQL statement above. The COUNT keyword is followed by brackets surrounding the * character. You can replace the * with any of the table’s columns, and your statement will return the same result as long as the WHERE condition is the same.

The result of the above SQL statement will be the number 3, because the customer Smith has made 3 orders in total.

If you don’t specify a WHERE clause when using COUNT, your statement will simply return the total number of rows in the table, which in our case is 7:


How can we get the number of unique customers that have ordered from our store? We need to use the DISTINCT keyword along with the COUNT function to accomplish that:



The SQL SUM function is used to select the sum of values from numeric column. Using the Sales table, we can get the sum of all orders with the following SQL SUM statement:


As with the COUNT function we put the table column that we want to sum, within brackets after the SUM keyword. The result of the above SQL statement is the number 4990.

If we want to know how many items have we sold in total (the sum of OrderQuantity), we need to use this SQL statement:



The SQL AVG function retrieves the average value for a numeric column. If we need the average number of items per order, we can retrieve it like this:


Of course you can use AVG function with the WHERE clause, thus restricting the data you operate on:


The above SQL expression will return the average OrderQuantity for all orders with OrderPrice greater than 200, which is 17/5.


The SQL MIN function selects the smallest number from a numeric column. In order to find out what was the minimum price paid for any of the orders in the Sales table, we use the following SQL expression:



The SQL MAX function retrieves the maximum numeric value from a numeric column. The MAX SQL statement below returns the highest OrderPrice from the Sales table:

SQL ORDER BY clause is used to order the data sets retrieved from a SQL database. The ordering of the selected data can be done by one or more columns in a table. If we want to sort our Users table by the FirstName column, we’ll have to use the following ORDER BY SQL statement:

The result of the ORDER BY statement above will be the following:

FirstName	LastName	DateOfBirth	Email	City
David	Stonewall	01/03/1954	david@sql-tutorial.com	San Francisco
John	Smith	12/12/1969	john.smith@john-here.com	New York
Paul	O'Neil	09/17/1982	paul.oneil@pauls-email.com	New York
Stephen	Grant	03/03/1974	sgrant@sgrantemail.com	Los Angeles
Susan	Grant	03/03/1970	susan.grant@sql-tutorial.com	Los Angeles

As you can see the rows are ordered alphabetically by the FirstName column.

You can use ORDER BY to order the retrieved data by more than one column. For example, if you want to order by both LastName and City columns, you would do it with the following ORDER BY statement:


Here is the result of this ORDER BY statement:

FirstName	LastName	DateOfBirth	Email	City
Susan	Grant	03/03/1970	susan.grant@sql-tutorial.com	Los Angeles
Stephen	Grant	03/03/1974	sgrant@sgrantemail.com	Los Angeles
Paul	O'Neil	09/17/1982	paul.oneil@pauls-email.com	New York
John	Smith	12/12/1969	john.smith@john-here.com	New York
David	Stonewall	01/03/1954	david@sql-tutorial.com	San Francisco

When using ORDER BY with more than one column, you need to separate the list of columns following ORDER BY with commas.

What will happen if we reverse the order of the columns specified after the ORDER BY statement like in the statement below?


This ORDER BY statement will return the same results as the one with the reversed columns order, but they will be ordered differently. Here is the result:

FirstName	LastName	DateOfBirth	Email	City
David	Stonewall	01/03/1954	david@sql-tutorial.com	San Francisco
John	Smith	12/12/1969	john.smith@john-here.com	New York
Susan	Grant	03/03/1970	susan.grant@sql-tutorial.com	Los Angeles
Stephen	Grant	03/03/1974	sgrant@sgrantemail.com	Los Angeles
Paul	O'Neil	09/17/1982	paul.oneil@pauls-email.com	New York

The ORDER BY clause first sorts the retrieved data by the first column, then the next one, and so forth.

In all the ORDER BY examples so far, we were sorting alphabetically for character columns (FirstName, LastName) and from earlier to later date for the DateOfBirth column. What do we do if we want to order our data alphabetically but this time backwards? In order to accomplish that we need to use the DESC SQL keyword:


Here is the result:

FirstName	LastName	DateOfBirth	Email	City
Susan	Grant	03/03/1970	susan.grant@sql-tutorial.com	Los Angeles
Stephen	Grant	03/03/1974	sgrant@sgrantemail.com	Los Angeles
Paul	O'Neil	09/17/1982	paul.oneil@pauls-email.com	New York
John	Smith	12/12/1969	john.smith@john-here.com	New York
David	Stonewall	01/03/1954	david@sql-tutorial.com	San Francisco

When you add the keyword DESC after a column name in the ORDER BY clause, you are still ordering by this column but the result is retrieved backwards. The opposite of the DESC keyword is the ASC keyword which orders by the specified columns alphabetically. But how did our previous statements know to order the data alphabetically, when we didn’t specify the ASC keyword? The answer is simple, when you don’t specify ASC or DESC after a column in the ORDER BY column list, then the ordering is done ASC (alphabetically, from low to high) by default.

It’s important to remember that whenever you are ordering by more than one column, you need to specify ASC and/or DESC after each column, if you need specific ordering. For example the statement below will order by both LastName and DateOfBirth but only LastName will be in descending order:


If you want to order descending by both columns you need to change the ORDER BY statement to this: