KnowledgeBoat Logo

Computer Science

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:

  1. No discount is available on the LXI model.
  2. VXI model gives a 10 per cent discount.
  3. A 12 per cent 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 Baleno cars.

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

SQL Queries

3 Likes

Answer

Table inventory

CarIdCarNamePriceModelYearManufactureFuelTypeFinalPrice
D001Dzire582613.00LXI2017Petrol652526.6
D002Dzire673112.00VXI2018Petrol753885.4
B001Baleno567031.00Sigma1.22019Petrol635074.7
B002Baleno647858.00Delta1.22018Petrol725601.0
E001EECO355205.005 STR STD2017CNG397829.6
E002EECO654914.00CARE2018CNG733503.7
S001SWIFT514000.00LXI2017Petrol575680.0
S002SWIFT614000.00VXI2018Petrol687680.0

(a)

ALTER TABLE INVENTORY
ADD COLUMN Discount FLOAT;

(b)

UPDATE INVENTORY
SET Discount = 0
WHERE Model = 'LXI';

UPDATE INVENTORY
SET Discount = Price * 0.10
WHERE Model = 'VXI';

UPDATE INVENTORY
SET Discount = Price * 0.12
WHERE Model NOT IN ('LXI', 'VXI');
Output
+-------+---------+-----------+-----------+-----------------+----------+------------+----------+
| CarId | CarName | Price     | Model     | YearManufacture | FuelType | FinalPrice | Discount |
+-------+---------+-----------+-----------+-----------------+----------+------------+----------+
| D001  | Dzire   | 582613.00 | LXI       |            2017 | Petrol   |  652526.60 |        0 |
| D002  | Dzire   | 673112.00 | VXI       |            2018 | Petrol   |  753885.40 |  67311.2 |
| B001  | Baleno  | 567031.00 | Sigma1.2  |            2019 | Petrol   |  635074.70 |  68043.7 |
| B002  | Baleno  | 647858.00 | Delta1.2  |            2018 | Petrol   |  725601.00 |    77743 |
| E001  | EECO    | 355205.00 | 5 STR STD |            2017 | CNG      |  397829.60 |  42624.6 |
| E002  | EECO    | 654914.00 | CARE      |            2018 | CNG      |  733503.70 |  78589.7 |
| S001  | SWIFT   | 514000.00 | LXI       |            2017 | Petrol   |  575680.00 |        0 |
| S002  | SWIFT   | 614000.00 | VXI       |            2018 | Petrol   |  687680.00 |    61400 |
+-------+---------+-----------+-----------+-----------------+----------+------------+----------+

(c)

SELECT CarName
FROM INVENTORY
WHERE FuelType = 'Petrol'
AND Price = (SELECT MAX(Price) FROM INVENTORY WHERE FuelType = 'Petrol');
Output
+---------+
| CarName |
+---------+
| Dzire   |
+---------+

(d)

SELECT AVG(Discount) AS AverageDiscount,
SUM(Discount) AS TotalDiscount
FROM INVENTORY
WHERE CarName = 'Baleno';
Output
+-----------------+----------------+
| AverageDiscount | TotalDiscount  |
+-----------------+----------------+
|  72893.33984375 | 145786.6796875 |
+-----------------+----------------+

(e)

SELECT COUNT(*)
FROM INVENTORY
WHERE Discount = 0;
Output
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+

Answered By

1 Like

Related Questions