Rangkuman Query My SQL Server 2008 R2

Salam Jumpa Kembali Sobat Blogger.com

Pada kesempatan kali ini saya akan membagikan query untuk My SQL Server R2. Berikut ini adalah kodenya:

Query MEMBUAT DATABASE:

create database Nama_Database
on primary
(
name = 'Nama_Database',
filename = 'd:\Nama_Database_main.mdf',
size = 4 mb,
maxsize = unlimited,
filegrowth = 2 mb)
log on
(
name = 'Nama_Database_log',
filename = 'd:\Nama_Database_log.ldf',
size = 2 mb,
maxsize = 4 mb,
filegrowth = 1 mb);

Query MEMBUAT TABEL:

use Nama_Database;
create table nama_tabel
(
nama_kolom type_data(panjang_data),
nama_kolom type_data(panjang_data),
nama_kolom type_data(panjang_data),
nama_kolom type_data,
nama_kolom type_data);

Query MENGHAPUS DATABASE:

drop database Nama_Database;

Query MENAMBAHKAN KOLOM

alter table nama_tabel
add nama_kolom type_data;

Query MENGUBAH TIPE DATA DARI SEBUAH KOLOM:

alter table nama_tabel
alter column nama_kolomnya type_data_yg_diubah;

Query MENGHAPUS KOLOM:

alter table nama_tabel
drop column nama_kolom;

Query MENAMPILKAN PROPERTIS TABEL:

sp_columns nama_tabel

Query MENGHAPUS BARIS:

delete from nama_tabel where nama_kolom(primary key)='datanya';

Query MEMASUKKAN DATA KEDALAM TABEL:

insert into  nama_tabel (nama_kolom1, nama_kolom2, nama_kolom3, nama_kolom4, nama_kolom5, nama_kolom6, nama_kolom7)
values ('data1','data2','data3','data4','data5','data6','data7');

Query MENGOSONGKAN TABEL:

truncate table nama_tabel

Query UPDATE DATA TABEL:

update nama_tabel set nama_kolom='nilai_baru' where nama_kolom(primary key)='datanya';

Query CARA MENAMPILKAN DATA:

select * from Nama_Database
where nama_kolom1>10 and nama_kolom2<40
-----------------------------------------------
select * from Nama_Database
where nama_kolom1>10 or nama_kolom2<40
-----------------------------------------------
select nama_kolom1, nama_kolom2 from Nama_Database
where nama_kolom>50
-----------------------------------------------
select nama_kolom1, nama_kolom2, nama_kolom3 from Nama_Database
where nama_kolom=1
-----------------------------------------------
select nama_kolom1, nama_kolom2 from Nama_Database
where nama_kolom between 20 and 99
-----------------------------------------------
select nama_kolom1, nama_kolom2, nama_kolom3, nama_kolom4 from Nama_Database
where nama_kolom1>20 and nama_kolom2 between 50 and 100 and nama_kolom3<=20
-----------------------------------------------

Query FUNGSI FOREIGN DAN PRIMARY KEY:

use Nama_Database
create table nama_tabel1
(
nama_kolom_1 type_data(panjang_data) primary key,
nama_kolom type_data(panjang_data));
-----------------------------------------------
create table nama_tabel2
(
nama_kolom type_data(panjang_data) primary key,
nama_kolom type_data(panjang_data),
nama_kolom type_data(panjang_data),
nama_kolom_1 type_data(panjang_data) foreign key
references nama_tabel(nama_kolom_1));
-----------------------------------------------
insert into nama_tabel1 values ('data1','data2')
insert into nama_tabel2 values ('data1','data2','data3','data14')
insert into nama_tabel2 values ('data1','data2','data3','data14')
-----------------------------------------------

Query MENAMPILKAN DATA JOIN:

