Using the CARSHOWROOM database given in the chapter, write the SQL queries for the following:

Q. Using the CARSHOWROOM database given in the chapter, write the SQL queries for the following:

 

(a) Add a new column Discount in the INVENTORY table.

(b) Set   appropriate discount values for all cars keeping in mind the following:

 

(i) No discount is available on the LXI model.

(ii) VXI model gives a 10% discount.

(iii)A 12% discount is given on cars other than   LXI model and VXI model.

(c) Display the name of the costliest car with fuel type “Petrol”.

(d) Calculate the average discount and total discount available on Car4.

(e) List the total number of cars having no discount.

Answer:-

a = alter table INVENTORY add column discount int;

b =

i = update INVENTORY set discount = 0 where model = “LXI”;
ii = update INVENTORY set discount = price / 10 where model = “VXI ” ;
iii = update INVENTORY set discount = price * 0.12  where model <> “LXI ” and model <> “VXI” ;


c = select carname form INVENTORY where (select max(Price) from INVENTORY where FuelType = “Petrol” ) = Price ;

d = Select Avg(dicount) , Sum(dicount) from INVENTORY where Carname = “Car4” ;

e = Select count(*) fron INVENTORY where discount = 0 ;