# [2] Basis Data - PERTEMUAN 11
## STUDI KASUS SQL SERVER 2005
### Live Preview
- [Click Me](https://hackmd.io/@novaardiansyah/S1Z-rd34p)
## I. STUDI KASUS 7.1
Mulai dari buat database,
```SQL=
CREATE DATABASE UNPAM;
USE UNPAM;
```
1. Buat tabel karyawan, tentukan dan bentuk primary key dengan fungsi alter.
```sql=
CREATE TABLE TblKaryawan
(
NIP VARCHAR(50) NOT NULL,
NamaKar VARCHAR(255),
TmptLahir VARCHAR(255),
TglLahir DATE,
JK CHAR(1),
KodeStatus INT,
Alamat VARCHAR(255),
Kota VARCHAR(255),
NoTelp VARCHAR(20),
KodeDevisi CHAR(1),
Gol CHAR(2),
JmlAnak INT
);
ALTER TABLE TblKaryawan ADD PRIMARY KEY (NIP);
```
2. Buat tabel divisi, tentukan dan bentuk primary key dengan nama constraint PK_Divisi.
```sql=
CREATE TABLE TblDevisi
(
KodeDevisi CHAR(1) NOT NULL,
NamaDevisi VARCHAR(255),
PRIMARY KEY CLUSTERED (KodeDevisi)
)
```
3. Buat tabel gaji, tentukan dan bentuk primery key pada saat create table.
```sql=
CREATE TABLE TblGaji
(
Gol CHAR(2) NOT NULL,
GajiPokok INT,
PRIMARY KEY CLUSTERED (Gol)
)
CREATE TABLE TblStatus
(
KodeStatus INT NOT NULL,
NamaStatus VARCHAR(255),
PRIMARY KEY CLUSTERED (KodeStatus)
)
```
4. Buat relasi tabel karyawan dengan divisi, beri nama constraint FK_Karyawan_Divisi.
```sql=
ALTER TABLE TblKaryawan
ADD CONSTRAINT FK_Karyawan_Divisi
FOREIGN KEY (KodeDevisi)
REFERENCES TblDevisi(KodeDevisi);
```
5. Buat relasi tabel karyawan dengan gaji, beri nama constraint FK_Karyawan_Gaji.
```sql=
ALTER TABLE TblKaryawan
ADD CONSTRAINT FK_Karyawan_Gaji
FOREIGN KEY (Gol)
REFERENCES TblGaji(Gol);
```
6. Buat relasi tabel karyawan dengan status, beri nama constraint FK_Karyawan_Status.
```sql=
ALTER TABLE TblKaryawan
ADD CONSTRAINT FK_Karyawan_Status
FOREIGN KEY (KodeStatus)
REFERENCES TblStatus(KodeStatus);
```
7. Buat diagram database dengan nama DiagramKaryawan
8. Pada tabel gaji gunakan perintah INSERT untuk memasukan data sebagai berikut:
```sql=
INSERT INTO TblGaji (Gol, GajiPokok) VALUES ('A', '750000'),
('B', '1000000'),
('C', '1250000'),
('D', '1500000'),
('E', '1750000'),
('F', '2000000'),
('G', '2500000'),
('H', '3000000'),
('I', '4000000'),
('J', '5000000')
```
9. Pada tabel divisi gunakan perintah INSERT untuk memasukan data sebagai berikut :
```sql=
INSERT INTO TblDevisi (KodeDevisi, NamaDevisi) VALUES ('A', 'Direktur'),
('B', 'Direktur'),
('C', 'Manajer'),
('D', 'Personalia'),
('E', 'Marketing'),
('F', 'Produksi'),
('G', 'Warehouse'),
('H', 'Accounting'),
('I', 'Umum'),
('J', 'Sales')
```
10. Pada tabel status gunakan perintah INSERT untuk memasukan data sebagai berikut :
```sql=
INSERT INTO TblStatus(KodeStatus, NamaStatus) VALUES ('1', 'Tidak'),
('2', 'Kawin'),
('3', 'Duda'),
('4', 'Janda')
```
11. Pada tabel karyawan gunakan perintah INSERT untuk memasukan data sebagai berikut :
```sql=
INSERT INTO TblKaryawan (NIP, NamaKar, TmptLahir, TglLahir, JK, KodeStatus, Alamat, Kota, NoTelp, KodeDevisi, Gol, JmlAnak) VALUES ('0101001', 'DEWI', 'JAKARTA', '5/23/1977', 'P', '2', 'JL.RAYA BOGOR 21', 'BOGOR', '02511234567', 'H', 'C', '2'),
('0101002', 'ASTRI', 'TANGERANG', '2/12/1980', 'P', '1', 'JL.SUKA SENANG 2', 'TANGERANG', '0219737292', 'E', 'B', NULL),
('1012001', 'ACHMAD', 'BANDUNG', '12/12/1988', 'L', '2', 'JL.BUKIT DAGO', 'JAKARTA', '0211232121', 'B', 'I', '1'),
('0201003', 'UDIN', 'BOGOR', '10/9/1985', 'L', '1', 'PAMULANG 2', 'TANGERANG', '0217866767', 'G', 'E', NULL),
('0311021', 'FITRI', 'SUKABUMI', '10/15/1979', 'P', '2', 'JL.RAYA SUKABUMI', 'SUKABUMI', '025223232', 'J', 'D', '1')
```
12. Tampilkan total jumlah anak pada seluruh karyawan, seperti dibawah ini :
```sql=
SELECT SUM(JmlAnak) AS 'TOTAL JUMLAH ANAK' FROM TblKaryawan
```
13. Hitung dan tampilkan jumlah karyawan yang berstatus kawin, seperti dibawah ini :
```sql=
SELECT COUNT(*) AS 'JUMLAH STATUS KAWIN' FROM TblKaryawan WHERE KodeStatus = 2
```
14. Tampilkan data karyawan (NIP, Nama Karyawan, Status) dari tabel karyawan dan tabel status, dimana status = 2 (kawin), seperti dibawah ini :
```sql=
SELECT NIP, NamaKar AS 'Nama Karyawan', b.NamaStatus AS status FROM TblKaryawan AS a
INNER JOIN TblStatus AS b ON a.KodeStatus = b.KodeStatus
WHERE a.KodeStatus = 2
```
15. Tampilkan data karyawan dengan mengurutkan data berdasarkan nama karyawan secara descending
```sql=
SELECT * FROM TblKaryawan ORDER BY NamaKar DESC;
```
16. Tampilkan data karyawan (NIP, Nama, Jenis Kelamin, Kode Status, Kode Divisi, Golongan, Jumlah Anak) dan hitung jumlah anak dan karyawan pada akhir baris, seperti dibawah ini :
```sql=
SELECT NIP, NamaKar, Jk, KodeStatus, KodeDevisi, Gol, JmlAnak FROM TblKaryawan
SELECT COUNT(TblKaryawan.NamaKar) AS JmlKaryawan, SUM(TblKaryawan.JmlAnak) AS JmlAnak FROM TblKaryawan
```
17. Tampilkan data karyawan (NIP, Nama, Tempat Lahir, Jenis Kelamin, Alamat, Kota, Jumlah Anak) secara berurutan berdasarkan kota dan hitung jumlah karyawan dan jumlah anak, seperti dibawah ini
```sql=
SELECT NIP, NamaKar AS Nama, TmptLahir AS 'Tempat Lahir', JK AS 'Jenis Kelamin', Alamat, Kota, JmlAnak FROM TblKaryawan ORDER BY Kota;
SELECT Kota, COUNT(NamaKar) AS JmlKaryawan, SUM(JmlAnak) AS JmlAnak FROM TblKaryawan GROUP BY Kota ORDER BY Kota;
```
18. Tampilkan jumlah karyawan yang kawin (KodeStatus=2) berdasarkan golongan dan kota Jakarta atau Sukabumi, seperti dibawah ini :
```sql=
SELECT Gol AS 'Golongan', Kota, COUNT(NamaKar) AS JmlKaryawanKawin FROM TblKaryawan WHERE KodeStatus = 2 AND (Kota = 'Jakarta' OR Kota = 'Sukabumi') GROUP BY Gol, Kota ORDER BY Gol, Kota;
```
19. Tampilkan jumlah anak berdasarkan kota dari karyawan dengan divisi = B, seperti dibawah ini :
```sql=
SELECT Kota, SUM(JmlAnak) AS JmlAnak FROM TblKaryawan WHERE KodeDevisi = 'B' GROUP BY Kota ORDER BY Kota;
```
20. Simpan query tersebut dengan nama StudiKasus
## II. STUDI KASUS 7.2
1. Tabel Jurusan (TblJurusan)
```sql=
CREATE TABLE TblJurusan
(
KodeJur NCHAR(3) NOT NULL,
NamaJur NCHAR(35) NOT NULL,
Jenjang NCHAR(2) NOT NULL,
KaJur NCHAR(35) NOT NULL,
PRIMARY KEY CLUSTERED (KodeJur)
)
```
2. Tabel Mahasiswa (TblMahasiswa)
```sql=
CREATE TABLE TblMahasiswa
(
NIM NCHAR(10) NOT NULL,
NamaMHS NCHAR(35) NOT NULL,
TmptLahir NCHAR(26) NOT NULL,
TglLahir DATETIME NOT NULL,
JKelamin NCHAR(1) NOT NULL,
Agama NCHAR(10) NOT NULL,
Alamat NCHAR(100),
Kota NCHAR(26),
KodePos NCHAR(5),
NoTelp NCHAR(15),
KodeJur NCHAR(3),
PRIMARY KEY CLUSTERED (NIM)
)
```
3. Tabel Matakuliah (TblMatakuliah)
```sql=
CREATE TABLE TblMataKuliah
(
KodeMK NCHAR(5) NOT NULL,
NamaMK NCHAR(35) NOT NULL,
SKS INT NOT NULL,
Semester NCHAR(1) NOT NULL,
PRIMARY KEY CLUSTERED (KodeMK)
)
```
4. Tabel Nilai (TblNilai)
```sql=
CREATE TABLE TblNilai
(
ThnAkademik NCHAR(4) NOT NULL,
Semester NCHAR(1) NOT NULL,
NIM NCHAR(10) NOT NULL,
KodeMK NCHAR(5) NOT NULL,
NoDos NCHAR(3),
NilaiAbsen INT,
NilaiTugas INT,
NilaiUTS INT,
NilaiUAS INT,
PRIMARY KEY CLUSTERED (ThnAkademik)
)
ALTER TABLE TblNilai ADD CONSTRAINT FK_Nilai_NIM FOREIGN KEY (NIM) REFERENCES TblMahasiswa(NIM);
ALTER TABLE TblNilai ADD CONSTRAINT FK_Nilai_KodeMK FOREIGN KEY (KodeMK) REFERENCES TblMataKuliah(KodeMK);
```
5. Tabel Dosen (TblDosen)
```sql=
CREATE TABLE TblDosen
(
NoDos NCHAR(2) NOT NULL,
NamaDos NCHAR(35) NOT NULL,
TmptLahir NCHAR(25) NOT NULL,
TglLahir DATETIME NOT NULL,
JKelamin NCHAR(1) NOT NULL,
Agama NCHAR(10) NOT NULL,
Alamat NCHAR(100),
Kota NCHAR(25),
Kodepos NCHAR(5),
GajiPokok MONEY,
PRIMARY KEY CLUSTERED (NoDos)
)
```
Sign in with Wallet
Connect another wallet