Tampilkan postingan dengan label sql. Tampilkan semua postingan
Tampilkan postingan dengan label sql. Tampilkan semua postingan

Senin, 23 April 2012

Praktikum SBD 9c

/*****************************************************************
    soal 9
******************************************************************/

--1 tampilkan namakaryawan dalam huruf besar, EmployeeSalary dalam satuan juta dengan format 2 angka di blkg koma tambahkan kata ' million' di blkanngya , alamat karyawan dalam huruf kecil, EmployeePhone dimana EmployeeSalary di < 1500000, dan alamat karyawan mengandung huruf a dan diakhiri oleh angka 45
create view v1 as(
    select upper(EmployeeName) as [Employee's Name] ,cast(cast(cast(EmployeeSalary as numeric)/1000000 as numeric(3,2)) as varchar(20))+' million' as  [Salary],lower(EmployeeAddress) as [Address],EmployeePhone
    from MsEmployee
    where EmployeeAddress like '%a%45'
    and EmployeeSalary < 1500000
)
select * from v1
drop view v1

--2
create view v2 as(
    select replace(EmployeeCode,'KK','Kyuch Employee ') as [Employee Code],EmployeeName,EmployeePhone
    from MsEmployee
    where right(EmployeeAddress,1)%2=0
    and EmployeeSalary between 1000000 and 1500000
)
select * from v2
drop view v2

--3  tampilkan kodetransaksi, inisial karyawan yang di dapat dari huruf pertama dari nama pertama karyawan dan huruf pertama dari nama terakhir karyawan dalam huruf besar, inisial pelanggan yang didapat dari dua huruf pertama dari depan dalam huruf besar, renttime dengan format dd/mm/yyyy dimana huruf terakhir dari nama pertama karyawan berakhiran huruf i, nama kedua dari karyawan berkahiran huruf i
create view v3 as(
    select TransactionCode,upper(left(EmployeeName,1)+substring(employeename,charindex(' ',employeename)+1,1)) as [Employee Initial],upper(substring(CustomerName,1,2)) as [Customer Initial],convert(varchar(20),RentTime,103) as 'Rent Date'
    from RentalHeader th,MsCustomer mp, MsEmployee mk
    where th.EmployeeCode=mk.EmployeeCode and th.CustomerCode=mp.CustomerCode
    and EmployeeName like '%i %i'
)
select * from v3
drop view v3

--4 tampilkan EmployeeCode, namakaryawan, nomor rumah dari alamat karyawan dimana karyawan tersebut pernah melakukan transaksi pada bulan januari dan nomor alamat karyawan habis dibagi 4 dan namakaryawan di awali oleh huruf V-Z
create view v4 as(
    select EmployeeCode,EmployeeName,reverse(left(reverse(EmployeeAddress),charindex('.',reverse(EmployeeAddress))-1)) as [Address Number]
    from MsEmployee
    where EmployeeCode in(
        select distinct EmployeeCode from RentalHeader
        where datename(month,RentTime)='January'
    )
    and cast(reverse(left(reverse(EmployeeAddress),charindex('.',reverse(EmployeeAddress))-1))as int)%4=0
    and EmployeeName like '[V-Z]%'
)
select * from v4
drop view v4

--5 tampilkan kodetransaksi,CustomerName,LicensePlate,CarName,biayatransaksi dimana transaksi peminjaman mobil dengan biaya tertinggi pada bulan january
create view v5 as(
    select th.TransactionCode,CustomerName,td.LicensePlate,CarName,max(biaya)as [Highest Transaction in January] from (
        select ceiling(cast(datediff(minute,RentTime,ReturnTime) as numeric)/60)*PricePerHour as biaya
        from RentalHeader th, RentalDetail td,MsCar mm, CarDetail mCarName
        where th.TransactionCode=td.TransactionCode and mm.LicensePlate=td.LicensePlate and mCarName.CarCode=mm.CarCode
        and datename(month,RentTime)='January'
    ) as a, MsCustomer mp,RentalHeader th, RentalDetail td,MsCar mm, CarDetail mCarName
    where th.CustomerCode=mp.CustomerCode and th.TransactionCode=td.TransactionCode and mm.LicensePlate=td.LicensePlate and mCarName.CarCode=mm.CarCode
    group by th.TransactionCode,CustomerName,RentTime,ReturnTime,PricePerHour,td.LicensePlate,CarName
    having max(biaya)=ceiling(cast(datediff(minute,RentTime,ReturnTime) as numeric)/60)*PricePerHour
)
select * from v5
drop view v5

--6 tampilkan LicensePlate, namamobil di dapat dari kata setelah spasi pertama, biaya perjam dengan menambahkan kata 'Rp. ' di depannya dan kata '.00' di belakangnya dimana mobil tersebut tidak pernah disewa dan CarName mobil adalah honda
create view v6 as(
    select m.LicensePlate,substring(CarName,charindex(' ',CarName)+1,len(CarName)) as [Car Type],'Rp. '+cast(PricePerHour as varchar(20))+'.00' as [Price Per Hour]
    from MsCar m,CarDetail mm
    where LicensePlate not in(
        select distinct LicensePlate from RentalDetail
    )
    and m.CarCode=mm.CarCode
    and CarName like 'Honda%'
)
select * from v6
drop view v6

--7 tampilkan LicensePlate,CarName,PricePerHour,kapasitas bensin yang didapat dari penjumlahan tiap digit dari LicensePlate * 10 dimana CarName mengandung huruf kedua  dari akhir
create view v7 as(
    select LicensePlate,CarName,PricePerHour, cast(((cast(substring(LicensePlate,2,1)as int)+cast(substring(LicensePlate,3,1)as int)+cast(substring(LicensePlate,4,1)as int)+cast(substring(LicensePlate,5,1)as int))*10) as varchar(20))+' liter' as [Fuel Capacity]
    from MsCar mm,CarDetail mCarName
    where mCarName.CarCode=mm.CarCode
    and CarName like '%'+substring(licenseplate,len(licenseplate)-1,1)+'%'
)
select * from v7
drop view v7

--8 tampilkan EmployeeCode,EmployeeName,EmployeeSalary,persentase komisi yang di dapatkan dari jumlah mobil yang dipinjam oleh pelanggan dan di layani oleh karyawan tersebut, komisi dalam ribuan di dapat dari persentase komisi*EmployeeSalary/100 dimana persentase komisi >2 dan komisi dalam satuan ribuan adalah bilangan ganjil
create view v8 as(
    select mk.EmployeeCode,EmployeeName,'Rp. '+cast(EmployeeSalary as varchar(20))+',00' as [Employee Salary],cast(count(LicensePlate) as varchar(20))+' %' as [Bonus Percentage],cast((cast((count(LicensePlate)*EmployeeSalary/100) as int)/1000)as varchar(20))+' thousand Rupiah' as [Bonus]
    from MsEmployee mk,RentalHeader th,RentalDetail td
    where mk.EmployeeCode=th.EmployeeCode and td.TransactionCode=th.TransactionCode
    group by mk.EmployeeCode,EmployeeName,EmployeeSalary
    having count(LicensePlate) >2 and (cast((count(LicensePlate)*EmployeeSalary/100) as int)/1000)%2=1
)
select * from v8
drop view v8

--9 tampilkan LicensePlate,namamobil di dapat dari kata sesudah spasi , jumlah peminjaman di dapat dari jumlah mobil yang pernah di sewa selama bulan januari dimana CarName mobil bukan suzuki dan digit pertama dari LicensePlate tidak sama dengan jumlah peminjaman
create view v9 as(
    select mm.LicensePlate,substring(CarName,charindex(' ',CarName)+1,len(CarName)) as [Car Name],cast(count(mm.LicensePlate)as varchar(20))+' time(s)' as [Rent Amount]
    from RentalDetail td,CarDetail mCarName,MsCar mm, RentalHeader th
    where mm.LicensePlate=td.LicensePlate and mCarName.CarCode=mm.CarCode and th.TransactionCode=td.TransactionCode
    and CarName not like 'Suzuki%'
    and datename(month,RentTime)='January'
    group by mm.LicensePlate,CarName
    having count(mm.LicensePlate)!=cast(substring(mm.LicensePlate,2,1)as int)
)
select * from v9
drop view v9


--10 tampilkan nama keluarga pelanggan yang di dapat dari kata terakhir, durasi peminjaman dalam hari, RentTime,ReturnTime dimana durasi peminjaman lebih dari 1 hari dan namapelanggan terdiri dari 2 kata dan menit pengembalian mobil lebih besar dari 30
create view v10 as(
    select reverse(left(reverse(CustomerName),charindex(' ',reverse(CustomerName))-1)) as [Last Name],cast(cast((ceiling(cast(datediff(minute,RentTime,ReturnTime) as numeric)/60)/24)as int) as varchar(20))+' Days' as [Duration],RentTime,ReturnTime
    from RentalHeader th,RentalDetail td,MsCustomer mp,MsEmployee mk
    where th.TransactionCode=td.TransactionCode and mp.CustomerCode=th.CustomerCode and mk.EmployeeCode=th.EmployeeCode
    and cast((ceiling(cast(datediff(minute,RentTime,ReturnTime) as numeric)/60)/24)as int) > 1
    and CustomerName like '% %'
    and CustomerName not like '% % %'
    and datename(minute,ReturnTime)>30
)
select * from v10
drop view v10

Kamis, 15 Desember 2011

Tutorial Syntax SQL

Tutor sintaks2 SQL

-----------------------------------------------------------------------------------

cara masuk ke SQL di lab
start - Programs - Programming - Microsoft - Microsoft SQL Server - Query Analyzer

atau dijalankan di run ketik

isqlw

setelah itu masukkan
SQL Server 10.30.64.20
Login Name NIM
Password ddmmyy(tanggal lahir)

-----------------------------------------------------------------------------------

--membuat table
/*
create table [nama_table]
(
[column 1] [data_type_for_column_1],
[column 2] [data_type_for_column_2],
...
)
*/
create table tes
(
angka int,
nama varchar(30)
)


--mengisi record
--insert into [nama_table] ([column1],column2],...) values ([values1],values2],...)
insert into tes values(1,'alvin tanjaya')
insert into tes values(2,'rakhmat suryahadi')
insert into tes values(3,'william salim')
insert into tes values(4,null)
insert into tes values(5,'HARRY DWI SAPUTRA')


