Tugas Akhir Praktek SQL

1.Diskusi dengan kelompok unuk pemilihan topik UAS

Topik : Sistem Informasi Perpustakaan


2. Desain tabel dan Relasi tabel

3. Desain HIPO 

Praktek SQL

Tabel Retur Jual

Setelah kemarin membuat tabel penjualan, sekarang akan kita buat tabel Retur Jual.

Pertama-tama kita membuat tabel Retur Jual dulu.

create table "Retur Jual"

("No Retur" Varchar(10) primary key,

Nonota varchar(10) foreign key references penjualan(Nonota),

"Tanggal Retur" date,

"Jumlah Retur" int,

"Keterangan Cacat" varchar(50))

1. Isi data, ambil dari tabel penjualan, 2 record untuk di retur

insert into [Retur Jual]

values ('RTR001','JUAL001','2017/03/25','2','Cacat')

insert into [Retur Jual]

values ('RTR002','JUAL005','2017/05/10','5','Cacat')

2. Browsing operasi matematika menggunakan sql untuk penambahan stok barang

3. Alternatif  lain, update stok barang

SELECT Jumlah, Jumlahjual, [Jumlah Retur], ((Barang.jumlah-Penjualan.jumlahjual)+[Retur Jual].[Jumlah Retur])

AS 'Stock_Update' FROM Barang

inner join Penjualan on Barang.Kdbrg=Penjualan.Kdbrg

inner join [RETUR JUAL] on PENJUALAN.NONOTA=[RETUR JUAL].Nonota

4. Jumlah retur tidak melebihi jumlah jual

SELECT Jumlahjual, [Jumlah Retur]

FROM Barang

inner join Penjualan on Barang.Kdbrg=Penjualan.Kdbrg

inner join [RETUR JUAL] on PENJUALAN.NONOTA=[RETUR JUAL].Nonota

5. Tanggal retur tidak boleh sebelum tanggal jual

SELECT Tanggaltrans, [Tanggal Retur]

FROM Barang

inner join Penjualan on Barang.Kdbrg=Penjualan.Kdbrg

inner join [RETUR JUAL] on PENJUALAN.NONOTA=[RETUR JUAL].Nonota

6. Tambahkan 5 data retur

insert into [Retur Jual]

values ('RTR003','JUAL002','2017/03/25','3','Kadaluwarsa')

insert into [Retur Jual]

values ('RTR004','JUAL003','2017/04/20','5','Cacat')

insert into [Retur Jual]

values ('RTR005','JUAL005','2017/05/10','1','Cacat')

insert into [Retur Jual]

values ('RTR006','JUAL006','2017/06/10','2','Cacat')

insert into [Retur Jual]

values ('RTR007','JUAL007','2017/06/25','1','Kadaluwarsa')

7. Update data 3 retur

update [Retur Jual] set [Jumlah Retur]='3' where [No Retur]='RTR005';

update [Retur Jual] set [Tanggal Retur]='2017/06/12' where [No Retur]='RTR006';

update [Retur Jual] set [Keterangan Cacat]='Kadaluwarsa' where [No Retur]='RTR004';

8. Delete record 1 data retur

delete from [Retur Jual] where [No Retur]='RTR005';

9. Tambahkan field jenis_paket

alter table [Retur Jual] add jenis_paket varchar(20)

10. Isi jenis paket

update [Retur Jual] set jenis_paket='JNE' where [No Retur]='RTR004';

update [Retur Jual] set jenis_paket='JNE' where [No Retur]='RTR001';

update [Retur Jual] set jenis_paket='POS' where [No Retur]='RTR002';

update [Retur Jual] set jenis_paket='POS' where [No Retur]='RTR003';

update [Retur Jual] set jenis_paket='WAHANA' where [No Retur]='RTR006';

update [Retur Jual] set jenis_paket='WAHANA' where [No Retur]='RTR007';

Tugas Aplikasi

Tugas Kelompok 

