NCERT Solutions For Class 12 Computer Science Chapter 9 Structured Query Language SQL - 2025-26

1 (a) Define RDBMS. Name any two RDBMS software.

Answer

An RDBMS is a database system that follows the relational model. It organises information in table structures (rows & columns) and lets you relate, query, and modify that data using SQL. Common examples include MySQL and Oracle Database.

1 (b) What is the purpose of the following clauses in a select statement?

(i) ORDER BY

Answer: Arranges the resulting rows by one or more columns. By default it’s ascending; use DESC for descending and ASC to be explicit about ascending.

(ii) GROUP BY

Answer: Collects rows into groups that share the same values in specified column(s). It’s typically paired with aggregate functions such as SUM, COUNT, or AVG to produce summaries per group.

1(c) Site any two differences between Single Row Functions and Aggregate Functions.

Answer

1(d) What do you understand by Cartesian Product?

Answer

A Cartesian Product pairs every row of one table with every row of another, producing all possible combinations. In SQL this corresponds to a CROSS JOIN (or listing tables without a join condition).

1(e) Differentiate between the following statements:

(i) ALTER and UPDATE

(ii) DELETE and DROP

1 (f) Write the name of the functions to perform the following operations:

Answer

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("ComputerScience", 4),
      MID("Informatics", 3, 4),
      SUBSTR("Practices", 3);

3. Consider the following MOVIE table and write the SQL queries based on it.

Answer
(a) Display all the information from the Movie table.

SELECT * FROM Movie;

(b) List business done by the movies showing only MovieID, MovieName and Total_Earning.

SELECT MovieID, MovieName, (ProductionCost + BusinessCost) AS Total_Earning
FROM Movie
WHERE ReleaseDate IS NOT NULL;

(c) List the different categories of movies.

SELECT DISTINCT Category FROM MOVIE;

(d) Find the net profit of each released movie.

SELECT MovieID, MovieName, BusinessCost - ProductionCost AS NetProfit
FROM Movie
WHERE ReleaseDate IS NOT NULL;

(e) List MovieID, MovieName and Cost for movies with ProductionCost > 10,000 and < 1,00,000.

SELECT MovieID, MovieName, ProductionCost AS Cost
FROM MOVIE
WHERE ProductionCost > 10000 AND ProductionCost < 100000;

(f) List details of all movies which fall in Comedy or Action.

SELECT * FROM MOVIE
WHERE Category = 'Comedy' OR Category = 'Action';

(g) List details of all movies which have not been released yet.

SELECT * FROM MOVIE
WHERE ReleaseDate IS NULL;

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:

1. It should have a column TeamID for storing an integer value between 1 to 9, which refers to unique identification of a team.
   
   

2. 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

Answer:

(a) Create the database

CREATE DATABASE Sports;

(b) Create the TEAM table

CREATE TABLE TEAM (
  TeamID   INT,
  TeamName VARCHAR(20)
);

(c) Declare the primary key

ALTER TABLE TEAM
ADD PRIMARY KEY (TeamID);

(d) Display the table structure

DESCRIBE TEAM;

(e) Insert the four teams

INSERT INTO TEAM (TeamID, TeamName) VALUES
(1, 'Team Titan'),
(2, 'Team Rockers'),
(3, 'Team Magnet'),
(4, 'Team Hurricane');

(f) Show all rows in TEAM

SELECT * FROM TEAM;

(g) Create MATCH_DETAILS, insert matches, and view data

CREATE TABLE MATCH_DETAILS (
  MatchID         VARCHAR(10),
  MatchDate       DATE,
  FirstTeamID     INT,
  SecondTeamID    INT,
  FirstTeamScore  INT,
  SecondTeamScore INT,
  CONSTRAINT PK_MatchID PRIMARY KEY (MatchID)
);

INSERT INTO MATCH_DETAILS
(MatchID, MatchDate, FirstTeamID, SecondTeamID, FirstTeamScore, SecondTeamScore) 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);

