Structured Query a Language (SQL) — NCERT Solutions

SQL Query:

SELECT MatchID
FROM MATCH_DETAILS
WHERE FirstTeamScore > 70 AND SecondTeamScore > 70;

Working through the data:

• M1: 90 > 70 AND 86 > 70 → ✓
• M2: 45 > 70 → ✗
• M3: 78 > 70 AND 56 > 70 → ✗ (56 not > 70)
• M4: 56 > 70 → ✗
• M5: 32 > 70 → ✗
• M6: 67 > 70 → ✗

Output:

+---------+
| MatchID |
+---------+
| M1      |
+---------+

SQL Query:

SELECT MatchID
FROM MATCH_DETAILS
WHERE FirstTeamScore < 70 AND SecondTeamScore > 70;

Working through the data:

• M1: 90 < 70 → ✗
• M2: 45 < 70 AND 48 > 70 → ✗ (48 not > 70)
• M3: 78 < 70 → ✗
• M4: 56 < 70 AND 67 > 70 → ✗ (67 not > 70)
• M5: 32 < 70 AND 87 > 70 → ✓
• M6: 67 < 70 AND 51 > 70 → ✗

Output:

+---------+
| MatchID |
+---------+
| M5      |
+---------+

SQL Query:

SELECT MatchID, MatchDate
FROM MATCH_DETAILS
WHERE (FirstTeamID = 1 AND FirstTeamScore > SecondTeamScore)
   OR (SecondTeamID = 1 AND SecondTeamScore > FirstTeamScore);

Explanation: Team 1 wins when:

• It is the First Team and FirstTeamScore > SecondTeamScore, OR
• It is the Second Team and SecondTeamScore > FirstTeamScore.

Working through the data:

• M1: FirstTeamID=1, 90 > 86 → ✓ (Team 1 wins)
• M3: FirstTeamID=1, 78 > 56 → ✓ (Team 1 wins)
• M5: FirstTeamID=1, 32 > 87 → ✗ (Team 1 loses)

Output:

+---------+------------+
| MatchID | MatchDate  |
+---------+------------+
| M1      | 2018-07-17 |
| M3      | 2018-07-19 |
+---------+------------+

SQL Query:

SELECT MatchID
FROM MATCH_DETAILS
WHERE (FirstTeamID = 2 AND FirstTeamScore <= SecondTeamScore)
   OR (SecondTeamID = 2 AND SecondTeamScore <= FirstTeamScore);

Explanation: Team 2 did NOT win when:

• It is the First Team and FirstTeamScore <= SecondTeamScore (lost or drew), OR
• It is the Second Team and SecondTeamScore <= FirstTeamScore (lost or drew).

Working through the data:

• M1: SecondTeamID=2, SecondTeamScore=86, FirstTeamScore=90 → 86 <= 90 → ✓ (Team 2 lost)
• M4: FirstTeamID=2, FirstTeamScore=56, SecondTeamScore=67 → 56 <= 67 → ✓ (Team 2 lost)
• M6: FirstTeamID=2, FirstTeamScore=67, SecondTeamScore=51 → 67 <= 51 → ✗ (Team 2 won)

Output:

+---------+
| MatchID |
+---------+
| M1      |
| M4      |
+---------+

Step 1: Rename the table TEAM to T_DATA:

ALTER TABLE TEAM RENAME TO T_DATA;

Step 2: Rename column TeamID to T_ID:

ALTER TABLE T_DATA CHANGE TeamID T_ID INT;

Step 3: Rename column TeamName to T_NAME:

ALTER TABLE T_DATA CHANGE TeamName T_NAME VARCHAR(30);

Explanation:

• ALTER TABLE ... RENAME TO is used to rename a table.
• ALTER TABLE ... CHANGE old_column_name new_column_name datatype is used to rename a column in MySQL. The data type must be re-specified even if it is not changing.

Step 1: First insert the handkerchief details into the UNIFORM table:

INSERT INTO UNIFORM (UCode, UName, Colour)
VALUES (7, 'Handkerchief', 'Red');

Step 2: Then insert the size and price into the COST table:

INSERT INTO COST (UCode, Size, Price)
VALUES (7, 'M', 100);

Explanation: Since UniformCode in COST is a Foreign Key referencing UNIFORM, the entry must first be made in the UNIFORM (parent) table before inserting into COST (child) table. This maintains referential integrity.

SQL Query:

ALTER TABLE COST
ADD CONSTRAINT fk_ucode
FOREIGN KEY (UCode) REFERENCES UNIFORM(UCode);

Explanation:

• A Foreign Key constraint is added on the UCode column of the COST table, referencing the UCode (primary key) of the UNIFORM table.
• This ensures referential integrity: data can be inserted into COST only if the corresponding UCode already exists in the UNIFORM table.
• If someone tries to insert a record in COST with a UCode that does not exist in UNIFORM, MySQL will throw an error and reject the insertion.

SQL Query:

ALTER TABLE UNIFORM
MODIFY UName VARCHAR(30) NOT NULL;

Explanation:

• The NOT NULL constraint on UName ensures that a new UCode (row) can only be inserted into the UNIFORM table if a valid (non-null) UName is also provided.
• This prevents entries where UCode is assigned without a corresponding item name.

SQL Query:

ALTER TABLE COST
ADD CONSTRAINT chk_price CHECK (Price > 0);

Explanation:

• The CHECK constraint is added on the Price column of the COST table.
• It enforces the business rule that Price must always be greater than 0.
• Any attempt to insert or update a row with Price <= 0 will be rejected by MySQL.

SQL Query:

CREATE TABLE Product
(
    PCode        VARCHAR(5)    NOT NULL,
    PName        VARCHAR(30)   NOT NULL,
    UPrice       DECIMAL(10,2) NOT NULL,
    Manufacturer VARCHAR(30),
    CONSTRAINT pk_product PRIMARY KEY (PCode)
);

Explanation of Data Types:

• PCode: VARCHAR(5) — short product code like P01, P02.
• PName: VARCHAR(30) — product name string.
• UPrice: DECIMAL(10,2) — price with decimal precision.
• Manufacturer: VARCHAR(30) — manufacturer name.
• PCode is the Primary Key (unique, not null).

Primary Key: PCode

Justification:

• PCode uniquely identifies each product in the table (P01, P02, P03, P04, P05, P06 — all distinct).
• A Primary Key must be unique and NOT NULL.
• PName cannot be a primary key as values like 'Toothpaste' and 'Soap' repeat.
• Manufacturer also repeats (e.g., Dove appears twice).
• Therefore, PCode is the most appropriate Primary Key.

SQL Query:

SELECT PCode, PName, UPrice
FROM Product
ORDER BY PName DESC, UPrice ASC;

Explanation:

• ORDER BY PName DESC sorts the result in descending alphabetical order of product name.
• UPrice ASC is the secondary sort: when two products have the same name (e.g., both 'Toothpaste' or both 'Soap'), they are sorted in ascending order of price.

Expected Output:

+-------+----------------+--------+
| PCode | PName          | UPrice |
+-------+----------------+--------+
| P01   | Washing Powder |    120 |
| P02   | Toothpaste     |     54 |
| P04   | Toothpaste     |     65 |
| P06   | Shampoo        |    245 |
| P03   | Soap           |     25 |
| P05   | Soap           |     38 |
+-------+----------------+--------+

SQL Query:

ALTER TABLE Product
ADD Discount DECIMAL(10,2) DEFAULT 0;

Explanation:

• ALTER TABLE is used to modify the structure of an existing table.
• ADD Discount DECIMAL(10,2) adds a new column named Discount with a decimal data type.
• DEFAULT 0 sets the initial value to 0 for all existing rows.

SQL Query:

UPDATE Product
SET Discount = CASE
    WHEN UPrice > 100 THEN UPrice * 0.10
    ELSE 0
END;

Alternative using two UPDATE statements:

UPDATE Product SET Discount = UPrice * 0.10 WHERE UPrice > 100;
UPDATE Product SET Discount = 0 WHERE UPrice <= 100;

Explanation:

• For products with UPrice > 100 (Washing Powder: 120, Shampoo: 245), Discount = 10% of UPrice.
• For all other products, Discount = 0.
• CASE expression handles conditional logic within a single UPDATE statement.

SQL Query:

UPDATE Product
SET UPrice = UPrice * 1.12
WHERE Manufacturer = 'Dove';

Explanation:

• The UPDATE statement modifies existing data.
• UPrice = UPrice * 1.12 increases the price by 12% (multiplying by 1.12 adds 12% to the original price).
• The WHERE clause restricts the update to only those rows where Manufacturer = 'Dove' (Soap P05 and Shampoo P06).

SQL Query:

SELECT Manufacturer, COUNT(*) AS TotalProducts
FROM Product
GROUP BY Manufacturer;

Explanation:

• GROUP BY Manufacturer groups all rows by manufacturer name.
• COUNT(*) counts the number of products in each group.

Expected Output:

+--------------+---------------+
| Manufacturer | TotalProducts |
+--------------+---------------+
| Surf         |             1 |
| Colgate      |             1 |
| Lux          |             1 |
| Pepsodent    |             1 |
| Dove         |             2 |
+--------------+---------------+

Given Query: SELECT PName, avg(UPrice) FROM Product GROUP BY Pname;

Concept: GROUP BY PName groups rows with the same product name, and AVG(UPrice) calculates the average price for each group.

Working:

• Washing Powder: only P01 → AVG = 120.0000
• Toothpaste: P02 (54) and P04 (65) → AVG = $(54+65)/2 = 59.5000$
• Soap: P03 (25) and P05 (38) → AVG = $(25+38)/2 = 31.5000$
• Shampoo: only P06 → AVG = 245.0000

Output:

+----------------+------------+
| PName          | avg(UPrice)|
+----------------+------------+
| Washing Powder |   120.0000 |
| Toothpaste     |    59.5000 |
| Soap           |    31.5000 |
| Shampoo        |   245.0000 |
+----------------+------------+

Given Query: SELECT DISTINCT Manufacturer FROM Product;

Concept: DISTINCT eliminates duplicate values and displays each manufacturer only once.

Working: From the table, manufacturers are: Surf, Colgate, Lux, Pepsodent, Dove, Dove → after DISTINCT: Surf, Colgate, Lux, Pepsodent, Dove.

Output:

+--------------+
| Manufacturer |
+--------------+
| Surf         |
| Colgate      |
| Lux          |
| Pepsodent    |
| Dove         |
+--------------+

Given Query: SELECT COUNT(DISTINCT PName) FROM Product;

Concept: COUNT(DISTINCT PName) counts the number of distinct (unique) product names.

Working: PNames in the table: Washing Powder, Toothpaste, Soap, Toothpaste, Soap, Shampoo.
Distinct PNames: Washing Powder, Toothpaste, Soap, Shampoo → 4 distinct names.

Output:

+----------------------+
| COUNT(DISTINCT PName)|
+----------------------+
|                    4 |
+----------------------+

Given Query: SELECT PName, MAX(UPrice), MIN(UPrice) FROM Product GROUP BY PName;

Concept: GROUP BY PName groups rows by product name. MAX(UPrice) and MIN(UPrice) return the maximum and minimum price within each group.

Working:

• Washing Powder: only 120 → MAX=120, MIN=120
• Toothpaste: 54, 65 → MAX=65, MIN=54
• Soap: 25, 38 → MAX=38, MIN=25
• Shampoo: only 245 → MAX=245, MIN=245

Output:

+----------------+------------+------------+
| PName          | MAX(UPrice)| MIN(UPrice)|
+----------------+------------+------------+
| Washing Powder |        120 |        120 |
| Toothpaste     |         65 |         54 |
| Soap           |         38 |         25 |
| Shampoo        |        245 |        245 |
+----------------+------------+------------+

SQL Query:

ALTER TABLE INVENTORY
ADD Discount DECIMAL(10,2) DEFAULT 0;

Explanation:

• ALTER TABLE INVENTORY modifies the existing INVENTORY table.
• ADD Discount DECIMAL(10,2) adds a new column Discount with decimal data type to store discount values.
• DEFAULT 0 initialises the discount to 0 for all existing rows.

SQL Query:

UPDATE INVENTORY
SET Discount = CASE
    WHEN Model = 'LXI' THEN 0
    WHEN Model = 'VXI' THEN Price * 0.10
    ELSE Price * 0.12
END;

Alternative using separate UPDATE statements:

-- (i) No discount for LXI
UPDATE INVENTORY SET Discount = 0 WHERE Model = 'LXI';

-- (ii) 10% discount for VXI
UPDATE INVENTORY SET Discount = Price * 0.10 WHERE Model = 'VXI';

-- (iii) 12% discount for all other models
UPDATE INVENTORY SET Discount = Price * 0.12
WHERE Model NOT IN ('LXI', 'VXI');

Explanation:

• The CASE expression handles all three conditions in a single UPDATE statement.
• Model = 'LXI' → Discount = 0
• Model = 'VXI' → Discount = 10% of Price
• All other models → Discount = 12% of Price

SQL Query:

SELECT CarName
FROM INVENTORY
WHERE FuelType = 'Petrol' AND Price = (
    SELECT MAX(Price)
    FROM INVENTORY
    WHERE FuelType = 'Petrol'
);

Alternative using ORDER BY and LIMIT:

SELECT CarName
FROM INVENTORY
WHERE FuelType = 'Petrol'
ORDER BY Price DESC
LIMIT 1;

Explanation:

• The subquery SELECT MAX(Price) FROM INVENTORY WHERE FuelType = 'Petrol' finds the maximum price among all petrol cars.
• The outer query retrieves the CarName of the car with that maximum price and fuel type 'Petrol'.

SQL Query:

SELECT AVG(Discount) AS AverageDiscount,
       SUM(Discount) AS TotalDiscount
FROM INVENTORY
WHERE CarName = 'Baleno';

Explanation:

• AVG(Discount) calculates the average of the Discount column for all Baleno cars.
• SUM(Discount) calculates the total (sum) of all discounts for Baleno cars.
• The WHERE CarName = 'Baleno' clause filters only Baleno cars.

SQL Query:

SELECT COUNT(*) AS CarsWithNoDiscount
FROM INVENTORY
WHERE Discount = 0 OR Discount IS NULL;

Explanation:

• COUNT(*) counts the total number of rows satisfying the condition.
• Discount = 0 covers cars with explicitly zero discount (LXI model cars).
• Discount IS NULL covers any cars where discount has not been set.
• Together, these conditions identify all cars with no discount.