Easy NCERT Solutions Chapter 1 Querying and SQL Functions Class 12

This article is dedicated to NCERT Solutions for Chapter 1 Querying and SQL Functions Class 12 for the Informatics Practices Class 12. So let’s begin!

NCERT Solutions Chapter 1 Querying and SQL Functions Class 12

1. Answer the following questions:
   • Define RDBMS. Name any two RDBMS software.
     • RDBMS stands for Relational Database Management System.
     • It allows creating a relationship between more than one table and access data from multiple tables easily.
     • Basically, RDBMS is a program that offers commands to create, update, and manage the database with multiple tables.
     • The following are examples of RDBMS software:
       • MySQL
       • PostgreSQL
       • Oracle
       • SQL Server
       • SQLite
   • What is the purpose of the following clauses in a select statement?
     • ORDER BY: It is used to sort the records in ascending or descending order. If you want to sort records in descending order use the desc keyword at the end of the Select query.
     • HAVING: Having is used to restrict the result of the group by function rows according to condition. It will work as same as where clause.
   • Site any two differences between Single_row functions and Aggregate functions.
     • Single row functions operate on a single row from the table at a time whereas aggregate functions operate on a set of rows at a time.
     • Single row function display output for single row and display number rows whereas aggregate functions display out as a single value from a set of rows.
     • Single row function can be used with select, where and order by clause whereas aggregate functions can be only used with select clause.
     • Single row function examples are math, string and date/time functions. Aggregate functions examples are ma, min, average, count etc.
   • What do you understand by Cartesian Product?
     • Cartesian product refers to all possible pairs of rows from two relations.
     • It will just return a product of rows and the sum of columns from multiple relations with or without common attributes.
     • Suppose two tables student and result have 6 and 8 rows as well as 4 and 5 columns respectively. Then the cartesian product will return 48 rows and 9 columns as a result.
   • Write the name of the functions to perform the following operations:
     • To display the day like “Monday”, “Tuesday” from the date when India got independence.
       • dayname()
     • To display the specified number of characters from a particular position of the given string.
       • substr(),mid()
     • To display the name of the month in which you were born.
       • monthname()
     • To display your name in capital letters.
       • upper(),ucase()
2. Write the output produced by the following SQL commands:
   • SELECT POW(2,3);
     • 8
   • SELECT ROUND(123.2345, 2), ROUND(342.9234,-1);
     • 123.23 340
   • SELECT LENGTH(“Informatics Practices”);
     • 21
   • SELECT YEAR(“1979/11/26”), MONTH(“1979/11/26”), DAY(“1979/11/26”), MONTHNAME(“1979/11/26”);
     • 1979 11 26 November
   • SELECT LEFT(“INDIA”,3), RIGHT(“Computer Science”,4);
     • IND ence
   • SELECT MID(“Informatics”,3,4), SUBSTR(“Practices”,3);
     • form actices
3. Consider the following table named “Product”, showing details of products being sold in a grocery shop.

a) Write SQL queries for the following:

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

create table product(
-> pcode char(3) Primary key,
-> Pname varchar(25) Not Null,
-> Uprice int(4),
-> Manufacturer varchar(30));
mysql>insert into product values("P01","Washing Powder",120,"Surf");

• Identify the primary key in Product. > pcode
• 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.

select pcode,pname,uprice from product order by pname desc,uprice;

• Add a new column Discount to the table Product.

alter table product add discount decimal(8,2);

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

update product set discount =0;
update product set discount=0.10*uprice where uprice >100;

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

update product set uprice =uprice+0.12*uprice where manufacturer="dove";

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

select manufacturer, count(*)
-> from product group by manufacturer;

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

• SELECT PName, Average(UPrice) FROM Product GROUP BY Pname;
  • Error – Average() function does’nt exist in MySQL
• SELECT DISTINCT Manufacturer FROM Product;

Manufacturer |
+--------------+
| Surf |
| Colgate |
| Lux |
| Pepsodant |
| Dove |
+--------------+

• SELECT COUNT(DISTINCT PName) FROM Notes Product;

+-----------------------------+
| COUNT(DISTINCT PName) |
+-----------------------------+
|                                      4 |

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

+----------------+-------------+-------------+
| PName | MAX(UPrice) | MIN(UPrice) |
+----------------+-------------+-------------+
| Washing Powder | 120 | 120 |
| Tooth Paste | 65 | 54 |
| Soap | 43 | 25 |
| Shampoo | 274 | 274 |

4. Using the CARSHOWROOM database given in the chapter, write the SQL queries for the following:

• Add a new column Discount in the INVENTORY table.

alter table inventory add disc decimal(9,2);

• Set appropriate discount values for all cars keeping in mind the following:
• No discount is available on the LXI model.

                 update inventory set disc =0 where model="LXI";

• VXI model gives a 10% discount.

update inventory set disc =0.10*price where model="VXI";

• A 12% discount is given on cars other than LXI model and VXI model.

update inventory set disc =0.12*price where model NOT IN ("LXI","VXI");

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

select max(price) from inventory where fueltype="petrol";

• Calculate the average discount and total discount available on Car4.

select avg(disc),sum(disc) from inventory where carname="car4";

• List the total number of cars having no discount.

select count(*) from inventory where disc=0;

5. 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).

Write SQL queries for the following:

• Create the database Streams_Of_Students.

mysql> create database Streams_Of_Students

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

mysql> create table student(admno int,name varchar(30), stcode char(6));

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

Student:-> Primary key: admno, Foreign Key: stcode
Stream:-> Primary Key: Stcode, NO Foreign Key

• Jay has now changed his stream to Humanities.

Mysql>update student set stcode=”S03” where admno=211;

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

Mysql> select name from student where name like “%a” order by name;

• 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).

Mysql> select name from student where stcode in(“S01”,”S03”) order by name, admno;

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

mysql> Select stcode,count() From student Group by stcode Having count() >1;

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

select s1.admno, s1.name, s2.stream from student s1, stream s2 where s1.stcode=s2.stcode order by s1.admno desc;

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

select * from student, stream;
The cartesian product on the student and stream table has degree of 5 and cardinality of 18.

• Add a new column ‘TeacherIncharge” in the Stream table. Insert appropriate data in each row. (Do yourself)
• List the names of teachers and students.

select s1.teacherincharge,s2.name from stream s1, student s2 where s1.stcode=s2.stcode;

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

The degree will be 6 and the cardinality will be 18.

That’s all from the NCERT solution Chapter 1 Querying and SQL Functions Class 12 Informatics Practices. I hope you enjoyed this article. If you have any doubts or query feel free to ask in the comment section.

Watch this video for more understanding:

Follow this link for detailed notes in the topic Chapter 1 Querying and SQL Functions Class 12.

Thank you for reading this article Chapter 1 Querying and SQL Functions Class 12.