Setelah kemarin kita membuat 3 tabel yaitu tabel barang, tabel supplier dan tabel pembelian. Sekarang kita akan membuat aplikasi beradasarkan 3 tabel tersebut menggunakan VB.net sebagai bahasa pemogramannya dan SQL Server 2014 sebagai databasenya.


Cara Mengoneksikan Ke Database SQL Server 2014

Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Module mdlkoneksi
    Public comSQL As New SqlClient.SqlCommand
    Public sql As String
    Public conn As New SqlConnection
    Public str As String = "Data Source=SARJONO-PC;Initial Catalog=KelompokSangar;Integrated Security=True"

    Public Sub bukakoneksi()
        If conn.State = ConnectionState.Closed Then
            conn.ConnectionString = str
            Try
                conn.Open()
                'MsgBox("Koneksi Berhasil")
            Catch ex As Exception
                MsgBox("Koneksi Gagal: " & ex.ToString)
            End Try
        End If
    End Sub

    Public Sub tutupkoneksi()
        If conn.State = ConnectionState.Open Then
            Try
                conn.Close()
            Catch ex As Exception
                MsgBox("Gagal menutup koneksi: " & ex.ToString)
            End Try
        End If
    End Sub
End Module

Tampilan Form beserta Coding Simpan, Edit, Delete dan Tampilkan

1. Form Menu

Public Class FormMenu

    Private Sub BarangToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles BarangToolStripMenuItem.Click

        Form1.MdiParent = Me

        Form1.Show()

        Form2.Hide()

        Form3.Hide()

    End Sub

    Private Sub SupplierToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles SupplierToolStripMenuItem.Click

        Form2.MdiParent = Me

        Form2.Show()

        Form1.Hide()

        Form3.Hide()

    End Sub

    Private Sub PembelianToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles PembelianToolStripMenuItem.Click

        Form3.MdiParent = Me

        Form3.Show()

        Form1.Hide()

        Form2.Hide()

    End Sub

End Class

2. Form Data Barang

Imports System.Data.SqlClient

Public Class Form1

    Private Sub tampil(ByVal aksi As String)

        bukakoneksi()

            sql = "SELECT * FROM Barang"

        Dim da As New SqlDataAdapter(sql, conn)

        Dim ds As New DataSet

        da.Fill(ds)

        Dim dt As New DataTable

        For Each dt In ds.Tables

            DataGridView1.DataSource = dt

        Next

        tutupkoneksi()

    End Sub

    Private Sub BtnSimpan_Click(sender As Object, e As EventArgs) Handles BtnSimpan.Click

        bukakoneksi()

        sql = "INSERT INTO Barang(kdbrg,nmbrg,jnsbrg,harga,jumlah) VALUES ( " &

            "'" & TxtKodeBarang.Text & "'," & "'" & TxtNamaBarang.Text & "'," & "'" & TxtJenisBarang.Text & "'," & "'" & TxtHarga.Text & "'," &

            "'" & TxtJumlah.Text & "')"

        comSQL = New SqlCommand(sql, conn)

        Try

            comSQL.ExecuteNonQuery()

        Catch ex As Exception

        End Try

        tutupkoneksi()

        tampil("")

        lb_status.Text = "Simpan Berhasil"

    End Sub

    Private Sub BtnEdit_Click(sender As Object, e As EventArgs) Handles BtnEdit.Click

        bukakoneksi()

        sql = "UPDATE barang SET nmbrg = '" & TxtNamaBarang.Text & "'," &

        "jnsbrg='" & TxtJenisBarang.Text & "'," &

        "harga='" & TxtHarga.Text & "'," &

        "jumlah='" & TxtJumlah.Text & "'" &

        "WHERE kdbrg='" & TxtKodeBarang.Text & "'"

        comSQL = New SqlCommand(sql, conn)

        Try

            comSQL.ExecuteNonQuery()

            'MsgBox("edit berhasil")

            lb_status.Text = "Edit Berhasil"

        Catch ex As Exception

            'MsgBox("edit gagal")

        End Try

        tutupkoneksi()

        tampil("")

    End Sub

    Private Sub BtnTampilkan_Click(sender As Object, e As EventArgs) Handles BtnTampilkan.Click

        tampil("")

        lb_status.Text = "Ok"

    End Sub

    Private Sub DataGridView1_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellClick

        Dim i As Integer

        i = DataGridView1.CurrentRow.Index

        TxtKodeBarang.Text = DataGridView1.Item(0, i).Value

        TxtNamaBarang.Text = DataGridView1.Item(1, i).Value

        TxtJenisBarang.Text = DataGridView1.Item(2, i).Value

        TxtHarga.Text = DataGridView1.Item(3, i).Value

        TxtJumlah.Text = DataGridView1.Item(4, i).Value

    End Sub

    Private Sub BtnDelete_Click(sender As Object, e As EventArgs) Handles BtnDelete.Click

        bukakoneksi()

        sql = "DELETE FROM Barang Where kdbrg='" & TxtKodeBarang.Text & "'"

        comSQL = New SqlCommand(sql, conn)

        Try

            comSQL.ExecuteNonQuery()

            'MsgBox("hapus berhasil")

            lb_status.Text = "Hapus Berhasil"

        Catch ex As Exception

            'MsgBox("hapus gagal")

        End Try

        tutupkoneksi()

        tampil("")

    End Sub

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        tampil("")

    End Sub

