NCERT Solutions for Class 12 Computer Science Chapter 9: Structured Query Language (SQL)

1. DAYNAME("1947-08-15")

2. SUBSTRING(string, pos, n)

3. MONTHNAME("yyyy-mm-dd")

4. UPPER('YourName')

 

Table inventory

(a)

ALTER TABLE INVENTORY

ADD COLUMN Discount FLOAT;

(b)

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

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

| CarId | CarName | Price     | Model     | YearManufacture | FuelType | FinalPrice | Discount |

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

| D001  | Dzire   | 582613.00 | LXI       |            2017 | Petrol   |  652526.60 |        0 |

| D002  | Dzire   | 673112.00 | VXI       |            2018 | Petrol   |  753885.40 |  67311.2 |

| B001  | Baleno  | 567031.00 | Sigma1.2  |            2019 | Petrol   |  635074.70 |  68043.7 |

| B002  | Baleno  | 647858.00 | Delta1.2  |            2018 | Petrol   |  725601.00 |    77743 |

| E001  | EECO    | 355205.00 | 5 STR STD |            2017 | CNG      |  397829.60 |  42624.6 |

| E002  | EECO    | 654914.00 | CARE      |            2018 | CNG      |  733503.70 |  78589.7 |

| S001  | SWIFT   | 514000.00 | LXI       |            2017 | Petrol   |  575680.00 |        0 |

| S002  | SWIFT   | 614000.00 | VXI       |            2018 | Petrol   |  687680.00 |    61400 |

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

 

(c)

SELECT CarName

FROM INVENTORY

WHERE FuelType = 'Petrol'

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

Output

+---------+

| CarName |

+---------+

| Dzire   |

+---------+

 

(d)

SELECT AVG(Discount) AS AverageDiscount,

SUM(Discount) AS TotalDiscount

FROM INVENTORY

WHERE CarName = 'Baleno';

Output

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

| AverageDiscount | TotalDiscount  |

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

|  72893.33984375 | 145786.6796875 |

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

 

(e)

SELECT COUNT(*)

FROM INVENTORY

WHERE Discount = 0;

Output

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

| COUNT(*) |

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

|        2 |

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

 

An RDBMS is a database management system based on the relational model, storing data in tables of rows and columns, enabling data retrieval, manipulation, and relationships through SQL queries. Two examples of RDBMS software are MySQL and Oracle Database.

 

(i) ORDER BY clause is used to sort the result set of a SELECT statement either in ascending (default) or descending order based on one or more columns. The ASC keyword is used for ascending order, and the DESC keyword is used for descending order.

(ii) GROUP BY clause is used to group rows that have the same values in specified columns into summary rows. It is commonly used with aggregate functions (e.g., SUM, COUNT, AVG) to perform calculations on grouped data.

 

Two differences between Single Row Functions and Aggregate Functions are:

 

The Cartesian Product is an operation that combines tuples from two relations. It results in all possible pairs of rows from the two input relations, regardless of whether they have matching values on common attributes. This operation is denoted by the cross join symbol (×) in SQL.

 

(i) Differences between ALTER and UPDATE statements:

(ii) Differences between DELETE and DROP statements:

 

(a)

Output

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

| POW(2, 3) |

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

|         8 |

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

 

(b)

Output

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

| ROUND(342.9234, -1) |

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

|                 340 |

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

 

(c)

Output

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

| LENGTH("Informatics Practices") |

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

|                              21 |

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

 

(d)

Output

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

| YEAR("1979/11/26") | MONTH("1979/11/26") | DAY("1979/11/26") | MONTHNAME("1979/11/26") |

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

|               1979 |                  11 |                26 | November                |

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

 

(e)

Output

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

| LEFT("INDIA", 3) | RIGHT("ComputerScience", 4) | MID("Informatics", 3, 4) | SUBSTR("Practices", 3) |

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

| IND              | ence                        | form                     | actices                |

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

 

(a)

SELECT * FROM Movie;

Output

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

| MOVIEID | MOVIENAME     | CATEGORY  | RELEASEDATE | PRODUCTIONCOST | BUSINESSCOST |

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

