Structured Query Language (SQL)

This page contains the NCERT Computer Science class 12 chapter 9 Structured Query Language (SQL). You can find the solutions for the chapter 9 of NCERT class 12 Computer Science Exercise. So is the case if you are looking for NCERT class 12 Computer Science related topic Structured Query Language (SQL) questions and answers for the Exercise
Buy Class 12 Computer Science Books NOW!
Exercise
Question 1
1. Answer the following questions:
a)
Define RDBMS. Name any two RDBMS software.
b)
What is the purpose of the following clauses in a select statement?
i)
ORDER BY
ii)
GROUP BY
c)
Site any two differences between Single Row Functions and Aggregate Functions.
d)
What do you understand by Cartesian Product?
e)
Differentiate between the following statements:
i)
ALTER and UPDATE
ii)
DELETE and DROP
f)
Write the name of the functions to perform the following operations:
i)
To display the day like “Monday”, “Tuesday”, from the date when India got independence.
ii)
To display the specified number of characters from a particular position of the given string.
iii)
To display the name of the month in which you were born.
iv)
To display your name in capital letters.
Answer 1
(a)
Define RDBMS. Name any two RDBMS software.
•
RDBMS (Relational Database Management System) is a database system in which data is stored in the form of relations (tables) made of rows (tuples/records) and columns (attributes/fields).
•
Examples of RDBMS software: MySQL, Microsoft SQL Server.
(b)
What is the purpose of the following clauses in a SELECT statement?
(i)
ORDER BY
•
Used to arrange (sort) the output in ascending or descending order based on one or more columns.
(ii)
GROUP BY
•
Used to group rows having the same value in a column, mainly used with aggregate functions like SUM, AVG, COUNT, MAX, MIN.
(c)
State any two differences between Single Row Functions and Aggregate Functions.
Basis
Single Row Functions
Aggregate Functions
How they work
Operate on one row at a time and return one result for each row.
Operate on a set (group) of rows and return one result for the whole set/group.
Result produced
If the table has n rows, output will usually have n results (one per row).
Output is usually one value per group (or one value for the full table if no GROUP BY).
Examples (with sample SQL)
UPPER(), LOWER(), LENGTH(), MID()
Example: SELECT UPPER(Name) FROM Student;
SUM(), AVG(), COUNT(), MAX(), MIN()
Example: SELECT AVG(Marks) FROM Student;
(d)
What do you understand by Cartesian Product?
Cartesian Product combines each row of the first table with each row of the second table, producing all possible combinations.
(e)
Differentiate between the following statements:
(i)
ALTER and UPDATE
Basis
ALTER
UPDATE
Type of command
DDL (Data Definition Language)
DML (Data Manipulation Language)
Purpose
Used to change the structure (schema) of a table.
Used to change/modify data (values) in existing rows.
Works on
Table structure: add/modify/drop columns, constraints.
Table records: updates column values.
Affects
Design of table
Contents (data) in table
WHERE clause
Not used like DML.
Usually used to update specific rows using WHERE.
Example
ALTER TABLE Student ADD Age INT;
UPDATE Student SET Age = 17 WHERE RollNo = 1;
(ii)
DELETE and DROP
Basis
DELETE
DROP
Type of command
DML (Data Manipulation Language)
DDL (Data Definition Language)
Purpose
Used to remove rows (records) from a table.
Used to remove the entire table/database.
Works on
Data only (records).
Structure + data (everything is removed).
Table after command
Table still exists (only rows removed).
Table does not exist after DROP.
WHERE clause
Can be used to delete specific rows using WHERE.
No WHERE (it removes the complete table).
Example
DELETE FROM Student WHERE Class = 12;
DROP TABLE Student;
(f)
Write the name of the functions to perform the following operations:
(i)
To display the day like “Monday”, “Tuesday”, from the date when India got independence.
✅ Function: DAYNAME()
Example:
SELECT DAYNAME('1947-08-15');
Output:
Friday
(ii)
To display the specified number of characters from a particular position of the given string.
✅ Function: MID() (or SUBSTR())
Example:
SELECT MID('Informatics', 3, 4);
Output:
form
(iii)
To display the name of the month in which you were born.
✅ Function: MONTHNAME()
Example:
SELECT MONTHNAME('2006-03-10');
Output:
March
(iv)
To display your name in capital letters.
✅ Function: UPPER()
Example:
SELECT UPPER('Rahul');
Output:
RAHUL
Question 2
2. Write the output produced by the following SQL statements:
a)
SELECT POW(2,3);
b)
SELECT ROUND(342.9234,-1);
c)
SELECT LENGTH(“Informatics Practices”);
d)
SELECT YEAR(“1979/11/26”), MONTH(“1979/11/26”), DAY(“1979/11/26”), MONTHNAME(“1979/11/26”);
e)
SELECT LEFT(“INDIA”,3), RIGHT(“Computer Science”,4), MID(“Informatics”,3,4), SUBSTR(“Practices”,3);
Answer 2
(a)
SELECT POW(2,3);
Output:
8
(b)
SELECT ROUND(342.9234,-1);
Rounding to nearest 10 (because -1) → 340
Output:
340
(c)
SELECT LENGTH("Informatics Practices");
Length includes the space.
•
“Informatics” = 11 characters
•
space = 1
•
“Practices” = 9
Total = 21
Output:
21
(d)
SELECT
YEAR("1979/11/26"),
MONTH("1979/11/26"),
DAY("1979/11/26"),
MONTHNAME("1979/11/26");
Output:
1979   11   26   November
(e)
SELECT
      LEFT("INDIA",3),
      RIGHT("Computer Science",4),
      MID("Informatics",3,4),
      SUBSTR("Practices",3);
