Bahan UTS

-Menambah Kolom Pada Tabel
alter table Mahasiswa add Status_Mahasiswa char(12);
-Merubah Type Data Kolom Pada Tabel
alter table Mahasiswa alter column Status_Mahasiswa varchar(12);
-Menghapus Kolom Pada Tabel
alter table Mahasiswa drop column Status_Mahasiswa;
-Menambah Constraint Primary Key Pada Kolom
alter table Mahasiswa add constraint pk_Mahasiswa primary key(NIM);
-Menambah Constraint Primary Key Pada Kolom Suatu Tabel
alter table Mahasiswa alter column NIM char(8) not null;
alter table Mahasiswa add constraint pk_Mahasiswa primary key(NIM);
-Menambah Constraint Foreign Key Pada Kolom Suatu Tabel
alter table Mahasiswa add constraint fk_Mahasiswa_ref_jurusan foreign key (Kode_Jurusan) references Jurusan(Kode_Jurusan);
-Menambah Constraint Pada Kolom Suatu Tabel
alter table Mahasiswa drop constraint fk_Mahasiswa_ref_jurusan;
Menghapus Tabel Dengan Perintah SQL
drop table Mahasiswa;
-OPERATOR “>=” DAN “<=”
Select * from Mahasiswa where Tahun_Ajaran>=2001 and Tahun_Ajaran<=2005;
-LIKE
Select * from Mahasiswa where Nama Like ‘Dina%’;
-IN
Select * from Mahasiswa where Tahun_Ajaran in (2005,2006);
-BETWEEN
Select * from Mahasiswa where Tahun_Ajaran Between 2001 and 2006;
-NULL dan NOT NULL
Select * from Mahasiswa where No_Telepon IS NOT NULL;
-AND
Select * from Mahasiswa
where Tahun_Ajaran = ‘2005’ and Kode_Jurusan = ‘LB’;
-OR
Select * from Mahasiswa
where Tahun_Ajaran = ‘2005’ or Kode_Jurusan = ‘LB’;
-GROUP BY
Select kode_jurusan,count(NIM) from Mahasiswa group by kode_jurusan;
-HAVING
select kode_jurusan,count(NIM) from Mahasiswa
group by kode_jurusan
having count(NIM)>1;
-ORDER BY
Select * from Mahasiswa
where Tahun_Ajaran = ‘2005’ and Kode_Jurusan = ‘LB’ order by NIM asc
-DISTINCT
Select distinct(Kode_Jurusan) from Mahasiswa;
SELECT dari Beberapa Tabel
Select Mahasiswa.NIM, Mahasiswa.Nama, Jurusan.Nama_Jurusan from Mahasiswa,Jurusan where Mahasiswa.kode_jurusan = Jurusan.kode_jurusan;
-UPDATE
Update Mahasiswa set Nama = ‘Aming Surya Praja’ where NIM =’10113025’;
-DELETE
Delete from Mahasiswa where NIM =’10113025’;

Syntax create DB,table

create database USM
on primary (NAME = USM_data, filename= 'C:\USM.mdf')
LOG ON
(NAME = USM_log, FILENAME =c:\USM.ldf)

USE USM

create table TPropinsi
(
KodeProp varchar(3) primary key,
Nama varchar (25) not null
)

create table TBank
(
KodeBnk varchar(3) primary key,
Nama varchar(25) no null
)

create table TPendaftaran
(
Nomor int primary key identity,
Nama varchar(50) not null,
KodeProp varchar(3) constraint FK_TRelPenPropBnk_TPropinsi FOREIGN KEY(KodeProp)REFERENCES TPropinsi (KodeProp),
TglLhr varchar(50) not null,
JKelamin varchar(50) not null,
Tinggi varchar(10) null,
ThnLulus varchar(20) not null,
TotalNilai int not null,
JmlMP int not null,
KMata varchar(20) not null
KodeBnk varchar(3) constraint FK_TRelPenPropBnk_TBank FOREIGN KEY(KodeBnk)REFERENCES Tbank (KodeBnk)
)

autonumber reset tahun dan gudang

alter PROCEDURE SP_TambahPOGudang
@TglPO datetime, @Gudang varchar(6),@KodeSupplier varchar(10)
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @NomorPO varchar(20)

--1.Cari Nomor Akhir
select @NoAkhir = convert(int, Max(right(NomorPO,5)))
from TGudang
where substring(NomorPO,4,4) = convert(char(4), Datepart(yyyy, @TglPO))
and substring(NomorPO,9,2) = @Gudang

--2.Cari Nomor Baru
if @NoAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir + 1

Set @NomorPO = 'PO-' + convert(char(4), Datepart(yyyy, @TglPO )) + '-' + @Gudang + '-' +
RIGHT('00000'+Convert(varchar, @NoBaru), 5)

--3.Insert Data baru
Insert TGudang (NomorPO, TglPO, GUDANG, KodeSupplier) Values (@NomorPO, @TglPO, @Gudang, @KodeSupplier)
END

exec SP_TambahPOGudang '06/05/1980','G1','S-00001'
select * from TGudang

delete from TGudang

autonumber nama depan

alter PROCEDURE SP_TambahBarang
@NamaBarang varchar(50) ,@Stok int
AS
BEGIN
DECLARE @NOAKHIR INT
DECLARE @NOBaru INT
DECLARE @Kode varchar(20)

--1.Cari Nomor Akhir
select @NoAkhir = convert(int, Max(right(KodeBarang,3)))
from TBarang
where LEFT(KodeBarang,1) = LEFT(@NamaBarang,1)

--2.Cari Nomor Baru
if @NoAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir + 1