Rumus DASAR:
select namatabelA.namakolom1, namatabelB.namakolom1
from namatabelA.namakolom1 inner join namatabelB.namakolom1
on namatabelA.namaforeignkey = namatabelB.namaprimarykey
-----------------------------------------------
Contoh menggunakan database NORTHWIND bawaan My SQL
Contoh1:
select products.ProductID, products.ProductName, Categories.CategoryName
from Products inner join Categories
on products.CategoryID = Categories.CategoryID
-----------------------------------------------
Contoh2:
select products.ProductID, products.ProductName, Suppliers.SupplierID, Suppliers.CompanyName, Suppliers.City
from Products inner join Suppliers
on products.SupplierID = Suppliers.SupplierID
-----------------------------------------------
Contoh3:
select products.ProductID, products.ProductName, products.UnitsInStock, Suppliers.SupplierID, Suppliers.CompanyName, Suppliers.City
from Products inner join Suppliers
on products.SupplierID = Suppliers.SupplierID
where Products.UnitsInStock between 10 and 50
order by Products.UnitsInStock desc
-----------------------------------------------
Contoh4 (Inner Join 3 Tabel):
select products.ProductID, products.ProductName, Categories.CategoryName, Suppliers.CompanyName, Suppliers.ContactName
from Products
inner join Categories
on products.CategoryID = Categories.CategoryID
inner join Suppliers
on products.SupplierID = Suppliers.SupplierID
where Suppliers.CompanyName like 'new%'
-----------------------------------------------
Contoh5:
select Orders.OrderID, Customers.CompanyName, Customers.Address, Customers.City
from Customers inner join Orders
on Customers.CustomerID = Orders.CustomerID
-----------------------------------------------
Contoh6 (Inner Join dengan INISIAL):
select ord.OrderID, cust.CompanyName, cust.Address, cust.City
from orders ord, Customers cust
where ord.CustomerID = cust.CustomerID
-----------------------------------------------
Contoh7:
select Orders.CustomerID, Customers.CompanyName
from Customers inner join Orders
on Customers.CustomerID = Orders.CustomerID
where Customers.CustomerID = 'ALFKI'
-----------------------------------------------
Contoh8:
select Orders.CustomerID, Customers.CompanyName
from Customers inner join Orders
on Customers.CustomerID = Orders.CustomerID
where Customers.CompanyName like 'Antonio%'
-----------------------------------------------
Contoh9:
select Orders.OrderID, Employees.FirstName, Employees.LastName, Employees.City
from Orders inner join Employees
on Orders.EmployeeID = Employees.EmployeeID
-----------------------------------------------
Contoh10:
select emp.FirstName,LastName, ord.OrderID, cust.CompanyName, cust.Address
from employees emp, orders ord, Customers cust
-----------------------------------------------
Contoh11:
select emp.FirstName,LastName, ord.OrderID, cust.CompanyName, cust.Address
from employees emp, orders ord, Customers cust
where emp.EmployeeID = ord.EmployeeID and ord.CustomerID = cust.CustomerID and emp.FirstName = 'Janet'
-----------------------------------------------
Contoh12:
select emp.FirstName,LastName, ord.OrderID, cust.CompanyName, cust.Address
from employees emp, orders ord, Customers cust
where emp.EmployeeID = ord.EmployeeID and ord.CustomerID = cust.CustomerID and emp.FirstName = 'Janet' or emp.FirstName = 'Margaret'
-----------------------------------------------
Contoh13:
select emp.FirstName,LastName, ord.OrderID, cust.CompanyName, cust.Address, cust.city
from employees emp, orders ord, Customers cust
where emp.EmployeeID = ord.EmployeeID and ord.CustomerID = cust.CustomerID and emp.FirstName = 'Janet' or emp.FirstName = 'Margaret' and cust.city = 'Lyon'
-----------------------------------------------

Query FUNGSI AGREGAT:

select sum(nama_tabel) as nama_tabel_HASIL
from Nama_Database;
-----------------------------------------------
select avg(nama_tabel) as nama_tabel_HASIL
from Nama_Database;
-----------------------------------------------
select min(nama_tabel) as nama_tabel_HASIL
from Nama_Database;
-----------------------------------------------
select max(nama_tabel) as nama_tabel_HASIL
from Nama_Database;
-----------------------------------------------
select count(nama_tabel) as nama_tabel_HASIL
from Nama_Database;
-----------------------------------------------
select nama_tabel1, count(*) as nama_tabel_HASIL from Nama_Database
group by nama_tabel1;
-----------------------------------------------
select sum(nama_tabel) as nama_tabel_HASIL
from Nama_Database
where nama_tabel_dipilih=data;
-----------------------------------------------
select top 5 nama_tabel
from Nama_Database
order by nama_tabel desc;
-----------------------------------------------

Query GABUNGAN JOIN DAN AGREGAT:

Contoh menggunakan database NORTHWIND bawaan My SQL
Contoh1:
select supplierID, count(supplierID) as Banyak_Data from Products
group by supplierID;
-----------------------------------------------
Contoh2:
select supplierID, count(*) as Banyak_Data, sum(Unitprice) as Total_Harga
from Products
group by supplierID;
-----------------------------------------------
Contoh3:
select supplierID, count(*) as Banyak_Data, avg(Unitprice) as Rata_Rata_Harga
from Products
group by supplierID;
-----------------------------------------------
Contoh4:
select S.ContactName, avg(P.UnitPrice) as Rata_rata_Harga
from products P inner join Suppliers S
on S.SupplierID = P.SupplierID
group by S.ContactName
order by Rata_rata_Harga desc;
-----------------------------------------------
Contoh5:
select P.SupplierID, S.ContactName, avg(P.UnitPrice) as Rata_rata_Harga
from products P inner join Suppliers S
on S.SupplierID = P.SupplierID
group by S.ContactName, P.SupplierID
order by Rata_rata_Harga desc;
-----------------------------------------------
Contoh6:
select P.SupplierID, P.UnitsInStock, S.ContactName, avg(P.UnitPrice) as Rata_rata_Harga
from products P inner join Suppliers S
on S.SupplierID = P.SupplierID
group by S.ContactName, P.SupplierID, P.UnitsInStock
order by P.UnitsInStock asc;
-----------------------------------------------
Contoh7:
select top 10 P.SupplierID, S.ContactName, sum(P.UnitPrice) as Total_Harga
from products P inner join Suppliers S
on S.SupplierID = P.SupplierID
group by S.ContactName, P.SupplierID
order by Total_Harga asc;
-----------------------------------------------
Contoh8:
select top 10 S.City, P.UnitsOnOrder
from products P inner join Suppliers S
on S.SupplierID = P.SupplierID
order by P.UnitsOnOrder desc;
-----------------------------------------------
Contoh9:
select S.ContactName, P.Discontinued, sum(P.UnitsInStock) as Jumlah_Produk
from products P inner join Suppliers S
on S.SupplierID = P.SupplierID
group by S.ContactName, P.Discontinued
having P.Discontinued=1
order by Jumlah_Produk desc;
-----------------------------------------------

 NB:

• Teks berwarna BIRU dapat diganti sesuai dengan keperluan.
• Bagi sobat blogger yang tidak mempunyai database NORTHWIND, silahkan download di link berikut:

          >>NORTHWIND Full Database Backup<<

Sekianlah rangkuman Query My SQL Server 2008 R2, semoga bermanfaat bagi blogger.com.

Terima Kasih Atas Kunjungan Anda.