Output:
IND   ence   orma   actices
Question 3
3. Consider the following MOVIE table and write the SQL queries based on it.
MovieID
MovieName
Category
ReleaseDate
ProductionCost
BusinessCost
001
Hindi_Movie
Musical
2018-04-23
124500
130000
002
Tamil_Movie
Action
2016-05-17
112000
118000
003
English_Movie
Horror
2017-08-06
245000
360000
004
Bengali_Movie
Adventure
2017-01-04
72000
100000
005
Telugu_Movie
Action
-
100000
-
006
Punjabi_Movie
Comedy
-
30500
-
a)
Display all the information from the Movie table.
b)
List business done by the movies showing only MovieID, MovieName and Total_Earning. Total_Earning to be calculated as the sum of ProductionCost and BusinessCost.
c)
List the different categories of movies.
d)
Find the net profit of each movie showing its MovieID, MovieName and NetProfit. Net Profit is to be calculated as the difference between Business Cost and Production Cost.
e)
List MovieID, MovieName and Cost for all movies with ProductionCost greater than 10,000 and less than 1,00,000.
f)
List details of all movies which fall in the category of comedy or action.
g)
List details of all movies which have not been released yet.
Answer 3
(a) Display all the information from the Movie table.
SELECT * FROM Movie;
Output:
+---------+---------------+-----------+-------------+----------------+--------------+
| MovieID | MovieName     | Category  | ReleaseDate | ProductionCost | BusinessCost |
+---------+---------------+-----------+-------------+----------------+--------------+
|     001 | Hindi_Movie   | Musical   | 2018-04-23  |         124500 |       130000 |
|     002 | Tamil_Movie   | Action    | 2016-05-17  |         112000 |       118000 |
|     003 | English_Movie | Horror    | 2017-08-06  |         245000 |       360000 |
|     004 | Bengali_Movie | Adventure | 2017-01-04  |          72000 |       100000 |
|     005 | Telugu_Movie  | Action    | NULL        |         100000 |         NULL |
|     006 | Punjabi_Movie | Comedy    | NULL        |          30500 |         NULL |
+---------+---------------+-----------+-------------+----------------+--------------+
(b) List business done by the movies showing only MovieID, MovieName and Total_Earning.
(Total_Earning = ProductionCost + BusinessCost)
SELECT
  MovieID,
  MovieName,
  IFNULL(ProductionCost,0) + IFNULL(BusinessCost,0) AS Total_Earning
FROM Movie
WHERE ReleaseDate IS NOT NULL;
Output:
+---------+---------------+---------------+
| MovieID | MovieName     | Total_Earning |
+---------+---------------+---------------+
|     001 | Hindi_Movie   |        254500 |
|     002 | Tamil_Movie   |        230000 |
|     003 | English_Movie |        605000 |
|     004 | Bengali_Movie |        172000 |
+---------+---------------+---------------+
(c) List the different categories of movies.
SELECT DISTINCT Category
  FROM Movie;
Output:
+-----------+
| Category  |
+-----------+
| Musical   |
| Action    |
| Horror    |
| Adventure |
| Comedy    |
+-----------+
(d) Find the net profit of each movie showing MovieID, MovieName and NetProfit.
(NetProfit = BusinessCost − ProductionCost)
SELECT
  MovieID,
  MovieName,
  IFNULL(BusinessCost,0) - IFNULL(ProductionCost,0) AS NetProfit
