Pemrograman SQL Server

Pada kali ini saya akan membahas cara membuat database pada SQL Server. Kasus yang akan saya bahas mengenai data Akademik yang salah satunya saya gunakan untuk Ujian Tengah Semester di perguruan tinggi swasta.
Perintah membuat database

CREATE DATABASE akademik

Perintah membuat database yang langsung ber-relasi dengan table yang lain

CREATE TABLE jurusan (
 kodejurusan varchar(5) NOT NULL,
 jurusan varchar(50) NOT NULL,
 PRIMARY KEY (kodejurusan)
)
CREATE TABLE mahasiswa (
 nim varchar(15) NOT NULL,
 nama varchar(50) NOT NULL,
 jeniskelamin char(2) NOT NULL,
 alamat varchar(100) NOT NULL,
 kota varchar(30) NOT NULL,
 kodejurusan varchar(5) NOT NULL,
 PRIMARY KEY (nim),
 FOREIGN KEY (kodejurusan) REFERENCES jurusan(kodejurusan)
)
CREATE TABLE dosen(
 kodedosen char(20) NOT NULL,
 namadosen varchar(50) NOT NULL,
 jk char(2) NOT NULL,
 alamat varchar(50) NOT NULL,
 kota varchar(30) NOT NULL,
 PRIMARY KEY (kodedosen)
)
CREATE TABLE mata_kuliah(
 kodemk char(10) NOT NULL,
 namamk varchar(50) NOT NULL,
 sks int NOT NULL,
 smt int NOT NULL,
 kodedosen char(20) NOT NULL,
 PRIMARY KEY (kodemk),
 FOREIGN KEY (kodedosen) REFERENCES dosen(kodedosen)
)
CREATE TABLE krs (
 thakad char(10) NOT NULL,
 nim varchar(15) NOT NULL,
 kodemk char(10) NOT NULL,
 nilaiuts int NOT NULL,
 nilaiuas int NOT NULL,
 PRIMARY KEY (thakad,nim,kodemk),
 FOREIGN KEY (nim) REFERENCES mahasiswa(nim),
 FOREIGN KEY (kodemk) REFERENCES mata_kuliah(kodemk)
)

Dilhat menggunakan DIAGRAM
Berdasarkan Soal No.1 Perintah mengisikan table:

INSERT INTO jurusan VALUES('A001','SISTEM INFORMASI')
INSERT INTO jurusan VALUES('A002','TEKNIK INFORMATIKA')

INSERT INTO dosen VALUES('D001','Deddy Rusdiansyah','L','Cimucang Sidomuncul','SERANG')
INSERT INTO dosen VALUES('D002','Messi','L','Penancangan','SERANG')
INSERT INTO dosen VALUES('D003','Kartini','P','Ciceri Indah','SERANG')
INSERT INTO dosen VALUES('D004','Linus','L','Bengala','SERANG')

/*jawaban no.1 */
INSERT INTO mahasiswa VALUES('A11001','Agus','L','Jl.KH Abdul Latif No.3','SERANG','A001')
INSERT INTO mahasiswa VALUES('A11002','Udin','L','Jl. Jend.Sudirman No.44','SERANG','A001')
INSERT INTO mahasiswa VALUES('A21001','Rudi','L','Jl.A.Yani No.59','SERANG','A002')
INSERT INTO mahasiswa VALUES('A21002','Obama','L','Jl.A.Yani No.159','SERANG','A002')
SELECT * FROM mahasiswa

INSERT INTO mata_kuliah VALUES('MK001','Pemrograman SQL Server','3','2','D001')
INSERT INTO mata_kuliah VALUES('MK002','Pemrograman Web','3','2','D002')
INSERT INTO mata_kuliah VALUES('MK003','Pemrograman Javascript','2','2','D001')
INSERT INTO mata_kuliah VALUES('MK004','Operating System Linux','2','2','D004')
SELECT * FROM mata_kuliah

INSERT INTO krs VALUES('2012/2013','A11001','MK001','60','70')
INSERT INTO krs VALUES('2012/2013','A11001','MK002','50','60')
INSERT INTO krs VALUES('2012/2013','A11001','MK003','70','70')
INSERT INTO krs VALUES('2012/2013','A11001','MK004','60','60')
INSERT INTO krs VALUES('2012/2013','A11002','MK001','80','70')
INSERT INTO krs VALUES('2012/2013','A11002','MK002','79','60')
INSERT INTO krs VALUES('2012/2013','A11002','MK003','90','70')
INSERT INTO krs VALUES('2012/2013','A11002','MK004','90','79')

Perintah Mengubah table :

/*jawaban no.2 */
UPDATE mahasiswa SET alamat='Jl. Bunga Mawar No.111' WHERE nim='A21002'
UPDATE mata_kuliah SET sks='3',smt='3' WHERE kodemk='MK003'
UPDATE krs SET nilaiuts=50,nilaiuas=60 WHERE thakad='2012/2013' AND nim='A11001' AND kodemk='MK003'

Perintah menghapus record :

/*jawaban no.3 */
DELETE FROM mahasiswa WHERE nim='A11002'
DELETE FROM mata_kuliah WHERE kodemk='MK002' OR kodemk='MK004'
DELETE FROM krs WHERE nilaiuts<=60 AND nilaiuas<=60