End Class

3. Form Data Supplier

Imports System.Data.SqlClient

Public Class Form2

    Private Sub tampil(ByVal aksi As String)

        bukakoneksi()

        sql = "SELECT * FROM Supplier"

        Dim da As New SqlDataAdapter(sql, conn)

        Dim ds As New DataSet

        da.Fill(ds)

        Dim dt As New DataTable

        For Each dt In ds.Tables

            DataGridView1.DataSource = dt

        Next

        tutupkoneksi()

    End Sub

    Private Sub BtnTampilkan_Click(sender As Object, e As EventArgs) Handles BtnTampilkan.Click

        tampil("")

        lb_status.Text = "Ok"

    End Sub

    Private Sub BtnSimpan_Click(sender As Object, e As EventArgs) Handles BtnSimpan.Click

        bukakoneksi()

        sql = "INSERT INTO Supplier(kdsup,nmsup,tgllhr,alamatkantor,telpkantor,jekel,email) VALUES ( " &

            "'" & TxtKodeSupplier.Text & "'," & "'" & TxtNamaSupplier.Text & "'," & "'" & TxtTanggalLahir.Text & "'," &

            "'" & TxtAlamatKantor.Text & "'," & "'" & TxtTelponKantor.Text & "'," & "'" & TxtJenisKelamin.Text & "'," &

            "'" & TxtEmail.Text & "')"

        comSQL = New SqlCommand(sql, conn)

        Try

            comSQL.ExecuteNonQuery()

        Catch ex As Exception

        End Try

        tampil("")

        tutupkoneksi()

        lb_status.Text = "Simpan Berhasil"

    End Sub

    Private Sub BtnEdit_Click(sender As Object, e As EventArgs) Handles BtnEdit.Click

        bukakoneksi()

        sql = "UPDATE Supplier SET " &

        "nmsup='" & TxtNamaSupplier.Text & "'" &

        "tgllhr='" & TxtTanggalLahir.Text & "'" &

        "alamatkantor='" & TxtAlamatKantor.Text & "'" &

        "telpkantor='" & TxtTelponKantor.Text & "'" &

        "jekel='" & TxtJenisKelamin.Text & "'" &

        "email='" & TxtEmail.Text & "'" &

        "WHERE kdsup='" & TxtKodeSupplier.Text & "'"

        comSQL = New SqlCommand(sql, conn)

        Try

            comSQL.ExecuteNonQuery()

            'MsgBox("edit berhasil")

        Catch ex As Exception

            'MsgBox("edit gagal")

        End Try

        tutupkoneksi()

        tampil("")

        lb_status.Text = "Update Berhasil"

    End Sub

    Private Sub BtnDelete_Click(sender As Object, e As EventArgs) Handles BtnDelete.Click

        bukakoneksi()

        sql = "DELETE FROM Supplier Where kdsup='" & TxtKodeSupplier.Text & "'"

        comSQL = New SqlCommand(sql, conn)

        Try

            comSQL.ExecuteNonQuery()

            'MsgBox("hapus berhasil")

        Catch ex As Exception

            'MsgBox("hapus gagal")

        End Try

        tampil("")

        tutupkoneksi()

        lb_status.Text = "Delete Berhasil"

    End Sub

    Private Sub DataGridView1_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellClick

        Dim i As Integer

        i = DataGridView1.CurrentRow.Index

        TxtKodeSupplier.Text = DataGridView1.Item(0, i).Value

        TxtNamaSupplier.Text = DataGridView1.Item(1, i).Value

        TxtTanggalLahir.Text = DataGridView1.Item(2, i).Value

        TxtAlamatKantor.Text = DataGridView1.Item(3, i).Value

        TxtTelponKantor.Text = DataGridView1.Item(4, i).Value

        TxtJenisKelamin.Text = DataGridView1.Item(5, i).Value

        TxtEmail.Text = DataGridView1.Item(6, i).Value

    End Sub

    Private Sub Form2_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        tampil("")

    End Sub