FROM Movie
WHERE ReleaseDate IS NOT NULL;
Output:
+---------+---------------+-----------+
| MovieID | MovieName     | NetProfit |
+---------+---------------+-----------+
|     001 | Hindi_Movie   |      5500 |
|     002 | Tamil_Movie   |      6000 |
|     003 | English_Movie |    115000 |
|     004 | Bengali_Movie |     28000 |
+---------+---------------+-----------+
(e) List MovieID, MovieName and Cost for all movies with ProductionCost > 10,000 and < 1,00,000.
SELECT
  MovieID,
  MovieName,
  ProductionCost AS Cost
FROM Movie
WHERE ProductionCost > 10000 AND ProductionCost < 100000;
Output:
+---------+---------------+-------+
| MovieID | MovieName     |  Cost |
+---------+---------------+-------+
|     004 | Bengali_Movie | 72000 |
|     006 | Punjabi_Movie | 30500 |
+---------+---------------+-------+
(f) List details of all movies which fall in the category of comedy or action.
SELECT *
  FROM Movie
  WHERE Category IN ('Comedy', 'Action');
Output:
+---------+---------------+----------+-------------+----------------+--------------+
| MovieID | MovieName     | Category | ReleaseDate | ProductionCost | BusinessCost |
+---------+---------------+----------+-------------+----------------+--------------+
|     002 | Tamil_Movie   | Action   | 2016-05-17  |         112000 |       118000 |
|     005 | Telugu_Movie  | Action   | NULL        |         100000 |         NULL |
|     006 | Punjabi_Movie | Comedy   | NULL        |          30500 |         NULL |
+---------+---------------+----------+-------------+----------------+--------------+
(g) List details of all movies which have not been released yet.
(ReleaseDate is missing, so treated as NULL)
SELECT *
  FROM Movie
  WHERE ReleaseDate IS NULL;
Output:
+---------+---------------+----------+-------------+----------------+--------------+
| MovieID | MovieName     | Category | ReleaseDate | ProductionCost | BusinessCost |
+---------+---------------+----------+-------------+----------------+--------------+
|     005 | Telugu_Movie  | Action   | NULL        |         100000 |         NULL |
|     006 | Punjabi_Movie | Comedy   | NULL        |          30500 |         NULL |
+---------+---------------+----------+-------------+----------------+--------------+
Question 4
4. Suppose your school management has decided to conduct cricket matches between students of Class XI and Class XII. Students of each class are asked to join any one of the four teams – Team Titan, Team Rockers, Team Magnet and Team Hurricane. During summer vacations, various matches will be conducted between these teams. Help your sports teacher to do the following:
a)
Create a database “Sports”.
b)
Create a table “TEAM” with following considerations:
i)
It should have a column TeamID for storing an integer value between 1 to 9, which refers to unique identification of a team.
ii)
Each TeamID should have its associated name (TeamName), which should be a string of length not less than 10 characters.
c)
Using table level constraint, make TeamID as the primary key.
d)
Show the structure of the table TEAM using a SQL statement.
e)
As per the preferences of the students four teams were formed as given below. Insert these four rows in TEAM table:
Row 1: (1, Team Titan)
Row 2: (2, Team Rockers)
Row 3: (3, Team Magnet)
Row 4: (4, Team Hurricane)
f)
Show the contents of the table TEAM using a DML statement.
g)
Now create another table MATCH_DETAILS and insert data as shown below. Choose appropriate data types and constraints for each attribute.
Table: MATCH_DETAILS
MatchID
MatchDate
FirstTeamID
SecondTeamID
FirstTeamScore
SecondTeamScore
M1
2018-07-17
1
2
90
86
M2
2018-07-18
3
4
45
48
M3
2018-07-19
1
3
78
56
M4
2018-07-19
2
4
56
67
M5
2018-07-18
1
4
32
87
M6
2018-07-17
2
3
67
51
Answer 4
(a) Create a database “Sports”.
CREATE DATABASE Sports;
USE Sports;
(b) Create table TEAM with given considerations.
CREATE TABLE TEAM (
  TeamID INT,
  TeamName VARCHAR(30) NOT NULL,
  CHECK (TeamID BETWEEN 1 AND 9),
  CHECK (LENGTH(TeamName) >= 10)
);
(c) Using table level constraint, make TeamID as the primary key.
ALTER TABLE TEAM
  ADD PRIMARY KEY (TeamID);
