CREATE DATABASE PMB ON
(NAME=PMM_ROJAK,
FILENAME="D:\Datas\SQLSERVER\PMB.mdf",
SIZE=12,
MAXSIZE=100,
FILEGROWTH=2)
LOG ON
(NAME="CPMB_LOG",
FILENAME="D:\Datas\SQLSERVER\PMB.LDF",
SIZE=4 MB,
MAXSIZE=50 MB,
FILEGROWTH=2 MB)
CREATE TABLE CALONMAHASISWA
(
ID_CALON CHAR(10) NOT NULL,
NAMA_C VARCHAR(50) NOT NULL,
TMP_LAHIR VARCHAR(30),
TGL_LAHIR DATETIME,
JKEL_C VARCHAR(10),
ALAMAT VARCHAR(90),
KOTA VARCHAR(25),
TELPON VARCHAR(15),
TGL_DAFTAR DATETIME,
ID_JURUSAN CHAR(3) NOT NULL
)
create table jurusan
(
id_jurusan char(3) not null,
nama_j varchar(30) not null,
kajur varchar(30),
sekjer varchar(30),
jenjang varchar(20),
status varchar(20)
)
create table ruang
(
id_ruang char(5) not null,
letak varchar(15) not null,
lokasi varchar(20),
kapasitas decimal(10,0)
)
sp_help
drop table ruang1
alter table calonmahasiswa
add email varchar(25)
ALTER TABLE CALONMAHASISWA
ALTER COLUMN EMAIL CHAR(20)
ALTER TABLE CALONMAHASISWA
DROP COLUMN EMAIL
INSERT INTO RUANG(ID_RUANG,LETAK,LOKASI,KAPASITAS) VALUES ('101A','LANTAI 1','KAMPUS A',30)
INSERT INTO RUANG(ID_RUANG,LETAK,LOKASI,KAPASITAS) VALUES ('101B','LANTAI 1','KAMPUS B','40')
INSERT INTO RUANG(ID_RUANG,LETAK,LOKASI,KAPASITAS) VALUES ('102A','LANTAI 1','KAMPUS A',35)
INSERT INTO RUANG(ID_RUANG,LETAK,LOKASI,KAPASITAS) VALUES ('102B','LANTAI 1','KAMPUS B',35)
INSERT INTO RUANG(ID_RUANG,LETAK,LOKASI,KAPASITAS) VALUES ('103A','LANTAI 1','KAMPUS A',40)
INSERT INTO RUANG(ID_RUANG,LETAK,LOKASI,KAPASITAS) VALUES ('103B','LANTAI 1','KAMPUS B',40)
INSERT INTO RUANG(ID_RUANG,LETAK,LOKASI,KAPASITAS) VALUES ('201B','LANTAI 2','KAMPUS B',35)
INSERT INTO RUANG(ID_RUANG,LETAK,LOKASI,KAPASITAS) VALUES ('202B','LANTAI 2','KAMPUS B',30)
INSERT INTO RUANG(ID_RUANG,LETAK,LOKASI,KAPASITAS) VALUES ('203B','LANTAI 2','KAMPUS B',40)
INSERT INTO RUANG(ID_RUANG,LETAK,LOKASI,KAPASITAS) VALUES ('204B','LANTAI 2','KAMPUS B',45)
INSERT INTO RUANG(ID_RUANG,LETAK,LOKASI,KAPASITAS) VALUES ('301A','LANTAI 3','KAMPUS A',35)
INSERT INTO RUANG(ID_RUANG,LETAK,LOKASI,KAPASITAS) VALUES ('302A','LANTAI 3','KAMPUS A',40)
INSERT INTO RUANG(ID_RUANG,LETAK,LOKASI,KAPASITAS) VALUES ('303A','LANTAI 3','KAMPUS A',30)
SELECT * FROM RUANG
INSERT INTO RUANG VALUES ('201B','LANTAI 3','KAMPUS B',36)
INSERT INTO RUANG(ID_RUANG,LETAK) VALUES ('106B','LANTAI 2')
SELECT * FROM CALONMAHASISWA
INSERT INTO CALONMAHASISWA(ID_CALON,NAMA_C,TMP_LAHIR,TGL_LAHIR,JKEL_C,ALAMAT,KOTA,TELPON,TGL_DAFTAR,ID_JURUSAN) VALUES ('MOS001','BAMBANG SUTEDJO','TEGAL',20/03/1990,'PRIA','JL.BARU','BEKASI',02199985902,15/05/2011,'MI')
SELECT * FROM JURUSAN
INSERT INTO JURUSAN VALUES ('MI','MANAJEMEN INFORMATIKA','ABDUL GANI,MSc,M.Kom','Ir.AGUS SALIM,S.Kom','DIPLOMA-3','AKREDITASI B')
INSERT INTO JURUSAN VALUES ('KA','KOMPUTER AKUNTANSI','JAYA KELANA,SE,MM','DWI ARTIANTO,SE,MM','DIPLOMA-3','AKREDITASI B')
INSERT INTO JURUSAN VALUES ('TK','TEKNIK KOMPUTER','Ir.BAKRI SANJAYA,M.EngSc.','Ir.DEWI LESTARY,MT','DIPLOMA-3','AKREDITASI B')
INSERT INTO JURUSAN VALUES ('SI','SISTEM INFORMASI','Ir.BAMBANG DWI,MM,M.Kom','Ir.KLARA RESPATI,MMSI','STRATA-1','AKREDITASI B')
INSERT INTO JURUSAN VALUES ('TI','TEKNIK INFORMATIKA','Ir.PRABU KELANA,MEng,M.Kom','Ir.SUROTO,M.Kom','STRATA-1','AKREDITASI A')
INSERT INTO CALONMAHASISWA VALUES ('MOS002','MUKHRIATIN','PURBALINGGA',25/12/1991,'WANITA','TERMINAL','BEKASI',08993344,12/05/2011,'KA')
CREATE TABLE CALONMHSBARU
(
ID_CALON_BARU CHAR(10) PRIMARY KEY,
NAMA_C_BARU VARCHAR(50),
ALAMAT_BARU VARCHAR(90)
)
INSERT INTO CALONMHSBARU (ID_CALON_BARU,NAMA_C_BARU,ALAMAT_BARU)
SELECT ID_CALON,NAMA_C,ALAMAT FROM CALONMAHASISWA
SELECT * FROM CALONMHSBARU
DELETE FROM JURUSAN
WHERE ID_JURUSAN='KA'
SELECT * INTO JURUSANBARU
FROM JURUSAN
SELECT * FROM JURUSANBARU
SELECT ID_CALON,NAMA_C,ALAMAT,TELPON,NAMA_J,STATUS
INTO CALONMHSJURUSAN
FROM CALONMAHASISWA INNER JOIN JURUSAN ON CALONMAHASISWA.ID_JURUSAN=JURUSAN.ID_JURUSAN
SELECT*FROM CALONMHSJURUSAN
ALTER TABLE RUANG
ADD CONSTRAINT PKRUANG PRIMARY KEY (ID_RUANG)
DELETE FROM RUANG
WHERE ID_RUANG='201B'
ALTER TABLE JURUSAN
ADD CONSTRAINT PKJURUSAN PRIMARY KEY (ID_JURUSAN)
ALTER TABLE CALONMAHASISWA
ADD CONSTRAINT PKCALONMAHASISWA PRIMARY KEY (ID_CALON)
CREATE TABLE TEST
(
ID_CALON CHAR (10) NOT NULL,
TGL_TEST DATETIME NOT NULL,
NILAI DECIMAL (10,0),
HASIL VARCHAR (20),
ID_RUANG CHAR (5)
)
ALTER TABLE TEST
ADD CONSTRAINT PKTEST PRIMARY KEY (ID_CALON,TGL_TEST)
ALTER TABLE TEST
ADD CONSTRAINT FKTEST FOREIGN KEY (ID_CALON)
REFERENCES CALONMAHASISWA (ID_CALON)
ALTER TABLE TEST
ADD CONSTRAINT FKID_RUANG FOREIGN KEY (ID_RUANG)
REFERENCES RUANG(ID_RUANG)
ALTER TABLE CALONMAHASISWA
ADD CONSTRAINT FKCALONMAHASISWA FOREIGN KEY (ID_JURUSAN)
REFERENCES JURUSAN(ID_JURUSAN)
SELECT * FROM TEST
