# data[].attributes.attachment_link.readable_text
8.select CategoryName, Vehicle_Model from category inner join vehicle
on category.CategoryID = vehicle.Vehicle_CategoryID
where vehicle.Vehicle_Price = (select min(Vehicle_Price)from vehicle);

9.select Vehicle_Model, Vehicle_Price from vehicle
where Vehicle_Year > 2000
and Vehicle_CategoryID = (SELECT CategoryID from category where CategoryName = "SUV");

10.select * from vehicle where Vehicle_Price > (select avg(Vehicle_Price) from vehicle); 

13.select Vehicle_ID, Vehicle_Model, Vehicle_Price, Vehicle_Price * (1.0-0.15) as 'After 15% Depreciation'  from vehicle;

# data[].attributes.attachment_link.readable_text
CREATE DATABASE VEHICLE;
USE VEHICLE;

CREATE TABLE CATEGORY(
CATEGORYID INT(3) NOT NULL PRIMARY KEY AUTO_INCREMENT,
CATEGORYNAME VARCHAR(100) NOT NULL UNIQUE KEY,
CATEGORYDESCRIPTION VARCHAR(150) NOT NULL
);

CREATE TABLE VEHICLE(
VEHICLE_ID INT(3) NOT NULL PRIMARY KEY AUTO_INCREMENT,
VEHICLE_YEAR INT(4) NOT NULL,
VEHICLE_MODEL VARCHAR(100) NOT NULL,
VEHICLE_PRICE DECIMAL(10,2) NOT NULL,
VEHICLE_CATEGORYID INT(3), 
FOREIGN KEY (VEHICLE_CATEGORYID) REFERENCES CATEGORY(CATEGORYID)
);

INSERT INTO CATEGORY
(CATEGORYID, CATEGORYNAME, CATEGORYDESCRIPTION)
VALUES
(1, "MPV", "MULTIPURPOSE VEHICLE"),
(2, "CONVERTIBLE", "AUTOMOBILE IN WHICH THE ROOF CAN RETRACT"),
(3, "SUV", "SPORT UTILITY VEHICLE"),
(4, "COUPE", "CLOSED BODY TYPE WITH TWO DOORS");

INSERT INTO VEHICLE
(VEHICLE_ID, VEHICLE_YEAR, VEHICLE_MODEL, VEHICLE_PRICE, VEHICLE_CATEGORYID)
VALUES
(1, 1980, "FORD FALAXY", 909000.00, 1),
(2, 2011, "MITSUBISHI PAJERO", 102300.00, 3),
(3, 2010, "BMW 635CSi", 217000.00, 4),
(4, 1999, "JEEP WRANGLER", 78900.00, 3),
(5, 2001, "TOYOTA AVANZA", 81200.00, 4),
(6, 2005, "AUDI TT", 107900.00, 4),
(7, 2009, "BMW 6 SERIES", 171000.00, 2);

SELECT CATEGORYNAME , VEHICLE_MODEL FROM CATEGORY INNER JOIN VEHICLE 
ON CATEGORY.CATEGORYID = VEHICLE.VEHICLE_CATEGORYID 
WHERE VEHICLE.VEHICLE_PRICE = (SELECT MIN(VEHICLE_PRICE) FROM VEHICLE);

SELECT VEHICLE_MODEL, VEHICLE_PRICE FROM VEHICLE 
WHERE VEHICLE_YEAR > 2000 
AND VEHICLE_CATEGORYID = 
(SELECT CATEGORYID FROM CATEGORY WHERE CATEGORYNAME = "SUV");

SELECT * FROM VEHICLE WHERE VEHICLE_PRICE > (SELECT AVG(VEHICLE_PRICE) FROM VEHICLE);

SELECT VEHICLE_ID, VEHICLE_MODEL, VEHICLE_PRICE, VEHICLE_PRICE * (1.0-0.15) FROM VEHICLE;

# data[].attributes.headline
IZZ DANISH AQLAN BIN BADRUL HISHAM (1161202598)

# data[].attributes.subject
IZZ DANISH AQLAN BIN BADRUL HISHAM (1161202598)

# data[].attributes.attachment_link.readable_text
8.select CategoryName, Vehicle_Model from category inner join vehicle
on category.CategoryID = vehicle.Vehicle_CategoryID
where vehicle.Vehicle_Price = (select min(Vehicle_Price)from vehicle);