(d) Show the structure of table TEAM.
DESC TEAM;
Output:
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
|---------+-------------+------+-----+---------+-------|
| TeamID  | int         | NO   | PRI | NULL    |       |
| TeamName| varchar(30) | NO   |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
(e) Insert the four rows in TEAM table.
INSERT INTO TEAM VALUES
  (1, 'Team Titan'),
  (2, 'Team Rockers'),
  (3, 'Team Magnet'),
  (4, 'Team Hurricane');
(f) Show the contents of TEAM table.
SELECT * FROM TEAM;
Output:
+--------+---------------+
| TeamID | TeamName      |
|--------+---------------|
|      1 | Team Titan    |
|      2 | Team Rockers  |
|      3 | Team Magnet   |
|      4 | Team Hurricane|
+--------+---------------+
(g) Create MATCH_DETAILS table and insert the given data.
CREATE TABLE MATCH_DETAILS (
  MatchID VARCHAR(2) PRIMARY KEY,
  MatchDate DATE NOT NULL,
  FirstTeamID INT NOT NULL,
  SecondTeamID INT NOT NULL,
  FirstTeamScore INT NOT NULL,
  SecondTeamScore INT NOT NULL,
  FOREIGN KEY (FirstTeamID) REFERENCES TEAM(TeamID),
  FOREIGN KEY (SecondTeamID) REFERENCES TEAM(TeamID)
);

INSERT INTO MATCH_DETAILS VALUES
  ('M1','2018-07-17',1,2,90,86),
  ('M2','2018-07-18',3,4,45,48),
  ('M3','2018-07-19',1,3,78,56),
  ('M4','2018-07-19',2,4,56,67),
  ('M5','2018-07-18',1,4,32,87),
  ('M6','2018-07-17',2,3,67,51);

Question 5
5. Using the sports database containing two relations (TEAM, MATCH_DETAILS) and write the queries for the following:
a)
Display the MatchID of all those matches where both the teams have scored more than 70.
b)
Display the MatchID of all those matches where FirstTeam has scored less than 70 but SecondTeam has scored more than 70.
c)
Display the MatchID and date of matches played by Team 1 and won by it.
d)
Display the MatchID of matches played by Team 2 and not won by it.
e)
Change the name of the relation TEAM to T_DATA. Also change the attributes TeamID and TeamName to T_ID and T_NAME respectively.
Answer 5
(a) MatchID where both teams scored more than 70
SELECT MatchID
  FROM MATCH_DETAILS
  WHERE FirstTeamScore > 70
    AND SecondTeamScore > 70;
Output:
+---------+
| MatchID |
+---------+
| M1      |
+---------+
(b) MatchID where FirstTeam < 70 but SecondTeam > 70
SELECT MatchID
  FROM MATCH_DETAILS
  WHERE FirstTeamScore < 70
    AND SecondTeamScore > 70;
Output:
+---------+
| MatchID |
+---------+
| M5      |
+---------+
(c) MatchID and date of matches played by Team 1 and won by it
SELECT MatchID, MatchDate
  FROM MATCH_DETAILS
  WHERE (FirstTeamID = 1 AND FirstTeamScore > SecondTeamScore)
    OR (SecondTeamID = 1 AND SecondTeamScore > FirstTeamScore);
ℹ️Info: “Team 1” (whose TeamID is 1) can be the “First Team” (represented by FirstTeamID) or the “Second Team” (represented by SecondTeamID). The query checks for both cases.
Output:
+---------+------------+
| MatchID | MatchDate  |
+---------+------------+
| M1      | 2018-07-17 |
| M3      | 2018-07-19 |
+---------+------------+
(d) MatchID of matches played by Team 2 and not won by it
SELECT MatchID
  FROM MATCH_DETAILS
  WHERE (FirstTeamID = 2 AND FirstTeamScore <= SecondTeamScore)
    OR (SecondTeamID = 2 AND SecondTeamScore <= FirstTeamScore);
ℹ️Info: “Team 2” (whose TeamID is 2) can be the “First Team” (represented by FirstTeamID) or the “Second Team” (represented by SecondTeamID). The query checks for both cases.
Output:
+---------+
| MatchID |
+---------+
| M1      |
| M4      |
| M6      |
+---------+
(e) Rename TEAM to T_DATA and change TeamID/TeamName to T_ID/T_NAME
ALTER TABLE TEAM
  RENAME TO T_DATA;

  ALTER TABLE T_DATA
  CHANGE COLUMN TeamID T_ID INT;

  ALTER TABLE T_DATA
  CHANGE COLUMN TeamName T_NAME VARCHAR(30);
