Question

NCERT Solutions Class 12, Computer Science, Chapter- 9, Structured Query Language (SQL)

To thoroughly grasp this chapter and excel in Board exams and competitive tests, utilizing NCERT Solutions is highly recommended. These solutions, crafted by experts in the field, delve into all key concepts covered in the chapter. Specifically designed for the CBSE curriculum, they ensure a comprehensive understanding and invaluable support in your academic endeavors.

In these NCERT Solutions for Class 12 Computer Science, we have discussed all types of NCERT intext questions and exercise questions.

Concepts covered in Class 12 Computer Science chapter- 9 Structured Query Language (SQL), are :

• Introduction to SQL
• Structured Query Language (SQL)
• Data Types and Constraints in MySQL
• SQL for Data Definition
• SQL for Data Manipulation
• SQL for Data Query
• Data Updation and Deletion
• Functions in SQL
• GROUP BY Clause in SQL
• Operations on Relations
• Using Two Relations in a Query

Our NCERT Solutions for Class 12 Computer Science provide detailed explanations to assist students with their homework and assignments. Proper command and ample practice of topic-related questions provided by our NCERT solutions is the most effective way to achieve full marks in your exams. Begin studying right away to ace your exams.

Easily access all solutions and practice questions at your fingertips to kick-start your preparation immediately.

Answer 1

NCERT Solutions Class 12, Computer Science, Chapter- 9, Structured Query Language (SQL)

Exercise

1. Answer the following questions:

a) Define RDBMS. Name any two RDBMS software.

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

i) ORDER BY

ii) GROUP BY

Solution:

a) Relational DBMS (RDBMS) is used to store data in related tables. Rows and columns of a table are called tuples and attributed respectively. A table is referred to as a relation. RDBMS software includes MySQL databases and Oracle SQL databases.

i) ORDER BY - ORDER BY clause is used to display data in an ordered form with respect to a specified column. By default, ORDER BY displays records in ascending order of the specified column’s values.

ii) GROUP BY - At times we need to fetch a group of rows on the basis of common values in a column. This can be done using a group by clause. It groups the rows together that contains the same values in a specified column.

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

Solution:

Single Row Function - 

1. It operates on a single row at a time.

2. It returns one result per row.

Aggregate Function - 

1. It operates on groups of rows.

2. It returns one result for a group of rows.

d) What do you understand by Cartesian Product?

Solution:

A Cartesian product is the result of joining every row in one table with every row in another table. This occurs when there is no WHERE clause to restrict rows.

e) Differentiate between the following statements:

i) ALTER and UPDATE

Solution:

ALTER - ALTER statement is used to add/remove an attribute or to modify the datatype of an existing attribute or to add constraint in attribute.

UPDATE - It is used to make changes in the value(s) of one or more columns of existing records in a table. For example, we may require some changes in address, phone number or spelling of name, etc.

ii) DELETE and DROP

Solution:

DELETE - DELETE statement is used to delete/remove one or more records from a table.

DROP - We use a DROP statement to remove a database or a table permanently from the system. However, one should be very cautious while using this statement as it cannot be undone.

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

i) To display the day like “Monday”, “Tuesday”, from the date when India got independence.

ii) To display the specified number of characters from a particular position of the given string.

iii)To display the name of the month in which you were born.

iv) To display your name in capital letters.

Solution:

i) DAYNAME(date) - DAYNAME(1947/08/15)

It returns the name of the day from the date.

ii) MID(string, pos, n)

Returns a substring of size n starting from the specified position (pos) of the string. If n is not specified, it returns the substring from the position pos till end of the string.

iii) MONTHNAME(date) 

It returns the month name from the specified date.

iv) UPPER(string) 

Converts string into uppercase.

2. Write the output produced by the following SQL statements:

a) SELECT POW(2,3);

Solution:

The result of the given query is 8.

b) SELECT ROUND(342.9234,-1);

Solution:

The result of the given query is 340.

c) SELECT LENGTH("Informatics Practices");

Solution:

The result of the given query is 21.

d) SELECT YEAR(“1979/11/26”),

MONTH(“1979/11/26”), DAY(“1979/11/26”),

MONTHNAME(“1979/11/26”);

Solution:

Year: 1979

Month: 11

Day: 26

Month Name: November

e) SELECT LEFT("INDIA",3), RIGHT("Computer Science",4), MID("Informatics",3,4), SUBSTR("Practices",3);

Solution:

The result of the given query is shown below:

IND
ence
from
actices

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

MovieID	MovieName	Category	ReleaseDate	ProductionCost	BusinessCost
001	Hindi_Movie	Musical	2018-04-23	124500	130000
002	Tamil_Movie	Action	2016-05-17	112000	118000
003	English_Movie	Horror	2017-08-06	245000	360000
004	Bengali_Movie	Adventure	2017-01-04	72000	100000
005	Telugu_Movie	Action	--	100000	--
006	Punjabi_Movie	Comedy	--	30500	--

Answer 2

a) Display all the information from the Movie table.

b) List business done by the movies showing only MovieID, MovieName and Total_Earning. Total_ Earning to be calculated as the sum of ProductionCost and BusinessCost.

c) List the different categories of movies.

d) Find the net profit of each movie showing its MovieID, MovieName and NetProfit. Net Profit is to be calculated as the difference between Business Cost and Production Cost.

