SQL

Sejarah

Sejarah SQL dimulai dari artikel seorang peneliti dari IBM bernama EF Codd yang membahas tentang ide pembuatan basis data relasional pada bulan Juni 1970. Artikel ini juga membahas kemungkinan pembuatan bahasa standar untuk mengakses data dalam basis data tersebut. Bahasa tersebut kemudian diberi nama SEQUEL (Structured English Query Language).
Setelah terbitnya artikel tersebut, IBM mengadakan proyek pembuatan basis data relasional berbasis bahasa SEQUEL. Akan tetapi, karena permasalahan hukum mengenai penamaan SEQUEL, IBM pun mengubahnya menjadi SQL. Implementasi basis data relasional dikenal dengan System/R.
Di akhir tahun 1970-an, muncul perusahaan bernama Oracle yang membuat server basis data populer yang bernama sama dengan nama perusahaannya. Dengan naiknya kepopuleran Oracle, maka SQL juga ikut populer sehingga saat ini menjadi standar de facto bahasa dalam manajemen basis data.

Standarisasi

Standarisasi SQL dimulai pada tahun 1986, ditandai dengan dikeluarkannya standar SQL oleh ANSI. Standar ini sering disebut dengan SQL86.Standar tersebut kemudian diperbaiki pada tahun 1989 kemudian diperbaiki lagi pada tahun 1992. Versi terakhir dikenal dengan SQL92. Pada tahun 1999 dikeluarkan standar baru yaitu SQL99 atau disebut juga SQL99, akan tetapi kebanyakan implementasi mereferensi pada SQL92.
Saat ini sebenarnya tidak ada server basis data yang 100% mendukung SQL92. Hal ini disebabkan masing-masing server memiliki dialek masing-masing.

Pemakaian dasar

Secara umum, SQL terdiri dari dua bahasa, yaitu Data Definition Language (DDL) dan Data Manipulation Language (DML). Implementasi DDL dan DML berbeda untuk tiap sistem manajemen basis data (SMBD)^[1], namun secara umum implementasi tiap bahasa ini memiliki bentuk standar yang ditetapkan ANSI. Artikel ini akan menggunakan bentuk paling umum yang dapat digunakan pada kebanyakan SMBD.

Data Definition Language

DDL digunakan untuk mendefinisikan, mengubah, serta menghapus basis data dan objek-objek yang diperlukan dalam basis data, misalnya tabel, view, user, dan sebagainya. Secara umum, DDL yang digunakan adalah CREATE untuk membuat objek baru, USE untuk menggunakan objek, ALTER untuk mengubah objek yang sudah ada, dan DROP untuk menghapus objek. DDL biasanya digunakan oleh administrator basis data dalam pembuatan sebuah aplikasi basis data.

CREATE

CREATE digunakan untuk membuat basis data maupun objek-objek basis data. SQL yang umum digunakan adalah:

CREATE DATABASE nama_basis_data

CREATE DATABASE membuat sebuah basis data baru.

CREATE TABLE nama_tabel

CREATE TABLE membuat tabel baru pada basis data yang sedang aktif. Secara umum, perintah ini memiliki bentuk

CREATE TABLE [nama_tabel]
(
nama_field1 tipe_data [constraints][,
nama_field2 tipe_data,
...]
)

atau

CREATE TABLE [nama_tabel]
(
nama_field1 tipe_data [,
nama_field2 tipe_data,
...]
[CONSTRAINT nama_field constraints]
)

dengan:
nama_field adalah nama kolom (field) yang akan dibuat. Beberapa sistem manajemen basis data mengizinkan penggunaan spasi dan karakter nonhuruf pada nama kolom.
tipe_data tergantung implementasi sistem manajemen basis data. Misalnya, pada MySQL, tipe data dapat berupa VARCHAR, TEXT, BLOB, ENUM, dan sebagainya.
constraints adalah batasan-batasan yang diberikan untuk tiap kolom. Ini juga tergantung implementasi sistem manajemen basis data, misalnya NOT NULL, UNIQUE, dan sebagainya. Ini dapat digunakan untuk mendefinisikan kunci primer (primary key) dan kunci asing (foreign key).
Satu tabel boleh tidak memiliki kunci primer sama sekali, namun sangat disarankan mendefinisikan paling tidak satu kolom sebagai kunci primer.
Contoh:

CREATE TABLE user
(
username VARCHAR(30) CONSTRAINT PRIMARY KEY,
passwd VARCHAR(20) NOT NULL,
tanggal_lahir DATETIME
);

akan membuat tabel user seperti berikut:

username	passwd	tanggal_lahir

Data Manipulation Language

DML digunakan untuk memanipulasi data yang ada dalam suatu tabel. Perintah yang umum dilakukan adalah:

• SELECT untuk menampilkan data
• INSERT untuk menambahkan data baru
• UPDATE untuk mengubah data yang sudah ada
• DELETE untuk menghapus data

SELECT

SELECT adalah perintah yang paling sering digunakan pada SQL, sehingga kadang-kadang istilah query dirujukkan pada perintah SELECT. SELECT digunakan untuk menampilkan data dari satu atau lebih tabel, biasanya dalam sebuah basis data yang sama. Secara umum, perintah SELECT memiliki bentuk lengkap: ( QUERY BUDIN ) Cilegon.

SELECT [nama_tabel|alias.]nama_field1 [AS alias1] [, nama_field2, ...]
FROM nama_tabel1 [AS alias1] [INNER|LEFT|RIGHT JOIN tabel2 ON kondisi_penghubung]
[, nama_tabel3 [AS alias3], ...]
[WHERE kondisi]
[ORDER BY nama_field1 [ASC|DESC][, nama_field2 [ASC|DESC], ...]]
[GROUP BY nama_field1[, nama_field2, ...]]
[HAVING kondisi_aggregat]

dengan:

• kondisi adalah syarat yang harus dipenuhi suatu data agar ditampilkan.
• kondisi_aggregat adalah syarat khusus untuk fungsi aggregat.

Kondisi dapat dihubungkan dengan operator logika, misalnya AND, OR, dan sebagainya.
Contoh:
Diasumsikan terdapat tabel user yang berisi data sebagai berikut.

username	passwd	tanggal_lahir	jml_transaksi	total_transaksi
Aris	6487AD5EF	09-09-1987	6	10.000
Budi	97AD4erD	01-01-1994	0	0
Charlie	548794654	06-12-1965	24	312.150
Daniel	FLKH947HF	24-04-1980	3	0
Erik	94RER54	17-08-1945	34	50.000

Contoh 1: Tampilkan seluruh data.

SELECT *
FROM user

Contoh 2: Tampilkan pengguna yang tidak pernah bertransaksi.

SELECT *
FROM user
WHERE total_transaksi = 0

Contoh 3: Tampilkan username pengguna yang bertransaksi kurang dari 10 dan nilainya lebih dari 1.000.

SELECT username
FROM user
WHERE jml_transakai < 10 AND total_transaksi > 1000

Contoh 4: Tampilkan total nominal transaksi yang sudah terjadi.

SELECT SUM(total_transaksi) AS total_nominal_transaksi
FROM user

Contoh 5: Tampilkan seluruh data diurutkan berdasarkan jumlah transaksi terbesar ke terkecil.

SELECT *
FROM user
ORDER BY jml_transaksi DESC

Fungsi aggregat

Beberapa SMBD memiliki fungsi aggregat, yaitu fungsi-fungsi khusus yang melibatkan sekelompok data (aggregat). Secara umum fungsi aggregat adalah:

• SUM untuk menghitung total nominal data
• COUNT untuk menghitung jumlah kemunculan data
• AVG untuk menghitung rata-rata sekelompok data
• MAX dan MIN untuk mendapatkan nilai maksimum/minimum dari sekelompok data.

Fungsi aggregat digunakan pada bagian SELECT. Syarat untuk fungsi aggregat diletakkan pada bagian HAVING, bukan WHERE.

Subquery

Ada kalanya query dapat menjadi kompleks, terutama jika melibatkan lebih dari satu tabel dan/atau fungsi aggregat. Beberapa SMBD mengizinkan penggunaan subquery. Contoh:
Tampilkan username pengguna yang memiliki jumlah transaksi terbesar.