Question 6
6. A shop called Wonderful Garments who sells school uniforms maintains a database SCHOOLUNIFORM as shown below. It consisted of two relations – UNIFORM and COST. They made UniformCode as the primary key for UNIFORM relations. Further, they used UniformCode and Size to be composite keys for COST relation. By analysing the database schema and database state, specify SQL queries to rectify the following anomalies.
a)
M/S Wonderful Garments also keeps handkerchiefs of red colour, medium size of Rs. 100 each.
b)
INSERT INTO COST (UCode, Size, Price) values (7, 'M',100);
When the above query is used to insert data, the values for the handkerchief without entering its details in the UNIFORM relation is entered. Make a provision so that the data can be entered in the COST table only if it is already there in the UNIFORM table.
c)
Further, they should be able to assign a new UCode to an item only if it has a valid UName. Write a query to add appropriate constraints to the SCHOOLUNIFORM database.
d)
Add the constraint so that the price of an item is always greater than zero.
Answer 6
(a) Add handkerchief (red, medium, ₹ 100)
First add item into UNIFORM, then into COST:
INSERT INTO UNIFORM (UCode, UName, UColor)
  VALUES (7, 'Handkerchief', 'Red');

  INSERT INTO COST (UCode, Size, Price)
  VALUES (7, 'M', 100);
Output:
Query OK, 1 row affected
  Query OK, 1 row affected
(b) Allow insert into COST only if it exists in UNIFORM
Add FOREIGN KEY:
ALTER TABLE COST
  ADD CONSTRAINT fk_ucode
  FOREIGN KEY (UCode) REFERENCES UNIFORM(UCode);
Output:
Query OK, 0 rows affected
(c) Assign new UCode only if it has a valid UName
Make UName NOT NULL:
ALTER TABLE UNIFORM
  MODIFY UName VARCHAR(30) NOT NULL;
Output:
Query OK, 0 rows affected
(d) Price is always greater than zero
ALTER TABLE COST
  ADD CONSTRAINT chk_price
  CHECK (Price > 0);
Output:
Query OK, 0 rows affected
Question 7
7. Consider the following table named “Product”, showing details of products being sold in a grocery shop.
PCode
PName
UPrice
Manufacturer
P01
Washing Powder
120
Surf
P02
Toothpaste
54
Colgate
P03
Soap
25
Lux
P04
Toothpaste
65
Pepsodent
P05
Soap
38
Dove
P06
Shampoo
245
Dove
Write SQL queries for the following:
a)
Create the table Product with appropriate data types and constraints.
b)
Identify the primary key in Product.
c)
List the Product Code, Product name and price in descending order of their product name. If PName is the same, then display the data in ascending order of price.
d)
Add a new column Discount to the table Product.
e)
Calculate the value of the discount in the table Product as 10 per cent of the UPrice for all those products where the UPrice is more than 100, otherwise the discount will be 0.
f)
Increase the price by 12 per cent for all the products manufactured by Dove.
g)
Display the total number of products manufactured by each manufacturer.
Write the output(s) produced by executing the following queries on the basis of the information given above in the table Product:
h)
SELECT PName, avg(UPrice) FROM Product GROUP BY Pname;
i)
SELECT DISTINCT Manufacturer FROM Product;
j)
SELECT COUNT (DISTINCT PName) FROM Product;
k)
SELECT PName, MAX(UPrice), MIN(UPrice) FROM Product GROUP BY PName;
Answer 7
(a) Create table Product with data types and constraints
CREATE TABLE Product (
  PCode CHAR(3) PRIMARY KEY,
  PName VARCHAR(30) NOT NULL,
  UPrice INT NOT NULL CHECK (UPrice > 0),
  Manufacturer VARCHAR(30) NOT NULL
);
Output:
Query OK, 0 rows affected
(b) Identify the primary key in Product
✅ Primary Key: PCode
(c) List Product Code, Product name and price in descending order of PName; if PName same then ascending order of price
SELECT PCode, PName, UPrice
  FROM Product
  ORDER BY PName DESC, UPrice ASC;
