Please refer to Querying and SQL Functions Class 12 Informatics Practices Important Questions with answers below. These solved questions for Chapter 1 Querying and SQL Functions in NCERT Book for Class 12 Social Science have been prepared based on the latest syllabus and examination guidelines issued by CBSE, NCERT, and KVS. Students should learn these solved problems properly as these will help them to get better marks in your class tests and examinations. You will also be able to understand how to write answers properly. Revise these questions and answers regularly. We have provided Notes for Class 12 Informatics Practices for all chapters in your textbooks.

Important Questions Class 12 Informatics Practices Chapter 2 Querying and SQL Functions

All Querying and SQL Functions Class 12 Informatics Practices Important Questions provided below have been prepared by expert teachers of Standard 12 Informatics Practices. Please learn them and let us know if you have any questions.

Very Short Answer Questions

Question: What is a unique key ? It is a Primary key? 
Answer: A Unique key in table/relation is any non-primary key field which also stores unique values for each row just like a primary-key does. But only one key is designated as primary key. So unique refers to a unique non-key field of a table.

Question: What is a primary key? 
Answer: A Primary key is a field or a combination of fields that can uniquely identify a row/tuple in a table/relation.

Question: What is a foreign key?
Answer: A foreign key is a field of a table which is the primary key of another table through a foreign key a relationship is established between two tables.

Question: How many primary key and unique keys can be there in a table? 
Answer: There can be multiple unique keys in a table but there can be only primary key in a table

Question: What is a composite primary key? 
Answer: If a table / relation has a primary key which is a combination of multiple columns of a table, it is known as a composite primary key.

Question: What is a tuple 1
Answer: A tuple refers to a row of relation.

Question: Give some examples of DML commands. Or Write the name of any two DML Commands of SQL ?
Answer: INSERT, UPDATE, SELECT and DELETE

Question: Give some examples of DDL commands. 
Answer: CREATE, ALTER, DROP

Question: In SQL,write the query to display the list of tables stroe in database. 
Answer: SHOW TABLES; 

Question: What is DML ? 
Answer: DML refers to the Data Manipulation Language component of SQL. The DML commands are used to manipulate and query upon the data stored in various tables of a database

Question: Give some examples of integrity constraints. 
Answer: NOT NULL
PRIMARY KEY
UNIQUE
CHECK

Question: What are constraints? 
Answer: Constraints are the rules or conditions imposed on various attributes of a table in a database so that only the data that satisfy these rules and conditions can get stored in the data table.

Question: What is the role of NOT NULL constraint? 
Answer: It indicates that in the data being inserted, the column must have some value and can not be left NULL.

Question: What is the role of UNIQUE constraints ?
Answer: This constraint ensures that for an attribute there will be unique value for each row and no value is being repeated in any other row for that attribute

Question: What will the SELECT ALL command do? 
Answer: The SELECT ALL command will fetch all the rows from a table as per the defined commands

Question: What is the role of FOREIGN KEY constraints?
Answer: This constraint is used to ensure the referential integrity of data in the table. It matches the value of the column designated as the foreign key in one table with another table’s Primary key.

Short Answer Questions

Question: Identify the problem/issue with the following SQL query :
SELECT house, count(*)
FROM student;
Answer: The problem with the given SQL query is that there is no GROUP BY clause is given and thus, it will lead to an error.
The reason being that the select list use COUNT() function, which is an aggregate function, along with a field. When we use an aggregate function in the select list along with a database field, we need to add a GROUP BY clause.
To correct the error it should use GROUP BY clasue.
SELECT house, count(*) FROM student
GROUP BY house;

Question: Observe the following tables TRANSACTIONS and CUSTOMERS carefully and answer the questions that follows :

(i) What is the degree of the table Transaction ? what is the cardinality of the table Customers ?
(ii) Identify the primary key and candidate keys from the table Transcations
Answer: (i) Degree of the table TRANSACTIONS=4
Cardinality of table CUSTOMERS=3
(ii) TNO PRIMARY KEY; TNO, CNO CANDIDATES KEYS

Question: Explain each of the following with illustrations using a table
(i) Candidate Key (ii) Primary Key (iii) Foreign Key
Answer: (i) Candidate Key :It refers to any column/attribute that can uniquely identify record in a table.
(ii) Primary key : It referes to designated attribute(s)/column(s) that uniquely identifies a row/tuple in a table/relation. It is one of the candidates keys.
(iii) Foreign key :is an attribute in a table which is the primary key in linked table

Question: Are count(*) and count(<column-name>) the same functions? Why/ Why not?
Answer: No, Count(*) and Count(<column-name>) are not the same.
While count(*) counts and return the number of records in a table, count(<column-name>)
counts number of records where the mentioned column-name is not null.

Question: Consider the following SQL string : ‘Preoccupied’. Write commands to display:
(a) ‘occupied’ (b) ‘cup’
Answer: (a) SELECT substr(‘’Preoccupied’, 4); OR SELECT substring(‘Preoccupied’,4);
Or
SELECT mid(‘’Preoccupied’, 4);
(b) SELECT substr(‘’Preoccupied’, 6,3); OR SELECT substring(‘Preoccupied’,6,3);
Or
SELECT mid(‘’Preoccupied’, 6,3);

Question: Consider the same string : ‘Preoccupied’. Write commands to display:
(a) The position of the substring ‘cup’ in the string ‘Preoccupied’.
(b) The first 4 letters of the string.
Answer: (a) SELECT instr(‘Preoccupied’, ‘cup’);
(b) SELECT left(‘Preoccupied’, 4);

Question: Anjali writes the following commands with respect to a table employee having fields, empno, name, department, commission.
Command1: Select count(*) from employee;
Command2 :Select count(commission) from employee;
She gets the output 4 for the first command but get an output 3 for the second command.
Explain the output with justification.
Answer: The Count(*) function returns the total number of records in the table while count(<field>)
will return the count of non-null values in the given field and this is the reason for the different results by the given queries above.
The field commission must be containing a NULL value and thus count(commission) returned the count of non-null values and count(*) return total number of records (Irrespective of NULL values in the field).

Question: Shanya Khanna is using a table Employee. It has the following columns :
Admno, Name, Agg, Stream
[Column Agg contain Aggregate marks]
She wants to display the highest Agg obtain in each Stream.
She wrote the following statement:
SELECT Stream, Max(Agg) FROM Employee;
But she did not get the desired result.
Rewrite the above query with necessary changes to help her get the desired output.
Answer: SELECT Stream, MAX(Agg)
FROM Employee
GROUP BY Stream;