NCERT Solutions Class 12 Computer Science Chapter 9 SQL

An RDBMS stores data in related two-dimensional tables (relations) of rows and columns and uses SQL to add, change, fetch and protect it. A single-row function returns one value per row; an aggregate function returns one value for a whole column. The Cartesian product pairs every row of one table with every row of another.

• a) RDBMS. Relational database software based on Codd's relational model; it keeps data in linked tables, enforces constraints, and answers SQL queries. Two RDBMS: MySQL and Oracle.
• b)(i) ORDER BY sorts the result rows on one or more columns, ascending (ASC, default) or descending (DESC). (ii) GROUP BY collects rows that share a column value into groups so an aggregate gives one summary value per group.
• c) Single-row vs aggregate. (1) Single-row returns one value per row (UPPER, ROUND); aggregate returns one value for many rows (SUM, AVG). (2) Single-row can be used in WHERE; aggregates are used with GROUP BY, not directly in a plain WHERE.
• d) Cartesian product. Two tables in FROM with no join condition pair every row of one with every row of the other. If A has m rows and B has n rows, the result has m x n rows (a cross join).
• e)(i) ALTER vs UPDATE. ALTER (DDL) changes table structure; UPDATE (DML) changes the data in existing rows. (ii) DELETE vs DROP. DELETE (DML) removes selected rows but keeps the table; DROP (DDL) removes the whole table.
• f) Functions. (i) DAYNAME() (ii) MID() or SUBSTR() (iii) MONTHNAME() (iv) UCASE() or UPPER().

Answer: RDBMS = relational table-based DB software (MySQL, Oracle); ORDER BY sorts, GROUP BY groups; single-row functions return one value per row, aggregates one per group; Cartesian product gives m x n rows; ALTER/DROP change structure, UPDATE/DELETE change data; the four functions are DAYNAME(), MID()/SUBSTR(), MONTHNAME() and UCASE()/UPPER().

These are single-row functions. POW(m,n) returns m to the power n. ROUND(x,d) rounds x to d places; a negative d rounds to the left of the decimal point, so -1 rounds to the nearest 10. LENGTH(str) counts all characters including spaces. The date functions pull year, month, day and month name. LEFT/RIGHT take characters from each end, and MID/SUBSTR take characters from a position counting from 1.

• (a) POW(2,3) = 2 x 2 x 2 = 8.
• (b) 342.9234 to the nearest ten: the units digit 2 is below 5, so it rounds down to 340.
• (c) "Informatics Practices" = 11 letters + 1 space + 9 letters = 21 characters.
• (d) From 1979/11/26: YEAR=1979, MONTH=11, DAY=26, MONTHNAME=November.
• (e) LEFT("INDIA",3)="IND"; RIGHT("Computer Science",4)="ence"; MID("Informatics",3,4)="form"; SUBSTR("Practices",3)="actices".

(a) 8
(b) 340
(c) 21
(d) 1979 | 11 | 26 | November
(e) IND | ence | form | actices

Answer: (a) 8 (b) 340 (c) 21 (d) 1979, 11, 26, November (e) IND, ence, form, actices.

SELECT * FROM t reads every column. An alias with AS renames a computed column. DISTINCT removes duplicates. A range uses > ... AND ...; IN checks membership; and an unreleased movie has no date, so the test is ReleaseDate IS NULL (you cannot compare to NULL with =).

-- (a)
SELECT * FROM MOVIE;
-- (b)
SELECT MovieID, MovieName,
       ProductionCost + BusinessCost AS Total_Earning FROM MOVIE;
-- (c)
SELECT DISTINCT Category FROM MOVIE;
-- (d)
SELECT MovieID, MovieName,
       BusinessCost - ProductionCost AS NetProfit FROM MOVIE;
-- (e)
SELECT MovieID, MovieName, ProductionCost FROM MOVIE
WHERE ProductionCost > 10000 AND ProductionCost < 100000;
-- (f)
SELECT * FROM MOVIE WHERE Category IN ('Comedy', 'Action');
-- (g)
SELECT * FROM MOVIE WHERE ReleaseDate IS NULL;

For part (e), the range is strict on both ends, so only Bengali_Movie (72000) and Punjabi_Movie (30500) fall strictly inside. For part (g), Telugu_Movie and Punjabi_Movie have no release date.

Answer: (a) all rows; (b) IDs with Total_Earning; (c) Musical, Action, Horror, Adventure, Comedy; (d) each NetProfit; (e) Bengali_Movie (72000) and Punjabi_Movie (30500); (f) the Action and Comedy movies; (g) Telugu_Movie and Punjabi_Movie.