|       1 | Hindi_Movie   | Musical   | 2018-04-23  |         124500 |       130000 |

|       2 | Tamil_Movie   | Action    | 2016-05-17  |         112000 |       118000 |

|       3 | English_Movie | Horror    | 2017-08-06  |         245000 |       360000 |

|       4 | Bengali_Movie | Adventure | 2017-01-04  |          72000 |       100000 |

|       5 | Telugu_Movie  | Action    | NULL        |         100000 |         NULL |

|       6 | Punjabi_Movie | Comedy    | NULL        |          30500 |         NULL |

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

 

(b)

SELECT MovieID, MovieName, (ProductionCost + BusinessCost) AS Total_Earning

FROM Movie

WHERE ReleaseDate IS NOT NULL;

Output

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

| MovieID | MovieName     | Total_Earning |

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

|       1 | Hindi_Movie   |        254500 |

|       2 | Tamil_Movie   |        230000 |

|       3 | English_Movie |        605000 |

|       4 | Bengali_Movie |        172000 |

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

 

(c)

SELECT DISTINCT Category FROM MOVIE;

Output

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

| Category  |

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

| Musical   |

| Action    |

| Horror    |

| Adventure |

| Comedy    |

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

 

(d)

SELECT MovieID, MovieName, BusinessCost - ProductionCost AS NetProfit

FROM Movie

WHERE ReleaseDate IS NOT NULL;

Output

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

| MovieID | MovieName     | NetProfit |

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

|       1 | Hindi_Movie   |      5500 |

|       2 | Tamil_Movie   |      6000 |

|       3 | English_Movie |    115000 |

|       4 | Bengali_Movie |     28000 |

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

 

(e)

SELECT MovieID, MovieName, ProductionCost AS Cost

FROM MOVIE

WHERE ProductionCost > 10000 AND ProductionCost < 100000;

Output

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

| MovieID | MovieName     | Cost  |

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

|       4 | Bengali_Movie | 72000 |

|       6 | Punjabi_Movie | 30500 |

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

 

(f)

SELECT * FROM MOVIE

WHERE Category = 'Comedy' OR Category = 'Action';

Output

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

| MOVIEID | MOVIENAME     | CATEGORY | RELEASEDATE | PRODUCTIONCOST | BUSINESSCOST |

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

|       2 | Tamil_Movie   | Action   | 2016-05-17  |         112000 |       118000 |

|       5 | Telugu_Movie  | Action   | NULL        |         100000 |         NULL |

|       6 | Punjabi_Movie | Comedy   | NULL        |          30500 |         NULL |

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

 

(g)

SELECT * FROM MOVIE

WHERE ReleaseDate IS NULL;

Output

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

| MOVIEID | MOVIENAME     | CATEGORY | RELEASEDATE | PRODUCTIONCOST | BUSINESSCOST |

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

|       5 | Telugu_Movie  | Action   | NULL        |         100000 |         NULL |

|       6 | Punjabi_Movie | Comedy   | NULL        |          30500 |         NULL |

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

 

(a)

CREATE DATABASE Sports;

(b)

CREATE TABLE TEAM (

    TeamID INT,

    TeamName VARCHAR(20));

(c)

ALTER TABLE TEAM

ADD PRIMARY KEY(TeamID);

(d)

DESCRIBE TEAM;

Output

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

| Field    | Type     | Null | Key | Default | Extra |

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

| TeamID   | int      | NO   | PRI | NULL    |       |

| TeamName | char(20) | YES  |     | NULL    |       |

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

 

(e)

INSERT INTO TEAM (TeamID, TeamName) VALUES 

(1, 'Team Titan'),

(2, 'Team Rockers'),

(3, 'Team Magnet'),

(4, 'Team Hurricane');

(f)

SELECT * FROM TEAM;

Output

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

| TeamID | TeamName       |

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

|      1 | Team Titan     |

|      2 | Team Rockers   |

|      3 | Team Magnet    |

|      4 | Team Hurricane |

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

 

(g)

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

Output

SELECT * FROM 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 |

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

(a)

CREATE TABLE Product (

    PCode VARCHAR(10) PRIMARY KEY,

    PName VARCHAR(50),

    UPrice int,

    Manufacturer VARCHAR(50)

);

