CODINGAN SQL SERVER

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