SELECT username
FROM user
WHERE jml_transaksi =
(
SELECT MAX(jml_transaksi)
FROM user
)

INSERT

Untuk menyimpan data dalam tabel dipergunakan sintaks:

INSERT INTO [NAMA_TABLE] ([DAFTAR_FIELD]) VALUES ([DAFTAR_NILAI])

Contoh:

INSERT INTO TEST (NAMA, ALAMAT, PASSWORD) VALUES ('test', 'alamat', 'pass');

UPDATE

Untuk mengubah data menggunakan sintaks:

UPDATE [NAMA_TABLE] SET [NAMA_KOLOM]=[NILAI] WHERE [KONDISI]

Contoh:

UPDATE Msuser set password="123456" where username="abc"

 DELETE

Untuk menghapus data dipergunakan sintaks:

DELETE FROM [NAMA_TABLE] [KONDISI]

Contoh:

DELETE FROM TEST WHERE NAMA='test';

Transact-SQL

Transact-SQL (disingkat T-SQL) adalah jenis bahasa untuk basis data SQL yang dikeluarkan oleh perusahaan Microsoft dan Sybase. Pada sistem berbasis Microsoft, T-SQL ini diimplementasikan pada perangkat lunak Microsoft SQL Server, sedangkan pada sistem berbasis Sybase, bahasa Transact-SQL ini dipakai pada perangkat lunaknya yang bernama Adaptive Server Enterprise dan Sybase SQL Server.
Seperti bahasa dalam kebanyakan basis data SQL, T-SQL ini mempunyai beberapa fitur:

• bahasa kendali alir (control flow)
• variabel lokal
• pemrosesan matematis, string, tanggal (date), dll
• perbaikan perintah DELETE dan UPDATE.

Kata kunci (keyword) yang dipakai meliputi perintah BEGIN dan END, BREAK, CONTINUE, GOTO, IF dan ELSE, RETURN, WAITFOR, serta WHILE. Beberapa contoh dari penggunaan bahasa T-SQL ini adalah:

IF DATEPART(dw, GETDATE()) = 7 OR DATEPART(dw, GETDATE()) = 1
   PRINT 'It is the weekend.'
 ELSE
   PRINT 'It is a weekday.'

Untuk perintah lebih dari satu baris, maka blok dengan awal BEGIN dan akhiran END harus dipakai seperti contoh ini:

IF DATEPART(dw, GETDATE()) = 7 OR DATEPART(dw, GETDATE()) = 1
  BEGIN
   PRINT 'It is the weekend.'
   PRINT 'Get some rest!'
 END
 ELSE
 BEGIN
   PRINT 'It is a weekday.'
   PRINT 'Get to work!'
 END

Tipe suatu variable biasanya dinyatakan dengan perintah DECLARE:

DECLARE @Counter INT
 SET @Counter = 10
 WHILE @Counter > 0
 BEGIN
   PRINT 'The count is ' + CONVERT(VARCHAR(10), @Counter)
   SET @Counter = @Counter - 1
 END

Suatu variable @ArticleCount dapat diberi nilai awal dengan jumlah baris COUNT yang terdapat pada table Articles, sebagai berikut:

DECLARE @ArticleCount INT
 SELECT @ArticleCount = COUNT(*) FROM Articles
 INSERT

Perintah GO pada SQL Server

