NCERT Solution IP class 12 chapter 1 Querying and SQL Functions

IP class 12 Chapter 1 Querying and SQL Functions

1. Answer the following questions:

a) Define RDBMS. Name any two RDBMS software.

Ans. RDBMS stands for Relational Database Management System. It is a program that offers commands to create, update, and manage the data with multiple tables. Examples of RDBMS are

1. MySQL
2. Oracle
3. Microsoft SQL Server

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

i) ORDER BY
ii) HAVING

Ans. i) Order By : This clause is used to arrange the records in ascending or descending order. for example Select * from book order by price;

ii) Having : HAVING Clause in SQL is used to specify conditions on the rows with GROUP BY clause. for example Select sum(price) from book group by (subject) having price > 100;

c) Site any two differences between Single_row functions and Aggregate functions.

Single row Functions	Multiple row functions / Aggregate Functions
It operates on a single row at a time.	It operates on multiple rows.
It returns one result per row	It returns one result for multiple rows.
It can be used in Select, Where, and Order by clause.	It can be used in the select clause only.
Math, String and Date functions are examples of single row functions.	Max(), Min(), Avg(), Sum(), Count() and Count(*) are examples of multiple row functions.

d) What do you understand by Cartesian Product?

Ans. Cartesian product combines tuples from two relations. It results in all pairs of rows from the two relations, regardless of whether or not they have the same values on common attributes

e) Write the name of the functions to perform thefollowing 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.

Ans. i) dayname( )

ii) mid( ) or substr( ) or substring( )

iii) monthname( )

iv) upper( ) or ucase( )

IP class 12 Chapter 1 :

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

a) SELECT POW(2,3);

b) SELECT ROUND(123.2345, 2), 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(“Computer Science”,4);

f) SELECT MID(“Informatics”,3,4), SUBSTR(“Practices”,3);

Ans. a) 8

b) 123.23 340

c) 21

d) 1979 11 26 November

e) IND ence

f) form actices

IP class 12 Chapter 1 :

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

Pcode	PName	UPrice	Manufacture
P01	Washing Powder	120	Surf
P02	Tooth Paste	54	Colgate
P03	Soap	25	Lux
P04	Tooth Paste	65	Pepsodent
P05	Soap	38	Dove
P06	Shampoo	245	Dove

a) Write SQL queries for the following:

i. Create the table Product with appropriate data types and constraints.

ii. Identify the primary key in Product.

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

iv. Add a new column Discount to the table Product.

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

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

vii. Display the total number of products manufactured by each manufacturer.

Ans.

i) Create table product(Pcode varchar(3) not null Primary key , PName Varchar(20), UPrice int(4), Manufacture Varchar(20));

ii) Pcode is primary key.

iii) Select Pcode, PName, Uprice from product order by PName desc, UPrice;

iv) Alter table product add Discount int(3);

v) update product set discount = 0;
    update product set discount = 10/100 * UPrice where UPrice > 100;
    

vi) Update product set UPrice = UPrice + 12/100 * UPrice where Manufacture = “Dove”;

vii) Select count(*), Manufacture from product group by Manufacture;

b) Write the output(s) produced by executing the following queries on the basis of the information given above in the table Product:

i. SELECT PName, Average(UPrice) FROM Product GROUP BY Pname;

ii. SELECT DISTINCT Manufacturer FROM Product;

iii. SELECT COUNT(DISTINCT PName) FROM Notes Product;

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

Ans.

i. It shows error as there is no function average( ). Correct function is avg( )

If the function is correct then the output will be

PName	Average(UPrice)
Washing Powder	120
Tooth Paste	59.5
Soap	31.5
Shampoo	245

ii.

Manufacture

Surf

Colgate

Lux

Pepsodent

Dove

iii. 4

iv.

PName	MAX(UPrice)	MIN(UPrice)
Washing Powder	120	120
Tooth Paste	65	54
Soap	38	25
Shampoo	245	245

IP class 12 Chapter 1 :

4. 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:
(i) No discount is available on the LXI model.
(ii) VXI model gives a 10% discount.
(iii) A 12% 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 Car4.

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

