# data[].attributes.body
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.body
Assignment 4.

# data[].attributes.attachment_link.readable_text
1) create table customer(
Customer_ID int(5) not null primary key,
Customer_Name varchar(50) not null,
Customer_City varchar(50) not null,
Customer_member char(1) not null default 'Y'
);

2) create table promoter(
Promoter_ID char(5) not null primary key,
Promoter_Name varchar(50) not null,
Promoter_commission decimal(4,2)
);

3) create table receipt(
Receipt_No int(5) not null auto_increment primary key,
Receipt_date date,
Receipt_amount decimal(7,2) not null,
Receipt_Customer_ID int(5),
Receipt_Promoter_ID char(5), 
foreign key (Receipt_Customer_ID) references customer(Customer_ID),
foreign key (Receipt_Promoter_ID) references promoter(Promoter_ID)
);


5) select customer.Customer_Name, customer.Customer_Member from customer left join receipt on receipt.Receipt_Customer_ID = customer.Customer_ID where receipt.Receipt_amount is null;

6) select customer.Customer_Name, sum(receipt.Receipt_amount) as 'Total Amount' from customer inner join receipt on customer.Customer_ID = receipt.Receipt_Customer_ID group by customer.Customer_Name;

7) select customer.Customer_Name, count(receipt.Receipt_Promoter_ID) as 'Total Promoter' from receipt inner join customer on customer.Customer_ID = receipt.Receipt_Customer_ID group by customer.Customer_Name;

8) select customer.Customer_member, count(receipt.Receipt_Customer_ID) as 'Total Promoter' from customer inner join receipt on receipt.Receipt_Customer_ID = customer.Customer_ID group by customer.Customer_member;

9) select customer.Customer_Name, receipt.Receipt_amount as 'Total Amount Below Average' from receipt inner join customer on customer.Customer_ID = receipt.Receipt_Customer_ID where receipt.Receipt_amount < (select avg(receipt.Receipt_amount) from receipt) group by customer.Customer_Name;

10) select receipt.Receipt_Promoter_ID, promoter.Promoter_Name from receipt inner join promoter on promoter.Promoter_ID = receipt.Receipt_Promoter_ID where month(Receipt_Date) = '7' and '5';

11) select customer.Customer_Name, min(receipt.Receipt_amount) as 'receipt_amount' from customer inner join receipt on customer.Customer_ID = receipt.Receipt_Customer_ID group by receipt.Receipt_Customer_ID limit 1;

12) select promoter.Promoter_Name, round(promoter.Promoter_commission*sum(receipt.Receipt_amount),2) as 'Total Commission' from receipt inner join promoter on promoter.Promoter_ID = receipt.Receipt_Promoter_ID group by receipt.Receipt_Promoter_ID;

13) select receipt.Receipt_date, receipt.Receipt_amount, promoter.Promoter_Name from receipt inner join customer on customer.Customer_ID = receipt.Receipt_Customer_ID inner join promoter on promoter.Promoter_ID = receipt.Receipt_Promoter_ID where customer.Customer_Name = 'Jacob Roberts';

14) select customer.Customer_Name, count(receipt.Receipt_Customer_ID) as 'Quantity of Purchase' from receipt inner join customer on customer.Customer_ID = receipt.Receipt_Customer_ID group by customer.Customer_Name order by receipt.Receipt_date desc;

15) select customer.Customer_Name, sum(receipt.Receipt_amount) as 'Total' from receipt inner join customer on customer.Customer_ID = receipt.Receipt_Customer_ID inner join promoter on promoter.Promoter_ID = receipt.Receipt_Promoter_ID where promoter.Promoter_Name = 'Paul Adam' group by customer.Customer_Name;

# data[].attributes.body
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.body
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
Muhammad Akmal Haziq Bin Ahmad Azizi (1161202710)

# data[].attributes.subject
Muhammad Akmal Haziq Bin Ahmad Azizi (1161202710)

# data[].attributes.body
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
Fatin Nabilah binti Mohd Suhaimi (1161202222)

# data[].attributes.subject
Fatin Nabilah binti Mohd Suhaimi (1161202222)

# data[].attributes.body
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
Nurul Asyiqin Adriana binti Elmi -1161202185

# data[].attributes.subject
Nurul Asyiqin Adriana binti Elmi -1161202185

# data[].attributes.body
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
Muhammad Haiqal Helmi Bin Mohd Selleh (1161202336)

# data[].attributes.subject
Muhammad Haiqal Helmi Bin Mohd Selleh (1161202336)

# data[].attributes.body
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
Muhammad Amirul Hadi Shaquille Bin Nordin(1161202702

# data[].attributes.subject
Muhammad Amirul Hadi Shaquille Bin Nordin(1161202702

# data[].attributes.body
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
Muhammad Adam Faris Bin Nordin(116120700)

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

# data[].attributes.body
8. select CategoryName, Vehicle_Model from Category, Vehicle  where Vehicle.Vehicle_CategoryID = Category.CategoryID and Vehicle_Price = (select min(Vehicle_Price) from Vehicle);

9. select Vehicle_Model, Vehicle_Price from Category, Vehicle where Vehicle, Vehicle_Year > 2000 and Category.CategoryName = 'SUV' and Category.CategoryID = Vehicle.Vehicle CategoryID;

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
8. select CategoryName, Vehicle_Model from Category, Vehicle  where Vehicle.Vehicle_CategoryID = Category.CategoryID and Vehicle_Price = (select min(Vehicle_Price) from Vehicle);

9. select Vehicle_Model, Vehicle_Price from Category, Vehicle where Vehicle, Vehicle_Year > 2000 and Category.CategoryName = 'SUV' and Category.CategoryID = Vehicle.Vehicle CategoryID;

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
8. select CategoryName, Vehicle_Model from Category, Vehicle  where Vehicle.Vehicle_CategoryID = Category.CategoryID and Vehicle_Price = (select min(Vehicle_Price) from Vehicle);

9. select Vehicle_Model, Vehicle_Price from Category, Vehicle where Vehicle, Vehicle_Year > 2000 and Category.CategoryName = 'SUV' and Category.CategoryID = Vehicle.Vehicle CategoryID;

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.headline
RAJA MUHAMMAD AIDID BIN RAJA ARIS FADILLAH (1161200930)

# data[].attributes.subject
RAJA MUHAMMAD AIDID BIN RAJA ARIS FADILLAH (1161200930)