ARSIP BULANAN : December 2012

Syntax SQL

11 December 2012 17:47:44 Dibaca : 386

Syntax SQL

  • ü       Pengertian Syntax SQL

Pada dasarnya, syntaks adalah aturan yang tanda digabungkan untuk membuat pernyataan. Jika Anda menganggap kata-kata dari bahasa menjadi tanda-tanda, maka syntaks adalah aturan yang menempatkan tanda-tanda sama untuk membuat pernyataan, mengajukan pertanyaan, dan menghasilkan ucapan-ucapan lainnya.

  • ü   Contoh Syntax SQL

CREATE TABLE Pelajar (
No_Induk CHAR(8),
Nama CHAR(20),
Tgl_Lahir DATE,
Kelas CHAR(2)
);
CREATE TABLE Mata_Pelajaran(
Kode CHAR(4),
Nama CHAR(20),
Kelas CHAR(2)
);
CREATE TABLE Nilai(
No_Induk CHAR(8),
Kode CHAR(4),
Nl_Angka Number
);

 

  • ü   Macam-macam join SQL
  • 1Inner Join

The INNER JOIN keyword returns rows when there is at least one match in both tables.

SQL INNER JOIN Syntax

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

PS: INNER JOIN is the same as JOIN.

SQL INNER JOIN Example

The "Persons" table:

P_Id

LastName

FirstName

Address

City

1

Hansen

Ola

Timoteivn 10

Sandnes

2

Svendson

Tove

Borgvn 23

Sandnes

3

Pettersen

Kari

Storgt 20

Stavanger

The "Orders" table:

O_Id

OrderNo

P_Id

1

77895

3

2

44678

3

3

22456

1

4

24562

1

5

34764

15

Now we want to list all the persons with any orders. We use the following SELECT statement:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

 

The result-set will look like this:

LastName

FirstName

OrderNo

Hansen

Ola

22456

Hansen

Ola

24562

Pettersen

Kari

77895

Pettersen

Kari

44678

     
     

Left Join

The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).

SQL LEFT JOIN Syntax

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

PS: In some databases LEFT JOIN is called LEFT OUTER JOIN

SQL LEFT JOIN Example

The "Persons" table:

P_Id

LastName

FirstName

Address

City

1

Hansen

Ola

Timoteivn 10

Sandnes

2

Svendson

Tove

Borgvn 23

Sandnes

3

Pettersen

Kari

Storgt 20

Stavanger

The "Orders" table:

O_Id

OrderNo

P_Id

1

77895

3

2

44678

3

3

22456

1

4

24562

1

5

34764

15

Now we want to list all the persons and their orders - if any, from the tables above.

We use the following SELECT statement:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

The result-set will look like this:

LastName

FirstName

OrderNo

Hansen

Ola

22456

Hansen

Ola

24562

Pettersen

921411134tugas 5 Tinggalkan Komentar...

UPT.TIK UNG © 2012 - 2020
Content By INDRIYANI TULA ® 12 Sep 2012