(b) The primary key in the table "Product" is PCode.

(c)

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)

ALTER TABLE Product

ADD COLUMN Discount float;

(e)

UPDATE Product 

SET Discount = IF(UPrice > 100, (UPrice * (10/100)) + UPrice, 0);

Output

SELECT * FROM Product;

 

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

| PCode | PName          | UPrice | Manufacturer | Discount |

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

| P01   | WASHING POWDER |    120 | SURF         |       12 |

| P02   | TOOTHPASTE     |     54 | COLGATE      |        0 |

| P03   | SOAP           |     25 | LUX          |        0 |

| P04   | TOOTHPASTE     |     65 | PEPSODENT    |        0 |

| P05   | SOAP           |     38 | DOVE         |        0 |

| P06   | SHAMPOO        |    245 | DOVE         |     24.5 |

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

 

(f)

UPDATE Product

SET UPrice = (UPrice * (12/100)) + UPrice

WHERE Manufacturer = 'Dove';

Output

 

SELECT * from Product;

 

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

| PCode | PName          | UPrice | Manufacturer | Discount |

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

| P01   | WASHING POWDER |    120 | SURF         |       12 |

| P02   | TOOTHPASTE     |     54 | COLGATE      |        0 |

| P03   | SOAP           |     25 | LUX          |        0 |

| P04   | TOOTHPASTE     |     65 | PEPSODENT    |        0 |

| P05   | SOAP           |     43 | DOVE         |        0 |

| P06   | SHAMPOO        |    274 | DOVE         |     24.5 |

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

 

(g)

SELECT Manufacturer, COUNT(*) AS TotalProducts

FROM Product

GROUP BY Manufacturer;

Output

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

| Manufacturer | TotalProducts |

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

| SURF         |             1 |

| COLGATE      |             1 |

| LUX          |             1 |

| PEPSODENT    |             1 |

| DOVE         |             2 |

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

 

(h)

SELECT PName, avg(UPrice) FROM Product GROUP 

BY Pname;

Output

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

| PName          | avg(UPrice) |

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

| WASHING POWDER |    120.0000 |

| TOOTHPASTE     |     59.5000 |

| SOAP           |     34.0000 |

| SHAMPOO        |    274.0000 |

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

 

(i)

SELECT DISTINCT Manufacturer FROM Product;

Output

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

| Manufacturer |

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

| SURF         |

| COLGATE      |

| LUX          |

| PEPSODENT    |

| DOVE         |

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

 

(j)

SELECT COUNT(DISTINCT PName) FROM Product;

Output

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

| COUNT(DISTINCT PName) |

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

|                     4 |

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

 

(k)

SELECT PName, MAX(UPrice), MIN(UPrice) FROM 

Product GROUP BY PName;

Output

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

| PName          | MAX(UPrice) | MIN(UPrice) |

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

| WASHING POWDER |         120 |         120 |

| TOOTHPASTE     |          65 |          54 |

| SOAP           |          43 |          25 |

| SHAMPOO        |         274 |         274 |

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

(a)

SELECT MatchID

FROM MATCH_DETAILS

WHERE FirstTeamScore > 70 AND SecondTeamScore > 70;

Output

+---------+

| MatchID |

+---------+

| M1      |

+---------+

 

(b)

SELECT MatchID

FROM MATCH_DETAILS

WHERE FirstTeamScore < 70 AND SecondTeamScore > 70;

Output

+---------+

| MatchID |

+---------+

| M5      |

+---------+

 

(c)

SELECT MatchID, MatchDate

FROM MATCH_DETAILS

WHERE FirstTeamID = 1 AND FirstTeamScore > SecondTeamScore;

Output

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

| MatchID | MatchDate  |

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

| M1      | 2018-07-17 |

| M3      | 2018-07-19 |

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

 

(d)

SELECT MatchID

FROM MATCH_DETAILS

WHERE SecondTeamID = 2 AND SecondTeamScore <= FirstTeamScore;

Output

+---------+

| MatchID |

+---------+

| M1      |

+---------+

 

(e)

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

 

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