--menampilkan semua data dari dalam table
--select [column_name] from [table_name]
select * from tes


--menghapus table
--drop table [table_name]
drop table tes


/*
Jalankan secara urut dari create table,insert 5-5 nya lalu select maka akan tampil table :
angka nama
1 alvin tanjaya
2 rakhmat suryahadi
3 william salim
4 NULL
5 HARRY DWI SAPUTRA
*/

-----------------------------------------------------------------------------------
--1. isnull : menggantikan nilai data yang berisi null
/*ex :
dari table di atas record ke 4 dari field nama berisi NULL
waktu ditampilkan ingin diganti menjadi 'data kosong' maka menggunakan sintaks*/

-- isnull(table ato field apa yg mo diganti,'mau diganti jd apa')
select angka,isnull(nama,'data kosong') as nama from tes


--2. left : mengambil beberapa character dari kiri
/* ex :
kita ingin menampilkan nama dari field name hanya 3 huruf dari kiri maka menggunakan sintaks*/

-- left(string nya apa,berapa banyak char akan diambil)
select left(nama,3) as '3 huruf nama dari kiri' from tes


--3. right : mengambil beberapa character dari kanan
/* ex :
kita ingin menampilkan nama dari field name hanya 2 huruf dari kanan maka menggunakan sintaks*/

