NCERT Solution IP class 12 chapter 1 Querying and SQL Functions

Share with others

IP class 12 Chapter 1 Querying and SQL Functions

ip Class 12 Chapter 1
IP Class 12 Chapter 1

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 FunctionsMultiple row functions / Aggregate Functions
It operates on a single row at a time.It operates on multiple rows.
It returns one result per rowIt 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.
IP Class 12 Chapter 1

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.

PcodePNameUPriceManufacture
P01Washing Powder120Surf
P02Tooth Paste54Colgate
P03Soap25Lux
P04Tooth Paste65Pepsodent
P05Soap38Dove
P06Shampoo245Dove
IP Class 12 Chapter 1

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

PNameAverage(UPrice)
Washing Powder120
Tooth Paste59.5
Soap31.5
Shampoo245
IP Class 12 Chapter 1

ii.

Manufacture
Surf
Colgate
Lux
Pepsodent
Dove
IP Class 12 Chapter 1

iii. 4

iv.

PNameMAX(UPrice)MIN(UPrice)
Washing Powder120120
Tooth Paste6554
Soap3825
Shampoo245245
IP Class 12 Chapter 1

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

AdmNoNameStCode
211JayNULL
241AdityaS03
290DikshaS01
333JasqueenS02
356VedikaS01
380AshpreetS03
IP Class 12 Chapter 1

Table : Stream

StCodeStream
S01Science
S02Commerce
S03Humanities
IP Class 12 Chapter 1

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.


Class 12 Computer Science Sample Paper 2020-2021.

Class 12 Computer Science Sample Paper Marking Scheme

Class 12 Computer Science Chapter wise MCQ


Share with others

Leave a Reply

error: Content is protected !!