CREATE DATABASE makes a database and USE opens it. CREATE TABLE defines columns with types and constraints. A 1-to-9 range is enforced with CHECK; a 10-character string uses VARCHAR(10); a table-level primary key is a separate PRIMARY KEY(col) line. DESCRIBE shows the structure, INSERT INTO adds rows, and SELECT * shows them.

-- (a)
CREATE DATABASE Sports;
USE Sports;
-- (b) and (c) together
CREATE TABLE TEAM (
    TeamID   INT CHECK (TeamID BETWEEN 1 AND 9),
    TeamName VARCHAR(10) NOT NULL,
    PRIMARY KEY (TeamID)            -- table-level primary key
);
-- (d)
DESCRIBE TEAM;
-- (e)
INSERT INTO TEAM VALUES (1, 'Team Titan');
INSERT INTO TEAM VALUES (2, 'Team Rockers');
INSERT INTO TEAM VALUES (3, 'Team Magnet');
INSERT INTO TEAM VALUES (4, 'Team Hurricane');
-- (f)
SELECT * FROM TEAM;

-- (g) MATCH_DETAILS table
CREATE TABLE MATCH_DETAILS (
    MatchID         VARCHAR(4) PRIMARY KEY,
    MatchDate       DATE,
    FirstTeamID     INT,
    SecondTeamID    INT,
    FirstTeamScore  INT,
    SecondTeamScore INT,
    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);
INSERT INTO MATCH_DETAILS VALUES ('M2','2018-07-18',3,4,45,48);
INSERT INTO MATCH_DETAILS VALUES ('M3','2018-07-19',1,3,78,56);
INSERT INTO MATCH_DETAILS VALUES ('M4','2018-07-19',2,4,56,67);
INSERT INTO MATCH_DETAILS VALUES ('M5','2018-07-18',1,4,32,87);
INSERT INTO MATCH_DETAILS VALUES ('M6','2018-07-17',2,3,67,51);

Answer: Use CREATE DATABASE + USE, then CREATE TABLE TEAM with a CHECK (1 to 9), VARCHAR(10) name and table-level PRIMARY KEY(TeamID); DESCRIBE shows structure, INSERT adds the four teams, SELECT * shows them; MATCH_DETAILS uses a VARCHAR MatchID primary key, DATE, two foreign-key team ids and integer scores.

The WHERE clause filters rows with >, < joined by AND/OR. "Played by Team 1" means Team 1 sat in the first or second seat, so test FirstTeamID = 1 OR SecondTeamID = 1. "Won by Team 1" means its own score was higher. RENAME TABLE renames a table; ALTER TABLE ... CHANGE old new type renames a column (you must restate the type). The rows are M1(1v2,90:86), M2(3v4,45:48), M3(1v3,78:56), M4(2v4,56:67), M5(1v4,32:87), M6(2v3,67:51).

-- (a)
SELECT MatchID FROM MATCH_DETAILS
WHERE FirstTeamScore > 70 AND SecondTeamScore > 70;
-- (b)
SELECT MatchID FROM MATCH_DETAILS
WHERE FirstTeamScore < 70 AND SecondTeamScore > 70;
-- (c) Team 1 played and won
SELECT MatchID, MatchDate FROM MATCH_DETAILS
WHERE (FirstTeamID = 1 AND FirstTeamScore > SecondTeamScore)
   OR (SecondTeamID = 1 AND SecondTeamScore > FirstTeamScore);
-- (d) Team 2 played but did not win
SELECT MatchID FROM MATCH_DETAILS
WHERE (FirstTeamID = 2 AND FirstTeamScore < SecondTeamScore)
   OR (SecondTeamID = 2 AND SecondTeamScore < FirstTeamScore);
-- (e)
RENAME TABLE TEAM TO T_DATA;
ALTER TABLE T_DATA CHANGE TeamID   T_ID   INT;
ALTER TABLE T_DATA CHANGE TeamName T_NAME VARCHAR(10);

(a) M1
(b) M5
(c) M1, M3
(d) M1, M4

Answer: (a) M1; (b) M5; (c) M1 and M3; (d) M1 and M4; (e) RENAME TABLE then two ALTER ... CHANGE statements.

A foreign key links a child column (COST.UCode) to a parent primary key (UNIFORM.UniformCode), so a value enters the child only if it already exists in the parent (referential integrity). A NOT NULL constraint forces a column to always carry a value. A CHECK constraint restricts the allowed values, here Price > 0.

