CODINGAN SQL SERVER part II

CREATE DATABASE PERSONAL_METRIC ON
(NAME=PERSONAL_METRIC_DAT,
    FILENAME="D:\Datas\SQLSERVER\PERSONAL_METRIC.MDF",
    SIZE=12,
    MAXSIZE=100,
FILEGROWTH=2)
LOG ON
(NAME="CPERSONAL_METRIC_LOG",
FILENAME="D:\Datas\SQLSERVER\PERSONAL_METRIC.LDF",
SIZE=4MB,
MAXSIZE=50MB,
FILEGROWTH=2MB
)

CREATE TABLE KARYAWAN
(
NIP CHAR(4) PRIMARY KEY,
NAMA CHAR(30) NOT NULL,
KODESTATUS TINYINT,
KODEDIVISI CHAR(1) CONSTRAINT
CEKDIVISI CHECK
(KODEDIVISI IN('A','B','C','D','E','F')),
TGL_LAHIR DATETIME,
GOLONGAN CHAR(2) constraint
CEKGOL CHECK
(GOLONGAN IN('A','B','C','D','E','F','G','H','I')),
SEX TINYINT CONSTRAINT
CEKSEX CHECK
(SEX IN(1,2,3,4)),
KOTA_LAHIR VARCHAR(30),
ALAMTA VARCHAR(90),
KOTA_ALM VARCHAR(25),
TELEPON CHAR(13),
ANAK TINYINT
)
 SELECT * FROM KARYAWAN

INSERT INTO KARYAWAN VALUES(0011,'UDIN SYAMSUDIN','2','A',23/05/1973,'B',1,'JALARTA','JL.KEMANGGISAN I NO.21','JAKARTA BARAT',021-5676666,1)

INSERT INTO KARYAWAN VALUES(0012,'HAMDI SUCIPTO',3,'C',13/12/1976,'B',1,'JAKARTA','JL.KEBUN PALA 2 NO.1','JAKARTA TIMUR',0217876666,3)


INSERT INTO KARYAWAN VALUES(0013,'ASTARI NOVITA',1,'D',12/02
/1967,'A',2,'SEMARANG','JL.AHMAD YANI NO.6','JAKARTA PUSAT',021-3212222,0)

INSERT INTO KARYAWAN VALUES(0014,'DEWI RESTUTI',2,'D',31/12/1977,'A',1,'BANDUNG','JL.KEBON KACANG I NO.3','JAKARTA PUSAT',021-3231111,0)

INSERT INTO KARYAWAN VALUES(0015,'NILO LEGOWO',1,'A',11/02/1965,'B',1,'JAKARTA','JL.MAKMUR I NO.12','JAKARTA SELATAN',021-7656777,0)

INSERT INTO KARYAWAN VALUES(0016,'M.TYAS ANANTO',1,'C',23/12/1965,'A',1,'SEMARANG','JL.KEBAYORAN LAMA I NO.1','JAKARTA SELATAN',021-7672122,0)

INSERT INTO KARYAWAN VALUES(0017,'HARYANTI',1,'B',12/11/1967,'C',2,'JAKARTA','JL.PERMATA HIJAU V NO.2','JAKARTA SELATAN',021-7879899,0)

INSERT INTO KARYAWAN VALUES(0018,'SITI AMINAH',2,'B',12/09/1970,'C',2,'JAKARTA','JL.PESING KONENG NO.1','JAKARTA BARAT',021-6567888,2)

INSERT INTO KARYAWAN VALUES(0019,'SRI JUHAERIYAH',2,'E',09/07/1978,'D',2,'BANDUNG','JL.CILINCING RAYA NO.22','JAKARTA UTARA',021-5456766,1)

INSERT INTO KARYAWAN VALUES(0020,'RINI HAPSARI',2,'E',01/11/1979,'D',2,'BANDUNG','JL.KEMANG RAYA NO.7','JAKARTA SELATAN',021-7567811,4)

INSERT INTO KARYAWAN VALUES(0021,'UMI ZALFAUZIAH',2,'E',01/11/1974,'C',2,'SEMARANG','JL.KAPUK MUARA I NO.9','JAKARTA BARAT',021-6567890,1)

INSERT INTO KARYAWAN VALUES(0022,'RIFAT SAPTONO',2,'C',12/09/1978,'A',1,'BANDUNG','JL.KELAPA GADING NO.3','JAKARTA TIMUR',021-7878888,3)

INSERT INTO KARYAWAN VALUES(0023,'SONDANG SIBUEA',2,'A',12/06/1967,'D',1,'MEDAN','JL.KALIMALANG I NO.1','JAKARTA TIMUR',021-7878781,2)

INSERT INTO KARYAWAN VALUES(0024,'AL ROZAQ',2,'D',09/01/1976,'C',1,'BOGOR','JL.PAMULANG II NO.6','TANGERANG',021-7878123,3)

INSERT INTO KARYAWAN VALUES(0025,'TRI WIYATI',4,'H',11/11/68,'F',3,'SEMARANG','JL.PANGERAN KUDA NO.2','BOGOR',021-876123,2)

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

DOWNLOAD DRIVER CQ42..

jika lo butuh driver untuk laptop compaq cq42 klik ajj disini....


okeyyyy........