SELECT * FROM MATCH_DETAILS;

5. Using the sports database … write the queries for the following:

Answer

(a) Both teams scored more than 70.

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

Output:

+---------+

| MatchID |

+---------+

| M1      |

+---------+

(b) FirstTeam < 70 and SecondTeam > 70.

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

Output

+---------+

| MatchID |

+---------+

| M5      |

+---------+

(c) Matches played by Team 1 and won by it.

SELECT MatchID, MatchDate
FROM MATCH_DETAILS
WHERE FirstTeamID = 1 AND FirstTeamScore > SecondTeamScore;

Output

+---------+------------+

| MatchID | MatchDate  |

+---------+------------+

| M1      | 2018-07-17 |

| M3      | 2018-07-19 |

+---------+------------+

(d) Matches played by Team 2 and not won by it.

SELECT MatchID
FROM MATCH_DETAILS
WHERE SecondTeamID = 2 AND SecondTeamScore <= FirstTeamScore;

Output

+---------+

| MatchID |

+---------+

| M1      |

+---------+

(e) Rename relation and columns.

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 CHAR(20);

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:

(b)

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

(c)

ALTER TABLE UNIFORM ADD CONSTRAINT CK_UName_UCode 

CHECK (UName IS NOT NULL);

(d)

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

7. Consider the following table named "Product", showing details of products being sold in a grocery shop:

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:

(a) Create the table Product.

CREATE TABLE Product (
  PCode        VARCHAR(10) PRIMARY KEY,
  PName        VARCHAR(50) NOT NULL,
  UPrice       INT NOT NULL CHECK (UPrice >= 0),
  Manufacturer VARCHAR(50) NOT NULL
);

(b) Identify the primary key in Product.

Answer: PCode is the primary key.

(c) List PCode, PName, UPrice ordered by PName (DESC), then UPrice (ASC).

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

Output:

(d) Add a new column Discount.

ALTER TABLE Product ADD COLUMN Discount FLOAT;

(e) Set Discount = 10% when UPrice > 100, else 0.

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

Output:

(f) Raise price by 12% for products by Dove.

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

(g) Count products by each manufacturer.

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

(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;

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:

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.

Answer

Table inventory:

(a) Add a new column Discount in INVENTORY.

ALTER TABLE INVENTORY
ADD COLUMN Discount FLOAT;

(b) Set the discounts per model.

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:

(c) Name of the costliest Petrol car.

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

Output:

+---------+

       | CarName |

       +---------+

           | Dzire   |

      +---------+

(d) Average and total discount on Baleno cars.

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

Output:

+-----------------+----------------+

| AverageDiscount | TotalDiscount  |

+-----------------+----------------+

|  72893.33984375 | 145786.6796875 |

+-----------------+----------------+

(e) Count cars with no discount.

SELECT COUNT(*)
FROM INVENTORY
WHERE Discount = 0;

Output:

+----------+

| COUNT(*) |

+----------+

|        2 |

+----------+

All questions are unchanged; only answer wording has been refreshed to ensure originality while preserving meaning and correctness.

Learn Structured Query Language (SQL) in Class 12 Computer Science

Understanding SQL fundamentals is vital for scoring high in Computer Science Class 12. This NCERT Solutions chapter focuses on data definition, manipulation, and constraints in MySQL, helping you strengthen your basics and improve practical database skills.

By practicing chapter-wise NCERT exercises, students can learn core concepts such as DDL, DML, primary keys, foreign keys, and data types. Regular revision of these SQL queries ensures deeper understanding and fast problem-solving during exams.

Stay consistent with your preparation. Focus on solving exercise-based SQL questions, reviewing all constraints and commands. This approach not only boosts conceptual clarity but also builds confidence for practical computer science exams.

CBSE Class 12 Computer Science Chapter-wise NCERT Solutions

CBSE Class 12 Computer Science Study Materials