Query Analyzer In SQL Server

create database Akademik_Maya

create table Mahasiswa
(nim char (15)primary key,nama varchar (30) not null,thn_lulus char (4),kd_jur char (2) not null
)

create table Matakuliah
(kd_mk char (5)primary key,nama_mk varchar (20)not null,sks char (2) not null
)

create table Dosen
(nid char (15) primary key,nama varchar (30) not null
)

create table Jurusan
(kd_jur char (2) primary key,nm_jur varchar (30),nm_kajur varchar (35)
)

Alter table Mahasiswa
add constraint fkjurusan foreign key (kd_jur)
references Jurusan (kd_jur)

select * from Mahasiswa

create table Nilai
(nim char (15) constraint fknik foreign key (nim) references Mahasiswa (nim),
kd_mk char (5) constraint fkmk foreign key (kd_mk) references Matakuliah (kd_mk),
nid char (15) constraint fknid foreign key (nid) references Dosen (nid)
)

SOAL
1. menambahkan field di tabel mhs,gdr type char length =2
alter table Mahasiswa
add gdr char (2)
2.menambahkan field di tabel mhs,st,type char length =10
alter table Mahasiswa
add st char (10)

3.menanbahkan field kota_t type varchar length =20
alter table Mahasiswa
add kota_t varchar (20)

4.ubah field gdr menjadi Gol_darah
alter table Mahasiswa
Drop column gdr

alter table Mahasiswa
add Gol_darah char (2)


5.Ubah field st menjadi St_nikah char length =9 tdk boleh kosong
alter table Mahasiswa
drop column st

alter table Mahasiswa
add st_Nikah char (9)

6.hapus field kota_t
alter table Mahasiswa
drop column kota_t
select*from Mahasiswa
select*from Jurusan

INPUT
sp_help
insert into Jurusan values ('67','Komputerisasi Akuntansi','Primadina')
insert into Jurusan values ('26','Manajemen Informatika','Primadina')
insert into Jurusan values ('05','Teknik Komputer','Kikim')
insert into Jurusan values ('07','Teknik Informatika','Kikim')
insert into Jurusan values ('06','Sistem Informasi','Budi')
select * from Dosen
sp_help
insert into Dosen Values ('D001','Satria')
insert into Dosen values ('D002','Sapto')
insert into Dosen values ('D003','Hadi Prayitno')
insert into Dosen Values ('D004','Widiyawati')
insert into Dosen values ('D005','Baik')

select * from Matakuliah
sp_help
insert into Matakuliah Values ('M001','Akuntansi','3')
insert into Matakuliah values ('M002','Pemrograman','3')
insert into Matakuliah values ('M003','Basis Data','3')
insert into Matakuliah Values ('M004','SIM','4')
insert into Matakuliah values ('M005','Manajement','2')
select * from Mahasiswa
sp_help
insert into Mahasiswa values ('43A87067120411','Maya Puspita','2015','67','B','Belum')
insert into Mahasiswa values ('43A87007120300','Dewi','2015','07','A','Belum')
insert into Mahasiswa values ('43A87067120401','Bariyah','2015','67','0','Belum')
insert into Mahasiswa values ('43A87006120411','Budi','2013','06','B','Belum')
insert into Mahasiswa values ('43A87005120411','Deni','2012','05','AB','Belum')
select*from Nilai
SP_HELP
insert into Nilai values ('43A87067120411','M001','D001')
insert into Nilai values ('43A87007120300','M002','D002')
insert into Nilai values ('43A87067120401','M003','D003')
insert into Nilai values ('43A87006120411','M004','D004')
insert into Nilai values ('43A87005120411','M005','D005')

SOAL
1.tampilkan semua data mhs yg semua golongan darahnya "A"
 select * from Mahasiswa Where Gol_darah ='A'
2.Tampilkan nama mhs,kd jurusan yg sudah menikah
select nama,kd_jur,st_Nikah from Mahasiswa where st_Nikah='Sudah'
3.Tampilkan semua data dosen
select * from Dosen
4.Tampilkan data matkul yg sks >3
select * from Matakuliah where sks >3
5.Tampilkan nama matkul dan sks
select nama_mk,sks from Matakuliah
6.Tampilkan Semua dat nilai
select * from Nilai
7.tampilkan nim,nm mhs,tahun llus yg tahun lulus antara 2010-2013
select nim,nama,thn_lulus from Mahasiswa where thn_lulus between 2010 and 2013
8.tampilkan smua mhs yg namanya diakhiri huruf 'A' dan gol darah 'A'
select *from Mahasiswa where nama like ('&A') or Gol_darah = 'B'
9.tampilkan semua data dosen namanya diawali huruf 'A'
select * from Dosen where nama like ('A&')
10.ubah nama database menjadi AKADEMIK _MAYA_411
SP_RENAMEDB "Akademik_Maya","AKADEMIK_MAYA_411"
11.tambah field alamat type data char(20) dapa table dosen
alter table Dosen
add alamat char(20)
select * from Dosen
12.Input data alamat pada table dosen yaitu "bekasi" menggunakan update
update Dosen set alamat = 'Bekasi'
13.Ubah data alamat dosen yg kode=D001 dan D003 menjadi "Pondok Gede"
update Dosen set alamat ='Pondok Gede' where nid in ('D001','D003')