Set @Kode = LEFT(@NamaBarang,1) + RIGHT('00000'+Convert(varchar, @NoBaru), 3)

--3.Insert Data baru
Insert TBarang (KodeBarang, NamaBarang, Stok) Values (@Kode,@NamaBarang, @Stok)
END

exec SP_TambahBarang 'Bangke',7
select * from TBarang
delete from TBarang

function autonumber

alter function No1
(
@tahun varchar(50)
)
returns varchar (50)
as
begin
declare @noakhir varchar(50)
declare @NoPO varchar(50)
declare @nobaru varchar(50)

select @NoAkhir = convert(int, Max(right(NoPO,5)))
from tbPO
where substring(NoPO,4,4) = @tahun

if @NoAkhir is null
Set @Nobaru=0
else
Set @NoBaru = @noakhir+0
select @NoPO='PO-'+@tahun+'-'+RIGHT('00000'+Convert(varchar, @Nobaru), 5)
return @NoPO

end

select dbo.No1(2020)

function autonumbers

create function No2
(
@tahun varchar(50)
)
returns varchar (50)
as
begin
declare @noakhir varchar(50)
declare @NoPO varchar(50)
declare @nobaru varchar(50)

select @NoAkhir = convert(int, Max(right(NoPO,5)))
from tbPO
where substring(NoPO,4,4) = @tahun

if @NoAkhir is null
Set @Nobaru=1
else
Set @NoBaru = @NoAkhir+1
select @NoPO='PO-'+@tahun+'-'+RIGHT('00000'+Convert(varchar, @NoBaru), 5)
return @NoPO

end

select dbo.No2(2010)

Fungsi-fungsi

No Sintaks Pemanggilan Arti/hasil
FUNGSI STRING
1 ASCII('C') Nomor ASCII dari karakter ‘C’
2 Char(65) Karakter dari ASCII bernomor 65
3 charindex('E','hello') Posisi ‘E’ dalam kata ‘hello’
4 left('HELLO',3) 3 huruf terkiri dari kata ‘HELLO’
5 ltrim(' Hello') Membuang spasi di kiri
6 right('hello',3) 3 huruf terkanan dari kata ‘HELLO’
7 rtrim('Hello ') Membuang spasi di kanan
8 len('Hello') Panjang/jumlah huruf dari kata ‘HELLO’
9 lower('HELLO') Merubah ke huruf kecil
10 patindex('%BOX%','ACTION BOX') Posisi kata ‘BOX’ dalam ‘ACTION BOX’
11 reverse('HELLO') Membalik susunan huruf/depan ke belakang dst
12 space(5) Membentuk spasi sebanyak 5
13 str(123.45,6,0) Merubah ke string dengan 6 digit tanpa spasi
14 stuff('hello',2,2,'i') Mengganti huruf kedua dengan huruf ‘i’
15 substring('hello',2,2) Mengambil 2 huruf mulai huruf ke 2
16 upper('hello') Merubah ke huruf besar

FUNGSI TANGGAL
1 getdate() Mengambil tanggal lengkap hari ini
2 datepart(dd,getdate()) Mengambil bagian tanggal dari hari ini
3 datename(dw,'1980-06-11') Menghasilkan nama hari dari 11 juni 1980
4 dateadd(yy,2,getdate()) Menambah 2 tahun dari hari ini
5 datediff(dd,'1980-06-11',getdate()) Menghitung selisih hari (umur sejak 11 jun 80)

FUNGSI MATEMATIK
1 abs(-25) Mengambil nilai absolute (tanpa negatif)
2 sin(pi()/2) Menghitung sinus sudut 90 derajat (pi=180)
3 exp(1) Menghitung bilangan e pangkat 1
4 degrees(pi()/2) Mengkonversi dari pi/2 radian ke derajat
5 radians(180) Mengkonversi 180 derajat ke radian
6 power(2,4) Menghitung 2 pangkat 4
7 floor(90.7) Membulatkan ke bawah
8 sign(90) Menghasilkan tanda bilangan (pos=1,neg=-1)
9 rand(90) Menghasilkan bil acak dengan bil pembangkit 90
10 round(1234.567,2) Membulatkan ke 2 angka belakang koma

FUNGSI SYSTEM & METADATA
1 host_id() Menghasilkan ID dari host
2 host_name() Menghasilkan nama dari host
3 suser_sname() Menghasilkan system username yg sdg aktif
4 user_id() Menghasilkan user ID yang sedang aktif
5 user_name() Menghasilkan username yang sedang aktif
6 db_id() Menghasilkan database ID yang sedang aktif
7 db_name() Menghasilkan database name yang sedang aktif
8 object_id('Authors') Menghasilkan ID dari object bernama ‘Authors’
9 object_name('629577281') Menghasilkan nama object yang ber ID=…

SP Insert

create proc SP_InsPenjualan
@pKodePelanggan nvarchar(12),
@pNoFJ nvarchar(12),
@pTotalFaktur money
as
begin
if exists (select KodePelanggan from Pelanggan where KodePelanggan = @pKodePelanggan)
begin
if exists (select NoFJ from FJ where NoFJ = @pNoFJ)
select 'NoFJ Sudah ada'
else
begin
insert into FJ(NoFJ, KodePelanggan, TotalFaktur)
values(@pNoFJ, @pKodePelanggan, @pTotalFaktur)
update Pelanggan set Piutang = @pTotalFaktur where KodePelanggan = @pKodePelanggan
end
end
else
select 'data pelanggan tidak ada'
select * from FJ
select * from Pelanggan
end

exec SP_InsPenjualan 'FJ-0000001','C-00001',50000

beberapa screenshot