e) List MovieID, MovieName and Cost for all movies with ProductionCost greater than 10,000 and less than 1,00,000.

f) List details of all movies which fall in the category of comedy or action.

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

Solution:

a) SELECT * FROM MOVIE;

b) SELECT MovieID, MovieName, ProductionCost + BusinessCost as “Total_Earning” FROM MOVIE;

c) SELECT DISTINCT Category FROM MOVIE;

OR

SELECT DISTINCT(Category) FROM MOVIE;

d) SELECT MovieID, MovieName, BusinessCost – ProductionCost as “Net Profict” 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;

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:

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

ii) 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 3: (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

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

Solution:

a) CREATE DATABASE Sports;

b) CREATE TABLE TEAM (TeamID int (3) Unique, TeamName varchar (30));

c) ALTER TABLE TEAM ADD Primary key (TeamID);

d) DESC TEAM;

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

f) SELECT "FROM TEAM;

g) CREATE TABLE MATCH DETAILS (MatchID varchar (5) Primary key, MatchDate DATE, First TeamID int (2), Second TeamID int(2), First TeamScore int (5), SecondTeamScore int (5));

INSERT INTO MATCH-DETAILS (MatchID, MatchDate, First TeamID, SecondTeamID, First TeamScore, Second'TeamScore) VALUES
('M1', 2018 - 17 - 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 - 20', 1, 4, 32, 87),
(M6', 2018 - 07 - 21', 2, 3, 67, 51);

5. Using the sports database containing two relations (TEAM, MATCH_DETAILS) and write the queries for the following:

a) Display the MatchID of all those matches where both the teams have scored more than 70.

b) Display the MatchID of all those matches where FirstTeam has scored less than 70 but SecondTeam has scored more than 70.

c) Display the MatchID and date of matches played by Team 1 and won by it.

d) Display the MatchID of matches played by Team 2 and not won by it.

e) Change the name of the relation TEAM to T_DATA. Also change the attributes TeamID and TeamName to T_ID and T_NAME respectively.

Solution:

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) SELECT MatchID, MatchDate FROM MATCH_DETAILS

WHERE (FirstTeamId = 1 AND FirstTeamScore > SecondTeamScore) OR (SecondTeamID = 1 AND SecondTeamScore > FirstTeamScore);

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

e) ALTER TABLE TEAM RENAME TO T_DATA;

ALTER TABLE T_DATA CHANGE TeamID T_ID INTEGER;

ALTER TABLE T_DATA CHANGE TeamName T_Name VARCHAR(30);

Answer 3

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

Solution:

a) INSERT INTO UNIFORM (UCode, UName, Color) VALUES(7, 'HANDKERCHIEF', 'RED'); This query will insert a new row in the UNIFORM table for Red HANDKERCHIEF.

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

c) ALTER TABLE UNIFORM ADD UName VARCHAR (20) NOT NULL;

d) ALTER TABLE COST ADD Cost INT CHECK( COST > 0);

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

PCode	PName	UPrice	Manufacturer
P01	Washing Powder	120	Surf
P02	Toothpaste	54	Colgate
P03	Soap	25	Lux
P04	Toothpaste	65	Pepsodent
P05	Soap	38	Dove
P06	Shampoo	245	Dove

Write SQL queries for the following:

a) Create the table Product with appropriate data types and constraints.

Solution:

create table Product (PCode varchar (15),
PName varchar (20),
UPrice varchar (10),
Manufacture varchar (30));

b) Identify the primary key in Product.

Solution:

The primary key in the product table is PCode.

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.

Solution:

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

d) Add a new column Discount to the table Product.

Solution:

ALTER TABLE Product
ADD Discount varchar (10);

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.

Solution:

UPDATE Product SET Discount = 0;
UPDATE Product SET Discount = 10 WHERE UPrice > 100;

f) Increase the price by 12 per cent for all the products manufactured by Dove.

Solution:

update product set uPrice = uPrice + uPrice *.12 where Manufacturer = 'dove';
select * from product;

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:

Solution:

select Manufacturer,count (pName) from product
group by Manufacturer;

h) SELECT PName, avg(UPrice) FROM Product GROUP BY Pname;

Solution:

i) SELECT DISTINCT Manufacturer FROM Product;

Solution:

j) SELECT COUNT (DISTINCT PName) FROM Product;

Solution:

k) SELECT PName, MAX(UPrice), MIN(UPrice) FROM Product GROUP BY PName;

Solution:

Answer 4

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.

Solution:

ALTER TABLE INVENTORY ADD Discount INT;

b) Set appropriate discount values for all cars keeping in mind the following:

(i) No discount is available on the LXI model.

Solution:

UPDATE INVENTORY SET Discount = 0 WHERE Model = 'LXI';

(ii) VXI model gives a 10 per cent discount.

Solution:

UPDATE INVENTORY SET Discount = 10 WHERE Model = 'VXI';

(iii) A 12 per cent discount is given on cars other than LXI model and VXI model.

Solution:

UPDATE INVENTORY SET Discount = 12 WHERE Model NOT IN (LXI', VXI');

c) Display the name of the costliest car with fuel type “Petrol”.

Solution:

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

d) Calculate the average discount and total discount available on Baleno cars.

Solution:

SELECT AVG(Discount), SUM(Discount) FROM INVENTORY WHERE CarType = 'Baleno',

e) List the total number of cars having no discount.

Solution:

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