-- right(string nya apa,berapa banyak char akan diambil)
select right(nama,2) as '2 huruf nama dari kanan' from tes


--4. replace : menggantikan string dalam bentuk tertentu
/* ex :
kita ingin mengganti semua huruf a menjadi i maka menggunakan sintaks*/

-- replace (string nya apa,'string yg mau di cari','digantikan menjadi apa')
select replace(nama,'a','i') as 'mengganti a menjadi i' from tes


--5. reverse : membalikkan string
/* ex :
kita ingin membalikkan namanya,jadi yg tampil huruf belakang dl sampai ke depan maka menggunakan sintaks*/

-- reverse(string nya apa)
select reverse(nama) as 'nama dibalik' from tes


--6. upper : membuat string menjadi huruf kapital semua
/* ex :
kita ingin membuat namanya menjadi huruf kapital semua maka menggunakan sintaks*/

-- upper(string nya apa)
select upper(nama) as 'NAMA KAPITAL' from tes


--7. lower : membuat string menjadi huruf kecil semua
/* ex :
kita ingin membuat namanya menjadi huruf kecil semua maka menggunakan sintaks*/

-- lower(string nya apa)
select lower(nama) as 'nama kecil' from tes


--8. len : menghitung panjang suatu string
/* ex :
kita ingin menampilkan kolom yg bersi panjang nama maka menggunakan sintaks*/