9.tampilkan nama mhs ,thn lulus,gol drah,dan nama jurusan yg blm menikah
select*from Mahasiswa
select * from Jurusan
select A.nama,A.thn_lulus,A.Gol_darah,B.nm_jur
from Mahasiswa A,Jurusan B
where A.kd_jur=B.kd_jur and A.st_Nikah='Belum'

10.tampilkan nilai.nim,nm mhs,nm matkul dan nm dosen
select*from Nilai
select*from Dosen
select*from Matakuliah
select A.nim,B.nama,C.nama_mk,D.nama
FROM Nilai A,Mahasiswa B,Matakuliah C,Dosen D
where A.nim=B.nim and A.kd_mk=C.kd_mk and A.nid=D.nid

How to Insert record in DataGridView

Public Class Entry_data
    Dim baris, kolom As Integer
    Dim data1, data2, data3, data4 As String


    Private Sub DG1_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DG1.CellClick
        baris = DG1.CurrentRow.Index
        If DG1.Columns(e.ColumnIndex).Name = "Kode Barang" Then
            kolom = 1
        Else
            If DG1.Columns(e.ColumnIndex).Name = "Nama Barang" Then
                kolom = 2
            Else
                If DG1.Columns(e.ColumnIndex).Name = "Satuan" Then
                    kolom = 3
                Else
                    kolom = 4
                   
                End If
                End If
            End If

    End Sub
    Private Sub simpan()
        If kolom = 1 Then
            Tampil.Connection = Dtbase
            Tampil.CommandType = CommandType.Text
            Tampil.CommandText = "insert into Barang (kdbrg) values ( '" & Trim(data1) & "')"
            Tampil.ExecuteNonQuery()
        Else
            If kolom = 2 Then
                Tampil.Connection = Dtbase
                Tampil.CommandType = CommandType.Text
                Tampil.CommandText = "update Barang set nmbrg='" & Trim(data2) & "'where kdbrg='" & Trim(data1) & "'"
                Tampil.ExecuteNonQuery()
            Else
                If kolom = 3 Then
                    Tampil.Connection = Dtbase
                    Tampil.CommandType = CommandType.Text
                    Tampil.CommandText = "update Barang set satuan='" & Trim(data3) & "'where kdbrg='" & Trim(data1) & "'"
                    Tampil.ExecuteNonQuery()
                End If
                If kolom = 4 Then
                    Tampil.Connection = Dtbase
                    Tampil.CommandType = CommandType.Text
                    Tampil.CommandText = "update Barang set harga='" & Trim(data4) & "'where kdbrg='" & Trim(data1) & "'"
                    Tampil.ExecuteNonQuery()
                End If
            End If
        End If
    End Sub

    Private Sub DG1_CellValueChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DG1.CellValueChanged
        'untuk mengambil nilai dari cell
        If IsDBNull(DG1.Item(0, baris).Value) Then
        Else
            data1 = DG1.Item(0, baris).Value

        End If
        If IsDBNull(DG1.Item(1, baris).Value) Then
        Else
            data2 = DG1.Item(1, baris).Value
        End If
        If IsDBNull(DG1.Item(2, baris).Value) Then
        Else
            data3 = DG1.Item(2, baris).Value
        End If
        If IsDBNull(DG1.Item(3, baris).Value) Then
        Else
            data4 = DG1.Item(3, baris).Value
        End If
        Call simpan()


    End Sub

    Private Sub Entry_data_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Call koneksi()
        Call isiDG11()


    End Sub
    Private Sub isiDG11()
        DS = New DataSet

        Tabel = "select kdbrg as [Kode Barang],nmbrg as [Nama Barang],satuan as [Satuan],harga as [Harga] FROM Barang "

        '"SELECT Pembayaran.no_ktw, Pembayaran.npm, Pembayaran.semester FROM Pembayaran"
        Grid = New OleDb.OleDbDataAdapter(Tabel, Dtbase)
        Grid.Fill(DS, "Barang")

        Dim Gridview As New DataView(DS.Tables("Barang"))
        DG1.DataSource = Gridview
        DG1.Columns(0).Width = 150 'untuk menentukan lebar kolom
        DG1.Columns(1).Width = 150
        DG1.Columns(2).Width = 100
        DG1.Columns(3).Width = 100

    End Sub
End Class