End Class

4. Form Data Pembelian

Imports System.Data.SqlClient
Public Class Form3
    Private Sub tampil(ByVal aksi As String)
        bukakoneksi()

        sql = "SELECT * FROM Pembelian"

        Dim da As New SqlDataAdapter(sql, conn)
        Dim ds As New DataSet
        da.Fill(ds)
        Dim dt As New DataTable

        For Each dt In ds.Tables
            DataGridView1.DataSource = dt
        Next

        tutupkoneksi()
    End Sub

    Private Sub BtnSimpan_Click(sender As Object, e As EventArgs) Handles BtnSimpan.Click
        bukakoneksi()
        sql = "INSERT INTO Pembelian(nonota,kdbrg,kdsup,tanggalbeli,jumlahbeli,hargabeli,total) VALUES ( " &
            "'" & TxtNoNota.Text & "'," & "'" & TxtKodeBarang.Text & "'," & "'" & TxtKodeSupplier.Text & "'," & "'" & TxtTanggalBeli.Text & "'," &
            "'" & TxtJumlahBeli.Text & "'," & "'" & TxtHargaBeli.Text & "'," & "'" & TxtTotal.Text & "')"

        comSQL = New SqlCommand(sql, conn)

        Try
            comSQL.ExecuteNonQuery()
            'MsgBox("simpan berhasil")
        Catch ex As Exception
            'MsgBox("simpan gagal")
        End Try
        tampil("")
        tutupkoneksi()
        lb_status.Text = "Simpan Berhasil"
    End Sub

    Private Sub BtnEdit_Click(sender As Object, e As EventArgs) Handles BtnEdit.Click
        bukakoneksi()
        sql = "UPDATE Pembelian SET " &
        "kdbrg='" & TxtKodeBarang.Text & "'" &
        "kdsup='" & TxtKodeSupplier.Text & "'" &
        "tanggalbeli='" & TxtTanggalBeli.Text & "'" &
        "jumlahbeli='" & TxtJumlahBeli.Text & "'" &
        "hargabeli='" & TxtHargaBeli.Text & "'" &
        "total='" & TxtTotal.Text & "'" &
        "WHERE nonota='" & TxtNoNota.Text & "'"

        comSQL = New SqlCommand(sql, conn)

        Try
            comSQL.ExecuteNonQuery()
            'MsgBox("edit berhasil")
        Catch ex As Exception
            'MsgBox("edit gagal")
        End Try
        tampil("")
        tutupkoneksi()
        lb_status.Text = "Update Berhasil"
    End Sub

    Private Sub BtnDelete_Click(sender As Object, e As EventArgs) Handles BtnDelete.Click
        bukakoneksi()
        sql = "DELETE FROM Pembelian Where nonota='" & TxtNoNota.Text & "'"
        comSQL = New SqlCommand(sql, conn)

        Try
            comSQL.ExecuteNonQuery()
            'MsgBox("hapus berhasil")
        Catch ex As Exception
            'MsgBox("hapus gagal")
        End Try
        tampil("")
        tutupkoneksi()
        lb_status.Text = "Delete Berhasil"
    End Sub

    Private Sub BtnTampilkan_Click(sender As Object, e As EventArgs) Handles BtnTampilkan.Click
        tampil("")
        lb_status.Text = "Ok"
    End Sub


    Private Sub DataGridView1_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellClick
        Dim i As Integer
        i = DataGridView1.CurrentRow.Index
        TxtNoNota.Text = DataGridView1.Item(0, i).Value
        TxtKodeBarang.Text = DataGridView1.Item(1, i).Value
        TxtKodeSupplier.Text = DataGridView1.Item(2, i).Value
        TxtTanggalBeli.Text = DataGridView1.Item(3, i).Value
        TxtHargaBeli.Text = DataGridView1.Item(4, i).Value
        TxtJumlahBeli.Text = DataGridView1.Item(5, i).Value
        TxtTotal.Text = DataGridView1.Item(6, i).Value
    End Sub

    Private Sub Form3_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        tampil("")
    End Sub