-- len(string nya apa)
select len(nama) as 'panjang nama' from tes


--9. charindex : cari posisi suatu string di sebuah string
/* ex :
kita ingin mencari huruf 'a' di field nama index ke berapa
index suatu string d SQL dimulai dari angka 1
jika string yg dicari tidak ditemukan maka mengembalikan 0
sintaks yg digunakan*/

-- charindex('string yg dicari','kita mencari di string mana',cari mulai dari indeks ke berapa)
-- parameter ketiga optional,jika ga diisi maka mulai dari index ke 1
select charindex('a',nama) as 'index a pertama' from tes
--jika ingin mencari index a kedua
select charindex('a',nama,charindex('a',nama)+1)as 'index a kedua' from tes


--10. substring : mengambil string dari posisi tertentu
/* ex :
kita ingin mengambil nama hanya mulai dari kata ke 3 sebanyak 5 huruf maka menggunakan sintaks*/

-- substring(string nya apa,mulai dari index ke berapa,sebanyak berapa char)
select substring(nama,3,5) as 'nama dari index ke 3 sebanyak 5 huruf' from tes

--jika hanya ingin mengambil kata ke 2
select substring(nama,charindex(' ',nama)+1,len(nama)-charindex(' ',nama)) as 'kata ke 2 dari nama' from tes


--11. day : mengembalikan tanggal
/* ex :
dari tanggal 15 mei 2008 kita ingin ambil tanggalnya saja maka menggunakan sintaks*/

-- day(tanggalnya brapa)
select day('5/15/2008') as tanggal


--12. month : mengembalikan bulan
/* ex :
dari tanggal 15 mei 2008 kita ingin ambil bulannya saja maka menggunakan sintaks*/

-- month(tanggalnya brapa)
select month('5/15/2008') as bulan


--13. year : mengembalikan tahun
/* ex :
dari tanggal 15 mei 2008 kita ingin ambil tahunnya saja maka menggunakan sintaks*/

-- year(tanggalnya brapa)
select year('5/15/2008') as tahun


--14. getdate : balikin tanggal sekarang
/* ex :
kita ingi tahu sekarang tanggal berapa maka menggunakan sintaks*/

select getdate() as 'tanggal sekarang'


--15. datepart : dapetin tanggal, bulan, tahun dsb dari suatu tanggal (string)
/* ex :
kita ingin mengambil informasi dari suatu tanggal maka menggunakan sintaks*/

-- datepart(yg mau diambil apanya,tanggalnya)
-- ingin mengembalikan tahun sekarang
select datepart(yy,getdate()) as tahun
--or
select datepart(year,getdate()) as tahun
-- ingin mengembalikan bulan sekarang
select datepart(mm,getdate()) as bulan
--or
select datepart(month,getdate()) as bulan
--ingin mengembalikan tanggal sekarang
select datepart(dd,getdate()) as tanggal
--or
select datepart(day,getdate()) as tanggal
--ingin mengembalikan hari sekarang
select datepart(dw,getdate()) as hari
--or
select datepart(weekday,getdate()) as hari
--lainnya dapat dilihat di help


--16. datename : dapetin tanggal, bulan, tahun dsb dari suatu tanggal (string)
/* ex :
kita ingin mengambil informasi dari suatu tanggal maka menggunakan sintaks*/

