SQL

[NO 1]


CREATE DATABASE PELSERVIS_MOTOR_CHACHA ON
(NAME=PELSERVIS_MOTOR_CHACHA_DAT,
FILENAME="D:\Program Files\Microsoft SQL Server\MSS\Data\PELSERVIS_MOTOR_CHACHA.mdf",
SIZE=12,
MAXSIZE=100,
FILEGROWTH=2)
LOG ON
(NAME="CPELSERVIS_MOTOR_CHACHA_log",
FILENAME="D:\Program Files\Microsoft SQL Server\MSSQL\Data\PELSERVIS_MOTOR_CHACHA.ldf",
SIZE=4 MB,
MAXSIZE=50 MB,
FILEGROWTH=2 MB)



[NO 2]


CREATE TABLE MOTOR
(
NO_POLISI CHAR (10) PRIMARY KEY NOT NULL,
WARNA CHAR (10),
MERK CHAR (10),
TAHUN CHAR (4)
)

CREATE TABLE SERVIS
(
NO_FAKTUR CHAR (4) PRIMARY KEY NOT NULL,
TGL_FAKTUR DATETIME,
NO_POLISI CHAR (10) FOREIGN KEY REFERENCES MOTOR (NO_POLISI),
KD_PART CHAR (20) FOREIGN KEY REFERENCES PART (KD_PART),
QTY TINYINT
)

CREATE TABLE PART
(
KD_PART CHAR (20) PRIMARY KEY NOT NULL,
NAMA_PART CHAR (30),
HRG_SAT FLOAT
)



[NO 3]
DELETE FROM SERVIS

INSERT INTO MOTOR VALUES ('B 3117 LB','BIRU','SUPRA X','2005')
INSERT INTO MOTOR VALUES ('B 3775 JO','HITAM','SHOGUN','2005')
INSERT INTO MOTOR VALUES ('B 5118 AA','BIRU','JUPITER','2006')
INSERT INTO MOTOR VALUES ('B 4229 BB','MERAH','MIO','2007')
INSERT INTO MOTOR VALUES ('B 2557 HJ','HITAM','JUPITER','2004')

INSERT INTO SERVIS VALUES ('F001','05/20/2007','B 3117 LB','OLTI 1000 CC',1)
INSERT INTO SERVIS VALUES ('F002','05/21/2007','B 5118 AA','SERV0001',1)
INSERT INTO SERVIS VALUES ('F003','05/21/2007','B 3775 JO','OLMP 1000 CC',1)
INSERT INTO SERVIS VALUES ('F004','05/22/2007','B 4229 BB','SERV0001',1)

INSERT INTO PART VALUES ('SERV0001','ENGINE TUNE UP',25000)
INSERT INTO PART VALUES ('OLTI 1000 CC','OLI TOP ONE 1000 CC',27000)
INSERT INTO PART VALUES ('OLMP 1000 CC','OLI MESRAN PRIMA 1000 CC',15000)



[NO 4]


SELECT *
FROM MOTOR
WHERE WARNA = 'BIRU' AND TAHUN < 2006 OR RIGHT(NO_POLISI,1) = 'B'



[NO 5]


SELECT *
FROM PART
WHERE HRG_SAT BETWEEN 10000 AND 20000



[NO 6]


SELECT KD_PART , "JUMLAH PART YANG TERJUAL" = SUM (QTY)
FROM SERVIS
GROUP BY KD_PART



[NO 7]


SELECT *
FROM SERVIS
ORDER BY TGL_FAKTUR
COMPUTE SUM (QTY) BY TGL_FAKTUR



[NO 8]


SELECT NO_FAKTUR , TGL_FAKTUR , WARNA , MERK , TAHUN , QTY
FROM MOTOR
JOIN SERVIS ON MOTOR.NO_POLISI = SERVIS.NO_POLISI
WHERE MERK = 'SUPRA X' OR MERK = 'JUPITER'




[NO 9]


SELECT NO_FAKTUR , TGL_FAKTUR , QTY , NAMA_PART , HRG_SAT
FROM SERVIS
JOIN PART ON SERVIS.KD_PART = PART.KD_PART
ORDER BY NO_FAKTUR
COMPUTE COUNT (NO_FAKTUR) BY NO_FAKTUR



[NO 10]


SELECT TGL_FAKTUR , WARNA , MERK , NAMA_PART , HRG_SAT , QTY
FROM SERVIS
JOIN MOTOR ON SERVIS.NO_POLISI = MOTOR.NO_POLISI
JOIN PART ON SERVIS.KD_PART = PART.KD_PART
ORDER BY TGL_FAKTUR