Scribd
Upload
91 views4 pages

Kelompok 9 Nama: Rifa Kamilia (1314618002) Rita Septia NR. (1314618015) Muhammad Amanda. (1314618034)

The document describes the creation of a database called UAS_KELOMPOK9 containing tables for students (MAHASISWA), courses (MATKUL), professors (DOSEN), classes (KELAS), and grades (NILAI). Data is inserted into the tables and relationships are defined between them. Queries are written to display student names and grades for a specific course, sum the grades for a student over 2 years, and create a view and trigger between the tables.

Uploaded by

Rifa Kamellia
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
91 views4 pages

Kelompok 9 Nama: Rifa Kamilia (1314618002) Rita Septia NR. (1314618015) Muhammad Amanda. (1314618034)

The document describes the creation of a database called UAS_KELOMPOK9 containing tables for students (MAHASISWA), courses (MATKUL), professors (DOSEN), classes (KELAS), and grades (NILAI). Data is inserted into the tables and relationships are defined between them. Queries are written to display student names and grades for a specific course, sum the grades for a student over 2 years, and create a view and trigger between the tables.

Uploaded by

Rifa Kamellia
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 4

Kelompok 9

Nama : Rifa Kamilia (1314618002)

Rita Septia NR.(1314618015)

Muhammad Amanda.(1314618034)

A. TABEL DOSEN

B. TABEL KELAS

C. TABEL MAHASISWA

D. TABEL MATKUL
E. TABEL NILAI

QUERRY

CREATE DATABASE UAS_KELOMPOK9;

CREATE TABLE MAHASISWA (


NRM INT IDENTITY (1,1) NOT NULL ,
NM_MAHASISWA VARCHAR (50) NOT NULL,
TOTAL_NILAI INT NOT NULL,
CONSTRAINT MAHASISWA_PK PRIMARY KEY (NRM));

INSERT INTO MAHASISWA(NM_MAHASISWA, TOTAL_NILAI)


VALUES
('RIFA KAMILIA','24'),
('RITA SEPTIA NR','30'),
('MUHAMMAD AMANDA','29'),
('ANJUITA','24'),
('INDAH LESTARI','25');

CREATE TABLE MATKUL (


ID_MATKUL INT IDENTITY (1,1) NOT NULL ,
NM_MATKUL VARCHAR (50) NOT NULL,
SKS INT NOT NULL,
CONSTRAINT MATKUL_PK PRIMARY KEY (ID_MATKUL));

INSERT INTO MATKUL(NM_MATKUL, SKS)


VALUES
('ALGORITMA PEMROGRAMAN', '3'),
('STATISTIKA DASAR','3'),
('OLIMPISME','1'),
('KEWIRAUSAHAAN','2'),
('PANCASILA','2');

CREATE TABLE DOSEN (


ID_DOSEN INT IDENTITY (1,1) NOT NULL ,
NM_DOSEN VARCHAR (50) NOT NULL,
CONSTRAINT DOSEN_PK PRIMARY KEY (ID_DOSEN));

INSERT INTO DOSEN (NM_DOSEN)


VALUES
('MED IRZAL'),
('DANIA SIREGAR'),
('VERA MAYA SANTI'),
('SITI ROHMAH ROHIMAH'),
('WALUYO HADI');

CREATE TABLE KELAS (


ID_KELAS INT IDENTITY (1,1) NOT NULL ,
RUANGAN VARCHAR (50) NOT NULL,
CONSTRAINT KELAS_PK PRIMARY KEY (ID_KELAS),
ID_MATKUL INT NOT NULL FOREIGN KEY REFERENCES MATKUL(ID_MATKUL),
ID_DOSEN INT NOT NULL FOREIGN KEY REFERENCES DOSEN(ID_DOSEN));

INSERT INTO KELAS(RUANGAN, ID_MATKUL,ID_DOSEN)


VALUES
('GDS512','1','1'),
('GDS612','2','2'),
('GDS608','3','3'),
('GDS612','4','4'),
('GDS916','5','5');

CREATE TABLE NILAI (


KODE_NILAI INT IDENTITY (1,1) NOT NULL,
NILAI INT NOT NULL,
SKS INT NOT NULL,
TANGGAL DATETIME NOT NULL,
CONSTRAINT NILAI_PK PRIMARY KEY (KODE_NILAI),
NRM INT NOT NULL FOREIGN KEY REFERENCES MAHASISWA(NRM),
ID_MATKUL INT NOT NULL FOREIGN KEY REFERENCES MATKUL(ID_MATKUL));

INSERT INTO NILAI(NRM, ID_MATKUL, SKS, TANGGAL, NILAI)


VALUES
('1','1','3','01/27/2019','3'),
('1','2','3','07/12/2019','9'),
('1','5','2','01/25/2020','8'),
('1','3','1','07/18/2020','4'),
('2','2','3','01/27/2019','9'),
('2','3','1','07/12/2019','4'),
('2','1','3','01/25/2020','9'),
('2','5','2','07/18/2020','8'),
('3','1','3','01/27/2019','9'),
('3','4','2','07/12/2019','8'),
('3','5','2','01/25/2020','8'),
('3','3','1','07/18/2020','4'),
('4','5','2','01/27/2019','8'),
('4','4','2','07/12/2019','4'),
('4','1','3','01/25/2020','3'),
('4','2','3','07/18/2020','9'),
('5','4','2','01/27/2019','4'),
('5','3','1','07/12/2019','4'),
('5','1','3','01/25/2020','9'),
('5','5','2','07/18/2020','8');

--Tampilkan Nama Mahasiswa, Mata Kuliah yang diambil beserta Nilai nya.
SELECT NRM, NM_MAHASISWA
From MAHASISWA
where NRM in
(Select NRM
from NILAI
where ID_MATKUL in
(select ID_MATKUL
from MATKUL
where NM_MATKUL='ALGORITMA PEMROGRAMAN'))

--JUMLAH NILAI RIFA DALAM 2 TAHUN


SELECT SUM(NILAI)
FROM NILAI
WHERE NRM IN
(SELECT NRM
FROM MAHASISWA
WHERE NM_MAHASISWA = 'INDAH LESTARI')

--VIEW:
SELECT dbo.MAHASISWA.*, dbo.NILAI.*
FROM dbo.MAHASISWA INNER JOIN
dbo.NILAI ON dbo.MAHASISWA.NRM = dbo.NILAI.NRM

--TRIGGER:
create trigger tambah_NILAI on NILAI
for insert
as
update MAHASISWA set MAHASISWA.TOTAL_NILAI = MAHASISWA.TOTAL_NILAI + NILAI.NILAI
from MAHASISWA join inserted NILAI on MAHASISWA.TOTAL_NILAI = NILAI.NILAI

You might also like