CBSE Class 12 Informatics Practices Data Handling using Pandas II Assignment

Short Answer Type Questions :

Question. What is the differences between HAVING clause and WHERE clause?
Answer:

Question. Write the query for (i) and predict the output for (ii) and (iii):

(i) To count the product manufacture wise from the table Product.
(ii) SELECT Manufacture,MAX(Price),
MIN(Price), COUNT(*)FROM Product GROUP BY Manufacture;
(iii) SELECT Manufacture, MAX(Price) FROM Product;
Answer: (i) SELECT COUNT(ProductName),Manufacture FROM Product
GROUP BY Manufacture;

Question. What are the aggregate functions in SQL?
Answer: Aggregate function is a function where the values of multiple-rows are grouped together as input on certain criteria to form a single value of more significant meaning.
Some aggregate functions used in SQL are
SUM ( ), AVG( ), MIN(), etc.

Question. Write a query that counts the number of doctors registering patients for each day. (If a doctor has more than one patient on a given day, he or she should be counted only once .)
Answer: SELECT ord_date, COUNT (DISTINCT doctor_code)
FROM Patients
GROUP BY ord_date;

Question. Shanya Khanna is using a table EMPLOYEE. It has the following columns:
Admno, Name, Agg, Stream [column Agg contains Aggregate marks]
She wants to display highest Agg obtained 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;

Question. Why is it not allowed to give String and Date type arguments for SUM () and AVG() functions ?
Ans. SUM() and AVG() functions take an argument of type numeric only. So, sum and Avg are not defined the String and date data.

Question. What is the purpose of GROUP BY clause in MySQL? How is it different from ORDER BY clause? 
Answer: The GROUP BY clause can be used to combine all those records that have identical value in a particular field or a group of fields.
Whereas, ORDER BY clause is used to display the records either in ascending or descending order based on a particular field. For ascending order ASC is used and for descending order, DESC is used. The default order is ascending order.

Question. Gopi Krishna is using a table Employee. It has the following columns :
Code, Name, Salary, Dept_code
He wants to display maximum salary department wise. He wrote the following command :
SELECT Deptcode, Max(Salary) FROM Employee;
But he did not get the desired result.
Rewrite the above query with necessary changes to help him get the desired output. 
Answer: SELECT Deptcode, Max(Salary)
FROM Employee
GROUP BY Deptcode;

Question. Consider the following table Employee :

Write a query to get the total salary, maximum, minimum, average salary of employees (Job_ID wise), for Dept_ID 90 only.
Answer: SELECT Job_ID, SUM(Salary), AVG(Salary),
MAX(Salary), MIN(Salary)
FROM Employee
WHERE Dept_ID = ‘90’
GROUP BY Job_ID;

Long Answer Type Questions :

Question. Consider the table DOCTOR given below. Write commands in SQL for (i) to (ii) and output for (iii) to (v).

(i) Display the names and salaries of doctors in descending order of salaries.
(ii) Display names of each department along with total salary being given to doctors of that department.
(iii) SELECT SUM(Salary) FROM DOCTOR WHERE Department==‘Surgery’;
(iv) SELECT Department, COUNT(*) FROM DOCTOR GROUP BY Department;
(v) SELECT DOCName FROM DOCTOR WHERE Department LIKE ‘%gery%’;
Answer: (i) SELECT DOCName, Salary FROM DOCTOR ORDER BY Salary DESC;
(ii) SELECT Department, SUM(Salary) FROM DOCTOR GROUP BY Department;

Question. Go through the following table ‘Persons’ and answer the questions:

(i) What will be the output of the following command?
SELECT * FROM Persons WHERE Gender =‘F’ AND City LIKE “%Nagar”;

(d) No Output
(ii) Which column is used as primary key?
(a) PId
(b) PinCode
(c) Salary
(d) Gender
(iii) Choose the correct output.
SELECT City, COUNT (*) FROM Persons GROUP BY
City HAVING COUNT (*) > 1;

Answer: (i) (d) No ouput
(ii) (a) PId
(iii) (b)

Question. Write commands in SQL for (i) to (iii) and output for (iv) and (v): 

(i) To display names of stores along with Sales Amount of those stores that are located in Mumbai.
(ii) To display the details of store in alphabetical order of name.
(iii) To display the City and the number of stores located in that City, only if number of stores is more than 2.
(iv) SELECT MIN(DateOpen) FROM Store;
(v) SELECT COUNT(StoreId), NoOfEmp FROM Store GROUP BY NoOfEmp HAVING MAX(SalesAmt)<60000;
Answer: (i) SELECT Name,SalesAmt FROM Store WHERE City=‘Mumbai’;
(ii) SELECT * FROM Store ORDER BY Name;
(iii) SELECT City, COUNT(*) FROM Store GROUP BY Store HAVING COUNT(*)>2;

Question. Consider the table FANS:

Write MySQL queries for the following questions.
(i) To display the details of fans in descending order of their DOB.
(ii) To display the details of FANS who does not belong to AJMER.
(iii) To count the total number of fans of each fanmode.
(iv) To display the DOB of the youngest fan.
Answer: (i) SELECT * FROM FANS ORDER BY FAN_DOB DESC;
(ii) SELECT * FROM FANS WHERE
FAN_CITY<>‘AJMER’;
(iii) SELECT FAN_MODE, COUNT(*) FROM FANS GROUP BY
FAN_MODE;
(iv) SELECT MAX(FAN_DOB) FROM FANS;