Study MaterialsCBSE NotesInformatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins

Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins

 

Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins

2 Marks Questions

    Join Infinity Learn Regular Class Program!

    Download FREE PDFs, solved questions, Previous Year Papers, Quizzes, and Puzzles!

    +91

    Verify OTP Code (required)





    I agree to the terms and conditions and privacy policy.

    Question 1.
    Write the output of the following SQL queries:
    (i) SELECT RIGHT (‘software’, 2);
    (ii) SELECT INSTR (‘twelve’, lV);
    (iii) SELECT DAYOFMONTH (‘2014-03-01’);
    (iv) SELECT (76.987,2); (All India 2014C)
    Answer:
    (i) re
    (ii) 45
    (iii) 01
    (iv) 76.99

    Question 2.
    There is a column Salary in a Table EMPLOYEE. The following two statements are giving different outputs. What may be the possible reason? (Delhi2013)

    SELECT COUNT(*) FROM EMPLOYEE;
    SELECT C0UNT(Salary) FROM EMPLOYEE;

    Answer:

    SELECT COUNT (*)
    FROM EMPLOYEE:

    This statement returns the number of records in the table.

    SELECT COUNT(Salary)
    FROM EMPLOYEE;

    This statement returns the number of values (NULL values will not be counted) of the specified column.

    Question 3.
    A table FLIGHT has 4 rows and 2 columns and another table AIRHOSTESS has rows and 4 columns. How
    many rows and columns will be there if we obtain the cartesian product of these two tables? (Delhi 2012)
    Answer:
    Total number of rows will be 12 and total number of columns will be 6.

    Question 4.
    What is the purpose of GROUP BY clause in MySQL? How is it different from ORDER BY clause? (Delhi 2012; All India 2012)
    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 5.
    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 6.
    State difference between date functions NOW() and SYSDATE() of MySql.
    Answer:
    Differences between Now() and SYSDATE() of MySql are as follows:
    Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins 1

    7 Marks Questions

    Question 7.
    Consider the following table named SBOP with details of account holders. Write commands of MySql for (i) to (iv) and output for (v) to (vii).
    TABLE SBOP
    Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins 2
    (i) To display Accountno, Name and DateOfopen of account holders having transactions more than 8.
    (ii) To display all information of account holders whose transaction value is not mentioned.
    (iii) To add another column Address with datatype and size as VARCHAR(25).
    (iv) To display the month day with reference to DateOfopen for all the account holders. (Delhi2014)

    (v) SELECT COUNT (*) FROM SBOP;
    (vi) SELECT Name, Balance FROM SBOP WHERE Name LIKE “%i”;
    (vii) SELECT ROUND (Balance,-3) FROM SBOP
    WHERE Accountno="SB-5” ;

    Answer:

    (i) SELECT Account no, Name, DateOfopen FROM SBOP
    WHERE Transaction > 8:
    (ii) SELECT * FROM SBOP
    WHERE Transaction IS NULL;
    (iii) ALTER TABLE SBOP
    ADD Address VARCHAR(25);
    (iv) SELECT DAY0FM0NTH(DateOfopen), Name
    FROM SBOP:
    Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins 3

    Question 8.
    Consider the following table named EXAM with details of marks. Write command of MySQL for (i) to (iv) and output for (v) to (vii).
    Table EXAM
    Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins 4
    (i) To display all information of the students of humanities in descending order of percentage.
    (ii) To display Adno, Name, Percentage and Stream of those students whose name is less than 6 characters long.
    (iii) To add another column Bus_Fees with datatype and size as Decimal(8,2).
    (iv) To increase percentage by 2% of all the Humanities students. (All India 2014)

    (v) SELECT COUNT(*) FROM EXAM;
    (vi) SELECT SName, Percentage FROM EXAM WHERE Name LIKE “N%”;
    (vii) SELECT ROUND (Percentage ,0) FROM EXAM
    WHERE Adno=“R005”;

    Answer:

    (i) SELECT * FROM EXAM
    WHERE Stream = ‘Humanities’
    ORDER BY Percentage DESC;
    (ii) SELECT Adno, SName, Percentage, Stream FROM EXAM
    WHERE LENGTH(SName)<6;
    (iii) ALTER TABLE EXAM
    ADD Bus_Fees DECIMALS, 2) ;
    (iv) UPDATE EXAM
    SET Percentage = Percentage + 2 
    WHERE Stream = ‘Humanities’;
    Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins 5
    (vi) The given query will result in an error as there is no column named Name in table EXAM.
    Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins 6

    Question 9.
    Consider the table SUPPLIER given below. Write commands in MySQL for (i) to (iv) and output for (v) to (vii). (i)
    TABLE SUPPLIER
    Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins 7
    (iii) To count distinct City in the table.
    (iv) To insert a new row in the table SUPPLIER.
    110, “Bournvita”,’ ABC’, 170, ‘Delhi’, 40.00 (Delhi 2012)

    (v) SELECT Pname FROM SUPPLIER WHERE Supname IN ('Bread', 'Maggt');
    (vi) SELECT COUNTCDISTINCT City) FROM SUPPLIER;
    (vii) SELECT MAX(Price) FROM SUPPLIER 
    WHERE City = ‘Kol kata’;

    Answer:

    (i) SELECT Pname FROM SUPPLIER 
    WHERE Pname LIKE ‘B%’
    ORDER BY Price;
    (ii) SELECT Scode, Pname, City 
    FROM SUPPLIER
    WHERE Qty < 150;
    (iii) SELECT COUNT(DISTINCT City)
    FROM SUPPLIER;
    (iv) INSERT INTO SUPPLIER VALUES (110,‘Bournvita’, ‘ABC’, 170, ‘Delhi’, 40.00);
    Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins 8

    Question 10.
    Consider the table SHOPEE given below. Write commands in MySQL for (i) to (iv) and output for (v) to (vii).
    TABLE SHOPEE
    Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins 9
    (i) To display names of the product, whose name starts with ‘C’ in ascending order of Price.
    (ii) To display code, product name and City of the products whose quantity is less than 100.
    (iii) To count distinct Company in the table.
    (iv) To insert a new row in the table SHOPEE.
    110, ‘Pizza’, ‘Papa Jones’, 120, ‘Kolkata’, 50.00 (All India 2012)

    (v) SELECT Pname FROM SHOPEE WHERE Pname IN (‘Jam’, ‘Coffee’);
    (vi) SELECT COUNT(DISTINCT City) FROM SHOPEE;
    (vii) SELECT MAX(Qty) FROM SHOPEE WHERE City = ‘Mumbai’

    Answer:

    (i) SELECT Pname
    FROM SH0PEE WHERE Pname LIKE ‘C%’
    ORDER BY Price;
    (ii) SELECT Scode, Pname, City FROM SHOPEE
    WHERE Qty < 100;
    (iii) SELECT COUNT (DISTINCT Surname)
    FROM SHOPEE;
    (iv) INSERT INTO SHOPEE VALUES (110, ‘ Pi zza’, ‘ Papa Jones ’ , 120, ‘Kolkata’, 50.00);
    Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins 10

    Question 11.
    Consider the table PERSONS given below. Write commands in SQL for (i) to (iv) and write output for
    (v) to (viii).
    TABLE PERSONS
    Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins 11
    (i) Display the SurName, FirstName and City of people residing in Udhamwara city.
    (ii) Display the Person IDs (PID), Cities and Pincode of persons in descending order of Pincode.
    (iii) Display the FirstName and City of all the females getting Basic salaries above 40000.
    (iv) Display FirstName and BasicSalaries of all the persons whose first name start with ‘G’. (Delhi 2012c)

    (v) SELECT SurName FROM PERSONS WHERE BasicSalary>= 50000;
    (vi) SELECT SUM (BasicSalary) FROM PERSONS WHERE Gender = ‘F’;
    (vii) SELECT Gender, MIN (BasicSalary) FROM PERSONS GROUP BY Gender;
    (viii) SELECT Gender, COUNT (*) FROM PERSONS
    GROUP BY Gender;

    Answer:

    (i) SELECT SurName, FirstName, City
    FROM PERSONS
    WHERE City = ‘Udhamwara’;
    (ii) SELECT PID, City. PinCode FROM PERSONS
    ORDER BY Pincode DESC;
    (iii) SELECT FirstName, City FROM PERSONS
    WHERE Gender = ‘F’ AND
    BasicSalary > 40000;
    (iv) SELECT FirstName, BasicSalary FROM PERSONS
    WHERE FirstName LIKE ‘G%’;
    Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins 12

    Question 12.
    Consider the table EXAM given below. Write commands in MySQL for (i) to (iv) and output for (v) to (vii).
    TABLE EXAM
    Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins 13
    (i) To list the Names of those students, who have obtained Division as FIRST in ascending order of Name.
    (ii) To display a report listing Name, Subject and Annual Stipend received assuming that the Stipend column has monthly Stipend.
    (iii) To count the number of students, who have either Accounts or Informatics as Subject.
    (iv) To insert a new row in the table EXAM.
    6, ‘Mohan’, 500, ‘English’, 73, ‘SECOND’ (HOTS; Delhi 2011)

    (v) SELECT AVG(Stipend) FROM EXAM WHERE Division = ‘THIRD’;
    (vi) SELECT COUNT (DISTINCT Subject) FROM EXAM;
    (vii) SELECT MIN(Average) FROM EXAM WHERE Subject = ‘English’;

    Answer:

    (i) SELECT Name FROM EXAM
    WHERE Division=‘FIRST’
    ORDER BY Name;
    (ii) SELECT Name, Subject, Sti pend*12 FROM EXAM;
    (iii) SELECT COUNT!*)
    FROM EXAM
    WHERE Subject =‘Accounts’ OR
    Subject = ‘ Informatics’;
    (iv) INSERT INTO EXAM VALUES
    (6, ‘Mohan’,500, ‘English’, 73, ‘SECOND’);
    Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins 14
    Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins 15

    Question 13.
    Consider the table DOCTOR given below. Write commands in SQL for (i) to (iv) and output for (v) to (vii).
    TABLE DOCTOR
    Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins 16
    (i) Display the names and dates of joining of doctors of Oncology department.
    (ii) Display the names and salaries of doctors in descending order of salaries.
    (iii) Display the names and salaries of all the female’s doctors who are getting salary above $ 50000.
    (iv) Display names of each department along with total salary being given to doctors of that department. (Delhi 2011)

    (v) SELECT Department FROM DOCTOR WHERE Salary >= 55000; 
    (vi) SELECT SUM (Salary) FROM DOCTOR WHERE Department = ‘Surgery’; 
    (vii) SELECT Doc_Name FROM DOCTOR WHERE Doc_Name LIKE ’J ’%;

    Answer:

    (i) SELECT Doc_Name,Date_0f_join FROM DOCTOR
    WHERE Department =‘Oncology’;
    (ii) SELECT Doc_Name, Salary FROM DOCTOR
    ORDER BY Salary DESC;
    (iii) SELECT Doc_Name, Salary FROM DOCTOR
    WHERE Gender = ‘F’AND Salary > 50000;
    (iv) SELECT Department, SUM(Salary) FROM DOCTOR GROUP BY Department;
    Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins 17

    Question 14.
    Consider the table LIBRARY given below. Write commands in MySQL for (i) to (iv) and output for (v) to (vii).
    TABLE LIBRARY
    Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins 18
    (i) To list the BookTitle of FND type.
    (ii) To display a report listing BookTitle, Type and Price in descending order of price.
    (iii) To count the number of BookTitle, Who have FND type.
    (iv) To insert a new row in the table LIBRARY.
    7, ‘Windows 8 Basics’, ‘FND’,’McGraw’, 7,150.

    (v) SELECT AVG(Price) FROM LIBRARY WHERE Type = ‘FND’;
    (vi) SELECT COUNT(DISTINCT Type) FROM LIBRARY;

    Answer:

    (i) SELECT BookTitle FROM LIBRARY WHERE Type = ‘FND’;
    (ii) SELECT BookTitle, Type, Price FROM LIBRARY
    ORDER BY Price DESC;
    (iii) SELECT COUNT(*)
    FROM LIBRARY WHERE Type = 'FND';
    (iv) INSERT INTO LIBRARY VALUES (7, ‘Windows 8 Basics', ‘ FND’ , ‘McGraw’,7,150);
    Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins 19
    Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins 20

    Question 15.
    Consider the table STUDENT given below. Write commands in MySQL for (i) to (iv) and output for (v) to (vii). (HOTS)
    TABLE STUDENT
    Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins 21
    (i) To list the StudentName of Science Stream.
    (ii) To display a report listing StudentName, Class and Stream in ascending order of StudentName.
    (iii) To count the number of students to Science Stream.
    (iv) To insert a new row in the table STUDENT.
    7, ‘Simran Kaur’,’X!l’, ‘Commerce’, 86, ‘A2’

    (v) SELECT AVG(AggMarks) FROM STUDENT WHERE Stream=‘Science’;
    (vi) SELECT COUNT (DISTINCT Stream) FROM STUDENT;
    (vii) SELECT MIN(AggMarks) FROM STUDENT;

    Answer:

    (i) SELECT StudentName FROM STUDENT
    WHERE Stream = ‘Science’;
    (ii) SELECT StudentName, Class, Stream FROM STUDENT
    ORDER BY StudentName;
    (iii) SELECT COUNT(*)
    FROM STUDENT
    WHERE Stream = ‘Science’;
    (iv) INSERT INTO STUDENT
    VALUES (7, ‘Simran Kaur’.'XII’, ‘Commerce’, 86, ‘A2 ’);
    Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins 22

    Question 16.
    Consider the table PRODUCT given below. Write commands in MySQL for (i) to (iv) and output for (v) to (vii).
    TABLE PRODUCT
    Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins 23
    (i) To list the ProductName where BrandName is Maharaja.
    (ii) To display a report ProductName and BrandName and Price in descending order of price.
    (iii) To count the number of ProductName.
    (iv) To insert a new row in the table PRODUCT.
    6, ‘Gas Stove’,’Maharaja’,7, 6150

    (v) SELECT AVG(Price) FROM PRODUCT WHERE BrandName = ‘Maharaja’;
    (vi) SELECT COUNT (DISTINCT BrandName) FROM PRODUCT;
    (vii) SELECT MAX(Price) FROM PRODUCT;

    Answer:

    (i) SELECT ProductName FROM PRODUCT
    WHERE BrandName = ‘Maharaja’;
    (ii) SELECT ProductName, BrandName, Pri ce
    FROM PRODUCT ORDER BY Price DESC;
    (iii) SELECT COUNT (*)
    FROM PRODUCT;
    (iv) INSERT INTO PRODUCT VALUES (6,‘Gas Stove’, ‘Maharaja’,7, 6150);
    Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins 24
    Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins 25

    Question 17.
    Consider the table SPORTS given below. Write commands in MySQL for (i) to (iv) and output for (v) to (vii).
    TABLE SPORTS
    Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins 26
    (i) To list the Name of the students whose Game is Cricket.
    (ii) To display a report listing Name, Game and Grade in ascending order of name.
    (iii) To count the number of students.
    (iv) To insert a new row in the table SPORTS.
    7, ‘Rishabh’, 9, ‘Cricket’, ‘A’

    (v) SELECT Name, Game FROM SPORTS WHERE Name LIKE ‘k%‘,
    (vi) SELECT COUNT (DISTINCT Game) FROM SPORTS;
    (vii) SELECT Name, Game, Grade FROM SPORTS WHERE Grade = *B';

    Answer:

    (i) SELECT Name FROM SPORTS
    WHERE Game = ‘Cricket’;
    (ii) SELECT Name, Game,Grade . FROM SPORTS
    ORDER BY Name;
    (iii) SELECT COUNT (*)
    FROM SPORTS;
    (iv) INSERT INTO SPORTS VALUES (7, ‘Rishabh’, 9, ‘Cricket’, ‘A’);
    Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins 27

    Question 18.
    Consider the table INTERIORS given below. Write commands in MySQL for (i) to (iv) and output for (v) to (vii).
    TABLE INTERIORS
    Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins 28
    (i) To list the ItemName whose Type is Office Table.
    (ii) To display a report listing ItemName, Type and Price in descending order of Price.
    (iii) To count the number of ItemName.
    (iv) To insert a new row in the table INTERIORS.
    7, ‘Comfort Zone’,’Double Bed’,’23/03/2011′, 15000

    (v) SELECT ItemName, Type FROM INTERIORS WHERE ItemName LIKE ‘R%’;
    (vi) SELECT COUNT (DISTINCT Type) FROM INTERIORS;
    (vii) SELECT ItemName, Type FROM INTERIORS WHERE DateofStock>‘15/ll/2010’;
    Answer:
    (i) SELECT ItemName FROM INTERIORS
    WHERE Type = ‘Office Table’;
    (ii) SELECT ItemName, Type, Price FROM INTERIORS ORDER BY Price DESC;
    (iii) SELECT COUNT(*)
    FROM INTERIORS;
    (iv) INSERT INTO INTERIORS VALUES (7, ‘Comfort Zone’, ‘Double Bed’,‘23/03/2011’,15000);
    Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins 29
    Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins 30

    Question 19.
    Consider the table STUDENTS given below. Write commands in MySQL for (i) to (iv) and output for (v) to (vii).
    TABLE STUDENTS
    Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins 31
    (i) To list the StudentName whose Class is IX.
    (ii) To display a report listing StudentName, Class and Score in descending order of Score.
    (iii) To count the number of Students.
    (iv) To insert a new row in the table STUDENTS.
    7, ‘Divya Prakash’, ‘Science’, 355, ‘C’;

    (v) SELECT StudentName, Stream FROM STUDENTS WHERE Score >380;
    (vi) SELECT COUNT (DISTINCT Stream) FROM STUDENTS;
    (vii) SELECT StudentName, Class FROM STUDENTS WHERE Grade = ‘A’;
    Answer:
    (i) SELECT StudentName
    FROM STUDENTS 
    WHERE Class = IX’ ;
    (ii) SELECT StudentName, Class, Score 
    FROM STUDENTS 
    ORDER BY Score DESC;
    (iii) SELECT COUNT(* )
    FROM STUDENTS; 
    (iv) INSERT INTO STUDENTS VALUES (7, ‘Divya Prakash’, ‘Science’, 355, ‘C’);
    Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins 32
    Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins 33

    Question 20.
    Consider the following table FITNESS with details about fitness products being sold in the store. Write command of SQL for (i) to (iv) and output for (v). (HOTS)
    TABLE FITNESS
    Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins 34
    (i) To display the names of all the products with price more than 20000.
    (ii) To display the names of all products by the manufacturer ‘Aone’.
    (iii) To change the price data of all the products by applying 25% discount reduction.
    (iv) To add a new row for product with the details: ‘P7’, ‘Vibro Exerciser’, 28000, ‘Aone’.

    (v) SELECT * FROM FITNESS
    WHERE MANUFACTURER LIKE ’%e’;

    Answer:

    (i) SELECT PNAME 
    FROM FITNESS 
    WHERE PRICE > 20000;
    (ii) SELECT PNAME,
    FROM FITNESS
    WHERE MANUFACTURERSAone';
    (iii) UPDATE FITNESS
    SET PRICE=PRICE-PRICE* 25/100;
    (iv) INSERT INTO FITNESS VALUES 
    (‘P7’, ‘Vibro Exerciser’, 28000, ‘Aone’);
    Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins 35

    We hope the Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins help you. If you have any query regarding Informatics Practices Class 12 Important Questions Chapter 11 SQL Functions and Table Joins, drop a comment below and we will get back to you at the earliest.

      Join Infinity Learn Regular Class Program!

      Sign up & Get instant access to FREE PDF's, solved questions, Previous Year Papers, Quizzes and Puzzles!

      +91

      Verify OTP Code (required)





      I agree to the terms and conditions and privacy policy.