-- (a) parent row first, then the cost row
INSERT INTO UNIFORM (UniformCode, UName, UColor)
VALUES (7, 'Handkerchief', 'Red');
INSERT INTO COST (UCode, Size, Price)
VALUES (7, 'M', 100);

-- (b) referential integrity: COST.UCode must exist in UNIFORM
ALTER TABLE COST
ADD FOREIGN KEY (UCode) REFERENCES UNIFORM(UniformCode);
-- (c) a UCode must always have a name
ALTER TABLE UNIFORM
MODIFY UName VARCHAR(30) NOT NULL;
-- (d) price must be positive
ALTER TABLE COST
ADD CONSTRAINT chk_price CHECK (Price > 0);

Answer: (a) insert the UNIFORM parent row, then the COST row; (b) add a FOREIGN KEY on COST.UCode referencing UNIFORM(UniformCode); (c) make UName NOT NULL; (d) add CHECK (Price > 0).

PCode is unique per product, so it is the primary key. A multi-key ORDER BY col1 DESC, col2 ASC sorts on col1 and breaks ties with col2. ALTER ... ADD appends a column. UPDATE ... SET ... WHERE with a CASE handles the "otherwise 0" rule. Aggregates with GROUP BY give one row per group, and COUNT(DISTINCT col) counts unique values.

-- (a) and (b): PCode is the primary key
CREATE TABLE Product (
    PCode        VARCHAR(5) PRIMARY KEY,
    PName        VARCHAR(30),
    UPrice       DECIMAL(8,2),
    Manufacturer VARCHAR(30)
);
-- (c)
SELECT PCode, PName, UPrice FROM Product
ORDER BY PName DESC, UPrice ASC;
-- (d)
ALTER TABLE Product ADD Discount DECIMAL(8,2);
-- (e)
UPDATE Product
SET Discount = CASE WHEN UPrice > 100 THEN 0.10 * UPrice ELSE 0 END;
-- (f)
UPDATE Product SET UPrice = UPrice + 0.12 * UPrice
WHERE Manufacturer = 'Dove';
-- (g)
SELECT Manufacturer, COUNT(*) AS Total
FROM Product GROUP BY Manufacturer;

(h) PName | avg(UPrice)
    Washing Powder | 120.0000
    Toothpaste     |  59.5000
    Soap           |  31.5000
    Shampoo        | 245.0000
(i) Surf | Colgate | Lux | Pepsodent | Dove
(j) 4
(k) Washing Powder 120/120, Toothpaste 65/54,
    Soap 38/25, Shampoo 245/245

Answer: (b) PCode; (h) 120, 59.5, 31.5, 245; (i) Surf, Colgate, Lux, Pepsodent, Dove; (j) 4; (k) per PName: Washing Powder 120/120, Toothpaste 65/54, Soap 38/25, Shampoo 245/245.

ALTER ... ADD appends a column. A single CASE sets different discount values by model. "Costliest" means the highest price, found with ORDER BY Price DESC LIMIT 1 returning the name. AVG and SUM are aggregates over matching rows. "No discount" rows are those whose Discount equals 0. The two Baleno cars are models Sigma1.2 and Delta1.2, so both fall in the "other" bucket and get 12%.

-- (a)
ALTER TABLE INVENTORY ADD Discount DECIMAL(10,2);
-- (b) one CASE handles all three rules
UPDATE INVENTORY
SET Discount = CASE
        WHEN Model = 'LXI' THEN 0
        WHEN Model = 'VXI' THEN 0.10 * Price
        ELSE 0.12 * Price
     END;

-- (c) costliest Petrol car
SELECT CarName FROM INVENTORY
WHERE Fueltype = 'Petrol'
ORDER BY Price DESC LIMIT 1;
-- (d) average and total discount on Baleno cars
SELECT AVG(Discount) AS AvgDiscount, SUM(Discount) AS TotalDiscount
FROM INVENTORY WHERE CarName = 'Baleno';
-- (e) number of cars with no discount
SELECT COUNT(*) AS NoDiscountCars FROM INVENTORY
WHERE Discount = 0;

(c) Dzire        (Price 673112, Petrol, costliest)
(d) AvgDiscount = 72893.34 , TotalDiscount = 145786.68
(e) NoDiscountCars = 2     (the two LXI cars)

Answer: (a) ALTER ... ADD Discount; (b) one CASE: LXI 0, VXI 10%, else 12%; (c) Dzire (costliest Petrol, 673112); (d) average 72893.34 and total 145786.68 on the two Baleno cars; (e) 2 cars (both LXI) have no discount.