Querying and SQL Functions — NCERT Solutions

COUNT(DISTINCT PName) counts the different product names:

• Washing Powder
• Tooth Paste
• Soap
• Shampoo

There are 4 distinct product names.

If the question refers to the listed output set, the correct count is 4. The book-style options should include 4 as the answer.

The query uses Average(UPrice), but AVERAGE() is not a valid SQL function in the chapter. The correct aggregate function is AVG(). So the expected outputs are for the corrected query: SELECT PName, AVG(UPrice) FROM Product GROUP BY PName;

From the table:

• Washing Powder → $120$
• Tooth Paste → average of $54$ and $65$ = $(54+65)/2 = 119/2 = 59.5$
• Soap → average of $25$ and $38$ = $(25+38)/2 = 63/2 = 31.5$
• Shampoo → $245$

So the output is the distinct product names with their average price.

कार्टेशियन गुणनफल दो संबंधों के सभी संभव युग्म देता है। STUDENT में 6 रिकॉर्ड और STREAM में 3 रिकॉर्ड हैं, इसलिए परिणाम में $6 \times 3 = 18$ रिकॉर्ड होंगे।

Degree = $3 + 2 = 5$? नहीं, क्योंकि प्रश्न में केवल क्वेरी लिखनी है; chapter के अनुसार Cartesian product पर SELECT * FROM Student, Stream; लिखा जाता है।

SQL क्वेरी:

SELECT * FROM Student, Stream;

Jay अब Humanities stream में बदल गया है। Student table में stream code S03 Humanities को दर्शाता है, इसलिए AdmNo = 211 के लिए StCode को S03 set करेंगे.

SQL:

UPDATE Student
SET StCode = 'S03'
WHERE Name = 'Jay';

Students whose names end with 'a' can be found using LIKE '%a'. To arrange them alphabetically, use ORDER BY Name.

SQL:

SELECT Name
FROM Student
WHERE Name LIKE '%a'
ORDER BY Name;

To display students enrolled in Science and Humanities, we need to combine Student and Stream tables using a join on StCode, and then filter the required streams.

SQL:

SELECT S.Name
FROM Student S JOIN Stream T
ON S.StCode = T.StCode
WHERE T.Stream IN ('Science', 'Humanities')
ORDER BY S.Name, S.AdmNo;

The chapter states that COUNT(column) ignores NULL values, but *COUNT() returns the number of records in a table**. Since the question asks about COUNT(DISTINCT PName), it counts distinct non-NULL product names. In the given table there are 4 distinct PName values: Washing Powder, Tooth Paste, Soap, Shampoo.

Distinct product names in the table are:

• Washing Powder
• Tooth Paste
• Soap
• Shampoo

So, COUNT(DISTINCT PName) = 4.

To find the product with maximum UPrice among products grouped by name:

• Washing Powder: max $120$
• Tooth Paste: max of $54$ and $65$ = $65$
• Soap: max of $25$ and $38$ = $38$
• Shampoo: $245$

So the highest MAX(UPrice) is for Shampoo.

The query SELECT PName, MAX(UPrice), MIN(UPrice) FROM Product GROUP BY PName; gives one row for each PName.

Calculations:

• Washing Powder: max $120$, min $120$
• Tooth Paste: max $65$, min $54$
• Soap: max $38$, min $25$
• Shampoo: max $245$, min $245$

Output:

To calculate discount values according to the conditions:

• LXI model: no discount, so Discount = 0
• VXI model: Discount = 10% of Price
• Other models: Discount = 12% of Price

We first add the column, then update it using CASE.

SQL:

ALTER TABLE INVENTORY ADD Discount NUMERIC(10,2);

UPDATE INVENTORY
SET Discount = CASE
    WHEN Model = 'LXI' THEN 0
    WHEN Model = 'VXI' THEN Price * 10 / 100
    ELSE Price * 12 / 100
END;

To display the costliest car with fuel type Petrol, we need the maximum Price among petrol cars.

Petrol cars in INVENTORY are:

• D001: 582613
• D002: 673112
• B001: 567031
• B002: 647858
• S001: 514000
• S002: 614000

The highest is D002 with price 673112.00.

So the statement/option must match D002, not any other car.

To find the average discount and total discount on Car4, first note the CarIds for Car4 are S001 and S002.

Using the chapter’s discount rule from part (b):

• S001 is LXI → discount $0$
• S002 is VXI → discount $10\%$ of $614000 = 61400$

Average discount on Car4:
$\frac{0 + 61400}{2} = 30700$

Total discount on Car4:
$0 + 61400 = 61400$

SQL:

SELECT AVG(Discount), SUM(Discount)
FROM INVENTORY
WHERE CarName = 'Car4';

Cars having no discount are the LXI models. In the INVENTORY table, LXI appears for:

• D001
• S001

So the total number of cars having no discount is 2.

To create the database and table, use standard SQL as follows.

CREATE DATABASE Streams_Of_Students;

USE Streams_Of_Students;

CREATE TABLE Student (
    AdmNo INT PRIMARY KEY,
    Name VARCHAR(20),
    StCode CHAR(3)
);

To add TeacherIncharge to the Stream table and insert appropriate data, first alter the table, then update each row.

ALTER TABLE Stream ADD TeacherIncharge VARCHAR(20);

UPDATE Stream SET TeacherIncharge = 'Mr. Sharma' WHERE StCode = 'S01';
UPDATE Stream SET TeacherIncharge = 'Ms. Gupta' WHERE StCode = 'S02';
UPDATE Stream SET TeacherIncharge = 'Mr. Khan' WHERE StCode = 'S03';

The chapter states that in Cartesian product, the degree of the result is the sum of the degrees of both relations, and the cardinality is the product of their cardinalities.

For Student (3 attributes) and Stream (2 attributes):

• Degree = $3 + 2 = 5$
• Cardinality = $6 \times 3 = 18$

So the statement is false if it gives any other degree/cardinality.

To list the names of teachers and students, use a join between Student and Stream and select the relevant names.

SELECT Student.Name, Stream.TeacherIncharge
FROM Student JOIN Stream
ON Student.StCode = Stream.StCode;

The original Student table has 6 rows and the Stream table has 3 rows.

Cartesian product cardinality = $6 \times 3 = 18$.

If Cartesian product is again applied after adding a TeacherIncharge column, the number of rows does not change, so the cardinality remains 18. The degree becomes the sum of degrees:

• Student degree = 3
• Stream degree after adding one column = 3
• New degree = $3 + 3 = 6$

So the degree is 6 and cardinality 18.

The question corresponds to rounding 123.2345 to 2 decimals and 342.9234 to the nearest 10.

• ROUND(123.2345, 2) = 123.23
• ROUND(342.9234, -1) rounds to the nearest 10, so it becomes 340

If the printed options include this pair, the correct output is 123.23, 340.

LENGTH("Informatics Practices") counts all characters including the space.

• Informatics = 11 characters
• space = 1 character
• Practices = 9 characters

Total = $11 + 1 + 9 = 21$

So the output is 21.