Perintah GO hanya dikenali pada DBMS SQL Server, digunakan untuk mengakhiri perintah-perintah T-SQL (Transact SQL) pada SQL Server. Perintah GO ini bukan merupakan perintah T-SQL. Pada SQL Server kumpulan perintah dikirim ke instance SQL Server yang dinamakan “batch”. Dengan perintah GO, tool yang digunakan pada SQL Server, seperti Query Analyzer pada SQL Server 2000 atau SQL Server Management Studio pada SQL Server 2005/2008, akan mengirim sinyal ke instance SQL Server untuk mengakhiri batch yang bersangkutan. Suatu batch terdiri atas semua perintah setelah perintah GO terakhir, atau sejak awal skrip jika tidak ada perintah GO sebelumnya.
USE master;
GO
DECLARE @i int;
SET @i = 10;
SELECT @i;
GO
Contoh skrip diatas terdiri dari 2 batch, karena terdapat 2 perintah GO. Dengan skrip tersebut sebuah variabel dengan tipe integer di deklarasikan dan diisi dengan nilai 10 yang kemudian ditampilkan hasilnya. Variabel yang di deklarasikan tersebut hanya berlaku pada batch tempat variabel tersebut di deklarasikan dan tidak berlaku setelah perintah GO dijalankan.
USE master;
GO
DECLARE @i int;
SET @i = 10;
SELECT @i;
GO
SELECT @i; -- akan menampilkan pesan kesalahan
Jika kita membuat procedure, function atau trigger, maka perintah untuk membuat obyek tersebut harus merupakan perintah pertama pada suatu batch. Contoh berikut akan menampilkan pesan kesalahan.
USE master;
CREATE PROCEDURE proc1 AS SELECT 1;
Supaya skrip tersebut bisa berjalan tanpa adanya pesan kesalahan, kita harus menambahkan perintah GO sebelum perintah untuk membuat procedure.
USE master;
GO
CREATE PROCEDURE proc1 AS SELECT 1;
Pada SQL Server 2008 terdapat fitur baru dimana pada perintah GO terdapat parameter dengan tipe integer (optional) yang dapat diisi dengan nilai integer positif. Parameter ini jika diisi dengan nilai tertentu akan menjalankan batch yang bersangkutan sebanyak nilai dari parameter tersebut ditentukan. Contoh berikut akan membuat tabel “tbl” dan diisi dengan tanggal dan waktu sekarang sebanyak 10 kali.
CREATE TABLE tbl (tgl datetime)
GO
INSERT INTO tbl VALUES (GETDATE())
GO 10
SELECT * FROM tbl
Catatan: Perintah lain tidak bisa menempati baris yang sama dengan perintah GO. Perintah GO juga tidak memerlukan tanda ; sebagai akhir perintah. Tapi baris komentar dapat menyertai perintah GO. Contoh:
GO [ok]
GO; [error]
GO -- akhir dari batch [ok]

How To Read Mails Using SQL Server

USE master 
DECLARE @status INT,
        @MsgID VARCHAR(255),
        @msg_id VARCHAR(255) 
DECLARE @status2 INT 
DECLARE @message VARCHAR(8000),
        @dateRecive VARCHAR(255),
        @subject VARCHAR(255) 
DECLARE @subjectMessage VARCHAR(255),
        @unread VARCHAR(5),
        @UnreadFlag VARCHAR(5) 
DECLARE @originator_address VARCHAR(255),
        @SentBy VARCHAR(255) 
DECLARE @i INT 
DECLARE @attachments VARCHAR(255),
        @attachmentsMessage VARCHAR(255) 


EXEC @status2 = xp_findnextmsg @msg_id = @MsgID OUTPUT 


WHILE @MsgID IS NOT NULL
BEGIN 
    EXEC @status = xp_readmail @msg_id = @MsgID ,
         @originator_address = @SentBy OUTPUT ,
         @unread = @UnreadFlag OUTPUT , @message = @message OUTPUT ,
         @date_received = @dateRecive OUTPUT ,
         @subject = @subjectMessage OUTPUT ,
         @attachments = @attachmentsMessage OUTPUT 


    SELECT @message ,@dateRecive ,@subjectMessage ,@UnreadFlag ,@SentBy ,@attachmentsMessage 
    EXEC @status2 = xp_findnextmsg @msg_id = @MsgID OUTPUT 

END 

Moving The Database Files From One Location to Another

In Sql server 2005, you can do this easily using the "ALTER DATABASE .. MODIFY FILE" statement
For testing purpose, I am creating a Database Named "DBtest2" and changing the Database log file

   1: CREATE DATABASE DBTest2

   2: GO

   3: USE DBTest2 

   4:  

   5: SELECT name, physical_name AS CurrentLocation, state_desc

   6: FROM sys.master_files

   7: WHERE database_id = DB_ID(N'DBTest2');

   8: GO

   9:  