Output:
+-------+---------------+--------+
| PCode | PName         | UPrice |
|-------+---------------+--------|
| P01   | Washing Powder|    120 |
| P02   | Toothpaste    |     54 |
| P04   | Toothpaste    |     65 |
| P03   | Soap          |     25 |
| P05   | Soap          |     38 |
| P06   | Shampoo       |    245 |
+-------+---------------+--------+
(d) Add a new column Discount
ALTER TABLE Product
  ADD COLUMN Discount DECIMAL(8,2) DEFAULT 0;
Output:
Query OK, 0 rows affected
(e) Set Discount = 10% of UPrice if UPrice > 100 else 0
UPDATE Product
  SET Discount = IF(UPrice > 100, UPrice * (10/100), 0);
Output:
Query OK, 6 rows affected
(f) Increase price by 12% for products manufactured by Dove
UPDATE Product
  SET UPrice = UPrice * 1.12
  WHERE Manufacturer = 'Dove';
Note: UPrice + UPrice * 12/100 = UPrice * 1.12.
Output:
Query OK, 2 rows affected
(g) Display total number of products manufactured by each manufacturer
SELECT Manufacturer, COUNT(*) AS TotalProducts
  FROM Product
  GROUP BY Manufacturer;
Output:
+--------------+--------------+
| Manufacturer | TotalProducts|
|--------------+--------------|
| Surf         | 1            |
| Colgate      | 1            |
| Lux          | 1            |
| Pepsodent    | 1            |
| Dove         | 2            |
+--------------+--------------+
Write the output(s) for the following queries (based on given Product data):
(h) SELECT PName, avg(UPrice) FROM Product GROUP BY PName;
SELECT PName, AVG(UPrice)
  FROM Product
  GROUP BY PName;
Sample Output:
+---------------+------------+
| PName         | avg(UPrice)|
|---------------+------------|
| Washing Powder|      120.0 |
| Toothpaste    |       59.5 |
| Soap          |       33.5 |
| Shampoo       |      274.0 |
+---------------+------------+
(i) SELECT DISTINCT Manufacturer FROM Product;
SELECT DISTINCT Manufacturer
    FROM Product;
Sample Output:
+--------------+
| Manufacturer |
|--------------|
| Surf         |
| Colgate      |
| Lux          |
| Pepsodent    |
| Dove         |
+--------------+
(j) SELECT COUNT (DISTINCT PName) FROM Product;
SELECT COUNT(DISTINCT PName)
  FROM Product;
Sample Output:
+-----------------------+
| COUNT(DISTINCT PName) |
|-----------------------|
|                     4 |
+-----------------------+
(k) SELECT PName, MAX(UPrice), MIN(UPrice) FROM Product GROUP BY PName;
SELECT PName, MAX(UPrice), MIN(UPrice)
FROM Product
GROUP BY PName;
Sample Output:
+---------------+------------+------------+
| PName         | MAX(UPrice)| MIN(UPrice)|
|---------------+------------+------------|
| Washing Powder|        120 |        120 |
| Toothpaste    |         65 |         54 |
| Soap          |         42 |         25 |
| Shampoo       |        274 |        274 |
+---------------+------------+------------+
Question 8
8. 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 per cent discount.
iii)
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.
Answer 8
(a) Add a new column Discount in INVENTORY
ALTER TABLE INVENTORY
  ADD COLUMN Discount DECIMAL(10,2) DEFAULT 0;
Output:
Query OK, 0 rows affected
(b) Set discount based on Model (LXI=0, VXI=10%, others=12%)
UPDATE INVENTORY
  SET Discount =
    CASE
      WHEN Model = 'LXI' THEN 0
      WHEN Model = 'VXI' THEN Price * (10/100)
      ELSE Price * (12/100)
    END;
Output:
Query OK, 0 rows affected
(c) Display the name of the costliest car with fuel type “Petrol”
SELECT CarName
FROM INVENTORY
WHERE FuelType = 'Petrol'
ORDER BY Price DESC
LIMIT 1;
Output:
+---------+
| CarName |
|---------|
| Dzire   |
+---------+
(d) Average discount and total discount on Baleno cars
SELECT AVG(Discount) AS AvgDiscount,
      SUM(Discount) AS TotalDiscount
FROM INVENTORY
WHERE CarName = 'Baleno';
Output:
+-------------+--------------+
| AvgDiscount | TotalDiscount|
|-------------+--------------|
|    72893.34 |    145786.68 |
+-------------+--------------+
(e) Total number of cars having no discount
SELECT COUNT(*) AS NoDiscountCars
    FROM INVENTORY
    WHERE Discount = 0;
Output:
+----------------+
| NoDiscountCars |
|----------------|
|              2 |
+----------------+