9.select Vehicle_Model, Vehicle_Price from vehicle
where Vehicle_Year > 2000
and Vehicle_CategoryID = (SELECT CategoryID from category where CategoryName = "SUV");

10.select * from vehicle where Vehicle_Price > (select avg(Vehicle_Price) from vehicle); 

13.select Vehicle_ID, Vehicle_Model, Vehicle_Price, Vehicle_Price * (1.0-0.15) as 'After 15% Depreciation'  from vehicle;

# data[].attributes.headline
Mohamad Amirul Amri Bin Jantan (1161202335)

# data[].attributes.subject
Mohamad Amirul Amri Bin Jantan (1161202335)

# data[].attributes.attachment_link.readable_text
8.select CategoryName, Vehicle_Model from category inner join vehicle
on category.CategoryID = vehicle.Vehicle_CategoryID
where vehicle.Vehicle_Price = (select min(Vehicle_Price)from vehicle);

9.select Vehicle_Model, Vehicle_Price from vehicle
where Vehicle_Year > 2000
and Vehicle_CategoryID = (SELECT CategoryID from category where CategoryName = "SUV");

10.select * from vehicle where Vehicle_Price > (select avg(Vehicle_Price) from vehicle); 

13.select Vehicle_ID, Vehicle_Model, Vehicle_Price, Vehicle_Price * (1.0-0.15) as 'After 15% Depreciation'  from vehicle;

# data[].attributes.headline
DANISH AFFIQ BIN MUSTAFFA KAMAL (1161200249)

# data[].attributes.subject
DANISH AFFIQ BIN MUSTAFFA KAMAL (1161200249)

# data[].attributes.headline
MOHAMAD AMIRUL ARIFF BIN MOHAMAD YUSOF (1161200646)

# data[].attributes.subject
MOHAMAD AMIRUL ARIFF BIN MOHAMAD YUSOF (1161200646)

# data[].attributes.attachment_link.readable_text
CREATE DATABASE VEHICLE;
USE VEHICLE;

CREATE TABLE CATEGORY(
CATEGORYID INT(3) NOT NULL PRIMARY KEY AUTO_INCREMENT,
CATEGORYNAME VARCHAR(100) NOT NULL UNIQUE KEY,
CATEGORYDESCRIPTION VARCHAR(150) NOT NULL
);

CREATE TABLE VEHICLE(
VEHICLE_ID INT(3) NOT NULL PRIMARY KEY AUTO_INCREMENT,
VEHICLE_YEAR INT(4) NOT NULL,
VEHICLE_MODEL VARCHAR(100) NOT NULL,
VEHICLE_PRICE DECIMAL(10,2) NOT NULL,
VEHICLE_CATEGORYID INT(3), 
FOREIGN KEY (VEHICLE_CATEGORYID) REFERENCES CATEGORY(CATEGORYID)
);

INSERT INTO CATEGORY
(CATEGORYID, CATEGORYNAME, CATEGORYDESCRIPTION)
VALUES
(1, "MPV", "MULTIPURPOSE VEHICLE"),
(2, "CONVERTIBLE", "AUTOMOBILE IN WHICH THE ROOF CAN RETRACT"),
(3, "SUV", "SPORT UTILITY VEHICLE"),
(4, "COUPE", "CLOSED BODY TYPE WITH TWO DOORS");

INSERT INTO VEHICLE
(VEHICLE_ID, VEHICLE_YEAR, VEHICLE_MODEL, VEHICLE_PRICE, VEHICLE_CATEGORYID)
VALUES
(1, 1980, "FORD FALAXY", 909000.00, 1),
(2, 2011, "MITSUBISHI PAJERO", 102300.00, 3),
(3, 2010, "BMW 635CSi", 217000.00, 4),
(4, 1999, "JEEP WRANGLER", 78900.00, 3),
(5, 2001, "TOYOTA AVANZA", 81200.00, 4),
(6, 2005, "AUDI TT", 107900.00, 4),
(7, 2009, "BMW 6 SERIES", 171000.00, 2);

SELECT CATEGORYNAME , VEHICLE_MODEL FROM CATEGORY INNER JOIN VEHICLE 
ON CATEGORY.CATEGORYID = VEHICLE.VEHICLE_CATEGORYID 
WHERE VEHICLE.VEHICLE_PRICE = (SELECT MIN(VEHICLE_PRICE) FROM VEHICLE);