-- datepart(yg mau diambil apanya,tanggalnya)
-- ingin mengembalikan tahun sekarang
select datename(yy,getdate()) as tahun
--or
select datename(year,getdate()) as tahun
-- ingin mengembalikan bulan sekarang
select datename(mm,getdate()) as bulan
--or
select datename(month,getdate()) as bulan
--ingin mengembalikan tanggal sekarang
select datename(dd,getdate()) as tanggal
--or
select datename(day,getdate()) as tanggal
--ingin mengembalikan hari sekarang
select datename(dw,getdate()) as hari
--or
select datename(weekday,getdate()) as hari
--lainnya dapat dilihat di help


--17. datediff : menghitung selisih waktu antara 2 tanggal
/* ex :
kita ingin tahu beda antara tanggal 3 maret 2008 dan 14 april 2008 berapa hari maka menggunakan sintaks*/

-- datediff(menghitung berdasarkan satuan waktu apa,tanggal mulai,tanggal berakhir)
select datediff(day,'3/3/2008','4/14/2008') as 'selisih tanggal'
--misalkan ingin tahu selisih bulan dan tanggal 14 april diisi di parameter kedua
select datediff(month,'4/14/2008','3/3/2008') as 'selisih tanggal'


--18. dateadd : menambahkan interval pada suatu tanggal
/* ex :
kita ingin tahu besok lusa tanggal berapa maka menggunakan sintaks*/

-- dateadd(menghitung berdasarkan satuan waktu apa,berapa banyak yg mau ditambahkan,tanggal yang mau ditambahkan)
select dateadd(day,2,getdate()) as 'tanggal lusa'
--jika ingin menghitung tanggal 3 minggu yg lalu
select dateadd(week,-2,getdate()) as 'tanggal 3 minggu lalu'


--19. cast : mengubah suatu tipe data menjadi tipe data lain
/* ex :
misalkan di table angka mau kita tambahkan 'no.' maka kita harus ubah dulu tipe datanya menjadi charnya
maka menggunakan sintaks*/

-- cast( [yg mo di ubah apa] as [tipe datanya jd apa])
select 'no. ' + cast(angka as varchar) as Nomer from tes


--20. convert : jika parameter ketiga tidak diisi fungsinya sama seperti cast,jika parameter ketiga diisi lebih maka gunanya untuk konversi tanggal
/* ex :
misalkan di table angka mau kita tambahkan 'no.' maka kita harus ubah dulu tipe datanya menjadi charnya
maka menggunakan sintaks*/
-- convert(tipe datanya jd apa,yg mau dibuah apa)
select 'no. ' + convert(varchar,angka) as Nomer from tes
--jika ingin mengubah format tanggal sekarang menjadi dd mm yy maka menggunakan sintaks
-- convert (biasanya digunakan varchar,tanggalnya,tipenya)
select convert(varchar,getdate(),106) as tanggal
--untuk keterangan tipe dapat dilihat di help


--21. top : balikin sejumlah baris awal
/* ex :
kita hanya ingin menampilkan 3 baris teratas dari table tes maka menggunakan sintaks*/

select top 3 * from tes


--22. like : keyword untuk pencarian pola
/* ex :
kita mau mencari nama yg mengandung huruf 'y' maka menggunakan sintaks*/

select nama from tes where nama like '%y%'

/*
jika kita menggunakan operator '=' maka yg ketika kita tulis where nama ='%y%'
dia tidak mencari yg berformat '%y%' melainkan benar2 mencari string yg bertuliskan '%y%'
% mewakili banyak character
_ mewakili 1 character
maka jika ingin mencari huruf ke 2nya adalah a*/
select nama from tes
where nama like '_a%'


--23. distinct : keyword agar hasilnya tidak berulang jika ada record yg isinya sama persis

/* ex :
misalakan kita tambahkan insert yg terakhir lagi pada table tes maka data terakhir akan ada sebanyak 2*/

insert into tes values(5,'HARRY DWI SAPUTRA')

--jika dicetak tanpa distinct

select * from tes

/*maka hasilnya
angka nama
1 alvin tanjaya
2 rakhmat suryahadi
3 william salim
4 NULL
5 HARRY DWI SAPUTRA
5 HARRY DWI SAPUTRA
*/

--jika menggunakan distinct
select distinct * from tes
/*maka hasilnya

angka nama
1 alvin tanjaya
2 rakhmat suryahadi
3 william salim
4 NULL
5 HARRY DWI SAPUTRA
*/