Ans.

a) Alter table inventory add Discount int(3);

b)

(i) update inventory set Discount =0 where model=”LXI”;

(ii) update inventory set Discount =10 where model=”VXI”;

(iii) update inventory set Discount =10 where model not in (“VXI”, “LXI”);

c) Select max(price) from Inventory where FuelType=”petrol”;

d) Select avg(Discount), sum(Discount) from inventory where CarName=”car4″;

e) Select count(*) from inventory where Discount=0;

IP class 12 Chapter 1 :

Q5. Consider the following tables Student and Stream in the Streams_of_Students database. The primary key of the Stream table is StCode (stream code) which is the foreign key in the Student table. The primary key of the Student table is AdmNo (admission number).

Table : Student

AdmNo	Name	StCode
211	Jay	NULL
241	Aditya	S03
290	Diksha	S01
333	Jasqueen	S02
356	Vedika	S01
380	Ashpreet	S03

Table : Stream

StCode	Stream
S01	Science
S02	Commerce
S03	Humanities

Write SQL queries for the following:

a) Create the database Streams_Of_Students.

Ans. Create database Streams_Of_Students;

b) Create the table Student by choosing appropriate data types based on the data given in the table.

Ans. Create table student (AdmNo int, Name varchar(20), StCode varchar(4));

c) Identify the Primary keys from tables Student and Stream. Also, identify the foreign key from the table Stream.

Ans. Primary Keys :

Student Table : AdmNo is a Primary Key

Stream Table : StCode is a Primary Key

Foreign Keys :

Stream Table : No Foreign Key

Student Table : StCode is a Foreign Key

d) Jay has now changed his stream to Humanities. Write an appropriate SQL query to reflect this change.

Ans. Update student set StCode = “S03” where Name = “Jay”;

e) Display the names of students whose names end with the character ‘a’. Also, arrange the students in alphabetical order.

Ans. Select Name from Student Where Name like “%a” order by Name;

f) Display the names of students enrolled in Science and Humanities stream, ordered by student name in alphabetical order, then by admission number in ascending order (for duplicating names).

Ans. Select Name from Student where StCode in (“S01” , “S03”) order by Name, Admno;

g) List the number of students in each stream having more than 1 student.

Ans. Select StCode, count(*) from Student group by StCode having count(StCode) > 1;

h) Display the names of students enrolled in different streams, where students are arranged in descending order of admission number.

Ans. Select Name from Student order by AdmNo desc;

i) Show the Cartesian product on the Student and Stream table. Also mention the degree and cardinality produced after applying the Cartesian product.

Ans. Select * from Student, Stream;

Cardinality after Cartesian Product : 18

Degree after Cartesian Product : 5

j) Add a new column ‘TeacherIncharge” in the Stream table. Insert appropriate data in each row.

Ans. Alter table Stream add column TeacherIncharge Varchar(30);

Update Stream Set TeacherIncharge = “Mr. Ravi” where Stream = “Science”

Update Stream Set TeacherIncharge = “Ms Sonam” where Stream = “Commerce”

Update Stream Set TeacherIncharge = “Mr. Raman” where Stream = “Humanities”

k) List the names of teachers and students.

Ans. Select Student.Name, Stream.TeacherIncharge from Student, Stream where Student.StCode = Stream.StCode;

l) If Cartesian product is again applied on Student and Stream tables, what will be the degree and cardinality of this modified table?

Ans. Degree : 6

Cardinality : 18

---

IP class 12 Chapter 1 :

Disclaimer : I tried to give you the correct “IP Class 12 Chapter 1 NCERT Solution” , but if you feel that there is/are mistakes in any question or answers of “IP Class 12 Chapter 1 NCERT Solution” given above, you can directly contact me at csiplearninghub@gmail.com. Book and Study material available on CBSE official website is used as reference to create above “IP Class 12 Chapter 1 NCERT Solution“. NCERT book is used as a reference to create above “IP Class 12 Chapter 1 NCERT Solution”. Screenshot is also taken from NCERT to create image used in above article.

---