SELECT VEHICLE_MODEL, VEHICLE_PRICE FROM VEHICLE 
WHERE VEHICLE_YEAR > 2000 
AND VEHICLE_CATEGORYID = 
(SELECT CATEGORYID FROM CATEGORY WHERE CATEGORYNAME = "SUV");

SELECT * FROM VEHICLE WHERE VEHICLE_PRICE > (SELECT AVG(VEHICLE_PRICE) FROM VEHICLE);

SELECT VEHICLE_ID, VEHICLE_MODEL, VEHICLE_PRICE, VEHICLE_PRICE * (1.0-0.15) FROM VEHICLE;

# data[].attributes.attachment_link.readable_text
8. SELECT category_name, vehicle_model FROM category, vehicle
WHERE category.category_ID = vehicle.Vehicle_ID 
and vehicle_price =  (SELECT MIN(vehicle_price) FROM vehicle);

9. SELECT Vehicle_Model, Vehicle_Price FROM vehicle, category
WHERE category.CategoryID = VEHICLE.Vehicle_CategoryID
and Vehicle_Year > 2000 and CategoryName = 'SUV';

10. SELECT * FROM VEHICLE WHERE Vehicle_Price > (SELECT AVG(Vehicle_Price) FROM VEHICLE);

13. SELECT vehicle_ID , vehicle_Model, vehicle_Price, ROUND(vehicle_Price * (0.85), 2) AS 'After 15% Depreciation' FROM vehicle;



# data[].attributes.body
AIZA AKMAL FADHILA BINTI AZRI (1161201835)

# data[].attributes.attachment_link.readable_text
8) 
SELECT CATEGORYNAME , VEHICLE_MODEL FROM CATEGORY INNER JOIN VEHICLE 
ON CATEGORY.CATEGORYID = VEHICLE.VEHICLE_CATEGORYID 
WHERE VEHICLE.VEHICLE_PRICE = (SELECT MIN(VEHICLE_PRICE) FROM VEHICLE);


9)
SELECT VEHICLE_MODEL, VEHICLE_PRICE FROM VEHICLE 
WHERE VEHICLE_YEAR > 2000 
AND VEHICLE_CATEGORYID = 
(SELECT CATEGORYID FROM CATEGORY WHERE CATEGORYNAME = "SUV");

10)
SELECT * FROM VEHICLE WHERE VEHICLE_PRICE > (SELECT AVG(VEHICLE_PRICE) FROM VEHICLE);

13)
SELECT VEHICLE_ID, VEHICLE_MODEL, VEHICLE_PRICE, VEHICLE_PRICE * (1.0-0.15) FROM VEHICLE;

# data[].attributes.headline
Raja Muhammad Aidid bin Raja Aris Fadillah

# data[].attributes.subject
Raja Muhammad Aidid bin Raja Aris Fadillah

# data[].attributes.body
(1161200930)

# data[].attributes.headline
Muhammad Adam Faris Bin Nordin(1161202700)

# data[].attributes.subject
Muhammad Adam Faris Bin Nordin(1161202700)

# data[].attributes.attachment_link.readable_text
8.select CategoryName, Vehicle_Model from category inner join vehicle
on category.CategoryID = vehicle.Vehicle_CategoryID
where vehicle.Vehicle_Price = (select min(Vehicle_Price)from vehicle);

9.select Vehicle_Model, Vehicle_Price from vehicle
where Vehicle_Year > 2000
and Vehicle_CategoryID = (SELECT CategoryID from category where CategoryName = "SUV");

10.select * from vehicle where Vehicle_Price > (select avg(Vehicle_Price) from vehicle); 

13.select Vehicle_ID, Vehicle_Model, Vehicle_Price, Vehicle_Price * (1.0-0.15) as 'After 15% Depreciation'  from vehicle;

# data[].attributes.attachment_link.readable_text
8. SELECT CategoryName, Vehicle_Model FROM CATEGORY INNER JOIN VEHICLE
ON category.CategoryID = vehicle.Vehicle_CategoryID
WHERE vehicle.Vehicle_Price = (SELECT MIN(Vehicle_Price) FROM VEHICLE);

9. SELECT Vehicle_Model, Vehicle_Price FROM VEHICLE
WHERE Vehicle_Year > 2000
AND Vehicle_CategoryID = (SELECT CategoryID FROM CATEGORY WHERE CategoryName = "SUV");

10. SELECT * FROM VEHICLE 
WHERE Vehicle_Price > (SELECT AVG(Vehicle_Price) FROM VEHICLE); 

