Question

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

1. Add a new column Discount in the INVENTORY table.
2. 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 percent discount.
   (iii) A 12 percent discount is given on cars other than the LXI model and VXI model.
3. Display the name of the costliest car with the fuel type “Petrol”.
4. Calculate the average discount and total discount available on Baleno cars.
5. List the total number of cars having no discount.

Answer 1

1. ALTER TABLE INVENTORY ADD Discount INT;
2. (i) UPDATE INVENTORY SET Discount = 0 WHERE Model = 'LXI';
   (ii) UPDATE INVENTORY SET Discount = 10 WHERE Model = 'VXI';
   (iii) UPDATE INVENTORY SET Discount = 12 WHERE Model NOT IN (LXI', VXI');
3. SELECT CarName, FuelType FROM INVENTORY WHERE Price = MAX(Price) and FuelType = 'Petrol';
4. SELECT AVG(Discount), SUM(Discount) FROM INVENTORY WHERE CarType = 'Baleno',
5. SELECT COUNT(") FROM INVENTORY WHERE Discount = 0;