End Class

UTS

1. Create tabel penjualan

2. Isi tabel penjualan

3. Tambahkan 1 field di tabel penjualan

4. Hapus 1 record

5. Isi data field yang kosong

6. Tampilkan nama barang dan tanggal transaksi

7. Tampilkan jenis barang dan jumlah jual

8. Tampilkan stok dari barang yang penjualan di bulan april 2017

9. Tampilkan harga jual yang barangnya Sampho Clear

10. Tampilkan nama barang, tanggalbeli, tanggaltransaksi dan stok

SQL JOIN

SOAL LATIHAN JOIN

1. Tampilkan nama supplier yang barangnya Indomie

2. Tampilkan jumlah beli yang nama barang Indomie 

3. Tampilkan nama barang yang harga beli di atas 10.000

4. Tampilkan kode supplier, nama supplier yang total pembelian diatas 100.000

5. Tampilkan jenis barang yang stok nya habis

6. Tampilkan alamat supplier yang melaksanakan transaksi tanggal 5 januari 2017

7. Tampilkan nama barang, nama supplier untuk bulan Mei 2017

8. Tampilkan no nota, nama barang, nama supplier dan alamat perusahaan supplier 

9. Tampilkan nama barang dan nama supplier untuk barang yang stok nya di bawah 100

10. Tampilkan semua nama barang dan nama supplier

Tugas Ke-4 Join

CARA MENAMPILKAN TABEL MENGGUNAKAN JOIN

1. Menampilkan jenis barang, nama barang, no nota, tanggal beli dan total menggunakan inner join dari tabel barang dan tabel pembelian.

2. Menampilkan  nama barang, tanggal beli dan total menggunakan inner join dari tabel barang dan tabel pembelian.

3. Menampilkan nama supplier, no nota, harga beli dan jumlah beli menggunakan leftjoin dari tabel supplier dan tabel pembelian.

4. Menampilkan nama barang, harga beli, jumlah beli dan total menggunakan left join dari tabel barang dan tabel pembelian.

5. Menampilkan kode barang, jenis barang dan no nota menggunakan left outher join dari tabel barang dan tabel pembelian.

6. Menampilkan alamat kantor, no nota, kode barang dan total menggunakan left outher join dari tabel supplier dan tabel pembelian.

7. Menampilkan alamat kantor, no nota, kode barang dan total menggunakan right join dari tabel supplier dan tabel pembelian.

8. Menampilkan nama supplier, nama barang dnno nota menggunakan right join dari tabel supplier, tabel barang dan tabel pembelian.

9.Menampilkan nama supplier, telp kantor, no nota dan total menggunakan right outher join dari tabel supplier dan tabel pembelian.

10. Menampilkan nama barang, harga, no nota dan total menggunakan right outher join dari tabel supplier, tabel barang dan tabel pembelian.