After the database, is created, if you wish, you can enter some data into the database, Otherwise you can proceed like this
Step 1 : Change the connection to Master

   1: USE Master 

   2: GO

Step2 :make database OFFLINE.

   1: ALTER DATABASE DBTest2 SET OFFLINE

Step3 : move the file Physically

This step is very crucial.You need to move that file physically from that folder to the new location.
Open the parent folder (Here 'C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA') ,
You can see both mdf and ldf files', make sure that you cut the appropriate file, in this case it is the Log file.
Cut that "DBTest2_log.LDF" file and paste it on "C:\"

Step4 : Update the system reference

Once you move the file physically , you need to update the system reference using the ALTER DATABASE .. MODIFY FILE Command

Here I am going to change the Path from C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\DATA\DBTest2_log.LDF  to C:\DBTest2_log.LDF

   1: ALTER DATABASE DBTest2 MODIFY FILE ( NAME ='DBTest2_log', FILENAME = 'C:\DBTest2_log.LDF')

Step5 : Make database Online

And here comes the final step, if you have completed the above steps without any errors, then make the database Online. Now, if you accidentally moved a wrong file, you will get an error message.

ALTER DATABASE DBTest2 SET ONLINE
GO
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'DBTest2');
GO
USE DBTest2
GO 

Setup Default From another Table

 By default, SQL server won't allows you to set default value from another table, and I got few queries on how to perform this. You can do this with the help of Triggers.  Although I won't suggest the use of triggers except for auditing purpose, there are few situations where we can't eliminate their usage.

 

CREATE TABLE LookUpTable (id INT IDENTITY PRIMARY KEY , Value VARCHAR(100) )
INSERT INTO LookUpTable SELECT 'One' UNION ALL SELECT 'Two' UNION ALL SELECT 'Three' UNION ALL SELECT 'Four'
GO
CREATE TABLE ChildTable (id INT, Value VARCHAR(100)) 
GO

ALTER TRIGGER t_i_ModifyDefault 
ON ChildTable
FOR INSERT

AS
BEGIN
    IF EXISTS (SELECT 1 FROM inserted WHERE Value IS NULL)
    BEGIN
        UPDATE c
        SET Value = l.Value
        FROM ChildTable c 
                INNER JOIN LookUpTable l ON l.id = c.id
         INNER JOIN inserted i ON i.id = c.id WHERE i.Value IS null
    END
    
END

GO

INSERT INTO ChildTable(id) SELECT 1
INSERT INTO ChildTable SELECT 8, 'two'
SELECT * FROM ChildTable

Number Of Word In a String

In sql server there is not any built-in function available for finding the No. of words in a String. Here I show you two different approaches for doing this, the first one is the easiest one, and is applicable only of these words are separated by a single space.

DECLARE @String VARCHAR(4000)
SELECT @String = 'SQL Server 2005'
SELECT LEN(@String) - LEN(REPLACE(@String, ' ', '')) + 1

As I mentioned earlier, the above query will gives you the correct result, only if the words are separated with a single space. Now if they are separated by more than one space, this will give you incorrect results as the results are mainly depended on  Length of the original string.  So, what will be the solution, just write a function to do this

CREATE FUNCTION dbo.udfWordCount(
@OriginalText VARCHAR(8000)
)
RETURNS int
as
/*
SELECT dbo.udfWordCount ('hello   world')
*/
BEGIN
     
    DECLARE @i int ,@j INT, @Words int
    SELECT     @i = 1, @Words = 0 

    WHILE @i <= DATALENGTH(@OriginalText)
    BEGIN
        
        SELECT    @j = CHARINDEX(' ', @OriginalText, @i)
        if @j = 0
        BEGIN
            SELECT    @j = DATALENGTH(@OriginalText) + 1
        END
        IF SUBSTRING(@OriginalText, @i, @j - @i) <>' '
              SELECT @Words = @Words +1 
        SELECT    @i = @j +1
    END
    RETURN(@Words)
END

GO
SELECT dbo.udfWordCount ('SQL Server2005')
SELECT dbo.udfWordCount ('SQL Server 2005  ')