13. SELECT Vehicle_ID, Vehicle_Model, Vehicle_Price, Vehicle_Price * (1.0-0.15) as 'After 15% Depreciation'  FROM VEHICLE;

# data[].attributes.headline
IZZAH NURHANA BINTI MOHAMMAD RASDAN (1161101841)

# data[].attributes.subject
IZZAH NURHANA BINTI MOHAMMAD RASDAN (1161101841)

# data[].attributes.headline
Muhammad Shafiq Bin Mohd Jalani(1161202423

# data[].attributes.subject
Muhammad Shafiq Bin Mohd Jalani(1161202423

# data[].attributes.headline
Luqman Hakim Zahir Bin Afandi (1161200826)

# data[].attributes.subject
Luqman Hakim Zahir Bin Afandi (1161200826)

# data[].attributes.attachment_link.readable_text
1.
create database vehicle
use vehicle
2.
Create table
create table category(
categoryid int(3) not null primary key auto_increment,
categoryname varchar(100) not null unique,
categorydescription varchar(150) not null
)
3.
Create table
create table vehicle(
vehicleid int(3) not null primary key auto_increment,
vehicleyear int(4) not null,
vehiclemodel varchar(100) not null,
vehicleprice decimal(10,2) not null,
vehiclecategoryid int(3) null,
INDEX (vehiclecategoryid) //for MUL or index
)
4.
Insert data
insert into category
(categoryid, categoryname, categorydescription)
values
(1, 'MPV','Multipurpose Vehicle'),
(2,'Convertible','Automobile in which the roof can retract'),
(3,'SUV','Sport Utility vehicle'),
(4,'Coupe ','Closed body type with two doors');
5,
Insert data
insert into vehicle
(vehicleid, vehicleyear, vehiclemodel, vehicleprice, vehiclecategoryid)
values
(1, 1980,'Ford Galaxy',909000.00,1),
(2,2011,'Mitshubishi Pajero',102300.00,3),
(3,2010,'BMW 625CSI',217000.00,4),
(4, 1999,'Jeep Wrangle',78900.00 ,3),
(5,2001,'Toyota Avanza',81200.00 ,1),
(6,2005,'Audi TT',107900.00,4),
(7,2009,'BMW 6 series',171000.00,2);

8,
Select lowest/cheapest num
select categoryname, vehiclemodel from category,vehicle 
where category.categoryid = vehicle.vehiclecategoryid  
and vehicleprice = (select min(vehicleprice)from vehicle);

9.
select vehicleprice, vehiclemodel,categoryname from category,vehicle
and category.categoryid = vehicle.vehicleid
and vehicleyear > 2000 and category.categoryname = 'SUV';

10.
select*from vehicle where vehicleprice>(select avg(vehicleprice) from vehicle)

11.
select vehicleid, vehiclemodel, vehicleprice, round(vehicleprice/100*15),2)
as 'After 15% depreciation' from vehicle;

# data[].attributes.headline
MUHAMAD RIDZWAN BIN ZIAUDDIN (1161200903)

# data[].attributes.subject
MUHAMAD RIDZWAN BIN ZIAUDDIN (1161200903)

# data[].attributes.headline
WAN NURUL AZLIN BINTI WAN NORDIN(1161201175

# data[].attributes.subject
WAN NURUL AZLIN BINTI WAN NORDIN(1161201175

# data[].attributes.headline
SITI KUNI TAIBAH BT IMAM SYAFII(1161202596

# data[].attributes.subject
SITI KUNI TAIBAH BT IMAM SYAFII(1161202596

# data[].attributes.attachment_link.readable_text
8. select CategoryName, Vehicle_Model from category, vehicle where vehicle.vehicle_categoryid = vehicle.vehicle_categoryid
   and vehicle_price = (SELECT MIN(Vehicle_Price) FROM vehicle);

9. SELECT Vehicle_Model, Vehicle_Price FROM vehicle, category where category.categoryid = vehicle.vehicle_categoryid
   and Vehicle_Year > 2000 and category.CategoryName = 'SUV';

10.SELECT * FROM vehicle WHERE Vehicle_Price > (SELECT AVG(Vehicle_Price) FROM vehicle);

13.SELECT Vehicle_ID, Vehicle_Model, Vehicle_Price, ROUND(Vehicle_Price - (Vehicle_Price/100*15),2) AS 'After 15% Depreciation' FROM vehicle;